Convert base64 code to image

[Quannd]

Hi all,

In the code above, I converted a data from MySQL into base64encodeString. Next, I want to convert this string into an image to display on Mashup.

Any help is appreciated.

Thanks!

[Rajesh_Sayana]

Hi,

From the image you have attached, it appears that the data you are trying to convert to image is of type BLOB. Please confirm if you have the data type as Blob on your MySQL end. If so, you can directly convert it to image by creating a service that accepts input parameter as base type Blob and set the return type of the service to Image. Assign the input parameter of type Blob to the result of the service. The obtained result will be the converted image. Please let me know if that helps.

[Quannd]

Hi Rajesh,

The "input" is the data in my MySQL and could you explain more details about the service you said? Does Thingworx snippet have it?

Thanks.

[Rajesh_Sayana]

Hi, Thank you for the reply. I understand that "input" is the data in MySQL. I am trying to understand the data type in which it is saved in MySQL. The scenario I have explained works if your input is of datatype Blob in MySQL. I first would like you to check the datatype of the input on your MySQL. If it is of type Blob, you can refer the attached service to convert it to Image. The output of the service should be image and the input should be Blob. That is all you have to do.

Kindly let me know if this helps or if you have a different datatype on MySQL.

Best regards,

Rajesh.

[Constantine · Accepted solution]

Hello,

Please let me know if this is what you need:

var input = "0x89504E470D0A1A0A0000000D4948445200000020000000200802000000FC18EDA30000000173524\
74200AECE1CE90000000467414D410000B18F0BFC61050000000970485973000012740000127401DE661F78000000C\
949444154484BEDD3C10DC230104451D7E5825C4FAA713329C608C9875D67767656825BE64402FA0F92D0D69FF702E\
964608E6636E63E9D4E057CBFF5EBDE6F641381A35F1034E0D1D70509007D595000DBEF7DBF500501B0FD31CD9124E\
480EFBB63454881B3BFEEAB749532E0D1AF0A0900FA458103B01F9E86A340182A080CB099E352E802016C848E0A316\
06F6532268440A14F850828F5991000CAA3EEBE43286040E98B0204B4BE262040FBEDDFB927197F12007A9FFE17F70\
050E80B02BEC93FDC0B245BEB0314878ED55E0206490000000049454E44AE426082";

var binaryString = "";
for (var i = 2; i < input.length; i += 2) {
    binaryString += String.fromCharCode(
        parseInt(
            input.substr(i, 2), 
            16
        )
    );
}

var result = base64EncodeString(binaryString);

Note that:

1. Make sure that the Service return type is IMAGE
2. No need for MIME type declaration, besides 0x89504E470D0A1A0A is PNG file signature, not JPEG
3. Concatenating strings is ugly, but works. Rhino doesn't support ArrayBuffers anyway.

/ Constantine

[Quannd]

Thanks Constantine, this is exactly what i need.

[Quannd]

Sorry Constantine,
There are two types of photo data in my MySQL: "0x89504..." and "0xFFD9F..." and the code that you sent me only works with the first data. Please help me with this problem.

Thank you.

[Constantine]

Hmmmmm FFD9 doesn’t look like JPEG signature, which should be FFD8 instead. Could you attach a complete example, please?

[Quannd]

Sorry, it's my mistake. I did attach a complete sample, please check it.

Thank you.

[Constantine]

This file doesn't look like a valid JPEG image. It has correct JPEG header, but I guess it's incomplete, because:

1. The HEX sequence has odd length, which obviously indicates truncation
2. All JPEG files should contain two bytes FF D9, which indicate end of file. Those are not present in your example.
3. When I convert it to binary file manually it won't open in any image viewer

So, I'd say you need to first make sure that you store and read those strings (blobs?) in/from MySQL correctly in the first place.

[Quannd]

Thank you, Constantine. I understood what you said.

If this data is JPEG's data, could you explain to me how to convert it? (One example maybe)

[Constantine]

If it's a valid JPEG content, then the same code will work just fine, here's an example (it's a JPEG this time):

var input = "0xFFD8FFE000104A46494600010101007800780000FFE1005A4578696600004D4D002A00000008000503010005000000010000004A0303000100\
0000010000000051100001000000010100000051110004000000010000127451120004000000010000127400000000000186A00000B18FFFDB\
0043000201010201010202020202020202030503030303030604040305070607070706070708090B0908080A0807070A0D0A0A0B0C0C0C0C07\
090E0F0D0C0E0B0C0C0CFFDB004301020202030303060303060C0807080C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C\
0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0CFFC00011080020002003012200021101031101FFC4001F000001050101010101010000\
0000000000000102030405060708090A0BFFC400B5100002010303020403050504040000017D01020300041105122131410613516107227114\
328191A1082342B1C11552D1F02433627282090A161718191A25262728292A3435363738393A434445464748494A535455565758595A636465\
666768696A737475767778797A838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CA\
D2D3D4D5D6D7D8D9DAE1E2E3E4E5E6E7E8E9EAF1F2F3F4F5F6F7F8F9FAFFC4001F010003010101010101010101000000000000010203040506\
0708090A0BFFC400B51100020102040403040705040400010277000102031104052131061241510761711322328108144291A1B1C109233352\
F0156272D10A162434E125F11718191A262728292A35363738393A434445464748494A535455565758595A636465666768696A737475767778\
797A82838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE2\
E3E4E5E6E7E8E9EAF2F3F4F5F6F7F8F9FAFFDA000C03010002110311003F00FDFCAF947FE0B19FF0550D1FFE0917FB26C5F12B50F0DFFC267A\
8EA3AD5B689A5E84353FECD37D2C81E47266F2A6D8A914523E7CB6C90ABC6EC8FABABF047FE0E20D4EE3FE0A5DFF0005B6FD9CBF646D267926\
D1F419A1BCF11A43210616BC2B7174580FE2874DB61229EDE7B74CD6728CEA55A587A4ED29C92BF44B76DF959357E97F99A45C214EA57A8AF1\
845B696FD95B6EAD7C8FD40FF823A7FC152F4BFF0082B8FEC9727C4CB1F0CA782EFAC759B9D1751D0C6ABFDA6D6524411D099BC9877078E447\
FF0056319232719AFABABF06FF00E0DD9D724FF82797FC16AFF699FD92EEDA4B7D0B54BBB9D43C3D0B4A64086CDCCB6F83FDE934FB956627AF\
902BF792BA67284E952C452568D4846493DF6B3BFF00DBC9FC8E78A9C2A55A151DDC24E2DF4EEBF06915F58D5EDB40D26EAFAF268EDECECA17\
9E795CE1628D0166627B000135FCA2FECE10FED9BFB7E7FC14B7E34FED49FB23E8773A86B71F88EF615D62E27D117FB3AD2EF7A5BDBAA6A8C2\
3761691A2651599140C91B867FA74FDB3FE036B1FB527ECA7E3EF871A178AFFE107D4BC71A34FA2AEBA34F37EDA7473AF972BAC2258B7318D9\
D47EF170581E7183E33FF046CFF8254E97FF000486FD94EF3E1BD8F8A9FC717FAB6B971AEEA3AD3699FD9BF6992448A244583CE9B62A470A0F\
F5872771E3381CD469FF00B44AACDB5CB0B46DD5CA4B993F2E55E5D56BB1BD69FEE234A293E69DE57E8A29F2B5E7CCFCED64FCCFE7C7E37EAD\
FB627FC1373FE0AA1F06FF006A0FDACB40934DF126A5AE40B3EAB03E8CDFDAF616C91DB5DC4CBA5318964FB1CC501750CC08FBDB4E3FABBD33\
53B7D6B4DB7BCB49A3B8B5BB89668658DB724A8C015607B820820D7C8DFF0005A3FF008248E97FF0585FD9AB45F01DDF8BE4F01EA5E1DD7A3D\
6EC3595D2BFB4FCBC432C3242D079D0EE5759739DE30514F3C8AF7AFD90BE07EA5FB337ECB7F0FFE1DEAFE24FF0084C2FF00C0FA0DA684FAD1\
B2366DA92DB44B124AD119652AC51573991B24139E703A28D4FF006674A692E59FBB6EB1924DBF2B497CDB6EC635A3FBF8D58B6F9A1695FA38\
B6A297AC5F9DAC9687FFD9";

var binaryString = "";
for (var i = 2; i < input.length; i += 2) {
    binaryString += String.fromCharCode(
        parseInt(
            input.substr(i, 2), 
            16
        )
    );
}

var result = base64EncodeString(binaryString);

For quick testing of HEX-to-file-to-HEX I usually use simple online tools like those two: 

• HEX to file: https://tomeko.net/online_tools/hex_to_file.php?lang=en
• File to HEX: https://tomeko.net/online_tools/file_to_hex.php?lang=en

I hope this answers your question.

/ Constantine

[Quannd]

Thank you very much, Constantine.

I will try to check my data.

[Quannd]

Hello @Constantine,

Could you guide me on how to convert an image to hex string?

Thank you.

[Constantine]

Hello,

If you need to do it for testing, the easiest is the following:

• Go to https://tomeko.net/online_tools/file_to_hex.php?lang=en
• Uncheck both checkboxes
• Upload your image

If you need to do it in your code, then you need to provide more details -- why, where the image is located, etc.

/ Constantine

[Quannd]

Hello Constantine,

I am creating a custom service which helps me to convert an image to hex string (the first data that's I showed you in this post "0x89504...", "0xFFD8...").

I did create a service like this but the result seems not right.

Could you help me with this service?

Thank you very much.

[Constantine]

There you are:

var blob = Things["SystemRepository"].LoadBinary({ path: '/test.png' });

var hexString = "";
for (var i = 0; i < blob.length; ++i) {
    var hex = (blob[i] & 0xFF).toString(16);
    hexString += (hex.length == 1 ? '0' + hex : hex);
};

var result = "0x" + hexString;

You need to treat images as binary blobs and not base64-encode them.

/ Constantine

[Quannd]

Thank you very much, Constantine.

It works perfectly.

[Quannd]

Please help me with one more problem, @Constantine

Like the service above, an image is converted to hexadecimal("0x89504...", "0xFFD8...") but the output data type is still String.

And then, when I try to insert this value into MySQL table's column with varbinary data type, MySQL forces me to convert the hex result to varbinary in order to insert into the database. But when I use the CAST() function of MySQL with my hex string, the output result is not right and the newly inserted image can't display.

Is there any solution for this problem?

Please check the attachment for more details.

[Constantine]

Hello,

First of all, in most cases storing image content in database is not recommended. Usually a better idea would be to store images as files (e.g. in a dedicated FileRepository), and reference them by name in the database.

Now, if you really have to do it, then check out BLOB data type in ThingWorx. It is designed to represent large arrays of binary data and should map transparently to BLOB in SQL. I'm not sure about VARBINARY though, it's something to test. If you work with BLOBs, then you don't need to convert to HEX string anymore, at least to store it. I didn't work with it before, so cannot help here, but I'm sure you'll find plenty of details in Help and on PTC Support portal.

By the way, you may want to check the max size of VARBINARY in your table. If I remember correctly at least in the past it didn't allow storing arbitrarily large objects. Maybe your images get truncated?

/ Constantine

[Quannd]

I understood. Thank you anyway.