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
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(
	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.
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ejb message driven bean mdb creation steps 2 35
SQL 2008 R2 2 31
sql script with strange password string 1 43
T-SQL Query - Group By Year 3 29
Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
In this post we will learn different types of Android Layout and some basics of an Android App.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

756 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