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?