UpdateHandler requires different tables PostgreSQL v Oracle

Hi,





Using JChem version 3.1.1 when I create a structure table using jcman I get different results in the jchemproperties table depending on whether I am connected to an Oracle XE or PostgreSQL 8.1 database.





When connected to PostgreSQL 8.1 with jcman as user john I get, for a structure table called "structure" entries like





select prop_name || ' ' || prop_value from jchemproperties;





table.structure.creationTime 2006-04-23 10:03:55.375


table.structure.version 24


etc





When connected to Oracle XE with jcman as user john I get, for a structure table called "structure" entries like








table.JOHN.STRUCTURE.creationTime 2006-04-23 10:03:55.375


table.JOHN.STRUCTUREversion 24


etc





This in itself I guess is not really problem except for the fact that I have an application that can switch between talking to an Oracle or PostgreSQL database and as such this makes specifying the structure and jchemproperties table names to UpdateHandler difficult.





For PostgreSQL 8.1 I give UpdateHandler


"structure" and;


"jchemproperties"





but for the inserts to work under Oracle for all users I have to give





"JOHN.STRUCTURE" and;


"JOHN.JCHEMPROPERTIES"





along with grants to public on the structure, jchemproperties and sequence objects created by jcman





I attempted to get around this by creating public synonyms to the tables in Oracle as well as granting the appropriate permissions to public on the following objects:





STRUCTURE


STRUCTURE_UL


STRUCUTRE_SQ


STRUCTURE_USQ and;


JCHEMPROPERTIES





and then changing the parameters I pass to UpdateHandler to be the same as for PostgreSQL when connecting to Oracle (i.e. "structure" and "jchemproperties" without the schema prefix). This approach only works when I am logging into Oracle XE as user john (who is the owner of the structure, jchemproperties and sequence objects created by jcman) but not when logging in as any other Oracle user.





When running under other Oracle users I get the exception:





The table JOHN.STRUCTURE doesn't exist or size of fingerprint is not properly set in property table "jchemproperties".





Logged into sqlplus as any user other than john I can do a describe on STRUCTURE, STRUCURE_UL, JCHEMPROPERTIES and select nextval from STRUCTURE_SQ and STRUCTURE_USQ due to the public synonyms and grants. So I guess this comes down to how UpdateHandler decides to do the lookup into the jchemproperties table?





Is there any way to avoid having to pass something different to UpdateHandler depending on which type of database I am working with? Perhaps it would be possible to make the lookup used by UpdateHandler into the JChemProperties table less database specific?





Thanks,





James

Hi

Quote:

Using JChem version 3.1.1 when I create a structure table using jcman I get different results in the jchemproperties table depending on whether I am connected to an Oracle XE or PostgreSQL 8.1 database.

You typically should not care about what is in the jchemproperties table. The JChem tools and API are supposed to hide the internals of the jchemproperties table from users.

Quote:

I pass to UpdateHandler to be the same as for PostgreSQL when connecting to Oracle (i.e. "structure" and "jchemproperties" without the schema prefix).

You should always use the name of the "jchemproperties" table with the proper schema prefix. While JChem will try and "guess" the schema-prefix for non-schema-prefixed structure tables, it will never do so with the "jchemproperties" table: if, say, June connects with JChem to the database specifying the jchemproperties table without schema prefix, JChem will create a jchemproperties table in June's schema and will use that jchemproperties table for the current session. If June wants to use the jchemproperties table found in another user's schema, she has to explicitly specify so during connection.





As you observed by looking into the jchemproperties table, JChem does not currently prefix structure tables with schema names in the case of PostgreSQL. JChem will be "schema-aware" when used with PostgreSQL starting with the next version.





Cheers,


Peter

Schema awareness with PostgreSQL is included in JChem starting with the recently released 3.2 version.