PDA

View Full Version : [Solved] Calling a stored procedure



naeempk
2014-11-22, 12:42
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
2014-11-22, 18:29
Hello,

use CALL instead of EXEC.

barrymavin
2014-11-22, 18:31
If you want to return a resultset look at setresultset() in the doc wiki.

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

naeempk
2014-11-23, 12:42
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
2014-11-23, 18:34
Try using

call sp_name()

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

naeempk
2014-11-24, 03:11
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
2014-11-24, 03:19
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.

829

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

barrymavin
2014-11-24, 03:21
Sorry... yes you can distribute only the .dbo files.

naeempk
2014-11-24, 06:04
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
2014-11-24, 10:38
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

naeempk
2014-11-28, 08:35
Hi Yvonne Milne,

1) Thanks for submitting the ticket for .dbo files execution.

2) I am not asking about parameter passing to stored procedures. My question was to create temp tables or cursors which I can access when my SP finished. Like in MS SQL

CREATE TABLE #people
(
id INT,
name VARCHAR(32)
)

OR

a temporary table available to a all sessions

CREATE TABLE ##people
(
id INT,
name VARCHAR(32)
)


3/4) I am using Lianja SQL server from the same LAN. How can I tell my network admin to setup IP/hostnames to reduce delay.

Thanks

yvonne.milne
2014-11-28, 11:14
Hi Naeempk,

2. The TMPNAM() function will give you a temporary filename in the current temporary directory (default for Lianja Server is C:\lianja\server\tmp and for Lianja App Builder C:\Users\your-user\AppData\Local\Temp\ on Windows). You can use this with create table or select .. into table, e.g.

mtmp = tmpnam()
create table &mtmp (...)

3/4 Try putting the IP/hostname mapping in the C:\Windows\System32\drivers\etc\hosts file on the client.

Regards,

Yvonne

naeempk
2015-02-04, 12:48
Hi,

Any update regarding ticket submitted for .dbo files execution in latest release ?

yvonne.milne
2015-02-05, 07:17
Hi Naeempk,

The '.prg' does still need to be present for stored procedures. I will see if this can be scheduled for resolution.

Regards,

Yvonne

naeempk
2015-05-25, 03:53
Hi Yvonne,

We have many Lianja releases but still we need '.prg' file to execute the stored procedures. Can this be resolved in near future ?

Regards
Naeem