Can I pass external file to jc_molconvertbb in JDBC?

Hi,





I am in trouble to convert sdf files using jc_molconvertbb. Some structure files are too large, I have to use jc_molconvertbb which takes a BLOB structure and returns BLOB. The structures files are not in the database, I am using JDBC to put sdf into BLOB and pass it to jc_molconvertbb. I got the following error message.





ORA-29532: Java call terminated by uncaught Java exception: java.sql.SQLException: Invalid empty lob operation


ORA-06512: at "JCHEM_CARTRIDGE.JCHEM_BLOB_PKG", line 0


ORA-06512: at "JCHEM_CARTRIDGE.JCF_MOLCONVERTBB", line 7





I searched online, didn’t find any useful information about “Invalid empty lob operation”.





BTW, if the query structure comes from a table, jc_molconvertbb works well. Also in oracle document, I was told that Temporary LOBs do not support the EMPTY_BLOB or EMPTY_CLOB functions that are supported for permanent LOBs. I wonder if you could tell me that if I can use jc_molconvertbb to convert external sdf files in JDBC. If the answer is yes, would you please provide a sample code for JDBC, I already tried my best, but failed.





Any input is helpful, thanks





Bin

Hi Bin,





Please, could you tell me which Oracle and JChem version you use.





Please, could you also post the corresponding Java stack trace from the Oracle session trace file in the "udump" directory.





Please, could post the relevant lines from your code so I better understand what you are actually doing.





Thanks,


Peter

Hi, Peter





Is it possible to provide a Java sample code that can use the jc_molconvertbb function to convert the attached SDF file into a smiles string (via the SQL interface)? I cannot use the molconvert command line to do this, but could not figure out how to do it through the cartridge.





Thanks.

BTW. I have tried jcf_molconvertbb as well.

Hi Bin,





If you are using Java anyway, it may make sense to use our Java API directly. See this example for converting molecules (this is a very simple version of the command line program molconvert):





http://www.chemaxon.com/marvin/examples/converter/SimpleConverter.java.txt





This example works fine with both ManrvinBeans.jar and jchem.jar. See the API documentation for more information of the classes used:


http://www.chemaxon.com/api_land.html





Szabolcs

Sample for calling jc_molconvertbb through JDBC:

Code:

StringWriter sw = new StringWriter();         FileReader fr = new FileReader("C:/tmp/test23.sdf");         try {             char[] buffer = new char[4096];             int readCount = fr.read(buffer);             while (readCount != -1) {                 sw.write(buffer, 0, readCount);                 readCount = fr.read(buffer);             }         } finally {             fr.close();         }         String smilesString = null;                 String sql = "SELECT jc_molconvertbb(?, ?) FROM dual";         ResultSet rs = null;         PreparedStatement pstmt = getConnection().prepareStatement(sql);         try {             String outputFormatOptions = "smiles";             TemporaryBlob tmpBlob =                     new TemporaryBlob(getConnection(), sw.toString());             try {                 pstmt.setBlob(1, tmpBlob.getBlob());                 pstmt.setString(2, outputFormatOptions);                 rs = pstmt.executeQuery();                 rs.next();                 Blob blob = rs.getBlob(1);                 smilesString = TemporaryBlob.blobToString((oracle.sql.BLOB) blob);             } finally {                 tmpBlob.free();             }         } finally {             pstmt.close();         }                         System.out.println("SMILES: " + smilesString);

I attached a sample implementation of the class TemporaryBlob, but I leave the implementation of the java.sql.Connection getConnection() method to you. The reason for a custom wrapper for temporary blobs is that temporary blob behavior with JDBC is not consistent across Oracle versions and in certain cases (9i) it is even buggy.





Cheers,


Peter