Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Java database program

Posted on 2011-02-23
Medium Priority
Last Modified: 2012-05-11
I need to setup a java program to incorporate an embedded derby database in the program.  I want it to be a pre-populated database that is deployed with the program.  I have the basics of the GUI designed but can't for the life of me figure out how to add an existing derby database to my project.  

Where do I put the database and then I would need the connection string to use to access it.  I have a database class file already but don't know how to add a pre-populated database to the project.  I have the sql file that would create the database originally if it is needed.

I am using Eclipse as my IDE so I would like it to be explained with that in mind.  I don't really want to use command line.

Question by:ninedoors19
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +1
LVL 86

Expert Comment

ID: 34961529
Just copy *all* files from the template database to the installation you want to use
LVL 47

Expert Comment

ID: 34961721

This apache tutorial gives detailed instructions how to set up
derby database using Eclipse environment:

They also show connection strings, etc.  Please, look at it, and let us know if you
see any particular problems.

Author Comment

ID: 34963346

I tried this tutorial again for the 3rd time with no luck.  I will try to explain exactly what I have done.  

So I add the Apache Derby Nature fine.  I then create my sql file that is going to create the database, myDB, and then create the tables and add 6 rows to the settings table, disconnect and exit.  

Once the file is created I right click on my sql file and choose Apache Derby -> Run SQL Script with ij

This works fine with no errors.  I then open ij and connect with the same url string used in the sql file I just ran and call the show tables command to make sure the tables were created and they were as I see this is code in the console window:

ij version 10.7
ij> connect 'jdbc:derby:myDB;create=true;user=app;password=golfgolf';
WARNING 01J01: Database 'myDB' not created, connection made to existing database instead.
ij> show tables;
TABLE_SCHEM         |TABLE_NAME                    |REMARKS             
SYS                 |SYSALIASES                    |                    
SYS                 |SYSCHECKS                     |                    
SYS                 |SYSCOLPERMS                   |                    
SYS                 |SYSCOLUMNS                    |                    
SYS                 |SYSCONGLOMERATES              |                    
SYS                 |SYSCONSTRAINTS                |                    
SYS                 |SYSDEPENDS                    |                    
SYS                 |SYSFILES                      |                    
SYS                 |SYSFOREIGNKEYS                |                    
SYS                 |SYSKEYS                       |                    
SYS                 |SYSPERMS                      |                    
SYS                 |SYSROLES                      |                    
SYS                 |SYSROUTINEPERMS               |                    
SYS                 |SYSSCHEMAS                    |                    
SYS                 |SYSSEQUENCES                  |                    
SYS                 |SYSSTATEMENTS                 |                    
SYS                 |SYSSTATISTICS                 |                    
SYS                 |SYSTABLEPERMS                 |                    
SYS                 |SYSTABLES                     |                    
SYS                 |SYSTRIGGERS                   |                    
SYS                 |SYSVIEWS                      |                    
SYSIBM              |SYSDUMMY1                     |                    
APP                 |contacts                      |                    
APP                 |errors                        |                    
APP                 |game_clock                    |                    
APP                 |game_music                    |                    
APP                 |game_rosters                  |                    
APP                 |music                         |                    
APP                 |new_players                   |                    
APP                 |official_games                |                    
APP                 |penalties                     |                    
APP                 |penalty_codes                 |                    
APP                 |players                       |                    
APP                 |rinks                         |                    
APP                 |schedule                      |                    
APP                 |scoring                       |                    
APP                 |settings                      |                    
APP                 |shootout                      |                    
APP                 |shots                         |                    
APP                 |suspensions                   |                    
APP                 |teams                         |                    
APP                 |threestars                    |                    
APP                 |update_log                    |                    

43 rows selected

Open in new window

So then I check the settings table to make sure it has the rows and I get this error:

ERROR 42X05: Table/View 'SETTINGS' does not exist.

Even tho just above I can see that the table is in the APP schema along with all my other tables.

So I then tried my database class jsut to see if it will work, and of course it doesn't, here it is:


And then I get this in the console:

java.sql.SQLSyntaxErrorException: Table/View 'SETTINGS' does not exist.
	at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
	at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
	at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
	at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
	at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedStatement.executeQuery(Unknown Source)
	at Database.selectRestaurants(Database.java:60)
	at Database.main(Database.java:19)
Caused by: java.sql.SQLException: Table/View 'SETTINGS' does not exist.
	at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
	at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
	... 10 more
Caused by: ERROR 42X05: Table/View 'SETTINGS' does not exist.
	at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
	at org.apache.derby.impl.sql.compile.FromBaseTable.bindTableDescriptor(Unknown Source)
	at org.apache.derby.impl.sql.compile.FromBaseTable.bindNonVTITables(Unknown Source)
	at org.apache.derby.impl.sql.compile.FromList.bindTables(Unknown Source)
	at org.apache.derby.impl.sql.compile.SelectNode.bindNonVTITables(Unknown Source)
	at org.apache.derby.impl.sql.compile.DMLStatementNode.bindTables(Unknown Source)
	at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown Source)
	at org.apache.derby.impl.sql.compile.CursorNode.bindStatement(Unknown Source)
	at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
	at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
	at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
	... 4 more

Open in new window

Thanks for any help you can provide.
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

LVL 47

Expert Comment

ID: 34963619

Can you try to select form another table and so you'll check
if this is something with this particular table?

Also waht  surprises me in your code *though this is probably unrelated)
is that you have three fields
in your select statement (id, variable, value) and then when you analyze resultSet
you retrieve four columns. Perhaps I missed something.

Author Comment

ID: 34963735
Yes you are right about the resultSet.  I have fixed that now but still the same result.

I tried it on another table, rinks, and no go again got this:

ij> connect 'jdbc:derby:software;create=true;user=app;password=golfgolf';
WARNING 01J01: Database 'software' not created, connection made to existing database instead.
ij> select name from rinks;
ERROR 42X05: Table/View 'RINKS' does not exist.

And then I tried the show tables again and it worked and showed all the again fine.

Could it be calling the SYS schema by default somehow?  Is there a way for me to check what schema is using in a query?
LVL 47

Expert Comment

ID: 34963763

This seems to be somthing similar:

Well, yyou explicitly sepcify APP in the query...
LVL 47

Expert Comment

ID: 34963777
Read in that link at the bottom - seems something really relevant
LVL 86

Accepted Solution

CEHJ earned 800 total points
ID: 34963813
I don't see any CREATE TABLE statements ...

Author Comment

ID: 34963916
The CREATE TABLE statements are in the sql file I run with no errors.  Do you wan to see that file?
LVL 86

Expert Comment

ID: 34964353
There are two possible scenarios:

a. You use the same (starting) data in each installation and create it once
b. You create the data each time in code

I thought that you wanted scenario a. (hence my initial comment).

Author Comment

ID: 34972214

You are correct.  I want to create the database prior to deployment.  I just created the sql for ease of use when I ran into this problem in not being able to connect to it.

Ok, I think I have solved my problem.  It had to do with how I was creating the tables in the sql file.  I was creating a table like this:

CREATE TABLE "shots" (
	"game_id" INTEGER NOT NULL, 
	"team_id" INTEGER NOT NULL, 
	"gametime" INTEGER NOT NULL, 
	PRIMARY KEY ("shot_id"))

Open in new window

Apparently the double quotes around the table name and columns throw the database for a loop. The table names and columns are all shown as lower case in this case but when I create the table like this:

	gametime INTEGER NOT NULL, 
	PRIMARY KEY (shot_id))

Open in new window

The names are all upper case and I can select the records fine.  With a select statement liek so:

"SELECT test_id, game FROM test"

Open in new window

Does anyone no why this is?  Thanks

LVL 47

Assisted Solution

for_yan earned 600 total points
ID: 34972942
That makes sense - table names, filed names in sql are ususally not in the
quotes - - I think I mostly used quotes in SQL in the printout names and
also only when those have say embedded space, like:

select x "total purchase amount", y price from this_table

then it will show in the header the many-word string as the haeder of one column, otherwise
in the case of price in this situation you don't need quotes.

But table names and table fields are usually not in the quotes
LVL 92

Assisted Solution

objects earned 600 total points
ID: 34974295
> Does anyone no why this is?

When you quote the tables names when creating the table that tells Derby that the names are case sensitive.
When you do a select without quotes, Derby converts the table name to all upper case

Thus table not found in your case

A rather annoying way that derby handles case

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Suggested Courses

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question