Oracle connection preparedStatement object print out the query to be executed

Question

In Java I would want to print out the query that is going to be submitted/queried on the database so that I can see whats the error when the query throws out exception.

It will be useful to exactly locate the issue instead of trying to understand Oracle Exception ID's and trying to match where exactly did it fail in the code. Any help please.

PreparedStatement ps = conn.prepareStatement("SELECT * FROM EMPLOYEES where EMPNAME=?");
ps.setString(1, "HULK");
ps.executeQuery();

Ideally I want to do a syso(ps) or syso(ps.getquery) and the output should be

SELECT * FROM EMPLOYEES WHERE EMPNAME='HULK'
or
SELECT * FROM EMPLOYEES WHERE EMPNAME=<HASHCODE OF THE OBJECT YOU ARE TRYING TO BIND>

Related Question: http://stackoverflow.com/questions/2683214/get-query-from-java-sql-preparedstatement?lq=1 — Srujan Kumar Gulla, Dec 27 '12 at 19:26

score 1 · Accepted Answer · answered Aug 23 '12 at 14:30

Something interesting I ran across, Log4JDBC, which allows you to log SQL Calls. I haven't had a chance to use it yet, but I thought it was a great idea to be able to change the logging level and get the SQL calls into a log file.

This is more than you asked for, but I thought it might be worth throwing out there.

score 0 · Answer 2 · edited May 23 '17 at 10:34

0

I think this is already been answered here.

Short answer: print toString() method or the PrepareStatement to see the query with the bind variables substituted with values.

BUT: It al depends of the implementor. Not all JDBC drivers add this nicety.

If your particular driver doesn't comply with this, then the only workaround would be composing the SQL by concatenating the values instead of using bind variables (losing the performance advantages the RDBMS gives you when using bind variables).

But for this you have to convert things to strings, etc.

This would be paradoxical, since I have found that concatenated SQLs are the most error prone and are the ones that need most printing and checking.

edited May 23 '17 at 10:34

Community

1
1

answered Aug 23 '12 at 14:26

Tulains Córdova

2,559
2
20
33

Agreed. MySQL does print out this information but I am looking for ORACLE JDBC drivers.If there is no way to do it in Oracle is there any work around??? – Srujan Kumar Gulla Aug 23 '12 at 14:28
I improved my answer to accommodate to your comment. – Tulains Córdova Aug 23 '12 at 14:51

Oracle connection preparedStatement object print out the query to be executed

2 Answers2