SQL timestamp type: VB6 equivalent?

[msc0tt]

My VB code is choking on the 8-byte SQL type 'timestamp'. I'm getting a Type Mismatch error. I know that this SQL value is note really a date/time, but rather a binary value. I'm trying to load a timestamp value (via ADO) into a Date variable (also 8 bytes) -Choke. I've also tried a variable of Double type -Choke!
Do I need a custom VB type to work with SQL timestamp values?
-thanks

 

[NoCoolHandle]

I think you might find it is binary..
or maybe a byte array...


Actually I just did a test..

QA reports the value in Hex, but you can declare a byte array and populate it, then print the decimal value of the time stamp..

i.e.

create table crap (c1 int identity,ts timestamp)
go
insert into crap..table1 default values
go
insert into crap..table1 default values
go
insert into crap..table1 default values
go
insert into crap..table1 default values
go
insert into crap..table1 default values
go
insert into crap..table1 default values
go
select * from crap..table1
go
insert into crap..table1 default values
go

Dim rs As ADODB.Recordset
Dim x() As Byte
Set rs = New ADODB.Recordset
rs.Open "select ts from crap.dbo.table1", "provider=sqloledb;server=.;integrated security=sspi"

in the immed window

x = rs(0).Value
?ubound(x)
 ?x(0)&x(1)&x(2)&x(3)&x(4)&x(5)&x(6)&x(7)

returned
[red]
0000000103
[/red] which in Hex = 0x0000000000000067
which was teh value I saw in QA

HTH

Rob

PS remember this field is only good for seeing if a record has changed and you can do that by doing a direct comparison of the recordset against the fresh row in a new recordset.. You normally wouldn't display it.



Rob

 

[msc0tt]

Thanks Rob.
I may have found what could be an even easier solution. I just updated the code to use Variant (duhhhhh! ;-{), and it compiled! Haven't tested it yet, but I'll post my results. I don't need to display the value anywhere, just save it in a different SQL table (using a datetime8 field), and compare it to other 'timestamp' values.
-Mike

 

[ESquared]

ADO should properly translate the dates, if this isn't occurring then it makes me wonder if things are being access/stored/declared correctly. Unless there's an out-of-range datetime value...

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[SQLBill]

Just remember: According to BOL (use Index tab, enter Timestamp data type and select the option for Using Special Data, then scroll down to Timestamp), SQL Server timestamp data type has nothing to do with times or dates.

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine

Posting advice: FAQ481-4875

 

[msc0tt]

Absolutly correct Bill. BOL is my reference but I couldn't find any mention of representing a timestamp value in VB. Still haven't tested the Variant (other priorities), but I suspect this is my answer. -cheers

 

[NoCoolHandle]

SQL_Variant doesn't work either (as a very strong guess)..
It will just return the timestamp datatype. (I find sql_variant to be a big waste of time.. almost as bad as a variant in VB.

You could cast it to a varbinary, but you will then need to do something with the varbinary data.. (it will display the same in VB as timestamp..)

Q. Would you like to see the same result as in Query Analyzer?


Rob

 

[SQLBill]

You say you "just save it in a different SQL table (using a datetime8 field), ". I don't believe you will be able to store it in a datetime field as TIMESTAMP isn't datetime (that was the point I was trying to make).

FYI-Timestamp uses @@DBTS. The first time the row is created @@DBTS is assigned. Then when it's modified, 1 is added to @@DBTS. (from BOL).

-SQLBill

 

[msc0tt]

Gads,
This gets more difficult with each post! ;-{) So much for what I thought was a clever idea. My task is as follows:
Write a simple 1-way sync utility (VB6) that copys new rows from a local SQL DB to a remote SQL DB (hosted by an ASP that doesn't support SQL Replication). My idea was to add a timestamp column to the local table. Then, every time I do a SYNC, save the largest timestamp value in a separate location (another local SQL table). At SYNC time, I copy only the rows where the timestamp value is greater than the value saved from the last SYNC. Get it? My goals are minimal change to the local DB (adding one column called rowversion of type timestamp), and zero coding to the existing app that works with the local DB.
Other suggestions welcome.

 

[ESquared]

Hmmm. I didn't actually know anything at all about the timestamp data type. Thanks for the info.

msc0tt, after looking this up in Books Online, I think you don't want to use this data type.

From SQL Server Books Online:

[ul]timestamp
timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.

Remarks
The Transact-SQL timestamp data type is not the same as the timestamp data type defined in the SQL-92 standard. The SQL-92 timestamp data type is equivalent to the Transact-SQL datetime data type.

A future release of Microsoft® SQL Server™ may modify the behavior of the Transact-SQL timestamp data type to align it with the behavior defined in the standard. At that time, the current timestamp data type will be replaced with a rowversion data type.

Microsoft® SQL Server™ 2000 introduces a rowversion synonym for the timestamp data type. Use rowversion instead of timestamp wherever possible in DDL statements. rowversion is subject to the behaviors of data type synonyms. For more information, see Data Type Synonyms.

In a CREATE TABLE or ALTER TABLE statement, you do not have to supply a column name for the timestamp data type:

CREATE TABLE ExampleTable (PriKey int PRIMARY KEY, timestamp)

If you do not supply a column name, SQL Server generates a column name of timestamp. The rowversion data type synonym does not follow this behavior. You must supply a column name when you specify rowversion.

A table can have only one timestamp column. The value in the timestamp column is updated every time a row containing a timestamp column is inserted or updated. This property makes a timestamp column a poor candidate for keys, especially primary keys. Any update made to the row changes the timestamp value, thereby changing the key value. If the column is in a primary key, the old key value is no longer valid, and foreign keys referencing the old value are no longer valid. If the table is referenced in a dynamic cursor, all updates change the position of the rows in the cursor. If the column is in an index key, all updates to the data row also generate updates of the index.

A nonnullable timestamp column is semantically equivalent to a binary(8) column. A nullable timestamp column is semantically equivalent to a varbinary(8) column.[/ul]

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[NoCoolHandle]

Just to offer a suggestion now we know that a timestamp WON'T work..

How about a trigger that acutally uses the getdate() function to alter a column whenever a row gets updated.

If the one on the remote server is newer, update it..

SOmething like

Create Trigger udTime on TableX
for Update 
as
begin
  update TableX set tmStmpCol = getdate where UniqueIDCOL = select UniqueIDCOl from inserted
end

Rob

 

[NoCoolHandle]

Sorry missed a few parens...
should look like...

Create Trigger udTime on TableX
for Update 
as
begin
  update TableX set tmStmpCol = getdate() where UniqueIDCOL = (select UniqueIDCOl from inserted)
end
[\code]

 

[msc0tt]

Thanks for the suggestion. The big selling feature of the timestamp field was the automatic updating by SQL -no triggers required. This table I wish to SYNC is vendor supplied, and intended for use only with the vendor application. Adding a harmless column was about as far as I wanted to go. Adding a trigger could cause me support woes if something went wrong.
I will continue experimenting, and post if I get a breakthrough.

 

[SQLBill]

Timestamp COULD work. I just had to make sure you were clear that it wasn't a date/time value at all.

Disclaimer: I've never done this myself.

SQL Server uses TIMESTAMP data type as a rowversion value. (in fact future versions will change the data type name to rowversion). One way I have heard of timestamp being used is:

Let's say you have two people that could be changing the same data. But if A 'pulls' a row to make a change and then 'sits' on it while B makes a change and submits it. You don't want A's change to overwrite B's change.

Solution....when a user 'grabs' a row, the timestamp (let's call it rowversion) for that row is saved off. Then when the user inserts/commits their change the saved timestamp/rowversion is compared to the current row's timestamp/rowversion. If they are not the same (old_timestamp <> current_timestamp) then the change doesn't take place.

In your case, why convert the timestamp? Just checking to see if it's the same should work.

-SQLBill

 

[ESquared]

But timestamp isn't a timestamp. It has nothing to do with what time something occurs. It is merely guaranteed to be unique across the database, and to change on every update.

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[SQLBill]

I forgot to add, you would have to have a Identity row or some other totally unique row that would not change and would be the same in both databases.

That way you would compare:

WHERE t1.rowid = t2.rowid
AND t1.timestamp <> t2.timestamp

Just completely forget about trying to convert it to a date/time or anything else. Do a simple comparison.

-SQLBill

 

[NoCoolHandle]

[red]ESquared (Programmer) Aug 10, 2004
But timestamp isn't a timestamp. It has nothing [/red]

I think this might have been aimed at my earlier post about triggers..

If so, sorry to cause confusion, I was thinking more along the lines of a datetime datatype column, but then using the update to change it to the latest time.
The column name of [blue]tmStmpCol [/blue] was only a logical name. Not necessarly a pointer to the datatype.

Hope that clears any missunderstanding..

Rob

 

[msc0tt]

I've lost track of who is talking to whom... :-{)
Facts:
The timestamp type is not an actual "timestamp", the datetime8 type is. The timestamp type is 8 bytes of binary data. This data is automatically incremented (not necessarily by 1) every time the row containing this field gets updated. It is safe to say that larger a timestamp field, the more recently his row was updated.

SQLBill,
I have no need to convert this timestamp data to a different format. Your sample code will work, but requires access to both tables each time a SYNC is possibly needed. I'm hoping for one further efficiency. I wish to keep track of the last-timestamp-value-used-for-a-SYNC in a local location. Then I won't have to nail-up a connection to my remote ASP every single time (every minute) I want to see if new rows have been added to the local DB. p.s. I can't use the number of records as an indicator since old records will be deleted as a maintenance routine.
-Mike

 

[ESquared]

I'm not sure that you should rely on timestamp being a larger value. The help says it is guaranteed to be unique across the database, but there is nothing about it incrementing. Do you know *for sure* that it will never reuse a timestamp value, once a row containing an old one is deleted? Do you know for sure that it's an incremental sort of thing and not a more random one?

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[msc0tt]

Well, I guess I'm not 100% positive. I've been researching this topic for days, and I know I came across something that indicated you could compare timestamps with > and < to indicate if a row was updated more recently than another row. I've hit too many sites to easily find this again, but I will try.....
-Mike