Virtual Tables - Local Stored Procedures

Under Construction

See Also

• Introduction to Virtual Tables

• Syntax for the CREATE VIRTUALTABLE and ALTER VIRTUALTABLE Commands

• Virtual Tables - Further Configuration and Troubleshooting

• Virtual Tables - Local Development, Remote Deployment

• Virtual Table Properties

• Properties and Methods of the CursorAdapter Class

Overview

A small but powerful enhancement introduced 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.

From 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.