Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (nike.apache.org: local policy)
Date: Tue, 07 Aug 2007 09:06:05 +0200
From: Thomas Nielsen <Thomas.Nielsen@Sun.COM>
Subject: Re: Accessing embedded database in JAR file
In-reply-to: <D66F86D6-E2A1-4F2E-A6DF-613C0A5E2C8A@semantra.com>
Sender: Thomas.Nielsen@Sun.COM
To: Derby Discussion <derby-user@db.apache.org>
Message-id: <46B819DD.2030905@sun.com>
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 7BIT
References: <D66F86D6-E2A1-4F2E-A6DF-613C0A5E2C8A@semantra.com>
User-Agent: Thunderbird 2.0.0.0 (X11/20070419)

Hi Ryan,

First of all, can you please state what version you are using?

I tested this on the main trunk, and from what I can tell there seems to 
be a slight problem - at least with the documentation regarding the '/' 
and the classpath subsubprotocol.

Using the documented way and a jar'ed small test database called 
MyNullsTest:
--- snip snip ---
ij version 10.4
ij> connect 'jdbc:derby:classpath:/MyNullsTest';
ERROR XJ004: Database 'classpath:/MyNullsTest' not found.
--- snip snip --

Skipping the '/' seems to work though (like you state):
--- snip snip --
ij>  connect 'jdbc:derby:classpath:MyNullsTest';
ij> select * from t;
C1         |C2
-----------------------
1          |1
..
..
--- snip snip ---

Maybe you can use the 'jar' subsubprotocol directly with the version you 
are using as a workaround?

--- snip snip ---
ij> connect 'jdbc:derby:jar:(./MyNullsTest.jar)MyNullsTest';
ij> select * from t;
C1         |C2
-----------------------
1          |1
..
..
--- snip snip ---

Hopefully some of the more experienced Derby people can shed some light 
on the '/' issue, and how it was meant to be...

HTH,
Thomas


Ryan Breidenbach wrote:
> I am trying to create a database that is embedded within a JAR file and 
> so far I am not having any luck. Here is what I have done so far (mostly 
> by the "book" from the Derby docs).
> 
> Obviously, the first step is to create a database, which I did in a 
> temporary directory. I then moved the created database files into my 
> "classes" directory (from which I will build my JAR file) *excluding* 
> the /tmp directory and *.lck files. All of these remaining DB files are 
> jarred up. Executing jar -tf of this file yields this (among other 
> things - this JAR also has a META-INF directory and some classes):
> 
> northwind/
> northwind/log/
> northwind/seg0/
> northwind/log/log.ctrl
> northwind/log/log1.dat
> northwind/log/log2.dat
> northwind/log/logmirror.ctrl
> northwind/seg0/c10.dat
> northwind/seg0/c101.dat
> northwind/seg0/c111.dat
> northwind/seg0/c121.dat
> northwind/seg0/c130.dat
> northwind/seg0/c141.dat
> northwind/seg0/c150.dat
> northwind/seg0/c161.dat
> northwind/seg0/c171.dat
> northwind/seg0/c180.dat
> northwind/seg0/c191.dat
> northwind/seg0/c1a1.dat
> northwind/seg0/c1b1.dat
> northwind/seg0/c1c0.dat
> northwind/seg0/c1d1.dat
> northwind/seg0/c1e0.dat
> northwind/seg0/c1f1.dat
> northwind/seg0/c20.dat
> northwind/seg0/c200.dat
> northwind/seg0/c211.dat
> northwind/seg0/c221.dat
> northwind/seg0/c230.dat
> northwind/seg0/c241.dat
> northwind/seg0/c251.dat
> northwind/seg0/c260.dat
> northwind/seg0/c271.dat
> northwind/seg0/c281.dat
> northwind/seg0/c290.dat
> northwind/seg0/c2a1.dat
> northwind/seg0/c2b1.dat
> northwind/seg0/c2c1.dat
> northwind/seg0/c2d0.dat
> northwind/seg0/c2e1.dat
> northwind/seg0/c2f0.dat
> northwind/seg0/c300.dat
> northwind/seg0/c31.dat
> northwind/seg0/c311.dat
> northwind/seg0/c321.dat
> northwind/seg0/c331.dat
> northwind/seg0/c340.dat
> northwind/seg0/c351.dat
> northwind/seg0/c361.dat
> northwind/seg0/c371.dat
> northwind/seg0/c380.dat
> northwind/seg0/c391.dat
> northwind/seg0/c3a1.dat
> northwind/seg0/c3b1.dat
> northwind/seg0/c3c0.dat
> northwind/seg0/c3d1.dat
> northwind/seg0/c3e0.dat
> northwind/seg0/c3f1.dat
> northwind/seg0/c400.dat
> northwind/seg0/c41.dat
> northwind/seg0/c411.dat
> northwind/seg0/c420.dat
> northwind/seg0/c431.dat
> northwind/seg0/c440.dat
> northwind/seg0/c451.dat
> northwind/seg0/c460.dat
> northwind/seg0/c471.dat
> northwind/seg0/c480.dat
> northwind/seg0/c491.dat
> northwind/seg0/c4a1.dat
> northwind/seg0/c4b0.dat
> northwind/seg0/c4c1.dat
> northwind/seg0/c4d1.dat
> northwind/seg0/c4e1.dat
> northwind/seg0/c4f0.dat
> northwind/seg0/c501.dat
> northwind/seg0/c51.dat
> northwind/seg0/c511.dat
> northwind/seg0/c520.dat
> northwind/seg0/c531.dat
> northwind/seg0/c541.dat
> northwind/seg0/c551.dat
> northwind/seg0/c560.dat
> northwind/seg0/c571.dat
> northwind/seg0/c581.dat
> northwind/seg0/c591.dat
> northwind/seg0/c5a0.dat
> northwind/seg0/c5b1.dat
> northwind/seg0/c5c1.dat
> northwind/seg0/c5d1.dat
> northwind/seg0/c5e1.dat
> northwind/seg0/c5f0.dat
> northwind/seg0/c60.dat
> northwind/seg0/c601.dat
> northwind/seg0/c611.dat
> northwind/seg0/c621.dat
> northwind/seg0/c71.dat
> northwind/seg0/c81.dat
> northwind/seg0/c90.dat
> northwind/seg0/ca1.dat
> northwind/seg0/cb1.dat
> northwind/seg0/cc0.dat
> northwind/seg0/cd1.dat
> northwind/seg0/ce1.dat
> northwind/seg0/cf0.dat
> northwind/service.properties
> 
> So far this is what I think it should look like for a database named 
> 'northwind'. The next step is to include this JAR as a dependency on 
> another project. Once this is done, I attempt to select data from one of 
> the northwind tables. Here is code snippet of that 
> 
> // configure system properties per docs
> System.setProperty("derby.storage.tempDirectory", tempDirectory);
> System.setProperty("derby.stream.error.file",logFile);
> DataSource dataSource = new 
> DriverManagerDataSource(EmbeddedDriver.class.getName(),"jdbc:derby:classpath:northwind", 
> "");
> Connection connection = dataSource.getConnection();
> Statement statement = connection.createStatement();
> ResultSet rs = statement.executeQuery("select * from employees");
> 
> And this is where everything goes wrong and I get "Table/View 
> 'EMPLOYEES' does not exist." According to the documentation, this URL 
> should not even work - the database name should begin with a "/" and I 
> should not have to include the "classpath" subprotocol. But if I exclude 
> either of these, I get a "Database /northwind not found" error message. 
> The fact that I am not getting that error message leads me to believe 
> that the embedded database *is* being found - I am just not sure where 
> my tables are.
> 
> Also, if I change the URL to access the the database on the filesystem 
> (from the previous project's build) via the directory subprotocol, 
> everything works. So, I know the database was created correctly at one 
> point in time.
> 
> I have been struggling with this for a day now and I am at a loss. Is 
> there anything obvious I am doing wrong? Did I miss a configuration step 
> to make a embedded JAR database work? Any help would be great.
> 
> Thanks.
> 
> Ryan
> 

-- 
Thomas Nielsen