timestamp cast problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

timestamp cast problem

Author

Topic

CodeMaster
Starting Member

3 Posts

Posted - 2010-10-14 : 11:04:36

In communication with a Powerbuilder 7 client by ODBC I keep on having cast problems.

I use insert and update stored procedures with a timestamp (rowversion) for resolving 'concurrent user update'-problems.

SQLserver 2008 is complaining about castingproblems (insert -> char to bigint) and (update -> date to bigint).

What do I have to do on the Powerbuilder and the SQLserver site. I'm lost.

With kind regards, Robert

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-10-14 : 11:23:15

What does casting char to bigint or date to bigint have to do with a timestamp (rowversion)?

Sounds like you just have bad values that cannot be cast to the target datatypes.

CODO ERGO SUM

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-10-14 : 12:28:12

Are you trying to insert the rowversion or compare rowversions or something else?

CodeMaster
Starting Member

3 Posts

Posted - 2010-10-15 : 10:05:48

At the Powerbuilder end I have a datawindow based on stored procedures:

CREATE TABLE [dbo].[DOMEINTYPE](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TYPE] [varchar](20) NOT NULL,
[T_USR_C] [varchar](20) NOT NULL,
[T_USR_U] [varchar](20) NULL,
[T_DT_C] [datetime] NOT NULL,
[T_DT_U] [datetime] NULL,
[TIMESTAMP] [rowversion] NOT NULL,
PRIMARY KEY CLUSTERED (
[ID] ASC
)
WITH ( PAD_INDEX = OFF
, STATISTICS_NORECOMPUTE = OFF
, IGNORE_DUP_KEY = OFF
, ALLOW_ROW_LOCKS = ON
, ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]
)
ON [PRIMARY]

===========================================

CREATE PROCEDURE [dbo].[spSQM_R_DOT]
AS
BEGIN
SET NOCOUNT ON;

SELECT DOT.ID
, DOT.TYPE
, DOT.TIMESTAMP
FROM dbo.DomeinType DOT
END

===========================================

CREATE PROCEDURE [dbo].[spSQM_C_DOT] (
@Id integer output,
@Type varchar(20),
@TIMESTAMP bigint output
)
AS
BEGIN
SET NOCOUNT ON

INSERT INTO dbo.DomeinType (
Type,
T_USR_C,
T_DT_C
)
VALUES (
@Type,
SUSER_SNAME(),
GetDate(),
)

SET @ID = Scope_Identity()
SET @Timestamp = CONVERT(bigint, @@DBTS)
END

============================================

CREATE PROCEDURE [dbo].[spSQM_U_DOT] (
@id int,
@Type varchar(20),
@Timestamp bigint output
)
AS
BEGIN
SET NOCOUNT ON

UPDATE dbo.DomeinType
SET TYPE = @Type,
T_USR_U = SUSER_SNAME(),
T_DT_U = GETDATE()
WHERE ID = @id
AND Timestamp = @Timestamp

-- Here is where the error catching comes in when no rows are
-- updated

SET @Timestamp = CONVERT(bigint, @@DBTS)
END

=============================================

This is what happens I think:

By the creation of the datawindow based on the select stored procedure spSQM_R_DOT the timestamp attribute gets the type timestamp.

After a save on an inserted row (timestamp = NULL) spSQM_C_DOT is called, converting the timestamp [varbinary(8)] to some kind of [char] type using ODBC.
Further more, the [char] type is converted to a [bigint]. This point is where it goes wrong. But I don't know what type else to define the incoming timestamp.

Later on, when updating an existing row (timestamp = Ox000000000003E4) spSQM_U_DOT] is called, converting the timestamp [varbinary(8)] to some kind of [date] type using ODBC.
Further more, the [date] type is converted to a [bigint]. This point is where it goes wrong. But I don't know what type else to define the incoming timestamp.

Can you understand why I am confused?

With kind regards, Robert

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-10-15 : 10:25:07

Use a data type of VARBINARY(8) for the TIMESTAMP. If Powerbuilder does not support that datatype, you need to ask how to handle that on a Powerbuilder forum.

Another thing; this code will not work as you expect to capture the TIMESTAMP:

SET @Timestamp = CONVERT(bigint, @@DBTS)

@@DBTS returns the last-used timestamp value of the current database, so it could be from another table, or from another row in the same table.

You should use the OUTPUT clause of the INSERT or UPDATE statements to capture the TIMESTAMP of the row.

CODO ERGO SUM

CodeMaster
Starting Member

3 Posts

Posted - 2010-10-16 : 06:35:30

Thanx,

a french Powerbuilderforum suggested to use at the powerbuilderside char(18) when connecting to Sybase ASE and to write conversion functions in SQLServer vrom varbinary2string and from string2varbinary.

These two functions are already present in SQLServer (although not documented I presume), i.e.

master.dbo.fn_varbintohexstr()
master.dbo.fn_cdc_hexstrtobin()

So on the serverside now I can deliver strings to Powerbuilder and compare strings from powerbuilder with the timestampvalue.

And yes, I am aware of the riscs involved with using @@DBTS and don't use it in my endsolution, but it's less to type in a developing (test) environment.

Thank you all for the quick help provided.

With kind regards, Robert

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources