Board index » delphi » Dynamic TStoredProc @ RunTime

Dynamic TStoredProc @ RunTime

I am trying to dynamically create and use a TStoredProc at runtime.
Following is the code I am using:

procedure SomeForm.SomeEvent;
var
  ApproveProc   : TStoredProc;
  ProcParam     : TParam;
begin
  try
    ApproveProc := TStoredProc.Create(Application);
    ProcParam := TParam.Create(ApproveProc.Params, ptInput);
    ProcParam.DataType := ftInteger;
    ProcParam.Name := 'SOME_ID';
    ProcParam.AsInteger := Query_Some_TableSOME_ID.AsInteger;
    ApproveProc.DatabaseName := 'SOME_DB';
    ApproveProc.StoredProcName := 'SOMEOWNER.APPROVE_ITEM';
    ApproveProc.Prepare;
    ApproveProc.ExecProc;
  finally
    ProcParam.Free;
    ApproveProc.Free;
  end;
end;

I get two exceptions: one from Oracle about having the wrong number of
parameters or the wrong types.  Then I get a GPF.

Here is the kicker:
I put a TStoredProc on my form, and set DatabaseName = 'SOME_DB',
StoredProcName = 'SOMEOWNER.APPROVE_ITEM', and added an Integer
parameter by the name of SOME_ID.  Then I replaced the above code
with:

  ApproveProc.ParamByName('SOME_ID').AsInteger :=
                                Query_Some_TableSOME_ID.AsInteger;
  ApproveProc.ExecProc;

this works wonderfully.

Does anybody know what is going on here?  Where did I{*word*222}up?

BTW, I'm using Delphi 1.0 against an Oracle 7 Server.

Thank you all so much!

- Rick
hammel_r...@promina.org

 

Re:Dynamic TStoredProc @ RunTime


Rick,

I've had some success with this approach using SQL Server in the past.
It shouldn't be necessary to create and manipulate a separate TParam
object - these are maintained as elements of the Params list property
within the TStoredProc class itself.

If you do:

ApproveProc := TStoredProc.Create(Application);
ApproveProc.DatabaseName := 'SOME_DB';
ApproveProc.StoredProcName := 'SOMEOWNER.APPROVE_ITEM';
ApproveProc.Prepare;

The .Prepare method will populate the Params list for you, pretty much
like using the Parameters Editor at design time.

In the case of MS SQL Server (using the SQL Links driver) the BDE is
too dim to work out the ParamType property of the individual TParam
elements - I had to modify the code (OTTOMH):

with ApproveProc do
begin
try
        Prepare;
except
        for i := 0 to ParamCount - 1 do
        if Params[i].ParamType = ptUnknown then
           Params[i].ParamType := ptInput;
     Prepare;
end;
// ...
ExecProc;

I don't know if these antics are required with Oracle - you should be
able to predict this from the properties that you have enter manually
when using the Parameters Editor.

HTH,
Steve

Quote
hammel_r...@promina.org (Rick Hammel) wrote:
>I am trying to dynamically create and use a TStoredProc at runtime.
>Following is the code I am using:

>procedure SomeForm.SomeEvent;
>var
>  ApproveProc       : TStoredProc;
>  ProcParam : TParam;
>begin
>  try
>    ApproveProc := TStoredProc.Create(Application);
>    ProcParam := TParam.Create(ApproveProc.Params, ptInput);
>    ProcParam.DataType := ftInteger;
>    ProcParam.Name := 'SOME_ID';
>    ProcParam.AsInteger := Query_Some_TableSOME_ID.AsInteger;
>    ApproveProc.DatabaseName := 'SOME_DB';
>    ApproveProc.StoredProcName := 'SOMEOWNER.APPROVE_ITEM';
>    ApproveProc.Prepare;
>    ApproveProc.ExecProc;
>  finally
>    ProcParam.Free;
>    ApproveProc.Free;
>  end;
>end;

>I get two exceptions: one from Oracle about having the wrong number of
>parameters or the wrong types.  Then I get a GPF.

>Here is the kicker:
>I put a TStoredProc on my form, and set DatabaseName = 'SOME_DB',
>StoredProcName = 'SOMEOWNER.APPROVE_ITEM', and added an Integer
>parameter by the name of SOME_ID.  Then I replaced the above code
>with:

>  ApproveProc.ParamByName('SOME_ID').AsInteger :=
>                            Query_Some_TableSOME_ID.AsInteger;
>  ApproveProc.ExecProc;

>this works wonderfully.

>Does anybody know what is going on here?  Where did I{*word*222}up?

>BTW, I'm using Delphi 1.0 against an Oracle 7 Server.

>Thank you all so much!

>- Rick
>hammel_r...@promina.org

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

Re:Dynamic TStoredProc @ RunTime


Quote
Rick Hammel wrote:

> I am trying to dynamically create and use a TStoredProc at runtime.
> Following is the code I am using:

> procedure SomeForm.SomeEvent;
> var
>   ApproveProc   : TStoredProc;
>   ProcParam     : TParam;
> begin
>   try
>     ApproveProc := TStoredProc.Create(Application);
>     ProcParam := TParam.Create(ApproveProc.Params, ptInput);
>     ProcParam.DataType := ftInteger;
>     ProcParam.Name := 'SOME_ID';
>     ProcParam.AsInteger := Query_Some_TableSOME_ID.AsInteger;
>     ApproveProc.DatabaseName := 'SOME_DB';
>     ApproveProc.StoredProcName := 'SOMEOWNER.APPROVE_ITEM';
>     ApproveProc.Prepare;
>     ApproveProc.ExecProc;
>   finally
>     ProcParam.Free;
>     ApproveProc.Free;
>   end;
> end;

> I get two exceptions: one from Oracle about having the wrong number of
> parameters or the wrong types.  Then I get a GPF.

> Here is the kicker:
> I put a TStoredProc on my form, and set DatabaseName = 'SOME_DB',
> StoredProcName = 'SOMEOWNER.APPROVE_ITEM', and added an Integer
> parameter by the name of SOME_ID.  Then I replaced the above code
> with:

>   ApproveProc.ParamByName('SOME_ID').AsInteger :=
>                                 Query_Some_TableSOME_ID.AsInteger;
>   ApproveProc.ExecProc;

> this works wonderfully.

> Does anybody know what is going on here?  Where did I{*word*222}up?

> BTW, I'm using Delphi 1.0 against an Oracle 7 Server.

> Thank you all so much!

> - Rick
> hammel_r...@promina.org

I'm not sure, because I didn't try, but I think you're doing wrong in:
...
Quote
>     ProcParam := TParam.Create(ApproveProc.Params, ptInput);
>     ProcParam.DataType := ftInteger;
>     ProcParam.Name := 'SOME_ID';...

I think then Proc component creates params objects after you assign proc
name.

Follow the design time process:
- create proc object
- assign database and proc name
- assign the param value

Hope this helps.

--
-------------------------------------
Robert Cerny - application designer & developer
Neosys Ltd. Ljubljana
Email: robert.ce...@neosys.xrs.si

Other Threads