Board index » delphi » Problem with SQL Server 2000 stored procedure with Output Parameters

Problem with SQL Server 2000 stored procedure with Output Parameters

Hi All,

I'm testing dbExpress in Delphi 7 to find out if it's the right choice to
move my applications from BDE. Thanks to Thomas Miller I've been able to
solve my first problem (getting the metadata).

Now I run a stored procedure with output parameters but it's not working
properly. The problem seems to be the way dbExpress uses to send the
parameters, consider this test:

I run a stored procedure MyStoredProcedure which expects 3 parameters:
@username, @password, @code. It returns 2 parameters: @MY_RESULT1 and
@MY_RESULT2.

1. Run stored procedure MyStoredProcedure using dbExpress: Using SQL
Profiler, I could see this trace:

declare @P1 tinyint
set @P1=1
declare @P2 varchar(5)
set @P2=NULL

exec sp_prepexecrpc @username = 'pr

This returns wrong results to my client application. Note that "exec
sp_prepexecrpc @username = 'pr" is truncated (SQL Profiler showed me just
that text), I don't know what it does neither I see a call to
MyStoredProcedure anywhere (where is the call?), and I can't see the other
two input parameters.

2. Run stored procedure MyStoredProcedure using BDE, and passing the same
parameters than 1: In SQL Profiler, I could see this trace:

declare @P1 smallint
set @P1=0
declare @P2 varchar(255)
set @P2='GP001'
exec dbo.MyStoredProcedure @username = 'pr', @password = '123', @code = '6',
@MY_RESULT1 = @P1 output, @MY_RESULT2 = @P2 output
select @P1, @P2

This returns the right results.

What can be wrong? Any ideas?

Thank you for your help.
________________________
Arturo Martnez Mardones

 

Re:Problem with SQL Server 2000 stored procedure with Output Parameters


Hi All,

I'm testing dbExpress in Delphi 7 to find out if it's the right choice to
move my applications from BDE. Thanks to Thomas Miller I've been able to
solve my first problem (getting the metadata).

Now I run a stored procedure with output parameters but it's not working
properly. The problem seems to be the way dbExpress uses to send the
parameters, consider this test:

I run a stored procedure MyStoredProcedure which expects 3 parameters:
@username, @password, @code. It returns 2 parameters: @MY_RESULT1 and
@MY_RESULT2.

1. Run stored procedure MyStoredProcedure using dbExpress: Using SQL
Profiler, I could see this trace:

declare @P1 tinyint
set @P1=1
declare @P2 varchar(5)
set @P2=NULL

exec sp_prepexecrpc @username = 'pr

This returns wrong results to my client application. Note that "exec
sp_prepexecrpc @username = 'pr" is truncated (SQL Profiler showed me just
that text), I don't know what it does neither I see a call to
MyStoredProcedure anywhere (where is the call?), and I can't see the other
two input parameters.

2. Run stored procedure MyStoredProcedure using BDE, and passing the same
parameters than 1: In SQL Profiler, I could see this trace:

declare @P1 smallint
set @P1=0
declare @P2 varchar(255)
set @P2='GP001'
exec dbo.MyStoredProcedure @username = 'pr', @password = '123', @code = '6',
@MY_RESULT1 = @P1 output, @MY_RESULT2 = @P2 output
select @P1, @P2

This returns the right results.

What can be wrong? Any ideas?

Thank you for your help.
________________________
Arturo Martnez Mardones

Re:Problem with SQL Server 2000 stored procedure with Output Parameters


I am surprised that you execute the stored procedure at all. I can't till
now.
I am crying ....

Re:Problem with SQL Server 2000 stored procedure with Output Parameters


Set SchemaName to dbo so that you can select from  names at design time. It
will decide your problem.

Quote
"Svilen Stefanov" <sno...@revolta.com> wrote in message

news:3e04ab30@newsgroups.borland.com...
Quote
> I am surprised that you execute the stored procedure at all. I can't till
> now.
> I am crying ....

Other Threads