Byte Array Image from Database to JMP

[#1]

Hello everyone,

we have a tricky problem. We have some images in a database stored as a byte array. Using SQL, we want to get those byte array images from the database into a jmp data table (or picture box).

First of all, is this even possible? And if yes,  a little sketch of how to do this would be highly appreciated. Right now, we have no idea of how to start...

Thank you very much!

[#2 · Accepted solution]

I think JMP does not support the binary data transfer from SQL.  But, if you can get the binary data to JMP, perhaps as hex encoded data into a character column in a data table, then it can be turned back into a picture.  You'll need to know what the format of the data is, JPG, PNG, RAW.  If RAW, you'll need to know what the raw format is and how to work with it.

When I first read the question, I imagined the byte array was holding RAW RGB data, but it seems more likely it would be a PNG or JPG, which would be a simpler problem than decoding the RAW data.

[#3]

Hi Craig,

thank you for your anwser. Yes, it is indeed hex encoded data. We actually read in a JPG with a .NET Software into a database, where it is saved in a varbinary(max) type of column. So as you say, I guess that it is possible to transfer the hex encoded data to jmp via a SQL query. Regarding this, we have two questions:

1. How do you read in a varbinary(max) column into a character column in jmp via a query?

2. What has to be done, to transform the data in the character column back to a picture?

I know those questions may go way overboard, but any little hint is appreciated.

Thanks!

EDIT: I stumbled across this thread, which addresses what we want to do. Referring to this, i guess it is necessary to transfer the BLOB data to hex in the database first and then transfer it to JMP.

[#4]

Sorry to take so long.  Been doing some research on this (thanks Bryan), I think it is going to be limited by a 64K buffer in JMP 12 that will prevent it from working with pictures bigger than 32K (after doubling their size in hex they hit the 64K problem).  That's not a lot, even in a JPG.  Bryan (knows way more SQL than me) says "If I made an “Encoded” column of VARCHAR(max) and encoded the varbinary to it using CONVERT, then I do get the HEX String data as expected."  Here's a reference https://basitaalishan.com/2014/09/11/sql-server-converting-binary-data-to-a-hexadecimal-string/ I found; leave off the 0x (style) on the front of the hex you send to JMP.

Once you have that hex data from convert, and get it into a JMP data table, you should be able to get the picture back like this:

//hex = hex(loadtextfile("$desktop/hi.jpg",blob)); // here's how I got some hex data

//write(hex);

hex="FFD8FFE000104A46494600010101006000600000FFDB0043000201010201010202020202020202030503030303030604040305070607070706070708090B0908080A0807070A0D0A0A0B0C0C0C0C07090E0F0D0C0E0B0C0C0CFFDB004301020202030303060303060C0807080C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0CFFC0001108006A007003012200021101031101FFC4001F0000010501010101010100000000000000000102030405060708090A0BFFC400B5100002010303020403050504040000017D01020300041105122131410613516107227114328191A1082342B1C11552D1F02433627282090A161718191A25262728292A3435363738393A434445464748494A535455565758595A636465666768696A737475767778797A838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE1E2E3E4E5E6E7E8E9EAF1F2F3F4F5F6F7F8F9FAFFC4001F0100030101010101010101010000000000000102030405060708090A0BFFC400B51100020102040403040705040400010277000102031104052131061241510761711322328108144291A1B1C109233352F0156272D10A162434E125F11718191A262728292A35363738393A434445464748494A535455565758595A636465666768696A737475767778797A82838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE2E3E4E5E6E7E8E9EAF2F3F4F5F6F7F8F9FAFFDA000C03010002110311003F00FDFCA28A2800A28A2800A28A2800A28A2800A28A2800A28A2800A28A2800A28AF2EFDB3FF698B4FD90BF66CF1378FAEAD46A0FA3451A5A59F9810DDDC4B22C51264F38DCE19B1C8456207159D5AB1A707526EC92BBF4476E5B97E231F8BA581C2479AA55928C577949D92F9B67A8D15E0FFF0004DDF895F127E33FECADA3F8C3E27BE9FF00DB3E279A5D474F8AD6D3ECDE4E9EF8FB3875E84B00CEAC3AC7247925B26BDE2A70F5956A71AB14D26AFAEE6D9D655532CC7D6CBEACA32952938B71778B717676765757EB60A28A2B63CC0A28A2800A28A2800A28A2800A28A2801971711DA5BBCB2BA45144A5DDDD82AA28192493D0015F969278374FFF0082CFFF00C149FC5D6FA9EA5AE7FC2A2F86161F65B2974C95163BB956608BF39DCA3ED0FF006990385DCD140AB952011EF3FF00058FF8DBE30D174CF869F083C1D7969A4DCFC74D526F0F5E6A532963040CF6D6ED1F438590DE0DCC06E0A840E5ABDC7F619FD8CB41FD877E06DA78474A961D4F51691EE355D67EC82DA6D5A62EC559D773615108455DC400BEA589F0319178EC52C25BF774ECE7E6ED751FC6EFA1FB3F0C578708F0F4B88D54B637191953C325BD382972D5ACDDAC9DE2E9C15F9B56F6BDBD774CD32DB44D36DECACEDE1B4B3B489618208502470C6A02AA2A8E028000007000A9E8A2BDF3F19949B777B9F33FFC15BFF686BAFD9D3F621F13EA3A2F893FE11BF166A725AD8E8B2C5284BA95DAE623308BBEE16E263B87DDC6720E2BD6FF00655F116ABE30FD97FE1BEADAF4D3DC6B9AA785B4CBBD4659976CB25CC96913CACC3030C5CB123039AF8ABF68EB1B4FDB57FE0B4FE02F00CBE66B5E0FF84DA49D5B5AB3CA35BC37433392C0E43A348DA7452291CE1978E6BF4480C0C0E00AF23055275F1756B5FDC8FB89776B56FEF76F91FA6715E0B0B94F0E65D95B82789AC9E2672B2BC61512853A77B5EDCB0F68D3D13969BDC2BCFBF6AAF893E2AF841FB3EF89BC49E09F0C4BE32F146956E925868F1A3C8D76C65456C2A7CEFB1199F6AFCCDB303935E83457A9522E50718BB36B7EDE67E7981C453A189A75EAD355231926E2EE949277716D59D9AD1D9A767A1F2CFECA7FF0570F84BFB40781629BC45E25D0FE1E78AACD366A9A46BF7C962B0CA0ED6314B31549149CE003BC7F128EFCFF00EDBDFF000570F0BFECDB61E0FD53C13AB7C3FF00891A65FEA86D75EB7D33C496F737F6506CDCAF14513B1E70FF003B0D80AAA9E64047A57C62FF00825F7C08F8F7E3EBCF1478A7E1F59DEEB9A8E0DD5CDBEA17963F6861FC6E96F322339EEC5771EE4D7C13FB79FF00C13F7E1947FB6AFC25F819F0ABC223C2DAAF8AE33AAEB5AA9D52EEED92CB74A18225C4EEBBA38AD6E642300B1F2C03C9CFCCE615B35A187B5E2DDD24D5F99B6D25A5AD77D75B1FBF705655E1D6739E29AA78884146752A539A83A508C61294FF0078AA46A38C7ECBE5E6BA8DD3D4FD218FF6E0F82D2C8AABF17FE173331C003C5560493E9FEB6BD36CEF21D42D22B8B79639E09D0491C91B0649148C860470411C822BE58D4BFE08A1FB35DFE9D3C117C3E9ECA59A3644B887C41A9192024603A87B8652C3A8DCA4647208E2BC2BC49FB1BFED0BFF0004CBB4BFF10FC0FF001B3F8FBE1DE98925EDDF84F5DC34D6F0A0677DB1E42390AB92D0343231C011B577CB198DA1EF62292947BC1B6D7C9A57F91F1B4385B84B37FF0067C8F309D2AFF6638A846109F92A94E738C5FF008EC9B7BA3F4828AF33FD8E7F68393F6A9FD99FC25E3F9B4897429BC456AF24B64EC5BCA78E57898A920128C632CA48E5594D7A657AD4AAC6A415486CD5D7A33F35CC7015F038AAB82C52E5A94E528495D3B4A2DA6AEB4766B75A1F107FC16F7E0078A7C6BF097C1FF147C1735CAF883E0D6A136B1B61DBBA1B76F2A47BA5CF5685ED616C74DA6427EEE297C55FB497ED0FFB4C7C1FF86FF15FF6791E10D5346BBB1D9E20F0A5F8896E1B505764990CB3347FB942B81B6589C8C361838DBF6C6A3A75BEAFA7CF6977045736B751B433432A078E5461865653C10412083D735F9FBFF0422B7D57E176B1F1F7E13DEDCDADED9FC3CF154691DC4519533DC3B5CDACCFCF3B08B088A8ED96F5AF07178771C728C64E2AB2D6CECD4A0AE9FCD5D3F43F65E19CEE35F83EA55AD42955A995C94A11AB1E68CE8E264E3384969F0D4719C649A92E67CA58D4BF6CBFDB77E14B5BEADE2EFD9E3C35AD684932C77169E1B91EE7509739C6D105DDD328E39630B28EF8CD7B17C26FF82896AFAC7ECF3F107E207C4AF84BE2CF84B6BE0481675B6D64C83FB60B2B6C8E0696185B7990247CA6DDD327CC7903E9EAF837FE0B77E2CF12F8A7FE14F7C18D1B51B5D2F4AF8D1AF1D2F549E5B71291E55DD8083DC2ACB3876DB863E5A8CE0906F150AF81A52AEAB4A692D1351776F45AD93B5DA39B87F1794F1766587C9EA659430F29CAF2AB4A55A3CB4A9A752A5A0E738B93A71924DA6EED6DB92FFC115BE16EBDE217F8A3F1DBC47A6E9FA7BFC66D5DEFF4A8132F3DAC02EAE9E6C311B9627964555079616EAC46369AFBB6B99F82BF0D63F833F06FC25E0F86E9EFA2F0A68D67A3A5CBC6236B816F0244242A09DA5B6671938CF5AE9ABD2CBB0BF57C3C69755BFABD5FE2CF83E39E22FEDCCF3119945250934A095ECA9C128535AEBA4231BDF77776D428A28AED3E4CE1BF697F8D967FB38FC00F17F8E6F7C868FC35A5CD79145348634B99C2E2183700706494A463DDC57C63FF00047EFD9C35BF8ABE3CF117ED41F107ECD71AEF8F65B87F0F4092487FB3A279E64B86D92025170AB1C2039C45BB390C0D59FF0082F478B6EBC63E05F85DF07B416693C4BF117C5113430ADCAC68E91FEE238E504F0AF3DD44549F973031EABC7DD9E0EF08E9DE00F09697A168F6B1D8E93A2DA4561656C8495B78224091A0CE4E02A81CFA5786E2B1598B52F868A5A7F79F5F92FC59FAF43112E1FE078CA82B57CCE534E5D561E9349C56B75ED2A5EEFAC62E269514515EE1F9085145140057C13FF0490FF93D6FDB27FEC775FF00D2FD5EBEF6AF0EFD95FF00626B2FD97BE337C5FF00185AEBF73ABBFC5AD6975892D65B558869844973298C3063E665EEA4E485C00A304824F9D8BC3CE789A1522B48B95FE716BF33EE78733AC261321CDF035E56A98885250566EEE15E1392BA56568A6F5B5ED6DEC8F71AF827FE0ADFFF0027ADFB1B7FD8EEDFFA5FA457DED5F097FC15CFF622F8B7FB60FC5CF8552F814E991E89E1F79964BE3742DAE744B89648D9EE98960CF1ED862DA2205C3467FBC318E791A92C1C9538B93BC745E524FF0043D4F0831183A1C5146AE3EBC68D3E4AC9CE6EC97350A905F3BC95975D96B647DDB583F13BE27E81F067C07A9789FC51AA5B68BA0E911896EEF2727642A5828E80924B32A8001249000C9ADEAE1FF691FD9FF42FDA97E09EBDE02F12BDF47A2F88238D2792CA5115C44639526474665600AC91A1E54838C10457A759CD5393A7ACACED7DAFD2E7C06591C24B194A38F938D1728F3B8EB250BAE6714F46D2BDAFD4EABC33E24B0F19786F4FD634ABB86FF4BD56DA3BCB3B985B747710C8A1D2453DD594820FA1ABD5F9B1E1AF187ED09FF0485CF87F56F0E5E7C65F82B68D2BD86A160B235EE8D6E3901D82B7908A0E4A48A63CE4248BC8AFA0FF00672FF82C57C0DFDA252F11BC483C0B776403183C5B35BE99E7A9EF1C9E6B44D83DB786EFB715E6E1F38A326A957F72A7552D3EE7B35DACCFBDCF3C2FCD6842598650BEB983DE35697BFA37A29C57BF092FB4A51567D59E0DFB0EF84BFE1AD7FE0ADBF1D3E23F89C699AB5BFC2FBE6D134481833ADB38B9960B5B88864AFCB0D9CC49FF009E971BD403C8FD1DAF83BFE0971A47C20FD82FE18F8A34FD6FE377C15D63C45E23D65AE65D46C3C576B896CE340B6D13EF907CCA5A77200C03391B9B19AFB3FE1D7C5DF09FC60D3A6BCF0978A3C3BE29B4B6711CD3E91A9437D1C4D8CED668998038EC6B2C8D463875CED73C9B93574DDDBFD158EEF17255B119CCA58584DE0F0F0A546949C2518F2C2114ED75A294F99AEBA9D1514515ED9F93051451400514514005145140051451400578D7ED01FF0004F9F839FB51F8963D67C73E06D3F58D5E34119BD8AE2E2C6E255030048F6F246D2600C0DE4E074C57B2D15956A14EAC792AC549766AEBF13D0CB336C765D5FEB397D69D1A9B73424E32B3DD5E2D33E5BFF872DFECCFFF0044D7FF002E1D57FF00926B85F1D7FC1003E03F8BBC4325EE9F3F8EBC2F6CEAAA2C34CD5A392DE32060B03730CD2E4F539908F402BEDEA2B8A7936064ACE8C7E492FC8FADC2F8A7C63879FB4A79A576F6F7AACE4BEE936BE76B9F397EC59FF04C5F027EC27E30D5F5AF08EB9E36D467D66CC594F06AFA8432DB2AEF57DE238A18C17CA8019B2402C06371CFD1B4515D787C352A10F6746365D8F98CEF3ECC338C5CB1D99D5756ABB27296F64ACBEE41451456E792145145001451450014514500145145001451450014514500145145007FFFD9";

picture = open( hextoblob(hex), "jpg");

newwindow( "decoded", picture );

a 4K jpg.

[#5]

No reason to be sorry. I am amazed about how quick anwsers are given here

And again, thank you very much! So far we were able to do the follwing:

1. In the database, we converted the image from BLOB (varbinary(max)) to hex using the CONVERT function and setting the style to "2" - this leaves out the "0x" in the beginning of the string.

2. We transfered the hex-string to jmp via a query where it is saved in a data table.

3. We showed the picture with the function you mentioned: New Window("Foo", Open(HexToBlob(hex-string)), "jpg")

But now we actually do run into the 64k issue. Since we figured out, that it basically means, that we can only store 64k in a single cell in a data table, we had the idea to split the hex-string in the database across several columns and concatenate it back in jmp after importing the parts in several columns.

I will report on this soon.

Have a nice Weekend!

EDIT: It works! We were able to transfer the JPG by first splitting the binary image into parts, converting the parts in the database to hex, transfer the hex parts and finally concatenate them again in jmp. Really great! Thank you.

[#6]

Great, thanks for the follow-up.  If you want to post an SQL snippet here, that might help someone in the future.  (me anyway...I'm not an SQL expert.) Craige

[#7]

Sure! Below you will find the code which did the job. We build a little SQL example, but i´m not an SQL expert either, so watch out

This code is capable of transfering JPGs with a maximum size of 256 kB. If you want to transfer larger pictures, just increase the number of columns, i. e. add ImgVarChar8, ImgVarChar9 etc.

Maybe you can help me with my follow up question here?

Cheers!

-------------- SQL Code Start --------------

CREATE TABLE tIMG(

ID INT identity primary Key,

Img VARBINARY(MAX)

)

GO

INSERT INTO tIMG(Img) SELECT BulkColumn FROM

OPENROWSET(BULK 'C:\Test\test.jpg', SINGLE_BLOB) AS InsertIMG

GO

CREATE VIEW vImg AS

SELECT convert(varchar(max),substring(Img,0,32000),2) ImgVarChar0         

      ,convert(varchar(max),substring(Img,32000,32000),2) ImgVarChar1

      ,convert(varchar(max),substring(Img,64000,32000),2) ImgVarChar2

      ,convert(varchar(max),substring(Img,96000,32000),2) ImgVarChar3

      ,convert(varchar(max),substring(Img,128000,32000),2) ImgVarChar4

      ,convert(varchar(max),substring(Img,160000,32000),2) ImgVarChar5

      ,convert(varchar(max),substring(Img,192000,32000),2) ImgVarChar6

      ,convert(varchar(max),substring(Img,224000,32000),2) ImgVarChar7

FROM [dbo].[tIMG]

GO

-------------- SQL Code End --------------

-------------- JMP Code Start --------------

// SQL Query to get the hex data (strings) from the database.

// The strings are stored in different columns in the data table "JPG_Query".

New SQL Query(

    Connection(

        "Your connection"

    ),

    QueryName( "JPG_Query" ),

    Select( Column( "ImgVarChar0", "t1" ), Column( "ImgVarChar1", "t1" ) ,

        Column( "ImgVarChar2", "t1" ) ,Column( "ImgVarChar3", "t1" ),

        Column( "ImgVarChar4", "t1" ) ,Column( "ImgVarChar5", "t1" ),

        Column( "ImgVarChar6", "t1" ) ,Column( "ImgVarChar7", "t1" ) ),

    From( Table( "vImg", Schema( "dbo" ), Alias( "t1" ) ) ),

) << Run;

// Loop to build the JPG image from the hex-strings and show the picture (maybe there is some redundant code...)

switch = 0;

count_try = 0;

max_try = 200;

hexcode = "";

While(switch == 0 & count_try < max_try,

    count_try++;

    Try(

        hexcode1 = DataTable("JPG_Query"):Name("ImgVarChar0")[1];

        hexcode2 = DataTable("JPG_Query"):Name("ImgVarChar1")[1];

        hexcode3 = DataTable("JPG_Query"):Name("ImgVarChar2")[1];

        hexcode4 = DataTable("JPG_Query"):Name("ImgVarChar3")[1];

        hexcode5 = DataTable("JPG_Query"):Name("ImgVarChar4")[1];

        hexcode6 = DataTable("JPG_Query"):Name("ImgVarChar5")[1];

        hexcode7 = DataTable("JPG_Query"):Name("ImgVarChar6")[1];

        hexcode8 = DataTable("JPG_Query"):Name("ImgVarChar7")[1];

        hexcode = hexcode1||hexcode2||hexcode3||hexcode4||hexcode5||hexcode6||hexcode7||hexcode8;

        picture = Open(Hex to Blob(hexcode), "jpg");

        If ( Is Missing(hexcode) == 0  &  Is Scriptable( Data Table("JPG_Query") ) == 1 ,

            New Window( "JPG Image", picture );

            hexcode = "";

            switch = 1;

            Close(Data Table("JPG_Query") , NoSave);

        );

        ,

        Wait(0.01);

        If (count_try == max_try ,

            Throw("Error!");

        );

    ); // Close Try

); // Close While

-------------- JMP Code End --------------