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

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?

Best Answers

  • Options
    HibikiHibiki Vertica Employee Employee
    Answer ✓

    @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;

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

  • Options
    HibikiHibiki Vertica Employee Employee
    Answer ✓

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


  • Options
    joergschaberjoergschaber Vertica Customer

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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file