You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by "Catalina Wei (JIRA)" <ji...@apache.org> on 2006/08/21 20:59:13 UTC
[jira] Created: (OPENJPA-27) SQL Parameter markers generated for
literals causes DB2 SQL error SQLCODE -417(SQLSTATE 42609), 418(SQLSTATE
42610)
SQL Parameter markers generated for literals causes DB2 SQL error SQLCODE -417(SQLSTATE 42609), 418(SQLSTATE 42610)
-------------------------------------------------------------------------------------------------------------------
Key: OPENJPA-27
URL: http://issues.apache.org/jira/browse/OPENJPA-27
Project: OpenJPA
Issue Type: Bug
Components: query
Reporter: Catalina Wei
over usage of parameter markers for LITERALS causes DB2 SQL errors.
Literals in the JP query if generated 'as is' in the pushdown SQL, can avoid DB2 SQL errors.
Simple predicates caused DB2 SQL errors:
where substring(e.name, 1, 5) = 'Harry'
where mod(e.empid, 2) > 0
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Closed: (OPENJPA-27) SQL Parameter markers generated for
literals causes DB2 SQL error SQLCODE -417(SQLSTATE 42609), 418(SQLSTATE
42610)
Posted by "David Wisneski (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-27?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
David Wisneski closed OPENJPA-27.
---------------------------------
Resolution: Duplicate
duplicate of OPENJPA-22
> SQL Parameter markers generated for literals causes DB2 SQL error SQLCODE -417(SQLSTATE 42609), 418(SQLSTATE 42610)
> -------------------------------------------------------------------------------------------------------------------
>
> Key: OPENJPA-27
> URL: https://issues.apache.org/jira/browse/OPENJPA-27
> Project: OpenJPA
> Issue Type: Bug
> Components: query
> Reporter: Catalina Wei
>
> over usage of parameter markers for LITERALS causes DB2 SQL errors.
> Literals in the JP query if generated 'as is' in the pushdown SQL, can avoid DB2 SQL errors.
> Simple predicates caused DB2 SQL errors:
> where substring(e.name, 1, 5) = 'Harry'
> where mod(e.empid, 2) > 0
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-27) SQL Parameter markers generated for
literals causes DB2 SQL error SQLCODE -417(SQLSTATE 42609), 418(SQLSTATE
42610)
Posted by "Marc Prud'hommeaux (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/OPENJPA-27?page=comments#action_12429502 ]
Marc Prud'hommeaux commented on OPENJPA-27:
-------------------------------------------
This limitation can frequently be worked around by adding in "CAST" functions around the parameter: e.g., "SELECT * FROM PEOPLE WHERE ? = ?" will cause the error "A statement string to be prepared contains parameter markers as the operands of the same operator.", but "SELECT * FROM PEOPLE WHERE CAST(? AS INTEGER) = CAST(? AS INTEGER)" will execute fine. In many cases, we already do put in the CAST for literal parameters.
Could you post the offending SQL and the exception that it raises so we can see where it is going wrong?
> SQL Parameter markers generated for literals causes DB2 SQL error SQLCODE -417(SQLSTATE 42609), 418(SQLSTATE 42610)
> -------------------------------------------------------------------------------------------------------------------
>
> Key: OPENJPA-27
> URL: http://issues.apache.org/jira/browse/OPENJPA-27
> Project: OpenJPA
> Issue Type: Bug
> Components: query
> Reporter: Catalina Wei
>
> over usage of parameter markers for LITERALS causes DB2 SQL errors.
> Literals in the JP query if generated 'as is' in the pushdown SQL, can avoid DB2 SQL errors.
> Simple predicates caused DB2 SQL errors:
> where substring(e.name, 1, 5) = 'Harry'
> where mod(e.empid, 2) > 0
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (OPENJPA-27) SQL Parameter markers generated for
literals causes DB2 SQL error SQLCODE -417(SQLSTATE 42609), 418(SQLSTATE
42610)
Posted by "Catalina Wei (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/OPENJPA-27?page=comments#action_12429507 ]
Catalina Wei commented on OPENJPA-27:
-------------------------------------
JP query: select e from EmpBean e where subString(e.name,1,5) = 'Harry'
failed. <0|false|0.0.0> org.apache.openjpa.persistence.PersistenceException: DB2 SQL error: SQLCODE: -417, SQLSTATE: 42609, SQLERRMC: null {prepstmnt 1355960530 SELECT t0.empid, t0.bonus, t1.deptno, t1.budget, t1.name, t0.execLevel, t0.hireDate, t0.hireTime, t0.hireTimestamp, t0.city, t0.state, t0.street, t0.zip, t0.isManager, t0.name, t0.salary FROM EmpBean t0 LEFT OUTER JOIN DeptBean t1 ON t0.dept_deptno = t1.deptno WHERE (SUBSTR(CAST((t0.name) AS VARCHAR(1000)), CAST(((? - ?)) AS INTEGER) + 1, CAST(((? + (? - ?))) AS INTEGER) - CAST(((? - ?)) AS INTEGER)) = ?) [params=(long) 1, (int) 1, (long) 5, (long) 1, (int) 1, (long) 1, (int) 1, (String) Harry]} [code=-417, state=42609]
JP query: select e from EmpBean e where e.salary > 5 and mod(e.empid, 2) > 0
failed. <0|false|0.0.0> org.apache.openjpa.persistence.PersistenceException: DB2 SQL error: SQLCODE: -418, SQLSTATE: 42610, SQLERRMC: null {prepstmnt 1468422022 SELECT t0.empid, t0.bonus, t1.deptno, t1.budget, t1.name, t0.execLevel, t0.hireDate, t0.hireTime, t0.hireTimestamp, t0.city, t0.state, t0.street, t0.zip, t0.isManager, t0.name, t0.salary FROM EmpBean t0 LEFT OUTER JOIN DeptBean t1 ON t0.dept_deptno = t1.deptno WHERE (t0.salary > ? AND MOD(t0.empid, ?) > ?) [params=(long) 5, (long) 2, (long) 0]} [code=-418, state=42610]
JP query: select e from EmpBean e where (e.empid+3 not between 2*2 and 8 and e.salary between 56000 and 64000)
failed. <0|false|0.0.0> org.apache.openjpa.persistence.PersistenceException: DB2 SQL error: SQLCODE: -417, SQLSTATE: 42609, SQLERRMC: null {prepstmnt 1651925622 SELECT t0.empid, t0.bonus, t1.deptno, t1.budget, t1.name, t0.execLevel, t0.hireDate, t0.hireTime, t0.hireTimestamp, t0.city, t0.state, t0.street, t0.zip, t0.isManager, t0.name, t0.salary FROM EmpBean t0 LEFT OUTER JOIN DeptBean t1 ON t0.dept_deptno = t1.deptno WHERE (NOT ((t0.empid + ?) >= (? * ?) AND (t0.empid + ?) <= ?) AND t0.salary >= ? AND t0.salary <= ?) [params=(long) 3, (long) 2, (long) 2, (long) 3, (long) 8, (long) 56000, (long) 64000]} [code=-417, state=42609]
> SQL Parameter markers generated for literals causes DB2 SQL error SQLCODE -417(SQLSTATE 42609), 418(SQLSTATE 42610)
> -------------------------------------------------------------------------------------------------------------------
>
> Key: OPENJPA-27
> URL: http://issues.apache.org/jira/browse/OPENJPA-27
> Project: OpenJPA
> Issue Type: Bug
> Components: query
> Reporter: Catalina Wei
>
> over usage of parameter markers for LITERALS causes DB2 SQL errors.
> Literals in the JP query if generated 'as is' in the pushdown SQL, can avoid DB2 SQL errors.
> Simple predicates caused DB2 SQL errors:
> where substring(e.name, 1, 5) = 'Harry'
> where mod(e.empid, 2) > 0
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira