Re: how to Escape single quotes with PreparedStatment

Hi,
The following code works properly.
    String query="SELECT count(*) over () as ROWCOUNT,
CONSUMER_ID,WENEXA_ID,CONSUMER_NAME,CONTACT_NO,residing_village from
db_consumer WHERE lower(CONSUMER_NAME) LIKE (lower(?))OR (lower(?)='') LIMIT
? OFFSET ? "; // AND (lower(RESIDING_VILLAGE) LIKE (lower(?) OR
(lower(?)='')) AND (lower(WENEXA_ID) LIKE (lower(?))OR(lower(?)=''))" ;


                log.info(query);            // Get the wenexa ID to be passed from servlet
here....
                stmt = con.prepareStatement(query);
                stmt.setString(1,"%"+name+"%");
                stmt.setString(2,"%"+name+"%");
                stmt.setInt(3,pageLimit);
                stmt.setInt(4,pageOffset);

However , when I try the code -
  String query="SELECT count(*) over () as ROWCOUNT,
CONSUMER_ID,WENEXA_ID,CONSUMER_NAME,CONTACT_NO,residing_village from
db_consumer WHERE lower(CONSUMER_NAME) LIKE (lower(?))OR (lower(?)='')  AND
(lower(RESIDING_VILLAGE) LIKE (lower(?) OR (lower(?)='')) AND
(lower(WENEXA_ID) LIKE (lower(?))OR(lower(?)='')) LIMIT ? OFFSET ?";
                                stmt = con.prepareStatement(query);
                                stmt.setString(1,"%"+name+"%");
                                stmt.setString(2,"%"+name+"%");
                                stmt.setString(3,"%"+village+"%");
                                stmt.setString(4,"%"+village+"%");
                               stmt.setInt(5,pageLimit);
                                stmt.setInt(6,pageOffset);
                                   rs= stmt.executeQuery();

It throws the previous error:org.postgresql.util.PSQLException: ERROR:
syntax error at or near "LIMIT"
  Position: 302
        at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
        at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
Any idea why this could be happening?

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/how-to-Escape-single-quotes-with-PreparedStatment-tp4718287p4722898.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.