ID Number: Q64794
1.10 1.11 4.20
OS/2
docerr
Summary:
Using the DB-LIBRARY (db-lib) calls dbcoltype() and dbprtype(), you
cannot differentiate between the varbinary and timestamp column types.
Dbcoltype() returns 45 for both types, and dbprtype() translates these
both as being "binary."
There are several methods you can use to differentiate between these
two column types, but they each have problems associated with them.
You can use the dbcolname() call to determine if the name of the
column in question is "timestamp"; however, although this seems like a
logical solution, there is a potential problem. It has been verified
that browse mode can be used to update a table with a timestamp column
that was not named "timestamp"; therefore, the timestamp column may
not always have this unique name.
A timestamp column is designated as a varbinary coltype. Only the
usertype (which is basically unused by db-lib except for special
columns such as timestamp) contains the timestamp data type value
(80). Db-lib does not provide a mechanism for getting to this
information as an exposed API. This information can be found in the
SQL Server internal tables described in Appendix B of the "Microsoft
SQL Server System Administrator's Guide."
More Information:
Consider the situation where you want to determine whether
column 10 of the table "alltypes" is actually type timestamp or
varbinary. The only reliable way to make this differentiation is to
execute a query such as the following:
select usertype from syscolumns
where colid = 10
and
id in
(select id from sysobjects
where name = 'alltypes')
Results
-------
80 = timestamp
4 = varbinary
While this query will provide you with this information, it is based
on internal information that may be subject to change in future versions
of Microsoft SQL Server.
Of the two workarounds given above, Microsoft recommends using the
first choice for the following reasons:
1. It is easy to implement.
2. It is based on a db-lib call.
3. It does not rely on internal information that is subject to change.
The only requirement is that you follow the nonenforced rule of naming
timestamp columns as "timestamp."
Additional reference words: dblib