sql-serverflutteruint8list

Storing Uint8List into SQL Server

I'm using Flutter and I'm Working on a functionality of getting a list of pictures from gallery and converting them to List<Uint8List> to store them in SQL Server Database.

I have a problem with the right data type to store each Uint8List data. When I was working with byte[] in other languages I was storing the data using varbinary(MAX) and it looked something like this: 0x89504E470D0A1A0A0000000D49484452000000380000004A080400000044655F5F0000016A49444154785EEDDA3F6AC33014C0E1DA2DC42521B3C76CB940F65CA2BD434ED06EED0172852CD93364EED05E2174CA0D32650A34E03FFA55E60D6F2826B5239B52DE7B48605BF2C7434618EC9B1E024D39EC33F80FA081E1C340030D34D040030D34D040460C48B86F980903468D4122DF5EC938F1D5304F64BCC81D1A81C40C58D326D624C4CD40212312B640F

Now with List<Uint8List> each file's data looks like:

[255, 216, 255, 225, 1, 182, 69, 120, 105, 102, 0, 0, 77, 77, 0, 42, 0, 0, 0, 8, 0, 7, 1, 16, 0, 2, 0, 0, 0, 26, 0, 0, 0, 98, 1, 0, 0, 4, 0, 0, 0, 1, 0, 0, 3, 192, 1, 1, 0, 4, 0, 0, 0, 1, 0, 0, 5, 0, 1, 50, 0, 2, 0, 0, 0, 20, 0, 0, 0, 124, 1, 18, 0, 3, 0, 0, 0, 1, 0, 1, 0, 0, 135, 105, 0, 4, 0, 0, 0, 1, 0, 0, 0, 152, 1, 15, 0, 2, 0, 0, 0, 8, 0, 0, 0, 144, 0, 0, 0, 0, 65, 110, 100, 114, 111, 105, 100, 32, 83, 68, 75, 32, 98, 117, 105, 108, 116, 32, 102, 111, 114, 32, 120, 56, 54, 0, 63, 63, 63, 63, 58, 63, 63, 58, 63, 63, 32, 63, 63, 58, 63, 63, 58, 63, 63, 0, 117, 110, 107, 110, 111, 119, 110, 0, 0, 16, 130, 157, 0, 5, 0, 0, 0, 1, 0, 0, 1, 94, 130, 154, 0, 5, 0, 0, 0, 1, 0, 0, 1, 102, 146, 146, 0, 2, 0, 0, 0, 4, 63, 63, 63, 0, 146, 145, 0, 2, 0, 0, 0, 4, 63, 63, 63, 0, 146, 144, 0, 2, 0, 0, 0, 4, 63, 63, 63, 0, 146, 10, 0, 5, 0, 0, 0, 1, 0, 0, 1, 110, 146, 9, 0, 3, 0, 0, 0, 1, 0, 0, 0, 0, 136, 39, 0, 3, 0, 0, 0, 1, 0, 100, 0, 0, 144, 4, 0, 2, 0, 0, 0, 20, 0, 0, 1, 118, 144, 3, 0, 2, 0, 0, 0, 20, 0

So I'm confused about how to store such data and what data type in SQl Server should I use.

Thanks in advance.

Solution

  • SQL Server does not have a data type that maps directly to a list, so the correct choice probably depends on how you intend to work with the list values.

    Some options:

    1. Create a new table and store each list value in a separate row along with the key value that relates it back to the main table. That way you can easily work directly with all the values individually and can define the values as INT, but with the trade off of an additional table and ensuring that you relate the values correctly.
    2. (N)VARCHAR(MAX) - storing them as character values allows you to store the list as '[255, 216, 255, 225, 1, 182, 69, 120, 105, 102, 0, 0, 77, 77, 0, 42]'. This keeps the list separators and the brackets as boundary and then lets you read that back to your code. However, there is conversion between INT and CHAR data types to consider. If you needed to work on the values of the list you would need to use STRING_SPLIT to convert the list to a table and then also further CAST/CONVERT the data type from CHAR back to INT.
    3. JSON - you could also manipulate your list into JSON format and store that again in a VARCHAR(MAX) column. The benefit is that you can use the JSON functions in SQL Server to work with the list items.

    Hope this helps