You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by Adam Hardy <ad...@cyberspaceroad.org> on 2008/01/15 12:55:12 UTC
using a KEY_SEQUENCE table
Doing a project with no dependency on any one database vendor, I am using
KEY_SEQUENCE tables to manage my primary keys. This was working fine until I
introduced a schema name. Now I get the following exception:
org.apache.openjpa.lib.jdbc.ReportingSQLException: Table DEV not found; SQL
statement:
SELECT LAST_KEY FROM DEV.DEV.KEY_SEQUENCE WHERE TABLE_SEQ = ? FOR UPDATE
[42S02-64] {SELECT LAST_KEY FROM DEV.DEV.KEY_SEQUENCE WHERE TABLE_SEQ = ? FOR
UPDATE} [code=42102, state=42S02]
My schema name is DEV and OpenJPA has prefixed it onto the table name twice! I
configure the schema name via the database connection parameters, e.g.
openjpa.jdbc.Schema=DEV
This is my KEY_SEQUENCE config if it helps:
<table-generator name="codeKeySequence" table="KEY_SEQUENCE"
pk-column-name="TABLE_SEQ" value-column-name="LAST_KEY"
pk-column-value="CODE">
</table-generator>
This is happening in both OpenJPA v 1.0.1 and 1.1.0. I'll have to look into this
further now to see if it is a problem just with the
org.apache.openjpa.jdbc.sql.H2Dictionary that I am using.
Does anyone have key sequence tables in a named schema working in any database?
Thanks
Adam
Re: using TableGenerator for 1ary keys
Posted by Adam Hardy <ad...@cyberspaceroad.org>.
Somebody beat me to Jira and has filed an issue there regarding this.
I tried this in Derby and it happens too, so it's not database-specific.
Adam Hardy on 16/01/08 14:12, wrote:
> I use TableGenerator mapping for primary keys on all my entity mappings.
> The XML for the table-generator is in the thread below.
>
> When I set up my app to use H2, the exact problem is as given in the
> exception: the SQL statement generated by OpenJPA for H2 refers to the
> table in the schema as
>
> DEV.DEV.KEY_SEQUENCE
>
> Obviously OpenJPA has prefixed the table name with the schema name twice
> instead of once.
>
> This is only when set up to use H2. When running against MySQL (which
> has no notion of schemas), there is no problem.
>
> I guess an easy further test would be to run the test against a 3rd
> database which does implement schemas. I have already tried Hypersonic -
> but there are other issues with that which prevent testing. I shall try
> derby or postgres soon. Theoretically I may have configured the schema
> in 2 different places in the config, but that situation should be
> catered for, methinks.
>
> Regards
> Adam
>
> David Beer on 15/01/08 22:22, wrote:
>> Can you give at little more detail as to the exact problem with H2 or
>> how you managed to solve the issue.
>>
>> Adam Hardy wrote:
>>> Realised the term is TableGenerator, not KEY_SEQUENCE, for the issue
>>> that is causing problems here.
>>>
>>> I narrowed it down to H2. With Mysql, there is no problem.
>>>
>>> Adam Hardy on 15/01/08 11:55, wrote:
>>>> Doing a project with no dependency on any one database vendor, I am
>>>> using KEY_SEQUENCE tables to manage my primary keys. This was
>>>> working fine until I introduced a schema name. Now I get the
>>>> following exception:
>>>>
>>>> org.apache.openjpa.lib.jdbc.ReportingSQLException: Table DEV not
>>>> found; SQL statement:
>>>> SELECT LAST_KEY FROM DEV.DEV.KEY_SEQUENCE WHERE TABLE_SEQ = ? FOR
>>>> UPDATE [42S02-64] {SELECT LAST_KEY FROM DEV.DEV.KEY_SEQUENCE WHERE
>>>> TABLE_SEQ = ? FOR UPDATE} [code=42102, state=42S02]
>>>>
>>>>
>>>> My schema name is DEV and OpenJPA has prefixed it onto the table
>>>> name twice! I configure the schema name via the database connection
>>>> parameters, e.g.
>>>>
>>>> openjpa.jdbc.Schema=DEV
>>>>
>>>> This is my KEY_SEQUENCE config if it helps:
>>>>
>>>> <table-generator name="codeKeySequence" table="KEY_SEQUENCE"
>>>> pk-column-name="TABLE_SEQ" value-column-name="LAST_KEY"
>>>> pk-column-value="CODE">
>>>> </table-generator>
>>>>
>>>> This is happening in both OpenJPA v 1.0.1 and 1.1.0. I'll have to
>>>> look into this further now to see if it is a problem just with the
>>>> org.apache.openjpa.jdbc.sql.H2Dictionary that I am using.
>>>>
>>>> Does anyone have key sequence tables in a named schema working in
>>>> any database?
>
>
Re: using TableGenerator for 1ary keys
Posted by Adam Hardy <ad...@cyberspaceroad.org>.
I use TableGenerator mapping for primary keys on all my entity mappings. The XML
for the table-generator is in the thread below.
When I set up my app to use H2, the exact problem is as given in the exception:
the SQL statement generated by OpenJPA for H2 refers to the table in the schema as
DEV.DEV.KEY_SEQUENCE
Obviously OpenJPA has prefixed the table name with the schema name twice instead
of once.
This is only when set up to use H2. When running against MySQL (which has no
notion of schemas), there is no problem.
I guess an easy further test would be to run the test against a 3rd database
which does implement schemas. I have already tried Hypersonic - but there are
other issues with that which prevent testing. I shall try derby or postgres
soon. Theoretically I may have configured the schema in 2 different places in
the config, but that situation should be catered for, methinks.
Regards
Adam
David Beer on 15/01/08 22:22, wrote:
> Can you give at little more detail as to the exact problem with H2 or
> how you managed to solve the issue.
>
> Adam Hardy wrote:
>> Realised the term is TableGenerator, not KEY_SEQUENCE, for the issue
>> that is causing problems here.
>>
>> I narrowed it down to H2. With Mysql, there is no problem.
>>
>> Adam Hardy on 15/01/08 11:55, wrote:
>>> Doing a project with no dependency on any one database vendor, I am
>>> using KEY_SEQUENCE tables to manage my primary keys. This was working
>>> fine until I introduced a schema name. Now I get the following
>>> exception:
>>>
>>> org.apache.openjpa.lib.jdbc.ReportingSQLException: Table DEV not
>>> found; SQL statement:
>>> SELECT LAST_KEY FROM DEV.DEV.KEY_SEQUENCE WHERE TABLE_SEQ = ? FOR
>>> UPDATE [42S02-64] {SELECT LAST_KEY FROM DEV.DEV.KEY_SEQUENCE WHERE
>>> TABLE_SEQ = ? FOR UPDATE} [code=42102, state=42S02]
>>>
>>>
>>> My schema name is DEV and OpenJPA has prefixed it onto the table name
>>> twice! I configure the schema name via the database connection
>>> parameters, e.g.
>>>
>>> openjpa.jdbc.Schema=DEV
>>>
>>> This is my KEY_SEQUENCE config if it helps:
>>>
>>> <table-generator name="codeKeySequence" table="KEY_SEQUENCE"
>>> pk-column-name="TABLE_SEQ" value-column-name="LAST_KEY"
>>> pk-column-value="CODE">
>>> </table-generator>
>>>
>>> This is happening in both OpenJPA v 1.0.1 and 1.1.0. I'll have to
>>> look into this further now to see if it is a problem just with the
>>> org.apache.openjpa.jdbc.sql.H2Dictionary that I am using.
>>>
>>> Does anyone have key sequence tables in a named schema working in any
>>> database?
Re: using TableGenerator for 1ary keys
Posted by David Beer <da...@btinternet.com>.
Hi Adam
Can you give at little more detail as to the exact problem with H2 or
how you managed to solve the issue.
Thanks
David
Adam Hardy wrote:
> Realised the term is TableGenerator, not KEY_SEQUENCE, for the issue
> that is causing problems here.
>
> I narrowed it down to H2. With Mysql, there is no problem.
>
> Adam Hardy on 15/01/08 11:55, wrote:
>> Doing a project with no dependency on any one database vendor, I am
>> using KEY_SEQUENCE tables to manage my primary keys. This was working
>> fine until I introduced a schema name. Now I get the following
>> exception:
>>
>> org.apache.openjpa.lib.jdbc.ReportingSQLException: Table DEV not
>> found; SQL statement:
>> SELECT LAST_KEY FROM DEV.DEV.KEY_SEQUENCE WHERE TABLE_SEQ = ? FOR
>> UPDATE [42S02-64] {SELECT LAST_KEY FROM DEV.DEV.KEY_SEQUENCE WHERE
>> TABLE_SEQ = ? FOR UPDATE} [code=42102, state=42S02]
>>
>>
>> My schema name is DEV and OpenJPA has prefixed it onto the table name
>> twice! I configure the schema name via the database connection
>> parameters, e.g.
>>
>> openjpa.jdbc.Schema=DEV
>>
>> This is my KEY_SEQUENCE config if it helps:
>>
>> <table-generator name="codeKeySequence" table="KEY_SEQUENCE"
>> pk-column-name="TABLE_SEQ" value-column-name="LAST_KEY"
>> pk-column-value="CODE">
>> </table-generator>
>>
>> This is happening in both OpenJPA v 1.0.1 and 1.1.0. I'll have to
>> look into this further now to see if it is a problem just with the
>> org.apache.openjpa.jdbc.sql.H2Dictionary that I am using.
>>
>> Does anyone have key sequence tables in a named schema working in any
>> database?
>>
>>
>> Thanks
>> Adam
>>
>
>
Re: using TableGenerator for 1ary keys (was: using a KEY_SEQUENCE
table)
Posted by Adam Hardy <ad...@cyberspaceroad.org>.
Realised the term is TableGenerator, not KEY_SEQUENCE, for the issue that is
causing problems here.
I narrowed it down to H2. With Mysql, there is no problem.
Adam Hardy on 15/01/08 11:55, wrote:
> Doing a project with no dependency on any one database vendor, I am
> using KEY_SEQUENCE tables to manage my primary keys. This was working
> fine until I introduced a schema name. Now I get the following exception:
>
> org.apache.openjpa.lib.jdbc.ReportingSQLException: Table DEV not found;
> SQL statement:
> SELECT LAST_KEY FROM DEV.DEV.KEY_SEQUENCE WHERE TABLE_SEQ = ? FOR UPDATE
> [42S02-64] {SELECT LAST_KEY FROM DEV.DEV.KEY_SEQUENCE WHERE TABLE_SEQ =
> ? FOR UPDATE} [code=42102, state=42S02]
>
>
> My schema name is DEV and OpenJPA has prefixed it onto the table name
> twice! I configure the schema name via the database connection
> parameters, e.g.
>
> openjpa.jdbc.Schema=DEV
>
> This is my KEY_SEQUENCE config if it helps:
>
> <table-generator name="codeKeySequence" table="KEY_SEQUENCE"
> pk-column-name="TABLE_SEQ" value-column-name="LAST_KEY"
> pk-column-value="CODE">
> </table-generator>
>
> This is happening in both OpenJPA v 1.0.1 and 1.1.0. I'll have to look
> into this further now to see if it is a problem just with the
> org.apache.openjpa.jdbc.sql.H2Dictionary that I am using.
>
> Does anyone have key sequence tables in a named schema working in any
> database?
>
>
> Thanks
> Adam
>