Since my recent understanding with optimizing case-insensitive index keys,
I have modifed a lot of my queries to be better optimised. The problem is,
a lot of my queries are now returning a zero recordcount!

ie I use a TDBISAMQuery component with a query of this form

SELECT * FROM translate_boxstatus WHERE (providerid=1) AND (lower(statusfrom)='o')
<--works

This works just fine, but is not optimised. So I modify the query to be

SELECT * FROM translate_boxstatus WHERE (providerid=1) AND (lower(statusfrom)=lower('o'))
<-- not working

statusfrom : string field, indexed and case-insensitive

all I am doing here is to add 'lower()' to both sides of the comparison to
force search optimisation.

Now - this does NOT work. It compiles, and runs and eveything, but no record
is ever returned. And this is on a few different TDBISAMQuery components,
and with different queries (but all of the same general type).

The weird this is I can use DBSYS and enter the same query there, and it
works. ie pointing to the same database folder, etc. The thing is, as far
as I can see, it should work just fine. Why don't DBISAMQUery components
in *MY* program work with the exact same query?

So - my first question is - and I doing the optimising correctly?

Secondarly - anyone have any idea why my queries do not work at runtime?

Kerry


If it works in DBSys and doesn't work in your program then there is something different. When a query in one returns different data to a query in the other then its almost certainly the data (the other could be a DBSys compiled with a different version of DBISAM).

I would double and triple check that the data is the same and also check that the queries are the same. Put a query.sql.savetofile in your program and run that code in DBSys.

Roy Lambert