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"

On Thu, 30 Apr 1998, Byron Nikolaidis wrote:

Thank you very much Byron for your explanation.

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.

Seems this problem exists also when I read only one row.
I tried this:
I got the first row using a form, then I modified a field on this form and
then I tried to load the next row (by using right arrow), and Access
is already there locked by PostgreSQL.
ps command give me the followinng result: (two backend connections as you said)

3033 ? S 0:00 postmaster -i -o -F -B 512 -S
5034 ? S 0:01 /usr/local/pgsql/bin/postgres -p -Q -P5 -F -B 512 -v 6553
5035 ? S 0:07 /usr/local/pgsql/bin/postgres -p -Q -P5 -F -B 512 -v 6553

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.

Is there in project to work on this problem ?
Jose'

Hi, all!

I created a table with a TIMESTAMP data type to use with M$-Access, because
Access uses such field to control concurrent access on records.
But I have a problem M$-Access doesn't recognize a TIMESTAMP type, it see
such fields as "text" instead of "date/time".
Is there a way to make Access recognize TIMESTAMPs ?
Thanks, Jose'

Jose' Soares Da Silva wrote:

Hi, all!

I created a table with a TIMESTAMP data type to use with M$-Access, because
Access uses such field to control concurrent access on records.
But I have a problem M$-Access doesn't recognize a TIMESTAMP type, it see
such fields as "text" instead of "date/time".
Is there a way to make Access recognize TIMESTAMPs ?
Thanks, Jose'

I could add TimeStamp as a supported data type of the odbc driver. Currently,
'abstime' is supported but not 'timestamp'.

Byron

On Tue, 9 Jun 1998, Jose' Soares Da Silva wrote:

Hi, all!

I created a table with a TIMESTAMP data type to use with M$-Access, because
Access uses such field to control concurrent access on records.
But I have a problem M$-Access doesn't recognize a TIMESTAMP type, it see
such fields as "text" instead of "date/time".
Is there a way to make Access recognize TIMESTAMPs ?
Thanks, Jose'

Also the following types are recognized as text:
int28
oid8
oidint2
oidint4

I forgot to say that I'm using :
PostgreSQL-6.3.2
Linyx ELF 2.0.33
psqlodbc-06.30.0243
M$-Access97
Ciao, Jose'

Byron Nikolaidis wrote:

I could add TimeStamp as a supported data type of the odbc driver. Currently,
'abstime' is supported but not 'timestamp'.

Also, the postgres "datetime" type is already supported as well.
Maybe that would work for you temporarily.
As a matter of fact, all the date/time types "look" the same since we now use
'ISO'.

Byron

Jose' Soares Da Silva wrote:

Also the following types are recognized as text:
int28
oid8
oidint2
oidint4

Just a little history here...any data type that is not directly supported by the
odbc driver will get mapped to SQL_VARCHAR or SQL_LONGVARCHAR, depending on
driver 'data type options'. That allows you to view it and possibly update it,
if there is an appropriate operator. This is great compared to what the driver
used to do in the old days with unsupported types (i.e., crash with no
descriptive error message)!

For int28 and oid8, there is no SQL data type that maps. Text is the only way to
display it that I know of.

oidint2 and oidint4 are just integers I guess, and probably could be mapped to
SQL_SMALLINT and SQL_INTEGER, respectively.

Byron

On Tue, 9 Jun 1998, Byron Nikolaidis wrote:

Jose' Soares Da Silva wrote:

Hi, all!

I created a table with a TIMESTAMP data type to use with M$-Access, because
Access uses such field to control concurrent access on records.
But I have a problem M$-Access doesn't recognize a TIMESTAMP type, it see
such fields as "text" instead of "date/time".
Is there a way to make Access recognize TIMESTAMPs ?
Thanks, Jose'

I could add TimeStamp as a supported data type of the odbc driver. Currently,
'abstime' is supported but not 'timestamp'.

Thank you Byron.
I think this is great. M$-Access should work well with a timestamp field,
I have problems with concurrent access and I think it is because this data type.
Jose'

On Tue, 9 Jun 1998, Byron Nikolaidis wrote:

Byron Nikolaidis wrote:

I could add TimeStamp as a supported data type of the odbc driver. Currently,
'abstime' is supported but not 'timestamp'.

Also, the postgres "datetime" type is already supported as well.
Maybe that would work for you temporarily.
As a matter of fact, all the date/time types "look" the same since we now use
'ISO'.

My problem is that I need a TIMESTAMP data type defined in M$-Access because
M$-Access wants it to have best performance when it updates a table via ODBC.
M$-Access doesn't lock a record being modified, to allow control concurrent
access to data M$-Access reads again the record to verify if it was modified by
another user, before update it to database.
If there's a TIMESTAMP M$-Access verifies only, if this field was modified,
otherwise it verifies every field of the table, and obviously it is slower.
I beleave it would very useful if you could add this feature to psqlodbc.
Thanks, Jose'

Byron

Ciao, Jose'
___, /
|_+_| /| / ~
~~~~~~~~~~~~~~~~~~~~~~~~~ | / | /| ~~~~~~~~~~~~~~~~~~~~~
Jose' Soares Da Silva ~ |/ | / | / "As armas e os Baroes
Progetto "OS LUSIADAS" ~ | |/| | /| assinalados, que da
SFERA CARTA SOFTWARE ~ /| / | | / | Occidental praia Lusitana
Via Bazzanese, 69 / | / | | /| | por mares nunca de antes
Casalecchio R. BO - Italy / | / | |/ | | navegados, passarono
http://www.sferacarta.com / |/____|_/__|_| ainda alem da Taprobana"
sferac@bo.nettuno.it /____|__| | __|___________ ~
Fax. ++39 51 6131537 ____________|_____|_/ LUSIADAS / (Luis de Camoes,
Tel. ++39 51 591054 \ o / Os Lusiadas, canto I)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~\~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Jose' Soares Da Silva wrote:

My problem is that I need a TIMESTAMP data type defined in M$-Access because
M$-Access wants it to have best performance when it updates a table via ODBC.
M$-Access doesn't lock a record being modified, to allow control concurrent
access to data M$-Access reads again the record to verify if it was modified by
another user, before update it to database.
If there's a TIMESTAMP M$-Access verifies only, if this field was modified,
otherwise it verifies every field of the table, and obviously it is slower.
I beleave it would very useful if you could add this feature to psqlodbc.
Thanks, Jose'

I have absolutely no problem with adding the postgres 'timestamp' type, in fact, I
already added it.
But, the thing is, the postgres types abstime and datetime, ALREADY map to
SQL_TIMESTAMP!

I think, that this actually has to do with SQLSpecialColumns 'SQL_ROWVER'. Access
checks for this but we don't return anything. SQL_ROWVER is defined as the column(s)
in the specified table, if any, that are automatically updated by the data source when
any value in the row is updated by any transaction (as in SQLBase ROWID or Sybase
TIMESTAMP).

It seems to me, that this suggests that if we had a hidden timestamp column, Access
would use that to verify. I don't believe we have such a column in postgres?

Byron

Byron Nikolaidis wrote:

Jose' Soares Da Silva wrote:

My problem is that I need a TIMESTAMP data type defined in M$-Access because
M$-Access wants it to have best performance when it updates a table via ODBC.
M$-Access doesn't lock a record being modified, to allow control concurrent
access to data M$-Access reads again the record to verify if it was modified by
another user, before update it to database.
If there's a TIMESTAMP M$-Access verifies only, if this field was modified,
otherwise it verifies every field of the table, and obviously it is slower.
I beleave it would very useful if you could add this feature to psqlodbc.
Thanks, Jose'

I did some testing with SQLSpecialColumns 'SQL_ROWVER'. As I noted in my previous mail,
we dont return anything for this function in the driver. I tried hard-coding a column
that was a SQL_TIMESTAMP type (in my table it was a postgres 'datetime'). Access did use
that column. Here are the results:

test1 table
----------
a,c,d,e,f,g = int2
b,h = varchar
datetim = datetime

Access results without ROWVER (this is the way things currently are)
---------------------------------------------------------------------
BEGIN
update test1 set b='hello' where a=7 AND b='other' AND c=3 AND d=4 AND e is NULL AND f is
NULL AND g=5 AND h='stuff'
COMMIT

Access results with ROWVER
-------------------------------
BEGIN
update test1 set b='hello' where a=7 AND datetim = '1998-05-30 10:59:00';
select a,b,c,d,e,f,g,h,datetim where a=7;
COMMIT

Conclusion:
-----------
The update statement was definately smaller and only involved the key and the timestamp
column. The extra select that it does to verify no one has changed anything (using the
value of the timestamp) slowed the update down, though. I don't think the speed gain on
the smaller update statement makes up for the extra query. In either case, the backend
locking problem would still prevent the update if the table was opened by someone else (or
even the same application, as in our declare/fetch problem).

Also, something would have to be done to actually put a timestamp value in every time a
row was added or updated. Access actually prevented me from entering a value in my
'datetim' field because it assumed the dbms would fill it in. I guess you could use a
trigger to update the timestamp field. OR if we had a pseudo column that qualified, we
could use that, however when I tried using a pseudo column, Access barfed on me
complaining "Table TMP%#$$^ already exists". If I added the pseudo column to the output,
the message went away. I have no idea what the heck that means?

Any ideas or thoughts?

Byron

On Wed, 10 Jun 1998, Byron Nikolaidis wrote:

Byron Nikolaidis wrote:

Jose' Soares Da Silva wrote:

My problem is that I need a TIMESTAMP data type defined in M$-Access because
M$-Access wants it to have best performance when it updates a table via ODBC.
M$-Access doesn't lock a record being modified, to allow control concurrent
access to data M$-Access reads again the record to verify if it was modified by
another user, before update it to database.
If there's a TIMESTAMP M$-Access verifies only, if this field was modified,
otherwise it verifies every field of the table, and obviously it is slower.
I beleave it would very useful if you could add this feature to psqlodbc.
Thanks, Jose'

I did some testing with SQLSpecialColumns 'SQL_ROWVER'. As I noted in my previous mail,
we dont return anything for this function in the driver. I tried hard-coding a column
that was a SQL_TIMESTAMP type (in my table it was a postgres 'datetime'). Access did use
that column. Here are the results:

test1 table
----------
a,c,d,e,f,g = int2
b,h = varchar
datetim = datetime

Access results without ROWVER (this is the way things currently are)
---------------------------------------------------------------------
BEGIN
update test1 set b='hello' where a=7 AND b='other' AND c=3 AND d=4 AND e is NULL AND f is
NULL AND g=5 AND h='stuff'
COMMIT

Access results with ROWVER
-------------------------------
BEGIN
update test1 set b='hello' where a=7 AND datetim = '1998-05-30 10:59:00';
select a,b,c,d,e,f,g,h,datetim where a=7;
COMMIT

Conclusion:
-----------
The update statement was definately smaller and only involved the key and the timestamp
column. The extra select that it does to verify no one has changed anything (using the
value of the timestamp) slowed the update down, though. I don't think the speed gain on
the smaller update statement makes up for the extra query. In either case, the backend

I don't know for sure, if in this way Access is faster, I red on Access
manual that it is faster using ROWVER during updates.
I think the extra select is to refresh the data on the Client side, otherwise
Access doesn't refresh the Client and it says that another user has
modified the record (but that other user is me).

locking problem would still prevent the update if the table was opened by someone else (or
even the same application, as in our declare/fetch problem).

Also, something would have to be done to actually put a timestamp value in every time a
row was added or updated. Access actually prevented me from entering a value in my
'datetim' field because it assumed the dbms would fill it in. I guess you could use a
trigger to update the timestamp field. OR if we had a pseudo column that qualified, we
could use that, however when I tried using a pseudo column, Access barfed on me
complaining "Table TMP%#$$^ already exists". If I added the pseudo column to the output,
the message went away. I have no idea what the heck that means?

Any ideas or thoughts?

Byron

Jose'