Обсуждение: NEW ODBC DRIVER
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.
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/>
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/>
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/>
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/>
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'
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
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/> 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.
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: > 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.
Вложения
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'
> > 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.
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'
Re: [INTERFACES] Group/Order by not in target - Was [NEW ODBC DRIVER]
От
"Jose' Soares Da Silva"
Дата:
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!