Home » Modeling » EMF » invalid union statements with DB2
| invalid union statements with DB2 [message #536844] |
Mon, 31 May 2010 09:09  |
 Henzler Peter
Messages: 19
Registered: July 2009 |
Junior Member |
|
|
Hi
I want to use teneo / Hibernate with a DB2 database.
While our application works perfect with PostgreSQL and MSSQL we have the following problem running teneo/hibernate with DB2:
select count(this_."id") as y0_ from ( select id, icon, description, name, active, sorthint, basiccode_parent_id, 1 as clazz_ from "code" union all select id, icon, description, name, nullif(0,0) as active, sorthint, basiccode_parent_id, 2 as clazz_ from "category" ) this_ where this_."id"=?
On execution of the above statement I get the following error:
[IBM][CLI Driver][DB2/NT] SQL0206N "ID" ist in dem verwendeten Kontext ungültig. SQLSTATE=42703
It says that 'ID' is not valid in this context.
I found that the reason for this message is that the select statement of the "code" table the column id is not escaped.
When I change the statement to
select count(this_."id") as y0_ from ( select "id", icon, description, name, active, sorthint, basiccode_parent_id, 1 as clazz_ from "code" union all select id, icon, description, name, nullif(0,0) as active, sorthint, basiccode_parent_id, 2 as clazz_ from "category" ) this_ where this_."id"=?
the error message is
[IBM][CLI Driver][DB2/NT] SQL0206N "ICON" ist in dem verwendeten Kontext ungültig. SQLSTATE=42703
When I put all the column names in quotes like this the statement can be executed without errors:
select count(this_."id") as y0_ from ( select "id", "icon", "description", "name", "active", "sorthint", "basiccode_parent_id", 1 as clazz_ from "code" union all select "id", "icon", "description", "name", nullif(0,0) as active, "sorthint", "basiccode_parent_id", 2 as clazz_ from "category" ) this_ where this_."id"=?
My question is:
Is this a problem of hibernate or is there an option in teneo that tells hibernate to put all column names in quotes?
Thank you for your help.
Best regards
Peter Henzler
[Updated on: Mon, 31 May 2010 09:43] Report message to a moderator |
|
|
| Re: invalid join statements with DB2 [message #536850 is a reply to message #536844] |
Mon, 31 May 2010 09:28  |
Martin Taal
Messages: 5468
Registered: July 2009 |
Senior Member |
|
|
Hi Peter,
Teneo will automatically quote all column/table names in the hibernate mapping (based on an option, see below). This is
the safest approach because it automatically handles reserved names.
However, I think that with quoting all hibernate generated sql queries should use the quotes also. I am not sure why
hibernate does not quote the columns in the inner select as it seems to qoute the "id" in the first select clause. It
seems to be a hibernate issue.
Maybe you can disable quoting all together by setting the qoute character to an empty string. See these topics in the wiki:
See the teneo.naming.sql_name_escape_character option here:
http://wiki.eclipse.org/Teneo/Hibernate/Configuration_Option s
http://wiki.eclipse.org/Teneo/Hibernate/EMF_Hibernate_Integr ation_Details#Escaping_of_table_and_column_names
gr. Martin
Henzler Peter wrote:
> Hi
>
> I want to use teneo / Hibernate with a DB2 database.
>
> While our application works perfect with PostgreSQL and MSSQL we have
> the following problem running teneo/hibernate with DB2:
>
> select count(this_."id") as y0_ from ( select id, icon, description,
> name, active, sorthint, basiccode_parent_id, 1 as clazz_ from "code"
> union all select id, icon, description, name, nullif(0,0) as active,
> sorthint, basiccode_parent_id, 2 as clazz_ from "category" ) this_ where
> this_."id"=?
>
> On execution of the above statement I get the following error:
>
> [IBM][CLI Driver][DB2/NT] SQL0206N "ID" ist in dem verwendeten Kontext
> ungültig. SQLSTATE=42703
>
> It says that 'ID' is not valid in this context.
>
> I found that the reason for this message is that the select statement of
> the "code" table the column id is not escaped.
>
> When I change the statement to
> select count(this_."id") as y0_ from ( select "id", icon, description,
> name, active, sorthint, basiccode_parent_id, 1 as clazz_ from "code"
> union all select id, icon, description, name, nullif(0,0) as active,
> sorthint, basiccode_parent_id, 2 as clazz_ from "category" ) this_ where
> this_."id"=?
>
> the error message is
> [IBM][CLI Driver][DB2/NT] SQL0206N "ICON" ist in dem verwendeten
> Kontext ungültig. SQLSTATE=42703
>
> When I put all the column names in quotes like this the statement can be
> executed without errors:
>
> select count(this_."id") as y0_ from ( select "id", "icon",
> "description", "name", "active", "sorthint", "basiccode_parent_id", 1 as
> clazz_ from "code" union all select "id", "icon", "description", "name",
> nullif(0,0) as active, "sorthint", "basiccode_parent_id", 2 as clazz_
> from "category" ) this_ where this_."id"=?
>
> My question is:
> Is this a problem of hibernate or is there an option in teneo that tells
> hibernate to put all column names in quotes?
>
> Thank you for your help.
>
> Best regards
> Peter Henzler
>
>
>
>
>
--
With Regards, Martin Taal
Springsite/Elver.org
Office: Hardwareweg 4, 3821 BV Amersfoort
Postal: Nassaulaan 7, 3941 EC Doorn
The Netherlands
Cell: +31 (0)6 288 48 943
Tel: +31 (0)84 420 2397
Fax: +31 (0)84 225 9307
Mail: mtaal@springsite.com - mtaal@elver.org
Web: www.springsite.com - www.elver.org
|
|
|
Goto Forum:
Current Time: Fri Apr 26 17:10:55 GMT 2024
Powered by FUDForum. Page generated in 0.02302 seconds |
] 
Search
Help
Register
Login
Home
