Board index » delphi » SQL Server 7 Stored Proc names in Delphi 4 C/S

SQL Server 7 Stored Proc names in Delphi 4 C/S

I put a TStoredProc component on my form. After setting the DatabaseName, I
can see a list of stored procedures that seems to match the list on the
server (what I see in either SQL Server Enterprise Mgr or SQL Explorer).
However, those names (in the object inspector) are followed by a semi-colon
and the number 1 (dbo.storedprocname;1). When I open the TStoredProc object,
I get the following message :

" Could not find stored procedure 'dbo.storedprocname;1 ' .. "

If I get rid of the " ;1 ", then I get this error message :

" Error creating cursor handle "

BTW, the procedure I wrote has 2 input parameters and one output parameter.
It therefore returns a single value. It runs fine in both SQL Explorer or
Query Analyzer.

Thanks.

--

Alain Quesnel
alainnos...@argosoftware.com

 

Re:SQL Server 7 Stored Proc names in Delphi 4 C/S


I don't know why Delphi puts the ';1' on the name, but I've found that you
need to remove that.

As for the cursor handle error, your stored procedure returns a parameter to
your application, but it does not actually produce a "result set" of data.
This means that you must "execute" the stored procedure rather than
"opening" it.

When you set the Active property to True in the stored procedure object, you
are in fact "opening" the stored procedure and delphi then expects a result
set (called a "cursor") of data to be available (so you can display it in a
grid or whatever). As this cursor is not available, you get the message
"Error creating cursor handle".

So in other words, if your stored procedure does not produce a "result set"
(ie. the results of a select statement) then you cannot open it in Delphi's
development environment.

I hope this all makes sense to you.

Mark.

Re:SQL Server 7 Stored Proc names in Delphi 4 C/S


Quote
Alain Quesnel wrote:

> I put a TStoredProc component on my form. After setting the DatabaseName, I
> can see a list of stored procedures that seems to match the list on the
> server (what I see in either SQL Server Enterprise Mgr or SQL Explorer).
> However, those names (in the object inspector) are followed by a semi-colon
> and the number 1 (dbo.storedprocname;1). When I open the TStoredProc object,
> I get the following message :

> " Could not find stored procedure 'dbo.storedprocname;1 ' .. "

> If I get rid of the " ;1 ", then I get this error message :

Yep, that's a bug somewhere, and removing the ";1" is the workaround for
it.

Quote

> " Error creating cursor handle "

> BTW, the procedure I wrote has 2 input parameters and one output parameter.
> It therefore returns a single value. It runs fine in both SQL Explorer or
> Query Analyzer.

It sounds like the result is being returned through the parameters, and
not through an actual result set. In this case, you need to call
TStoredProc.ExecProc instead of TStoredProc.Open (the error listed means
that a result set wasn't returned).  You need to set up your parameters
correctly to ensure that the one parameter is an output parameter, then
after calling ExecProc you'll need to interrogate the parameter with
TStoredProc.ParamByname('xxx').AsXXX.

HTH,
Wayne
--
Bogus email address - use wgshef instead, keep the domain.

Re:SQL Server 7 Stored Proc names in Delphi 4 C/S


And removing the ;1 will cause all of your old paramaters to go away.
View the datamodule as text, remove the ;1's in there and you will not
lose your parameters.

Re:SQL Server 7 Stored Proc names in Delphi 4 C/S


Thank you Bradford and Wayne. Bradford's solution did the trick. BTW, I was
calling ExecProc.

--

Alain Quesnel
alainnos...@argosoftware.com

Quote
Bradford C. Miller wrote in message

<3846992A.4D53C...@spicedhamawayaol.com>...
Quote
>And removing the ;1 will cause all of your old paramaters to go away.
>View the datamodule as text, remove the ;1's in there and you will not
>lose your parameters.

Re:SQL Server 7 Stored Proc names in Delphi 4 C/S


let me post my code here I am getting the same error but mine is ISAPI
app.  I am creating everything dynamically

  sUserName := csUser_Name;
  dbLocal := TDataBase.Create(nil);

 //prepare the Database;
  with dbLocal do
  begin
     AliasName         := 'WebDBODBC';
     DataBaseName      :=  'dbLocal';
     LoginPrompt       := False;
     Params.Add('USER NAME=xxxxx');
     Params.Add('PASSWORD=xxxxx');
     Connected         := True;
  end;

  spLocal := TStoredProc.Create(nil);

  with spLocal do
  begin
    DataBaseName       := dbLocal.DataBaseName;
    StoredProcName     := 'sp_update_tblLogin_Status_1;1';
    close;
//    Params[1].AsString := sUserName;
//    Params[2].AsInteger := 54;
      ParamByName('@sUser_Name_1').AsString := sUserName;
      ParamByName('@mystage').AsInteger := 54

    Prepare;
 end;

my error
Internal Server Error 500

Exception: EDatabaseError
Message: Parameter '@sUser_Name_1' not found

* Sent from RemarQ http://www.remarq.com The Internet's Discussion Network *
The fastest and easiest way to search and participate in Usenet - Free!

Other Threads