Informix PrepareStatement throws error - "A character to numeric conversion process failed" when LIMIT clause is added to query

Question

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.

Answer 1

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 ?