using blob or clob from db as document

[Former Community Member]

I'm changing a working process to fetch an XDP document from a database rather than fetch from resources:// on the Adobe server. The DB2 database field containing the XDP is a clob data type. We were using blob. The services operations are:

- Foundation/JdbcService/Query Single Row   this fetches the XDP

- Foundation/SetValue/Execute   this converts whatever was fetched into a document variable

- Forms/FormsService/renderPDFForm   this merges the document with XML and produces PDF output

I'm unable to write the database field into a variable due to lack of choices. For instance there is no BLOB or CLOB variable type in the list of available types. When using STRING I get the following error:

Caused by: java.io.NotSerializableException: com.ibm.db2.jcc.b.ub
    at java.io.ObjectOutputStream.writeObject0(ObjectOutputStream.java:1081)
    at java.io.ObjectOutputStream.writeObject(ObjectOutputStream.java:302)
    at com.adobe.idp.dsc.util.CoercionUtil.toString(CoercionUtil.java:498)

When using XML I get the following error:

Caused by: com.adobe.workflow.WorkflowRuntimeException: Invalid location: /process_data/@clob_XDP_string cannot be stored for action instance: -1
    at com.adobe.workflow.pat.service.PATExecutionContextImpl.setProcessDataValue(PATExecutionContextImpl.java:701)

When using OBJECT I get the following error:

Caused by: com.adobe.workflow.WorkflowRuntimeException: Invalid location: /process_data/@clob_XDP_string cannot be stored for action instance: -1
    at com.adobe.workflow.pat.service.PATExecutionContextImpl.setProcessDataValue(PATExecutionContextImpl.java:701)

[Former Community Member]

Try a 'document' variable.

I created a table in MySQL with a column of type BLOB, inserted an .xdp, and successfully recreated your process. I used a variable of type 'document' called 'blob' in the output data mapping of the Query Single Row service operation. I then used 'blob' as both the input and output in the renderPDFForm service operation.

After invoking the process in Workbench I was able to view the document since 'blob' was defined as an output.

Steve

[Former Community Member]

Steve,

Going against DB2 doesn't work for me with a document variable type. It gives a coercion error.

I did solve my problem though from the following URL: http://groups.google.com/group/livecycle/browse_thread/thread/6c4b9156b52b71a7

JYates:

You can do this, but you have to  use the Execute Script service -- at this time there isn't a deployable component for it.

Use this sort of script in the Execute Script service to read  the PDF blob from the database and populate a Document variable.

import java.sql.Connection;

import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.ResultSet;
import javax.sql.DataSource;
import javax.naming.InitialContext;int processId =  patExecContext.getProcessDataIntValue("/process_data/@id");InitialContext context = new InitialContext();
Connection connection = ((DataSource)context.lookup("java:/IDP_DS")).getConnection();String queryQuery = "select bigdocument, bigstring from tb_pt_workwithxlobs where process_instance_id = ?";
PreparedStatement queryStatement = connection.prepareStatement(queryQuery);try {
   queryStatement.setInt(1, processId);
   ResultSet results = queryStatement.executeQuery();
   results.next();
   java.sql.Blob documentBlob = results.getBlob(1);
   com.adobe.idp.Document document = new com.adobe.idp.Document(documentBlob.getBinaryStream());
   patExecContext.setProcessDataValue("/process_data/@NewBigDocument",document);
   java.sql.Clob stringClob = results.getClob(2);
   patExecContext.setProcessDataValue("/process_data/@NewBigString",stringClob.getSubString(1L,(int)stringClob.length()));
}
catch(Exception ex) {
   ex.printStackTrace();
}
queryStatement.close();
connection.close();

[Former Community Member]

Interesting. I am curious. What JDBC driver are you using?

Additionally, with MySQL I was able to map the BLOB output to a 'binary' process variable, cast the 'binary' to a 'document', and render the PDF.

Steve

[Former Community Member]

I have no idea what JDBC driver. It's whatever Adobe decides I guess. It's a DB2 database. The problem I was having was where the querySingleRow operation tried to map a database field to a variable. It would either not coerce and not store, one of he two.