SQLBase's ROWID & SQLServer's Timestamp Columns

[mukesh]

Dear All,

Have used ROWID feature of SQLBase to handle version conflicts with table rows in the SQLBase database i.e used ROWID in the WHERE clause when updating data against table. This was long back. I remember we need to configure something in SQL.INI so that when update clause with ROWID fails, SQLBase throws error message.

1. Has anyone worked on this? How can this be effected? Or, by default, SQLBase will throw error message if ROWID comparison fails?

2. Similarly, in SQLServer, have used Timestamp data-type in place of ROWID to handle version conflicts of table rows. Would like to know if SQL.INI file requires some modification? Or, SQLServer throws error message when comparison against Timestamp column fails?

Would appreciate if you could help me on this.

Thanks,
Mukesh

[RainerE]

Hi,

with SQLBase an Update or delete with a Rowid that has changed in the meatime automatically throws an error. You do not need to configure sql.ini.

With Sql Server the behaviour is different.
I had analysed this some years ago, but did not use it. As far as I remember, this depends on the type of connection (Router, ODBC, OLEDB) and settings (but I did not change sql.ini because I decided to use OLEDB which does not care sql.ini).
Some things from my old documentations:
- ODBC: update with rowid/timestamp does not show an error, you have to use SqlGetModifiedRows()
- OLEDB: update with rowid/timestamp works if you prefix the rowid value with 0x ( '... where rowid = 0x' ||sRowId ),
but the error number is different from SQLBase

Regards,
Rainer

[Jeff Luther]

Rainer has some comments, plus as a consultant I've done this a couple times over the years. That is, 'duplicate' functionality of ROWID in a non-SQLBase DB, like using timestamp. Let me respond to your questions:

1. Has anyone worked on this? How can this be effected? Or, by default, SQLBase will throw error message if ROWID comparison fails?

* Each TB table has to explicitly have a timestamp column added at create table time. AND each table must have a primary key/PK.

* each INSERT has to add a current timestamp value to that column

* every SELECT has to fetch the timestamp and PK for that row. This could be into a hidden field or table window column; user shouldn't see the value

* each UPDATE has to update that row's timestamp value for updating AND
..the UPDATE must have a WHERE which includes the timestamp and the PK AND
..your code needs to query the DB to see if the update went through or not. Update will fail (but no error) if the timestamp has been changed due to an update by another user. NOTE: One way you can check for this is to call this TD/SAL function: SqlGetModifiedRows( hSql, nCount )
Read TD Help for details. (I just tested this function with MS SS 2008 and it does return number of rows changed by an INSERT or UPDATE.)

No, no error if ROWID does not exist.

2. Similarly, in SQLServer, have used Timestamp data-type in place of ROWID to handle version conflicts of table rows. Would like to know if SQL.INI file requires some modification? Or, SQLServer throws error message when comparison against Timestamp column fails?

* No change to SQL.INI file at all. This a DB-side issue (plus your TD code)

* No error from MS SS that I recall. Since an UPDATE WHERE clause would be something like:
...WHERE id = :dfnID AND timestamp = :dfsTimestamp
there is no error with the update, like if I had tried to update where id = -99999 and there's no ID with that value. No error there, either.


It sounds like you have not done any testing (yet) with MS SS, so I strongly suggest you do. Simple DB table, 2 apps on the the same client fetching, inserting/updating, etc. Just to see how it works in a multi-user environment.

[RainerE]

...
* Each TB table has to explicitly have a timestamp column added at create table time. AND each table must have a primary key/PK.

* each INSERT has to add a current timestamp value to that column

You can choose "timestamp" as datatype for the timestamp column. In this case you do not need to care about adding a current timestamp value to the insert statement.
And you can name the timestamp column "rowid" so that you can reduce the effort to modify SQL statements. But I do not recommend this. I would recommend to name the timestamp column "timestamp" to point out that this is different to a rowid column in SQLBase.

Regards,
Rainer