2 Replies Latest reply on Apr 3, 2012 8:17 AM by LSNOVER

    JDBC and binary, PutAs function

    dobunzli

      Hello all,

      I am trying to insert a pdf file into a filemaker database from a software named Mirth Connect that receives and handles lab results in hl7 and pdf.

      The pdf is first converted by Mirth Connect into a Base64 encoded string. Then I am using javascript to transform this string into a byte array (using apache commons decode Base64) and then into an inputstream using ByteArrayInputStream. If I have understood well, this inputstream should then be used in the Filemaker jdbc PutAs function.

       

      I receive the following error

      [2012-03-28 16:01:26,720] ERROR (org.mule.impl.DefaultComponentExceptionStrategy:95): Caught exception in Exception Strategy for: 8151cbdd-52b7-485a-8721-b14e63f420c9: java.lang.Exception: java.lang.AbstractMethodError: com.filemaker.jdbc3.J3PreparedStatement.setBinaryStream(ILjava/io/InputStream;J)V

       

      Here is the javascript code that I am using. Any idea what is wrong ?

      Thanks in advance

       

      importPackage(java.sql);

      importPackage(java.io);

       

      // database connexion information

      new com.filemaker.jdbc.Driver();

      var dbConn = DriverManager.getConnection('jdbc:filemaker://localhost/M_mirthbridge','labobbv','labobbv2010');

       

      // get the base64 String as byte array

      var pdfBytes = org.apache.commons.codec.binary.Base64.decodeBase64(messageObject.getRawData().replaceAll('[^A-Za-z0-9\+\/\=]',''));

       

      // get the length of byte array (useful in setBinaryStream function)

      var arrayLength = pdfBytes.length;

       

      // creates the ByteArrayInputStream

      var inputStream = new ByteArrayInputStream(pdfBytes);

       

      // creates the sql command to insert the filename and the binary file using PutAs

      var ps = dbConn.prepareStatement("insert into Laboratoire_Incoming (labin_labodossier_id, labin_blob) values (?,PutAs(?,'PDF '))");

       

      // parameter 1

      ps.setString(1,$('originalFilename'));

       

      // parameter 2

      ps.setBinaryStream(2,inputStream,arrayLength);

       

      // do it

      ps.executeUpdate();

       

      // close

      ps.close();

      dbConn.close();

        • 1. Re: JDBC and binary, PutAs function
          dobunzli

          Actually with using "setBytes" instead of "setBinaryStream" it worked !

           

           

          importPackage(java.sql);
          importPackage(java.io);
          
          
          // database connexion information
          new com.filemaker.jdbc.Driver();
          var dbConn = DriverManager.getConnection('jdbc:filemaker://localhost/M_mirthbridge','login','pass');
          
          
          // The pdf is transformed by Mirth Connect into a base64 String
          // Here we transform the base64 String as byte array. 
          var pdfBytes = org.apache.commons.codec.binary.Base64.decodeBase64(messageObject.getRawData().replaceAll('[^A-Za-z0-9\\+\\/\\=]',''));
          
          
          // creates the sql command to insert the filename and the binary file using PutAs
          var ps = dbConn.prepareStatement("insert into Laboratoire_Incoming (labin_labodossier_id, labin_blob) values (?, PutAs(?,'PDF '))");
          
          
          // original filename is used as id for the pdf file
          // original filename with extension
          var filenameFull = $('originalFilename')
          // filename without extension
          var fileName = filenameFull.substr(0,filenameFull.lastIndexOf("."));
          
          
          // parameter 1
          ps.setString(1,fileName);
          
          
          // parameter 2
          ps.setBytes(2,pdfBytes)
          
          
          // do it
          ps.executeUpdate();
          
          
          // close
          ps.close();
          dbConn.close();
          
          • 2. Re: JDBC and binary, PutAs function
            LSNOVER

            Glad you got it sorted out.  I'm not much of a Java person.    I wish ESS supported these binary fields.