Hello all,

I'm migrating my project from Delphi 5! to XE6 where I have been used to use TIBQuery with dynamic SQL preparation in runtime.

with Query do begin
  SQL.Clear;
  SQL.Add('SELECT * FROM TABLE WHERE ID IN (SELECT ID FROM TABLE2 WHERE NAME=:NAME');
  ParamByName('NAME').AsString := edit1.Text;
  SQL.Add(')');
  Open;
  ...  
end;

but since XE6 I found that when I send a value to parameter, IBX tries to prepare SQL statement which is not finished because of missing bracket.

How can I avoid of this problem, otherwise I will need to find out other components to access database or comment out the preparation in IBX code.

Thanks in advance.

Roland Turcan wrote:
>> {quote:title=Jeff Overcash (TeamB) wrote:}{quote}
>> Roland Turcan wrote:
>>> Hello all,
>>>
>>> I'm migrating my project from Delphi 5! to XE6 where I have been used to use TIBQuery with dynamic SQL preparation in runtime.
>>>
>>> with Query do begin
>>>   SQL.Clear;
>>>   SQL.Add('SELECT * FROM TABLE WHERE ID IN (SELECT ID FROM TABLE2 WHERE NAME=:NAME');
>>>   ParamByName('NAME').AsString := edit1.Text;
>>>   SQL.Add(')');
>>>   Open;
>>>   ...  
>>> end;
>>>
>>> but since XE6 I found that when I send a value to parameter, IBX tries to prepare SQL statement which is not finished because of missing bracket.
>>>
>>> How can I avoid of this problem, otherwise I will need to find out other components to access database or comment out the preparation in IBX code.
>>>
>>> Thanks in advance.
>> Before a parameter can be set th SQL must first get internally prepared.  IBX 
>> does this for you, but you are trying to set the parameter with an invalid SQL 
>> statement.  Parameters are not macros.  It is not a string replacement.  Your 
>> SQL must be valid and complete before you try to access parameters.
>>
>> -- 
>> Jeff Overcash (TeamB)
> 
> Of course I have found that part of code executing Prepare when setting value to parameter. I even understand the reason for it, but my problem is, that I have written all my code
> that way and the SQL statement build process of often more complicated than this simple example. I have my biggest project built with Delphi 5 and changing to Delphi XE6, where
> is not enough manpower to change all sources to follow new IBX way and release new version in short time.
> 

IBX has never been any other way.  TParams has never been a macro expansion, 
ever.  If ParamCheck is true (the default from the very beginning) when you 
change the query the query is reparsed by the TParams class (not IBX itself), 
the old values copied to the new list of parameters, the old parameters clears, 
then the new parameters copied back into the Params variable.  Nothing in that 
has changed since D5.

This code has not cahnged since D5

{code}
procedure TIBQuery.QueryChanged(Sender: TObject);
var
   List: TParams;
begin
   if not (csReading in ComponentState) then
   begin
     Disconnect;
     if ParamCheck or (csDesigning in ComponentState) then
     begin
       List := TParams.Create(Self);
       try
         FText := List.ParseSQL(SQL.Text, True);
         List.AssignValues(FParams);
         FParams.Clear;
         FParams.Assign(List);
       finally
         List.Free;
       end;
     end else
       FText := SQL.Text;
     DataEvent(dePropertyChange, 0);
   end else
     FText := FParams.ParseSQL(SQL.Text, False);
   SelectSQL.Assign(SQL);
end;
{code}

IBDataset will not have th same issues because it does not use TParams and is 
100% in my control due to that.

The change in behavior for IBX in XE6 was around params not being available at 
for IBQuery after reading it in from the DFM.  An explicite prepare was needed 
before working with the parameters.  This was a behavior difference from all 
other IBX components.  IBQuery was brought in line with all other components in 
that you never have to explicitly prepare TIBQuery, it knows when it needs a 
prepare done.

> OK, but tell me please what way should be used when SQL statement is built up from runtime where the state of user input and parameters are added dynamically.
> 

Multiple ways.

Build the whole SQL string, once done then assign the params.

If you are note reusing the SQL but building it every time don't even use 
parameters and put your values in the SQL itself.  If the code you are showing 
is how you are actually building thing and using it this is probably the method 
you should have been using already.  Parameters should normally be reserved for

Create a TParams before building your SQL.  As you build it add a TParam with 
the same name and value you want into this temporary TParams and when finished 
just call

IBQuery.Params.AssignValue();

> PS: I have commented out the preparation of SQL statement and I haven't found any side-effect of it yet. Will I get it as surprise in the future?
> 

SQL statements CAN NOT RUN in InterBase if not prepared first.  The preparing is 
when the plan is determined for the statement.  It also determines the 
underlying SQLVar that is sent and which the TParam values are copied into 
before executing.  At some point the statement must be prepared.  Explicite 
prepares are never needed with IBX, this was the last place you sometimes had to 
and this was corrected to fall in line with everything else.

> Thanks Jeff.

If you were going to modify the IBX code I would change these two in this way.

{code}
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;
{code}

-- 
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)

> {quote:title=quinn wildman wrote:}{quote}
> I'm a little surprised that TIBQuery has changed, but why don't you do 
> this instead?:
> 
> with Query do begin
>      SQL.Clear;
>      SQL.Add('SELECT * FROM TABLE WHERE ID IN (SELECT ID FROM TABLE2 
> WHERE NAME=:NAME)');
>      ParamByName('NAME').AsString := edit1.Text;
>      Open;

Hello Quinn,

Of course in this simple example is it easy to do it with your suggestion, but the SQL statement build process is more complicated and it is not about the only parameter, but many subselects, ...

Greetings, RT

> {quote:title=Jeff Overcash (TeamB) wrote:}{quote}
> Roland Turcan wrote:
> > Hello all,
> > 
> > I'm migrating my project from Delphi 5! to XE6 where I have been used to use TIBQuery with dynamic SQL preparation in runtime.
> > 
> > with Query do begin
> >   SQL.Clear;
> >   SQL.Add('SELECT * FROM TABLE WHERE ID IN (SELECT ID FROM TABLE2 WHERE NAME=:NAME');
> >   ParamByName('NAME').AsString := edit1.Text;
> >   SQL.Add(')');
> >   Open;
> >   ...  
> > end;
> > 
> > but since XE6 I found that when I send a value to parameter, IBX tries to prepare SQL statement which is not finished because of missing bracket.
> > 
> > How can I avoid of this problem, otherwise I will need to find out other components to access database or comment out the preparation in IBX code.
> > 
> > Thanks in advance.
> 
> Before a parameter can be set th SQL must first get internally prepared.  IBX 
> does this for you, but you are trying to set the parameter with an invalid SQL 
> statement.  Parameters are not macros.  It is not a string replacement.  Your 
> SQL must be valid and complete before you try to access parameters.
> 
> -- 
> Jeff Overcash (TeamB)

Of course I have found that part of code executing Prepare when setting value to parameter. I even understand the reason for it, but my problem is, that I have written all my code
that way and the SQL statement build process of often more complicated than this simple example. I have my biggest project built with Delphi 5 and changing to Delphi XE6, where
is not enough manpower to change all sources to follow new IBX way and release new version in short time.

OK, but tell me please what way should be used when SQL statement is built up from runtime where the state of user input and parameters are added dynamically.

PS: I have commented out the preparation of SQL statement and I haven't found any side-effect of it yet. Will I get it as surprise in the future?

Thanks Jeff.

Roland Turcan wrote:
> Hello all,
> 
> I'm migrating my project from Delphi 5! to XE6 where I have been used to use TIBQuery with dynamic SQL preparation in runtime.
> 
> with Query do begin
>   SQL.Clear;
>   SQL.Add('SELECT * FROM TABLE WHERE ID IN (SELECT ID FROM TABLE2 WHERE NAME=:NAME');
>   ParamByName('NAME').AsString := edit1.Text;
>   SQL.Add(')');
>   Open;
>   ...  
> end;
> 
> but since XE6 I found that when I send a value to parameter, IBX tries to prepare SQL statement which is not finished because of missing bracket.
> 
> How can I avoid of this problem, otherwise I will need to find out other components to access database or comment out the preparation in IBX code.
> 
> Thanks in advance.

Before a parameter can be set th SQL must first get internally prepared.  IBX 
does this for you, but you are trying to set the parameter with an invalid SQL 
statement.  Parameters are not macros.  It is not a string replacement.  Your 
SQL must be valid and complete before you try to access parameters.

-- 
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)

I'm a little surprised that TIBQuery has changed, but why don't you do 
this instead?:

with Query do begin
     SQL.Clear;
     SQL.Add('SELECT * FROM TABLE WHERE ID IN (SELECT ID FROM TABLE2 
WHERE NAME=:NAME)');
     ParamByName('NAME').AsString := edit1.Text;
     Open;

Roland Turcan wrote:
> Hello all,
>
> I'm migrating my project from Delphi 5! to XE6 where I have been used to use TIBQuery with dynamic SQL preparation in runtime.
>
> with Query do begin
>    SQL.Clear;
>    SQL.Add('SELECT * FROM TABLE WHERE ID IN (SELECT ID FROM TABLE2 WHERE NAME=:NAME');
>    ParamByName('NAME').AsString := edit1.Text;
>    SQL.Add(')');
>    Open;
>    ...
> end;
>
> but since XE6 I found that when I send a value to parameter, IBX tries to prepare SQL statement which is not finished because of missing bracket.
>
> How can I avoid of this problem, otherwise I will need to find out other components to access database or comment out the preparation in IBX code.
>
> Thanks in advance.
>