Info/Warning messages from SQL procedures to Java through JDBC driver

Question

By seeing the ResultSet.getWarnings() method there must be some way to pass warning/info messages from stored procedures to Java layer but found most of the drivers doesn't implement this method. Are there any way to send info/warning message from stored procedure to Java layer?

Answer 1

Did you already try using CallableStatement when calling stored procedures. I think that's how it should be done. Maybe there getWarnings() is supported.

CallableStatement cs = Connection.prepareCall(..)
cs.registerOutParameter(..)
cs.setXXX
....
cs.executeUpdate()
cs.getXXX()
cs.getWarnings()

Answer 2

This is going to depend on the specific server. In MSSQL, you would use RAISERROR. I think the behavior of just hiding in the get warnings or throwing an SQLException (which you can always catch and parse the message of) will depend on the level.

But the if and how it is supported will vary by database vendor, so you should target your question to the specific database you are interested in.

Answer 3

I presume by warning/info messages you mean text strings generated by the stored procedure itself. I would recommend adding an out parameter to the stored procedure and use that to return messages/warnings. If your stored procedures are written in Java on Oracle you could even serialize the list of messages, return it across the JDBC interface as a blob, and reconstitute it in the client Java code. I did this in a large system several years ago and it worked quite well.