RE: [INTERFACES] Re: M$-Access'97 and TIMESTAMPs

I haven't looked at DATETIME stuff, but does Postgres not have something
similar to Oracle's SYSDATE? In Oracle you can set an Oracle DATE column to
have a default of SYSDATE. This way Oracle can fill in the column at the
time an insert is done.

- Greg

> -----Original Message-----
> From: Byron Nikolaidis [SMTP:byronn(at)insightdist(dot)com]
> Sent: Wednesday, June 10, 1998 10:46 AM
> To: Jose' Soares Da Silva; interfaces postgres
> Subject: 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
>
>
>