[Answers] Stored procedure

[josipradnik]

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.

---

---

​All topics in [Answers] alphabetically:http://www.lianja.com/community/show...ll=1#post12352

[josipradnik]

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.

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.

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

---

---

All topics in [Answers] alphabetically: https://www.lianja.com/community/sho...ll=1#post13748

These answers are also systematized on the site "Lianja developer": https://lianjadeveloper.wordpress.co...red-procedure/