SQL - Accessing data in BLOB

Off Topic
1

Totally not related to Epicor… But if you wanted to make up a use case, pretend you have QC data in an external DB and want to pull that info into a report in Epicor.

My real case …
I have a BLOB field in a SQL database that holds raw data from a devices memory. I need to access specific data, knowing the offset from the beginning.

For example: imagine my blob (field name Data_Blob) has the following binary data stored in it

7f 00 0a e3
18 0b 42 bf
83 20 01 fe
00 00 00 5a
91 03 0a f8

It’s all sequential (no line breaks), I added them as every “chunk” of data I need is at a 4 byte interval. And those are just the hex representations of the bytes in the blob.

I need to extract the 18, 0b, 42, and bf. Prefer getting them one at a time (offsets of 0x004, 0x005, 0x006, and 0x007) But getting one 4-byte number (0x180b42bf) from (0x004) would be okay.

I’ve tried using substring(Data_Blob,4,4), but it returns nothing. Checked to see if substring even returns anything from a blob with

SELECT recID, length(substring(Data_Blob,0,1)) AS SubBlob, Data_Blob FROM `h10_data` ORDER BY `RecID` DESC;

and that returns a big fat zero in the SubBlob field. I’m guessing that the SUBSTRING only works on strings, which only ever have a null char to indicate the end of the string. And with my BLOB having “null’s” in it, SUBSTRING terminates when it hits one. Or it just doesn’t like BLOBs.

Any thoughts on how I can access specific bytes in the BLOB?

And for what its worth, I did do a lot of Googling on this topic. but everything I come across about BLOBs, assumes they are the contents of a file and you’re just storing them and retrieving them. Never doing anything with their actual contents. And every example for “displaying” the contents is based on downloading the contents and then sending them to a viewer for their type (jpeg, png, WAV, etc…)

1 Like
2

Have you ever embarked on the perilous quest of transforming a stubborn BLOB into a string, much like trying to convince a cat to take a bath?

SELECT CAST(blob_column AS VARCHAR(MAX)) AS converted_text
FROM your_table;

This is not Tested, but just an idea

2 Likes
3

This works:

CONVERT(varchar, SUBSTRING( CAST(403391167 as BINARY(4)) , 1, 1), 2) + ' ' +
CONVERT(varchar, SUBSTRING( CAST(403391167 as BINARY(4)) , 2, 1), 2) + ' ' +
CONVERT(varchar, SUBSTRING( CAST(403391167 as BINARY(4)) , 3, 1), 2) + ' ' +
CONVERT(varchar, SUBSTRING( CAST(403391167 as BINARY(4)) , 4, 1), 2)

Yields 18 0B 42 BF

I would try to cast your blob field to BINARY(LENGTH), and then use the above method with substring to get at it.

maybe

CONVERT(varchar, SUBSTRING( CAST(Data_Blob as BINARY(16)) , 5, 1), 2)

or…
you may have unicode so length may be 2, I don’t have a blob to play with

CONVERT(varchar, SUBSTRING( CAST(Data_Blob as VARBINARY(MAX)) , 5, 2), 2)
4

Just some food for thought, I was testing by converting XXXChunk.Chunk to binary, and I guess it’s full unicode because this:

CONVERT(char, SUBSTRING( CAST(XXXChunk.Chunk as VARBINARY(MAX)) , 1, 8), 1)

yielded 0x51006C0070006F00, which translates to Q l p o (imagine the blanks as nulls), whereas if it was ascii or simple ascii utf8 would have been 0x516C706F and Qlpo

5

You can also cast 4 byte sections out as INT… or a 1 byte section as a TINYINT

CAST(SUBSTRING( CAST(403391167 as BINARY(4)) , 1, 4) AS INT)

CAST(SUBSTRING( CAST(403391167 as BINARY(4)) , 1, 1) AS TINYINT)
6

Hmmm … A lot of ways to approach this.

Ended up skinning the cat in a slightly different way…

CONV(MID(CAST(Data_Blob AS VARCHAR(8192)),4201,2),16,10) AS 'DOM_Day'

With the full select to get Day, Month and year of Mfg…

SELECT RecID, CONV(MID(CAST(Data_Blob AS VARCHAR(8192)),4201,2),16,10) AS 'DOM_Day', CONV(MID(CAST(Data_Blob AS VARCHAR(8192)),4203,2),16,10) AS 'DOM_Mon', CONV(MID(CAST(Data_Blob AS VARCHAR(8192)),4201,2),16,10)+2000 AS 'DOM_YR' FROM `h10_data` ORDER BY `RecID` DESC;

To get …
image

1 Like
7

Nice to know VARCHAR doesn’t care about the nulls.

8

Ok now that we have a skinned cat, tell us more about what you are doing.

curiosity GIF

3 Likes
9

We make a sensor that was designed back in the 1990’s, that has a basic EEPROM memory chip in it. When the sensor is recalibrated while it’s in the instrument, the call factors are stored in the EEPROM. It’s a basic SPI interface. Will still use this antiquated design to remain compatible with older equipment.

To collect data (for SPC) from sensors returned to us for recalibration, I’m making a tool to read the contents of the EEPROM (along with some other some voltages). I figured storing the results in a DB would be the most versatile way to go. Especially with service centers in various locations.

Reading the EEPROM and other parameters of the sensor was easy. A raspberry pi, a few discreet components to interface to the sensor, and some python code was all I needed.

The backend is a MySQL DB, and some PHP code to act like a basic API to receive the requests, make the connection to the DB and execute an INSERT query.

Using a BLOB to hold the image of the EEPROM seemed like a good idea at the time. Saving it in the DB was quite easy. Working with it afterwards, not so much. I’ve since just decided to save it as a string of hex characters. It takes twice the space, but 8k vs 4k really isn’t an issue.

Twas a fun little project. Last part to do is write a reporting system. If only SSRS worked with MySQL…

3 Likes
10

image
image4032×3024 4.4 MB

image
image3024×4032 4.42 MB

4 Likes
11

https://dev.mysql.com/downloads/connector/odbc/

3 Likes
12

I think that still requires a MS SQL server - as that’s where the reports (RDL’s) are stored. It’s just how to point the SSRS report to the external data source in a MySQL server.

2 Likes
13

you couldn’t run an express instance? Or is that not a thing anymore?

1 Like