Board index » delphi » Am I using the TStoredProc component properly?

Am I using the TStoredProc component properly?

Just wondering if I could get some comments on how I write my code for
StoredProcs.  I am not experiencing any errors, I just want to know if I am
doing things right.

  with TIBStoredProc.Create(Self) do
  try
    Database := a_database_object;
    Transaction := a_transaction_object;
    StoredProcName := 'the_name_of_an_SP';
    if not Database.Connected then
      Database.Open;
    if Transaction.InTransaction then
      Transaction.Rollback;
    Transaction.StartTransaction;
    try
      Prepare;
      Params[0].AsInteger := param1;
      Params[1].AsInteger := param2;
      ExecProc;
      Transaction.Commit;
    except
      Transaction.Rollback;
    end;
  finally
    Unprepare;
    Free;
  end;

The two parts that I am most interested in getting comments on are:
1) Calling Prepare to get the params for the sp rather than programmatically
creating them as is described in the Developer's Guide

2) The position of the call to Unprepare.  It seams funny to me that it is
after Commit, but if I place it before the commit and an error occurs then
only rollback would be called and the SP would not be Unprepared.  To be
honest I don't even know if that is a problem... =)

Any way, any comments on the above two (or any other part for that mater)
would be greatly appreciated.

Cheers,
Nick Barrett

 

Re:Am I using the TStoredProc component properly?


Hi Patrick,

Quote
> I wonder why you want to make it "So Runtime". Do you really not even know

the database?

I use this method because the code is actualy in a component I am writing.
The component wraps up all the functionality I need to deal with a
particular set of tables.  I currently have about 30 of them at the moment.

Quote
> I don't know about your use of prepare to get the parameters. I would
think you
> could specify them first.

I was kind of hoping I wouldn't have to create them myself.  I have about
500 SPs of about half of which I call using a TStoredProc (The other half I
use a TIBQuery so that I can get the results from them) So as you can see I
would be quite reluctant to go through them all... =)

Quote
> Could the transaction actually be active and stay active coming into
> this so that you could need the rollback?

This is some times the case.  The code sample I showed however was the one
that had the Unprepare after the commit, which is what I was concerned
about.

Thanks for your input Patrick =)

Cheers,
Nick Barrett

Re:Am I using the TStoredProc component properly?


I didn't notice the Params[0].  That would be the only case you need to prepare
it because it bypasses TIBStoredProc altogether.  ParamByName does not require a
prepare.  You never need to unprepare.

Quote
Nick Barrett wrote:

> Thanks for the response Jeff,

> > No need to call Prepare or Unprepare.
> I just tried removing the calls to Prepare and Unprepare and I got 'List
> index out of bounds (0)' error.

--
Jeff Overcash (TeamB)  
  (Please do not email me directly unless asked.  Thank You)    
A spider wanders aimlessly within the warmth of a shadow, not the regal creature
of border caves, but the poor, misguided, directionless familiar of some obscure
Scottish poet.  The mist crawls from the c{*word*7}like some primordial phantom of
romance To curl, under the cascade of neon pollen.  While I sit tied to the
phone like some expectant father, your carnation will rot in a vase.  (Fish)

Re:Am I using the TStoredProc component properly?


Thanks for the very quick responce.

If I were to keep using Params[index] rather than ParamByName then should I
be placing the call to Unprepare before or after commit?  The reason I use
Params[index] is that I sometimes change the params in my SP's.  So rather
than ensuring that every name is spelt correctly and in the correct order, I
only have to worry about the order.

Cheers,
Nick Barrett

Re:Am I using the TStoredProc component properly?


Sorry Jeff.  I didn't realize that when you said never you realy ment never.
I thought you ment never if you are using ParamByName.

Thanks for your help =)

Cheers,
Nick Barrett

Re:Am I using the TStoredProc component properly?


Quote
Nick Barrett wrote:

> Thanks for the very quick responce.

> If I were to keep using Params[index] rather than ParamByName then should I
> be placing the call to Unprepare before or after commit?  

Never.  Unlike the BDE a call to Unprepare is never needed in IBX.

Quote
> The reason I use
> Params[index] is that I sometimes change the params in my SP's.  So rather
> than ensuring that every name is spelt correctly and in the correct order, I
> only have to worry about the order.

> Cheers,
> Nick Barrett

--
Jeff Overcash (TeamB)  
  (Please do not email me directly unless asked.  Thank You)    
A spider wanders aimlessly within the warmth of a shadow, not the regal creature
of border caves, but the poor, misguided, directionless familiar of some obscure
Scottish poet.  The mist crawls from the c{*word*7}like some primordial phantom of
romance To curl, under the cascade of neon pollen.  While I sit tied to the
phone like some expectant father, your carnation will rot in a vase.  (Fish)

Other Threads