Skip to main content
Announcements
New: No-code data prep in Qlik Cloud Analytics™ TAKE A TOUR
cancel
Showing results for 
Search instead for 
Did you mean: 
ElsKnockaert
Contributor III
Contributor III

Get Images from SQL Anywhere DB through ODBC / OLE DB

Good morning

I've tried to follow the steps of https://community.qlik.com/t5/Member-Articles/Loading-Image-files-stored-in-database-into-a-Qlik-Sen... to show the images I have in our DB in Qlik. But no luck. I'll include my steps and print screens to show you what I've done and hopefully someone can notice where I went wrong:

Data Connection works :

ElsKnockaert_1-1736929671746.png

 

 

 

Load Script results in no errors :

ElsKnockaert_0-1736929560476.png

MultiMedia_Content is a long binary in our database.

MultiMedia_Content_Type is  varchar(129) in our database and is either image/jpeg or image/bmp or image/png.

 

Result on sheet including possible filters:

ElsKnockaert_2-1736929877605.png

 

MultiMedia_Content only shows 8 results while there are 55 pictures in our DB.

ElsKnockaert_4-1736930096467.png

ElsKnockaert_7-1736930202178.png

ElsKnockaert_8-1736930222632.png

 

 

ElsKnockaert_6-1736930167126.png

 

 

Labels (1)
2 Solutions

Accepted Solutions
JandreKillianRIC
Partner - Creator III
Partner - Creator III

Hi @ElsKnockaert 

Seems like that value you are getting is the "image signature" and not the base64 format. Try doing this 

TO_BASE64STRING(blob_column) for the image. 


Regards - Jandre

Mark the solution as accepted that solved your problem and if you found it useful, press the like button! Check out my YouTube Channel QeepItSimpleQupid

View solution in original post

ElsKnockaert
Contributor III
Contributor III
Author

@JandreKillianRIC 

Brilliant! I used    base64_encode( ) in my SQL statement and => VICTORY! 

Thank you so much!

View solution in original post

11 Replies
JandreKillianRIC
Partner - Creator III
Partner - Creator III

Hi @ElsKnockaert 

 

Can you add a screenshot of the MultiMedia_Image field.

Based on the script above I suspect a result like this, 

data:image/jpg;base64,R0lGODlh0ACWAPcAA.... etc
 
But the MultiMedia_Content also doesn't look correct. Is looks "funny" 
JandreKillianRIC_1-1736931140182.png

 

You will need the base64 information, Perhaps try a Text() over that field and make sure this contains the base64 information. 

 
Regards - Jandre

 

Mark the solution as accepted that solved your problem and if you found it useful, press the like button! Check out my YouTube Channel QeepItSimpleQupid
ElsKnockaert
Contributor III
Contributor III
Author

I have now tried to use Text() in my load script as such, but MultiMedia_Content is still off.
In the database the value is in fact for example: 0x3ed1fece355 ... so I don't get why it's these weird characters.

ElsKnockaert_0-1736931445963.png

ElsKnockaert_1-1736931537752.png

 

When I use ODBC to connect to the database however, I get something that seems correct but for my 55 images in the DB, it seems like only 2 different MultiMedia_Content values were uploaded

ElsKnockaert_3-1736932065244.png

 

ElsKnockaert_2-1736931982160.png

 

JandreKillianRIC
Partner - Creator III
Partner - Creator III

Hi @ElsKnockaert 

 

This one looks correct, is this via ODBC and not OLE DB? 

If you use this one in a Map object does your image show? 

JandreKillianRIC_0-1736932341019.png

Regards - Jandre

Mark the solution as accepted that solved your problem and if you found it useful, press the like button! Check out my YouTube Channel QeepItSimpleQupid
JandreKillianRIC
Partner - Creator III
Partner - Creator III

Hi @ElsKnockaert 

For the OLE DB connection you can try doing something like this on the image_content field 

SELECT CAST(image_field AS VARBINARY(MAX)) AS image_data

Regards - Jandre 

Mark the solution as accepted that solved your problem and if you found it useful, press the like button! Check out my YouTube Channel QeepItSimpleQupid
ElsKnockaert
Contributor III
Contributor III
Author

This is using ODBC, but still nada in my Map object

JandreKillianRIC
Partner - Creator III
Partner - Creator III

Hi @ElsKnockaert 

 

I think this might get cut-off somewhere. Try the Cast I mentioned earlier and lets test that? 

 

Regards - Jandre

Mark the solution as accepted that solved your problem and if you found it useful, press the like button! Check out my YouTube Channel QeepItSimpleQupid
ElsKnockaert
Contributor III
Contributor III
Author

@JandreKillianRIC 

With OLE DB connector, and changes to my select statement in the DB:

ElsKnockaert_0-1736933350964.png

Again the weird characters appear:

ElsKnockaert_1-1736933382713.png

 

JandreKillianRIC
Partner - Creator III
Partner - Creator III

Hi @ElsKnockaert 

That's very strange. Are you forced to wotk with OLE DB connection now or can the ODBC work? 

Also - With the ODBC where the MultiMedia_image "seems" correct, Are you sure that is base64 format, Something still doesn't look correct in the string. I would expect lower and upper case values. Here is a emoji in base64. 

data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAABgAAAAYCAYAAADgdz34AAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAAApgAAAKYB3X3/OAAAABl0RVh0U29mdHdhcmUAd3d3Lmlua3NjYXBlLm9yZ5vuPBoAAANCSURBVEiJtZZPbBtFFMZ/M7ubXdtdb1xSFyeilBapySVU8h8OoFaooFSqiihIVIpQBKci6KEg9Q6H9kovIHoCIVQJJCKE1ENFjnAgcaSGC6rEnxBwA04Tx43t2FnvDAfjkNibxgHxnWb2e/u992bee7tCa00YFsffekFY+nUzFtjW0LrvjRXrCDIAaPLlW0nHL0SsZtVoaF98mLrx3pdhOqLtYPHChahZcYYO7KvPFxvRl5XPp1sN3adWiD1ZAqD6XYK1b/dvE5IWryTt2udLFedwc1+9kLp+vbbpoDh+6TklxBeAi9TL0taeWpdmZzQDry0AcO+jQ12RyohqqoYoo8RDwJrU+qXkjWtfi8Xxt58BdQuwQs9qC/afLwCw8tnQbqYAPsgxE1S6F3EAIXux2oQFKm0ihMsOF71dHYx+f3NND68ghCu1YIoePPQN1pGRABkJ6Bus96CutRZMydTl+TvuiRW1m3n0eDl0vRPcEysqdXn+jsQPsrHMquGeXEaY4Yk4wxWcY5V/9scqOMOVUFthatyTy8QyqwZ+kDURKoMWxNKr2EeqVKcTNOajqKoBgOE28U4tdQl5p5bwCw7BWquaZSzAPlwjlithJtp3pTImSqQRrb2Z8PHGigD4RZuNX6JYj6wj7O4TFLbCO/Mn/m8R+h6rYSUb3ekokRY6f/YukArN979jcW+V/S8g0eT/N3VN3kTqWbQ428m9/8k0P/1aIhF36PccEl6EhOcAUCrXKZXXWS3XKd2vc/TRBG9O5ELC17MmWubD2nKhUKZa26Ba2+D3P+4/MNCFwg59oWVeYhkzgN/JDR8deKBoD7Y+ljEjGZ0sosXVTvbc6RHirr2reNy1OXd6pJsQ+gqjk8VWFYmHrwBzW/n+uMPFiRwHB2I7ih8ciHFxIkd/3Omk5tCDV1t+2nNu5sxxpDFNx+huNhVT3/zMDz8usXC3ddaHBj1GHj/As08fwTS7Kt1HBTmyN29vdwAw+/wbwLVOJ3uAD1wi/dUH7Qei66PfyuRj4Ik9is+hglfbkbfR3cnZm7chlUWLdwmprtCohX4HUtlOcQjLYCu+fzGJH2QRKvP3UNz8bWk1qMxjGTOMThZ3kvgLI5AzFfo379UAAAAASUVORK5CYII=

You can try this in a variable with a map just to see if the map (image) is rendering correctly, after this I would focus on the ODBC to get that string in a Base64 format. If possible you can share a value (like I did above) and I can try and see what this actually is? 

Regards - Jandre

Mark the solution as accepted that solved your problem and if you found it useful, press the like button! Check out my YouTube Channel QeepItSimpleQupid
JandreKillianRIC
Partner - Creator III
Partner - Creator III

Hi @ElsKnockaert 

Seems like that value you are getting is the "image signature" and not the base64 format. Try doing this 

TO_BASE64STRING(blob_column) for the image. 


Regards - Jandre

Mark the solution as accepted that solved your problem and if you found it useful, press the like button! Check out my YouTube Channel QeepItSimpleQupid