Solved

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

Posted on 1998-11-04
16
1,046 Views
Last Modified: 2013-12-25
I need to pass a timestamp value to a stored procedure in SQL server using an ADO parameter. I can create the parameter with no problem, but I always get an error that the datatype is wrong when I assign the timestamp value to the parameter. The timestamp has been converted to a hex string (via MS's RDO code TimestampToHex). I need to get the timestamp back to the proc for update/delete operations.
0
Comment
Question by:cadler
  • 7
  • 4
  • 3
  • +2
16 Comments
 
LVL 3

Expert Comment

by:vmano
Comment Utility
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
0
 
LVL 1

Author Comment

by:cadler
Comment Utility
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.
0
 
LVL 2

Expert Comment

by:vspeter
Comment Utility
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,
0
 
LVL 1

Author Comment

by:cadler
Comment Utility
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??
0
 
LVL 2

Expert Comment

by:vspeter
Comment Utility
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.
0
 
LVL 1

Author Comment

by:cadler
Comment Utility
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.
0
 
LVL 2

Expert Comment

by:alokm
Comment Utility
I think timestamp values are not to be compared.
0
 
LVL 2

Expert Comment

by:vspeter
Comment Utility
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,
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Author Comment

by:cadler
Comment Utility
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?
0
 
LVL 1

Expert Comment

by:wdeem
Comment Utility
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)

0
 
LVL 1

Expert Comment

by:wdeem
Comment Utility
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.

0
 
LVL 1

Author Comment

by:cadler
Comment Utility
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.
0
 
LVL 1

Author Comment

by:cadler
Comment Utility
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?
0
 
LVL 1

Accepted Solution

by:
wdeem earned 210 total points
Comment Utility
I have tried that before and could not find a way to reconstruct the value in a way that SQL Server will accept it. The problem is not in the value but in the length of the variable. My guess is they made it that way to keep us from doing anything with the timestamp.

In the project I am working on now, we pass data from a middle-tier but do not hand over the timestamp to the client, it remains hidden in the data object (a class in VB) that is created in the middle tier. The client has no use for the timestamp and it can only cause problems if the value gets changed by accident. It doesn't serve any meaningful purpose except that you need it if you want to use "tsequal" in your stored procedures to control updates and deletes.

We create a data object (a class) and pass it to the client so that the client has access to any of the public properites (the data) it needs but cannot access anything else inside the object that we want to keep hidden (like the timestamp). We also have some internal values that are set automatically (like who created, when created, last access, who updated, when updated, etc.) that are accessable to the client but are read-only. The timestamp remains hidden away in a variant variable in the object. Objects that are updatable have a "Save" method that envokes the update stored procedure and has the timestamp readily available because the Save method and timestamp variable are in the same object.

If you don't use objects and are passing data in strings then I don't see a way to pass the timestamp with the data. The only solution I can think of is to pass the timestamp separately to the client in a variant variable to keep it separate, and then the client has to pass it back when needed for updating.
0
 
LVL 2

Expert Comment

by:vspeter
Comment Utility
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.
0
 
LVL 1

Author Comment

by:cadler
Comment Utility
vsPeter, you are partly right. Your answer would work, if it worked at all in SQL Server, which it doesn't.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now