Need a hint on stored procedures

[Negator]

Hello friends!
I'm trying to study stored procedures. So I've got such function:
(code entered in Sybase Central, function created successfully)

CREATE FUNCTION "DBA"."TestF"( /* parameters,... */ )
RETURNS integer
BEGIN
DECLARE res integer;
SET res = 14;
RETURN (res);
END


Then I coded in PowerBuilder:

integer Res
DECLARE emp_proc PROCEDURE FOR TestFunk;

EXECUTE emp_Proc;

FETCH emp_Proc INTO :Res;
MessageBox('Result', string(Res))


You may guess that MessageBox shows the zero result. What is my mistake? How to put the result into Res?

Thank you!!!

 

[Negator]

Small mistake:
DECLARE emp_proc PROCEDURE FOR TestF; (not "TestFunk&quot

 

[wsmall73]

I would also add error messaging to look at your transaction object...

IF SQLCA.SQLCODE<> 0 and SQLCA.SQLDBCODE <> 0 THEN
Messagebox(&quot;Error Executing TestFunk&quot;+String(SQLCA.SQLDBCODE),SQLCA.SqlErrText,Exclamation!)

ELSE
FETCH emp_Proc INTO :Res;
END IF


This way you can root database level issues... stored procedures not being permissioned etc.

 

[King30]

Hi!!!:
i got the same problem with this:
My function in Sybase Central is:
create function f_nombre(in rut_cli integer)
returns varchar(20)
begin
declare nom_cli varchar(20);
select nombre into nom_cli
from cliente where
rut=rut_cli;
return(nom_cli)
end

and in PowerBuilder:

string ls_nombre
DECLARE f_nom PROCEDURE FOR f_nombre
rut_cli = 2 using sqlca;
IF SQLCA.SQLCode <> 0 THEN
MessageBox ( "Error", "DECLARE failed" )
RETURN
END IF

EXECUTE f_nom;
FETCH f_nom INTO :ls_nombre;

IF ( SQLCA.SQLCode <> 0 ) and ( SQLCA.SQLCode <> 100 ) THEN
MessageBox ( "Error", "EXECUTE failed"+' '+SQLCA.SqlErrText )
END IF
CLOSE f_nom;
MessageBox("Resultado",ls_nombre)

and the "Error" is "Cursor is not Open"

Please, Could you Help me?

Thanks in Advice

 

[mbalent]

One easy way to use stored procs which return results from within PB is to use a datastore.

Set up a datawindow object using the stored procedure as the datasource. You execute a retrieve (passing the appropriate parameters if needed) and the results are returned into the datawindow.