You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by "Michael Dick (JIRA)" <ji...@apache.org> on 2009/07/21 19:56:14 UTC
[jira] Created: (OPENJPA-1189) Column names do not contain
delimiters when obtained from DatabaseMetaData
Column names do not contain delimiters when obtained from DatabaseMetaData
--------------------------------------------------------------------------
Key: OPENJPA-1189
URL: https://issues.apache.org/jira/browse/OPENJPA-1189
Project: OpenJPA
Issue Type: Bug
Components: jdbc
Affects Versions: 2.0.0
Environment: DB2 (could exist on other databases)
Reporter: Michael Dick
The DatabaseMetaData returned by DB2 does not include delimiters for column names which include whitespace. This causes potential duplicate column names if we merge into an existing schema.
The problem can be illustrated by running the TestManualDelimitedJoinAnnotations test twice sequentially. The first time the test is executed the database will be clean (presumably) and everything will work. Subsequent executions read the existing column information from the database (non delimited) and end up creating a duplicate column.
Example stacktrace :
<openjpa-2.0.0-SNAPSHOT-r422266:794075M nonfatal general error> org.apache.openjpa.persistence.PersistenceException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=);entityCs_c id OTHER;<references_spec>, DRIVER=3.50.152 {stmnt 10751758 CREATE TABLE "delim id"."c d" ("entityCs_c id" INTEGER, ENTITYDS_ID INTEGER, entityCs_c id OTHER)} [code=-104, state=42601]
at org.apache.openjpa.jdbc.meta.MappingTool.record(MappingTool.java:553)
at org.apache.openjpa.jdbc.meta.MappingTool.record(MappingTool.java:453)
<snip>
To resolve the problem we can detect a column / table / schema name which contains whitespace and add the appropriate delimiters (when the name is provided by the database). One place to make this change is in DBDictionary.newColumn()
protected Column newColumn(ResultSet colMeta)
throws SQLException {
Column c = new Column();
c.setSchemaName(colMeta.getString("TABLE_SCHEM"));
c.setTableName(colMeta.getString("TABLE_NAME"));
c.setName(colMeta.getString("COLUMN_NAME")); // detect whether we need delimiters and add them
It's also worth noting that the testcase drops the offending table and then recreates it. There may be a larger issue where the metadata is cached improperly (could be DB2 specific).
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-1189) Column names do not contain
delimiters when obtained from DatabaseMetaData
Posted by "Michael Dick (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1189?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12734243#action_12734243 ]
Michael Dick commented on OPENJPA-1189:
---------------------------------------
Hi Dianne,
I figured you'd know when we need delimiters better than I do (although I'm surprised that mixed case would be one of them). I imagine DBDictionary could do with a boolean needsDelimiters(String identifier) method, or String delimitIfNeeded(String identifier). A quick grep through DBDictionary didn't turn one up (I could have missed it though).
> Column names do not contain delimiters when obtained from DatabaseMetaData
> --------------------------------------------------------------------------
>
> Key: OPENJPA-1189
> URL: https://issues.apache.org/jira/browse/OPENJPA-1189
> Project: OpenJPA
> Issue Type: Bug
> Components: jdbc
> Affects Versions: 2.0.0
> Environment: DB2 (could exist on other databases)
> Reporter: Michael Dick
>
> The DatabaseMetaData returned by DB2 does not include delimiters for column names which include whitespace. This causes potential duplicate column names if we merge into an existing schema.
> The problem can be illustrated by running the TestManualDelimitedJoinAnnotations test twice sequentially. The first time the test is executed the database will be clean (presumably) and everything will work. Subsequent executions read the existing column information from the database (non delimited) and end up creating a duplicate column.
> Example stacktrace :
> <openjpa-2.0.0-SNAPSHOT-r422266:794075M nonfatal general error> org.apache.openjpa.persistence.PersistenceException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=);entityCs_c id OTHER;<references_spec>, DRIVER=3.50.152 {stmnt 10751758 CREATE TABLE "delim id"."c d" ("entityCs_c id" INTEGER, ENTITYDS_ID INTEGER, entityCs_c id OTHER)} [code=-104, state=42601]
> at org.apache.openjpa.jdbc.meta.MappingTool.record(MappingTool.java:553)
> at org.apache.openjpa.jdbc.meta.MappingTool.record(MappingTool.java:453)
> <snip>
> To resolve the problem we can detect a column / table / schema name which contains whitespace and add the appropriate delimiters (when the name is provided by the database). One place to make this change is in DBDictionary.newColumn()
> protected Column newColumn(ResultSet colMeta)
> throws SQLException {
> Column c = new Column();
> c.setSchemaName(colMeta.getString("TABLE_SCHEM"));
> c.setTableName(colMeta.getString("TABLE_NAME"));
> c.setName(colMeta.getString("COLUMN_NAME")); // detect whether we need delimiters and add them
> It's also worth noting that the testcase drops the offending table and then recreates it. There may be a larger issue where the metadata is cached improperly (could be DB2 specific).
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-1189) Column names do not contain
delimiters when obtained from DatabaseMetaData
Posted by "Dianne Richards (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1189?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12734181#action_12734181 ]
Dianne Richards commented on OPENJPA-1189:
------------------------------------------
>To resolve the problem we can detect a column / table / schema name which contains whitespace and add the appropriate delimiters (when the name is provided by the database).
It probably won't be this simple. There could be other characters (other than whitespace) that need delimiting. Also, there could be lowercase characters.
> Column names do not contain delimiters when obtained from DatabaseMetaData
> --------------------------------------------------------------------------
>
> Key: OPENJPA-1189
> URL: https://issues.apache.org/jira/browse/OPENJPA-1189
> Project: OpenJPA
> Issue Type: Bug
> Components: jdbc
> Affects Versions: 2.0.0
> Environment: DB2 (could exist on other databases)
> Reporter: Michael Dick
>
> The DatabaseMetaData returned by DB2 does not include delimiters for column names which include whitespace. This causes potential duplicate column names if we merge into an existing schema.
> The problem can be illustrated by running the TestManualDelimitedJoinAnnotations test twice sequentially. The first time the test is executed the database will be clean (presumably) and everything will work. Subsequent executions read the existing column information from the database (non delimited) and end up creating a duplicate column.
> Example stacktrace :
> <openjpa-2.0.0-SNAPSHOT-r422266:794075M nonfatal general error> org.apache.openjpa.persistence.PersistenceException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=);entityCs_c id OTHER;<references_spec>, DRIVER=3.50.152 {stmnt 10751758 CREATE TABLE "delim id"."c d" ("entityCs_c id" INTEGER, ENTITYDS_ID INTEGER, entityCs_c id OTHER)} [code=-104, state=42601]
> at org.apache.openjpa.jdbc.meta.MappingTool.record(MappingTool.java:553)
> at org.apache.openjpa.jdbc.meta.MappingTool.record(MappingTool.java:453)
> <snip>
> To resolve the problem we can detect a column / table / schema name which contains whitespace and add the appropriate delimiters (when the name is provided by the database). One place to make this change is in DBDictionary.newColumn()
> protected Column newColumn(ResultSet colMeta)
> throws SQLException {
> Column c = new Column();
> c.setSchemaName(colMeta.getString("TABLE_SCHEM"));
> c.setTableName(colMeta.getString("TABLE_NAME"));
> c.setName(colMeta.getString("COLUMN_NAME")); // detect whether we need delimiters and add them
> It's also worth noting that the testcase drops the offending table and then recreates it. There may be a larger issue where the metadata is cached improperly (could be DB2 specific).
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Assigned: (OPENJPA-1189) Column names do not contain
delimiters when obtained from DatabaseMetaData
Posted by "Dianne Richards (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1189?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dianne Richards reassigned OPENJPA-1189:
----------------------------------------
Assignee: Dianne Richards
> Column names do not contain delimiters when obtained from DatabaseMetaData
> --------------------------------------------------------------------------
>
> Key: OPENJPA-1189
> URL: https://issues.apache.org/jira/browse/OPENJPA-1189
> Project: OpenJPA
> Issue Type: Bug
> Components: jdbc
> Affects Versions: 2.0.0
> Environment: DB2 (could exist on other databases)
> Reporter: Michael Dick
> Assignee: Dianne Richards
>
> The DatabaseMetaData returned by DB2 does not include delimiters for column names which include whitespace. This causes potential duplicate column names if we merge into an existing schema.
> The problem can be illustrated by running the TestManualDelimitedJoinAnnotations test twice sequentially. The first time the test is executed the database will be clean (presumably) and everything will work. Subsequent executions read the existing column information from the database (non delimited) and end up creating a duplicate column.
> Example stacktrace :
> <openjpa-2.0.0-SNAPSHOT-r422266:794075M nonfatal general error> org.apache.openjpa.persistence.PersistenceException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=);entityCs_c id OTHER;<references_spec>, DRIVER=3.50.152 {stmnt 10751758 CREATE TABLE "delim id"."c d" ("entityCs_c id" INTEGER, ENTITYDS_ID INTEGER, entityCs_c id OTHER)} [code=-104, state=42601]
> at org.apache.openjpa.jdbc.meta.MappingTool.record(MappingTool.java:553)
> at org.apache.openjpa.jdbc.meta.MappingTool.record(MappingTool.java:453)
> <snip>
> To resolve the problem we can detect a column / table / schema name which contains whitespace and add the appropriate delimiters (when the name is provided by the database). One place to make this change is in DBDictionary.newColumn()
> protected Column newColumn(ResultSet colMeta)
> throws SQLException {
> Column c = new Column();
> c.setSchemaName(colMeta.getString("TABLE_SCHEM"));
> c.setTableName(colMeta.getString("TABLE_NAME"));
> c.setName(colMeta.getString("COLUMN_NAME")); // detect whether we need delimiters and add them
> It's also worth noting that the testcase drops the offending table and then recreates it. There may be a larger issue where the metadata is cached improperly (could be DB2 specific).
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Work started: (OPENJPA-1189) Column names do not contain
delimiters when obtained from DatabaseMetaData
Posted by "Dianne Richards (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1189?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Work on OPENJPA-1189 started by Dianne Richards.
> Column names do not contain delimiters when obtained from DatabaseMetaData
> --------------------------------------------------------------------------
>
> Key: OPENJPA-1189
> URL: https://issues.apache.org/jira/browse/OPENJPA-1189
> Project: OpenJPA
> Issue Type: Bug
> Components: jdbc
> Affects Versions: 2.0.0
> Environment: DB2 (could exist on other databases)
> Reporter: Michael Dick
> Assignee: Dianne Richards
>
> The DatabaseMetaData returned by DB2 does not include delimiters for column names which include whitespace. This causes potential duplicate column names if we merge into an existing schema.
> The problem can be illustrated by running the TestManualDelimitedJoinAnnotations test twice sequentially. The first time the test is executed the database will be clean (presumably) and everything will work. Subsequent executions read the existing column information from the database (non delimited) and end up creating a duplicate column.
> Example stacktrace :
> <openjpa-2.0.0-SNAPSHOT-r422266:794075M nonfatal general error> org.apache.openjpa.persistence.PersistenceException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=);entityCs_c id OTHER;<references_spec>, DRIVER=3.50.152 {stmnt 10751758 CREATE TABLE "delim id"."c d" ("entityCs_c id" INTEGER, ENTITYDS_ID INTEGER, entityCs_c id OTHER)} [code=-104, state=42601]
> at org.apache.openjpa.jdbc.meta.MappingTool.record(MappingTool.java:553)
> at org.apache.openjpa.jdbc.meta.MappingTool.record(MappingTool.java:453)
> <snip>
> To resolve the problem we can detect a column / table / schema name which contains whitespace and add the appropriate delimiters (when the name is provided by the database). One place to make this change is in DBDictionary.newColumn()
> protected Column newColumn(ResultSet colMeta)
> throws SQLException {
> Column c = new Column();
> c.setSchemaName(colMeta.getString("TABLE_SCHEM"));
> c.setTableName(colMeta.getString("TABLE_NAME"));
> c.setName(colMeta.getString("COLUMN_NAME")); // detect whether we need delimiters and add them
> It's also worth noting that the testcase drops the offending table and then recreates it. There may be a larger issue where the metadata is cached improperly (could be DB2 specific).
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (OPENJPA-1189) Column names do not contain
delimiters when obtained from DatabaseMetaData
Posted by "Dianne Richards (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1189?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dianne Richards updated OPENJPA-1189:
-------------------------------------
Attachment: patch.txt
Attaching patch for review and possibly commit.
This problem occurred when a column name retrieved from the DB metadata was passed as a parameter to the Table.getColumn(String name) to retrieve the Column object. The column name retrieved from the DB does not contain delimiters and this info apparently cannot be obtained. The Table object was created using input metadata from annotations and xml, and might contain delimiters for the Map keys. Therefore, the name might not be found. So, the fix is to delimit the input name if it is initially not found.
Changes were made in the Table getColumn() and containsColumn() methods to take DBDictionary as a parameter, from which the DB-specific delimiter is retrieved. However, some calling methods do not currently have access to the DBDictioinary and pass in null. In this scenario, the double-quote is used as the delimiter. While this is spec-complient, this may not work for some dbs, which is not good. So, I've opened jira OPENJPA-1236 to handle this in the future. I suspect some chaining of changes in method signatures to allow a DBDictionary parameter will fix this.
> Column names do not contain delimiters when obtained from DatabaseMetaData
> --------------------------------------------------------------------------
>
> Key: OPENJPA-1189
> URL: https://issues.apache.org/jira/browse/OPENJPA-1189
> Project: OpenJPA
> Issue Type: Bug
> Components: jdbc
> Affects Versions: 2.0.0
> Environment: DB2 (could exist on other databases)
> Reporter: Michael Dick
> Assignee: Dianne Richards
> Attachments: patch.txt
>
>
> The DatabaseMetaData returned by DB2 does not include delimiters for column names which include whitespace. This causes potential duplicate column names if we merge into an existing schema.
> The problem can be illustrated by running the TestManualDelimitedJoinAnnotations test twice sequentially. The first time the test is executed the database will be clean (presumably) and everything will work. Subsequent executions read the existing column information from the database (non delimited) and end up creating a duplicate column.
> Example stacktrace :
> <openjpa-2.0.0-SNAPSHOT-r422266:794075M nonfatal general error> org.apache.openjpa.persistence.PersistenceException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=);entityCs_c id OTHER;<references_spec>, DRIVER=3.50.152 {stmnt 10751758 CREATE TABLE "delim id"."c d" ("entityCs_c id" INTEGER, ENTITYDS_ID INTEGER, entityCs_c id OTHER)} [code=-104, state=42601]
> at org.apache.openjpa.jdbc.meta.MappingTool.record(MappingTool.java:553)
> at org.apache.openjpa.jdbc.meta.MappingTool.record(MappingTool.java:453)
> <snip>
> To resolve the problem we can detect a column / table / schema name which contains whitespace and add the appropriate delimiters (when the name is provided by the database). One place to make this change is in DBDictionary.newColumn()
> protected Column newColumn(ResultSet colMeta)
> throws SQLException {
> Column c = new Column();
> c.setSchemaName(colMeta.getString("TABLE_SCHEM"));
> c.setTableName(colMeta.getString("TABLE_NAME"));
> c.setName(colMeta.getString("COLUMN_NAME")); // detect whether we need delimiters and add them
> It's also worth noting that the testcase drops the offending table and then recreates it. There may be a larger issue where the metadata is cached improperly (could be DB2 specific).
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (OPENJPA-1189) Column names do not contain
delimiters when obtained from DatabaseMetaData
Posted by "Donald Woods (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1189?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Donald Woods updated OPENJPA-1189:
----------------------------------
Affects Version/s: (was: 2.0.0)
2.0.0-beta
Fix Version/s: (was: 2.0.0)
2.0.0-beta
show as being fixed in 2.0.0-beta
> Column names do not contain delimiters when obtained from DatabaseMetaData
> --------------------------------------------------------------------------
>
> Key: OPENJPA-1189
> URL: https://issues.apache.org/jira/browse/OPENJPA-1189
> Project: OpenJPA
> Issue Type: Bug
> Components: jdbc
> Affects Versions: 2.0.0-beta
> Environment: DB2 (could exist on other databases)
> Reporter: Michael Dick
> Assignee: Jeremy Bauer
> Fix For: 2.0.0-beta
>
> Attachments: patch.txt
>
>
> The DatabaseMetaData returned by DB2 does not include delimiters for column names which include whitespace. This causes potential duplicate column names if we merge into an existing schema.
> The problem can be illustrated by running the TestManualDelimitedJoinAnnotations test twice sequentially. The first time the test is executed the database will be clean (presumably) and everything will work. Subsequent executions read the existing column information from the database (non delimited) and end up creating a duplicate column.
> Example stacktrace :
> <openjpa-2.0.0-SNAPSHOT-r422266:794075M nonfatal general error> org.apache.openjpa.persistence.PersistenceException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=);entityCs_c id OTHER;<references_spec>, DRIVER=3.50.152 {stmnt 10751758 CREATE TABLE "delim id"."c d" ("entityCs_c id" INTEGER, ENTITYDS_ID INTEGER, entityCs_c id OTHER)} [code=-104, state=42601]
> at org.apache.openjpa.jdbc.meta.MappingTool.record(MappingTool.java:553)
> at org.apache.openjpa.jdbc.meta.MappingTool.record(MappingTool.java:453)
> <snip>
> To resolve the problem we can detect a column / table / schema name which contains whitespace and add the appropriate delimiters (when the name is provided by the database). One place to make this change is in DBDictionary.newColumn()
> protected Column newColumn(ResultSet colMeta)
> throws SQLException {
> Column c = new Column();
> c.setSchemaName(colMeta.getString("TABLE_SCHEM"));
> c.setTableName(colMeta.getString("TABLE_NAME"));
> c.setName(colMeta.getString("COLUMN_NAME")); // detect whether we need delimiters and add them
> It's also worth noting that the testcase drops the offending table and then recreates it. There may be a larger issue where the metadata is cached improperly (could be DB2 specific).
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Resolved: (OPENJPA-1189) Column names do not contain
delimiters when obtained from DatabaseMetaData
Posted by "Jeremy Bauer (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1189?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Jeremy Bauer resolved OPENJPA-1189.
-----------------------------------
Resolution: Fixed
Fix Version/s: 2.0.0
Assignee: Jeremy Bauer (was: Dianne Richards)
This issue was resolved under OPENJPA-1115. The OpenJPA naming utility detects when identifiers returned from dbmd require delimiters.
> Column names do not contain delimiters when obtained from DatabaseMetaData
> --------------------------------------------------------------------------
>
> Key: OPENJPA-1189
> URL: https://issues.apache.org/jira/browse/OPENJPA-1189
> Project: OpenJPA
> Issue Type: Bug
> Components: jdbc
> Affects Versions: 2.0.0
> Environment: DB2 (could exist on other databases)
> Reporter: Michael Dick
> Assignee: Jeremy Bauer
> Fix For: 2.0.0
>
> Attachments: patch.txt
>
>
> The DatabaseMetaData returned by DB2 does not include delimiters for column names which include whitespace. This causes potential duplicate column names if we merge into an existing schema.
> The problem can be illustrated by running the TestManualDelimitedJoinAnnotations test twice sequentially. The first time the test is executed the database will be clean (presumably) and everything will work. Subsequent executions read the existing column information from the database (non delimited) and end up creating a duplicate column.
> Example stacktrace :
> <openjpa-2.0.0-SNAPSHOT-r422266:794075M nonfatal general error> org.apache.openjpa.persistence.PersistenceException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=);entityCs_c id OTHER;<references_spec>, DRIVER=3.50.152 {stmnt 10751758 CREATE TABLE "delim id"."c d" ("entityCs_c id" INTEGER, ENTITYDS_ID INTEGER, entityCs_c id OTHER)} [code=-104, state=42601]
> at org.apache.openjpa.jdbc.meta.MappingTool.record(MappingTool.java:553)
> at org.apache.openjpa.jdbc.meta.MappingTool.record(MappingTool.java:453)
> <snip>
> To resolve the problem we can detect a column / table / schema name which contains whitespace and add the appropriate delimiters (when the name is provided by the database). One place to make this change is in DBDictionary.newColumn()
> protected Column newColumn(ResultSet colMeta)
> throws SQLException {
> Column c = new Column();
> c.setSchemaName(colMeta.getString("TABLE_SCHEM"));
> c.setTableName(colMeta.getString("TABLE_NAME"));
> c.setName(colMeta.getString("COLUMN_NAME")); // detect whether we need delimiters and add them
> It's also worth noting that the testcase drops the offending table and then recreates it. There may be a larger issue where the metadata is cached improperly (could be DB2 specific).
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.