H2 UDF Returning a Result Set - View Results and Logging

H2 UDF can return a result set such as in the following example (from H2 documentation): create alias query as $$ ResultSet query(Connection conn, String sql) throws SQLException { return conn.createStatement().executeQuery(sql); } $$; call query('select * from TestTable'); This seems to work fine in DbVis but the ResultSet from the call query(...) statement is not displayed in the results window of DbVis. Is this possible and if so then how? Also, is there anyway to write to the log window and/or debug window from inside these UDFs? Thanks, Peter

Roger Brilliant, thanks so much. I think both of my issues were attributable to simple user error although one small puzzle remains. I don't get the Result Set tab if run the create alias and the call statement in the same script. If I run the call statement in a tab without the create alias then the Result Set tab appears as expected. (This is what threw me off when I first tried this, I just never tried them in separate tabs.) That is, if I run the following script in a query tab, after creating the table test as in your example, then no Result Set tab appears, just the log tab appears (see attached file 'CombinedCreateAndCallScript.log'). drop alias if exists query; create alias query as $$ ResultSet query(Connection conn, String sql) throws SQLException { System.out.println(" ******************************************"); return conn.createStatement().executeQuery(sql); } $$; call query('select * from test'); However, if I run the call statement in a tab without the create alias then the Result Set tab appears as normal along with the appropriate output (see the attached file 'CallScript.log'). call query('select * from test'); I have attached the logs for the two scripts in case that is helpful - although I'm happy to just run things in separate tabs. My version information is in the attached log files. I don't know why I didn't see my println output when I tried it first time, probably just didn't look hard enough. Thanks again for your help. --Peter

Hi Peter, I get the Result Set tab even when I run your combined script with drop, create and call, along with the Log tab. Can it be that the Log tab is the one that is displayed in your case, but you also get the Result Set tab but have to click on it to see it? The Log tab should be shown only if there are any warnings or errors when there is also a Result Set tab, so it still sounds like something is not quite right if it behaves as you describe. Best Regards, Hans

Hans Yes, something on my setup must be off, I simply don't get the Result Set tab from the combined script. I've attached the log and a screen capture. The Log reports that there was a warning although I don't see what that was. No errors from the script. Curious. Peter

Hi, The log shows a couple of things: 1) You execute all statements together with Execute Buffer instead of one by one with Execute 2) You do not use the default SQL Block end delimiter, probably because that would give an error when you use Execute Buffer. So change the script like this: CREATE TABLE TEST (COL1 INTEGER, COL2 VARCHAR(10)); INSERT INTO TEST (COL1, COL2) VALUES (1, 'V1'); INSERT INTO TEST (COL1, COL2) VALUES (2, 'V2'); INSERT INTO TEST (COL1, COL2) VALUES (3, 'V3'); --/ create alias query as $$ ResultSet query(Connection conn, String sql) throws SQLException { return conn.createStatement().executeQuery(sql); } $$; / call query('select * from TEST');") And execute it with Execute instead of Execute Buffer. Best Regards, Hans Edited by: Hans Bergsten on Apr 26, 2013 5:57 PM

Hans, thanks very much. Got it. I appreciate your help.