I've discovered the same problem with Access97 and ODBCLink/SE-32
when querying ImageSQL attached tables .
This problem did not exist with Access 2.0 and ALLBASE dll.

I've traced this in the log file, and your right, MS Access does a
query like you describe in 2 phases.

It's as if it creates a cursor on the client, getting the keys first,
then going back and getting the data on the second pass.

The problem with this approach, is when sorting on something other
than the key field, the first phase select looks like:

SELECT FIELD1 FROM TABLE ORDER BY FIELD3;

which is invalid SQL syntax(for ALLBASE and in the SQL definition,
you must include the sort field in the return set).

However, this is VALID syntax for MS Access databases and MS SQL
Server databases.  They 'embrace and extend' the SQL standard such
that you can sort on a field not included in the select set.

So, I don't think the problem is with ODBC, but with MS Access's
method of doing business(although I could be wrong).
I haven't found a workaround for this yet.

Perhaps Birket can shed some light on his favourite 'client from
hell'.

Bob.

On  7 Oct 99 at 15:20, Mark Wilkinson wrote:

> Hi All,
>
> Maybe this has already been discussed but I've found a rather obvious bug in the
> ODBC driver (ODBCLINK/SE) supplied by HP with 5.5.
>
> I'm using MS-Access, linking in the tables and building queries. It seems that
> the driver is doing some kind of optimisation to get some efficiency saving by
> doing keyed reads wherever possible. If I've got a dataset with 3 fields field1
> field2 and field3 and field1 is a (unique primary) key item, then the extract
> seems to be done in two phases..
>
> 1. select just the key item.
> 2. Go back in and read the rest of the data as determined by the original SELECT
>
> Unfortunately, if you define an ORDER BY clause, this is attempted at step 1 and
> if field3 is the sortkey then phase 1 fails.
>
> The SQL actually passed in phase 1 would look like this:-
>
> >select field1 from file1 order by field3;
>
> Big syntax error - Column field3 does not exist. You have to select the field in
> order to sort on it.
>
> Do we have a patch for this?
>
> Cheers.
>
> Mark W.
> SPE.
>
--=--=--
Bob Walker, Computer Centre  --  [log in to unmask]
Capilano College, North Vancouver, BC, CANADA.
------
'No one is listening until you make a mistake.'