Results 1 to 3 of 3

Thread: Virtual Tables with "local" stored procedures in 5.5

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

    Virtual Tables with "local" stored procedures in 5.5

    I've been asked recently about how to use virtual tables that access remote data when you want to use LianjaScript to massage the data that is retrieved.

    This is accomplished using "local" stored procedures. These reside in the client side database and can be written in LianjaScript.

    Create your Virtual Table and specify "local" as the connection string, then "call sp_getorders()" as the sql command.

    Name:  Screen Shot 2020-11-11 at 11.06.32 AM.jpg
Views: 82
Size:  20.8 KB

    "dir" in the console can be used to verify the VT definitions.

    Name:  Screen Shot 2020-11-11 at 11.09.59 AM.jpg
Views: 89
Size:  10.0 KB

    https://www.lianja.com/doc/index.php...red_Procedures

    In Lianja 5.5 the special macro "{where}" which will be substituted by requery() on the CursorAdaptor. This can now contain a "where" clause and an "order by" clause. See this example below.

    Name:  Screen Shot 2020-11-11 at 11.09.39 AM.jpg
Views: 102
Size:  105.0 KB

    Sample code:

    Code:
    open database southwind
    use vt_localcall
    list first 5
    ca = cursoradaptor()
    ca.requery("customerid='WILMK' order by freight")
    list first 5
    ca.requery("customerid='WILMK' order by shipvia,freight")
    list first 5
    ca.requery()
    list first 5
    
    The sp_getorders.prg code is simply:

    Code:
    parameter m_where
    if typeof(m_where) = "string"
        select * from southwind!orders &m_where into cursor localorders
    else
        select * from southwind!orders into cursor localorders
    endif
    return setResultSet("localorders")
    With a little bit of thought and creativity your stored procedure could use sqlConnect() and sqlExec() or the VFP OLEDB driver to access remote data and present it as a Virtual Table.
    Last edited by barrymavin; 2020-11-10 at 22:39.
    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
    Location
    Rome - Italy
    Posts
    1,877
    very interestig
    thanks Barry

  3. #3
    Senior Member
    Join Date
    Oct 2012
    Posts
    209
    I agree, very interesting!

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