Board index » delphi » MS SQL 6.5,Delphi 2,Stored Procedures

MS SQL 6.5,Delphi 2,Stored Procedures

Subject: MS SQL 6.5,Delphi 2,Stored Procedures

When I used the TStoredProc component in Delphi 2.0 and point it to
a stored procedure on the SQL Server it creates an extra parameter
called
RETURN_VALUE with no parameter type.  When I try to execute the
procedure I
get an error message saying parameter return_value has no parameter
type.
No matter what type i choose or what value i return from the procedure I
can't get it to execute

What am i doing wrong here???

Kyle
k...@securedoc.com

 

Re:MS SQL 6.5,Delphi 2,Stored Procedures


On Mon, 30 Sep 1996 16:07:41 -0400, "Alan Gordie (Viper)"

Quote
<vi...@jax-inter.net> wrote:
>Subject: MS SQL 6.5,Delphi 2,Stored Procedures

>When I used the TStoredProc component in Delphi 2.0 and point it to
>a stored procedure on the SQL Server it creates an extra parameter
>called
>RETURN_VALUE with no parameter type.  When I try to execute the
>procedure I
>get an error message saying parameter return_value has no parameter
>type.
>No matter what type i choose or what value i return from the procedure I
>can't get it to execute

>What am i doing wrong here???

Are you trying to use a stored procedure with no parameters?  I
remember some sort of bug where it insists on having something set,
when there is nothing to set in the first place.  Unfortunately, i
don't remember the solution.  You might want to check Dejanews and see
if you can spot something on it.

                                                James

Re:MS SQL 6.5,Delphi 2,Stored Procedures


Make sure 'Result'is set to OUTPUT. And I believe it is parameter 0.
All the user create parameter start as parameter 1

HTH

-Tommy <8-)

Re:MS SQL 6.5,Delphi 2,Stored Procedures


Quote
Alan Gordie (Viper) wrote:

> Subject: MS SQL 6.5,Delphi 2,Stored Procedures

> When I used the TStoredProc component in Delphi 2.0 and point it to
> a stored procedure on the SQL Server it creates an extra parameter
> called
> RETURN_VALUE with no parameter type.  When I try to execute the
> procedure I
> get an error message saying parameter return_value has no parameter
> type.
> No matter what type i choose or what value i return from the procedure I
> can't get it to execute

> What am i doing wrong here???

> Kyle
> k...@securedoc.com

Kule,

You can't avoid having a return (Output) parameter with your stores
procedures
as defined in Delphi. From my experience the following is the proper way
of
executing a stored procedure with code:

  with myStoredProcedure do
  begin
    Close;
    // this is the name of the proc. on the server
    StoredProcName := 'DO_Something';
    Params.Clear;
    // This is were you define the output parameter. (You have to do
this or
    // the TStoredProc won't work)!!.
    Params.CreateParam(ftInteger, 'RETURN_VALUE', ptOutput);
    // Define any other input parameters you might have
    Params.CreateParam(ftInteger, 'MyNumber', ptInput);
    Params.CreateParam(ftDateTime, 'MyDate', ptInput);
    Prepare;
    Params[1].AsInteger := YourValue;
    Params[2].AsString := FormatDateTime('mm/dd/yy',Now);
    try
    begin
      // Use "open" for procedures that return results and "ExecProc"
for the others
      Open;
      if not EOF then
        result := giSUCCESS
      else
        result := giEMPTY;
    end
    except
      on E:EDBEngineError do
      begin
        MessageDlg(E.Message, mtError, [mbOK], 0);
        result := giFAILURE;
      end;
    end;
  end;
  // The above is just an example of using the TStoreProc component with
MS SQL 6.x and Delphi.

Hope this helps

Nick Liristis.

Re:MS SQL 6.5,Delphi 2,Stored Procedures


The RETURN_VALUE was a notorious bug with MS SQL Server and ODBC - it
was never an issue with the SQL Links MSSQL driver.

Happily, the problem has disappeared with the Delphi 2.01 upgrade - no
need to jump through hoops in your  code any longer ...

HTH,
Steve

Quote
Nick Liristis <liris...@sprynet.com> wrote:
>Alan Gordie (Viper) wrote:

>> Subject: MS SQL 6.5,Delphi 2,Stored Procedures

>> When I used the TStoredProc component in Delphi 2.0 and point it to
>> a stored procedure on the SQL Server it creates an extra parameter
>> called
>> RETURN_VALUE with no parameter type.  When I try to execute the
>> procedure I
>> get an error message saying parameter return_value has no parameter
>> type.
>> No matter what type i choose or what value i return from the procedure I
>> can't get it to execute

>> What am i doing wrong here???

>> Kyle
>> k...@securedoc.com

>Kule,

>You can't avoid having a return (Output) parameter with your stores
>procedures
>as defined in Delphi. From my experience the following is the proper way
>of
>executing a stored procedure with code:

>  with myStoredProcedure do
>  begin
>    Close;
>    // this is the name of the proc. on the server
>    StoredProcName := 'DO_Something';
>    Params.Clear;
>    // This is were you define the output parameter. (You have to do
>this or
>    // the TStoredProc won't work)!!.
>    Params.CreateParam(ftInteger, 'RETURN_VALUE', ptOutput);
>    // Define any other input parameters you might have
>    Params.CreateParam(ftInteger, 'MyNumber', ptInput);
>    Params.CreateParam(ftDateTime, 'MyDate', ptInput);
>    Prepare;
>    Params[1].AsInteger := YourValue;
>    Params[2].AsString := FormatDateTime('mm/dd/yy',Now);
>    try
>    begin
>      // Use "open" for procedures that return results and "ExecProc"
>for the others
>      Open;
>      if not EOF then
>        result := giSUCCESS
>      else
>        result := giEMPTY;
>    end
>    except
>      on E:EDBEngineError do
>      begin
>        MessageDlg(E.Message, mtError, [mbOK], 0);
>        result := giFAILURE;
>      end;
>    end;
>  end;
>  // The above is just an example of using the TStoreProc component with
>MS SQL 6.x and Delphi.

>Hope this helps

>Nick Liristis.

-------------------------------------------------------------
Finger steve.mabb...@pobox.com for phone/fax and PGP key info

Other Threads