Eclipse Community Forums: EMF "Technology" (Ecore Tools, EMFatic, etc)

Help

Home

Home » Modeling » EMF "Technology" (Ecore Tools, EMFatic, etc) » [TENEO] using createSQLQuery

Show: Today's Messages :: Show Polls :: Message Navigator

[TENEO] using createSQLQuery [message #102505]

Tue, 13 November 2007 20:15

Will Horn

Messages: 265
Registered: July 2009

Senior Member

I have a teneo annotated ecore model and I have been able to
successfully make HQL queries such as:

session.createQuery("from GeneralCodeItem")

However, if I try to use a SQL query such as:

session.createSQLQuery("select value, description, short_description
from general_code_item").addEntity("GeneralCodeItem")

I get
64606777 [main] INFO org.hibernate.type.StringType - could not read
column value from result set: "VALUE"; Invalid column name

After debugging, I've determined the source of the problem in an
additional set of quotes. The result set has a column VALUE, but
hibernate is looking for "VALUE". When I look in the mapping file, I
see backticks in the column name which I presume is the source of the
extra quotes:

<id name="value" type="java.lang.String">
<column not-null="true" unique="false" name="`VALUE`"/>
</id>

All my column names have backticks. What is the reason for this and is
there anything I can do about it? I have the following workaround, but
it is very verbose and defeats the purpose of having the teneo mappings:

session.createSQLQuery("select {gc}.value {gc.value}, {gc}.description
{gc.description}, {gc}.short_description {gc.shortDescription} from
general_code_item {gc}").addEntity("gc", "GeneralCodeItem")

Thanks!
Will

Report message to a moderator

Re: [TENEO] using createSQLQuery [message #102546 is a reply to message #102505]

Tue, 13 November 2007 21:52

Martin Taal

Messages: 5468
Registered: July 2009

Senior Member

Hi Will,
The mapping has backticks because then hibernate will quote the names when issueing real sql
statements. This allows for example column names or table names which are sql keywords (for example
index).
Can you try a backticked sql query, something like this:
session.createSQLQuery("select `VALUE` from general_code_item").addEntity("GeneralCodeItem")

btw, for my curiosity why don't you use a hql? Because then afaics this issue does not occur.

gr. Martin

Will Horn wrote:
> I have a teneo annotated ecore model and I have been able to
> successfully make HQL queries such as:
>
> session.createQuery("from GeneralCodeItem")
>
> However, if I try to use a SQL query such as:
>
> session.createSQLQuery("select value, description, short_description
> from general_code_item").addEntity("GeneralCodeItem")
>
> I get
> 64606777 [main] INFO org.hibernate.type.StringType - could not read
> column value from result set: "VALUE"; Invalid column name
>
> After debugging, I've determined the source of the problem in an
> additional set of quotes. The result set has a column VALUE, but
> hibernate is looking for "VALUE". When I look in the mapping file, I
> see backticks in the column name which I presume is the source of the
> extra quotes:
>
> <id name="value" type="java.lang.String">
> <column not-null="true" unique="false" name="`VALUE`"/>
> </id>
>
> All my column names have backticks. What is the reason for this and is
> there anything I can do about it? I have the following workaround, but
> it is very verbose and defeats the purpose of having the teneo mappings:
>
> session.createSQLQuery("select {gc}.value {gc.value}, {gc}.description
> {gc.description}, {gc}.short_description {gc.shortDescription} from
> general_code_item {gc}").addEntity("gc", "GeneralCodeItem")
>
> Thanks!
> Will

--

With Regards, Martin Taal

Springsite/Elver.org
Office: Hardwareweg 4, 3821 BV Amersfoort
Postal: Nassaulaan 7, 3941 EC Doorn
The Netherlands
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

Report message to a moderator

Re: [TENEO] using createSQLQuery [message #102555 is a reply to message #102546]

Tue, 13 November 2007 22:10

Will Horn

Messages: 265
Registered: July 2009

Senior Member

Thanks for the reply Martin.

When I use backticks, I get an "Invalid character" exception.

The reason I'm not using HQL is that the query is actually much more
complicated :) I need to use unmapped columns in the where clause and
subselects against other tables. In particular (if you care),
description and short_description can have translations in other tables.
Also, the rows are also effective dated. I wanted to hide this
complexity from my EMF model and thought this would be the best ways to
achieve that.

For the post, I just boiled it down to the simplest case.

Any other suggestions?

Thanks again,
Will

Martin Taal wrote:
> Hi Will,
> The mapping has backticks because then hibernate will quote the names
> when issueing real sql statements. This allows for example column names
> or table names which are sql keywords (for example index).
> Can you try a backticked sql query, something like this:
> session.createSQLQuery("select `VALUE` from
> general_code_item").addEntity("GeneralCodeItem")
>
> btw, for my curiosity why don't you use a hql? Because then afaics this
> issue does not occur.
>
> gr. Martin
>
> Will Horn wrote:
>> I have a teneo annotated ecore model and I have been able to
>> successfully make HQL queries such as:
>>
>> session.createQuery("from GeneralCodeItem")
>>
>> However, if I try to use a SQL query such as:
>>
>> session.createSQLQuery("select value, description, short_description
>> from general_code_item").addEntity("GeneralCodeItem")
>>
>> I get
>> 64606777 [main] INFO org.hibernate.type.StringType - could not read
>> column value from result set: "VALUE"; Invalid column name
>>
>> After debugging, I've determined the source of the problem in an
>> additional set of quotes. The result set has a column VALUE, but
>> hibernate is looking for "VALUE". When I look in the mapping file, I
>> see backticks in the column name which I presume is the source of the
>> extra quotes:
>>
>> <id name="value" type="java.lang.String">
>> <column not-null="true" unique="false" name="`VALUE`"/>
>> </id>
>>
>> All my column names have backticks. What is the reason for this and
>> is there anything I can do about it? I have the following workaround,
>> but it is very verbose and defeats the purpose of having the teneo
>> mappings:
>>
>> session.createSQLQuery("select {gc}.value {gc.value}, {gc}.description
>> {gc.description}, {gc}.short_description {gc.shortDescription} from
>> general_code_item {gc}").addEntity("gc", "GeneralCodeItem")
>>
>> Thanks!
>> Will
>
>

Report message to a moderator

Re: [TENEO] using createSQLQuery [message #102584 is a reply to message #102555]

Tue, 13 November 2007 23:33

Martin Taal

Messages: 5468
Registered: July 2009

Senior Member

I tested it and it seems a hibernate issue to me. I can also see that hibernate tries to get a
backticked column name from the resultset. Backticking in the mapping file is allowed so this sql
query should not be a problem.
I tested without backticking and then the query works fine.
As a workaround to prevent the backticking you can do the following:
- override the MappingContext class and then this method:
protected String trunc(String truncName, boolean truncPrefix) {
- the standard implementation method will return a backticked string, the easiest is call the
superclass then to strip the backticks off, or ofcourse you can override the complete method
- register your subclass of MappingContext as an extension (before initializing the datastore), see
here:
http://www.elver.org/hibernate/extensions.html
The extension point is the MappingContext.class.

gr. Martin

Will Horn wrote:
> Thanks for the reply Martin.
>
> When I use backticks, I get an "Invalid character" exception.
>
> The reason I'm not using HQL is that the query is actually much more
> complicated :) I need to use unmapped columns in the where clause and
> subselects against other tables. In particular (if you care),
> description and short_description can have translations in other tables.
> Also, the rows are also effective dated. I wanted to hide this
> complexity from my EMF model and thought this would be the best ways to
> achieve that.
>
> For the post, I just boiled it down to the simplest case.
>
> Any other suggestions?
>
> Thanks again,
> Will
>
> Martin Taal wrote:
>> Hi Will,
>> The mapping has backticks because then hibernate will quote the names
>> when issueing real sql statements. This allows for example column
>> names or table names which are sql keywords (for example index).
>> Can you try a backticked sql query, something like this:
>> session.createSQLQuery("select `VALUE` from
>> general_code_item").addEntity("GeneralCodeItem")
>>
>> btw, for my curiosity why don't you use a hql? Because then afaics
>> this issue does not occur.
>>
>> gr. Martin
>>
>> Will Horn wrote:
>>> I have a teneo annotated ecore model and I have been able to
>>> successfully make HQL queries such as:
>>>
>>> session.createQuery("from GeneralCodeItem")
>>>
>>> However, if I try to use a SQL query such as:
>>>
>>> session.createSQLQuery("select value, description, short_description
>>> from general_code_item").addEntity("GeneralCodeItem")
>>>
>>> I get
>>> 64606777 [main] INFO org.hibernate.type.StringType - could not read
>>> column value from result set: "VALUE"; Invalid column name
>>>
>>> After debugging, I've determined the source of the problem in an
>>> additional set of quotes. The result set has a column VALUE, but
>>> hibernate is looking for "VALUE". When I look in the mapping file, I
>>> see backticks in the column name which I presume is the source of the
>>> extra quotes:
>>>
>>> <id name="value" type="java.lang.String">
>>> <column not-null="true" unique="false" name="`VALUE`"/>
>>> </id>
>>>
>>> All my column names have backticks. What is the reason for this and
>>> is there anything I can do about it? I have the following
>>> workaround, but it is very verbose and defeats the purpose of having
>>> the teneo mappings:
>>>
>>> session.createSQLQuery("select {gc}.value {gc.value},
>>> {gc}.description {gc.description}, {gc}.short_description
>>> {gc.shortDescription} from general_code_item {gc}").addEntity("gc",
>>> "GeneralCodeItem")
>>>
>>> Thanks!
>>> Will
>>
>>

--

With Regards, Martin Taal

Springsite/Elver.org
Office: Hardwareweg 4, 3821 BV Amersfoort
Postal: Nassaulaan 7, 3941 EC Doorn
The Netherlands
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

Report message to a moderator

Re: [TENEO] using createSQLQuery [message #102878 is a reply to message #102584]

Thu, 15 November 2007 23:28

Will Horn

Messages: 265
Registered: July 2009

Senior Member

Thanks for looking into it. I found this
http://opensource.atlassian.com/projects/hibernate/browse/HH H-1093 so
apparently it is a bug. Their workaround is to have an explicit
<return-property> for the affected property, which is essentially the
more verbose syntax I was using.

It would be cleaner for my purposes to actually use the <sql-query> and
<return-property> elements in the hibernate mapping xml, which leads me
to the question of whether it is possible to merge elements into the
teneo generated hibernate mapping xml at runtime. I know I can just
make the modifications and create a static hibernate.hbm.xml, but that
seems like it would be harder to maintain if the ecore changes.

I've searched the newsgroup and the evler.org help but didn't see
anything about this.

-Will

Martin Taal wrote:
> I tested it and it seems a hibernate issue to me. I can also see that
> hibernate tries to get a backticked column name from the resultset.
> Backticking in the mapping file is allowed so this sql query should not
> be a problem.
> I tested without backticking and then the query works fine.
> As a workaround to prevent the backticking you can do the following:
> - override the MappingContext class and then this method:
> protected String trunc(String truncName, boolean truncPrefix) {
> - the standard implementation method will return a backticked string,
> the easiest is call the superclass then to strip the backticks off, or
> ofcourse you can override the complete method
> - register your subclass of MappingContext as an extension (before
> initializing the datastore), see here:
> http://www.elver.org/hibernate/extensions.html
> The extension point is the MappingContext.class.
>
> gr. Martin
>
> Will Horn wrote:
>> Thanks for the reply Martin.
>>
>> When I use backticks, I get an "Invalid character" exception.
>>
>> The reason I'm not using HQL is that the query is actually much more
>> complicated :) I need to use unmapped columns in the where clause and
>> subselects against other tables. In particular (if you care),
>> description and short_description can have translations in other
>> tables. Also, the rows are also effective dated. I wanted to hide
>> this complexity from my EMF model and thought this would be the best
>> ways to achieve that.
>>
>> For the post, I just boiled it down to the simplest case.
>>
>> Any other suggestions?
>>
>> Thanks again,
>> Will
>>
>> Martin Taal wrote:
>>> Hi Will,
>>> The mapping has backticks because then hibernate will quote the names
>>> when issueing real sql statements. This allows for example column
>>> names or table names which are sql keywords (for example index).
>>> Can you try a backticked sql query, something like this:
>>> session.createSQLQuery("select `VALUE` from
>>> general_code_item").addEntity("GeneralCodeItem")
>>>
>>> btw, for my curiosity why don't you use a hql? Because then afaics
>>> this issue does not occur.
>>>
>>> gr. Martin
>>>
>>> Will Horn wrote:
>>>> I have a teneo annotated ecore model and I have been able to
>>>> successfully make HQL queries such as:
>>>>
>>>> session.createQuery("from GeneralCodeItem")
>>>>
>>>> However, if I try to use a SQL query such as:
>>>>
>>>> session.createSQLQuery("select value, description, short_description
>>>> from general_code_item").addEntity("GeneralCodeItem")
>>>>
>>>> I get
>>>> 64606777 [main] INFO org.hibernate.type.StringType - could not
>>>> read column value from result set: "VALUE"; Invalid column name
>>>>
>>>> After debugging, I've determined the source of the problem in an
>>>> additional set of quotes. The result set has a column VALUE, but
>>>> hibernate is looking for "VALUE". When I look in the mapping file,
>>>> I see backticks in the column name which I presume is the source of
>>>> the extra quotes:
>>>>
>>>> <id name="value" type="java.lang.String">
>>>> <column not-null="true" unique="false" name="`VALUE`"/>
>>>> </id>
>>>>
>>>> All my column names have backticks. What is the reason for this and
>>>> is there anything I can do about it? I have the following
>>>> workaround, but it is very verbose and defeats the purpose of having
>>>> the teneo mappings:
>>>>
>>>> session.createSQLQuery("select {gc}.value {gc.value},
>>>> {gc}.description {gc.description}, {gc}.short_description
>>>> {gc.shortDescription} from general_code_item {gc}").addEntity("gc",
>>>> "GeneralCodeItem")
>>>>
>>>> Thanks!
>>>> Will
>>>
>>>
>
>

Report message to a moderator

Re: [TENEO] using createSQLQuery [message #102984 is a reply to message #102878]

Fri, 16 November 2007 13:52

Martin Taal

Messages: 5468
Registered: July 2009

Senior Member

There are a number of classes in the org.eclipse.emf.teneo.hibernate.mapper package which you can
override with your own implementation. Each of these classes handles one specific aspect of the
mapping. See here on how to plug your own classes into the system:
http://www.elver.org/hibernate/extensions.html

Did you try to remove the backticks from the mapping (see a few posts back in this thread)?

gr. Martin

Will Horn wrote:
> Thanks for looking into it. I found this
> http://opensource.atlassian.com/projects/hibernate/browse/HH H-1093 so
> apparently it is a bug. Their workaround is to have an explicit
> <return-property> for the affected property, which is essentially the
> more verbose syntax I was using.
>
> It would be cleaner for my purposes to actually use the <sql-query> and
> <return-property> elements in the hibernate mapping xml, which leads me
> to the question of whether it is possible to merge elements into the
> teneo generated hibernate mapping xml at runtime. I know I can just
> make the modifications and create a static hibernate.hbm.xml, but that
> seems like it would be harder to maintain if the ecore changes.
>
> I've searched the newsgroup and the evler.org help but didn't see
> anything about this.
>
> -Will
>
>
> Martin Taal wrote:
>> I tested it and it seems a hibernate issue to me. I can also see that
>> hibernate tries to get a backticked column name from the resultset.
>> Backticking in the mapping file is allowed so this sql query should
>> not be a problem.
>> I tested without backticking and then the query works fine.
>> As a workaround to prevent the backticking you can do the following:
>> - override the MappingContext class and then this method:
>> protected String trunc(String truncName, boolean truncPrefix) {
>> - the standard implementation method will return a backticked string,
>> the easiest is call the superclass then to strip the backticks off, or
>> ofcourse you can override the complete method
>> - register your subclass of MappingContext as an extension (before
>> initializing the datastore), see here:
>> http://www.elver.org/hibernate/extensions.html
>> The extension point is the MappingContext.class.
>>
>> gr. Martin
>>
>> Will Horn wrote:
>>> Thanks for the reply Martin.
>>>
>>> When I use backticks, I get an "Invalid character" exception.
>>>
>>> The reason I'm not using HQL is that the query is actually much more
>>> complicated :) I need to use unmapped columns in the where clause
>>> and subselects against other tables. In particular (if you care),
>>> description and short_description can have translations in other
>>> tables. Also, the rows are also effective dated. I wanted to hide
>>> this complexity from my EMF model and thought this would be the best
>>> ways to achieve that.
>>>
>>> For the post, I just boiled it down to the simplest case.
>>>
>>> Any other suggestions?
>>>
>>> Thanks again,
>>> Will
>>>
>>> Martin Taal wrote:
>>>> Hi Will,
>>>> The mapping has backticks because then hibernate will quote the
>>>> names when issueing real sql statements. This allows for example
>>>> column names or table names which are sql keywords (for example index).
>>>> Can you try a backticked sql query, something like this:
>>>> session.createSQLQuery("select `VALUE` from
>>>> general_code_item").addEntity("GeneralCodeItem")
>>>>
>>>> btw, for my curiosity why don't you use a hql? Because then afaics
>>>> this issue does not occur.
>>>>
>>>> gr. Martin
>>>>
>>>> Will Horn wrote:
>>>>> I have a teneo annotated ecore model and I have been able to
>>>>> successfully make HQL queries such as:
>>>>>
>>>>> session.createQuery("from GeneralCodeItem")
>>>>>
>>>>> However, if I try to use a SQL query such as:
>>>>>
>>>>> session.createSQLQuery("select value, description,
>>>>> short_description from
>>>>> general_code_item").addEntity("GeneralCodeItem")
>>>>>
>>>>> I get
>>>>> 64606777 [main] INFO org.hibernate.type.StringType - could not
>>>>> read column value from result set: "VALUE"; Invalid column name
>>>>>
>>>>> After debugging, I've determined the source of the problem in an
>>>>> additional set of quotes. The result set has a column VALUE, but
>>>>> hibernate is looking for "VALUE". When I look in the mapping file,
>>>>> I see backticks in the column name which I presume is the source of
>>>>> the extra quotes:
>>>>>
>>>>> <id name="value" type="java.lang.String">
>>>>> <column not-null="true" unique="false" name="`VALUE`"/>
>>>>> </id>
>>>>>
>>>>> All my column names have backticks. What is the reason for this
>>>>> and is there anything I can do about it? I have the following
>>>>> workaround, but it is very verbose and defeats the purpose of
>>>>> having the teneo mappings:
>>>>>
>>>>> session.createSQLQuery("select {gc}.value {gc.value},
>>>>> {gc}.description {gc.description}, {gc}.short_description
>>>>> {gc.shortDescription} from general_code_item {gc}").addEntity("gc",
>>>>> "GeneralCodeItem")
>>>>>
>>>>> Thanks!
>>>>> Will
>>>>
>>>>
>>
>>

--

With Regards, Martin Taal

Springsite/Elver.org
Office: Hardwareweg 4, 3821 BV Amersfoort
Postal: Nassaulaan 7, 3941 EC Doorn
The Netherlands
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

Report message to a moderator

Re: [TENEO] using createSQLQuery [message #612501 is a reply to message #102505]

Tue, 13 November 2007 21:52

Martin Taal

Messages: 5468
Registered: July 2009

Senior Member

Report message to a moderator

Re: [TENEO] using createSQLQuery [message #612503 is a reply to message #102546]

Tue, 13 November 2007 22:10

Will Horn

Messages: 265
Registered: July 2009

Senior Member

Report message to a moderator

Re: [TENEO] using createSQLQuery [message #612507 is a reply to message #102555]

Tue, 13 November 2007 23:33

Martin Taal

Messages: 5468
Registered: July 2009

Senior Member

Report message to a moderator

Re: [TENEO] using createSQLQuery [message #612552 is a reply to message #102584]

Thu, 15 November 2007 23:28

Will Horn

Messages: 265
Registered: July 2009

Senior Member

Report message to a moderator

Re: [TENEO] using createSQLQuery [message #612565 is a reply to message #102878]

Fri, 16 November 2007 13:52

Martin Taal

Messages: 5468
Registered: July 2009

Senior Member

Report message to a moderator

Previous Topic:	Re: EMF load resource behavior when the resource is a Database via Teneo
Next Topic:	EMF Model Synchrinization with Multipage Editor (Tree based Editor and Text Editor)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jul 27 16:05:06 GMT 2024

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter