topic Re: Get Images from SQL Anywhere DB through ODBC / OLE DB in App Development

Get Images from SQL Anywhere DB through ODBC / OLE DB

ElsKnockaert — Wed, 15 Jan 2025 08:38:42 GMT

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-Sense-App/ta-p/1622236 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.

Re: Get Images from SQL Anywhere DB through ODBC / OLE DB

JandreKillianRIC — Wed, 15 Jan 2025 08:53:07 GMT

Hi @ElsKnockaert

Can you add a screenshot of the MultiMedia_Image field.

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

data&colon;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

Re: Get Images from SQL Anywhere DB through ODBC / OLE DB

ElsKnockaert — Wed, 15 Jan 2025 09:07:55 GMT

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

Re: Get Images from SQL Anywhere DB through ODBC / OLE DB

JandreKillianRIC — Wed, 15 Jan 2025 09:12:56 GMT

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

Re: Get Images from SQL Anywhere DB through ODBC / OLE DB

JandreKillianRIC — Wed, 15 Jan 2025 09:20:23 GMT

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

Re: Get Images from SQL Anywhere DB through ODBC / OLE DB

ElsKnockaert — Wed, 15 Jan 2025 09:21:20 GMT

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

Re: Get Images from SQL Anywhere DB through ODBC / OLE DB

JandreKillianRIC — Wed, 15 Jan 2025 09:25:56 GMT

Hi @ElsKnockaert

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

Regards - Jandre

Re: Get Images from SQL Anywhere DB through ODBC / OLE DB

ElsKnockaert — Wed, 15 Jan 2025 10:43:43 GMT

@JandreKillianRIC

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

Again the weird characters appear:

Re: Get Images from SQL Anywhere DB through ODBC / OLE DB

JandreKillianRIC — Wed, 15 Jan 2025 10:50:09 GMT

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&colon;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

Re: Get Images from SQL Anywhere DB through ODBC / OLE DB

JandreKillianRIC — Wed, 15 Jan 2025 11:02:01 GMT

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

Re: Get Images from SQL Anywhere DB through ODBC / OLE DB

ElsKnockaert — Wed, 15 Jan 2025 11:12:49 GMT

@JandreKillianRIC

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

Thank you so much!

Re: Get Images from SQL Anywhere DB through ODBC / OLE DB

JandreKillianRIC — Wed, 15 Jan 2025 11:13:55 GMT

Amazing news!!! I dont have access to a SQL Anywhere site now to test so I was reading and reading and just thought - Lets just go with something like a convert! glad it helped!

Have a great day and happy building!