Oracle datatypes and jcman

User 6d24b35814

22-12-2010 10:44:42

jcman version:
JChemManager 5.3.4, (C) 2000-2010 ChemAxon Ltd.


oracle: 9i client connecting to 10g server


I have a smiles file with several fields in it which I want to import into a database. I set up a MySQL test db and managed to successfully import data into it, using the following string:-


jcman --driver com.mysql.jdbc.Driver --dburl jdbc:mysql://machine/TestDB -l root --t:any --connect "parent_id=FIELD_0;DONOR=FIELD_3;ACCEPTOR=FIELD_2;ROT_BOND=FIELD_3;PREFILTER=FIELD_1" --stconfig parentstand.xml a PARENT multitest.smi


but that was just a test db, the real database needs to be in oracle. The chem table in oracle has been set up with the following extra fields:-


CD_FP32 NUMBER(10,0)
PARENT_ID NUMBER
HEAVY_ATOMS NUMBER
DONOR NUMBER
ACCEPTOR NUMBER
LOGP NUMBER 
ROT_BOND NUMBER 
RG_SMILES VARCHAR2(4000 BYTE) 
RG_FP VARCHAR2(4000 BYTE)
TP_SMILES VARCHAR2(4000 BYTE) 
TP_FP VARCHAR2(4000 BYTE) 
PREFILTER VARCHAR2(6 BYTE)


I can't import the full data with this set up.


I can get the smiles and parent id in, using the following string:-


 jcman --driver oracle.jdbc.driver.OracleDriver --dburl jdbc:oracle:thin:@machine:1521:DB -l user -p pwd --t:any --connect "parent_id=FIELD_0" --stconfig parentstand.xml a DB.PARENT multitest.smi


but if I try to add any other field, eg,


 jcman --driver oracle.jdbc.driver.OracleDriver --dburl jdbc:oracle:thin:@machine:1521:DB -l user -p pwd --t:any --connect "parent_id=FIELD_0;DONOR=FIELD_3;ACCEPTOR=FIELD_2;ROT_BOND=FIELD_3;PREFILTER=FIELD_1" --stconfig parentstand.xml a DB.PARENT multitest.smi


I get the following error for every row:-


Error in molecule 2000
java.lang.NumberFormatException
        at java.math.BigDecimal.<init>(BigDecimal.java:459)
        at java.math.BigDecimal.<init>(BigDecimal.java:728)
        at chemaxon.jchem.db.sql.TypeConverter.convert(TypeConverter.java:58)
        at chemaxon.jchem.db.FieldInfo.setValue(FieldInfo.java:230)
        at chemaxon.jchem.db.ParallelStructTableUpdater.setValues(ParallelStructTableUpdater.java:187)
        at chemaxon.jchem.db.ParallelStructTableUpdater.importFile(ParallelStructTableUpdater.java:395)
        at chemaxon.jchem.db.FileToSQLHandler.importFile(FileToSQLHandler.java:129)
        at chemaxon.jchem.db.Importer.importMols(Importer.java:469)
        at chemaxon.jchem.Command.importFromFile(Command.java:1191)
        at chemaxon.jchem.Command.run(Command.java:655)
        at chemaxon.jchem.Command.main(Command.java:216)


Is jcman very strict about the number datatype in oracle and what should this be set as? Incidentally I did try to import the data with just a varchar field:-


jcman --driver oracle.jdbc.driver.OracleDriver --dburl jdbc:oracle:thin:@machine:1521:DB -l user -p pwd --t:any --connect "parent_id=FIELD_0;PREFILTER=FIELD_1" --stconfig parentstand.xml a DB.PARENT multitest.smi


but that still gives me the same number format error.


Kind regards


Ceara

User 6d24b35814

22-12-2010 11:12:57

 


Apologies, I remember now how I got the data into MySQL, it seems the smiles file can be entered with a whitespace separator, if there are just 2 fields


Smiles ID


CCC<whitespace>1234


but if there are more fields, then the separator has to be tab


Smiles ID OtherData.......


CCC<tab>1234<tab>string<tab>number


 


 

ChemAxon e274e1bada

23-12-2010 17:51:53

Hi Ceara,


It seems to be a bug in our SQL type converter.
Could you send me some rows from the smiles file? You can send it directly to my address or to support _at_ chemaxon.com.


If understood well, the MySql import was successfull. wasn' t it?


Best Regards, Edvard