PDA

View Full Version : Tip: Accessing remote databases without Virtual Tables



barrymavin
2017-04-08, 04:26
You can access remote databases readonly without the need to setup a VirtualTable definition.



use sales.customers connstr "lianja_mssqltest"
list first 25


also...



use sales.customers connstr "lianja_mssqltest" where condition
list first 25


and...



use sales.customers connstr "lianja_mssqltest" as select * from sales.customers where condition order by column
list first 25


Once you have these "local cursors" you can join disparate databases as Lianja will build the required join indexes dynamically for you.

So, you can dynamically fetch data from remote databases with very little effort. If you need to update data you will need to setup a VirtualTable definition that describes the primary key and other pertinent information such as updatefldlist etc.

Here is an example.

1463

hmischel@diligentsystems.com
2017-06-15, 10:27
Hi,

This is very interesting. It also works when specifying a dynamic connectstring. Like so


use mydb.dbo.mytable connstr "driver={SQL server};server=myserver;Trusted_Connection=Yes"

2 questions.
1. once I call the command, how can I reference the cursor? I can list the data, but I can't seem to browse it.
2. When I call the command a second time, it is telling me the database is already open. Is there a connection that I need to close?

Thanks

Herb

barrymavin
2017-06-15, 10:42
Hi Herb,

Give it an alias name then SELECT aliasname

LIST Status to see what the default was.

One you have performed the query against MSSQL you can get a reference to the CursorAdaptor() and perform requery() on it.

hmischel@diligentsystems.com
2017-06-15, 15:02
Ahh - thanks barry.

This is exceptionally helpful.


use mydb.dbo.mytable connstr "driver={SQL server};server=myserver;Trusted_Connection=Yes" alias mytemp.

select mytemp
browse