Script tool without -options adds every line of output to result set

[Ken Jorissen]

With this setup:

$ java -cp ~/data/git/h2-1.3.174/h2/bin/h2-1.3.174.jar org.h2.tools.Shell -url "jdbc:h2:./test" -sql "create table foo (bar int)"

$ java -cp ~/data/git/h2-1.3.174/h2/bin/h2-1.3.174.jar org.h2.tools.Shell -url "jdbc:h2:./test" -user sa -password sa -sql "insert into foo values (1)"

These two commands do very different things internally:

$ java -cp ~/data/git/h2-1.3.174/h2/bin/h2-1.3.174.jar org.h2.tools.Script -url "jdbc:h2:./test" -user sa -password sa -script script.sql

$ java -cp ~/data/git/h2-1.3.174/h2/bin/h2-1.3.174.jar org.h2.tools.Shell -url "jdbc:h2:./test" -user sa -password sa -sql "SCRIPT TO 'shell.sql'"

When using the script tool, it executes the SQL "SCRIPT". The query execution doesn't get the OutputStream and all the data is added to the result set. For a large database, that can be problematic because a large temp table can be created. That takes a lot longer (my test case of a 5GB database dumps in 30 minutes versus about 3 minutes) and temporarily uses a lot of disk space (30 extra GB versus no extra space when the temp table is avoided).

When using the shell tool to execute "SCRIPT to 'shell.sql'", the OutputStream is set and the insert statements do not go into the result set leading to no temp table in most cases.

Using any of the -options arguments or just a naked "-options" on the end will force Script.java to go through Script.processScript(). That generates the SQL as "SCRIPT TO 'filename.sql'", that gets around this as well.

Is there a reason to use the form that just calls "SCRIPT"? The following diff unifies the behavior and I don't see a downside:

===================================================================

--- src/main/org/h2/tools/Script.java (revision 5922)

+++ src/main/org/h2/tools/Script.java (working copy)

@@ -59,7 +59,7 @@

         String user = "sa";

         String password = "";

         String file = "backup.sql";

-        String options1 = null, options2 = null;

+        String options1 = "", options2 = "";

         for (int i = 0; args != null && i < args.length; i++) {

             String arg = args[i];

             if (arg.equals("-url")) {

@@ -98,11 +98,7 @@

             showUsage();

             throw new SQLException("URL not set");

         }

-        if (options1 != null) {

-            processScript(url, user, password, file, options1, options2);

-        } else {

-            execute(url, user, password, file);

-        }

+        processScript(url, user, password, file, options1, options2);

     }

 

     private static void processScript(String url, String user, String password,

The above is against 1.3.174. Other than the default user changing, trunk looks the same.

I work with a program that is calling org.h2.tools.Script.execute(url, user, password, fileName). I was working on setting BLOCKSIZE, but now I am on a tangent trying to figure out why it doesn't seem to be making a giant temp table. I ran into this when testing things and found the workaround. More debugging. I'm thinking we should just use the SQL manually instead of the tool. That way we can set BLOCKSIZE. Any thoughts on that would be greatly appreciated.

Thanks,

Ken

[Thomas Mueller]

Hi,

Good point! I will fix this for the next release (with a slightly different patch; I will also remove some of the unused methods).

Regards,

Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.