Board index » delphi » Parameterized Queries

Parameterized Queries

Hi,

Maybe I'm missing something.  This is supposed to be VERY basic, and I've
gotten it to work before, about 30% of the time.  Sometimes code that didn't
work a minute before decided to start working out of the blue, without any
modifications that could possibly be relevant.

I have a query:

select b.dob, b.beneficiary_no, b.title, b.initials, b.fnames, b.name,
b.cli_no, b.beneficiary_no_parent, b.id_no, d.depend_type, d.descr
  from beneficiary b, dependtype d
  where b.cli_no = :CliNo
    and b.depend_type = d.depend_type

ParamCheck is set to True, and in the Params property's editor, I set
CliNo's type to String  -  which is correct.  In my code, I have a fragment
such as:

Query.Close;
Query.ParamByName('CliNo').AsString := CliNo;
Query.Open;

CliNo is assigned elsewhere.  Basic, huh?  It doesn't freaking work.  I get
an empty result set, whereas I get a ten record result set in SQL explorer
is I copy the damn statement over and replace :CliNo with the exact same
value that I assign to the CliNo variable in code.  I've even tried using
Prepare beforehand, but this doesn't seem to make any difference.

I know this must work.  All my books tell me this will work, the help file
tells me it will work, and I've gotten it to work before  -  only I don't
know what is different from then.  I get the exact same problem when using
TStoredProc to exec a stored procedure.  I must be missing something really
basic, but I give up.

I have used Evaluate/Modify to check the value of the CliNo variable, as
well as the entire Query.ParamByName('CliNo').AsString after the assignment.
Both check out fine.

New, is that I now get an EDBEngine exception "Could not find object" which
to me is senseless, as the query works fine in SQL Explorer.

Any ideas?

Regards,
Cobus Kruger

 

Re:Parameterized Queries


Just a thought.
Delphi is not tricky but very minor detail is important.
I have not test any code . Just a theory.

1. if both DataSource and ParamByName are used, which one will take
precedence ?
Is it possible Delphi is so smart when Datasource already supply your
:CliNo it ignores your :CliNo in ParamByName

I would like to know how Delphi works too.

Cobus Kruger <cob...@limon.co.za> wrote in article
<6soq8n$n2...@asgard.sprintlink.co.za>...

Quote
> Hi,

> Maybe I'm missing something.  This is supposed to be VERY basic, and I've
> gotten it to work before, about 30% of the time.  Sometimes code that
didn't
> work a minute before decided to start working out of the blue, without
any
> modifications that could possibly be relevant.

> I have a query:

> select b.dob, b.beneficiary_no, b.title, b.initials, b.fnames, b.name,
> b.cli_no, b.beneficiary_no_parent, b.id_no, d.depend_type, d.descr
>   from beneficiary b, dependtype d
>   where b.cli_no = :CliNo
>     and b.depend_type = d.depend_type

> ParamCheck is set to True, and in the Params property's editor, I set
> CliNo's type to String  -  which is correct.  In my code, I have a
fragment
> such as:

> Query.Close;
> Query.ParamByName('CliNo').AsString := CliNo;
> Query.Open;

> CliNo is assigned elsewhere.  Basic, huh?  It doesn't freaking work.  I
get
> an empty result set, whereas I get a ten record result set in SQL
explorer
> is I copy the damn statement over and replace :CliNo with the exact same
> value that I assign to the CliNo variable in code.  I've even tried using
> Prepare beforehand, but this doesn't seem to make any difference.

> I know this must work.  All my books tell me this will work, the help
file
> tells me it will work, and I've gotten it to work before  -  only I don't
> know what is different from then.  I get the exact same problem when
using
> TStoredProc to exec a stored procedure.  I must be missing something
really
> basic, but I give up.

> I have used Evaluate/Modify to check the value of the CliNo variable, as
> well as the entire Query.ParamByName('CliNo').AsString after the
assignment.
> Both check out fine.

> New, is that I now get an EDBEngine exception "Could not find object"
which
> to me is senseless, as the query works fine in SQL Explorer.

> Any ideas?

> Regards,
> Cobus Kruger

Re:Parameterized Queries


Quote
pc wrote:

> Just a thought.
> Delphi is not tricky but very minor detail is important.
> I have not test any code . Just a theory.

> 1. if both DataSource and ParamByName are used, which one will take
> precedence ?
> Is it possible Delphi is so smart when Datasource already supply your
> :CliNo it ignores your :CliNo in ParamByName

> I would like to know how Delphi works too.

> Cobus Kruger <cob...@limon.co.za> wrote in article
> <6soq8n$n2...@asgard.sprintlink.co.za>
...
> > Maybe I'm missing something...

> > Query.Close;
> > Query.ParamByName('CliNo').AsString := CliNo;
> > Query.Open;

> > CliNo is assigned elsewhere.  Basic, huh?  It doesn't freaking work.

I ended up doing

Query.SQL.Clear;
Query.SQL.Add(MYSELECT + ' ' + CliNo + ';');
Query.Open;

most of the time.  The only exception is TDateTime parameters, and
they work for me.  The only thing I can think of is that I never touched
parameter check...

PC -- if both DataSource and ParamByName are supplied your app may
crash.  I should have a zip of an old app somewhere that crashed on

Query.Filtered:= True;
if not Query.FindFirst then Query.Filtered:= False; <-- crash!

What's more,

if Query.FindFirst then Query.Filtered:= True;

would work, but only until

Query.Filtered:= False;
Query.First;  <-- bang!

Removing DataSource from this and a couple of other queries in the same
data module fixed it and I never got around to submitting a proper bug
report with "small working example that uses DBDemos" blah, blah, blah
No time.

Dimitri

Other Threads