Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Eclipselink queries on DB2
Eclipselink queries on DB2 [message #390611] Wed, 29 July 2009 15:38 Go to next message
Milos is currently offline MilosFriend
Messages: 12
Registered: July 2009
Junior Member
Hello! Our application has successfully worked on both Oracle and
SQLServer database. Now I need to use DB2 database, also. I have set
target database properties to DB2, created schema and it seemed to work.
But then it turned out that I keep getting error -418 for some queries:

STRING TO BE PREPARED CONTAINS INVALID USE OF PARAMETER MARKERS

Of course, I have looked into it. Google is our friend, but I couldn't
find any reference regarding using toplink/eclipselink. On the other hand,
it turns out that the error is quite common when using pure JDBC and
parameter queries such as mine. References I found say that sometimes DB2
does not know the type of given parameter, so explicit type cast needs to
be done. For instance,


select * from mytable t where t.name like ?

should use explicit cast

select * from mytable t where t.name like (cast ? as varchar(300))

or something like that.

But, I use JPA query language and I don't have control over how my query
is actually translated.

So, does anyone have any experience with DB2 database and these kinds of
queries and could workaround be found?

Thanks,
Milos
Re: Eclipselink queries on DB2 [message #390612 is a reply to message #390611] Wed, 29 July 2009 19:55 Go to previous messageGo to next message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

Please include the full EclipseLink exception including the SQL that was
generated. Also include your query.

---
James


James : Wiki : Book : Blog : Twitter
Re: Eclipselink queries on DB2 [message #409738 is a reply to message #390611] Thu, 30 July 2009 07:59 Go to previous messageGo to next message
Milos is currently offline MilosFriend
Messages: 12
Registered: July 2009
Junior Member
Here is one of the examples. This is error eclipse returns:

[EL Warning]: 2009.07.30 09:52:21.265--UnitOfWork(14832679)--Exception
[EclipseLink-4002] (Eclipse Persistence Services
- 1.0.2 (Build 20081024)):
org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: [BEA][DB2 JDBC
Driver][DB2]STRING TO BE PREPARED CONTAINS INVALID USE OF PARMETER MARKERS
Error Code: -418
Call: SELECT ID, VERSION, CODE, ROLE, DESCRIPTION, DATASOURCE, TITLESTYLE
FROM RE1REPORT WHERE (((? IS NULL) OR (CODE LIKE ?)) AND ((? IS NULL) OR
(DESCRIPTION LIKE ?)))
bind => [null, null, null, null]
Query: ReadAllQuery


and this is the JPA query:

select r from Report r where (:code is null or r.code like :code)
and (:description is null or r.description like :description)

where :code and :description are given query parameters.

It makes no difference whether you set values to these parameters or leave
them NULL.
Re: Eclipselink queries on DB2 [message #467976 is a reply to message #409738] Mon, 03 August 2009 13:50 Go to previous messageGo to next message
Milos is currently offline MilosFriend
Messages: 12
Registered: July 2009
Junior Member
Any updates on this one?
Re: Eclipselink queries on DB2 [message #480790 is a reply to message #409738] Tue, 18 August 2009 13:46 Go to previous messageGo to next message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

You can disable parameter binding in EclipseLink.

Either for the persistence unit, or per query,

See,
http://www.eclipse.org/eclipselink/api/1.1.2/org/eclipse/per sistence/config/PersistenceUnitProperties.html#JDBC_BIND_PAR AMETERS

http://www.eclipse.org/eclipselink/api/1.1.2/org/eclipse/per sistence/config/QueryHints.html#BIND_PARAMETERS


James : Wiki : Book : Blog : Twitter
Re: Eclipselink queries on DB2 [message #482047 is a reply to message #480790] Tue, 25 August 2009 08:45 Go to previous message
Milos is currently offline MilosFriend
Messages: 12
Registered: July 2009
Junior Member
I have tried this. Indeed, it did the work of directly putting parameters
into query rather than binding, but unfortunately it seems to have a
problem with comparing null values. I get SQL Exception

Internal Exception: java.sql.SQLException: [BEA][DB2 JDBC
Driver][DB2]OPERANDS OF ARITHMETIC OR COMPARISON OPERATION NOT
COMPARABLE
Error Code: -401


Experimenting with generated query (I copied generated query from console
and executed it in TOAD) showed that the problem is in comparing nulls for
date typed, in this part of generated where condition:

AND (t0.DUE_DATE >= NULL)) AND (t0.DUE_DATE <= NULL)

while in other places it generates appropriate CAST (NULL AS
VARCHAR(32672) )

when I remove these date comparisons from query, it works. Otherwise, when
executing from TOAD, I get error:

ERROR [42818] [IBM][DB2/NT] SQL0401N The data types of the operands for
the operation ">=" are not compatible. SQLSTATE=42818
Previous Topic:JPA DAO in Desktop Application
Next Topic:setShouldBindAllParameters performance issue
Goto Forum:
  


Current Time: Mon Dec 22 22:10:55 GMT 2014

Powered by FUDForum. Page generated in 0.02909 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software