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.