Difference between revisions of "Virtual Tables - Local Stored Procedures"

Latest revision as of 20:11, 15 December 2020

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

• SET CONNSTR Command (default 'ODBC Connection')

• Using Parameterized Views

Overview

You can use "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.

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 vtorders connstr "local" as call sp_getorders()

Here is the stored procedure which is executed locally:

// This is an example of a "local" stored procedure
select * from southwind!orders into cursor localorders
return setResultSet("localorders")

The stored procedure can be quite sophisticated, including joins and filters, 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.

If your virtual table is to be used from the web/mobile clients, the basetable must be set using the TABLE clause of CREATE VIRTUALTABLE or ALTER VIRTUALTABLE:

create virtualtable vtorders connstr "local" table "orders" as call sp_getorders()

or

create virtualtable vtorders connstr "local" as call sp_getorders()
alter virtualtable vtorders table "orders"

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

create virtualtable vtorders connstr "local" table "orders" ;
           as call sp_getorders("{m_arg1}", "{m_arg2}")

or

create virtualtable vtorders connstr "local" table "orders" ;
          as call sp_getorders(?m_arg1,?m_arg2)

// This is an example of a "local" stored procedure using parameters
select * from southwind!orders where customerid = ?m_arg1 ;
            and employeeid = ?m_arg2 into cursor localorders
return setResultSet("localorders")

In desktop apps, the parameters must be declared as public variables. In OData calls from the Web/Mobile clients, these are specified using the $args argument.

The OData $args argument is set to a comma-separated list of parameters. These are parsed and created as server side public/global variables containing the specified values. The first value is assigned to a public variable named m_arg1, the second to m_arg2 and so on.

In Web/Mobile Apps you can use Parameterized Views. These result in sending an OData request to the server with a $args list which corresponds to the named parameters specified.

Here, the public variable m_arg1 will be created and will contain the value "ALFKI" and the public variable m_arg2 will be created and will contain the value 4.

http://localhost:8001/odata/southwind/vtorders?$args=ALFKI,4

So, if your stored procedure is to be used from both desktop and the web/mobile clients, the m_argN naming convention should be used.

If parameters are optional, their existence can be checked in the stored procedure itself, e.g.

create virtualtable vtorders connstr "local" table "orders" as call sp_getorders()

// This is an example of a "local" stored procedure using optional parameters
if isset(m_arg1) and isset(m_arg2)
	select * from southwind!orders where customerid = ?m_arg1 ;
                     and employeeid = ?m_arg2 into cursor localorders
else
	select * from southwind!orders into cursor localorders
endif
return setResultSet("localorders")