Virtual Tables with "local" stored procedures in 5.5

[barrymavin]

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.



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



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.



Sample 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:

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.

[phabio]

very interestig
thanks Barry

[strato]

I agree, very interesting!