Get Images from SQL Anywhere DB through ODBC / OLE DB

[ElsKnockaert]

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 :

Load Script results in no errors :

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:

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

[JandreKillianRIC]

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" 

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

Regards - Jandre

[ElsKnockaert]

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.

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

[JandreKillianRIC]

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? 

Regards - Jandre

[JandreKillianRIC]

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 

[ElsKnockaert]

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

[JandreKillianRIC]

Hi @ElsKnockaert 

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

Regards - Jandre

[ElsKnockaert]

@JandreKillianRIC 

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

Again the weird characters appear:

[JandreKillianRIC]

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

[JandreKillianRIC · Accepted solution]

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