How to insert/select varbinary as byte[] using ADO.NET

joergschaber

Hi,
I am trying to insert and select a byte-array of length 1754, i.e. byte[1754], into a varbinary column in C# using the ADO.NET driver for Vertica 10.
First, I am unable to insert the byte-array as such, but I have to convert it to a string first, e.g. using
Convert.ToBase64String(array ).
When I select this again into a Datatable, I do not retrieve the string, but an byte-array, now of length 2346, which is not the same byte-array as before. To get the same byte-array as before, I first have to convert the result to a string again and then convert to an byte-array, e.g. using Convert.FromBase64String.

Is there an easier way to do this?

Hibiki

@joergschaber I think you can insert the binary data into VARBINARY column using the following codes.

// Prepare the byte array value
byte[] bytes = ...

// Prepare INSERT statement, set the value, and execute.
command.CommandText = "INSERT INTO table VALUES (@VALUE)";
command.Parameters.Add(new VerticaParameter("VALUE", VerticaType.Binary));
command.Parameters["VALUE"].Value = bytes;
command.ExecuteNonQuery();

Then, you can retrieve the binary data through VerticaDataReader.GetBytes().

Hibiki

@joergschaber Please use VerticaType.Bit for a boolean value.

joergschaber

Works! Thanks.
When I want to insert a Boolean value, what Verticatype do I use? There seems to be no VerticaType.Boolean.