There may be occasions where it is necessary to understand BLOB data from within MySQL, rather than through a UI. This can easily be done by querying the relevant table and converting the BLOB to text. In the below example, we will be doling this for Device Director, however, this could be done for any other database.
- Identify the BLOB data that needs converting
- In the Device Director Profile_Parameter table, the BLOB data here will outline the profile configuration information.
- To convert this, we need to specify the column that needs converting and any other column that you’d like to be visible.
- Select Profile_ID,CONVERT (VALUE USING utf8), ID From devicedirectortfo.profile_parameter
- The above will convert all rows in the table.
- So that we can limit the returned rows, I know that Profile ID 15 correlates to the profile information I need (If you are unsure of which profile ID you ned, simply query the profile table – Select * from devicedirector.profileand get the relevant ID)
- The above query can then be edited
- Select Profile_ID,CONVERT (VALUE USING utf8), ID From devicedirectortfo.profile_parameter Where Profile_ID = '15';
- Executing the above will return the following
With the above, it is now possible to uncover the relevant settings necessary to configure your profile. However, not all BLOBs will convert to usable data or will state as being NULL.
ID Values
The above process will not work for ID fields, therefore a different query will need to be used:
SELECT Hex(ID) FROM xchangemanager.media;
Hex(INSERTCOLUMNNAMEHERE) will convert the BLOB value to the numeric value.