tquery help

[Ed]

Hi,

I have the following code that attempts to access a foxpro
table via BDE<->ODBC. This is a console app so no forms
are used. I'm using RAD2007 on a Vista machine.

program chprj_console;

{$APPTYPE CONSOLE}

uses
SysUtils,
DB,
DBTables;

var
q1, q2 : TQuery;

begin
q1 := TQuery.Create(nil);
q2 := TQuery.Create(nil);

try
q1.databasename := 'tabletest';

q1.SQL.clear;
q1.SQL.text := 'select * from stock where stkno = :pStkno';
q1.ParamByName('pStkno').value := 'KDT1101';
q1.active := true;
if (q1.RecordCount > 0) then
begin
writeln('yes');
end
else
begin
writeln('no');
end;
readln;

except
on E:Exception do
Writeln(E.Classname, ': ', E.Message);
end;
end.

I've done this countless times before and before, it
would work. But suddenly, it just stopped working in
the sense it keeps on printing 'No'.

I know the stock # exists. So my suspicion is in
the possibility that the database *isn't* opened
or connected. Normally it would ask for the
username and password. Now it doesn't. But if
it can't access a table (due to database connection
issues), wouldn't the IDE complain?

The problem is, if I create a new form, add a
Tdatasource, tquery, tdbgrid and link it up as
given above, (Tdbgrid.datasource -> Tdatasource,
Tdatasource-> tquery) it works. The line is shown
on the grid.

Am I missing something?

Any help apprecaited

Thanks

Edmund

[Ed]

Ed wrote:
> Hi,
>
> I have the following code that attempts to access a foxpro
> table via BDE<->ODBC. This is a console app so no forms
> are used. I'm using RAD2007 on a Vista machine.
>
> program chprj_console;
>

As an addendum, and possibly future reference, it seems
as if I boffed up my code. I was reading up on the
TQuery.Recordcount and realize that it's always -1.

So clearly it will always say 'no'. Then it hit me,
and if someone can point out if this is the right
way, I'd appreciate it. Basically, instead of
checking for recordcount, couldn't I check if
it is EOF? What about using the 'rowsaffected'?

(No, it doesn't seem as if rowsaffected is the right
property to check. I don't understand why though.)

It seems as if the EOF test 'works'.

Can someone please explain what's going on? I can
'understand' the EOF working (since what I'm doing
is basically filtering the table, albeit using
the SQL select statement), but if I'm doing that
wouldn't ROWSAFFECTED and RECORDCOUNT work as well
(by default)? I'm not sure how I can come about
in 'redefining' recordcount in a descendant class.

Thanks

Edmund

[Michael Scherr]

Hi.

I never trust RecordCount.

Why don't you use sql-count?:

q1.SQL.text := 'select count(*) from stock where stkno = :pStkno';

q1.ParamByName('pStkno').value := 'KDT1101';
q1.active := true;

if (q1.Fields.Field[0].AsInteger > 0) then

Rgds,
Michael

Ed schrieb:

[Bill Todd [TeamB]]

TQuery.IsEmpty

--
Bill Todd (TeamB)

[Wayne Niddery (TeamB)]

"Ed" <e...@kdtc.net> wrote in message
news:483f68f2$1...@newsgroups.borland.com...

>
> Can someone please explain what's going on? I can
> 'understand' the EOF working (since what I'm doing
> is basically filtering the table, albeit using
> the SQL select statement), but if I'm doing that
> wouldn't ROWSAFFECTED and RECORDCOUNT work as well

While there might be differences depending on the specific database drivers
involved (besides the BDE itself), generally the BDE will not automatically
update RecordCount when dealing with anything other than its own
Paradox/Dbase tables. The reason is that, especially in the case of SQL
databases, getting an exact record count can be 1) expensive, time-wise, and
2) as soon as you get it, it could be wrong if dealing with a multi-user
system. Instead, RecordCount will reflect, at best, how many records have
actually been fetched, which is typically not very important.

RowsAffected only applies to SQL insert/update/delete statements, not
selects.

--
Wayne Niddery - TeamB (www.teamb.com)
Winwright, Inc. (www.winwright.ca)

[Ed]

Bill Todd [TeamB] wrote:
> TQuery.IsEmpty
>

Quite simple. Don't understand why I didn't see IsEmpty.

Thanks Bill!

Edmund

[Ed]

Michael Scherr wrote:
> Hi.
>
> I never trust RecordCount.
>
> Why don't you use sql-count?:
>
> q1.SQL.text := 'select count(*) from stock where stkno = :pStkno';
> q1.ParamByName('pStkno').value := 'KDT1101';
> q1.active := true;
> if (q1.Fields.Field[0].AsInteger > 0) then
>
> Rgds,
> Michael

The reason being is that would the 'select count(*) .." just
return a 'count(*)' result instead of the actual list of
matched records?

Edmund

[Ed]

Wayne Niddery (TeamB) wrote:
> "Ed" <e...@kdtc.net> wrote in message
> news:483f68f2$1...@newsgroups.borland.com...
>>
>> Can someone please explain what's going on? I can
>> 'understand' the EOF working (since what I'm doing
>> is basically filtering the table, albeit using
>> the SQL select statement), but if I'm doing that
>> wouldn't ROWSAFFECTED and RECORDCOUNT work as well
>
> While there might be differences depending on the specific database
> drivers involved (besides the BDE itself), generally the BDE will not
> automatically update RecordCount when dealing with anything other than
> its own Paradox/Dbase tables. The reason is that, especially in the case
> of SQL databases, getting an exact record count can be 1) expensive,
> time-wise, and 2) as soon as you get it, it could be wrong if dealing
> with a multi-user system. Instead, RecordCount will reflect, at best,
> how many records have actually been fetched, which is typically not very
> important.

Thanks for the clarification, Wayne. My understanding of database
access has been limited to local tables(incl. via BDE accessed
tables) so with the foray into SQL access, it has been quite
slow going.

Edmund

[Ed]

If given this, and it was found, what is the best
way of actually 'updating' this record? Do
I use SQL commands or do I just simply Append(),
change and post;?

Thanks

Edmund

[Bill Todd [TeamB]]

Ed wrote:

> If given this, and it was found, what is the best
> way of actually 'updating' this record? Do
> I use SQL commands or do I just simply Append(),
> change and post;?

Edit, change then post is the easiest in this case.

--
Bill Todd (TeamB)

[Ed]

Doesn't seem to like me doing that. It keeps on
saying I can't update a read only dataset.

Apparently, using sql queries cannot be used with
Edit/POst commands? I don't know. I looked up
the SQL Update command and tried it out. So far,
it seems to be working on a non-shared set of
tables.

Thanks

Ed

[Paul Hughes]

"Ed" <e...@kdtc.net> wrote in message

news:4844bf9c$1...@newsgroups.borland.com...

> Doesn't seem to like me doing that. It keeps on
> saying I can't update a read only dataset.
>
> Apparently, using sql queries cannot be used with
> Edit/POst commands? I don't know. I looked up
> the SQL Update command and tried it out. So far,
> it seems to be working on a non-shared set of
> tables.
>

Some ODBC drivers I have seen are read only. However, if the one you use is
read/write, check that you have set the RequestLive property to true on the
query component. Without that, it will always return a read only dataset.

Also, using local sql (BDE), adding an order by clause will force it to
return a read only dataset.

Regards, Paul.

[Bill Todd [TeamB]]

Is the TQuery.RequestLive property set to true? If it still does not
work it must be a limitation of the FoxPro driver. Your query meets the
requirements for being updateable.

--
Bill Todd (TeamB)