Java database program

Posted on 2011-02-23
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
  • 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(
	at Database.main(
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.
Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

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 200 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 150 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 150 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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL replication over high latency link 10 60
going to wrong jsp page 2 33
SequenceInputStream example 3 19
Bot application - advice 3 38
Creating and Managing Databases with phpMyAdmin in cPanel.
This article will shed light on the latest trends when it comes to your resume building needs. For far too long, the traditional CV format has monopolized the recruitment market.
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
This video teaches viewers about errors in exception handling.

837 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