Re: [INTERFACES] Access'97 and ODBC

On Fri, 24 Apr 1998, Julia A.Case wrote:

Please see the Insight Dist site for a newer source and binary
distribution of the ODBC driver

http://www.insightdist.com/psqlodbc

Julie

Thanks Julie. Now it works, but now I have a little problem about
date formats.
I have a table with field1 DATE and field2 TIMESTAMP. If I insert data
into these fields, field2 looks OK, but Access97 show me a strange
date on field1.

This is Access97 output:
field1: 27/7/99
field2: 1998-04-27 12:20:21+02

This is psql output:
Field | Value
-- RECORD 0 --
field1| 1998-04-27
field2| 1998-04-27 12:20:21+02
----
PS: My DateStyle is setting to 'ISO'
Jose'

Hello,

The ODBC driver can not yet handle multiple datestyle formats. Currently,
it expects dates to be in US format. There will be a future option that
allows you to configure that for the driver or per datasource.

Byron

Jose' Soares Da Silva wrote:

Jose' Soares Da Silva wrote:

I have a table with field1 DATE and field2 TIMESTAMP. If I insert data
into these fields, field2 looks OK, but Access97 show me a strange
date on field1.

This is Access97 output:
field1: 27/7/99
field2: 1998-04-27 12:20:21+02

This is psql output:
Field | Value
-- RECORD 0 --
field1| 1998-04-27
field2| 1998-04-27 12:20:21+02
----
PS: My DateStyle is setting to 'ISO'

You should set it to 'US' when using Insight ODBC drivers.

It should affect the output in no way, but the driver expects it from
the backend in US format. As this is a per-connection setting it can
safely be set from the driver at startup without affecting other
connections.

There has been some discussion about 'fixing' it and making the
driver recognize other date formats. That would be IMHO unnecessary.
It should be enough just to do "SET DateStyle TO 'US';" at startup.

This can be currently done by setting some registry entries, but
this should really be just a part of driver startup.

Hannu

Hannu Krosing wrote:

Jose' Soares Da Silva wrote:

I have a table with field1 DATE and field2 TIMESTAMP. If I insert data
into these fields, field2 looks OK, but Access97 show me a strange
date on field1.

This is Access97 output:
field1: 27/7/99
field2: 1998-04-27 12:20:21+02

This is psql output:
Field | Value
-- RECORD 0 --
field1| 1998-04-27
field2| 1998-04-27 12:20:21+02
----
PS: My DateStyle is setting to 'ISO'

You should set it to 'US' when using Insight ODBC drivers.

It should affect the output in no way, but the driver expects it from
the backend in US format. As this is a per-connection setting it can
safely be set from the driver at startup without affecting other
connections.

There has been some discussion about 'fixing' it and making the
driver recognize other date formats. That would be IMHO unnecessary.
It should be enough just to do "SET DateStyle TO 'US';" at startup.

This can be currently done by setting some registry entries, but
this should really be just a part of driver startup.

Hannu

Hannu,

I understand what you are saying here, and am very tempted to just go with
setting the datestyle to US at connection time by default. It is true that
this would have no negative effect on applications such as Access.

But, before I do, is there cases out there where people are executing DIRECT
queries through the driver where they are expecting the date to be in a
particular format such as:

insert into tablex (date1) values('28-04-1998') # DD-MM-YYYY
format

If the driver always sets the datestyle to "US", the above insert might not
work. Of course, I would imagine the query should be written more portably
using the ODBC shorthand escape syntax, as:

insert into tablex (date1) values( {d '1998-04-28'} ),

which would work correctly. The reverse is true also, if the user does
"select date1 from tablex", and uses SQL_C_CHAR as the return type,
expecting the format to be EURO, when in fact it would be US.

If no one has any objections, I will change the driver to always set the
datestyle to US, and forget about adding a selection to the dialogs to
select it.

Byron

Hello,

At 17.32 28/04/98 -0400, Byron Nikolaidis wrote:

I understand what you are saying here, and am very tempted to just go with
setting the datestyle to US at connection time by default. It is true that
this would have no negative effect on applications such as Access.

But, before I do, is there cases out there where people are executing DIRECT
queries through the driver where they are expecting the date to be in a
particular format such as:

insert into tablex (date1) values('28-04-1998') # DD-MM-YYYY
format

If the driver always sets the datestyle to "US", the above insert might not
work. Of course, I would imagine the query should be written more portably
using the ODBC shorthand escape syntax, as:

insert into tablex (date1) values( {d '1998-04-28'} ),

which would work correctly. The reverse is true also, if the user does
"select date1 from tablex", and uses SQL_C_CHAR as the return type,
expecting the format to be EURO, when in fact it would be US.

If no one has any objections, I will change the driver to always set the
datestyle to US, and forget about adding a selection to the dialogs to
select it.

Microsoft says that the US date format is *always* recognized by the Jet
database engine, no matter of the windows interntional settings, and it
suggest to use US date format as a kind of international date format. This
means that whenever you don't know in which country your program will be
executed, it is safe to use the US date format. Setting US datestyle by
default in the ODBC driver will provide a behaviour which is much similar
to the Jet database engine, i.e. the behaviour Access/VB programmers
usually have to deal with. So go on with this solution !

Bye !

P.S. I tested the new ODBC driver with index support. VisData still isn't
able to show the index list, anyway it sees them because it allow updates.
Used with VB the ODBC is rather slow compared with other ODBC (About 10
time slower than MS SQL and Velocis, about 30 times slower than MySql) but
it works pretty well. Anyway it is about 3/4 times faster than the OpenLink
driver, which is also pretty buggy ;) Really good job Byron !

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

Sbragion Denis wrote:

P.S. I tested the new ODBC driver with index support. VisData still isn't
able to show the index list, anyway it sees them because it allow updates.
Used with VB the ODBC is rather slow compared with other ODBC (About 10
time slower than MS SQL and Velocis, about 30 times slower than MySql) but
it works pretty well. Anyway it is about 3/4 times faster than the OpenLink
driver, which is also pretty buggy ;) Really good job Byron !

I'm not sure why VisData still isn't able to show the index list. First of all,
I dont know what "VisData" is anyway! Perhaps you could use the odbc tracing
feature (through the 32 bit odbc administrator) and send the "sql.log" to me.
Make sure it is empty before you begin your session. This will really slow
things down by the way.

As for performance, the backend affects that equation greatly. You should see
what happens in Access when you are using unique indexes. Even with one keypart,
Access generates that infamous query we have been talking about (with all the
ANDs and ORs), which really slows things down.

Byron

"For Postgres v6.3 (and earlier) the default date/time style is
"traditional Postgres". In future releases, the default may become
ISO-8601, which alleviates date specification ambiguities and Y2K
collation problems."

I vote for changing default date format to ISO-8601 to reflect
PostgreSQL documentation and for adherence to Standard SQL92.

I was thinking that if the default format changes it should change at a
major rev (i.e. v7.0) since one might expect interfaces to need updates
at a major rev anyway.

But let me turn around the question, in case no one is bothered by this:

Does anyone think that the default date format _shouldn't_ change to
ISO-8601 for the next release?

(I expect to hear that it shouldn't change, but figured I should confirm
it...).

- Tom

On Tue, 28 Apr 1998, Byron Nikolaidis wrote:

Hannu Krosing wrote:

Jose' Soares Da Silva wrote:

I have a table with field1 DATE and field2 TIMESTAMP. If I insert data
into these fields, field2 looks OK, but Access97 show me a strange
date on field1.

This is Access97 output:
field1: 27/7/99
field2: 1998-04-27 12:20:21+02

This is psql output:
Field | Value
-- RECORD 0 --
field1| 1998-04-27
field2| 1998-04-27 12:20:21+02
----
PS: My DateStyle is setting to 'ISO'

You should set it to 'US' when using Insight ODBC drivers.

It should affect the output in no way, but the driver expects it from
the backend in US format. As this is a per-connection setting it can
safely be set from the driver at startup without affecting other
connections.

There has been some discussion about 'fixing' it and making the
driver recognize other date formats. That would be IMHO unnecessary.
It should be enough just to do "SET DateStyle TO 'US';" at startup.

This can be currently done by setting some registry entries, but
this should really be just a part of driver startup.

Hannu

Hannu,

I understand what you are saying here, and am very tempted to just go with
setting the datestyle to US at connection time by default. It is true that
this would have no negative effect on applications such as Access.

But, before I do, is there cases out there where people are executing DIRECT
queries through the driver where they are expecting the date to be in a
particular format such as:

insert into tablex (date1) values('28-04-1998') # DD-MM-YYYY
format

If the driver always sets the datestyle to "US", the above insert might not
work. Of course, I would imagine the query should be written more portably
using the ODBC shorthand escape syntax, as:

insert into tablex (date1) values( {d '1998-04-28'} ),

which would work correctly. The reverse is true also, if the user does
"select date1 from tablex", and uses SQL_C_CHAR as the return type,
expecting the format to be EURO, when in fact it would be US.

If no one has any objections, I will change the driver to always set the
datestyle to US, and forget about adding a selection to the dialogs to
select it.

Why not ISO-8601 this is the Standard SQL92 date format (i.e. YYYY-MM-DD)
and for coherence with PostgreSQL User's Guide, quoting Thomas Lockhart
at page 14, chapter 4, under "Date/Time Styles":

"For Postgres v6.3 (and earlier) the default date/time style is
"traditional Postgres". In future releases, the default may become
ISO-8601, which alleviates date specification ambiguities and Y2K
collation problems."

I vote for changing default date format to ISO-8601 to reflect PostgreSQL
documentation and for adherence to Standard SQL92.
Jose'

Hello,

At 09.31 29/04/98 -0400, Byron Nikolaidis wrote:

I'm not sure why VisData still isn't able to show the index list. First

of all,

I dont know what "VisData" is anyway! Perhaps you could use the odbc tracing

VisData is a small tool provided with visual basic 5.0. It provides a
graphical representation of all the feature of any database that could be
opened through visual basic, including ODBC databases. It is quite an hard
test for any ODBC driver because it tries to show *almost anything* that
could be retrieved through an ODBC driver, not only data. Most ODBC
drivers, even some "famous" one, fail with VisData and still can perfectly
be used in normal applications.

feature (through the 32 bit odbc administrator) and send the "sql.log" to me.
Make sure it is empty before you begin your session. This will really slow
things down by the way.

I'll do it ASAP, and I'll provide also the exact sequence of operation
performed to show the problems. Anyway the problem showed with VisData has
no importance at all, at least using Visual Basic and Access. ASAP I'll
also perform some test using Power Builder, wich uses the ODBC in a
different way than VB.

As for performance, the backend affects that equation greatly. You should

see

what happens in Access when you are using unique indexes. Even with one

keypart,

Access generates that infamous query we have been talking about (with all the
ANDs and ORs), which really slows things down.

I know. Anyway I was not using Access but a small test program I wrote
myself. This program perform random operations (insert, update, select and
delete) through recordset opened on simple tables, so it doesn't suffer
the Access "feature" of creating too complex queries. I know this is not a
deep test, anyway it is the sort of operations 90% of VB code perform on
databases. I think first we should obtain a functioning ODBC driver, i.e.
you should continue on the way you are going now. After this we could take
care of performances. Doing things in reverse order usually produce "very
fast non functioning code", which is not usefull at all ;)

Bye !

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

"Jose' Soares Da Silva" <sferac@bo.nettuno.it> writes:

I vote for changing default date format to ISO-8601 to reflect
PostgreSQL documentation and for adherence to Standard SQL92.

Hear! Hear! Good standards beat silly conventions any day!

-tih
--
Popularity is the hallmark of mediocrity. --Niles Crane, "Frasier"

On 30 Apr 1998, Tom Ivar Helbekkmo wrote:

"Jose' Soares Da Silva" <sferac@bo.nettuno.it> writes:

I vote for changing default date format to ISO-8601 to reflect
PostgreSQL documentation and for adherence to Standard SQL92.

Hear! Hear! Good standards beat silly conventions any day!

Seems that you don't like conventions Tom, but you want
that all world use dates with American format.
Seems that you want impose one convention.
We're working with a database which name is PostgreSQL.
I suppose that you know what's mean the last 3 letters.
Jose'

I vote for changing default date format to ISO-8601 to reflect

Hear! Hear! Good standards beat silly conventions any day!

Seems that you don't like conventions Tom, but you want
that all world use dates with American format.
Seems that you want impose one convention.
We're working with a database which name is PostgreSQL.
I suppose that you know what's mean the last 3 letters.

Uh, Jose', he was agreeing with you :))

Anyway, imo the only issue is _when_ this kind of change should take
place. My comment in the documentation did not promise that it would
change in the next release, only that it might change in a future
release. btw, I don't think that the ISO date style is mandated by the
SQL92 standard, but it does seem like a good idea, particularly as we
approach y2k...

Of course, since we now have the PGDATESTYLE environment variable,
usable by both the backend (at startup) and libpq (at connect time),
perhaps a change in default date format is not something to worry about
too much.

I haven't heard any negative comments (yet) about changing the default
date format to ISO-8601 (yyyy-mm-dd). Does anyone have a strong feeling
that this should _not_ happen for v6.4??

Speak up or it might happen ;)

- Tom

Anyway, imo the only issue is _when_ this kind of change should take
place. My comment in the documentation did not promise that it would
change in the next release, only that it might change in a future
release. btw, I don't think that the ISO date style is mandated by the
SQL92 standard, but it does seem like a good idea, particularly as we
approach y2k...

Of course, since we now have the PGDATESTYLE environment variable,
usable by both the backend (at startup) and libpq (at connect time),
perhaps a change in default date format is not something to worry about
too much.

I haven't heard any negative comments (yet) about changing the default
date format to ISO-8601 (yyyy-mm-dd). Does anyone have a strong feeling
that this should _not_ happen for v6.4??

Speak up or it might happen ;)

I'll cast my vote FOR it if it helps speed it along.

That format makes sorting/ordering a no-brainer. Might not help inside
postgres, but for putting result sets out to a flat file for script
processing, you could then use the unix sort command. Much easier...

Go for it, whenever.

darrenk

On Thu, 30 Apr 1998, Jose' Soares Da Silva wrote:

On 30 Apr 1998, Tom Ivar Helbekkmo wrote:

"Jose' Soares Da Silva" <sferac@bo.nettuno.it> writes:

I vote for changing default date format to ISO-8601 to reflect
PostgreSQL documentation and for adherence to Standard SQL92.

Hear! Hear! Good standards beat silly conventions any day!

Seems that you don't like conventions Tom, but you want
that all world use dates with American format.
Seems that you want impose one convention.

Can someone inform me of what ISO-8601 exactly is?

Thanks to every body that replied my question. Now dates are Ok.

Now I have another problem using M$-Access;
I have a table like this one:

Table    = comuni
+------------------------------+----------------------------------+-------+
|          Field               |              Type                | Length|
+------------------------------+----------------------------------+-------+
| istat                        | char() not null                  |     6 |
| nome                         | varchar()                        |    50 |
| provincia                    | char()                           |     2 |
| codice_fiscale               | char()                           |     4 |
| cap                          | char()                           |     5 |
| regione                      | char()                           |     3 |
| distretto                    | char()                           |     4 |
+------------------------------+----------------------------------+-------+
... in this table I have stored 8k rows, if I load it from M$-Access and 
then I modify a row and I try to save it to database, it goes in a loop
I don't know what's happening.
    Please help me.                                         Thanks, Jose'

On Tue, 28 Apr 1998, Hannu Krosing wrote:

Jose' Soares Da Silva wrote:

Now I have another problem using M$-Access;
I have a table like this one:

Table    = comuni
+------------------------------+----------------------------------+-------+
|          Field               |              Type                | Length|
+------------------------------+----------------------------------+-------+
| istat                        | char() not null                  |     6 |
| nome                         | varchar()                        |    50 |
| provincia                    | char()                           |     2 |
| codice_fiscale               | char()                           |     4 |
| cap                          | char()                           |     5 |
| regione                      | char()                           |     3 |
| distretto                    | char()                           |     4 |
+------------------------------+----------------------------------+-------+
... in this table I have stored 8k rows, if I load it from M$-Access and
then I modify a row and I try to save it to database, it goes in a loop
I don't know what's happening.
Please help me.                                         Thanks, Jose'

This problem has to do with the Postgres' locking mechanism. You cant update a
table while you have the table open for reading. You may be asking yourself,
but I do not have the table open for reading. Ahhh, but Access does because of
the way the odbc driver uses cursors to manage backend data.

Here is the illustration:
---------------------
Access uses two backend connections. On one connection, it does a query to get
key values from the table:
"declare c1 cursor for select key from table"

It then fetches 101 keys from this query. This fetch results in the following
2 queries to the backend:
"fetch 100 in c1"
"fetch 100 in c1"

(Note that there are 8000+ rows in the table so this leaves the table locked)

On the other connection, it actually does the update query:
"update table set a1=2 where key=1"

This update will wait forever because the other query has the table completely
locked.

Workarounds
--------------
In Access, you can go to the end of the table first, before you begin your
update. Then, any update or insert you do should work.

You can also do your update on a smaller subset of records by using a filter in
Access. 200 or less rows would allow the driver to handle it since all the
keys would have been read in as illustrated above.

Now for the ultimate question
-----------------------------
What is the current status/priority of the locking enhancements for Postgres?
Clearly, this is an important problem and needs to be addressed. Even though
the above example only involves Microsoft Access, we have applications which
need to write data to tables that may already be open for reading for a long
time,
such as while doing a massive report with lots of joins. With the current
locking strategy, these applications are impossible.

Regards,

Byron

On Thu, 30 Apr 1998, Thomas G. Lockhart wrote:

I vote for changing default date format to ISO-8601 to reflect

Hear! Hear! Good standards beat silly conventions any day!

Seems that you don't like conventions Tom, but you want
that all world use dates with American format.
Seems that you want impose one convention.
We're working with a database which name is PostgreSQL.
I suppose that you know what's mean the last 3 letters.

Uh, Jose', he was agreeing with you :))

I'm sorry Tom Ivar, my mistake (guilt of my poor english)

Anyway, imo the only issue is _when_ this kind of change should take
place. My comment in the documentation did not promise that it would
change in the next release,

Yes I know...

only that it might change in a future
release. btw, I don't think that the ISO date style is mandated by the
SQL92 standard, but it does seem like a good idea, particularly as we
approach y2k...

I think so, Tom. Here the syntax from...

(Second Informal Review Draft) ISO/IEC 9075:1992, Database
Language SQL- July 30, 1992

5.3 <literal>
<date literal> ::=
DATE <date string>

<date string> ::=
<quote> <date value> <quote>

<date value> ::=
<years value> <minus sign> <months value> <minus sign> <days value>

example date syntax: DATE '0001-01-01'
DATE '9999-12-31'

Ok, I know that keyword DATE before value is a silly and an useless
thing but YYYY-MM-DD format it's an intelligent thing.

Of course, since we now have the PGDATESTYLE environment variable,
usable by both the backend (at startup) and libpq (at connect time),
perhaps a change in default date format is not something to worry about
too much.

I haven't heard any negative comments (yet) about changing the default
date format to ISO-8601 (yyyy-mm-dd). Does anyone have a strong feeling
that this should _not_ happen for v6.4??

Speak up or it might happen ;)

Go for it Tom! Jose'

On Thu, 30 Apr 1998, The Hermit Hacker wrote:

On Thu, 30 Apr 1998, Jose' Soares Da Silva wrote:

On 30 Apr 1998, Tom Ivar Helbekkmo wrote:

"Jose' Soares Da Silva" <sferac@bo.nettuno.it> writes:

I vote for changing default date format to ISO-8601 to reflect
PostgreSQL documentation and for adherence to Standard SQL92.

Hear! Hear! Good standards beat silly conventions any day!

Seems that you don't like conventions Tom, but you want
that all world use dates with American format.
Seems that you want impose one convention.

Can someone inform me of what ISO-8601 exactly is?

- ISO 8601:1988, Data elements and interchange formats - Information
interchange-Representation of dates and times.

3.1.2 Definitions taken from ISO 8601

This International Standard makes use of the following terms
defined in ISO 8601:

a) Coordinated Universal Time (UTC)
b) date ("date, calendar" in ISO 8601)

See (Second Informal Review Draft) ISO/IEC 9075:1992,
Database Language SQL- July 30, 1992)

The required ISO 8601 syntax for DATE is:

DATE 'YYYY-MM-DD'

Comments:

1) DATE combines the datetime fields YEAR, MONTH and DAY.

2) DATE defines a set of correctly formed values that
represent any valid Gregorian calendar date between January 1, 1
AD and December 31, 9999 AD.

3) Any operation that attempts to make a DATE <data type>
contain a YEAR value that is less than 1 or greater than 9999
will fail; the DBMS will return the:
SQLSTATE error 22007 "data exception-invalid datetime format".

4) DATE expects dates to have the following form: yyyy-mm-dd
e.g.: 1994-07-15 represents July 15, 1994.
5) DATE has a length of 10.
6) Date literals must start with the <keyword> DATE and
include 'yyyy-mm-dd';
e.g.:

CREATE mytable (mydate DATE);
INSERT INTO mytable (mydate) VALUES (DATE '1996-01-01');

Jose'

The Hermit Hacker <scrappy@hub.org> writes:

Can someone inform me of what ISO-8601 exactly is?

It's the international standard for representation of date and time.
ISO is the International Organization for Standardization (yeah, I
know, the letters are in the wrong order -- although not in French).
8601 is big and complicated, and some of the legal variations in there
look pretty silly, but the gist of it is simple and good, and I'm
typing this text at approximately 1998-04-30 20:59:07 UTC.

-tih
--
Popularity is the hallmark of mediocrity. --Niles Crane, "Frasier"