Passing a timestamp (binary) to SQL Server SP via ADO Parameter

check out the MSDN article Q170380. URL is:
http://support.microsoft.com/support/kb/articles/q170/3/80.asp?FR=0

let me know if this helps
vmano

Sorry, but this doesn't help. The same you allude to uses RDO and sends the query as a string to SQL Server. I am using ADO and must call a stored proc via ADO's parameter objects.

Okie,

1. I believed you are using stored procedure to select and return record(s) to the front-end. Timestamp type field is one of the return column. For the Timestamp type field, you would have to convert it to VARCHAR type before return it to the front end. Below is an example I've used to test it,

select convert(varchar(40), convert(numeric(30), convert(binary(20), timestamp_field)))
from table_name

2. You would pass the timestamp back as VARCHAR just like any other varchar parameter. From within the receiving stored procedure, you would convert it (the value pass in) and the timestamp column to 'Binary' data type before comparing the two. Below is the code I've used to test the conversion

select convert(binary(20), convert(numeric(30), '79228163022532147289276809216'))

The string there is the result that I got from the select statement in step 1.

Hope it works for you

Cheers,

That solution would probably work, but it requires converting the timestamps each time, something I have never done in the past using SQL Server. There must be a better way. I am looking for the DIRECT way of passing the value. Has anyone done this before??

I doubt that there is a way, because 'Timestamp' is a special datatype within server itself. There isn't any such type from VB client side.

And yes, the converting will be done with the select statement, NOT SEPARATELY. Therefore It is considered as one statement.

When I execute the following code:
----------------------------------
select convert(varchar(40), convert(numeric(30), convert(binary(20), timestamp))) from sales_rep where sales_rep_code = '1234'

I get the following error:
--------------------------
Msg 8114, Level 16, State 2
Error converting type binary to type numeric.

I can't convert from a binary to a varchar as your sample above eludes to. Again, I'm using SQL Server 6.5.

I think timestamp values are not to be compared.

I am using SYBASE 11.5.x and It works for me every single time.

Could the timestamp value size be different between SYBASE and SQL Server 6.5 ?

Can you increase the size of numeric to 40 or greater. I think I had the same error when the size was small. I am pretty sure, it just the value size.

Cheers,

Timestamps CAN be compared. There is a big difference between Sybase and SQL Server 6.5. The conversion techniques above do not work in the SQL Server 6.5 environment. Anyone else have an idea?

I am using SQL Server 6.5 timestamps with ADO as follows:

When reading the timestamp from a field in an ADO recordset, save it in a variant variable (no conversion):
  dim vntTimeStamp as Variant
  vntTimeStamp = rsData.Fields("TimeStamp").Value

If your data comes from an ADO command parameter instead in a recordset, then save the parameter value the same way in a variant variable:
  vntTimeStamp = parTimeStamp.Value

Saving the timestamp in a variant is the only way to preserve the format that SQL Server will recognize when you pass it back for updating or deleting. If you need to see the timestamp in some other form then copy it into the appropriate variable and then convert it with TimestampToHex or something like that but don't disturb the original value in the variant variable.

For updating, pass the timestamp back to a stored procedure in an ADO input/output parameter, formatted exactly as follows. The SP can check the current timestamp and can return the new timestamp after the update (note the ADO parameter type is BINARY and the size is 8):
  Dim adoUpdate As New Command
  Dim parID As Parameter
  Dim parValue As Parameter
  Dim parTimeStamp As Parameter
  With adoUpdate    
    .ActiveConnection = {your connection}
    .CommandText = "sp_my_update"
    .CommandType = adCmdStoredProc
    Set parID = .CreateParameter("ID", adInteger, adParamInput)
    Set parValue = .CreateParameter("ID", adDouble, adParamInput)
    Set parTimeStamp = .CreateParameter("TimeStamp", adBinary, adParamInputOutput, 8)
    .Parameters.Append parID
    .Parameters.Append parValue
    .Parameters.Append parTimeStamp
    parID.Value = {something}
    parValue.Value = {something}
    parTimeStamp.Value = vntTimeStamp
    .Execute
  End With
  vntTimeStamp = parTimeStamp.Value  'Returns the new timestamp.

This is a sample update stored procedure that uses the built-in SQL Server function "tsequal" to verify the timestamp before updating, and then passes back the new timestamp after the update is finished:
  CREATE PROCEDURE sp_my_update (@my_id smallint, @my_value int, @my_timestamp timestamp OUTPUT)
  AS
  UPDATE table
  SET table.value = @my_value WHERE table.id = @my_id and tsequal(table.timestamp, @my_timestamp)
  SELECT @my_timestamp = table.timestamp FROM table WHERE table.id = @my_id

For deleting, you only need to pass the timestamp to the stored procedure in an ADO input parameter so the SP can check the current timestamp:
  Dim adoDelete As New Command
  Dim parID As Parameter
  Dim parTimeStamp As Parameter
  With adoUpdate    
    .ActiveConnection = {your connection}
    .CommandText = "sp_my_delete"
    .CommandType = adCmdStoredProc
    Set parID = .CreateParameter("ID", adInteger, adParamInput)
    Set parTimeStamp = .CreateParameter("TimeStamp", adBinary, adParamInputOutput, 8)
    .Parameters.Append parID
    .Parameters.Append parTimeStamp
    parID.Value = {something}
    parTimeStamp.Value = vntTimeStamp
    .Execute
  End With

This is a sample delete stored procedure that uses the built-in SQL Server function "tsequal" to verify the timestamp before deleting:
  CREATE PROCEDURE sp_my_delete (@my_id smallint, @my_timestamp timestamp)
  AS
  DELETE FROM table WHERE table.id = @my_id and tsequal(table.timestamp, @my_timestamp)

I spotted a mistake in the DELETE example:
   Set parTimeStamp = .CreateParameter("TimeStamp", adBinary, adParamInputOutput, 8)
should be:
   Set parTimeStamp = .CreateParameter("TimeStamp", adBinary, adParamInput, 8)

Sorry about that.

That was nothing but a typo. My code does define the parameter as just an input parameter. The error occurs in code when the assignment of the timestamp value is made to the parameter defined as a timestamp.

Oops.. sorry WDeem. Your answer does look good. I'll give you the points if you answer again. I still have another problem.... The db code is all in the middle tier and I pass it back to the client NOT in a variant array or ADO recordset but in a string format that can contain significantly more than the above 2 methods. Do you know any way to translate the value in the variant variable using the TimestampToHex and later "reassemble" it back to a variant so ADO will be happy?

Calder, You will find that what WDeem provided is part of my earlier suggestion (from Timestamp to Binary). What you are after is next bit from Binary to String. Converting from a variant (holding binary value) to string is okie, but convert it back from string to variant (holding binary value) might be a bit of a problem. You might not get the same value. That is why my suggestion was convert it all the way to string (from within the Server) before pass it back to the middle tier and then client.

vsPeter, you are partly right. Your answer would work, if it worked at all in SQL Server, which it doesn't.