Win a copy of Terraform in Action this week in the Cloud forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Tim Cooke
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Liutauras Vilda
Sheriffs:
  • Jeanne Boyarsky
  • Rob Spoor
  • Bear Bibeault
Saloon Keepers:
  • Jesse Silverman
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • Al Hobbs
  • salvin francis

connecting to an Apache Derby database

 
Rancher
Posts: 623
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am struggling to connect



I am getting to 2 but not 3

Any helpful comments ?

p.s. the database DCEvents was created in Netbeans 12.4 - has two tables full of data
 
Saloon Keeper
Posts: 8770
71
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You are concatenating protocol with dbname which also contains the protocol. I also suspect that "create=true" may need to be a property but I'm not sure.
 
Bob Matthews
Rancher
Posts: 623
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Carey

my bad!

what confuses me is that setting up the database in Netbeans I had to specify the location of the databases

this I did : C:\Apache\Derby\Databases

but when I try the following line

String dbName = "C:/Apache/Derby/Databases/DCEvents"; // the path to the database

no connection ?

although I can see the database in that folder
 
Carey Brown
Saloon Keeper
Posts: 8770
71
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I believe it's the database server that's responsible for knowing where a particular DB is kept. You should only need the DB name.
 
Bob Matthews
Rancher
Posts: 623
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
String dbName = "C:/Users/xxxx/.netbeans-derby/us_copiosus_EURJPY_S"; // the name of the database

the above worked for me in 2017
 
Marshal
Posts: 3727
534
Android Eclipse IDE TypeScript Redhat MicroProfile Quarkus Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Carey Brown wrote:I believe it's the database server that's responsible for knowing where a particular DB is kept. You should only need the DB name.


He is using the embedded driver (org.apache.derby.jdbc.EmbeddedDriver), so the path to the DB location needs to be specified.
 
Ron McLeod
Marshal
Posts: 3727
534
Android Eclipse IDE TypeScript Redhat MicroProfile Quarkus Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Your database URL looks correct.

I just looked at an old project where I used embedded Derby, and these are the URLs that I used (first one in-memory for testing, second uses the file system):
 
Ron McLeod
Marshal
Posts: 3727
534
Android Eclipse IDE TypeScript Redhat MicroProfile Quarkus Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Is there a derby.log file in the working directory?  It may provide some clues.
 
Ron McLeod
Marshal
Posts: 3727
534
Android Eclipse IDE TypeScript Redhat MicroProfile Quarkus Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Bob Matthews wrote:
I am getting to 2 but not 3


We can't see what you are doing with exceptions.  Maybe getConnection() wasn't happy.   Are you catching and printing/logging them?
 
Saloon Keeper
Posts: 24580
168
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
From:To:

Try this and I think you'll see that not only are you doubling up on the protocol in your database URL (which is invalid), but that you also have rammed the dbName up against the protocol without a separating ";" character.
 
Bob Matthews
Rancher
Posts: 623
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I couldn't locate a derby.log file

Only 'derby' which has the following info

Tue Aug 03 12:52:24 NZST 2021 : Apache Derby Network Server - 10.14.2.0 - (1828579) started and ready to accept connections on port 1527
----------------------------------------------------------------
Tue Aug 03 12:52:34 NZST 2021:
Booting Derby version The Apache Software Foundation - Apache Derby - 10.14.2.0 - (1828579): instance a816c00e-017b-097f-52c3-ffffc126f61b
on database directory C:\Apache\Derby\Databases\DCEvents with class loader jdk.internal.loader.ClassLoaders$AppClassLoader@73d16e93
Loaded from file:/C:/Apache/db-derby-10.14.2.0-bin/lib/derby.jar
java.vendor=Oracle Corporation
java.runtime.version=16.0.1+9-24
user.dir=C:\Apache\db-derby-10.14.2.0-bin
os.name=Windows 10
os.arch=amd64
os.version=10.0
derby.system.home=C:\Apache\Derby\Databases
Database Class Loader started - derby.database.classpath=''
--------------------------------------------------------------------------------------
This may be as a result of my making a connection within Netbeans

Output from Tim's suggestion

Time               Messages                                                      
2021-08-03 21:00:00 Database URL is 'jdbc:derby:C:/Apache/Derby/Databases/DCEvents'
 
Tim Holloway
Saloon Keeper
Posts: 24580
168
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Fortune smiles upon you.

I used in-memory derby for a product I used to maintain as the testing database. It's a little bit older, but my log has this in it:
Note the word "memory". Your database is listening on a TCP/IP port. I think you're running the network server, not the in-memory server.

My URL is also different:
AND my driver:
 
Bob Matthews
Rancher
Posts: 623
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
my driver is same



I did not have "memory" in the protocol

the above setup has worked for me before (about 5 years ago) ?
 
Tim Holloway
Saloon Keeper
Posts: 24580
168
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'd recommend this:

https://db.apache.org/derby/docs/10.8/devguide/cdevdvlpinmemdb.html#cdevdvlpinmemdb
 
Ron McLeod
Marshal
Posts: 3727
534
Android Eclipse IDE TypeScript Redhat MicroProfile Quarkus Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Which dependency do you have in your POM: Derby Embedded or Derby Client?  You want the library for the embedded driver (and engine).
 
Bob Matthews
Rancher
Posts: 623
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am running a standalone java program on a trading platform at Dukascopy

no "POM" required

I setup the database and its tables from within Netbeans, as I did 5 years ago
 
Ron McLeod
Marshal
Posts: 3727
534
Android Eclipse IDE TypeScript Redhat MicroProfile Quarkus Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Bob Matthews wrote:no "POM" required


Ok - which Derby jar are you using: derby-10.XXX.jar or derbyclient-10.XXX.jar?
 
Bob Matthews
Rancher
Posts: 623
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
//These two files must be in the "files" Directory (usually: Documents/JForex/files)
@Library("C:/Users/xxx/Documents/JForex/files/weka.jar;C:/Users/xxx/Documents/JForex/files/derby.jar")
@RequiresFullAccess
 
Bob Matthews
Rancher
Posts: 623
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Is there something inherently wrong with the way I am setting up the database and tables from within Netbeans ?

I find it easy to follow and easy to create .sql files to populate the tables

And then I move off to a standalone java program to access the database

Bob M
 
Tim Holloway
Saloon Keeper
Posts: 24580
168
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
From the sound of it, you're using Netbeans as a database administration tool and to run your application as a Netbeans project, not as a stand-alone program.

Nothing wrong with that other than that as long as you only need to run it for yourself and not in an Enterprise environment.

But there are a couple of things to be careful about.

The most important thing is that your Netbeans database "workbench" may not be using the same derby driver JAR file that your project is using. Generally you'd have to set the driver jar both as part of the database editor's configuration and in the runtime classpath as separate operations. There are very good reasons for that, including the fact that the primary purpose of an IDE is to be able to build apps that can run without the IDE.

Another consideration is that you can have "memory-only" database, a file-resident database or a server-based database so the URL you use is just as important as the driver class you give. Some JDBC driver classes will support more than one type of URL.

And if you have a file-resident database, you need to make sure that it's persistent and doesn't get wiped out between runs. In-memory gets wiped out by definition unless you back it up and restore it to RAM.

Finally, getting back to the actual original complaint. The in-memory and filesystem-based Derby databases should not be opening any TCP/IP ports. Only the server version of Derby should be doing that. While we tend to think of JDBC as a network-based protocol, it's not. It's just that most JDBC clients are connecting to a network-based server. When your database is living in the same filesystem as your client, what's the point of going through the network when you can talk to the files directly?

OK, let me re-phrase that. Obviously I can talk via network to a MySQL server even though it and its data files are both on the same machine. But there I'm talking to the server, not to the server's file(s). I talk to TCP port 3301. When you have a memory database, your URL specifies the file and not only would a port be pointless (and extra overhead), but I don't believe that disk-based Derby databases are designed for multiple concurrent clients. Whereas almost all port-based  DBMS servers are.
 
Bob Matthews
Rancher
Posts: 623
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have gone back to the dos prompt and followed the installation of derby instructions:

1) I have set the DERBY_INSTALL variable

2) I have set the CLASSPATH explicitly to include derby.jar, derbytools.jar etc

3) I then try to verify derby with the following command
C:\>java org.apache.derby.tools.sysinfo

and get the following error

Error: Could not find or load main class org.apache.derby.tools.sysinfo
Caused by: java.lang.ClassNotFoundException: org.apache.derby.tools.sysinfo


 
Tim Holloway
Saloon Keeper
Posts: 24580
168
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
If you have set the CLASSPATH thinking that it will be picked up by NetBeans you are probably mistaken. IDE's usually have their own ideas of where the classpath should be and they don't read the one that you set. It definitely won't work for Eclipse, I don't think it works for IntelliJ and I doubt NetBeans will work any differently than they do.

There should be a "Preferences" menu/dialog for your NetBeans database editor and that's where you'd tell it where the database JARs that it will work with are. If you're using a copy of NetBeans based on an older installation you may have inherited an incompatible derby JAR. Meaning that if you've defined a derby JAR for your project that's not the same as whatever was installed in the database editor, it's possible that differences in the logic and/or database file format are making your attempt fail.
 
Bob Matthews
Rancher
Posts: 623
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
OK confused with version of derby.......

all good now...........got up to checking ij version 10.14

however I don't wish to create my dbase from there

so I am back to my original problem.......not getting a connection in my stand-alone java program

p.s. I am not running a Netbeans project as such

so to summarize:

1) I install derby as per instructions

2) I create a database in Netbeans and populate my tables there

3) I run a stand-alone java program on the Dukascopy trading platform

I am trying to get a connection with my database and failing (but 5 years ago it worked with current code)

Am wondering whether I should go back to an earlier version of derby ?

Have worked thru Step 3 : embedded Derby - all satisfactory

so I can create a database, add a table, add records etc. from derby.tools.ij

BUT I can't connect to that database from my java program ???
 
Tim Holloway
Saloon Keeper
Posts: 24580
168
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I see a "try" statement, but you didn't show the corresponding "catch".

Since you're attempting to connect and not reaching the post-connect statement, we can reasonably assume that an exception was thrown.

If an exception was thrown the catch should be printing the stack trace. We NEED that stack trace!
 
Bob Matthews
Rancher
Posts: 623
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Time               Messages                                                                                    
2021-08-04 22:40:01 File access error: No suitable driver found for jdbc:derbyC:/Apache/Derby/Databases/DCEvents
2021-08-04 22:40:01 we get to here - 2:                                                                        
2021-08-04 22:40:01 we get to here - 1:      
                                                                 
 
Ron McLeod
Marshal
Posts: 3727
534
Android Eclipse IDE TypeScript Redhat MicroProfile Quarkus Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
jdbc:derbyC:/Apache/Derby/Databases/DCEvents

There is a colon missing between derby and C
 
Tim Holloway
Saloon Keeper
Posts: 24580
168
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
As I said, sometimes a driver class can support more than one type of URL. The reverse of that is that if you supply a driver class that DOESN'T support that URL, that's the message you'll get.

Try a "jdbc:derby:memory:" URL. Just because it was optional 5 years ago doesn't mean it still is.
 
Carey Brown
Saloon Keeper
Posts: 8770
71
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I don't see the variable "driver" being passed to "loadDriver()". Is something missing here?
 
Bob Matthews
Rancher
Posts: 623
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
private String protocol = "jdbc:derby:";

It was the missing colon after derby which was the problem

Many thanks to all
 
Tim Holloway
Saloon Keeper
Posts: 24580
168
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Carey Brown wrote:I don't see the variable "driver" being passed to "loadDriver()". Is something missing here?



Ding!

Yes, you can  include driver classname(s) as a property to getConnection(). But if I haven't forgotten something, you don't need to do so anymore since the DriverManager internally enumerates all the driver classes in its classpath - which is also why using Class.forName() is obsolete.

I think, in fact, that when you attempt to get a connection to a JDBC URL that the entire driver set is polled to see which one responds for that URL pattern (if any - otherwise you get "No Suitable Driver"), but I'm speculating here. I'd expect an explicit driver classname to override that.

The loadDriver() method there is a user-defined method and we have no idea of what it's doing, since the source for it was not supplied to us. If it takes from some examples I've seen, it's calling Class.forName. Which again, is a decade or more obsolete.

I'm spoiled I've gone too long using frameworks and DataSources or that might have stood out more.
 
Bob Matthews
Rancher
Posts: 623
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Just as I was rejoicing......................

I now can't seem to add a record ???



I get to 4 but not 5
 
Carey Brown
Saloon Keeper
Posts: 8770
71
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
 
Carey Brown
Saloon Keeper
Posts: 8770
71
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Your parameter 1 is type Date but you are setting it with a String. Don't you need to set it with a java.sql.Date type?
 
Bob Matthews
Rancher
Posts: 623
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Have added but now am getting this error.............

Time               Messages                                                                                                                                                                          
2021-08-05 01:20:01 we get to here - 3:                                                                                                                                                                
2021-08-05 01:20:01 File access error: Failed to start database 'C:/Apache/Derby/Databases/DCEvents' with class loader com.dukascopy.dds2.greed.agent.a.c@4990a0b9, see the next exception for details.
2021-08-05 01:20:01 we get to here - 2:                                                                                                                                                                
2021-08-05 01:20:01 we get to here - 1:                                                                                                                                                                
2021-08-05 01:20:01 DerbyApp starting in embedded mode                                                                                                                                                
2021-08-05 01:20:01 ASK Price: 1.1836; BID Price: 1.18357                                                                                                                                              
 
Bob Matthews
Rancher
Posts: 623
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
yes parameters 1 and 2 are Date and Time but are stored as Strings
 
Tim Holloway
Saloon Keeper
Posts: 24580
168
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Bob Matthews wrote:private String protocol = "jdbc:derby:";

It was the missing colon after derby which was the problem

Many thanks to all



If you'd kept my example and used the URL that was built instead of re-concatenating that would not have been a problem.  Because you showed us the correct URL that came from my code.

Now you've given us an Exception, but it says "see the next exception"

And you didn't show us the next Exception.

We appreciate not drowning us in data, but when it comes to stack traces, it's better to give us the whole thing. Because often you get a "Caused By" and sometimes it has a "Caused By" and so forth until one comes to the real reason for the problem. We'll be happy to wade through all the other stack junk because it will save hours trying to figure out what happened.
 
Bob Matthews
Rancher
Posts: 623
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
what is the code I should have to see the full stack trace ?
 
Tim Holloway
Saloon Keeper
Posts: 24580
168
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Note that it's no great benefit to catch only SQLExceptions. Might as well catch everything.
Also, technically closing statements closes their resultsets, but I'm being paranoid here. Actually, closing the Connection probably closes open Statements as well, but I'm less sure about that

Finally, closing JDBC objects can also throw Exceptions, so if you're really paranoid, you could catch them. But if you don't, the JBM will and it's not something to expect normally.
 
Tim Holloway
Saloon Keeper
Posts: 24580
168
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Addendum: you can make things a bit tidier using try-with-resources, but we'll stick to the legacy approach for the moment.
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic