import in csmol error: unique constraint violated

Dear Support,





To populate the JChem Structure table I used to execute the following code:

Code:

rs = pstmt.executeQuery(); rsmd = rs.getMetaData(); uh = new UpdateHandler(conh,zeMode,tableCsmol,otherFields); while (rs.next()) {    molfile = rs.getString(1);    id = rs.getInt(2);    uh.setID(id);    uh.setStructure(molfile);    for (i=2; i<rsmd.getColumnCount(); i++) {       int type = rsmd.getColumnType(i+1);       int canBeNull = rsmd.isNullable(i+1);       if (type==Types.NUMERIC) {          uh.setValueForAdditionalColumn(i-1,new Double(rs.getDouble(i+1)));       } else {          s = rs.getString(i+1);          if ((s!=null) || (canBeNull==ResultSetMetaData.columnNullable)) {             uh.setValueForAdditionalColumn(i-1, s);          } else {             uh.setValueForAdditionalColumn(i-1, "");          }       }    }    uh.execute(); }

But since the new version of JChem 5.1.4 I got the following error:

Quote:

ORA-00001: unique constraint (SCHEMA.TABLE_CSMOL_MD_PF_IDX) violated

Does something has changed in the procedure to import structures to csmol table using JChem API?





Thank you very much for your help





Warmest Regards,


Jacques

Dear support,





I found out the problem: I delete lines from table_csmol but not from table_csmol_md_pf.





But if I want to clean the csmol table and erase molecule that doesn"t match a condition and import new structure, do I need to erase both lines from table_csmol and table_csmol_md_pf? Or is there a trigger that will erase the line from the second table if the structure from the first one is deleted?





I'm doing like that:

Code:

delete from table_csmol where [i]criteria[/i]

And then import from a select on an other table.














Is there a way to do it using SQL command? Or Is it better to use JChemManager or JChem API?





What problems might I have to do it using SQL script?














Thank you for your help














Best Regards,





Jacques

Hi,





Any modification to JChem Base structure tables and other JChem Base database objects (property table, _UL table, indexes, sequences, etc) should only be done via ChemAxon applications or the JChem API.


(exception : you may update the values of custom data fields of JChem Base tables if they are not defined as Chemical Terms fields)





Please use one the following API methods to delete rows:





http://www.chemaxon.com/jchem/doc/api/chemaxon/jchem/db/UpdateHandler.html#deleteRow(chemaxon.util.ConnectionHandler,%20java.lang.String,%20int)





http://www.chemaxon.com/jchem/doc/api/chemaxon/jchem/db/UpdateHandler.html#deleteRows(chemaxon.util.ConnectionHandler,%20java.lang.String,%20java.lang.String)





Best regards,





Szilard

Dear Support,

I'm using

UpdateHandler uh = new UpdateHandler(conh, UpdateHandler.INSERT_WITH_ID, tableCsmol, "CUSTOM_ID, CUSTOM_FIELD");
UpdateHandler.deleteRows(conh, tableCsmol, condition);
When condition is not empty, there are no problem and the field of tableCsmol_MD_PF are deleted too.

But If I want to delete the whole table (condition = empty)
the table tableCsmol_MP_PF is untouched and each of the insert are

If I count data after deleting all rows the count gives me that data are still there?
ORA-00001: unique constraint (SCHEMA.TABLECSMOL_MD_PF_IDX) violatedDid I do something wrong?

Best Regards

Jack

Hi Jack,

 

Could you please

- describe what are you trying to achieve here ? The code exceprt shows an UpdateHandler for insert which is not used (?) and then the static delete call. Do you want to insert a row and then delete all structures ?

- let us know the exact JChem Version

- provide the full stack trace of the eror message

 

Best regards,

 

Szilard

Dear support,

I'm using actually JChem 5.1.4

My goal is to regenerate completly the structure search table.
For exemple, I know that some of the structure in the source table have changed, so I want to remove all the structure from the csmol table and import again all the structure from the source table.

I do as following

ConnectionHandler conh = new ConnectionHandler();
conh.setConnection(connection);
UpdateHandler uh = new UpdateHandler(conh, UpdateHandler.INSERT_WITH_ID, tableCsmol, "ID, PARAM1, PARAM2");
/* delete all rows from the structure table */
UpdateHandler.deleteRows(conh, tableCsmol, "");
Statement stmt = connection.createStatement();
/* Select all molecules from the source table*/
ResultSet rs = stmt.executeQuery(query);
/* Import all molecules in CSMOL table*/
while (rs.next()) {
  try {
    molfile = rs.getString(1);
    id = rs.getInt(2);
    uh.setID(id);
    uh.setStructure(molfile);
    uh.setValueForAdditionalColumn(1, rs.getInt(3));
    uh.setValueForAdditionalColumn(2, rs.getInt(4));
    uh.setValueForAdditionalColumn(3, rs.getInt(5));
    uh.execute();
  } catch (Exception e) { e.printStackTrace(); }
}

at com.aurtools.test.TestImportMolBatch.main(TestImportMolBatch.java:55)
java.sql.SQLException: ORA-00001: violation de contrainte unique (TABLE_CSMOL_MD_PF_IDX)

    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
    at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:219)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:970)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1190)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3370)
    at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3476)
    at chemaxon.descriptors.MDDBWriter.put(MDDBWriter.java:170)
    at chemaxon.jchem.db.UpdateHandler.updateDescriptors(UpdateHandler.java:2470)
    at chemaxon.jchem.db.UpdateHandler.execute(UpdateHandler.java:2420)
    at chemaxon.jchem.db.UpdateHandler.execute(UpdateHandler.java:2294)
    at com.aurtools.test.TestImportMolBatch.insertMolfile(TestImportMolBatch.java:145)
    at com.aurtools.test.TestImportMolBatch.run(TestImportMolBatch.java:118)
    at com.aurtools.test.TestImportMolBatch.main(TestImportMolBatch.java:55)
chemaxon.descriptors.MDWriterException: ORA-00001: violation de contrainte unique (TABLE_CSMOL_MD_PF_IDX)

    at chemaxon.descriptors.MDDBWriter.put(MDDBWriter.java:179)
    at chemaxon.jchem.db.UpdateHandler.updateDescriptors(UpdateHandler.java:2470)
    at chemaxon.jchem.db.UpdateHandler.execute(UpdateHandler.java:2420)
    at chemaxon.jchem.db.UpdateHandler.execute(UpdateHandler.java:2294)
    at com.aurtools.test.TestImportMolBatch.insertMolfile(TestImportMolBatch.java:145)
    at com.aurtools.test.TestImportMolBatch.run(TestImportMolBatch.java:118)
    at com.aurtools.test.TestImportMolBatch.main(TestImportMolBatch.java:55)After deleting all the rows, If I make a query 'select count(*) from tablecsmol_md_pf' all the values are still there.

Maybe I need to make a "commit" after deleted rows?

Actually it works fine, if I delete only a few rows using "where" option instead of Empty string.

I hope it helps.

Thank you for your help.

Hello Jack!

UpdateHandler.deleteRows  - works for me just as written in the API - deleting all the rows if empty string or null are specified as criterion.

From your stack trace I still can't see where the program fails. Please copy the full trace. There should be an UpdateHandler.deleteRows command.

I also wonder if you set the url, driver, login and passwd to the ConnectionHandler object.

Bye,

Robert

 

Hi,

The following seem to occur:

- You delete allrows from a structure table

- By referential integrity the rows from the Molecular Descriptor table(s) should be deleted

- You insert again with the same ID inot the structure table, JChem tries to update the descriptor tables where the auto update option is set

- Most likely not all rows were deted from the descriptor table for some reason , and then you get a uniqe constraint violation upon insert into the descriptor table.

 

The index (?) TABLE_CSMOL_MD_PF_IDX does not seem familiar to me.

- have you created this index yourself via SQL?

- have you manipulated the table structure via SQL ?

The latter could explain why the corresponding rows from the descriptor table were not deleted.

We have tried the same sistutation, and it worked for us without any problem.

Please note that as I have mentioned above that all JChem databse objects should only be manipulated by our API or applications.

 

Best regards,

 

Szilard