topic Re: Loading image from a database in QlikView

Loading image from a database

Wed, 30 Oct 2013 09:56:43 GMT

Hello

I have some pictures in my SQL Server database, in the column FileContent.

I've tried to link and/or load these pictures into QC 11 using info, bundle,... but I can't see anything.

What do I do wrong?

Here is my attempts:

(reference table):

SQL SELECT e.[IndividualId] as [ID Employé]

      ,e.[EmployeeNumber] as [Numéro Employé]

      ,i.LastName as [Nom Employé]

      ,i.FirstName as [Prénom Employé]

      ,i.Sex as [Sexe]

      ,i.BirthDateUTC as [Date naissance Employé]

      ,e.[SeniorityDateUTC] as [Date ancienneté Employé]

      ,i.[Photo_FileId] as [Photo_Id]

FROM [CLIBDL].[Individual].[Employees] e

INNER JOIN [CLIBDL].[Individual].[Individuals] i on e.IndividualId = i.IndividualId where e.IndividualId not in (1,2);

bundle
SQL SELECT [FileId] as [Photo_Id]

,[FileName] as [Photo_File_Name]

,[FileContent] as [Photo]

FROM [CLIBDL].[Exploitation].[Files]

where VirtualPath = 'TalentSoft/Photos';

Bundle Info

Image_Size(256,256)

SQL SELECT [FileId] as [Photo_Id]

/* ,[FileName] as [Photo_File_Name] */

,[FileContent] as [Photo]

FROM [CLIBDL].[Exploitation].[Files]

where VirtualPath = 'TalentSoft/Photos';

3)
Info
SQL SELECT [FileId] as [Photo_Id]

,[FileContent] as [Photo]

FROM [CLIBDL].[Exploitation].[Files]

where VirtualPath = 'TalentSoft/Photos';

Re: Loading image from a database

Gysbert_Wassenaar — Wed, 30 Oct 2013 16:53:51 GMT

If you retrieve images from a database then you need to use INFO. And you must load only two fields. The first contains the image key (or name), the other field contains the binary data of the image. From the help file:

Info

If a piece of external information, such as a text file, a picture or a video is to be linked to a field value, this is done in a table that is loaded using an info prefix. (In some cases it will be preferable to store the information inside the .qvw file, by using the Bundle prefix.) The table must contain two columns only, the first one with the field values that will form the keys to the information, the second one containing the information elements, e.g. the file names of the pictures etcetera.

The same applies to, for example, a picture from a database management system. On a binary field, a blob, the info select statement makes an implicit bundle, i.e. the binary data will be fetched immediately and stored in the qvw. The binary data must be the second field in a select statement.

The syntax is:

info ( loadstatement | selectstatement )

Examples:

Info LOAD * FROM flagoecd.csv;

Info SQL SELECT * FROM infotable;

Info SQL SELECT Key, Picture FROM infotable;

Re: Loading image from a database

Thu, 31 Oct 2013 13:00:05 GMT

Hi!

It's what I've tried, with SELECT as it is in a database (see my 3rd example) => do you have any idea what I did wrong? I have the info, 2 fields only and the second one is my binary field...

Re: Loading image from a database

Gysbert_Wassenaar — Thu, 31 Oct 2013 13:40:28 GMT

No idea. You say ... but I can't see anything. Where are you looking? What do you expect to see? If the images are loaded you should be able to find them in the expression editor on the Images tab after choosing the table name as Image Folder.

Re: Loading image from a database

Fri, 08 Nov 2013 13:25:24 GMT

I think my images aren't loaded. I've tried to use them in a expression, with example found on this forum, but I only have the standard blue background color, and no image 😞