| Author |
Message
|
| Mut1ey |
 Posted: Wed Aug 12, 2009 10:49 am Post subject: Return Code from Sybase Stored Procedure |
 |
|
Acolyte
Joined: 07 Oct 2005
Posts: 74
Location: England
|
I am running 6.0.9 on Solaris and calling a stored procedure in Sybase 12.5. I am using the recommended 'CALL' option, rather than PASSTHU.
So it works successfully. Great. It is an update/ insert procedure and all the params are IN.
What I am not clear about is the approach to handling any return codes from the Sybase Proc. I cannot work out how to query this value. It is not a value in the Stored Proc signature on Sybase. The SP does return an Integer 'reason code' (SQL RETURN stmnt). So if I execute the stored proc using a Sybase tool, it works and displays an rc of 0. How do I trap that same rc in Broker?
Does the Sybase stored proc need to be updated with an INOUT param
that holds that rc?
Or am I missing something obvious here? It feels like it....
Thanks in advance. |
|
| Back to top |
|
 |
| mgk |
| Posted: Wed Aug 12, 2009 11:16 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hi,
You can just add a RETURNS INTEGER clause to the CREATE PROCEDURE definition and then use CALL mySPCall() INTO <myIntVariable>; to pick up the return value (or use SET myIntVariable = mySPCall(); syntax)
Regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| Mut1ey |
| Posted: Wed Aug 12, 2009 12:40 pm Post subject: |
|
|
Acolyte
Joined: 07 Oct 2005
Posts: 74
Location: England
|
| mgk wrote: |
Hi,
You can just add a RETURNS INTEGER clause to the CREATE PROCEDURE definition and then use CALL mySPCall() INTO <myIntVariable>; to pick up the return value (or use SET myIntVariable = mySPCall(); syntax)
Regards, |
Thanks, I had already tried that one. But I get a syntax error in the toolkit if I add the RETURNS INTEGER to it. Strange, as it is in the manual, although a bit hard to find. Also tried the SET x = mySP(); but that then complains that there is not RETURNS clause. |
|
| Back to top |
|
|
| mgk |
| Posted: Wed Aug 12, 2009 1:16 pm Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Well, if you post your code that you tried I can verify that it is correct for you. RETURNS INTEGER is the right way to do this, so either you had incorrect syntax or there is a defect in the version of the toolkit you are using. If you think the syntax is correct you can always try an "executable comment" to force the code to be deployed and if it works you can then raise a PMR against the tooling...
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| Mut1ey |
| Posted: Wed Aug 12, 2009 1:25 pm Post subject: |
|
|
Acolyte
Joined: 07 Oct 2005
Posts: 74
Location: England
|
| mgk wrote: |
| Well, if you post your code that you tried I can verify that it is correct for you. RETURNS INTEGER is the right way to do this, so either you had incorrect syntax or there is a defect in the version of the toolkit you are using. If you think the syntax is correct you can always try an "executable comment" to force the code to be deployed and if it works you can then raise a PMR against the tooling... |
I am trying to emulate this on my local machine here, I do not have the actual code with me right now, and I think I have used incorrect semantics. I based the esql create proc on the following example from the docs:
CREATE PROCEDURE myProc1 (IN P1 INT, OUT P2 INT)
LANGUAGE DATABASE
DYNAMIC RESULT SETS x
EXTERNAL NAME "myschema.myproc";
Even though the SP on Sybase does not return a Result set. I thought the result set may be the vehicle for the return code given that I could not find mention of SP and Return code.
When I tried the RETURNS INTEGER I left the LANGUAGE DATABASE in the ESQL. Looking at the other examples, from the same manual page, they do not include the these two lines:
LANGUAGE DATABASE
DYNAMIC RESULT SETS x
I think this may be that "something obvious"? What do you think?
I will give it try anyway locally today and try for real tomorrow. I will post the results. |
|
| Back to top |
|
|
| mgk |
| Posted: Wed Aug 12, 2009 2:38 pm Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Well you do not need the DYNAMIC RESULT SETS clause unless your procedure returns resultsets as well as the integer return code. The LANGUAGE DATABASE clause is just good practice but not required to call a stored procedure as DATABASE is the default if there is no LANGUAGE clause but there is an EXTERNAL NAME. The following should be OK:
CREATE PROCEDURE myProc1 (IN P1 INT, OUT P2 INT)
RETURNS INTEGER
LANGUAGE DATABASE
EXTERNAL NAME "myschema.myproc";
and use the following to call:
CALL myProc1( var1, var2 ) INTO var3;
or
SET var3 = myProc1( var1, var2 ) ;
Regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| Mut1ey |
| Posted: Thu Aug 13, 2009 11:29 am Post subject: |
|
|
Acolyte
Joined: 07 Oct 2005
Posts: 74
Location: England
|
| mgk wrote: |
Well you do not need the DYNAMIC RESULT SETS clause unless your procedure returns resultsets as well as the integer return code. The LANGUAGE DATABASE clause is just good practice but not required to call a stored procedure as DATABASE is the default if there is no LANGUAGE clause but there is an EXTERNAL NAME. The following should be OK:
CREATE PROCEDURE myProc1 (IN P1 INT, OUT P2 INT)
RETURNS INTEGER
LANGUAGE DATABASE
EXTERNAL NAME "myschema.myproc";
and use the following to call:
CALL myProc1( var1, var2 ) INTO var3;
or
SET var3 = myProc1( var1, var2 ) ;
Regards, |
I wasn't able to try on my local machine, so had to wait till today, and indeed it was the LANGUAGE DATABASE that needed to be removed. Once done, I added RETURNS INTEGER and the world is now a better place.  Thanks for your help. Will try with the LANGUAGE DATABASE after the RETURNS INTEGER, as above. It threw the syntax error with it before the RETURNS INTEGER though.
Mutley |
|
| Back to top |
|
|
| mgk |
| Posted: Thu Aug 13, 2009 1:14 pm Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
I'm glad it is working. Please let me know what you find with the LANGUAGE clause. I suspect that you had the LANGUAGE clause before the RETURNS clause which is an error and if you put it after the RETURNS clause it will work fine, as the clauses have to match the order given in the syntax diagram in the infocenter...
Kind Regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| Mut1ey |
| Posted: Tue Aug 18, 2009 6:53 am Post subject: |
|
|
Acolyte
Joined: 07 Oct 2005
Posts: 74
Location: England
|
| mgk wrote: |
I'm glad it is working. Please let me know what you find with the LANGUAGE clause. I suspect that you had the LANGUAGE clause before the RETURNS clause which is an error and if you put it after the RETURNS clause it will work fine, as the clauses have to match the order given in the syntax diagram in the infocenter...
Kind Regards, |
Sorry about delay, MGK. That works with LANGUAGE DATABASE after the RETURNS - no syntax error.
Thanks |
|
| Back to top |
|
|
|
|
