How do I get system.byte to MySQL Bit type?

Question

I have a MySQL database (5.6 Community) with a column of type bit(60). The field holds values such as 1001, 0011, etc. I am trying to pass a string of "1010" to the database through a vb.net adapter. If I use a regular query it would look like this:

insert into my_table (my_bit_field) values (b'1010');

This works and inserts the string exactly as shown but I need to use a data adapter so I can't send the query directly.

When using the data adapter in vb.net, I was getting an error saying that it was expecting a byte array. So I tried using that:

System.Text.Encoding.ASCII.GetBytes("1010")

but it just converted it to its ASCII representation of bytes (49,48,49,48).

Is there a better way to go through the data adapter and is there a way to do this?

Thanks.

How are you storing the values in your code? The post is using literals (of sorts) — Ňɏssa Pøngjǣrdenlarp, Feb 05 '16 at 18:34
In a string the literal "1010" is stored as: Dim bit_code as string = "1010". I just wanted to show its true value in the example. — John, Feb 05 '16 at 18:39
`(b'1010')` is a cast, but MySQL saves it as `10`. If you query it, you get it back as ULong. Net doesnt have a bit datatype, byte being the smallest, but a *string* is a bad starting point...it isnt even numeric. What does this represent? — Ňɏssa Pøngjǣrdenlarp, Feb 05 '16 at 18:46
I can get it to work (not with string, but actual bits), but there are a fair number of conversions involved. If you are happy with the string form, why not save it as string? — Ňɏssa Pøngjǣrdenlarp, Feb 05 '16 at 19:11

score 0 · Answer 1 · edited May 23 '17 at 12:30

In this case, you could try the following to convert your string to a byte array:

Dim bytes() As Byte = { Convert.ToByte("1010", 2) }

However, that breaks once you have more than 8 bits in your string. You could (perhaps should) break the string into byte-sized sections and convert each to a byte, such as for this question. Since you have a bit(60) column you could also cheat a little and use something like this:

Dim inputValue As String = "000100000010000000110000010000000101000001100000011100001000"
If inputValue.Length > 60 Then ' up to 64 could be supported
    ' Error
End If
Dim longValue As ULong = Convert.ToUInt64(inputValue, 2)
Dim bytes() As Byte = BitConverter.GetBytes(longValue)
If BitConverter.IsLittleEndian Then
    Array.Reverse(bytes)
End If

This will give you the byte array that you can presumably use in your data adapter code that was not shown.

How do I get system.byte to MySQL Bit type?

1 Answers1