[Solved] Calling a stored procedure

[naeempk]

Hello,

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 ?

I am getting error in vfp if I call it as

? SQLEXEC(lnConnHandle,"exec sp_GetOrderDetails")
Connectivity error: Unrecognized SQL phrase/keyword near column 4


Thanks

[barrymavin]

Hello,

use CALL instead of EXEC.

[barrymavin]

If you want to return a resultset look at setresultset() in the doc wiki.

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

[naeempk]

Hello,

1) I tried to call a stored procedure in vfp9 and the following is the error

? SQLEXEC(lnconnHandle,"call sp_GetOrderDetails")
Connectivity error: Variable/field 'SP_GETORDERDETAILS' not found

I even tried

? SQLEXEC(lnconnHandle,"sp_GetOrderDetails")
Connectivity error: Unrecognized SQL phrase/keyword near column 18

and

? SQLEXEC(lnconnHandle,"execute sp_GetOrderDetails")
Connectivity error: Unrecognized SQL phrase/keyword near column 26


2) How can I get multiple resultsets from a single sql pass through command.
like
? SQLEXEC(lnconnHandle,"select * from orders;select * from oder_details")

Thanks

[barrymavin]

Try using

call sp_name()

to to get multiple result sets issue two sqlexec() calls.

[naeempk]

Thank you. It's working now.

1) When I call my stored procedure, Lianja Sql server makes a file with dbo extension. What is this ? Is it a fxp equivalent ? If yes, then will it call this file if I remove my orignal prg file from there ? Let's assume I want to distribute some stored procedures and hide code like distributing fxp files only.

2) 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 ?

Thanks

[barrymavin]

Hi Naeem,

1. .dbo files are compiled .prg files just as .rso files are compiled .rsp files.

2. You edit stored procedures in the "Data" workspace for the database table. See below.



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

[barrymavin]

Sorry... yes you can distribute only the .dbo files.

[naeempk]

Hi,

1) I renamed my prg file to some other name and now when I tried again I got this error.

? SQLEXEC(lnconn,"call sp_GetOrderDetails()")
_cliptext = MESSAGE()
Connectivity error: File 'sp_GetOrderDetails.prg' does not exist

How can I direct Lianja Sql server to execute my dbo files if corresponding prg files are missing ?


2) >> to to get multiple result sets issue two sqlexec() calls.

I have vfp prg files which process some data and finally produces some cursors for vfp reports. I want to convert these prgs to Lianja stored procedure so that I can rebuild/convert these reports in Lianja. The objective is to provide these reports from Lianja cloud to our remote users. How can I create/save 'temporay tables' in lianja like in MS SQL server (created by # and ##) ? Some of my current vfp prgs create multiple vfp cursors and then I display multiple reports in sequence like first summary report (based on vfp cursor1) and then detail report (based on vpf cursor 2). Is there a way to do this in Lianja ?

3) When I connect from vfp through ODBC to lianja server it takes more time to connect than other databases like MS SQL / Oracle.

If I use a connection string instead of a pre defined data source name, Sqlstringconnect takes more time than Sqlconnect("ODBC"). The following is the connection string

lcDSNLess="DRIVER=Lianja ODBC Driver;SERVER=xxx.xxx.xxx.xxx;DATABASE=southwind;U id=someuser;Pwd=somepassword"

4) I got all records (2154 ?) from order_details table in Lianja console in 11ms. But from ODBC in vfp9, I got a slow response. The "Press Esc to cancel" message appears in wait window. Why it takes more time via ODBC for such a small number of records ?

Thanks

[yvonne.milne]

Hi Naeempk,

1. We have a ticket submitted for this. Currently, the '.prg' needs to be present for stored procedures. This will be resolved in a future release, so that only the '.dbo' needs to be present.

2. You can pass parameters to your stored procedure, e.g. nret = sqlexec(nhand,"call sp_demo('MA')","mycursor") would this not allow you to call your stored procedure and return the resultsets you need based on the parameter passed, or have I misunderstood the requirement?

3/4. I'm not seeing delays in my tests here. Can you give me more details on the delays you are experiencing? Also, are you accessing the local host, or a remote host? I believe the latest version of the Lianja Server no longer does a reverse lookup on connection - which could cause delays if IP/hostnames are not fully configured on the server - but I will double-check that.

Regards,

Yvonne Milne