Board index » delphi » Params unknown in TIBQuery?

Params unknown in TIBQuery?

Hi All,

I want to use the name of the current user in a TIBQuery. I use the
following approach:

At design time, I double click the TIBQuery->Params and add a parameter. I
set the name to "USERID", set the datatype to ftString, and even add a
default entry "MYNAME".

Now at runtime I want to set the value for this parameter, I have tried this
on several ways:

1. The BeforeOpen event of the query:
Q1->Params->ParamByName("USERID")->AsString=MF->UserName; (yes, BCB6 )

2. Somewhere in the main program loop after opening the database.
D->Q1->ParamByName("USERID")->AsString=UserName;
(both variants compile without problems)

(D = datamodule, Q1 is a TIBQuery on D, MF= Main Form, UserName is a public
var on MF)

Anyway, the BeforeOpen event throws the exception: "Parameter "USERID" not
found."..
After this error the query is opened and yields an empty result.
The SQL code is:
SELECT * FROM PROJECTS WHERE PERSON = :USERID
This does not throw an exception (it does when I remove the parameter...) so
the parameter USERID  DOES exist.

I want the parameter USERID to be available by default for any query which
is executed by the TIBquery but how do I get the value in Params at runtime?
I'm rather desparate...

Thanks a 1000000!

Best regards,
Johann

 

Re:Params unknown in TIBQuery?


Quote
"Johann Sijpkes" <jsijp...@abl.nl> wrote in message

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

Quote
> Hi All,

> I want to use the name of the current user in a TIBQuery. I use the
> following approach:

What version of IBX are you using? Are you currently up to date?

--
Woody (TMW)

"Never trust a computer you can't throw out a window."
-Steve Wozniak

Re:Params unknown in TIBQuery?


Quote

> What version of IBX are you using? Are you currently up to date?

I've checked: 6.01 at the time of the post. Did download the update from
codecentral and installed it (rough ride due to other components which
blocked the ibxepress60.bpl) .
My TIBquery component now shows 6.05
But still the same error :

Q1->ParamByName("USERID")->AsString=MF->UserName;
Gives the error "Parameter "USERID" not found. Double clicking Params shows
that it's there...

?

Best regards,
Johann

Re:Params unknown in TIBQuery?


Quote
"Johann Sijpkes" <jsijp...@abl.nl> wrote in message

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

Quote

> > What version of IBX are you using? Are you currently up to date?

> I've checked: 6.01 at the time of the post. Did download the update from
> codecentral and installed it (rough ride due to other components which
> blocked the ibxepress60.bpl) .
> My TIBquery component now shows 6.05
> But still the same error :

> Q1->ParamByName("USERID")->AsString=MF->UserName;
> Gives the error "Parameter "USERID" not found. Double clicking Params
shows
> that it's there...

AFAIK, you shouldn't have to call Prepare, especially since it's created and
set at design time. You should never need to explicitly call Prepare anyway,
in any case. However, knowing something is true and testing whether it's
true are two different things. Try issuing a Prepare statement on the
TIBQuery just before setting the parmeter in code. Also, you might want to
debug that point and look at the contents of the SQL in code to make sure
it's what you think it is. (Sorry if that sounds basic like you wouldn't
have done it already, but sometimes I find myself taking things for granted
so I want to make sure. :)

--
Woody (TMW)

"Never trust a computer you can't throw out a window."
-Steve Wozniak

Re:Params unknown in TIBQuery?


Quote
> AFAIK, you shouldn't have to call Prepare, especially since it's created
and
> set at design time. You should never need to explicitly call Prepare
anyway,
> in any case. However, knowing something is true and testing whether it's
> true are two different things. Try issuing a Prepare statement on the
> TIBQuery just before setting the parmeter in code. Also, you might want to

Preparing seems not to make a difference. I have had some result now, but
things are very strange....
I have a table (IBDataset connected to IB database, which contains custom
queries which endusers can build using a query builder). On a form I have a
grid from which the user can choose the query record. On the same form there
is a TIBQuery component, Datasource and a ReadOnly grid to display query
results. On the form there is a button whichh people can press to execute
the query.
The code for this button :

  Q->Close();
  Q->SQL->Text=D->QueriesQUER->AsString;
  Q->ParamByName("USERID")->AsString = MF->Gebruiker; //Gebruiker = Dutch
for User ...
  Q->Open();

 So people can use :USERID to use their own name in queries.

 Currently I have 2 queries in D->Queries.
When I select the first I get the error "Parameter USERID not found" when
pressing the button (The query is SELECT * FROM PROJECTS"
The second query processes fine giving expected results
(This query:

SELECT
  ASSIGNMENTS.PERSON,
  ASSIGNMENTS.FUNCDESC
FROM
  ASSIGNMENTS
WHERE
  (ASSIGNMENTS.PERSON = :USERID)
)

This seems that parameters cannot exist if it is not available in the
query??
What is the availability and lifetime of parameters of TIBQuery?
I assumed that designtime created params were permanent... are they?
(I have trouble debugging the parameters : I only get a pointer, the
Param->Name/Value properties are inavailable in the de{*word*81}..

Is all this normal behaviour? (Apart from this all works fine....)

Thanks!
Johann

Re:Params unknown in TIBQuery?


Quote
Johann Sijpkes wrote:

> > AFAIK, you shouldn't have to call Prepare, especially since it's created
> and
> > set at design time. You should never need to explicitly call Prepare
> anyway,
> > in any case. However, knowing something is true and testing whether it's
> > true are two different things. Try issuing a Prepare statement on the
> > TIBQuery just before setting the parmeter in code. Also, you might want to

> Preparing seems not to make a difference. I have had some result now, but
> things are very strange....
> I have a table (IBDataset connected to IB database, which contains custom
> queries which endusers can build using a query builder). On a form I have a
> grid from which the user can choose the query record. On the same form there
> is a TIBQuery component, Datasource and a ReadOnly grid to display query
> results. On the form there is a button whichh people can press to execute
> the query.
> The code for this button :

>   Q->Close();
>   Q->SQL->Text=D->QueriesQUER->AsString;

Changing the SQL clears the params and the params are recalculated from the new
SQL.

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

Re:Params unknown in TIBQuery?


Thanks,

Indeed, after changing the SQL, the parameters are cleared. I've added an
OnBeforeOpen event where I scan for the occurance of the Parameters. This
works on any query the user builds.

if (Q1->SQL->Text.Pos(":NEXTWEEK"))
   Q1->ParamByName("NEXTWEEK")->AsDate=Now()+7;

Other Threads