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