Using GetBytes with varbinary field

  • Thread starter Thread starter Dan
  • Start date Start date
D

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.
..
 
Dan said:
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.
Click to expand...

Could you give an example of how you're using GetBytes?
 
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()
 
Dan said:
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(")")
Click to expand...

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()
Click to expand...

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*.
 
Back
Top