Announcement

Collapse
No announcement yet.

Stored procedure with SQLTools

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Stored procedure with SQLTools

    I want to call to store procedure that run a SELECT in SQLServer and that has 4 input parameters, with SQLTools Pro in a program written in powerbasic. Store procedure is declared of the following form:
    spo_buscarArticulo (@param1 Int, @param2 int, @param3 int, @param4 varchar(30))

    The code that calls to store procedure is:
    cmd = " exec spo_buscarArticulo 13,3,1, ' CANALETA' "
    SQL_Stmt %SQL_STMT_IMMEDIATE, cmd$
    IF SQL_ErrorPending THEN
    lResult& = SQL_MsgBox (sql_errorQuickAll, %MSGBOX_OK)
    ELSE
    DO
    SQL_Fetch %NEXT_ROW
    IF SQL_EOD THEN EXIT LOOP
    Fld (1) = SQL_ResColText (1)
    .....
    LOOP
    END IF

    But when doing the test of the program, does not give back anything to me. In what I am being mistaken, or perhaps I must use SQL_Bind ?.

    Thanks for the aid that could give me

  • #2
    I found this in my SDK... you'll have to translate to SQLTools syntax but I think the path is clear,,,,

    How to call stored procedures (ODBC)
    When a SQL statement calls a stored procedure using the ODBC CALL escape clause, the Microsoft® SQL Server™ driver sends the procedure to SQL Server using the remote stored procedure call (RPC) mechanism. RPC requests bypass much of the statement parsing and parameter processing in SQL Server and are faster than using the Transact-SQL EXECUTE statement.

    To run a procedure as an RPC

    Construct a SQL statement that uses the ODBC CALL escape sequence. The statement uses parameter markers for each input, input/output, and output parameter, and for the procedure return value (if any):
    {? = CALL procname (?,?)}

    Call SQLBindParameter for each input, input/output, and output parameter, and for the procedure return value (if any).


    Execute the statement with SQLExecDirect.
    No, I don't know how to return a complete result set from a stored procedure.

    MCM
    Last edited by Michael Mattias; 22 May 2008, 09:18 AM.
    Michael Mattias
    Tal Systems (retired)
    Port Washington WI USA
    [email protected]
    http://www.talsystems.com

    Comment


    • #3
      Afaik the sp's result is a table 'variable'.

      When we do T-SQL we use dbo.thefunction(...)
      hellobasic

      Comment


      • #4
        Marco --

        I am away from my dev computer today so I can't check until later, but I think that Michael is correct in saying that the correct ODBC syntax for a stored procedure (for SQL Server) is "call" not "exec". There's a page about call in Appendix A of the SQL Tools Help File.

        Other than that, if the stored procedure is a SELECT statement, I don't see anything wrong with your code. Binding should not be necessary, other than what SQL Tools does automatically.

        I'll check back in with this thread when I return to my office...

        -- Eric Pearson, Perfect Sync Software
        "Not my circus, not my monkeys."

        Comment


        • #5
          Thanks to all by the answers. I have modified my code to use " call". But , when test the program gives back [not bound]. My code is the following one:

          Code:
            
            cmd = "{call spo_buscarCliente (?,?,?,?)}"
          	  SQL_Stmt %SQL_STMT_PREPARE, cmd
          		
          	  codalm = 1
          		SQL_BindParam 1, _                 								'bind parameter #1
                          %SQL_PARAM_INPUT, _    								'this is an input parameter
                          %BAS_LONG, _         	  							'BASIC data type
                          %SQL_INTEGER, _          							'SQL data type
                          11, _                  								'display size of a SQL_CHAR
                          0, _                   								'digits after the decimal point
                          VarPtr(codalm), _     								'pointer to variable to receive data
                          SizeOf(codalm), _     								'size of variable
                          lcodalm               								'indicator variable (required)
           		
          	  longcod = 13
          		SQL_BindParam 2, _                 								'bind parameter #1
                          %SQL_PARAM_INPUT, _    								'this is an input parameter
                          %BAS_LONG, _         	  							'BASIC data type
                          %SQL_INTEGER, _          							'SQL data type
                          11, _                  								'display size of a SQL_CHAR
                          0, _                   								'digits after the decimal point
                          VarPtr(longcod), _     								'pointer to variable to receive data
                          SizeOf(longcod), _     								'size of variable
                          llongcod               								'indicator variable (required)
          
          	  codalm = 3
          		SQL_BindParam 3, _                 								'bind parameter #1
                          %SQL_PARAM_INPUT, _    								'this is an input parameter
                          %BAS_LONG, _         	  							'BASIC data type
                          %SQL_INTEGER, _          							'SQL data type
                          11, _                  								'display size of a SQL_CHAR
                          0, _                   								'digits after the decimal point
                          VarPtr(tipoBus), _     								'pointer to variable to receive data
                          SizeOf(tipoBus), _     								'size of variable
                          ltipoBus               								'indicator variable (required)
          
          	  cadbus = "CANALETA"
          		SQL_BindParam 4, _                 								'bind parameter #1
                          %SQL_PARAM_INPUT, _    								'this is an input parameter
                          %BAS_STRING, _       	  							'BASIC data type
                          %SQL_CHAR, _            							'SQL data type
                          30, _                  								'display size of a SQL_CHAR
                          0, _                   								'digits after the decimal point
                          VarPtr(cadbus), _     								'pointer to variable to receive data
                          SizeOf(cadbus), _     								'size of variable
                          lcadbus               								'indicator variable (required)
           	 	 			
          '    SQL_Stmt %SQL_STMT_IMMEDIATE, cmd$
          	  SQL_Stmt %SQL_STMT_EXECUTE, cmd
          
              If SQL_ErrorPending Then
                lResult& = SQL_MsgBox(sql_errorQuickAll, %MSGBOX_OK)
              Else  
                do
                  SQL_Fetch %NEXT_ROW
                  If SQL_EOD Then exit loop
                  
                  txt = SQL_ResColText(1)
                  l = msgbox (txt, %MB_ICONQUESTION OR %MB_YESNO OR %MB_TASKMODAL,"Seguimos ?")
                  if l = %IDNO THEN exit loop
                    
                loop
                
              End If

          Comment


          • #6
            Marco --

            Wow, based on that last snippet there are many things that could be causing problems. Here's what I think we should do.

            1) Go back to a simpler version of your program, as shown in your first post.

            2) In the SQL statement, use call instead of exec, curly brackets (if necessary), etc. Do not bind parameters, use literal values as shown in first post.

            3) Add SQL_Trace %TRACE_ON right after SQL_Init.

            4) Compile and run the program.

            5) A trace file will be created in the root folder of your C drive. Please send it to the email address below.

            -- Eric
            "Not my circus, not my monkeys."

            Comment


            • #7
              Erick thanks for the answer.
              The problem seems to be in stored procedure.
              Stored procedure works correctly within SQL Management Studio, but in SQLTools no.
              The code of stored procedure is more or less :

              CREATE PROCEDURE spo_buscar_articulo .....

              IF @tipoBus = 1
              SELECT ..... WHERE almacen = @codalm
              ELSE
              IF @tipoBus = 2
              SELECT… WHERE almacen = @codalm
              ELSE
              SELECT… WHERE almacen = @codalm
              GO

              If I erase instructions IF (apparently the IF is the problem) within stored procedure and use a single instruction SELECT program PB with SQLTools works correctly. The call to stored procedure with SQLTools is:

              cmd = " exec spo_buscar_articulo_porDescripcion 1,13,3, ' CANALETA' "

              I am not use "call ..." .

              Perhaps I am using incorrectly the IF, but still I do not know that I am making bad. It is not problem with the parameters apparently.

              Comment


              • #8
                Marco --

                Stored procedure works correctly within SQL Management Studio, but in SQLTools no.
                Then this is almost certainly a SQL-statement syntax problem.

                You see, SQL Tools does not process SQL statements in any way, we simply pass them -- as a string -- to the ODBC driver and say "please process this statement". So you are probably seeing a difference between 1) SQL Management Studio's syntax and 2) the syntax that the ODBC Driver is expecting. Neither of those things are under SQL Tools' control, of course, it all comes down to the specific syntax that is required by SQL Server.

                Are you certain that you should use exec instead of call?

                > apparently the IF is the problem

                Then you'll need to investigate the IF syntax that the SQL Server ODBC driver requires. It's probably something subtle like a missing space, or the use of single quotes instead of double quotes, or some missing brackets...

                Added: By the way, some of your code examples show a leading space before ' CANALETA' and some do not. Which is correct?

                -- Eric
                Last edited by Eric Pearson; 23 May 2008, 10:02 AM.
                "Not my circus, not my monkeys."

                Comment

                Working...
                X