How to recover from the DB not existing

[nick negulescu <ni...@gmail.com>]

I'm new to Derby and I'm trying to figure out how to load a database 
from a file if it doesn't already exist when loading the driver for the 
first time. I know I'm getting an SQLState of XJ004 and an ErrorCode of 
40000. This is code for when the application first starts up -- if the 
db doesn't exist -- let's create it unbeknownst to the user. What I 
would like to do is catch the exception. If the DB is not there, I want 
to create it, load it from the "bogus.SQL" text file and then try again:

        try {
            _conn = DriverManager.getConnection("jdbc:derby:bogusdb");
        }
        catch (SQLException e){
             <if SQLState = XJ004; create db with 
"jdbc:derby:bogusdb;create=true">
             <load db schema from file "bogus.SQL">
             <try "DriverManager.getConnection("jdbc:derby:bogusdb")" again>
             ...
            }
        }

Can anybody point me to the correct location in the documentation? I'm 
thinking that I'll need to load the file using IJ, but I'm not sure how 
to load the schema from a text file....

P.S. Great presentation Dan & Dave. I've been a list follower for a few 
months. I hadn't thought about to the memory stick idea: 
http://developers.sun.com/learning/javaoneonline/sessions/2006/TS-3154/index.html

Re: How to recover from the DB not existing

[John Embretsen <Jo...@Sun.COM>]

Nicholas Negulescu wrote:

[...]
> My problem is that I'm trying to figure out how to load it from an SQL 
> file. Sure, I can do it on the command line like so:
> 
> java -Djdbc.drivers=org.apache.derby.jdbc.EmbeddedDriver 
> org.apache.derby.tools.ij <bogusdb.sql>
> 
> but I was wondering which tool/class to use when you've alread got the 
> driver loaded and you're executing withing the VM already.
[...]

You might want to check out the new ij method called runScript(...), 
which was added in Derby 10.2 (soon to be released).

JavaDoc (for 10.2 runScript method) is here:

http://db.apache.org/derby/docs/10.2/publishedapi/jdbc3/org/apache/derby/tools/ij.html#runScript(java.sql.Connection,%20java.io.InputStream,%20java.lang.String,%20java.io.OutputStream,%20java.lang.String)


-- 
John

Re: How to recover from the DB not existing

[Nicholas Negulescu <ni...@gmail.com>]

I was thinking just the other way around. =)

The only time the exception will be thrown is if there is no database. In
the (rare/one-time) event that this is the first time a user has started the
application and the database does not exist, then go and create it from the
bogusdb.sql file -- zero admin. I'm using POJOs, not working with Hibernate
either (yet).

My problem is that I'm trying to figure out how to load it from an SQL file.
Sure, I can do it on the command line like so:

java -Djdbc.drivers=org.apache.derby.jdbc.EmbeddedDriver
org.apache.derby.tools.ij <bogusdb.sql>

but I was wondering which tool/class to use when you've alread got the
driver loaded and you're executing withing the VM already. I see that dblook
has an output (but no input) mechanism:

java org.apache.derby.tools.dblook -d 'jdbc:derby:c:\private\stuff\sample'
-o 'C:\temp\newDB.sql'

But this too is command-line driven. Thanks in advance!



On 9/24/06, Barry Books <tr...@gmail.com> wrote:
>
> You could always use create=true then load the database if it's not
> loaded. To figure out if it's been loaded you could select your your
> table names to see if they have been created. None of that will
> generate an exception.
>

Re: How to recover from the DB not existing

[Barry Books <tr...@gmail.com>]

You could always use create=true then load the database if it's not
loaded. To figure out if it's been loaded you could select your your
table names to see if they have been created. None of that will
generate an exception.