Link to home
Start Free TrialLog in
Avatar of innumonenu
innumonenu

asked on

Prepared Statement Bind Variables within SQL Single Quotes Problem

I am using the java.sql.PreparedStatement for the following query and have problems binding variables within the single quotes.

SELECT DISTINCT id, name FROM user WHERE office_id = ?
AND upper(id) like upper('%?%')
AND upper(name) like upper('?%')
ORDER BY upper(?)

If I use the bind variables within the single quotes then it says
"java.sql.SQLException: ORA-01006: bind variable does not exist". Maybe it is not treating the ? within the single quotes as a bind variable.

If I don't use the single quotes then it says
"java.sql.SQLException: ORA-00911: invalid character" as it requires the single quotes for upper statements.

Any idea how to resolve this?
Thanks.
Avatar of girionis
girionis
Flag of Greece image

Can you try escaping the ' by using another ' (I think this is the escape char on Oracle).
ASKER CERTIFIED SOLUTION
Avatar of TimYates
TimYates
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial