I have a sql query which works fine until a LIMIT clause is added to the end of the query.Below is the query:
String query = SELECT * FROM customer
WHERE acct = ?
AND cust_id = ?
AND call_status = ?
AND entry_id = get_user_id(?)
AND cust_call_id = ?
AND severity = ?
LIMIT ?;
I am using PreparedStatement to set the values using Informix driver this is the complete class name: com.informix.jdbc.IfxPreparedStatement.
PreparedStatement pstmt = pstmt = m_con.prepareStatement(query);
There are no errors when operations like setString(), setInt() are done. Using setInt() when adding value for "?" for the LIMIT. I have checked multiple times and the correct values are set to the respective "?" . It throws an error when this line executes: pstmt.executeQuery()
Error trace:
A character to numeric conversion process failed
at com.informix.util.IfxErrMsg.buildExceptionWithMessage(IfxErrMsg.java:416)
at com.informix.util.IfxErrMsg.buildIsamException(IfxErrMsg.java:401)
at com.informix.jdbc.IfxSqli.addException(IfxSqli.java:3021)
at com.informix.jdbc.IfxSqli.receiveError(IfxSqli.java:3272)
at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2269)
at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2194)
at com.informix.jdbc.IfxSqli.sendStatementQuery(IfxSqli.java:1524)
at com.informix.jdbc.IfxSqli.executeStatementQuery(IfxSqli.java:1474)
at com.informix.jdbc.IfxResultSet.executeQuery(IfxResultSet.java:183)
at com.informix.jdbc.IfxStatement.executeQueryImpl(IfxStatement.java:902)
at com.informix.jdbc.IfxPreparedStatement.executeQuery(IfxPreparedStatement.java:289)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at com.choicehotels.dbaccess.PreparedStatementProxy.execute(PreparedStatementProxy.java:225)
at com.choicehotels.dbaccess.PreparedStatementProxy.invoke(PreparedStatementProxy.java:115)
at com.sun.proxy.$Proxy4.executeQuery(Unknown Source)
The error is because of "LIMIT ?" in the query, because if I remove it then the query runs fine. If I remove any of the "Where" conditions but keep "LIMIT ?" , the error is still thrown.
It is not possible to use a ?
placeholder with LIMIT
. You could rephrase your query to use ROW_NUMBER
instead:
SELECT
FROM (
SELECT c.*, ROW_NUMBER() OVER (ORDER BY <some_col>) rn
FROM customer c
WHERE acct = ? AND
cust_id = ? AND
call_status = ? AND
entry_id = get_user_id(?) AND
cust_call_id = ? AND
severity = ?
) t
WHERE rn <= ?; -- bind the LIMIT value to this ?