Oracle BLOB value to a LC variable

[Former Community Member]

Hello,

Is it possible in LC ES2 to coerce oracle blob value to some livecycle variable ?

When mapping sql query result (column of type BLOB) to a LC variable (of type Document), we receive:

com.adobe.workflow.datatype.InvalidCoercionException: Cannot coerce object: oracle.sql.BLOB@27e7d6 of type: oracle.sql.BLOB to type: com.adobe.idp.Document

We have tried mapping to string, binary, Document (, we even desperately hardcoded sql.oracle.BLOB to process XML), but none of this works.

Is it possible to solve this other way, then following ?

http://groups.google.com/group/livecycle/browse_thread/thread/6c4b9156b52b71a7

Thanks.

[Jasmin_Charbonn]

Have you tried to use the data type "Object"?

Jasmin

[Former Community Member]

Thanks for reply Jasmin.

It is really possible to store BLOB column from query result to Object variable. I have tried to assign it then to Document variable via SetValue, but this produces:

Caused by: ALC-DSC-119-000: com.adobe.idp.dsc.util.InvalidCoercionException: Cannot coerce object: oracle.sql.BLOB@bbdde2 of type: oracle.sql.BLOB to type: interface org.w3c.dom.Document

Would You have some hint yet for this?

The goal is to read pdf form stored in BLOB Oracle field, than use it (as a Document) with Apply Usage Rights to Reader extend the form.

[Former Community Member]

:-/ I still don't like this way, but the execute script may be minimized to cast logic:

Then we don't need to place jdbc logic to executeScript (and avoid to hardcode/pass here e.g. datasource ids).

It is still required to map blob field to Object type in Query Single Row component.

try {
    java.sql.Blob documentBlob =  (java.sql.Blob) patExecContext.getProcessDataValue("/process_data/BLOB_FIELD_STORED_IN_OBJECT_VAR");
    com.adobe.idp.Document document = new com.adobe.idp.Document(documentBlob.getBinaryStream());
    patExecContext.setProcessDataValue("/process_data/@doc", document);

} catch(Exception e) {
    //do something, if desired
    throw e;
}

I still feel, that this should be possible to make somehow without exectue script ?

[Jasmin_Charbonn]

I just noticed something in your error:

Caused by: ALC-DSC-119-000:com.adobe.idp.dsc.util.InvalidCoercionException: Cannot coerce object:oracle.sql.BLOB@bbdde2 of type: oracle.sql.BLOB to type: interfaceorg.w3c.dom.Document

It can't cast the Blob into a org.w3c.dom.Document.

The org.w3c.dom.Document is an xml datatype that's why it's not working. The LiveCycle document data type is com.adobe.idp.Document. It's a different object.

Are you sure you're selecting the right data type?

Jasmin

[Former Community Member]

Sorry, You are right. I don't know, where the org.w3c.dom.Document came from.

But still, when I use com.adobe.idp.Document, I get:

com.adobe.workflow.datatype.InvalidCoercionException: Cannot coerce object: oracle.sql.BLOB@1045881 of type: oracle.sql.BLOB to type: com.adobe.idp.Document

[_Nith_ · Accepted solution]

My idea is to fetch the BLOB column data as UTF8 (base64) encoded string and then use getDocumentFromBase64 available in ES2.

I am not sure what syntax is to fetch from ORACLE.

I have used MySql database and the query is:

SELECT CONVERT(Blob_Column USING UTF8) as MyBlobDataAsBase64Str FROM myTableName;

Now using SetValue activity to look like:   myXMLvariable = getDocumentFromBase64(strVariableHoldingBase64Data)

This case works perfectly without any issues.

---------------

The problems is you should find out the appropriate syntax for ORACLE.

I was searching about CONVERT() & UNISTR() functions. But i'm unable to evaluate.

Try by yourself..

Nith

[Former Community Member]

I think, this is the cleanest available solution $Nith$, very good idea.

I have even tried st. similar, but as an Oracle beinner, I have problem with the BLOB -> base64 string conversion. I am consulting this with db specialists and post solution, if I get one :-).

Thanks!