JChem table + custom columns

Hi,





I cannot insert into a JChem table which has custom columns added. I have tried this both using JChemManager and the JC_INSERT function. Is this by design, or should I be able to do this?





Cheers





Paul

Hi Paul,





For the sake of simplicity I suggest to solve this for JChemManager first.





Please provide the following information:


- Which JChem version are you using ?


- What database engine are you connecting to ?


- What is the type of the custom column ?


- Did you attempt it from the GUI or from command-line


- Do you get any error messages, or the data is just not present ?





One limitation came to my mind (we plan to eliminate this): currently you should add the custom columns after the fixed fields as described in the documentation :


http://www.chemaxon.com/jchem/doc/admin/#create


The documentation also states that the name of the custom columns should not start with "cd_".





Best regards,





Szilard

JChem 3.1.7


Oracle 9.2





The insert was attempted from the GUI.





I did add the custom columns after the JChem columns and none of the columns start with "cd_"





The error relates to a field of type 'DATE'. Below is the error/stack trace from the dos window. Error is because it is trying to insert NULL into the date field, but it is set with a default value and null is not allowed.





Error in molecule 1


java.sql.SQLException: ORA-01400: cannot insert NULL into ("JCHEMTEST"."JCHEM_VI


TIC"."FIELD3")





at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)


at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)


at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)


at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)


at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:109


3)


at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.ja


va:2047)


at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.jav


a:1940)


at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStateme


nt.java:2709)


at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePrepar


edStatement.java:589)


at chemaxon.jchem.db.UpdateHandler.execute(UpdateHandler.java:1529)


at chemaxon.jchem.db.FileToSQLHandler.importFile(FileToSQLHandler.java:2


62)


at chemaxon.jchem.gui.Importer.run(Importer.java:137)


at java.lang.Thread.run(Unknown Source)








Cheers





Paul

Hi,





This is because JChem tries to insert NULL if the tag is not specified in a particular record in the input file.





I suggest you to remove the "NOT NULL" constraint from the column, and do one of the following:





- create a trigger for the table, if the input value is NULL for the column change it to the desired default





- If the GUI availability is not important for you (Cartridge): after import set the NULL values to the desired default, e.g


UPDATE <mytable> SET <column>=<default> WHERE <column> IS NULL;


If you do it in a transaction with the import, data will also be always consistent. (other connections cannot see your changes until you commit)








Best regards,





Szilard





PS: We will also release a new GUI product called Instant JChem soon.


http://www.chemaxon.com/forum/forum62.html


There you will have the ability to specify default values for columns at table creation, and you can also override them during a specific import.

Thanks for your solution.





It would be nice if the importer could insert the default value if one is specified.





Cheers





Paul

But then the "defaultValue" option would be specified to the importer and not to your database.





P.

I meant the default value as defined on the column in oracle, not as an option on the import.

I thought that the functionality you want is that when an SDF tag is missing for a structure, a default value be inserted into the column (where normally an SDF tag value would be stored). If this is what you want, then specifying the default value to the importer would be more logical than specifying it to your database (which does not know anything about the importer importing something -- let alone about SDF tags).





P.