	Please see the Insight Dist site for a newer source and binary
distribution of the ODBC driver
	
	http://www.insightdist.com/psqlodbc

Julie

Quoting Jose' Soares Da Silva (sferac@proxy.bazzanese.com):
> Hello,
> 
> I have a problem using Access97 and PostODBC (po021-32.tgz).
> I can link PostgreSQL 6.3.1 tables to Access'97 but I can open them
> only if they are empty.
> If I insert data into tables and then I try to access it, I have the
> following message:
> 
>        Receiving an unsupported type from Postgres (#14) SELECT (#513)
>                             
> 			                 Thanks for any help
> 					       Jose'
> 

-- 
[  Julia Anne Case  ] [        Ships are safe inside the harbor,       ]
[Programmer at large] [      but is that what ships are really for.    ]  
[   Admining Linux  ] [           To thine own self be true.           ]
[ Windows/WindowsNT ] [ Fair is where you take your cows to be judged. ]

Fri, 24 Apr 1998 08:40:33 +0000
"Julia A.Case" <julie@hub.org>
Re: [INTERFACES] Access'97 and ODBC
Hello,

I have a problem using Access97 and PostODBC (po021-32.tgz).
I can link PostgreSQL 6.3.1 tables to Access'97 but I can open them
only if they are empty.
If I insert data into tables and then I try to access it, I have the
following message:

       Receiving an unsupported type from Postgres (#14) SELECT (#513)
                            
			                 Thanks for any help
					       Jose'


Fri, 24 Apr 1998 15:04:55 +0000 (UTC)
"Jose' Soares Da Silva" <sferac@proxy.bazzanese.com>
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'


Mon, 27 Apr 1998 14:25:11 +0000 (UTC)
"Jose' Soares Da Silva" <sferac@bo.nettuno.it>
Re: [INTERFACES] Access'97 and ODBC
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:

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




Mon, 27 Apr 1998 10:40:17 -0400
Byron Nikolaidis <byronn@insightdist.com>
Re: [INTERFACES] Access'97 and ODBC
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

Tue, 28 Apr 1998 17:51:02 +0300
Hannu Krosing <hannu@trust.ee>
Re: [INTERFACES] Access'97 and ODBC


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




Tue, 28 Apr 1998 17:32:43 -0400
Byron Nikolaidis <byronn@insightdist.com>
Re: [INTERFACES] Access'97 and ODBC
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

Wed, 29 Apr 1998 09:05:07 +0200
Sbragion Denis <infotecn@tin.it>
Re: [INTERFACES] Access'97 and ODBC


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


Wed, 29 Apr 1998 09:31:25 -0400
Byron Nikolaidis <byronn@insightdist.com>
Re: [INTERFACES] Access'97 and ODBC
>    "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

Wed, 29 Apr 1998 13:59:29 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [INTERFACES] Access'97 and ODBC
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'


Wed, 29 Apr 1998 15:04:26 +0000 (UTC)
"Jose' Soares Da Silva" <sferac@bo.nettuno.it>
Re: [INTERFACES] Access'97 and ODBC
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

Thu, 30 Apr 1998 08:38:36 +0200
Sbragion Denis <infotecn@tin.it>
Re: [INTERFACES] Access'97 and ODBC
"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"

30 Apr 1998 09:44:30 +0200
Tom Ivar Helbekkmo <tih+mail@Hamartun.Priv.NO>
Re: [INTERFACES] Access'97 and ODBC
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:
> > 
> > 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


Thu, 30 Apr 1998 15:36:00 +0000 (UTC)
"Jose' Soares Da Silva" <sferac@bo.nettuno.it>
Re: [INTERFACES] Access'97 and ODBC
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


Thu, 30 Apr 1998 12:10:45 -0400
Byron Nikolaidis <byronn@insightdist.com>
Postgres Locking, Access'97 and ODBC
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'


Tue, 5 May 1998 10:00:27 +0000 (UTC)
"Jose' Soares Da Silva" <sferac@bo.nettuno.it>
Re: [INTERFACES] Postgres Locking, Access'97 and ODBC
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'



Tue, 9 Jun 1998 10:18:08 +0000 (UTC)
"Jose' Soares Da Silva" <sferac@bo.nettuno.it>
M$-Access'97 and TIMESTAMPs


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


Tue, 09 Jun 1998 09:16:13 -0400
Byron Nikolaidis <byronn@insightdist.com>
Re: M$-Access'97 and TIMESTAMPs
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'


Tue, 9 Jun 1998 14:42:23 +0000 (UTC)
"Jose' Soares Da Silva" <sferac@bo.nettuno.it>
Re: [INTERFACES] M$-Access'97 and TIMESTAMPs


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


Tue, 09 Jun 1998 11:12:13 -0400
Byron Nikolaidis <byronn@insightdist.com>
Re: [INTERFACES] Re: M$-Access'97 and TIMESTAMPs


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


Tue, 09 Jun 1998 11:55:41 -0400
Byron Nikolaidis <byronn@insightdist.com>
Re: [INTERFACES] M$-Access'97 and TIMESTAMPs
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'


Tue, 9 Jun 1998 17:25:11 +0000 (UTC)
"Jose' Soares Da Silva" <sferac@bo.nettuno.it>
Re: M$-Access'97 and TIMESTAMPs
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)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~\~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


Wed, 10 Jun 1998 09:27:01 +0000 (UTC)
"Jose' Soares Da Silva" <sferac@bo.nettuno.it>
Re: [INTERFACES] Re: M$-Access'97 and TIMESTAMPs


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




Wed, 10 Jun 1998 11:15:49 -0400
Byron Nikolaidis <byronn@insightdist.com>
Re: [INTERFACES] Re: M$-Access'97 and TIMESTAMPs



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




Wed, 10 Jun 1998 13:45:51 -0400
Byron Nikolaidis <byronn@insightdist.com>
Re: [INTERFACES] Re: M$-Access'97 and TIMESTAMPs
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'


Thu, 11 Jun 1998 10:15:56 +0000 (UTC)
"Jose' Soares Da Silva" <sferac@bo.nettuno.it>
Re: [INTERFACES] Re: M$-Access'97 and TIMESTAMPs