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.

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

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

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

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


Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

w

Connecting to %s