Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: [Solved] Calling a stored procedure

  1. #1
    Member
    Join Date
    Feb 2012
    Posts
    43

    [Solved] Calling a stored procedure

    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
    Last edited by barrymavin; 2014-12-23 at 21:33.

  2. #2
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    5,726
    Hello,

    use CALL instead of EXEC.
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  3. #3
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    5,726
    If you want to return a resultset look at setresultset() in the doc wiki.

    http://www.lianja.com/doc/index.php/SETRESULTSET()
    Last edited by barrymavin; 2014-11-23 at 03:55.
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  4. #4
    Member
    Join Date
    Feb 2012
    Posts
    43
    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

  5. #5
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    5,726
    Try using

    call sp_name()

    to to get multiple result sets issue two sqlexec() calls.
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  6. #6
    Member
    Join Date
    Feb 2012
    Posts
    43
    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

  7. #7
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    5,726
    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.

    Name:  Screen Shot 2014-11-24 at 3.16.31 PM.jpg
Views: 630
Size:  39.0 KB

    3. No. Lianja will look for the stored procedure by filename in the database container directory.
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  8. #8
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    5,726
    Sorry... yes you can distribute only the .dbo files.
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  9. #9
    Member
    Join Date
    Feb 2012
    Posts
    43
    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

  10. #10
    Lianja Team yvonne.milne's Avatar
    Join Date
    Feb 2012
    Location
    Berkshire, UK
    Posts
    1,369
    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

Page 1 of 2 12 LastLast

Bookmarks

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Journey into the Cloud
Join us