Board index » delphi » TIBQuery.sql.text with params

TIBQuery.sql.text with params

Is it possible to read the contents of an opened TIBQuery.sql.text in
runtime (with code). I am talking about the sql statement sent to the
database (with the value of params included).

I get:
select * from TEST where TEST = :Param

I want:
select * from TEST where TEST = 'value of param'

I need to pass the SQL statement along to a IBSQL component to perform a
count(*) statement on the current query (with params included).

Hope you understand my problem..

Thanks!

/Johan

 

Re:TIBQuery.sql.text with params


Quote
Johan Petersson wrote:
> Is it possible to read the contents of an opened TIBQuery.sql.text in
> runtime (with code). I am talking about the sql statement sent to the
> database (with the value of params included).

> I get:
> select * from TEST where TEST = :Param

> I want:
> select * from TEST where TEST = 'value of param'

> I need to pass the SQL statement along to a IBSQL component to
> perform a count(*) statement on the current query (with params
> included).

TIBSQL also supports parameters. Just copy the SQL over, changing to do a
count, and then also assign the IBSQL parameters from the TIBQuery
parameters.

--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
Powered by Delphi and IB: http://www.logicfundamentals.com/RadBooks.html
"Democracy, without that guarantee of liberty, is merely a method of
selecting tyrants." - Alan Nitikman

Re:TIBQuery.sql.text with params


How is this possible? Params is a readonly property on IBSQL and there is no
AssignValues method..

Thank you for any help!

/Johan

"Wayne Niddery [TeamB]" <wnidd...@chaff.aci.on.ca> wrote in message
news:3ecd737c$1@newsgroups.borland.com...

Quote
> Johan Petersson wrote:
> > Is it possible to read the contents of an opened TIBQuery.sql.text in
> > runtime (with code). I am talking about the sql statement sent to the
> > database (with the value of params included).

> > I get:
> > select * from TEST where TEST = :Param

> > I want:
> > select * from TEST where TEST = 'value of param'

> > I need to pass the SQL statement along to a IBSQL component to
> > perform a count(*) statement on the current query (with params
> > included).

> TIBSQL also supports parameters. Just copy the SQL over, changing to do a
> count, and then also assign the IBSQL parameters from the TIBQuery
> parameters.

> --
> Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
> Powered by Delphi and IB: http://www.logicfundamentals.com/RadBooks.html
> "Democracy, without that guarantee of liberty, is merely a method of
> selecting tyrants." - Alan Nitikman

Re:TIBQuery.sql.text with params


Quote
"Johan Petersson" <helps...@hotmail.com> wrote in message

news:3ece4818$1@newsgroups.borland.com...

Quote
> How is this possible? Params is a readonly property on IBSQL and there is
no
> AssignValues method..

> Thank you for any help!

You could try something like this:
(This is untested code and is only for an example)

function GetRecordCount(OrigQuery: TIBQuery): integer;
var
    x: integer;

begin
    result := 0;
    with TIBSQL.Create(nil) do
    try
        Database := OrigQuery.Database;
        Transaction := OrigQuery.Transaction;
        SQL.Text := 'Select count(*) ' + copy(OrigQuery.SQL.Text, pos(' from
', OrigQuery.SQL.Text), length(OrigQuery.SQL.Text));
        for x := 0 to OrigQuery.Params.Count-1 do
            ParamByName(OrigQuery.Params[x].Name).Value :=
                    OrigQuery.Params[x].Value;
        IBSQL.ExecSQL;
        result := IBSQL.Fields[0].AsInteger;
        IBSQL.Close;
    finally
        Free;
    end;
end;

HTH

--

Woody (TMW)

"Start every day off with a smile and get it over with."
-- W. C. Fields

Re:TIBQuery.sql.text with params


Quote
Johan Petersson wrote:
> How is this possible? Params is a readonly property on IBSQL and
> there is no AssignValues method..

ibsql1.Params.ByName

...or...

ibsql1.ParamByName

--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
Powered by Delphi and IB: http://www.logicfundamentals.com/RadBooks.html
"Democracy, without that guarantee of liberty, is merely a method of
selecting tyrants." - Alan Nitikman

Re:TIBQuery.sql.text with params


Thanks!!

I used an IBQuery instead (using Parmas.AssignParams). Will that affect
performance? Is IBSql much faster?

/Johan

Quote
"Woody (TMW)" <woody....@ih2000.net> wrote in message

news:3ece57a7@newsgroups.borland.com...
Quote
> "Johan Petersson" <helps...@hotmail.com> wrote in message
> news:3ece4818$1@newsgroups.borland.com...
> > How is this possible? Params is a readonly property on IBSQL and there
is
> no
> > AssignValues method..

> > Thank you for any help!

> You could try something like this:
> (This is untested code and is only for an example)

> function GetRecordCount(OrigQuery: TIBQuery): integer;
> var
>     x: integer;

> begin
>     result := 0;
>     with TIBSQL.Create(nil) do
>     try
>         Database := OrigQuery.Database;
>         Transaction := OrigQuery.Transaction;
>         SQL.Text := 'Select count(*) ' + copy(OrigQuery.SQL.Text, pos('
from
> ', OrigQuery.SQL.Text), length(OrigQuery.SQL.Text));
>         for x := 0 to OrigQuery.Params.Count-1 do
>             ParamByName(OrigQuery.Params[x].Name).Value :=
>                     OrigQuery.Params[x].Value;
>         IBSQL.ExecSQL;
>         result := IBSQL.Fields[0].AsInteger;
>         IBSQL.Close;
>     finally
>         Free;
>     end;
> end;

> HTH

> --

> Woody (TMW)

> "Start every day off with a smile and get it over with."
> -- W. C. Fields

Re:TIBQuery.sql.text with params


Quote
Johan Petersson wrote:

> Thanks!!

> I used an IBQuery instead (using Parmas.AssignParams). Will that affect
> performance? Is IBSql much faster?

Yes.  In some instances IBSQL is twice as fast.

--
Jeff Overcash (TeamB)
      (Please do not email me directly unless  asked. Thank You)
If there is somebody up there could they throw me down a line.  Just a
little helping hand just a little understanding.  Just some answers to the
questions that surround me now.  If there's somebody up there could
they throw me down a line.               (Fish)

Other Threads