Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: IBX: Why does setting parameters now trigger a Prepare?


This question is answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 7 - Last Post: May 7, 2015 10:02 AM Last Post By: Anthony Gautier
Anthony Gautier

Posts: 17
Registered: 10/10/09
IBX: Why does setting parameters now trigger a Prepare?  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Apr 20, 2015 2:58 PM
I'm migrating an application from Delphi 2007 to XE7 and noticed that within IBX, setting parameters now triggers a Prepare. This wasn't the case in Delphi 2007, so when migrating I'm running into errors (more details below). How come the Prepare wasn't needed before, but is now? I'd like to understand the reasoning so I can update my application to work most effectively.

Details:
In Delphi 2007, sometimes I created the SQL and set parameters before starting a transaction. This was in order to simplify the code. For example, I was able to create code like this:

Set SQL statement to 'select * from table1 where column1 = :value'
Set value to 'abc'
Run SQL statement (transaction automatically handled here)

With XE7, the above code causes a "transaction not active" error, since Prepare is called when I set the value, prior to the transaction. It looks like I need to now manage the transaction within the same code, like this:

Start transaction
Set SQL statement to 'select * from table1 where column1 = :value'
Set value to 'abc'
Run SQL statement (no transaction handling)
End transaction

The 2 additional lines seem like they shouldn't be necessary to me -- not to mention, finding all places in my code that need this change can be difficult. But, I don't know a lot about the details of IBX. I wonder if my old method is a bad idea, and if the automatic Prepare is meant to guide me to a solution that is better in some way. If it's not strictly "better", it would be great to have the option to turn off the "auto-prepare" so I don't risk adding bugs in my application that I'll only find at runtime.

Edited by: Anthony Gautier on Apr 20, 2015 3:02 PM
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: IBX: Why does setting parameters now trigger a Prepare? [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Apr 22, 2015 2:11 PM   in response to: Anthony Gautier in response to: Anthony Gautier
Anthony Gautier wrote:
I'm migrating an application from Delphi 2007 to XE7 and noticed that within IBX, setting parameters now triggers a Prepare. This wasn't the case in Delphi 2007, so when migrating I'm running into errors (more details below). How come the Prepare wasn't needed before, but is now? I'd like to understand the reasoning so I can update my application to work most effectively.

Details:
In Delphi 2007, sometimes I created the SQL and set parameters before starting a transaction. This was in order to simplify the code. For example, I was able to create code like this:

Set SQL statement to 'select * from table1 where column1 = :value'
Set value to 'abc'
Run SQL statement (transaction automatically handled here)

With XE7, the above code causes a "transaction not active" error, since Prepare is called when I set the value, prior to the transaction. It looks like I need to now manage the transaction within the same code, like this:

Start transaction
Set SQL statement to 'select * from table1 where column1 = :value'
Set value to 'abc'
Run SQL statement (no transaction handling)
End transaction

The 2 additional lines seem like they shouldn't be necessary to me -- not to mention, finding all places in my code that need this change can be difficult. But, I don't know a lot about the details of IBX. I wonder if my old method is a bad idea, and if the automatic Prepare is meant to guide me to a solution that is better in some way. If it's not strictly "better", it would be great to have the option to turn off the "auto-prepare" so I don't risk adding bugs in my application that I'll only find at run
time.

Edited by: Anthony Gautier on Apr 20, 2015 3:02 PM

IBQuery was the only component left that at times required a manual prepare.
This was fixed in XE7. There was a bug in that fix though when dynamically
building the SQL in code.

change The following two procedures and statically add the changed
IBX.IBQuery.pas file to your project.

function TIBQuery.GetParams: TParams;
begin
    if (not (csDesigning in ComponentState)) and
       (FParams.Count = 0) and
       (not Prepared) and (not SQL.Text.Trim.IsEmpty) then
      Prepare;
    Result := FParams;
end;
 
function TIBQuery.ParamByName(const Value: string): TParam;
begin
    if (FParams.Count = 0) and
       (not Prepared) and
       (not SQL.Text.Trim.IsEmpty) then
      Prepare;
    Result := FParams.ParamByName(Value);
end;


That should resolve this issue.

This was already asked and answered in the delphi.database.interbase_express group.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
And so I patrol in the valley of the shadow of the tricolor
I must fear evil. For I am but mortal and mortals can only die.
Asking questions, pleading answers from the nameless
faceless watchers that stalk the carpeted corridors of Whitehall.
(Fish)
quinn wildman

Posts: 856
Registered: 12/2/99
Re: IBX: Why does setting parameters now trigger a Prepare? [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: May 1, 2015 11:39 AM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
Did this fix get in XE8 RTM?

Jeff Overcash (TeamB) wrote:
IBQuery was the only component left that at times required a manual prepare.
This was fixed in XE7. There was a bug in that fix though when dynamically
building the SQL in code.
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: IBX: Why does setting parameters now trigger a Prepare? [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: May 1, 2015 10:08 PM   in response to: quinn wildman in response to: quinn wildman
quinn wildman wrote:
Did this fix get in XE8 RTM?

Jeff Overcash (TeamB) wrote:
IBQuery was the only component left that at times required a manual prepare.
This was fixed in XE7. There was a bug in that fix though when dynamically
building the SQL in code.

Yes this fix is in XE8. I have no reported issues since last fall on this.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
And so I patrol in the valley of the shadow of the tricolor
I must fear evil. For I am but mortal and mortals can only die.
Asking questions, pleading answers from the nameless
faceless watchers that stalk the carpeted corridors of Whitehall.
(Fish)
Anthony Gautier

Posts: 17
Registered: 10/10/09
Re: IBX: Why does setting parameters now trigger a Prepare? [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: May 6, 2015 10:03 AM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
Thanks Jeff, I just installed XE8 and was able to verify the fix is in place. I noticed that calls to ParamCount don't have the same fix in place -- is this intentional?
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: IBX: Why does setting parameters now trigger a Prepare? [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: May 6, 2015 11:46 AM   in response to: Anthony Gautier in response to: Anthony Gautier
Anthony Gautier wrote:
Thanks Jeff, I just installed XE8 and was able to verify the fix is in place. I noticed that calls to ParamCount don't have the same fix in place -- is this intentional?

No not intentional. Just me focused on the reported problem and not expanding
my scope of looking to other similar instances. Same style of fix, only prepare
if the FParams.Count is still 0.

function TIBQuery.GetParamsCount: Word;
begin
   if (not (csDesigning in ComponentState)) and
      (FParams.Count = 0) and
      (not Prepared) and (not SQL.Text.Trim.IsEmpty) then
     Prepare;
   Result := FParams.Count;
end;


That should fix it.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
And so I patrol in the valley of the shadow of the tricolor
I must fear evil. For I am but mortal and mortals can only die.
Asking questions, pleading answers from the nameless
faceless watchers that stalk the carpeted corridors of Whitehall.
(Fish)
Anthony Gautier

Posts: 17
Registered: 10/10/09
Re: IBX: Why does setting parameters now trigger a Prepare? [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: May 7, 2015 10:02 AM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
That should do it. Thanks Jeff!
Anthony Gautier

Posts: 17
Registered: 10/10/09
Re: IBX: Why does setting parameters now trigger a Prepare?  
Click to report abuse...   Click to reply to this thread Reply
  Posted: May 6, 2015 10:04 AM   in response to: Anthony Gautier in response to: Anthony Gautier
Bug in XE7 that is fixed in XE8 (but note that ParamCount still has XE7 behavior).
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02