Board index » delphi » Working with TQuery Result Set

Working with TQuery Result Set

I'm using a TQuery object to run an SQL select on an Oracle database
using a couple of parameters. I've verified that the query is correct by
checking the value of TQuery.text and TQuery.Params[]. I know the values
are present in the database I'm querying, but the TQuery.Fields[] values
are all coming back NULL.

I've even made up values that I know aren't in the database and selected
them by the same method and TQuery.Fields[] values are coming back NULL.
Am I missing something?

All the documentation about this in Delphi deals with editing the result
set, I don't need to edit the result set, I just want to verify that
what's in TQuery.Fields is the same as what was in TQuery.Params and if it
isn't, I want to warn the user of the program.

Thanks for your help.

 

Re:Working with TQuery Result Set


Can you post your code where you set up and run the query? A copy of
the sql text would help, too. Copy and paste if you can; sometimes the
problem is pretty subtle, and you might not copy it if you just retype
the info into your newsreader.

Dan

On Thu, 25 Oct 2001 16:06:36 -0500, Dave Hull <dph...@insipid.com>
wrote:

Quote
>I'm using a TQuery object to run an SQL select on an Oracle database
>using a couple of parameters. I've verified that the query is correct by
>checking the value of TQuery.text and TQuery.Params[]. I know the values
>are present in the database I'm querying, but the TQuery.Fields[] values
>are all coming back NULL.

>I've even made up values that I know aren't in the database and selected
>them by the same method and TQuery.Fields[] values are coming back NULL.
>Am I missing something?

>All the documentation about this in Delphi deals with editing the result
>set, I don't need to edit the result set, I just want to verify that
>what's in TQuery.Fields is the same as what was in TQuery.Params and if it
>isn't, I want to warn the user of the program.

>Thanks for your help.

--
Dan Brennand
CMDC systems, inc.
Configuration Management and Document Control:
visit us at www.cmdcsystems.com
[SPAM block: my e-mail address is DanB at CMDCsystems.com]

Re:Working with TQuery Result Set


Is the data type in the columns you're returning supported?  I had a
similar problem with MS SQL server when I created an nvarchar column
which Delphi didn't support.  Switching it to varchar cured the
problem.
HTH,
Myron

Quote
>On Thu, 25 Oct 2001 16:06:36 -0500, Dave Hull <dph...@insipid.com>
>wrote:

>>I'm using a TQuery object to run an SQL select on an Oracle database
>>using a couple of parameters. I've verified that the query is correct by
>>checking the value of TQuery.text and TQuery.Params[]. I know the values
>>are present in the database I'm querying, but the TQuery.Fields[] values
>>are all coming back NULL.

Definition: Emergency Numbers -- Police station, Fire Department and
 Fast Food Places that deliver.

Re:Working with TQuery Result Set


You want code? Well, it's ugly, it's a first attempt at using TQuery and a
return to Delphi after years away from it. Here goes:

  var
    garage_file : textfile;
    line_temp   : string;
    check_num   : string;
    proposal    : string;
begin
  if fileExists('q:\garage\garage.sql') then
  begin
    assignfile(garage_file, 'q:\garage\garage.sql');
    reset(garage_file);
    statusBar1.SimpleText := 'Connecting to FM database';
    try
      readln(garage_file);
      while not EOF(garage_file) do
      begin
        readln(garage_file, line_temp);
        check_num := copy(line_temp, 32, 8);
        readln(garage_file, line_temp);
        proposal  := copy(line_temp, length(line_temp) - 9, 8);
        FMQuery.OpenDatabase;
        FMQuery.SQL.Clear;
        FMQuery.SQL.Add('Select root.ae_p_pro_e.proposal, ');
        FMQuery.SQL.Add('root.ae_i_pet_e.check_num from root.ae_p_pro_e,
');
        FMQuery.SQL.Add('root.ae_i_pet_e where root.ae_p_pro_e.proposal
');
        FMQuery.SQL.Add('= :proposal and root.ae_i_pet_e.check_num ');
        FMQuery.SQL.Add('= :check_num');
        FMQuery.Params[0].AsString := proposal;
        FMQuery.Params[1].AsString := check_num;
        FMQuery.Prepare;
        FMQuery.Open;
        if (VarToStr(FMQuery['PROPOSAL']) <> proposal) or
            (VarToStr(FMQuery['CHECK_NUM']) <> check_num) then
          Memo.Lines.Add(VarToStr(FMQuery['PROPOSAL']) + ' '
          + VarToStr(FMQuery['CHECK_NUM']));
        FMQuery.Close;
        FMQuery.Unprepare;
========================================

Again, any and all help is appreciated.

Re:Working with TQuery Result Set


Quote
Dave Hull wrote:

> You want code? Well, it's ugly, it's a first attempt at using TQuery and a
> return to Delphi after years away from it. Here goes:

>         FMQuery.Prepare;
>..
>         FMQuery.Unprepare;

The code looks quite ok. I just noticed those Prepare and Unprepare lines,
they are not necessary here.

The Delphi manuals and Helps are quite short worded with Prepare/Unprepare.
So it took me almost a year before I realized the right usage of
these two commands:)

When running some Query only once, with one set of Parameters, Prepare/
Unprepare brings you no benefit. It does not speed up things, but
fortunately it does not do any harm either.

But if you run your query in a loop with let's say 1000 different Parameter
values, then you Prepare the Query _once_, then do the loop with 1000 different
Parameter values, and after it you Unprepare the Query.
This tells the SQL engine, NOT to try to touch or optimize the SQL query
within every loop. It does it only once, with the first set of parameters.

This will speed up things a bit, and your Query may be ready a few secs
earlier. With current 1000+ Mhz machines the benefit starts to be very
small anyway, at least with local databases.
The CPU has power, and enough clock cyckles to process also the needless
SQL optimization even full 1000 times, when waiting the slow hard disks
and memory to return the result set.

Markku Nevalainen

Re:Working with TQuery Result Set


Quote
On Fri, 26 Oct 2001, Markku Nevalainen wrote:
> Dave Hull wrote:

> > You want code? Well, it's ugly, it's a first attempt at using TQuery and a
> > return to Delphi after years away from it. Here goes:

> >         FMQuery.Prepare;
> >..
> >         FMQuery.Unprepare;

> The code looks quite ok. I just noticed those Prepare and Unprepare lines,
> they are not necessary here.

Thanks for the advise. I'll make that change to the code, but your
response doesn't help me with regard to my original question. In the
posted code the FMQuery['PROPOSAL'] and FMQuery['CHECK_NUM'] are always
coming back NULL even though the values I'm selecting are in the
database. And when I select for values that I know are not in the
database, these FMQuery.Fields are still coming back NULL.

What am I missing?

Thanks.

Re:Working with TQuery Result Set


Quote
Dave Hull wrote:

>     FMQuery.SQL.Add('Select root.ae_p_pro_e.proposal, ');
>     FMQuery.SQL.Add('root.ae_i_pet_e.check_num from root.ae_p_pro_e,');
>     FMQuery.SQL.Add('root.ae_i_pet_e where root.ae_p_pro_e.proposal');
>     FMQuery.SQL.Add('= :proposal and root.ae_i_pet_e.check_num ');
>     FMQuery.SQL.Add('= :check_num');

To me it looks like you are trying to select combination from
two Tables. There both of these tables have fields 'root.ae_p_pro_e.proposal'
and 'root.ae_i_pet_e.check_num'.

And all those fields must also have the values that you have
in your two Parameters. If any of those four fields in the two
tables does not match with Parameters, then you get zero records.

Then the rest of your comparisons, would also be meaningless:
 if (VarToStr(FMQuery['PROPOSAL']) <> proposal)

Well, that's what I quickly thought, your tables, field names
and parameters are a bit complicated:)

Try to temporarily remove your Parameters, and write the parameter
values directly in your code. This will simplify any lengthy,
complicated SQL problem.
Maybe also drop a DbGrid on your Form, so you'll quickly see
how the changes to TQuery component?s SQL clauses affects the
result.  
Temporarily Drop the second table away from your SQL sentence,
again to see how it affects, etc.

Good luck.

Markku Nevalainen

Re:Working with TQuery Result Set


Quote
On Sat, 27 Oct 2001, Markku Nevalainen wrote:
> Dave Hull wrote:

> >     FMQuery.SQL.Add('Select root.ae_p_pro_e.proposal, ');
> >     FMQuery.SQL.Add('root.ae_i_pet_e.check_num from root.ae_p_pro_e,');
> >     FMQuery.SQL.Add('root.ae_i_pet_e where root.ae_p_pro_e.proposal');
> >     FMQuery.SQL.Add('= :proposal and root.ae_i_pet_e.check_num ');
> >     FMQuery.SQL.Add('= :check_num');

> To me it looks like you are trying to select combination from
> two Tables. There both of these tables have fields 'root.ae_p_pro_e.proposal'
> and 'root.ae_i_pet_e.check_num'.

This is correct.

Quote
> And all those fields must also have the values that you have
> in your two Parameters. If any of those four fields in the two
> tables does not match with Parameters, then you get zero records.

How do I find out the number of records that have been returned? If I
could find this out I could drop the comparisons altogether.

Quote
> Well, that's what I quickly thought, your tables, field names
> and parameters are a bit complicated:)

Yes, the table names are incredibly complicated, this was not my doing and
I cannot change them due to the proprietary software that depends on these
table names. It sucks. Yes.

Quote
> Try to temporarily remove your Parameters, and write the parameter
> values directly in your code. This will simplify any lengthy,
> complicated SQL problem.

I'll give that a shot.

Quote
> Maybe also drop a DbGrid on your Form, so you'll quickly see
> how the changes to TQuery component?s SQL clauses affects the
> result.

I have done this and the DBGrid completely fills up with proposal and
check_num. Every comparison fails because every value returned is NULL.

Quote
> Temporarily Drop the second table away from your SQL sentence,
> again to see how it affects, etc.

Thanks again. I'll try your suggestions and see what happens.

Re:Working with TQuery Result Set


Quote
On Mon, 29 Oct 2001, Dave Hull wrote:
> On Sat, 27 Oct 2001, Markku Nevalainen wrote:

> > Dave Hull wrote:

> > >     FMQuery.SQL.Add('Select root.ae_p_pro_e.proposal, ');
> > >     FMQuery.SQL.Add('root.ae_i_pet_e.check_num from root.ae_p_pro_e,');
> > >     FMQuery.SQL.Add('root.ae_i_pet_e where root.ae_p_pro_e.proposal');
> > >     FMQuery.SQL.Add('= :proposal and root.ae_i_pet_e.check_num ');
> > >     FMQuery.SQL.Add('= :check_num');

> > in your two Parameters. If any of those four fields in the two
> > tables does not match with Parameters, then you get zero records.

I'm only selecting two values not four, but yes, what you're saying seems
right to me. If the table doesn't contain rows that would match the
parameters then nothing should be returned.

However, I've made up bogus entries to query for and TQuery.FieldCount
still has a value of two indicating there was a match. In all cases,
regardless of whether I'm querying for data in the database or completely
fabricated data the FieldCount is always two and the values of the fields
are always NULL.

Could this be a problem with the way the BDE interacts with Oracle? Or is
there something else I'm not catching on to?

Re:Working with TQuery Result Set


Quote
Dave Hull wrote:

> However, I've made up bogus entries to query for and TQuery.FieldCount
> still has a value of two indicating there was a match.

It looks like you were checking wrong property. Your Query selected
two Fields, and TQuery.FieldCount will also report there being two
Fields.

Instead, try checking what the TQuery.RecordCount says.

Note: There's something mysterious with Query's RecordCount. When the
right record count is zero records, occasionally RecordCount returns 1,
claiming there is one record.

I have found this kind check return more reliable result with
empty Query result sets:
  if TQuery.BOF and TQuery.EOF then        //The Query dataset is empty

Markku Nevalainen

Re:Working with TQuery Result Set


Quote
"Markku Nevalainen" <m...@iki.fi> wrote in message

news:3BDDAAEF.7EF5@iki.fi...

Quote
> It looks like you were checking wrong property. Your Query selected
> two Fields, and TQuery.FieldCount will also report there being two
> Fields.

> Instead, try checking what the TQuery.RecordCount says.

The value of TQuery.RecordCount is always 0. Regardless of whether I'm
querying for valid data or not. Go figure. TQuery.FieldCount was always 2.

Any other suggestions? I've tried dropping the parameters and querying for
both valid and invalid data first from one table, then from two tables. I'm
always getting back FieldValues that are NULL. I've tried using a packet
sniffer between my machine and the database to confirm what's being sent
across the network and I have verified that the server is receiving the
queries correctly, but the result set is always NULL.

This doesn't make sense to me.

Thanks again for your help.

Re:Working with TQuery Result Set


The mystery continues... I used a packet sniffer to watch what was coming
and going across the wire on these selects. Here's what's happening from
within Delphi:

Select root.ae_p_pro_e.proposal,
root.ae_i_pet_e.check_num from root.ae_p_pro_e,
root.ae_i_pet_e where root.ae_p_pro_e.proposal
= '02-48553' and root.ae_i_pet_e.check_num
= 'BH524787'

The reply back from the Oracle server inludes this:

02-48553       BH524787

Which is the correct data, then this comes across the wire:

ORA-01403: no data found

Not sure why that is happening. Interestingly enough, I get the exact same
information back and forth across the wire when using SQLPlus, Oracle's SQL
client.

The crux of the problem is that the TQuery.Fields values are still coming up
NULL, when they should have values in them. The code I'm using has been
posted here already, so if you need to see it look through the thread. If I
can't get this problem solved soon, I may have to resort to a Perl or PHP
solution. I'd prefer this to be done in Delphi, but management only has so
much patience before they want to see results. Ugh.

Thanks.

Re:Working with TQuery Result Set


Dave,
Have you tried starting with a simple one-table query such as SELECT *
FROM root WHERE somefield = somevalue

This should eliminate the question of where the problem is: in the sql
statement or in your Delphi program. If this works OK, then I would
start changing the sql text to link in the second table, then start
restricting the result set with WHERE clauses to get to where you need
to be.

If you can't get the data into your grid or dbedits with the simple
query, then the problem may be in how you have set the properties in
the various Delphi objects.

HTH,
Dan

Quote
On Tue, 30 Oct 2001 09:26:01 -0600, "Dave Hull" <dph...@ku.edu> wrote:
>The mystery continues... I used a packet sniffer to watch what was coming
>and going across the wire on these selects. Here's what's happening from
>within Delphi:

>Select root.ae_p_pro_e.proposal,
>root.ae_i_pet_e.check_num from root.ae_p_pro_e,
>root.ae_i_pet_e where root.ae_p_pro_e.proposal
>= '02-48553' and root.ae_i_pet_e.check_num
>= 'BH524787'

>The reply back from the Oracle server inludes this:

>02-48553       BH524787

>Which is the correct data, then this comes across the wire:

>ORA-01403: no data found

>Not sure why that is happening. Interestingly enough, I get the exact same
>information back and forth across the wire when using SQLPlus, Oracle's SQL
>client.

>The crux of the problem is that the TQuery.Fields values are still coming up
>NULL, when they should have values in them. The code I'm using has been
>posted here already, so if you need to see it look through the thread. If I
>can't get this problem solved soon, I may have to resort to a Perl or PHP
>solution. I'd prefer this to be done in Delphi, but management only has so
>much patience before they want to see results. Ugh.

>Thanks.

--
Dan Brennand
CMDC systems, inc.
Configuration Management and Document Control:
visit us at www.cmdcsystems.com
[SPAM block: my e-mail address is DanB at CMDCsystems.com]

Re:Working with TQuery Result Set


In article <Pine.LNX.4.33.0110260929350.631-100...@insipid.cc.ukans.edu>,
dph...@insipid.com says...

Quote

>You want code? Well, it's ugly, it's a first attempt at using TQuery and a
>return to Delphi after years away from it. Here goes:
>        FMQuery.SQL.Add('Select root.ae_p_pro_e.proposal, ');
>        FMQuery.SQL.Add('root.ae_i_pet_e.check_num from root.ae_p_pro_e,
>');
>        FMQuery.SQL.Add('root.ae_i_pet_e where root.ae_p_pro_e.proposal
>');
>        FMQuery.SQL.Add('= :proposal and root.ae_i_pet_e.check_num ');
>        FMQuery.SQL.Add('= :check_num');

What's the relationship between the two tables?  Does there need to be a line
in the where clause that says root.ae_i_pet_e.??? = root.ae_p_pro_e.???

If it's too much of a pain to find - what about a work around?  Instead of
using the params of the tquery, use stringreplace on the parameters.
Change
    FMQuery.Params[0].AsString := proposal;
    FMQuery.Params[1].AsString := check_num;
to
    FMQuery.sql.text :=
        stringreplace(fmquery.sql.text,':proposal',proposal,[rfReplaceAll]);
    FMQuery.sql.text :=
        stringreplace(fmquery.sql.text,':check_num',check_num,[rfReplaceAll]);

klint.

Re:Working with TQuery Result Set


Thanks to everyone for the help. I finally figured out a solution to the
problem. I gave up the parameters for the string concatenating operator
(+) to substitute the changing values. Once I got the quote marks worked
out, this method started returning the results I expected.

Thanks again.

Go to page: [1] [2]

Other Threads