Running SQL from the command-line

[Jon Carlson]

I've recently discovered the shell script. Very nice. I'm trying to
easily get SQL output from a bash script like I can do with postgres:

psql -c 'select * from user' <db-name-goes-here>

Can this be easily done? The source coded didn't seem to include this
as an option. Something like:

java -cp h2.jar org.h2.tools.Shell -url ... -user ... -password ... -
sql "select * from user"

and have it go to standard out?

- Jon

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

[Jon Carlson]

Here is my "diff" to make it happen. It works great. I would much
prefer if this were part of the standard H2 distribution so I don't
have to manage copying these changes from release to release. The new
class is named Shell2 just so I could easily diff the two:

jon:h2 jcarlson$ diff src/main/org/h2/tools/Shell.java src/main/org/h2/
tools/Shell2.java
37c37
< public class Shell extends Tool implements Runnable {
---
> public class Shell2 extends Tool implements Runnable {
74c74
< new Shell().runTool(args);
---
> new Shell2().runTool(args);
112a113
> String sql = null;
123a125,126
> } else if (arg.equals("-sql")) {
> sql = args[++i];
136c139,143
< promptLoop();
---
> if (sql == null) {
> promptLoop();
> } else {
> execute(sql);
> }

Thanks!

[Thomas Mueller]

Hi,

Thanks! I did a bit more and support multiple statements:

java org.h2.tools.Shell -url jdbc:h2:test -sql "select 'hello'; select
'world' from dual"

Using the following:

ScriptReader r = new ScriptReader(new StringReader(sql));
while (true) {
String s = r.readStatement();
if (s == null) {
break;
}
execute(s);
}

Regards,
Thomas

[Dario Fassi]

Hi,
This is very convenient extension to integrate maintenance database tasks ala *nix command into general tasks and cron scripts.

Can be useful too an additional extension for an  "-sqlFile <sqlFileName>"   option to get a complete script from an external sql file.

I think that would be convenient to if  H2 Shell and H2 Console share a common behavior and capabilities for their respective devices ( tty and browser ).
So console built-in @command can be available to the shell too.

I did a bit more and support multiple statements:

java org.h2.tools.Shell -url jdbc:h2:test -sql "select 'hello'; select
'world' from dual"

Using the following:

ScriptReader r = new ScriptReader(new StringReader(sql));
while (true) {
    String s = r.readStatement();
    if (s == null) {
        break;
    }
    execute(s);
}

Regards,

Dario

[Thomas Mueller]

Hi,

> Can be useful too an additional extension for an  "-sqlFile <sqlFileName>"
> option to get a complete script from an external sql file.

There is already a RunScript tool that supports it. Why wouldn't you
want to use that instead of the Shell tool?

> console built-in @command can be available to the shell too.

Yes, I will add that as a feature request. It's a bit more work to do
that. Patches are welcome of course!

Regards,
Thomas