How to store images into SQL database and load them?

Anonymous — Tue, 24 Sep 2013 05:47:49 GMT

I know how to load images by paths of them into QV, but how can I store images into this table and load them into QV?

I created a table with an image type field in the SQL database, but I don't know how to use this field.

CREATE TABLE [dbo].[ImageLibrary](

[IMG_Name] [nvarchar](50)

,[IMG] [image] NULL

)

Re: How to store images into SQL database and load them?

Anonymous — Thu, 16 Feb 2017 15:24:22 GMT

Hi John,

I'm working on this same issue myself. I have however cracked the first step to storing the images in to a database by creating an App.

If you have Visual Studio you can follow the instructions via this youtube video: Insert Image to SQL Server and Retrieve Image from SQL Server - YouTube

Another method if you have direct access to the host server machine is to use OpenRowSet Bulk import;

insert into tableName (ImageColumn) SELECT BulkColumn FROM Openrowset( Bulk 'image..Path..here', Single_Blob) as img

Note, however that the image location must be on the same server host machine. A network location will not work unless mapped from the host server. (hence you need permission on actual SQL Server host machine.

Ok I solved the import to Qlikview bit with the help of another post: GET A BLOB INTO YOUR QV SUCCESFULLY!

This is the script I used;

ODBC CONNECT TO [YourDatabaseName];

Images:

LOAD ImageId,

SiteReference as SiteRef_2,

ImageName,

ImageType,

ImageFile;

SQL SELECT *

FROM [YourDatabaseTableName];

INFO

SELECT ImageId, ImageFile

FROM [YourDatabaseTableName];

The first section of script is a normal load of the whole image holding table so I can bring in other data fields. Also SiteRef_2 is used to link to another table elsewhere in qlikview.

The second section of the actual image load. Use Info and just 2 fields with the second field being the BLOB, the 1st field I'll refer to as the Image reference field below.

Next is how to use and reference the image file (which stumped me for a bit) In any Edit Expression screen select the Images Tab at the bottom and then change the Image Folder, where you should now find the Image reference field (in my case ImageId). You can now paste in any of your database images.

The actual expression to refer to an image is 'qmem://[ImageReferenceField Name]/[ImageReferenceField relating to image to show]'

So as an example if I wanted to show ImageFile with ImageId 1 the expression would be 'qmem://ImageId/1' this can be made dynamic by using ='qmem://ImageId/'&[ImageId] in a Text Object and then selecting any of the linked fields such as Image name or type from a List box.

topic How to store images into SQL database and load them? in QlikView

How to store images into SQL database and load them?

Re: How to store images into SQL database and load them?