JCManager and PostgreSQL date column load error

jcman fails on loading date column types on PostgreSQL 9.1.1. Date is ISO format (YYYY-MM-DD).  Direct database insert via psql prompt works fine.  Stack Trace below:

 

 

WARNING: Unexpected error during DB import

java.sql.SQLException: 2007-03-29 can not be converted to date

        at chemaxon.jchem.db.sql.TypeConverter.convert(TypeConverter.java:95)

        at chemaxon.jchem.db.FieldInfo.setValue(FieldInfo.java:235)

        at chemaxon.jchem.db.ParallelStructTableUpdater.setValues(ParallelStructTableUpdater.java:186)

        at chemaxon.jchem.db.ParallelStructTableUpdater.importFile(ParallelStructTableUpdater.java:402)

        at chemaxon.jchem.db.FileToSQLHandler.importFile(FileToSQLHandler.java:136)

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

        at java.lang.Thread.run(Thread.java:662)

java.util.concurrent.ExecutionException: chemaxon.jchem.db.TransferException: Unexpected error during DB import: 2007-03-29 can not be converted to date

        at chemaxon.jchem.db.ParallelStructTableUpdater.importFile(ParallelStructTableUpdater.java:491)

        at chemaxon.jchem.db.FileToSQLHandler.importFile(FileToSQLHandler.java:136)

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

        at java.lang.Thread.run(Thread.java:662)

Caused by: chemaxon.jchem.db.TransferException: Unexpected error during DB import: 2007-03-29 can not be converted to date

        at chemaxon.jchem.db.ParallelStructTableUpdater.importFile(ParallelStructTableUpdater.java:481)

Caused by: java.sql.SQLException: 2007-03-29 can not be converted to date

        at chemaxon.jchem.db.sql.TypeConverter.convert(TypeConverter.java:95)

        at chemaxon.jchem.db.FieldInfo.setValue(FieldInfo.java:235)

        at chemaxon.jchem.db.ParallelStructTableUpdater.setValues(ParallelStructTableUpdater.java:186)

        at chemaxon.jchem.db.ParallelStructTableUpdater.importFile(ParallelStructTableUpdater.java:402)

 

 

Hi Matthew,

Could you provide information on

• your jcman version


• your table type?

It would be also helpful if you could send us the file you tried to import.

Best regards,
Tamás

jcman version 5.6.0.2

table type: molecules

Work around:

I successfully imported the complete file changing the sdf field name to text_date column type to char(10), then executed the following sql statements;

update [table] set registration_date = cast(text_date as date) where text_date is not null;

alter table [table] drop column text_date;

I think most modern databases automatically parse date and timestamp string literals so there is no need to create a java Date object which seems to be the cause of the problem.  I would suggest that users format date and timestamps that can be understood and parsed by the specific relational database engine they are using e.g. Oracle, PostgreSQL, MySQL.

Hi Mat,

thanks for sharing your workaround with the community and with us. Does this mean that the issue has lower severity now?

Regards,

Miklos

Yes, lower severity since we have a work around, but would be nice to have future versions of jcmanager support date, timestamp fields.

JChem 6.1 has been released with a fix to this problem:

JChem Base - DB functionalities

• JChem Manager and chemaxon.jchem.db.Importer failed to import date formats into PostgreSQL tables. See also the related FAQ entry in JChemBaseFAQ. 

Peter