Results 1 to 4 of 4

Thread: Tip: Accessing remote databases without Virtual Tables

  1. #1
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    6,076

    Tip: Accessing remote databases without Virtual Tables

    You can access remote databases readonly without the need to setup a VirtualTable definition.

    Code:
    use sales.customers connstr "lianja_mssqltest"
    list first 25
    also...

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

    Code:
    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.

    Name:  Screen Shot 2017-04-08 at 3.40.26 PM.jpg
Views: 453
Size:  140.9 KB
    Last edited by barrymavin; 2017-04-08 at 04:49.
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  2. #2
    Senior Member
    Join Date
    Feb 2012
    Posts
    1,236
    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

  3. #3
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    6,076
    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.
    Last edited by barrymavin; 2017-06-15 at 10:55.
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  4. #4
    Senior Member
    Join Date
    Feb 2012
    Posts
    1,236
    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

Bookmarks

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Journey into the Cloud
Join us