Category: Stored procedure

Stored procedure [examples]

Published on by homunizamLeave a comment

Creates a stored procedure in a database

CREATE PROCEDURE
OPEN DATABASE southwind
CREATE PROCEDURE creaxml AS
SELECT orders.orderid, orders.customerid, employees.employeeid,;
  employees.lastname, employees.firstname, orders.orderdate,;
  orders.freight, orders.requireddate, orders.shippeddate,;
  orders.shipvia, orders.shipname, orders.shipaddress, orders.shipcity,;
  orders.shipregion, orders.shippostalcode, orders.shipcountry,;
  customers.companyname, customers.address, customers.city,;
  customers.region, customers.postalcode, customers.country;
  FROM orders INNER JOIN customers;
  ON customers.customerid = orders.customerid,;
  orders INNER JOIN employees;
  ON orders.employeeid = employees.employeeid;
  SAVE AS XML orderinfo
ENDCREATE

https://www.lianja.com/doc/index.php/CREATE_PROCEDURE

Removes a stored procedure from a database
DROP PROCEDURE
OPEN DATABASE southwind
// Removes a stored procedure from a database 
DROP PROCEDURE creaxml

https://www.lianja.com/doc/index.php/DROP_PROCEDURE

Compile stored procedure files in the specified database or databases

COMPILE DATABASE
compile database southwind

https://www.lianja.com/doc/index.php/COMPILE_DATABASE

Mark an SQL cursor as a resultset

SETRESULTSET
function GetExampleCursor
  lparameters lcAccountNo
  select * from example where account_no = lcAccountNo into cursor curExample
return setresultset("curExample")
 
open database southwind
GetExampleCursor("00050")
select * from curexample
? "Cleared resultset marker in work area #" + ltrim(str(clearresultset()))
? iif(getresultset() > 0,"Resultset available in work area #" + ltrim(str(getresultset())),;
       "No resultsets available")
?
close databases

https://www.lianja.com/doc/index.php/SETRESULTSET()

Clear the marker from an SQL cursor previously marked as a resultset

CLEARRESULTSET
function GetExampleCursor
  lparameters lcAccountNo
  select * from example where account_no = lcAccountNo into cursor curExample
return setresultset("curExample")
 
open database southwind
GetExampleCursor("00050")
select * from curexample
? "Cleared resultset marker in work area #" + ltrim(str(clearresultset()))
? iif(getresultset() > 0,"Resultset available in work area #" + ltrim(str(getresultset())),;
       "No resultsets available")
?
close databases

https://www.lianja.com/doc/index.php/CLEARRESULTSET()

Return the workarea number of an SQL cursor previously marked as a resultset

GETRESULTSET
function GetExampleCursor
  lparameters lcAccountNo
  select * from example where account_no = lcAccountNo into cursor curExample
return setresultset("curExample")
 
open database southwind
GetExampleCursor("00050")
select * from curexample
? "Cleared resultset marker in work area #" + ltrim(str(clearresultset()))
? iif(getresultset() > 0,"Resultset available in work area #" + ltrim(str(getresultset())),;
       "No resultsets available")
?
close databases

https://www.lianja.com/doc/index.php/GETRESULTSET()

Description of the input, output and results associated with stored procedures

SYSPROCEDURECOLUMNS
SELECT * FROM sysprocedurecolumns

https://www.lianja.com/doc/index.php/SYSPROCEDURECOLUMNS

Description of the stored procedures available in the catalog

SYSPROCEDURES
SELECT * FROM sysprocedures

https://www.lianja.com/doc/index.php/SYSPROCEDURES

Executing Procedures and Prepared Statements using Lianja SQL

PREPARE

 

create procedure creaxml as
select orders.orderid, orders.customerid, employees.employeeid,;
  employees.lastname, employees.firstname, orders.orderdate,;
  orders.freight, orders.requireddate, orders.shippeddate,;
  orders.shipvia, orders.shipname, orders.shipaddress, orders.shipcity,;
  orders.shipregion, orders.shippostalcode, orders.shipcountry,;
  customers.companyname, customers.address, customers.city,;
  customers.region, customers.postalcode, customers.country;
  from orders inner join customers;
  on customers.customerid = orders.customerid,;
  orders inner join employees;
  on orders.employeeid = employees.employeeid;
  save as xml orderinfo
endcreate
 
drop procedure creaxml

The prepare statement is used to set up an SQL statement for subsequent execution.

stmtbuf = "select * from example where account_no = ?"
prepare mystmt from :stmtbuf
m_acc = "00002"
execute mystmt using :m_acc
 
m_acc = "00003"
execute mystmt using :m_acc
 
stmtbuf = "insert into example (account_no, last_name, forename) values (?,?,?)"
prepare mystmt from :stmtbuf
buf1 = "99999"
buf2 = "Smith"
buf3 = "John"
execute mystmt using :buf1, :buf2, :buf3
 
stmtbuf = "delete from example where account_no = ?"
prepare mystmt from :stmtbuf
buf1 = "99999"
execute mystmt using :buf1

The execute immediate statement is used to prepare and execute immediately a delete, insert, or update SQL statement.

execute immediate select * from example where account_no = m_acc

The sysresultset system table returns the singleton result from any Lianja expression.

select set("exclusive") as excl, time() as time from sysresultset

https://www.lianja.com/doc/index.php/Using_Recital_SQL

create procedure creaxml as
select orders.orderid, orders.customerid, employees.employeeid,;
  employees.lastname, employees.firstname, orders.orderdate,;
  orders.freight, orders.requireddate, orders.shippeddate,;
  orders.shipvia, orders.shipname, orders.shipaddress, orders.shipcity,;
  orders.shipregion, orders.shippostalcode, orders.shipcountry,;
  customers.companyname, customers.address, customers.city,;
  customers.region, customers.postalcode, customers.country;
  from orders inner join customers;
  on customers.customerid = orders.customerid,;
  orders inner join employees;
  on orders.employeeid = employees.employeeid;
  save as xml orderinfo
endcreate

drop procedure creaxml

stmtbuf = "select * from example where account_no = ?"
prepare mystmt from :stmtbuf
m_acc = "00002"
execute mystmt using :m_acc
 
m_acc = "00003"
execute mystmt using :m_acc
 
stmtbuf = "insert into example (account_no, last_name, forename) values (?,?,?)"
prepare mystmt from :stmtbuf
buf1 = "99999"
buf2 = "Smith"
buf3 = "John"
execute mystmt using :buf1, :buf2, :buf3
 
stmtbuf = "delete from example where account_no = ?"
prepare mystmt from :stmtbuf
buf1 = "99999"
execute mystmt using :buf1
execute immediate select * from example where account_no = m_acc
select set("exclusive") as excl, time() as time from sysresultset

https://www.lianja.com/doc/index.php/Using_Lianja_SQL

Server side procedures [examples]

Published on by homunizamLeave a comment

Lianja.evaluate()

The Lianja.evaluate() method can be used to call a Lianja/VFP function/procedure.

var result = Lianja.evaluate("myproc()");

To call a procedure in a library, prefix the procedure name with the library name and ‘::’.

var result = Lianja.evaluate("mylib::mylibproc()");

Lianja.evaluateJavascript()

The Lianja.evaluateJavascript() method can be used to call a JavaScript function.

var result = Lianja.evaluateJavascript("myfunc()");

The file ‘myfunc.js’ should be in the app or library directory and contain a function with the same name as the file. If the ‘myfunc.js’ file does not exist in the app or library directory and the file ‘server_functions.js’ exists then the function ‘myfunc’ is assumed to be defined in the ‘server_functions.js’ file.

To call a function in a library, prefix the function name with the library name and ‘::’.

var result = Lianja.evaluateJavascript("myjslib::mylibfunc()");

An alternative syntax using the Lianja.evaluate() method is also available:

var result = Lianja.evaluate("javascript:myfunc()");
var result = Lianja.evaluate("javascript:myjslib::mylibfunc()");

 

 

Stored procedure

Published on by homunizamLeave a comment

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

Stored procedure

Published on by homunizamLeave a comment

You can pass parameters to your stored procedure, e.g. nret = sqlexec(nhand,”call sp_demo(‘MA’)”,”mycursor”)

Currently, the ‘.prg’ needs to be present for stored procedures. This will be resolved in a future release, so that only the ‘.dbo‘

Lianja SQL Server lets you call stored procedures (or in fact just procedures) that use SEEK/SCAN/etc NoSQL commands inside them.

Server-side pages (.rsp pages written in VFP compatible scripting) provide the same level of functionality in the cloud.
Best of both worlds NoSQL and SQL.

Q:
I created a stored procedure in “southwind” database through Lianja App builder. I named is as
sp_GetOrderDetails.prg
=======================
select * from order_details into cursor cc

I can run this in console and can browse cc cursor.
How can I call this or any other stored procedure from “Lianja Sql Server” to VFP ?
A:
call sp_name()
to to get multiple result sets issue two sqlexec() calls.

Q:
In VFP, a Databsae file (.DBC), saved all sotred procedures in field named as ‘code‘.
We can edit any procedure with ‘modify procedure‘.
Can we have multiple stored procedures in a single sp_library.prg file ?
A:
You edit stored procedures in the “Data” workspace for the database table.

No. Lianja will look for the stored procedure by filename in the database container directory.