topic Re: Reading BLOBS from Oracle into QV11 in QlikView

Reading BLOBS from Oracle into QV11

Wed, 09 Sep 2015 15:48:33 GMT

The proof of concept I am attempting to create has 2 parts

A QV11 app loads a BLOB from Oracle which is a pdf file
The pdf will then be able to be downloaded from the QV app.

Are either of these possible with standard QV functionality? This qvw will be accessible off of Access Point and the users do have the ability to use the IE Plugin.

-Jake

Re: Reading BLOBS from Oracle into QV11

MarcoWedel — Wed, 09 Sep 2015 23:05:02 GMT

Hi,

maybe using some macro to convert (depending on the format) and store the blob data as pdf file to disk could be a solution.

One example could be:

load script

tabPDF:
LOAD *,
    RecNo() as PDFid,
    len(PDFdata) as LenPDFdata,
    OctetStringToFile(PDFdata, PDFfile) as ResultOctetStringToFile;
LOAD @1 as PDFdata,
    'C:\temp\PDF-File'&RecNo()&'.pdf' as PDFfile
FROM  (txt, codepage is 1252, no labels, delimiter is '\t', msq);

VBS module

Function OctetStringToFile(OctetString, FilePath)
  Const adTypeText = 2
  Const adSaveCreateOverWrite = 2
  LenData = Len(OctetString)\2 + Len(OctetString) Mod 2
  set fso = createobject("scripting.filesystemobject")
  set stream = createobject("adodb.stream")
  stream.type = adTypeText
  stream.charset = "windows-1252"
  stream.open
  For i = 0 to LenData-1
  stream.writetext chr(CByte("&h" & Mid(OctetString, i*2+1, 2)))
  Next
  stream.savetofile FilePath, adSaveCreateOverWrite
  stream.close
  OctetStringToFile = "processed"
End Function

hope this helps

regards

Marco

Re: Reading BLOBS from Oracle into QV11

MarcoWedel — Thu, 10 Sep 2015 06:57:01 GMT

You might have to adjust your macro security settings in the edit module window to be able to execute the vbs function.

Regards

Marco

Re: Reading BLOBS from Oracle into QV11

Thu, 10 Sep 2015 13:30:16 GMT

Marco -

Thanks for your reply. How confident are you that this solution would work on Access Point (while using IE Plugin)?

-Jake

Re: Reading BLOBS from Oracle into QV11

MarcoWedel — Thu, 10 Sep 2015 21:22:56 GMT

Hi,

I don't see why the application should not work when published to server.

The table/link part is pretty straight forward while the tricky part is done during the script execution.

regards

Marco

Re: Reading BLOBS from Oracle into QV11

Anonymous — Mon, 21 Sep 2015 12:38:14 GMT

Hello Marco

I'm just experiencing a limit of 16k on the "long varbinary" field. So for small files your solution works but for "long varbinary" > 16k I'm loosing data.

Best Regards

Valerio

Re: Reading BLOBS from Oracle into QV11

Thu, 24 Sep 2015 16:10:42 GMT

So I tried the solution and it doesn't work on Access Point with IE Plugin. The reason why it doesnt work is because the path is relative to the computer you are using. The file opened fine when using the brower on the server that was hosting Access Point, however when I tried the link from my own pc it said "failed to open".