Stored procedure with SQLTools

[MARCO RUIZ VENTURA]

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

[Michael Mattias]

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

[Edwin Knoppert]

Afaik the sp's result is a table 'variable'.

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

[Eric Pearson]

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

[MARCO RUIZ VENTURA]

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:

  
  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

[Eric Pearson]

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

[MARCO RUIZ VENTURA]

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.

[Eric Pearson]

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