| Eclipselink queries on DB2 [message #390611] |
Wed, 29 July 2009 11:38  |
Eclipse User |
|
|
|
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 #409738 is a reply to message #390611] |
Thu, 30 July 2009 03:59  |
| Eclipse User |
|
|
|
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 #482047 is a reply to message #480790] |
Tue, 25 August 2009 04:45 |
| Eclipse User |
|
|
|
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
|
|
|
Powered by
FUDForum. Page generated in 0.26899 seconds
Home