Reading data from CLOB column | Content Size > 4000 Characters

[Archit Garg]

Hi All,

 

Can you please tell me what variable type I should be using for storing CLOB type of data in Adobe LiveCycle Processes?

 

I have a JDBC - Query Sigle Row activity included in my process and the select query looks like:

 

SELECT
 FORM_DATA_CLOB  -- CLOB Datatype
FROM
 DEP_FORM_ENTRY
WHERE
 FORM_ENTRY_ID = 82

 

Now I am not sure in which type of variable I should be reading "FORM_DATA_CLOB" column value into, as java.lang.Object, java.lang.String, com.adobe.idp.Document are all erroring out. The size of CLOB is greater than 4000 Characters and that is why I could not even convert FORM_DATA_CLOB value from CLOB to STRING at database level by using TO_CHAR(FORM_DATA_CLOB) as it truncates the CLOB content with index > 4000. 

 

Please help.

 

Many thanks.

Archit

[Jeff Yates, Sr. Technical Architect, Avoka.com]

There may be more than one way to do this, I used an ExecuteScript
component to use JDBC. My company created the "how to" on reading and
writing LOB data in the Tour de LiveCycle, which you can get here:
http://download.macromedia.com/pub/developer/livecycle/TourDeLiveCycle.air

The TdL for LiveCycle ES2 doesn't have the xLOB sample, but for
completeness it is here: http://www.adobe.com/devnet/livecycle/tourdelivecycle/

TdL has several samples and how tos.

Jeff Yates
Avoka Technologies
www.avoka.com

[Jeff Yates, Sr. Technical Architect, Avoka.com]

I just checked the TdL content, and the xLOB sample didn't make it
past final editing. The script in the ExecuteScript component to read
from a CLOB and a BLOB is:

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();

The "NewBigDocument" process variable is type "Document" and
"NewBigString" process variable is type "string" with unlimited
length.

Hope that helps.

Jeff Yates
Avoka Technologies
www.avoka.com

On Feb 18, 5:16 pm, "Jeff Yates, Sr. Technical Architect, Avoka.com"

<jya...@avoka.com> wrote:
> There may be more than one way to do this, I used an ExecuteScript
> component to use JDBC.  My company created the "how to" on reading and

> writing LOB data in the Tour de LiveCycle, which you can get here:http://download.macromedia.com/pub/developer/livecycle/TourDeLiveCycl...