NEW ODBC DRIVER

I just downloaded it, and I cannot get any queries that have an
"ORDER BY" in them to work. Access '97 keeps returning an ODBC call
failed error, and gives me the message:

ERROR: The field being ordered must appear in the target list (#1)

If I cut and paste the SQL code directly into a psql session, it
runs just fine.

---
Chris Osborn, Network Administrator T3West/WebCow!
707 255 9330 x225 - Voice 1804 Soscol Ave, #203
707 224 9916 - Fax Napa, CA 94559
<fozztexx@t3west.com> <http://t3west.com/&gt;

It also keeps insisting that the connection is read only, even
though I unchecked the read only boxes in both the Driver settings
and Advanced settings.

---
Chris Osborn, Network Administrator T3West/WebCow!
707 255 9330 x225 - Voice 1804 Soscol Ave, #203
707 224 9916 - Fax Napa, CA 94559
<fozztexx@t3west.com> <http://t3west.com/&gt;

Access insists on using order by clauses like that, even though the
driver returns information saying it can't support it!

Postgres simply can't handle order by clauses without the fields also
being in the target.

The error you see is a legitimate error coming from the backend.

When the backend can handle those kinds of order by clauses, the error
will stop happening.

Byron

Chris Osborn wrote:

Hello,

For Access to be able to update records it must have a unique key.
Access 95 and 97 can ask for the key if the driver doesn't automatically
return it.
Access 2.0 can not ask and relies completely on the driver to return the
unique key.

There is a setting in the Driver Options dialog which controls how this
happens.
It is called "Recognize Unique Indexes". If checked, the driver will
automatically return the information. If not checked (the default), it
will not return a unique index.

Hope this helps.

Byron

Chris Osborn wrote:

Jose' Soares Da Silva wrote:

I have a problem with types.
I created a table with a column of type money and another with type
bool, Access translate money to numeric (double precision) and bool to
text.
I thought that Access recognized this types as Money and Yes/No.
Is it an ODBC or a PostgreSQL problem ?
Thanks, Jose'

In my tests, Access never bothered to retrieve the information returned by
the driver which says that the field is a MONEY type. I chose to make it a
numeric, but I could make it character, which would allow you to see the
money symbols, but I'm not sure if you could perform calculations on it?

As for the BOOL problem, I tried to return it as a SQL_BOOL, but Access
displayed it as 0=FALSE, and (-1)=TRUE. Why does TRUE translate to a -1, I
have no idea. But for that reason, I chose to make it a character type
instead.

I could add options to the setup dialog for handling these two types, if
anyone's interested.

Byron

On Fri, 8 May 1998, Byron Nikolaidis wrote:

Hello all,

There is a new odbc driver (version 6.30.0237) and source code at our
...
Feedback on this new driver is appreciated.

I just down load the ODBC 6.30.0238
and seems that it works well with Access. Thanks to Byron. Great job!

I have a problem with types.
I created a table with a column of type money and another with type
bool, Access translate money to numeric (double precision) and bool to
text.
I thought that Access recognized this types as Money and Yes/No.
Is it an ODBC or a PostgreSQL problem ?
Thanks, Jose'

Hello,

At 08.35 11/05/98 -0400, Byron Nikolaidis wrote:

As for the BOOL problem, I tried to return it as a SQL_BOOL, but Access
displayed it as 0=FALSE, and (-1)=TRUE. Why does TRUE translate to a -1, I
have no idea. But for that reason, I chose to make it a character type
instead.

This is an MS brain damage implementation of Booleans. It is used this way
starting from MS Access 1.0 up to VB 5.0. I don't know why MS decided to
use this convention in the early MS Access 1.0 age but for compatibility
reason they had to retain it up to the most recent version of their
development programs.

Bye !

Dr. Sbragion Denis
InfoTecna
Tel, Fax: +39 39 2324054
URL: http://space.tin.it/internet/dsbragio

So what can I do to sort in Access '97? I doubt the backend will
be getting changed anytime soon.

---
Chris Osborn, Network Administrator T3West/WebCow!
707 255 9330 x225 - Voice 1804 Soscol Ave, #203
707 224 9916 - Fax Napa, CA 94559
<fozztexx@t3west.com> <http://t3west.com/&gt;

On May. 09 98, 17:12 PDT, "Byron Nikolaidis" <byronn@insightdist.com>
wrote:

Sbragion Denis wrote:

Hello,

At 08.35 11/05/98 -0400, Byron Nikolaidis wrote:

As for the BOOL problem, I tried to return it as a SQL_BOOL, but Access
displayed it as 0=FALSE, and (-1)=TRUE. Why does TRUE translate to a -1, I
have no idea. But for that reason, I chose to make it a character type
instead.

This is an MS brain damage implementation of Booleans. It is used this way
starting from MS Access 1.0 up to VB 5.0. I don't know why MS decided to
use this convention in the early MS Access 1.0 age but for compatibility
reason they had to retain it up to the most recent version of their
development programs.

OK,

I'm gonna make it an option. But, as I mentioned before, there are some
weirdnesses with Access. Here's another weird thing with the way it handles
NULL SQL_BIT columns.

If I have my Postgres bool column, and it contains a NULL, Access automatically
displays it as "0". Then if I try to update the record, it uses the "0" in the
where clause. Well guess what, no records are updated because the "0" doesn't
match the NULL in the record, and you get this ugly message about a user
conflict!

When BOOLS are handled as character data, this doesnt happen of course.

Anybody got any ideas about this?

Byron

I suspect that it is only going to be the MS Access 97 users that are
going to suffer from this weakness in the backend. I believe Access is
trying to optimize somehow by breaking a single multi-join statement into
multiple statements. To do this Access must be joining on the client
side based on a relative row position rather than the specified join
columns.

Until the problem is resolved in the backend, the workaround is to
explicitly include the missing attributes in the query. In my
experience, the missing attributes are usually from one or more sides of
any join clauses. But, because Access is not showing the actual
statements it is sending to the backend, you will have to guess the
attributes until the query succeeds. (You could also look at the log
file)

It is not very difficult to produce this problem in MS Access 97; I
expect my users to beat me up pretty good on this issue. Thus, I plan to
look into making the fix in the backend myself. Conceptually it does not
seem too difficult.

1. Add a hidden attribute to the target node structure.

2. Modify the parser/analyzer to add any attributes in the GROUP/ORDER BY
clause that are missing from the target list, to the target list with the
hidden attribute set.

3. Strip the hidden nodes from the target list projection of the query.

4. Add the feature to HAVING clause?

Any, hints, comments, or objections?

Chris Osborn wrote:

I have hit the Access 97 problem of ORDER BY not in target list with a
simple, one-table query. The problem does not seem to have anything to do
with joins.

I have logged the SQL sent to the back end with the CommLog option in the
238 driver (very useful, thanks Byron). When using ORDER BY, Access 97
first sends a query to retrieve just the key(s) and then sends another to
get the required data. Hence, for the first query unless the fields used
in ORDER BY are key fields they are not in the target list. Nice one MS!
Nothing like making your software do the obvious thing, is there?

There is a work around. First create your query the usual way (point and
click if you like). Next, display the SQL. Finally, convert it into a
PASSTHROUGH query, so that the backend receives your SQL as written. This
worked for my very simple test, but I have not checked it out for more
complex queries.

BTW, Access 2.0 does not seem to have this problem, so why this strange
behaviour has been introduced into 97 I cannot imagine.

Tony Cowderoy

At 08.35 11/05/98 -0400, Byron Nikolaidis wrote:

As for the BOOL problem, I tried to return it as a SQL_BOOL, but Access
displayed it as 0=FALSE, and (-1)=TRUE. Why does TRUE translate to a -1, I
have no idea. But for that reason, I chose to make it a character type
instead.

If I have my Postgres bool column, and it contains a NULL, Access automatically
displays it as "0". Then if I try to update the record, it uses the "0" in the
where clause. Well guess what, no records are updated because the "0" doesn't
match the NULL in the record, and you get this ugly message about a user
conflict!

When BOOLS are handled as character data, this doesnt happen of course.

Anybody got any ideas about this?

When migrating tables from Access 2.0 to an SQL Server (Informix,
Interbase or PostgreSQL) I'm using INT4 to simulate boolean values
(0=False, -1=True), all Access queries using boolean columns will
work as before with native Access tables:

"... WHERE (b=True);" selects all rows with column b == -1
"... WHERE (b=False);" selects all rows with column b == 0
"... WHERE (b is Null);" selects all rows with column b == NULL

Of course you can't issue queries like "...where (b = true);" on the
UNIX side.

Regards,
Olaf
--
Olaf Mittelstaedt - IuK - mittelstaedt@fh-ulm.de
Fachhochschule Ulm Prittwitzstr. 10 89075 Ulm
Tel.: +49 (0)731-502-8220 Fax: -8270

Tertium non datur.

On Mon, 11 May 1998, Byron Nikolaidis wrote:

Jose' Soares Da Silva wrote:

I have a problem with types.
I created a table with a column of type money and another with type
bool, Access translate money to numeric (double precision) and bool to
text.
I thought that Access recognized this types as Money and Yes/No.
Is it an ODBC or a PostgreSQL problem ?
Thanks, Jose'

In my tests, Access never bothered to retrieve the information returned by
the driver which says that the field is a MONEY type. I chose to make it a
numeric, but I could make it character, which would allow you to see the
money symbols, but I'm not sure if you could perform calculations on it?

Numeric should be OK, but Access doesn't read the data on money fields.
Access displays the word "#deleted" on the field instead of data.

Jose'

On Tue, 12 May 1998, Jose' Soares Da Silva wrote:

On Mon, 11 May 1998, Byron Nikolaidis wrote:

In my tests, Access never bothered to retrieve the information returned by
the driver which says that the field is a MONEY type. I chose to make it a
numeric, but I could make it character, which would allow you to see the
money symbols, but I'm not sure if you could perform calculations on it?

Numeric should be OK, but Access doesn't read the data on money fields.
Access displays the word "#deleted" on the field instead of data.

Please forget the above message, now it works. The reason for "#deleted"
probably was that Access put this field as the primary key.
Jose'

This sounds like a different problem. I have tested money columns and at least I
can see the data. Since the driver must convert the money type into a numeric,
maybe it is having trouble with your type of money. Didn't you say that you are
not using dollars?

Byron

Jose' Soares Da Silva wrote:

On Mon, 11 May 1998, David Hartwig wrote:

It is not very difficult to produce this problem in MS Access 97; I
expect my users to beat me up pretty good on this issue. Thus, I plan to
look into making the fix in the backend myself. Conceptually it does not
seem too difficult.

1. Add a hidden attribute to the target node structure.

2. Modify the parser/analyzer to add any attributes in the GROUP/ORDER BY
clause that are missing from the target list, to the target list with the
hidden attribute set.

This would be a great enhancement!
SQL92 specifies that columns in the ORDER BY must appear in the
SELECT clause, but this limitation has no sense, indeed many databases
already implement this enhancement.
Go for it!

On Mon, 11 May 1998, David Hartwig wrote:

It is not very difficult to produce this problem in MS Access 97; I
expect my users to beat me up pretty good on this issue. Thus, I plan to
look into making the fix in the backend myself. Conceptually it does not
seem too difficult.

1. Add a hidden attribute to the target node structure.

2. Modify the parser/analyzer to add any attributes in the GROUP/ORDER BY
clause that are missing from the target list, to the target list with the
hidden attribute set.

This would be a great enhancement!
SQL92 specifies that columns in the ORDER BY must appear in the
SELECT clause, but this limitation has no sense, indeed many databases
already implement this enhancement.
Go for it!

There already is code in the backend for Junk fields to be removed.  Not
sure what it does, though.
-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)