Sidebar

Query From DB and Write to File

0 votes
710 views
asked Apr 11, 2019 by michael-s-4691 (220 points)

I'm trying to query a blob from a SQL table and write it to a file using byte[]; however, it is becoming corrupt somewhere along the way.  I have confirmed via SSMS that the blob is not corrupted within the SQL table.  Using the current code now:

var queryResult = qie.doQuery(
   "DB Connection",
   "select attachment_blob\n" +
   "from DB.dbo.BlobTest d\n" +
   "where ID = 1");

var str = queryResult.getNode('attachment_blob');

// write bytes out to a file
var out = new java.io.FileOutputStream('\\\\server\\folder\\testy.pdf', false);
var bytes = str.getBytes();
out.write(bytes);
out.close();

When comparing the 2 versions (working from SSMS and not working via QIE), there are a few beginning characters missing in the file from the QIE version.

1 Answer

+1 vote
 
Best answer

Here is an explanation with a solution at the end.

I created the sample database below and tried to reproduce this issue using the code provided.  If the PDF only contains text, it works.  However, if the PDF contains image data then it fails to output a valid PDF.   These are the sample PDF files i used:

sample pdf with just text: http://www.africau.edu/images/default/sample.pdf

sample pdf with images and text: http://www.africau.edu/images/default/sample.pdf

 

Sample database script:

USE [BlobTest]
GO

/****** Object:  Table [dbo].[BlobTest]    Script Date: 4/11/2019 2:44:27 PM ******/
DROP TABLE [dbo].[BlobTest]
GO

/****** Object:  Table [dbo].[BlobTest]    Script Date: 4/11/2019 2:44:27 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[BlobTest](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [blob_as_var_binary_max] [varbinary](max) NULL,
    [blob_as_image] [image] NULL,
 CONSTRAINT [PK_BlobTable] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

 

I loaded the pdfs this way:

// //Create a handle to the ArrayUtils that we use
// //later to convert primitive byte[] to java.lang.Byte[]
var ArrayUtils = org.apache.commons.lang.ArrayUtils;

//Insert the first record
var insertStatement = "" +
   "INSERT INTO BlobTest (" +
   " blob_as_var_binary_max, " +
   " blob_as_image " +
   ") VALUES (" +
   " :blobasvarbinarymax, " +
   " :blobasimage " +
   ")";

   var pQuery = qie.getParameterizedQuery(insertStatement);
   //Read in the file. The qie.readfile returns a byte[]
   var firstFileBytes = qie.readFile(new java.io.File("/qie/In/c4611_sample_explain.pdf"));
   //The setBytes() method take a java.lang.Byte[]
   //instead of a primitive byte[] for consistency in null handling
   pQuery.setBytes('blobasvarbinarymax', ArrayUtils.toObject(firstFileBytes));
   //For large files an InputStream can be used to
   //avoid loading the entire file into memory
   pQuery.setBinaryStream('blobasimage', new java.io.ByteArrayInputStream(firstFileBytes));

   var statementCount = pQuery.doUpdateQuery('DB Connection');
   qie.debug("BINARY - insert count: " + statementCount);

Then I wrote them out using something like your code (just changed fieldname and file path/name):

var queryResult = qie.doQuery(
   "DB Connection",
   "select blob_as_var_binary_max\n" +
   // "select blob_as_image\n" +
   "from BlobTest d\n" +
   "where ID = 1");

var str = queryResult.getNode('blob_as_var_binary_max');
// var str = queryResult.getNode('blob_as_image');

// write bytes out to a file
var out = new java.io.FileOutputStream('/qie/Out/SampleMax.pdf', false);
// var out = new java.io.FileOutputStream('/qie/Out/SampleImage.pdf', false);
var bytes = str.getBytes();
out.write(bytes);
out.close();

When I used the pdf with just text I was able to open both the source (sample.pdf) and the two output files (SampleMax.pdf, SampleImage.pdf) successfully from the file system.  However, this fails when the pdf contains image or binary data.

Solution: Use the doConditionQuery after getting the id of the record to return to output PDF files with images or binary data.

var queryResult = qie.doConditionQuery(
   "DB Connection",
   "select blob_as_var_binary_max\n" +
   "from BlobTest d\n" +
   "where ID = 1");

// write bytes out to a file
var out = new java.io.FileOutputStream('/qie/Out/Condition_SampleMax.pdf', false);
out.write(queryResult);
out.close();

If this doesn't work for you please provide more details: What is the MSSql data type for the field 'attachment_blob'?  What version of QIE are you using?  What version of MSSql server?  How was the pdf loaded into the database?  Sample pdf?

answered Apr 11, 2019 by rich-c-2789 (16,180 points)
selected Apr 11, 2019 by michael-s-4691
commented Apr 11, 2019 by michael-s-4691 (220 points)
This worked!  Thanks for your help.
...