Open PDF from external SQL database. Citrix, BLOB and ADO

Sl1m4er

Hi folks,

Problem:
Not possible to open external web site URL (a link to an invoice registered outside) from NAV launched in the Citrix environment. More details in the previous topic: http://mibuso.com/forum/viewtopic.php?f=23&t=60040
As a result information on the page (PDF and some other data) is not accessible from NAV.

Customer is running NAV 5 SP1

Expected behavior:
It was agreed that all the other data is not that relevant for NAV user, but it is really important to open the PDF file.

Suggested solution:
With this, I was thinking about the following solution as we are already using ADO to get the data from web application:

- Web application would store PDFs for corresponding invoices as BLOB (image) fields in SQL db
- When user triggers PDF lookup from NAV, NAV-interface would download the PDF from SQL db using one of the methods described here using ADO: viewtopic.php?p=122794#p122794
- Once the file is retreived from SQL it will be opened using one of the methods described here: viewtopic.php?t=12861 (Will it be necessary to store the downloaded PDF file as BLOB in NAV or it would be possible to open the file just using the path retreived during download?)

Please let me know what do you think about the suggested approach to solving this problem?

Thank you.

Sl1m4er

Any ideas?

Sl1m4er

I'd like to share a solution that worked for me:

1. Upload a PDF into SQL db for testing (prior to our web-application being able to do it):

Insert YourTableName (Column1Name, Column2Name,..., PDF) Select 'Column1Value', 'Column2Value', ..., BulkColumn from Openrowset(Bulk 'C:\PDFFilePath\test.pdf', Single_Blob) as PDFImage

PDF field type is 'image'

2. Import PDF file from SQL into NAV as BLOB:

IF SetupTable."Store PDFs Locally" THEN BEGIN
  IF FORMAT(ADORecordSet.Fields.Item('Filename').ActualSize) <> FORMAT(0) THEN
    PDFFileName := ADORecordSet.Fields.Item('Filename').Value;
  
  IF FORMAT(ADORecordSet.Fields.Item('PDF').ActualSize) <> FORMAT(0) THEN BEGIN
    ADOStream.Type := 1; //1 = Binary
    ADOStream.Open;
    ADOStream.Write(ADORecordSet.Fields.Item('PDF').Value);
    ADOStream.SaveToFile('c:\LocalPDFs\' + PDFFileName,2); //2 = SaveCreateOverWrite
    TableThatContainsPDFBlobField.PDF.IMPORT('c:\LocalPDFs\' + PDFFileName);
    ADOStream.Close;
    CLEAR(ADOStream);
  END;
END;

3. Open PDF stored in NAV as BLOB:
'PDF' is a BLOB field in a table

CALCFIELDS(PDF);
IF PDF.HASVALUE THEN BEGIN
  PDFExportFileName := 'c:\LocalPDFs\' + 'someFileName' + '.pdf';
  PDF.EXPORT(PDFExportFileName,FALSE);
  HYPERLINK(PDFExportFileName);
END;

The question is - is it possible to delete a created file during step 2 and 3 from above?
I noticed, there is a function File.ERASE in NAV 2009 and later, but the solution will be installed into 5.00 SP1 where it is not available yet.

Sl1m4er

Any chance?

ftornero

The function to ERASE a file is available in NAV 5.01.