Database connection using embedded driver
I have a small application that relies on data stored in an underlying Java DB database. If I use the following to connect to the database, the application works:
derbyclient.jar in the main class CLASSPATH
public static final String DRIVER_NAME = "org.apache.derby.jdbc.ClientDriver";
public static final String DATABASE_URL = "jdbc:derby://localhost:1527/UnitConverters";
BUT...if I use the embedded driver, it does not work:
derby.jar in the main class CLASSPATH
public static final String DRIVER_NAME = "org.apache.derby.jdbc.EmbeddedDriver";
public static final String DATABASE_URL = "jdbc:derby:UnitConverters";
The error I get when I try using the embedded driver is: java.sql.SQLException: Database 'UnitConverters' not found.
Here's what I'm using to connect to the database (for both scenarios):
public void init() {
try {
Class.forName(DRIVER_NAME);
myConnection = DriverManager.getConnection(DATABASE_URL,"user","pass");
} catch (SQLException ex) {
ex.printStackTrace();
} catch (ClassNotFoundException ex) {
ex.printStackTrace();
}
}
Thanks in advance for your help,
Greg.
[1222 byte] By [
Chaoborida] at [2007-11-27 11:18:52]
are you using builtin Derby driver in eclipse? if you are then this article might be interesting for you. Secondly you are only suppose to use the clientDriver not the embedeed one.
try {
Class.forName("org.apache.derby.jdbc.ClientDriver");
System.out.println("JDBC driver loaded");
}
catch (ClassNotFoundException e) {
System.out.println(e.toString());
}
try {
Connection con = DriverManager.getConnection("jdbc:derby://localhost:1527/sample");
.
.
.
}
http://www.eclipse.org/articles/Article-EclipseDbWebapps/article.html
>
> Greg.
Have you tried with
public static final String DATABASE_URL="jdbc:derby:UnitConverters;create=true";
I took a look at some sample code I have for Derby and noticed this line, it just creates the DB but you should already have one from you first test. It might be however that you need a separate instance for the embedded mode.
Thanks for the quick reply. I'm using the client driver in NetBeans 5.5 (it looks to be pretty much the same from your code snippet). If I could get away with using the client driver, I would (because it works!). The problem is that when I try to run the app outside the IDE, it doesn't connect to the database. That's why I thought I'd try the embedded driver.
Greg.
> Thanks for the quick reply. I'm using the client
> driver in NetBeans 5.5 (it looks to be pretty much
> the same from your code snippet). If I could get
> away with using the client driver, I would (because
> it works!). The problem is that when I try to run
> the app outside the IDE, it doesn't connect to the
> database. That's why I thought I'd try the embedded
> driver.
>
> Greg.
You should be using the embedded driver, did you try the code I posted?
You may want to take a look at this
http://db.apache.org/derby/papers/DerbyTut/embedded_intro.html
Also when you downloaded the DB there should have been an example class for connecting.
> You should be using the embedded driver, did you try
> the code I posted?
Yes, I did try it just now. Although it still doesn't work (it tells me that my schema doesn't exist), I think we're getting closer.
> You may want to take a look at this
>
> http://db.apache.org/derby/papers/DerbyTut/embedded_in
> tro.html
Thanks for this--I'll have a look.
> Also when you downloaded the DB there should have
> been an example class for connecting.
Yes, there was. I've been trying examples and googling for a couple of days now, and I can't get it to work. Thanks for your help, though--at least I got a different error message this time! ;)
> Yes, there was. I've been trying examples and
> googling for a couple of days now, and I can't get it
> to work. Thanks for your help, though--at least I got
> a different error message this time! ;)
Try this, it is a sample I used to get connected in embedded mode.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
public class DatabaseConnection {
private String driver = "org.apache.derby.jdbc.EmbeddedDriver";
private String protocol = "jdbc:derby:";
public static void main(String[] args) {
new DatabaseConnection();
}
private DatabaseConnection() {
try{
Class.forName(driver).newInstance();
Connection conn = null;
Properties props = new Properties();
props.put("user", "user1");
props.put("password", "user1");
conn = DriverManager.getConnection(protocol +
"derbyDB;create=true", props);
System.out.println("Connected to and created database derbyDB");
conn.setAutoCommit(false);
} catch (SQLException e) {
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
}
> Try this, it is a sample I used to get connected in
> embedded mode.
I tried the code you provided, and it does make a connection using the embedded driver. The new problem now is that as soon as it hits the first sql statement, it returns an error suggesting that the Table/View does not exist (when in fact it does). Here's the actual error message:
java.sql.SQLSyntaxErrorException: Table/View 'CONTYPES' does not exist.
Any thoughts about this?
Greg.
Could it be that you didn't change the username/password which I provided and you made a connection with user1 which created a new empty schema?
> Could it be that you didn't change the
> username/password which I provided and you made a
> connection with user1 which created a new empty
> schema?
Actually, no--I changed the username and password (in fact, I had to modify your code a bit so it would work in my app). Nevertheless, I got it to work (sort of), but the behaviour is very strange. If I provide the full path (not the relative path) for the database URL, it works in the IDE, but it still doesn't find the data outside of the IDE (i.e., by double clicking the .jar file, say, on another machine)--but only when connected using the embedded driver; using the client driver, I only have to specify the relative path (but there are still no data in the fully-compiled .jar and therefore useless on another machine).
Again, I suspect that it's probably a classpath issue. Right now, I'm assuming that the compiler knows to include my database when building the app--but I'm not sure how I can tell. Do I need to put the database on the classpath? If so, how would I do it? I've tried just adding the directory that contains my database to the classpath, and I've tried packing it into a db.jar file and sticking that on the classpath--but neither works. The only way my data are found is if I use a fully-qualified path to the database (which of course, will be different on a different machine, and not very useful for a stand-alone app).
Thanks again for your help,
Greg.
Hi
I have a very similar problem, I am using NetBeans 5.5.1 with JDK1.6. I am using the derby Embedded driver and the SimpleApp example.
I have created a database that I can then see within the Netbeans IDE. Within the IDE I create a new table called 'NewTable' with one column called 'Colmn1'.
I adjust the simple app example with the following connection string:-
Properties props = new Properties();
props.put("user", "user1");
props.put("password", "user1");
conn = DriverManager.getConnection("jdbc:derby:D:/Java/Database/Database/NewDatabase; create=true",props);
If I then try to query the data (after getting the connection) with the following:-
s.execute("insert into NewTable values(99)");
I get the following error:-
java.sql.SQLSyntaxErrorException: Table/View 'USER1.NEWTABLE' does not exist.
Any ideas why I can't access the NewTable I have created?
Thanks
David
I have just resolved the issue from my perspective I had to adjust the SQL statement from
s.execute("insert into NewTable values(99)");
to
s.execute("insert into \"USER1\".\"NewTable\" values(99)");
Thanks
David
