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

From Lianjapedia
Jump to: navigation, search
(Created page with "''Under Construction'' ==See Also== * Introduction to Virtual Tables * Syntax for the CREATE VIRTUALTABLE and ALTER VIRTUALTABLE Commands * Vi...")
 
(→‎Overview)
Line 24: Line 24:
 
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.
 
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()
+
  create virtualtable vtorders 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.
+
Here is the stored procedure which is executed locally:
  
 
<pre>
 
<pre>
 
// This is an example of a "local" stored procedure
 
// This is an example of a "local" stored procedure
−
select * from orders into cursor localorders
+
select * from southwind!orders into cursor localorders
−
// filter or join cursors here
+
 
return setResultSet("localorders")</pre>
 
return setResultSet("localorders")</pre>
  
−
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()|setResultSet("alias")]] function as shown in the example above.
+
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()|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)
 +
 +
<pre>// 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")</pre>
 +
 +
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 public 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.
 +
 +
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()
 +
 +
<pre>// 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")</pre>
  
  

Revision as of 06:09, 21 April 2017

Under Construction

See Also

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

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")
Retrieved from "https://www.lianja.com/doc/index.php?title=Virtual_Tables_-_Local_Stored_Procedures&oldid=16563"