Stored procedure

A small but powerful enhancement in Lianja 3.4 is the ability to specify “local” stored procedures for Virtual Tables.

As you are probably aware by now, Virtual Tables provide transparent access to third party SQL backends.

Occasionally you want to fetch data from several backend servers and join the cursors or filter the records before presenting to the user.

In Lianja 3.4 you can create a Virtual Table with a “local” connection string and then specify the SQL command as a call to a stored procedure in the database.

create virtualtable barry connstr "local" as call sp_getorders()

Parameter substitution occurs when {arg} is specified or ?arg

create virtualtable barry connstr "local" as call sp_getorders("{m_arg1}", "{m_arg2}")

You must declare m_arg1 and m_arg2 as public variables in desktop apps and specify them as $args=value1,value2… etc in OData calls from the Web/Mobile clients.

Here is the stored procedure which is executed locally.

// This is an example of a "local" stored procedure
select * from orders into cursor localorders
// filter or join cursors here
return setResultSet("localorders")

The stored procedure can be quite sophisticated and the key to using these “local” stored procedures is to always return back the resultset using the setResultSet(“alias”) function as shown in the example above.

For more on using local stored procedure based virtual tables from desktop and web/mobile clients, please see the Lianja wiki here:

Virtual Tables – Local Stored Procedures


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.