User 2082812c83
13-10-2006 02:27:05
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
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