Tip: Accessing remote databases without Virtual Tables

[barrymavin]

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.

[hmischel@diligentsystems.com]

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]

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]

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