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'