TopLink native query issues

[bht]

Hi,

I am a user not a GlassFish developer. please accept my apologies.

There are a few bugs on the topic of case sensitivity with native
queries. Some are closed, others still open. The overall status
appears to be inconsistent to me.

The dilemma can possibly be best understood by reading a 2008 post:

http://kr.forums.oracle.com/forums/thread.jspa?threadID=612380
"primary key detected to be null, but it isn't" (I can really
understand Adam's frustration and I feel sorry for him)

I have hit these issues myself multiple times while working only with
two popular database engines in different ways (Derby and Postgresql).

I found that bugs were closed as duplicates of
https://glassfish.dev.java.net/issues/show_bug.cgi?id=1442
which was closed fixed in 2006.

Example:
https://glassfish.dev.java.net/issues/show_bug.cgi?id=1507
"Native query fails using PostgreSQL"

Three years later I am hitting this closed bug id=1507 with the Java
EE bundled with the latest NetBeans 6.7.1. In fact nothing works as
expected in this area. It is a minefield.

I hope that someone can shed some light on this. I applied for
observer status on these bugs but it hasn't worked for me yet.

Best regards

Bernard

[christopher delahunt]

Hello Bernard,

The issues you have linked are symptoms of Glassfish bug 600
(https://glassfish.dev.java.net/issues/show_bug.cgi?id=600) which should
describe the issue .  Incase you've read it already and still need more
info, the issues is essentially that TopLink/TLE/EclipseLink are case
sensitive, meaning that database field "ID" is not the same field as
"id" - they are treated as regular Java Strings.

In a simple example:

@Id
@Column(name="ID")
Integer id;

The column name used in the database for the integer will be "ID".  The
specification also mandates that when the column is not defined, the
field that is used by default is also in uppercase, so:

@Id
Integer id;

will also use the "ID" database column.  As most databases are case
insensitive by default, the database will accept "ID" regardless of
using PostgreSQL or Oracle.  The problem though, and the issue you are
likely encountering
is then what the different databases return.  By default, most databases
such as Oracle, will return the column/table names in upper case.  Using
a native query "Select * from Employee" on one of these databases return
all the metadata in uppercase.
When the results are searched, TopLink will use the field names to look
up the values - so the "ID" string will be used to find an Integer to
put in the id attribute.  Unfortunately, when using other databases such
as PostgreSQL, the default is to return metadata in lowercase.  Since
"ID" != "id" in the resultset returned from the query, the resultset
returns null for the "ID" databasefield (even though "id" might have a
value).

The other common issue from case sensitivity is with joincolumns.  
JoinColumns are used to specify the foreignkey field using the
referencedColumnName, and it is common to use the attribute name instead
of the field.  ie:
   @JoinColumn(name="FKTO_ID", referencedColumnName="id")
The above example will think that the user wants to use a database field
"id" instead of the "ID" that is associated to the id attribute. This
results in either table creation problems or odd runtime issues where
the FK never gets updated in the database since the "id" field never has
a value.

TopLink/EclipseLink being case sensitive means it can work on a database
that is in case sensitive mode, since "ID" is considered different from
"id".

Hope this helps,
Chris


bht@... wrote:

> Hi,
>
> I am a user not a GlassFish developer. please accept my apologies.
>
> There are a few bugs on the topic of case sensitivity with native
> queries. Some are closed, others still open. The overall status
> appears to be inconsistent to me.
>
> The dilemma can possibly be best understood by reading a 2008 post:
>
> http://kr.forums.oracle.com/forums/thread.jspa?threadID=612380
> "primary key detected to be null, but it isn't" (I can really
> understand Adam's frustration and I feel sorry for him)
>
> I have hit these issues myself multiple times while working only with
> two popular database engines in different ways (Derby and Postgresql).
>
> I found that bugs were closed as duplicates of
> https://glassfish.dev.java.net/issues/show_bug.cgi?id=1442
> which was closed fixed in 2006.
>
> Example:
> https://glassfish.dev.java.net/issues/show_bug.cgi?id=1507
> "Native query fails using PostgreSQL"
>
> Three years later I am hitting this closed bug id=1507 with the Java
> EE bundled with the latest NetBeans 6.7.1. In fact nothing works as
> expected in this area. It is a minefield.
>
> I hope that someone can shed some light on this. I applied for
> observer status on these bugs but it hasn't worked for me yet.
>
> Best regards
>
> Bernard
>