Using GetBytes with varbinary field

[Dan]

I'm storing a byte array in a varbinary field in a SQL
Server 2000 database, eg:

dim b() as byte = new byte() {1,2,3,4}

but when I attempt to retrieve the byte array (using
OdbcDataReader.GetBytes), my array contains different
information than what is stored.

When I query the field using query analyzer, the data
displayed is as I inserted. I believe that sql server
stores varbinary in hexadecimal format and when it comes
back to vb, it's obviously in a different, and unexpected
format.

Is there a mechanism to retrieve the exact data that was
inserted?

Thanks in advance.
..

 

[Jon Skeet [C# MVP]]

I'm storing a byte array in a varbinary field in a SQL
Server 2000 database, eg:

dim b() as byte = new byte() {1,2,3,4}

but when I attempt to retrieve the byte array (using
OdbcDataReader.GetBytes), my array contains different
information than what is stored.

Could you give an example of how you're using GetBytes?

 

[Dan]

Jon,

Thanks for the reponse. I have pasted below the relevant
code snippet:

Dim conn As OdbcConnection = New OdbcConnection
("DRIVER={SQL
Server};SERVER=<server_name>;Trusted_connection=yes;DATABAS
E=<database_name>;")
conn.Open()

Dim b() As Byte = New Byte() {1, 2, 3}
Dim sql As New StringBuilder("INSERT INTO test (id,
byteArray) VALUES(1, 0x")
For i As Integer = 0 To b.Length - 1
sql.Append(b(i))
Next
sql.Append(")")

Dim comm As OdbcCommand = New OdbcCommand
(sql.ToString, conn)
comm.ExecuteNonQuery()

comm = New OdbcCommand("SELECT * FROM test WHERE id =
1", conn)
Dim reader As OdbcDataReader = comm.ExecuteReader()
If reader.Read Then
Array.Clear(b, 0, b.Length)
reader.GetBytes(1, 0, b, 0, b.Length)
'The byte array now contains the following values:
'{1, 35, 0}
End If
reader.Close()
conn.Close()

 

[Jon Skeet [C# MVP]]

Thanks for the reponse. I have pasted below the relevant
code snippet:

Dim conn As OdbcConnection = New OdbcConnection
("DRIVER={SQL
Server};SERVER=<server_name>;Trusted_connection=yes;DATABAS
E=<database_name>;")
conn.Open()

Dim b() As Byte = New Byte() {1, 2, 3}
Dim sql As New StringBuilder("INSERT INTO test (id,
byteArray) VALUES(1, 0x")
For i As Integer = 0 To b.Length - 1
sql.Append(b(i))
Next
sql.Append(")")

That looks to me like it will insert a single value: "0x123". Now I'm
not familiar with inserting varbinaries directly in SQL (I'd recommend
using parameters for precisely this reason) but it looks dodgy to me.

Dim comm As OdbcCommand = New OdbcCommand
(sql.ToString, conn)
comm.ExecuteNonQuery()

comm = New OdbcCommand("SELECT * FROM test WHERE id =
1", conn)
Dim reader As OdbcDataReader = comm.ExecuteReader()
If reader.Read Then
Array.Clear(b, 0, b.Length)
reader.GetBytes(1, 0, b, 0, b.Length)
'The byte array now contains the following values:
'{1, 35, 0}
End If
reader.Close()
conn.Close()

That doesn't surprise me - note that 0x123 is 1*256+35. I think if you
fix the insertion, you'll find it all works. Parameters are great - I
suggest using them for pretty much *everything*.