java.sql.SQLException: Table does not exist

Hello,

Can someone possibly help in trying to track down this issue?  I am getting the following exception when I'm trying to do a structure search using the JChemSearch API.

java.sql.SQLException: Table 'public.syn_structure' does not exist

        at chemaxon.jchem.db.RegenerationChecker.isRegenerationNeededExact(RegenerationChecker.java:187)

        at chemaxon.jchem.db.RegenerationChecker.isRegenerationNeeded(RegenerationChecker.java:132)

        at chemaxon.jchem.db.RegenerationChecker.isRegenerationNeeded(RegenerationChecker.java:112)

        at chemaxon.jchem.db.RegenerationChecker.isRegenerationNeeded(RegenerationChecker.java:99)

        at chemaxon.jchem.base.persist.impl.sql.SqlStructureCollectionMetaData.checkVersion(SqlStructureCollectionMetaData.java:104)

        at chemaxon.jchem.db.SimpleJChemSearch$TargetCollectionRef.acquire(SimpleJChemSearch.java:119)

        at chemaxon.jchem.db.SimpleJChemSearch.searchCore(SimpleJChemSearch.java:238)

        at chemaxon.jchem.db.JChemSearch.searchCore(JChemSearch.java:1414)

        at chemaxon.jchem.db.JChemSearch.search(JChemSearch.java:1471)

        at chemaxon.jchem.db.JChemSearch.run(JChemSearch.java:1387)

        at com.synsci.compchem.jobmodel.structsearch.JChemBaseStructureSearch.jchemBaseSearch(JChemBaseStructureSearch.java:155)

        at com.synsci.compchem.jobmodel.structsearch.JChemBaseStructureSearch.search(JChemBaseStructureSearch.java:93)

        at com.synsci.compchem.jobmodel.structsearch.StructureRmiJobModel$1.runWork(StructureRmiJobModel.java:158)

        at com.synsci.compchem.concurrent.StoppableCallable.call(StoppableCallable.java:42)

        at com.synsci.compchem.concurrent.StoppableTimerTask.exec(StoppableTimerTask.java:42)

        at com.synsci.compchem.concurrent.StoppableTimerTask.exec(StoppableTimerTask.java:35)

        at com.synsci.compchem.jobmodel.composite.StructureRmiCompositeJobModel$1.runWork(StructureRmiCompositeJobModel.java:119)

        at com.synsci.compchem.concurrent.StoppableCallable.call(StoppableCallable.java:42)

        at com.synsci.compchem.concurrent.StoppableTimerTask.exec(StoppableTimerTask.java:42)

        at com.synsci.compchem.concurrent.StoppableTimerTask.exec(StoppableTimerTask.java:35)

        at com.synsci.compchem.jobmodel.composite.MultiStructureCompositeJobModel$4.runWork(MultiStructureCompositeJobModel.java:292)

        at com.synsci.compchem.concurrent.StoppableCallable.call(StoppableCallable.java:42)

        at com.synsci.compchem.concurrent.StoppableTimerTask.exec(StoppableTimerTask.java:42)

        at com.synsci.compchem.concurrent.StoppableTimerTask.exec(StoppableTimerTask.java:35)

        at com.synsci.compchem.jobmodel.base.AbstractJobModel.run(AbstractJobModel.java:151)

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

We're using postgresql and the following commands show that the syn_structure table exists and is accessible.

<Show tables in database>
synaptic=# \d;

...

 public | syn_structure                            | table    | jchem

 public | syn_structure_cd_id_seq                  | sequence | jchem

 public | syn_structure_ul                         | table    | postgres

 public | syn_structure_ul_update_id_seq           | sequence | postgres

 

<Show JChemProperties entries for syn_structure table>
synaptic=# SELECT prop_name, prop_value FROM JCHEMPROPERTIES WHERE prop_name LIKE 'table.public.syn_structure.%';

                         prop_name                          |       prop_value        

------------------------------------------------------------+-------------------------

 table.public.syn_structure.creationTime                    | 2006-04-23 10:03:55.375

 table.public.syn_structure.absoluteStereo                  | true

 table.public.syn_structure.fingerprint.numberOfBits        | 512

 table.public.syn_structure.fingerprint.numberOfOnes        | 2

 table.public.syn_structure.fingerprint.numberOfEdges       | 6

 table.public.syn_structure.fingerprint.numberOfStrucFPCols | 0

 table.public.syn_structure.tableType                       | 2

 table.public.syn_structure.switchOffAllProtections         | false

 table.public.syn_structure.tautomerDuplicateFiltering      | false

 table.public.syn_structure.duplicateFiltering              | false

 table.public.syn_structure.version                         | 6100000

 table.public.syn_structure.JChemVersion                    | 6.1.0

 table.public.syn_structure.validityTimestamp               | 2013-09-25 17:36:41.343

 

<Verify that entries can be accessed>
synaptic=# select * from public.syn_structure;  

<This returns entries just fine>

Any idea why this exception is being thrown?  

Thanks!

Hi,

Could you double-check if the correct connection is used for JChemSearch (i.e. the same as you used for your SQL commands).

For example, add these lines just before executing the run() method of JChemSearch:

    ConnectionHandler ch = <JChemSearch instance>.getConnectionHandler();
    String tableName = "syn_structure";
    System.out.println(ch);
    System.out.println(TableInfo.isTableExists(ch, tableName));
    System.out.println(new DatabaseProperties(ch, false).getTableProperties(tableName));

The first print command should print the parameters of the connection, while the second one should print "true", and third one should print the property entries corresponding to your table (provided that the connection is correct).

Regards,
Peter

Thanks for the response.  The issue was that we were querying multiple databases and one of the database tables did not have the proper jchemproperties entries. 

Hi,

I am happy that you have found the solution.

All the bests!

Norbert