Re: how to Escape single quotes with PreparedStatment

Hi,

Am Montag, 22. August 2011 schrieb JavaNoobie:
> 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(%\"?\"%))";
It should be:

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(%?%))";

If you double quote the question mark, it would be an identifier (filed
name, tabel name ...). The question mark is the insert position for the
parameter. The prepared statement system escapes/expanded/... the
inserted value when necessary. The parameter number ist the count of a
question mark from left to right.

Bye Thomas


>
>                 stmt = con.prepareStatement(query);
>                 stmt.setString(1, name);
>                 rs= stmt.executeQuery();
> However upon executing the block, I get an error as follows
> org.postgresql.util.PSQLException: The column index is out of range:
> 1, number of columns: 0.
>     at
> org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.j
> ava:53) at
> org.postgresql.core.v3.SimpleParameterList.setStringParameter(SimpleP
> arameterList.java:118) at
> org.postgresql.jdbc2.AbstractJdbc2Statement.bindString(AbstractJdbc2S
> tatement.java:2184) at
> org.postgresql.jdbc2.AbstractJdbc2Statement.setString(AbstractJdbc2St
> atement.java:1303) at
> org.postgresql.jdbc2.AbstractJdbc2Statement.setString(AbstractJdbc2St
> atement.java:1289) at
> com.enzen.cis.dao.DAOConsumerSearch.getcList(DAOConsumerSearch.java:
> 5
>
> Any idea why this could be happening?
> Thank you.
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/how-to-Escape-single-quotes-
> with-PreparedStatment-tp4718287p4722152.html Sent from the PostgreSQL
> - jdbc mailing list archive at Nabble.com.