Question

Help with reading blob from db3 file using FME

  • 12 April 2024
  • 4 replies
  • 54 views
manojse07
Rank
Badge +1

I am attaching a sample db3 file that I am using for a project. This db3 file contains many fields of blob type. The table, field of my interest are as below

Table Name: CURRENT_TAG

Field Name: _TAGOBJ_SUBSTATIONGID

select _TAGOBJ_SUBSTATIONGID from CURRENT_TAG where _TAGOBJ_GID = '145242397947723855';

This particular record in my application shows this value “151433541765593417”

But in db3 file it is actually stored as blob. How do I decode the blob as text? I tried to use online tools to convert this binary to text - it doesn’t work. Also, how do I know the data format that is stored as blob - is it either image, xml, json etc.?

1 Attachment

4 replies

crystalatsafe
Rank
Badge +4

Hi @manojse07 

 

I tested this out and although I can’t quite tell if the decoded result is what you were looking for I was able to decode the blob format.

 

I first read in the table from that zip file with an SQLite reader. 

Then I added a TextDecoder transformer. 

 

I set the ‘encoding type’ to Base64 and the ‘string to decode’ as the _TAGOBJ_SUBSTATIONGID field. 

You can try a different value for the ‘character encoding for binary data’ as each one will give a slightly different result. 

 

I hope this works! 

 

Crystal 

manojse07
Rank
Badge +1

Thanks @cfitzpat for you answer. I did try the transformer you suggested and is still returning some garbage values. The actual value I am expecting is a GUID

 

 

Considering that the data type returned by sqlite reader for blob type is “binarybuffer” can we do something to achieve what is intended?

danminneyatsaf
Rank
Userlevel 2
Badge +10

@manojse07 What software are you viewing the .db3 file in? 

I’ve checked the FME SQLite Reader parameters and it detects the data type as a Blob for your dataset so it should be reading in the values correctly.

 

I’ve cross-referenced reading the data in other database management programs (DBeaver) and a SQLite specific database reader (https://alpha.sqliteviewer.app/) and the results are consistent with what FME produces.

 

manojse07
Rank
Badge +1

@danminneyatsaf All the software you mentioned show the hex representation of the blob object. My problem statement is that I have to decode it to the text. If you can check my question - the value is actually a GUID that is encoded as a blob in the db3. So, when I read using FME I have to decode as a GUID.

Reply


Community Stats

28,738
Posts
109,568
Replies
36,577
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.