VB, RDO - ODBC, SQL Server and Timestamp column manipulation 
Author Message
 VB, RDO - ODBC, SQL Server and Timestamp column manipulation

It is necessary sometimes to update or delete a previously read row with a
SQL statement including a timestamp column in the WHERE clause.
Since the timestamp real datatype VARBINARY(8) is incompatible with VB, it
has to be converted somehow to pass the timestamp as a parameter and then
take benefit of the temporary stored proc created. This temporary stored
proc can be executed many times whithout the overhead for SQL Server to
process a new plan for it.

After reading all what has been said about timestamp, I have been able to
pass the timestamp as a RDO parameter with rdExecDirect. But then, I have
lost the benefit of the temporary stored proc since it is not created with
this option.
After a mad week trying to figure out how to do it, here is the solution,
since I think everyone having the same problem might be interested in:

The idea is to force SQL Server to do an implicit type conversion plus
using VB compatible data types (long integer). I try to make explicit
conversion on the right of the '=' sign, but with SQL Trace, I realised
that ODBC was always asking SQL Server what datatype the expression was on
the left. So the conversion has to be on the left!

(general) (declarations)
Private Type half_tmstp_type
    tmstpb1 As Byte
    tmstpb2 As Byte
    tmstpb3 As Byte
    tmstpb4 As Byte
End Type

Private Type tmstp_int_type
    tmstp_lg As Long
End Type

Private Sub Command1_Click()
    Dim ps As rdoPreparedStatement
    Dim tmstp_tmp As half_tmstp_type
    Dim tmstp_1 As tmstp_int_type
    Dim tmstp_2 As tmstp_int_type

    dbCn.Execute "BEGIN TRANSACTION", rdExecDirect

    'create a prepared statement to update a row with "manual" concurrency
control
    Set ps = dbCn.CreatePreparedStatement("", "Update COMPANY set
OFFICL_NAME = 'MISE JOUR' WHERE COMPANY_NO = ? AND 0 |
SUBSTRING(contrl_ind,1,4) = ? AND 0 | SUBSTRING(contrl_ind,5,4) = ?")

    ps.rdoParameters(0) = 19   'COMPANY_NO

    'transfer 1st half of timestamp, resulting in an integer datatype
    tmstp_tmp.tmstpb1 = MSRDC1.Resultset.rdoColumns(3)(3)
    tmstp_tmp.tmstpb2 = MSRDC1.Resultset.rdoColumns(3)(2)
    tmstp_tmp.tmstpb3 = MSRDC1.Resultset.rdoColumns(3)(1)
    tmstp_tmp.tmstpb4 = MSRDC1.Resultset.rdoColumns(3)(0)
    LSet tmstp_1 = tmstp_tmp 'like memcpy

    'transfer 2nd half of timestamp, resulting in an integer datatype
    tmstp_tmp.tmstpb1 = MSRDC1.Resultset.rdoColumns(3)(7)
    tmstp_tmp.tmstpb2 = MSRDC1.Resultset.rdoColumns(3)(6)
    tmstp_tmp.tmstpb3 = MSRDC1.Resultset.rdoColumns(3)(5)
    tmstp_tmp.tmstpb4 = MSRDC1.Resultset.rdoColumns(3)(4)
    LSet tmstp_2 = tmstp_tmp  'like memcpy

    ps.rdoParameters(1) = tmstp_1.tmstp_lg
    ps.rdoParameters(2) = tmstp_2.tmstp_lg

    ps.Execute
    dbCn.Execute "COMMIT TRANSACTION", rdExecDirect

End Sub

--
Yann
SNC - LAVALIN



Tue, 10 Aug 1999 03:00:00 GMT  
 VB, RDO - ODBC, SQL Server and Timestamp column manipulation

You are right. But as soon as you want to benefit the use of temporary
stored proc, you have to deal with datatype conversion, because SQL Server
forces you to use BINARY datatype.
Since a temporary stored proc can be reused 1000s of times, I generally
prefer this way, especially in a heavy batch.
--
Yann
SNC - LAVALIN



Quote:
> If you do a VarType on the timestamp field in a resultset, you find
> it's a byte array with eight elements.  It stores in a Variant
> correctly, and probably will work cleanly that way.

> I do all my I/O with ASCII SQL strings instead of RDO field
> assignments, so I translate it to a hex string, and that works for me.

> Josh



Sat, 14 Aug 1999 03:00:00 GMT  
 VB, RDO - ODBC, SQL Server and Timestamp column manipulation

If you do a VarType on the timestamp field in a resultset, you find
it's a byte array with eight elements.  It stores in a Variant
correctly, and probably will work cleanly that way.

I do all my I/O with ASCII SQL strings instead of RDO field
assignments, so I translate it to a hex string, and that works for me.

Josh



Sat, 14 Aug 1999 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. VB, SQL Server, RDO, ODBC and Timestamp column manipulation

2. Formatting SQL Server TimeStamps with RDO ?

3. VB Sql Server Via ADO Problem: [Microsoft][ODBC Sql Server Driver]

4. RDO, SQL Server and IDENTITY columns

5. RDO: Connecting to SQL server table containing an identity column

6. VB4, SQL Server 6.5, RDO and ODBC

7. RDO, ODBC, SQL Server and Nulls...

8. SQL Server 6.5 timestamps to VB?

9. pass a SQL Server timestamp as variable in VB

10. VB and SQL Server TIMESTAMP

11. LONDON - Senior Developer - VB/SQL/ODBC/RDO/VBA - Equity Derivatives Team - Investment Banking

12. VB 4/5 and RDO and ODBC and SQL Anywhere

 

 
Powered by phpBB® Forum Software