Execute SQL on database creation

[Luigi R. Viggiano]

Hello everybody.

The feature to execute SQL on connection is an innovative idea.
Especially because the RUNSCRIPT can take a URL and can read the
script from the classpath.

But for my embedded application, what I need to do is to have a
feature that I would call "Execute SQL on Database Creation": not on
every connection but just if the database is created anew. So, when my
application starts for the first time, it will connect and silently
create the database *and the schema*.
I think it would be really useful. Is this already available? Or, does
it makes sense for a feature request?

At the moment, I think I will do something like:

"show tables" and, if returns 0 recs I'll do a "runscript
classpath:foo/bar/baz.sql"

or moving the above logic into stored procedure, and connecting using:

jdbc:h2:file:~/sample;INIT=CREATE ALIAS IF NOT EXISTS MAKE_SCHEMA FOR
"com.myApp.Function.makeSchema"; CALL MAKE_SCHEMA();

it could work, right? :-)

Thanks.
Luigi

[vrota...@gmail.com]

If you are using Spring, you can init H2 using the jdbc:embedded-database element and then use jdbc:script property

More here: http://stackoverflow.com/questions/7968067/startup-script-to-create-a-schema-in-hsqldb

Luigi

--
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.

--
   Vasile Rotaru

[Luigi R. Viggiano]

> If you are using Spring, you can init H2 using the jdbc:embedded-database

Many thanks vrotaru.
The project I'm working on, it's a kind of "pet project", so when I
work on my own I stay miles away from Spring :-)

I wonder how spring detects if the database is new, to run the script.
Btw the guy is asking about an in-memory database... and this is an
easier case to address.

BTW, since H2 is also targetted to platforms like android, having this
feature may be useful for those who can't (or don't want to) use
external libraries like spring.

Thanks again.

Luigi.

[vrota...@gmail.com]

Spring does not detect anything, and the spring is run always.

CREATE TABLE $TableName IF NOT EXISTS ...

and

MERGE ...

are your firends ;)

--
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.

--
   Vasile Rotaru

[Luigi R. Viggiano]

Hello.

To solve this problem (create the schema on database creation), I've
done it this way:

https://gist.github.com/2727424

It uses the modified DbStarter from https://gist.github.com/2727189

I'm sharing with anyone may need it.

Cheers,
Luigi

On May 8, 10:23 pm, "vrotaru...@gmail.com" <vrotaru...@gmail.com>
wrote: