Re: how to Escape single quotes with PreparedStatment

Better still, with the Postgres driver once you have prepared the
statement (including when you have "?" parameters for substitution), you
can use the "toString()" method of the PreparedStatement object to see
what the final SQL statement is that will be executed.

So, try this to get a better trace of what is failing:-

PreparedStatement stmt = con.prepareStatement(query);
System.out.println("SQL=" + stmt.toString());
ResultSet rs= stmt.executeQuery();

Hope this helps.

Andrew



On 20/08/2011 16:10, dmp wrote:
> JavaNoobie wrote:
> > Hi All,
> > I'm trying to write a preparedstatement query as below.
> >
> >     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('%"+name+"%'))OR
> > (lower('" + name + "')='')) AND (lower(RESIDING_VILLAGE) LIKE
> > (lower('%"+village+"%')) OR (lower('" + village + "')='')) AND
> > (lower(WENEXA_ID) LIKE (lower('%"+wenexaid+"%'))OR(lower( '" +
> wenexaid +
> > "')='')) LIMIT '"+pageLimit+"'OFFSET '"+pageOffset+"'";
> >
> >                 stmt = con.prepareStatement(query);
> >                 rs= stmt.executeQuery();
> >
> > However , the query fails with postgresql when a double quote is
> passed into
> > it.I was under the impression that Prepared statement would take
> care of the
> > same . But can anyone explain why I'm getting the error?
> > Thank you.
>
> Perhaps to get a better idea of what exactly the query string
> is that is being executed you could:
>
> System.out.println(query);
> stmt = con.prepareStatement(query);
> rs= stmt.executeQuery();
>
> danap.
>