VB, SQL Server, RDO, ODBC and Timestamp column manipulation 
Author Message
 VB, SQL Server, RDO, ODBC 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
    Set ps = dbCn.CreatePreparedStatement("", "Update COMPANY set
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

    dbCn.Execute "COMMIT TRANSACTION", rdExecDirect

End Sub


Tue, 10 Aug 1999 03:00:00 GMT  
 [ 1 post ] 

 Relevant Pages 

1. VB, RDO - ODBC, SQL Server 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