You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by Michael Simons <mi...@optitool.de> on 2009/06/22 15:48:17 UTC

Problem with @TableGenerator [OpenJPA 1.2.2]

Hello,

Here's my annotation:
    @TableGenerator(name = "pkGenSessionConfig", table = "jdo_keygen", pkColumnName =
"table_name", valueColumnName = "last_used_id", pkColumnValue = "session_config", allocationSize
= 10)
    @Id
    @GeneratedValue(strategy=GenerationType.TABLE, generator="pkGenSessionConfig")
    @Column(name="session_config_id", unique=true, nullable=false)
    private int id;

And here's the according SQL generated by OpenJPA:
Unknown column 'TABLE_NAME0' in 'where clause' {prepstmnt 32845046 SELECT LAST_USED_ID FROM
jdo_keygen WHERE TABLE_NAME0 = ? FOR UPDATE [params=(String) session_config]} [code=1054,
state=42S22]

Question: Where does OpenJPA take "TABLE_NAME0" from while pkColumnValue="table_name"

Kind Regards,
Michael

Re: Problem with @TableGenerator [OpenJPA 1.2.2]

Posted by Michael Simons <mi...@optitool.de>.
Hello Fay Wang,

Thanks a lot for your tips. However we solved it by a custom-built work-around, that you cannot
generalize.
I opened JiRA Issue http://issues.apache.org/jira/browse/OPENJPA-1144.

Regards,
Michael

Fay Wang schrieb:
> As a workaround so that you can proceed, could you alter your jdo_keygen table by changing the column table_name to table_name0, meanwhile you might want to open a jira issue for this problem. 
> 
> Regards,
> Fay


Re: Problem with @TableGenerator [OpenJPA 1.2.2]

Posted by Fay Wang <fy...@yahoo.com>.
As a workaround so that you can proceed, could you alter your jdo_keygen table by changing the column table_name to table_name0, meanwhile you might want to open a jira issue for this problem. 

Regards,
Fay



----- Original Message ----
From: Michael Simons <mi...@optitool.de>
To: users@openjpa.apache.org
Sent: Monday, June 22, 2009 9:58:33 AM
Subject: Re: Problem with @TableGenerator [OpenJPA 1.2.2]

Hi Fay Wang,

thanks for your reply. As you probably already guessed beacuse of the name "jdo_keygen", we are
porting an application that was based on JDO to JPA. So the table already exists and we must not
generate it. As the table already exists with a column "table_name" this name is valid as a
column name for these databases (we tested with MS SQL Server and MySQL, both failed for the
same reason).

Can't I tell JPA to take the pkColumnName as is when accessing the database table?
Can we influence what's assumed a valid column name?
Is there any other opportunity to come around this problem. I thought about dropping the whole
@GeneratedValue thing and doing the key generating on my own. How do you think about that idea?

kind regards,
Michael

Fay Wang schrieb:
> Hi,
>    I used your annotation and my test case runs fine.
>    Given your pkColumnName = "table_name", openjpa internally makes this column name valid for a given database.  This code is in ValueTableJDBCSeq:
> 
>    protected Column addPrimaryKeyColumn(Table table) {
>        DBDictionary dict = getConfiguration().getDBDictionaryInstance();
>        Column pkColumn = table.addColumn(dict.getValidColumnName
>            (getPrimaryKeyColumn(), table));
>        ... 
> 
>    }
> 
> Note that the return value from dict.getValidColumnName is table_name0.
> 
> From my jdbc log, the table jdo_keygen is created as:
> 
> CREATE TABLE jdo_keygen (TABLE_NAME0 VARCHAR(254) NOT NULL, LAST_USED_ID BIGINT, PRIMARY KEY (TABLE_NAME0))
> 
> As a result, there is no problem executing
> 
> SELECT LAST_USED_ID FROM jdo_keygen WHERE TABLE_NAME0 = ? FOR UPDATE 
> 
> I am using openjpa trunk for this exercise. You might want to turn on jdbc log to see how create jdo_keygen is created.
> 
> Hope this helps.
> Fay
> 
> 
> 
> ----- Original Message ----
> From: Michael Simons <mi...@optitool.de>
> To: users@openjpa.apache.org
> Sent: Monday, June 22, 2009 6:48:17 AM
> Subject: Problem with @TableGenerator [OpenJPA 1.2.2]
> 
> Hello,
> 
> Here's my annotation:
>    @TableGenerator(name = "pkGenSessionConfig", table = "jdo_keygen", pkColumnName =
> "table_name", valueColumnName = "last_used_id", pkColumnValue = "session_config", allocationSize
> = 10)
>    @Id
>    @GeneratedValue(strategy=GenerationType.TABLE, generator="pkGenSessionConfig")
>    @Column(name="session_config_id", unique=true, nullable=false)
>    private int id;
> 
> And here's the according SQL generated by OpenJPA:
> Unknown column 'TABLE_NAME0' in 'where clause' {prepstmnt 32845046 SELECT LAST_USED_ID FROM
> jdo_keygen WHERE TABLE_NAME0 = ? FOR UPDATE [params=(String) session_config]} [code=1054,
> state=42S22]
> 
> Question: Where does OpenJPA take "TABLE_NAME0" from while pkColumnValue="table_name"
> 
> Kind Regards,
> Michael
> 
> 
> 
>      
> 



      

Re: Problem with @TableGenerator [OpenJPA 1.2.2]

Posted by Michael Simons <mi...@optitool.de>.
Hi Fay Wang,

thanks for your reply. As you probably already guessed beacuse of the name "jdo_keygen", we are
porting an application that was based on JDO to JPA. So the table already exists and we must not
generate it. As the table already exists with a column "table_name" this name is valid as a
column name for these databases (we tested with MS SQL Server and MySQL, both failed for the
same reason).

Can't I tell JPA to take the pkColumnName as is when accessing the database table?
Can we influence what's assumed a valid column name?
Is there any other opportunity to come around this problem. I thought about dropping the whole
@GeneratedValue thing and doing the key generating on my own. How do you think about that idea?

kind regards,
Michael

Fay Wang schrieb:
> Hi,
>     I used your annotation and my test case runs fine.
>     Given your pkColumnName = "table_name", openjpa internally makes this column name valid for a given database.  This code is in ValueTableJDBCSeq:
> 
>     protected Column addPrimaryKeyColumn(Table table) {
>         DBDictionary dict = getConfiguration().getDBDictionaryInstance();
>         Column pkColumn = table.addColumn(dict.getValidColumnName
>             (getPrimaryKeyColumn(), table));
>         ... 
> 
>     }
> 
> Note that the return value from dict.getValidColumnName is table_name0.
> 
> From my jdbc log, the table jdo_keygen is created as:
> 
> CREATE TABLE jdo_keygen (TABLE_NAME0 VARCHAR(254) NOT NULL, LAST_USED_ID BIGINT, PRIMARY KEY (TABLE_NAME0))
> 
> As a result, there is no problem executing
> 
> SELECT LAST_USED_ID FROM jdo_keygen WHERE TABLE_NAME0 = ? FOR UPDATE 
> 
> I am using openjpa trunk for this exercise. You might want to turn on jdbc log to see how create jdo_keygen is created.
> 
> Hope this helps.
> Fay
> 
> 
> 
> ----- Original Message ----
> From: Michael Simons <mi...@optitool.de>
> To: users@openjpa.apache.org
> Sent: Monday, June 22, 2009 6:48:17 AM
> Subject: Problem with @TableGenerator [OpenJPA 1.2.2]
> 
> Hello,
> 
> Here's my annotation:
>     @TableGenerator(name = "pkGenSessionConfig", table = "jdo_keygen", pkColumnName =
> "table_name", valueColumnName = "last_used_id", pkColumnValue = "session_config", allocationSize
> = 10)
>     @Id
>     @GeneratedValue(strategy=GenerationType.TABLE, generator="pkGenSessionConfig")
>     @Column(name="session_config_id", unique=true, nullable=false)
>     private int id;
> 
> And here's the according SQL generated by OpenJPA:
> Unknown column 'TABLE_NAME0' in 'where clause' {prepstmnt 32845046 SELECT LAST_USED_ID FROM
> jdo_keygen WHERE TABLE_NAME0 = ? FOR UPDATE [params=(String) session_config]} [code=1054,
> state=42S22]
> 
> Question: Where does OpenJPA take "TABLE_NAME0" from while pkColumnValue="table_name"
> 
> Kind Regards,
> Michael
> 
> 
> 
>       
> 

Re: Problem with @TableGenerator [OpenJPA 1.2.2]

Posted by Fay Wang <fy...@yahoo.com>.
Hi,
    I used your annotation and my test case runs fine.
    Given your pkColumnName = "table_name", openjpa internally makes this column name valid for a given database.  This code is in ValueTableJDBCSeq:

    protected Column addPrimaryKeyColumn(Table table) {
        DBDictionary dict = getConfiguration().getDBDictionaryInstance();
        Column pkColumn = table.addColumn(dict.getValidColumnName
            (getPrimaryKeyColumn(), table));
        ... 

    }

Note that the return value from dict.getValidColumnName is table_name0.

From my jdbc log, the table jdo_keygen is created as:

CREATE TABLE jdo_keygen (TABLE_NAME0 VARCHAR(254) NOT NULL, LAST_USED_ID BIGINT, PRIMARY KEY (TABLE_NAME0))

As a result, there is no problem executing

SELECT LAST_USED_ID FROM jdo_keygen WHERE TABLE_NAME0 = ? FOR UPDATE 

I am using openjpa trunk for this exercise. You might want to turn on jdbc log to see how create jdo_keygen is created.

Hope this helps.
Fay



----- Original Message ----
From: Michael Simons <mi...@optitool.de>
To: users@openjpa.apache.org
Sent: Monday, June 22, 2009 6:48:17 AM
Subject: Problem with @TableGenerator [OpenJPA 1.2.2]

Hello,

Here's my annotation:
    @TableGenerator(name = "pkGenSessionConfig", table = "jdo_keygen", pkColumnName =
"table_name", valueColumnName = "last_used_id", pkColumnValue = "session_config", allocationSize
= 10)
    @Id
    @GeneratedValue(strategy=GenerationType.TABLE, generator="pkGenSessionConfig")
    @Column(name="session_config_id", unique=true, nullable=false)
    private int id;

And here's the according SQL generated by OpenJPA:
Unknown column 'TABLE_NAME0' in 'where clause' {prepstmnt 32845046 SELECT LAST_USED_ID FROM
jdo_keygen WHERE TABLE_NAME0 = ? FOR UPDATE [params=(String) session_config]} [code=1054,
state=42S22]

Question: Where does OpenJPA take "TABLE_NAME0" from while pkColumnValue="table_name"

Kind Regards,
Michael