Insert document variable to database

[Former Community Member]

Hi,



In my process, I have a variable list of document which is used to store attachments from Workspace ES. I have managed to loop through the list and get each document variable. However, I have a problem with inserting the document variable to a table in database such as MySQL and SQL Server 2005. The attachment column in the database table will have a datatype of 'BLOB' (for MySQL) and 'IMAGE' (for SQL Server 2005). I have tried to use 'Foundation - JDBC - Execute SQL Statement' and it was not successful.



My question:

Which service component should I use to insert document variable to database and how to configure/setup the component?



Please advise.



Thanks,

Fadly

[Jasmin_Charbonn]

This is a little more tricky than it looks like. The problem is you can't pass a variable of type document because the jdbc service doesn't really know what a type "document" is. If you put it in a binary object it doesn't work for some reason.



I'm investigation why.



As a workaround you can use the script operation (under Foundation) and use the following script which would add it to your database:



import javax.naming.InitialContext;

import java.sql.Connection;

import java.lang.System;

import java.io.InputStream;

import java.io.ByteArrayInputStream;

import java.sql.*;

import com.adobe.idp.Document;



InitialContext _ic = new InitialContext();



ds = _ic.lookup("java:/IDP_DS");



Connection conn = null;

PreparedStatement stmt = null;



conn = ds.getConnection();

String strStmt = "Insert into temp.test Values (9,?)";

stmt = conn.prepareStatement(strStmt);



//Get the document from the document variable

Document myDoc = patExecContext.getProcessDataDocumentValue("/process_data/@pdfdoc");

InputStream inputStream = myDoc.getInputStream();

//Get the size of the InputStream object

int size = inputStream.available();

//Create and populate a byte array

byte[] data = new byte[size];

inputStream.read(data);



ByteArrayInputStream _in = new ByteArrayInputStream(data);

stmt.setBinaryStream(1, _in, _in.available());

stmt.executeUpdate();

conn.commit();



Jasmin

[Former Community Member]

Thanks Jasmin for your advice. The workaround is working fine.



Btw, where can we find documentation about patExecContext for LiveCycle ES?