2 Replies Latest reply on Nov 21, 2007 11:52 PM by (fadly)

    Insert document variable to database

    Level 1
      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
        • 1. Re: Insert document variable to database
          Jasmin Charbonneau Level 4
          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
          • 2. Re: Insert document variable to database
            Level 1
            Thanks Jasmin for your advice. The workaround is working fine.

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