Board index » delphi » Stored Procedure with parameters

Stored Procedure with parameters

Hello,

I try to call a stored procedure providing parameters.
If I do not use parameters, the stored procedure works.
When I add a parameter it fails.

Here is the code:
storedproc1.Params[0].asinteger := 1;
storedproc1.prepare;
storedproc1.execproc;

Error message:
EDBEngineError with message: 'Could not find object'

Stored Procedure:
CREATE PROCEDURE test_date
@@myfield integer
AS
insert into aatest values(@@myfield)
GO

Thanks,
Doru

 

Re:Stored Procedure with parameters


Doru,

   If your using MSSQL, remove the ;1 from the end of the stored procedure
name (in the Object Inspector).

Good luck,
krf

Re:Stored Procedure with parameters


Call Prepare before you set the params.
Quote
Doru Roman wrote:
> Hello,

> I try to call a stored procedure providing parameters.
> If I do not use parameters, the stored procedure works.
> When I add a parameter it fails.

> Here is the code:
> storedproc1.Params[0].asinteger := 1;
> storedproc1.prepare;
> storedproc1.execproc;

> Error message:
> EDBEngineError with message: 'Could not find object'

> Stored Procedure:
> CREATE PROCEDURE test_date
> @@myfield integer
> AS
> insert into aatest values(@@myfield)
> GO

> Thanks,
> Doru

Re:Stored Procedure with parameters


[This followup was posted to borland.public.delphi.database.sqlservers
and a copy was sent to the cited author.]

procedure Form1.RunStoredProcedure;
begin
     with StoredProc1 do begin
          Params.Clear;
          Params.CreateParam(ftInteger, 'myParam', ptInput);
          ParamByName('myParam').AsInteger := 1;
          Prepare;
          DataModule1.Database1.StartTransaction;
          try
            ExecProc;
            DataModule1.Database1.Commit;
          except
            DataModule1.Database1.Rollback;
            ShowMessage('Failed to execute stored procedure.');
          end;
          UnPrepare;
          Close;
     end;
end;

In article <3b968949$1_1@dnews>, Constan...@telehop.com says...

Quote
> Hello,

> I try to call a stored procedure providing parameters.
> If I do not use parameters, the stored procedure works.
> When I add a parameter it fails.

> Here is the code:
> storedproc1.Params[0].asinteger := 1;
> storedproc1.prepare;
> storedproc1.execproc;

> Error message:
> EDBEngineError with message: 'Could not find object'

> Stored Procedure:
> CREATE PROCEDURE test_date
> @@myfield integer
> AS
> insert into aatest values(@@myfield)
> GO

> Thanks,
> Doru

Re:Stored Procedure with parameters


Thank you all for the reply.
I made it work. Using a SP control, it automatically creates a
@@Return_Value
parameter. I removed it and it worked.

The only instance when it does not work is when I use ParmByName to give
values. It is still something to figure out why it fails.

Doru

Re:Stored Procedure with parameters


When you call prepare the client object reads the SP params from the server
and builds the Params list. Then you can call ParamByName(). Before calling
Prepare the Params list is empty so ParamByName can not find a param. It
must exist in the list or it will not be found.

The sample that Matt presented also works, but you have to create the params
manually. I like my method better, but that is all personal preference.

Allen.

Quote
Doru Roman wrote:
> Thank you all for the reply.
> I made it work. Using a SP control, it automatically creates a
> @@Return_Value
> parameter. I removed it and it worked.

> The only instance when it does not work is when I use ParmByName to give
> values. It is still something to figure out why it fails.

> Doru

Re:Stored Procedure with parameters


Allen,

I will give it a try. But if this is the answer, then how come that:
- using a TQuery I can prepare then use the parameters and it works ok.
- the sample in the help on line uses parameters and then prepare.

Thanks,
Doru

G Allen Casteran <al...@nisnic.com> wrote in message
news:3B97AC86.F1DA0BA@nisnic.com...

Quote
> When you call prepare the client object reads the SP params from the
server
> and builds the Params list. Then you can call ParamByName(). Before
calling
> Prepare the Params list is empty so ParamByName can not find a param. It
> must exist in the list or it will not be found.

> The sample that Matt presented also works, but you have to create the
params
> manually. I like my method better, but that is all personal preference.

> Allen.

> Doru Roman wrote:

> > Thank you all for the reply.
> > I made it work. Using a SP control, it automatically creates a
> > @@Return_Value
> > parameter. I removed it and it worked.

> > The only instance when it does not work is when I use ParmByName to give
> > values. It is still something to figure out why it fails.

> > Doru

Re:Stored Procedure with parameters


Sorry I made a mistake:
- using a TQuery I can use the parameters then  prepare and it works ok

Re:Stored Procedure with parameters


I don't have a good explanation for that. The documentation seemed to contradict
my experience. Calling Prepare and then setting the params just seems to work in
all cases for me.
Quote
Doru Roman wrote:
> Allen,

> I will give it a try. But if this is the answer, then how come that:
> - using a TQuery I can prepare then use the parameters and it works ok.
> - the sample in the help on line uses parameters and then prepare.

> Thanks,
> Doru

> G Allen Casteran <al...@nisnic.com> wrote in message
> news:3B97AC86.F1DA0BA@nisnic.com...
> > When you call prepare the client object reads the SP params from the
> server
> > and builds the Params list. Then you can call ParamByName(). Before
> calling
> > Prepare the Params list is empty so ParamByName can not find a param. It
> > must exist in the list or it will not be found.

> > The sample that Matt presented also works, but you have to create the
> params
> > manually. I like my method better, but that is all personal preference.

> > Allen.

> > Doru Roman wrote:

> > > Thank you all for the reply.
> > > I made it work. Using a SP control, it automatically creates a
> > > @@Return_Value
> > > parameter. I removed it and it worked.

> > > The only instance when it does not work is when I use ParmByName to give
> > > values. It is still something to figure out why it fails.

> > > Doru

Re:Stored Procedure with parameters


Hi Allen,

I tried to prepare before setting the parameter and I still get the error.
It says that parameter 'myfield' is not found.

Here is the procedure:
CREATE PROCEDURE test_date
@@myfield datetime
AS
insert into aatest values(@@myfield)

And here is how I call it:

storedproc1.prepare;
storedproc1.ParamByName('myfield').asdatetime :=now();
storedproc1.execproc;

Thanks,
Doru

Re:Stored Procedure with parameters


Doru,

   The error is correct, the parameter is '@@myfield', you are setting
'myfield'.

Good luck,
krf

Quote
Doru Roman <Constan...@telehop.com> wrote in message

news:3b98baf2_1@dnews...
Quote
> Hi Allen,

> I tried to prepare before setting the parameter and I still get the error.
> It says that parameter 'myfield' is not found.

> Here is the procedure:
> CREATE PROCEDURE test_date
> @@myfield datetime
> AS
> insert into aatest values(@@myfield)

> And here is how I call it:

> storedproc1.prepare;
> storedproc1.ParamByName('myfield').asdatetime :=now();
> storedproc1.execproc;

> Thanks,
> Doru

Re:Stored Procedure with parameters


That did the trick.

Thanks,
Doru

Quote
Kevin Frevert <kfrev...@midwayusa.com> wrote in message

news:3b98c26d$1_1@dnews...
Quote
> Doru,

>    The error is correct, the parameter is '@@myfield', you are setting
> 'myfield'.

> Good luck,
> krf

> Doru Roman <Constan...@telehop.com> wrote in message
> news:3b98baf2_1@dnews...
> > Hi Allen,

> > I tried to prepare before setting the parameter and I still get the
error.
> > It says that parameter 'myfield' is not found.

> > Here is the procedure:
> > CREATE PROCEDURE test_date
> > @@myfield datetime
> > AS
> > insert into aatest values(@@myfield)

> > And here is how I call it:

> > storedproc1.prepare;
> > storedproc1.ParamByName('myfield').asdatetime :=now();
> > storedproc1.execproc;

> > Thanks,
> > Doru

Other Threads