Board index » delphi » Getting MSSQL 7 Stored Proc output parameters

Getting MSSQL 7 Stored Proc output parameters

Hello,

I have a Delphi 5 program which is connected to a SQL Server 7 (MSDE)
database through the standard SQL Links.
I have written a stored proc that has only output parameters. Its definition
is :
CREATE PROCEDURE GENERE_EXPORT @Num_export INT OUTPUT, @DateHeure DATETIME
OUTPUT, @Tot_ref INT OUTPUT, @Tot_moules_casses INT OUTPUT,
@Tot_moules_entres INT OUTPUT AS
.....

When I run this procedure from the SQL Server query analyzer, I get the
right results.

In my Delphi code, I use a TStoredProc component.
On the TStoredProc.Open instruction, I get an EDatabaseError exception with
the message "no type for the '@Num_export' parameter" (or something like
that since I have translated it from French to English).
I have set the @Num_export DataType property to ftInteger but it still
doesn't work.

Althoug it works fine with ADO components, I wouldn't like to use those
components since I have to rewrite a large part of my code.

Could someone help me ?

Thanks in advance.

Best Regards.

Alain FAJNER

 

Re:Getting MSSQL 7 Stored Proc output parameters


On Tue, 13 Nov 2001 12:00:14 +0100, "Alain FAJNER" <afaj...@yahoo.fr>
wrote:

Quote
>I have a Delphi 5 program which is connected to a SQL Server 7 (MSDE)
>database through the standard SQL Links.
>I have written a stored proc that has only output parameters. Its definition
>is :
>CREATE PROCEDURE GENERE_EXPORT @Num_export INT OUTPUT, @DateHeure DATETIME
>OUTPUT, @Tot_ref INT OUTPUT, @Tot_moules_casses INT OUTPUT,
>@Tot_moules_entres INT OUTPUT AS

>When I run this procedure from the SQL Server query analyzer, I get the
>right results.

>In my Delphi code, I use a TStoredProc component.
>On the TStoredProc.Open instruction, I get an EDatabaseError exception with
>the message "no type for the '@Num_export' parameter" (or something like
>that since I have translated it from French to English).

First of all, since your stored procs doesn't return a result set, do
not use the "Open" method, but rather use the "ExecProc" call instead.

Quote
>Althoug it works fine with ADO components, I wouldn't like to use those
>components since I have to rewrite a large part of my code.

If you want to do anything that has been introduced in SQL Server 7 or
2000 (such as text fields longer than 255 chars, any of the newer
datatypes etc.), you will HAVE TO switch to ADO. It is the preferred
and supported way to access SQL Server after v6.5 - sooner or later,
it will be the only way to do so...

Marc

------------------------------------------------------------------------
Marc Scheuner                                          Software Engineer
Quest Software Canada                       Halifax, Nova Scotia, Canada
Email: marc.scheu...@quest.com                      http://www.quest.com

Re:Getting MSSQL 7 Stored Proc output parameters


Thank you very much for your quick answer.

I have also tried to call the ExecProc method but it also failed.
I've now tried to "prepare" my stored procedure in the FormShow event code
and now it fails when it calls the prepare method.
The only way that I've found to bypass this problem is to create the same
procedure with no arguments and to store the output in another database
table. Then I'm obliged to run a new query to retrieve the results.

Best Regards.

Alain FAJNER

"Marc Scheuner" <mscheu...@no.spam.for.me.ca> a crit dans le message news:
iu82vtk2po9rs6t7ail9u9mhqei76io...@4ax.com...

Quote
> On Tue, 13 Nov 2001 12:00:14 +0100, "Alain FAJNER" <afaj...@yahoo.fr>
> wrote:
> >I have a Delphi 5 program which is connected to a SQL Server 7 (MSDE)
> >database through the standard SQL Links.
> >I have written a stored proc that has only output parameters. Its
definition
> >is :
> >CREATE PROCEDURE GENERE_EXPORT @Num_export INT OUTPUT, @DateHeure
DATETIME
> >OUTPUT, @Tot_ref INT OUTPUT, @Tot_moules_casses INT OUTPUT,
> >@Tot_moules_entres INT OUTPUT AS

> >When I run this procedure from the SQL Server query analyzer, I get the
> >right results.

> >In my Delphi code, I use a TStoredProc component.
> >On the TStoredProc.Open instruction, I get an EDatabaseError exception
with
> >the message "no type for the '@Num_export' parameter" (or something like
> >that since I have translated it from French to English).

> First of all, since your stored procs doesn't return a result set, do
> not use the "Open" method, but rather use the "ExecProc" call instead.

> >Althoug it works fine with ADO components, I wouldn't like to use those
> >components since I have to rewrite a large part of my code.

> If you want to do anything that has been introduced in SQL Server 7 or
> 2000 (such as text fields longer than 255 chars, any of the newer
> datatypes etc.), you will HAVE TO switch to ADO. It is the preferred
> and supported way to access SQL Server after v6.5 - sooner or later,
> it will be the only way to do so...

> Marc

> ------------------------------------------------------------------------
> Marc Scheuner                                          Software Engineer
> Quest Software Canada                       Halifax, Nova Scotia, Canada
> Email: marc.scheu...@quest.com                      http://www.quest.com

Re:Getting MSSQL 7 Stored Proc output parameters


On Tue, 13 Nov 2001 15:11:22 +0100, "Alain FAJNER" <afaj...@yahoo.fr>
wrote:

Quote
>I have also tried to call the ExecProc method but it also failed.

I tried the same thing here - I did up a fake stored proc with the
same name and the same parameter list that you had mentioned, and
tried to execute it.

I got the same error that you did.

The problem is this: even though the SQL Server stored procedure
defines all parameters as "OUTPUT", Delphi makes them into
"ptInputOutput" parameters - like a VAR parameter for a Pascal
procedure. In this case, it would expect some input values going into
the stored procedure call.

If you change all the OUTPUT stored proc parameters to strictly
ptOutput (their "Direction" property) in the Parameters editor on the
TADOStoredProc object on your form, and then run your code, it works
just fine!

HTH
Marc

------------------------------------------------------------------------
Marc Scheuner                                          Software Engineer
Quest Software Canada                       Halifax, Nova Scotia, Canada
Email: marc.scheu...@quest.com                      http://www.quest.com

Other Threads