Getting return value from Sybase via TSQLStoredPRoc

[martinm]

Delphi 6.02
Sybase 12.51

How can I retrieve the single return value from an SP?

I've tried adding ptResult params to the TSLQStoredProc, but no joy.

Also can't seem to get the output params back either.

Ta.

 

[svanels]

I have made the following stored procedure on Interbase, it has 2 input values and 1 output

ALTER PROCEDURE INSERT_SURVEY 
(  IDATE TIMESTAMP, IREMARKS VARCHAR(80))
RETURNS
(  RSNUM INTEGER)
AS
begin
  Insert into SURVEYS(START_DATE,REMARKS) 
  VALUES(:iDATE, :iREMARKS);
  rSNUM =  gen_id(SURVEY_NUMBER_GEN,0);
end

In delphi I simply use a TStoredProc, the ExecProc command will do the rest. Click on the Params properties to see the needed variables.

my code:

begin
  with spInsertSurvey do
  begin
   ParamByName('IDATE').asDateTime := "my Value Date";
   ParamByName('IREMARKS').asString :=' my remarks';
   [i]//input [/i]
   [b]execProc;[/b]
   SurveyNumber := ParamByName('RSNUM').asInteger;
   [i] //output[/i]
  end;
end;

Hope this example will help you out

Steven van Els
SAvanEls@cq-link.sr

 

[martinm]

Thanks Steven - I've got the equivalent for Sybase, but get null back....

A bit baffled now!

 

[Pikkunero]

This is what i do in DB2/400.

CREATE PROCEDURE TEST
IN PARAMETER1 CHAR(10),
IN PARAMETER2 CHAR(10),
INOUT RESPARAM INTEGER
.... etc

And in Delphi i pass 3 parameters to the StoredProc component. ParamType for first and second parameters is ptInput and for the third it is ptInputOutput.

Off course you have to define correct datatypes for the parameters.

StoredProc1.Params[0].AsString := '12';
StoredProc1.Params[1].AsString := 'NAME';
StoredProc1.Params[2].AsInteger := 0;
StoredProc1.ExecProc;

TheStoredProcResult := StoredProc1.Params[2].AsInteger;

Hope this helps.

 

[martinm]

Tried that too!

Code as follows:

CREATE PROCEDURE dbo.mm_spats_test_3 (@retval int output)
AS
BEGIN
select test from test_table
select @retval = -23
return -1
END

// Delphi snippet
spTestdbe.Params.CreateParam(ftInteger, '@retval', ptOutput)
spTestdbe.ExecProc;
showmessage(spTestdbe.Params.ParamByName'@retval').AsString);


Gives a blank showmess., so is presumably null.
I've set the o/p param to a value before execproc, but still get null back.


I also need to get the return value, but am having the same problems.....

 

[martinm]

Well, I've decided that this must be a bug(s) in the dbExpress-Sybase driver or dbExpress stuff, as using the same approach with BDE works fine. BDE doesn't give me the functionality I need tho, but it looks like ADO is the answer....

 

[svanels]

What kind of funcionality you are looking for?

Steven van Els
SAvanEls@cq-link.sr

 

[martinm]

Get the return value, plus any output param values, plus multiple resultsets.

Have decided to use ADO now as it seems to do the job. (Althoug I can't seem to detect any 'RaiseErrors').