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.
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.
Can you try escaping the ' by using another ' (I think this is the escape char on Oracle).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.