OleDbDataReader and TEXT field

[£ukasz Ledóchowski]

Hi!

I am using OleDbConnection to access SQL Server and Oracle databases.
On SQL Server I have a table that is used to store files in TEXT data
type field. It should be IMAGE, but I didn't create the table and can't
change it. When I try to access this field by OleDbCommand and
OleDbDataReader, I get string value. String, that doesn't represent
whole file, but only first bytes of file, ending with 0. Of course, if
I access IMAGE field, I get byte array and everything works fine. What
can I do to access TEXT field through OleDbConnection and get byte
array?

 

[Jeroen Mostert]

I am using OleDbConnection to access SQL Server and Oracle databases.
On SQL Server I have a table that is used to store files in TEXT data
type field. It should be IMAGE, but I didn't create the table and can't
change it. When I try to access this field by OleDbCommand and
OleDbDataReader, I get string value. String, that doesn't represent
whole file, but only first bytes of file, ending with 0. Of course, if
I access IMAGE field, I get byte array and everything works fine. What
can I do to access TEXT field through OleDbConnection and get byte
array?

You can try using OleDbDataReader.GetBytes() directly, but it might very
well not work because the field isn't a byte array.

Failing that, you're in trouble, because you have to use .GetChars(). The
reason that's trouble is that the field is encoding-specific TEXT. When
reading it as characters, .NET will do its best to convert the text in that
field to Unicode characters. Problem is, depending on the encoding
configured at the server and the actual contents of the column, this might
fail. If it does, the resulting replacement characters will make it
impossible to retrieve the original contents.

You might get lucky if the server uses an encoding that happens to map all
the characters in the range U+0000 - U+00FF to the same byte values and back
(like ISO-8859-1), or if you can tell it to use that encoding for the
column. In that case, conversion is as easy as reading the characters and
casting them to bytes, and this will "just work". If it doesn't "just work",
however, you'll end up with corrupt data.

Alternatively, you can try getting the server to convert the data itself by
using TEXTPTR and READTEXT to pump it into a VARBINARY variable and
returning that. However, this requires multiple SQL statements to read a
single column, and is really quite a clumsy solution (it certainly doesn't
integrate neatly with a DataReader). It should get around any encoding
trouble, though, which may not even be possible on the client side.