Hello all,

There is a new odbc driver (version 6.30.0237) and source code at our
website (http://www.insightdist.com/psqlodbc).  This one contains some
bug fixes AND alot of setup options!  You can now configure the driver
more closely to match your needs hopefully.  Please click on the version
link and/or the "dialog boxes" link for more detailed information.  Some
quick highlights are:

- By democratic vote, uses ISO datestyle, period!  Automatically sets
this datestyle to backend on connection.
- Advanced options for Driver and Datasource (and lots of them).
- Ability to control whether cursors are used, thus emulating the old
driver/libpq behavior.
- Ability to control how unknown sizes are reported: "Longest" emulates
old driver/libpq behavior.
- Recognizing Unique indexes is a driver option.
- Some data type mappings and data type sizes are configurable.
- SQLExtendedFetch implemented when not using cursors.

There is also a "Defaults" button to set all these options back to
optimum settings.

Note, there are performance issues involved with not using cursors,
since the driver must suck down all the rows in the result set.
However, there are some advantages when it comes to updating tables
since the tables are not kept locked by the backend.  If you are having
problems with locked tables or the driver hanging, try setting Use
Cursors to false.  This is a workaround until whenever the backend
locking improves.  Also, when not using cursors, the sizes of character
data types varchar and text can be known since all the tuples are
retrieved in the result set.  So if you set the option "Longest" on the
Advanced Options (Driver),  this will be possible.


Feedback on this new driver is appreciated.

Regards,

Byron.


Fri, 08 May 1998 12:35:19 -0400
Byron Nikolaidis <byronn@insightdist.com>
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/>


Fri, 8 May 1998 17:49:36 PDT
"Chris Osborn" <fozztexx@talia.hq.vxn.com>
Re: [INTERFACES] NEW ODBC DRIVER
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/>


Fri, 8 May 1998 18:12:56 PDT
"Chris Osborn" <fozztexx@talia.hq.vxn.com>
Re: [INTERFACES] NEW ODBC DRIVER
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:

> 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/>




Sat, 09 May 1998 20:12:21 -0400
Byron Nikolaidis <byronn@insightdist.com>
Re: [INTERFACES] NEW ODBC DRIVER
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:

> 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/>




Sat, 09 May 1998 20:15:37 -0400
Byron Nikolaidis <byronn@insightdist.com>
Re: [INTERFACES] NEW ODBC DRIVER
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/>

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

> 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.


Mon, 11 May 1998 10:45:09 PDT
"Chris Osborn" <fozztexx@talia.hq.vxn.com>
Re: [INTERFACES] NEW ODBC DRIVER


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


Mon, 11 May 1998 08:35:29 -0400
Byron Nikolaidis <byronn@insightdist.com>
Re: NEW ODBC DRIVER
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'


Mon, 11 May 1998 13:25:03 +0000 (UTC)
"Jose' Soares Da Silva" <sferac@bo.nettuno.it>
Re: NEW ODBC DRIVER
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

Mon, 11 May 1998 18:15:24 +0200
Sbragion Denis <infotecn@tin.it>
Re: [INTERFACES] Re: NEW ODBC DRIVER
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



Mon, 11 May 1998 14:42:16 -0400
Byron Nikolaidis <byronn@insightdist.com>
Re: [INTERFACES] Re: NEW ODBC DRIVER
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:

> So what can I do to sort in Access '97? I doubt the backend will
> be getting changed anytime soon.
>
> On May. 09 98, 17:12 PDT, "Byron Nikolaidis" <byronn@insightdist.com>
> wrote:
>
> > 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.
Mon, 11 May 1998 15:20:58 -0400
David Hartwig <daveh@insightdist.com>
Group/Order by not in target - Was [NEW ODBC DRIVER]
> > 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.


Tue, 12 May 1998 09:50:22 +0100
"Olaf Mittelstaedt" <mstaedt@va-sigi.va.fh-ulm.de>
Re: NEW ODBC DRIVER
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'


Tue, 12 May 1998 11:02:43 +0000 (UTC)
"Jose' Soares Da Silva" <sferac@bo.nettuno.it>
Re: [INTERFACES] Re: NEW ODBC DRIVER
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'


Tue, 12 May 1998 12:17:56 +0000 (UTC)
"Jose' Soares Da Silva" <sferac@bo.nettuno.it>
Re: [INTERFACES] Re: NEW ODBC DRIVER
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, 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'




Tue, 12 May 1998 10:37:08 -0400
Byron Nikolaidis <byronn@insightdist.com>
Re: [INTERFACES] Re: NEW ODBC DRIVER
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!


Fri, 15 May 1998 13:34:04 +0000 (UTC)
"Jose' Soares Da Silva" <sferac@bo.nettuno.it>
Re: [INTERFACES] Group/Order by not in target - Was [NEW ODBC DRIVER]
> 
> 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)

Fri, 15 May 1998 09:51:07 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Re: [INTERFACES] Group/Order by not in target - Was
	[NEW ODBC DRIVER]