Start a new topic

H2 UDF Returning a Result Set - View Results and Logging

[This topic is migrated from our old forums. The original author name has been removed]

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

[This reply is migrated from our old forums.]

Re: H2 UDF Returning a Result Set - View Results and Logging
Peter, What version of DbVisualizer and H2 are you using? Easiest way to find out the H2 version is to connect in DbVisualizer then in the Connection tab, right-click in the Connection message and select Copy. Paste in a follow-up. I just tried with: DbVisualizer Pro 9.0.6 H2 1.3.167 (2012-05-23) H2 JDBC Driver 1.3.167 (2012-05-23) I run the following script: [nobr] [pre] 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'); [/pre] [/nobr] The result is: [nobr] [pre] COL1 COL2 1 V1 2 V2 3 V3 [/pre] [/nobr] You say you don't get any result at all. Can you please enable debug of DbVisualizer in Tools->Debug Window and run the above script? If you want to write to the debug window try: *System.out.println("");* in the Java code. Example: [nobr] [pre] create alias query as $$ ResultSet query(Connection conn, String sql) throws SQLException { System.out.println(""); return conn.createStatement().executeQuery(sql); } $$; [/pre] [/nobr] Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]
[Attachment has been removed.]

Re: H2 UDF Returning a Result Set - View Results and Logging
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
[This reply is migrated from our old forums.]

Re: H2 UDF Returning a Result Set - View Results and Logging
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
[This reply is migrated from our old forums. The original author name has been removed]
[Attachment has been removed.]

Re: H2 UDF Returning a Result Set - View Results and Logging
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
[This reply is migrated from our old forums.]

Re: H2 UDF Returning a Result Set - View Results and Logging
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
[This reply is migrated from our old forums. The original author name has been removed]

Re: H2 UDF Returning a Result Set - View Results and Logging
Hans, thanks very much. Got it. I appreciate your help.