Accessing output params of Stored procedures from Delphi
ns_dk...@ns_warrenrogersassociates.com
(Transact SQL dialect, I believe is what it's called) running in ASA 9,
and it works fine except when I try to access the return value from my
Delphi program, which is using the TAdoStoredProcedure component. The
Delphi program needs to be able to run against both the original SQL
Server db, and against my 9.0.2 db, so I have to keep the two versions
of the SP call-compatible.
The SP looks like this:
ALTER PROCEDURE "DBA"."stp_WRAInsertEvent" (
@storeNbr varchar(10),
@productId varchar(8),
@vrChannel int,
@tankNbr int,
@eventType int,
@eventDescription varchar(50),
@statusCode int,
@lossRate int,
@cumulativeLossRate int,
@eventDate datetime,
@observationDate datetime,
@notes varchar(500) ,
@EventId int output
)
AS
BEGIN
(do some processing)
set @rtnval = @EventId
COMMIT TRAN -- No Errors, so go ahead
RETURN @rtnval
END
In Delphi, I use the CreateParameter method with the appropriate
direction (pdInout, pdOutput, pdReturnValue) The problem I'm having is
that I can get either the Returned value, OR the output parameter
(@EventId) in the program, but not both. I've tried creating the
pdReturnValue parameter at the beginning of the parameter list, and at
the end:
sptest.Connection := adoconnection1;
sptest.ProcedureName := 'stp_WRAInsertEvent';
sptest.Parameters.Clear;
sptest.parameters.CreateParameter('@storeNbr', ftString, pdInput, 10,
'999' );
sptest.parameters.CreateParameter('@productId', ftString, pdInput, 8,
'04' );
sptest.parameters.CreateParameter('@vrChannel', ftInteger, pdInput,
4, 1 );
sptest.parameters.CreateParameter('@tankNbr', ftinteger, pdInput, 4,
1 );
sptest.parameters.CreateParameter('@eventType', ftInteger, pdInput,
4, 1 );
sptest.parameters.CreateParameter('@eventDescription', ftString,
pdInput, 50, 'Test event' );
sptest.parameters.CreateParameter('@statusCode', ftInteger, pdInput,
4, 1 );
sptest.parameters.CreateParameter('@lossRate', ftInteger, pdInput, 4,
1 );
sptest.parameters.CreateParameter('@cumulativeLossRate', ftInteger,
pdInput, 4, 1 );
sptest.parameters.CreateParameter('@eventDate', ftstring, pdInput,
19, '2007-08-21 12:34:56' );
sptest.parameters.CreateParameter('@observationDate', ftstring,
pdInput, 19, '2007-08-21 14:56:01' );
sptest.parameters.CreateParameter('@notes', ftString, pdInput, 500,
'Long notes field' );
sptest.parameters.CreateParameter('@RETURN_VALUE', ftInteger,
pdReturnValue, 0, 0 );
sptest.parameters.CreateParameter('@EventId', ftInteger, pdOutput, 4,
0 );
spTest.ExecProc;
id := spTest.Parameters.ParamByName('@EventId').Value;
tmpStr := Format('Event key: %d', [id]);
ShowMessage(tmpstr);
rv := sptest.parameters.ParamByname( '@RETURN_VALUE' ).value;
tmpStr := Format('RtnVal: %d', [rv] );
ShowMessage(tmpstr);
With the code arranged as above, I get the correct number for
RETURN_VALUE, but zero for EventId; if I switch the order of the last
two parameters, then EventId is correct and RETURN_VALUE is zero. If I
move RETURN_VALUE to the beginning of the list (as SQL server wants it),
I errors about not being able to convert strings to integers when i try
to execute the procedure, so the parameters aren't getting to the
correct fields.
Has anybody worked with this stuff in Delphi, who could give me some
suggestions? Or is there something fundamental that prevents me from
getting both an output parameter and a return code from the same stored
proc?
--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).
Martin Baur
> I have a stored procedure copied and translated from MS SQL server
> (Transact SQL dialect, I believe is what it's called) running in ASA 9,
> and it works fine except when I try to access the return value from my
> Delphi program, which is using the TAdoStoredProcedure component. The
> Delphi program needs to be able to run against both the original SQL
> Server db, and against my 9.0.2 db, so I have to keep the two versions
> of the SP call-compatible.
Have you tried dbExpress as interface? There is no need for ADO to access ASA from Delphi.
Alternatively but not for free, NativeDB. Whenever I develop something for business use, I rely on this interface. For quick and dirty I use dbExpress ...
Martin Baur
MindPower.com, IT-Services, Switzerland
ns_dk...@ns_warrenrogersassociates.com
ti...@mindpower.com says...
> In article <MPG.214f4fb0f...@forums.sybase.com>, ns_dkerber@ns_WarrenRogersAssociates.com says...
> > I have a stored procedure copied and translated from MS SQL server
> > (Transact SQL dialect, I believe is what it's called) running in ASA 9,
> > and it works fine except when I try to access the return value from my
> > Delphi program, which is using the TAdoStoredProcedure component. The
> > Delphi program needs to be able to run against both the original SQL
> > Server db, and against my 9.0.2 db, so I have to keep the two versions
> > of the SP call-compatible.
>
>
> Have you tried dbExpress as interface? There is no need for ADO to access ASA from Delphi.
We didn't know which interface to use when developing this; we always
used to use the BDE, but couldn't get that to work with SQL Server.
Does dbExpress work with both ASA and M$ SQL server? For this
application, we need to access both with the same code base.
>
> Alternatively but not for free, NativeDB. Whenever I develop something for business use, I rely on this interface. For quick and dirty I use dbExpress ...
>
>
> Martin Baur
>
> MindPower.com, IT-Services, Switzerland
>
--
Paul Horan[TeamSybase]
would appear that they're the same value.
Output parms are easy - you just pass that particular argument "by
reference" to the SP call.
As for return values, I've never seen the purpose of declaring a return
value from a stored procedure - just declare a result set and return the
value with a SELECT statement...
Paul Horan[TeamSybase]
<ns_dkerber@ns_WarrenRogersAssociates.com> wrote in message
news:MPG.214f4fb0f...@forums.sybase.com...
ns_dk...@ns_warrenrogersassociates.com
AT sybase DOT com> says...
> I'm not sure why you have both an output parm AND a return value, when it
> would appear that they're the same value.
In this particular SP they are the same, but there are others for the
same app where they are not the same value.
>
> Output parms are easy - you just pass that particular argument "by
> reference" to the SP call.
We haven't had any trouble getting the output parameter working; it's
the return value that we can't seem to get working with Delphi's
TAdoStoredProcedure object.
> As for return values, I've never seen the purpose of declaring a return
> value from a stored procedure - just declare a result set and return the
> value with a SELECT statement...
I've wondered the same thing, but these SP's were defined by the
customer, and we have to live with them as they are :-(
Does ASA using Transact-SQL expect the return value to be the first
parameter defined by the calling application? SQL server seems to...
--
Paul Horan[TeamSybase]
set row-by-row until the end-of-cursor flag is encountered, then you do
another FETCH to get the return value.
I don't typically have to bother with any of that, because I use
PowerBuilder datawindows. Maybe a Delphi newsgroup would be able to help
with that tool's deficiencies... <G>
Paul Horan[TeamSybase]
<ns_dkerber@ns_WarrenRogersAssociates.com> wrote in message
news:MPG.2151c442e...@forums.sybase.com...
Kornilov Alexey
set/return value and output parameters in one call. Only ASA EmbedSQL work
OK for that kind of SP..
I post message about this "bug" about 1 year ago, but nothing had changed.
MSSQL drivers support SP with out parameters and result sets OK..
"Paul Horan[TeamSybase]" <phoran AT sybase DOT com> wrote in message
news:46e8ddaf$1@forums-1-dub...
ns_dk...@ns_warrenrogersassociates.com
> ASA9 ODBC/ADO/OLEDB drivers don't properly support SP which return result
> set/return value and output parameters in one call. Only ASA EmbedSQL work
> OK for that kind of SP..
> I post message about this "bug" about 1 year ago, but nothing had changed.
> MSSQL drivers support SP with out parameters and result sets OK..
Thank you!
...