0 Replies Latest reply: Nov 7, 2014 7:14 AM by Pete Hutchins RSS

    Using images from Oracle BLOB fields

      Hi. I've been racking my brains over this one for about 4 days and I have finally cracked it.

       

      Firstly, I needed to understand the process from soup to nuts. Loading the .png files into the the Oracle database in the first place and then having them display correctly in Qlikview. I had previously been using the Excel solution, however as this is going to moved to production on a server to which I will not have access to the directory structure, I needed to have them stored along with the data.

       

      I found various solutions on the net, none of which were complete, and none of which worked straight out of the box, therefore I hope to share what I have done and that it will be less painful for you to do the same.

       

      1. Loading the images into Oracle.

       

      You need to have a table with at least 2 columns. This is what I set up, (yes, I spelt image wrong!).

        CREATE TABLE "SYSTEM"."QLIKVIEWIMAGES" (

           "IMAGETYPE" VARCHAR2(20 BYTE),

           "IAMGE" BLOB);

       

      This next part had me going round in circles. You have to create a directory variable in Oracle in order to access the images. This is not made clear in any of the examples I have found. I used SQL*PLUS for most of this job.

           Create or Replace Directory 'DFM_IMAGES' as 'C:\users\xxxxxx\Documents\SQL Developer\sqldeveloper\Images';

      You need to replace the 'C:\users ... etc' with your own path where you images are held.

       

      Next is to create a procedure that actually picks up the image and loads it into the table. Again I used SQL*PLUS.

      create or replace procedure Pete1

        is

        temp_clob blob;

        temp_os_file bfile;

        ex number;

        begin --1st begin

       

           DBMS_LOB.CREATETEMPORARY(temp_clob,true);

       

           temp_os_file := BFILENAME ('DFM_IMAGES','Data Warehouse.png');

           ex := dbms_lob.fileexists(temp_os_file);

           if ex = 1 then

                dbms_output.put_line('File Exists !');

                dbms_lob.fileopen(temp_os_file, dbms_lob.file_readonly);

                dbms_lob.loadfromfile(temp_clob,temp_os_file, dbms_lob.getlength(temp_os_file));

                dbms_output.put_line('Length '||dbms_lob.getlength(temp_os_file));

                insert into QLIKVIEWIMAGES values ('Data Warehouse',empty_blob());

                update QLIKVIEWIMAGES set iamge = temp_clob where IMAGETYPE = 'Data Warehouse';

                commit;

                dbms_lob.fileclose(temp_os_file);

           else

                dbms_output.put_line('No file.');

           end if;

      end;

       

      The syntax for the 'BFILENAME' function uses the directory variable that I discussed first off and then the name of the image file that you want to load from the directory contained in the directory variable.

       

      Please note that I have been lazy by not renaming the BLOB variables, they are BLOBs not CLOBs.

       

      Apart from the 'dbms_output.put_line' statements, all the others are required, including the strange combination of the 'insert' followed immediately by the 'update'. Apparently the BLOB must be initialised before it can be used, another one that had me running round in circles!

       

      The procedure can be run from the SQL*PLUS command line by typing 'execute pete1;'.

       

      2. Loading the images into Qlikview.

      Firstly you must add an ODBC connection to your 'Edit Script'. This must first be added to the list of data sources available on your machine, (I won't go into depth about how to do this as there is plenty of documentation available on the net). The connection line is created when you follow the steps after pressing the 'Select' button on the 'Data' tab. The instructions for this are contained in section 23.5 from the 'qlikview reference manual v11'.

       

      Section 23.7 is key to accessing the images from the database with the details about BLOBs right at the end of the section. I tried at first to just enter the line 'INFO SELECT IMAGETYPE, IAMGE FROM SYSTEM.QLIKVIEWIMAGES;' directly into the 'Edit Script'' however this didn't work. I followed the instructions in the manual and although there didn't seem to be any apparent difference between what I wrote and what was put in by Qlikview, it appears that something happens behind the scenes to bundle the images and they show up when you add the object and select the image from the bundles available.

       

      My 'Edit Script' ended up with these entries;

      ODBC CONNECT TO [PetesDFM;DBQ=xe] (XUserId is eLNcfZFPTDdKWWZK, XPassword is eSfTSSRNMLaeGaROCE);

      PeteDFM:

      //-------- Start Multiple Select Statements ------

      SQL SELECT IAMGE,

         IMAGETYPE

      FROM SYSTEM.QLIKVIEWIMAGES;

      //-------- End Multiple Select Statements ------

      //info select imagetype, iamge from system.qlikviewimages;//-------- Start Multiple Select Statements ------

      INFO SELECT IMAGETYPE, IAMGE FROM SYSTEM.QLIKVIEWIMAGES;

      //-------- End Multiple Select Statements ------

       

      I think that about covers everything, however if this doesn't work for you please feel free to ask me questions and I'll try my best to answer them.

       

      Good Luck !

      Pete.