Results 1 to 5 of 5

Thread: How to call a SQL Stored Procedure

  1. #1

    Default How to call a SQL Stored Procedure

    I feel like I must be stupid or somehow lazy, as I can't find anyone asking this question on here, but neither can I decipher the help on this matter...

    When looking at how to call SQL Stored Procedures the help seems to suggest I need to do the following:

    Send SQLSetProcedureName 'APP_DoTheThing'
    Send SQLSetArgument sVarForSQLParam
    Send SQLCall
    Which is fine. Except that I get 'Invalid Message MSG_SQLSETPROCEDURENAME' when I'm debugging.

    I looked at the examples and they appear (vaguely, maybe) to be explicitly setting up a connection to a database. But my application already has a connection to the MSSQL Database in question, used by such things as 'Entry_Item' and even just going Move Table.Field to sVariable...

    Is that the problem? Do I need to set up an explicit extra connection? And if so, can someone point me to the section of the help that's gonna go through how that works (it'd be nice to know why it's necessary)
    Of course if I'm wrong and the problem is something else I'd love to know what it *is*...

  2. #2
    Join Date
    Feb 2009
    Hengelo, Netherlands

    Default Re: How to call a SQL Stored Procedure


    I am missing the object reference in each call. It is of course possible that you send these messages from within the cSQLStatement based object but in most cases this object is created dynamically which means you need to pass the object handle.

    For an example:
    Data Access Worldwide
    Vincent Oorsprong

  3. #3

    Default Re: How to call a SQL Stored Procedure

    Ok so I think I'm getting a *little* more clarity here...

    Thanks for directing me back to that topic in the help, as with my fresh day eyes I've managed to work out the following:

    All three of those procedures are methods of the cSQLStatement Class, and must therefore be called from within an object of that class, or to reference such an object.
    This cSQLStatement object seems to need to be created by a cSQLConnection object, which seems itself to need to be created by a cSQLHandleManager object.

    So to execute a Stored Procedure that already exists on the database that the application already has a connection to (for DDOs, DEOs etc to work) I must

    1. Create a cSQLHandleManager object.
    2. Use that to create a cSQLConnection
    3. Use that to create a cSQLStatement
    4. Use that to define the name and arguments, and then call the procedure.

    I guess I'm confused because it seems nuts to me that it must be that complicated. Is there no way to simply pass the name and arguments of the Stored Procedure to the Global cConnection object, as it is already handling such a connection?

  4. #4
    Join Date
    Feb 2009
    South Florida

    Default Re: How to call a SQL Stored Procedure

    thats how it works. And yes DF is a bit verbose (lol) at times

    in other languages you might just write

    sqlhandler.connection.statment.execute("select ... ")

    or similar

    you can of course create a simple function yourself that hides all that logic

    also dont forget to clean up afterwards

    close the statement, disconnect the connection and destroy the objects
    Michael Salzlechner
    StarZen Technologies, Inc

    Development Blog

    DataFlex Package Manager (aka Nuget for DataFlex)

  5. #5
    Join Date
    Mar 2009

    Default Re: How to call a SQL Stored Procedure

    This is how I do it:

              Get SQLConnectionId of ghoConnection "YOURDATABASE" to hoConnection
              If (hoConnection = 0) Begin
                 Send End_Status
                 Send Stop_Box "Cannot Open Connection!"
              Else Begin
                 Get SQLOpen of hoConnection to hoStmt 
                 Move (SFormat("EXEC DELETEBBATCHBYCYCLE '%1'", sCycle)) to sQuery
                 Send SQLExecDirect of hoStmt sQuery
                 Send SQLClose of hoStmt
              Send SQLDisconnect of hoConnection
    You could also create a global procedure in the application that has all this code in it and set a parameter to pass the sQuery string to it and then all you need to do each time is format the sQuery string.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts