Fail to run a SQL Server stored procedure'

[ivano_osti]

Hi, it's the first time I need to run a SQL Server stored procedure.

The SP returns an int.

The problem is that I'm not having success to run the SP and get the return value.

If you have a sample, please send me.

The sample that I'm trying is attached. Please, read "Application Description".

Thanks in advance.

PS: I use CTD1.5.1-ptf6

[UVS]

No attachment.....

But I will assume that you read the Books Online for working with Stored Procedures on MS SQL Server.
Chapter 3 of Connecting Gupta Objects to Databases.

Post your attachment and we should be able to help.

Martin

[ivano_osti]

<<No attachment.....>>
Now I could see the error "The extension apt is not allowed" uploading the file again.
I think I'm going crazy... I was sure that I posted the attachment... Here it goes...

<<But I will assume that you read the Books Online>>
No. But I'm using odbsal32.apl and Odr...() functions... I read the Odr´s help... I'll read the books online to see if I find the answer to my problem but I would appreciate a lot a sample if possible...

Thank you for the answer.

[MartinD]

Add the bolded line to your code and see if that works for you:

If NOT OdrPrepareProc( hSql, sComand, sInto )

• Call SalMessageBox( 'OdrPrepareProc() failed', 'Err', MB_Ok )
  Call SalQuit( )

Call SqlExecute( hSql )
Call SqlFetchNext( hSql, nFetch)
Call SqlCommit( hSql )


Martin

[ivano_osti]

Hi Martin.

Excuse-me for so long time for the answer.

I had no success with your suggestion. Please see attachment.

Thank you for your effort.

[MartinD]

Howdy Ivano,

It has been busy here...
Ok, looked at your code some more and got it working here.

The first thing I did was to rename your StringInto variable. Kind of confusing when your TD code uses the same variable names as the Stored Procedure.

Second I changed your code calling the stored procedure. In the Application Description you show the way to call the stored procedure from Query Analyzer. Yet you use different code in TD to call it.

In Query Analyzer:
declare @nRet int
exec SPR_TEST 3,5, @nRet OUTPUT
select 'Value returned: ' = @nRet

as opposed to your TD code:
{call SPR_TEST(:nVar1, :nVar2)}

So your query string (sCommand) should be the same as you run in Query Analyzer:
declare @nRet int
exec SPR_TEST :nVar1, :nVar2, @nReturn = @nRet OUTPUT
select \'Value returned: \' = @nRet

So the problem was you were not using the proper syntax for calling the stored procedure to start with (no return varible). Not sure where you got that "call" part from unless it is used with another database.

I'll attach a working modification of your code to this mesasge. You should only have to change the database name and the sa password to work on your system.

Martin

[ivano_osti]

Hi Martin.

Thank you a lot for the reply.

It'll help me a lot!

Count on me when you need.

<<where you got that "call" part>>
forum.td.development - Subject: Stored Proc with SQLServer (TD 1.51)
Hi
Using {call procname (params)} or "execute procname params" works fine for
us one think you should care about - not bind vars in odrexecuteproc ie:
odrexecuteproc ("{cal procname(:param1)}", ":ret1, :ret" ) - doesnot work
but odrexecuteproc("{cal procname("
|| param1|| ")}", ":ret1, :ret" works ok
For odrprepareproc you can use bind vars (:param) - do not ask why

There is one more if you connect with oledb you should use SalPrepareSP
instead

best regards
Kris

[kolinek]

I want only add error message to Martin code:

Code: Select all

If NOT OdrPrepareProc( hSql, sComand, sInto )
     Call SalMessageBox( 'OdrPrepareProc() failed', 'Err', MB_Ok )
     Return FALSE
When SqlError
     Set nSqlError = SqlError ( hSql )
     Call SqlGetErrorText ( nSqlError, strErrorText )
     Call SalNumberToStr ( nErrorPos, 0, strErrorPos )
     Call SalMessageBox ( 'SQL Error: ' || strErrorText
               || 'Error Position: ' || strErrorPos, 
               'Invalid SQL', MB_Ok )   
     Return FALSE
Call SqlExecute( hSql )
Call SqlFetchNext( hSql, nFetch) 
Call SqlCommit( hSql )