Solved

Java database program

Posted on 2011-02-23
13
960 Views
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.

Nick
0
Comment
Question by:ninedoors19
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 86

Expert Comment

by:CEHJ
Comment Utility
Just copy *all* files from the template database to the installation you want to use
0
 
LVL 47

Expert Comment

by:for_yan
Comment Utility

This apache tutorial gives detailed instructions how to set up
derby database using Eclipse environment:
http://db.apache.org/derby/integrate/plugin_help/nature.html

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

Author Comment

by:ninedoors19
Comment Utility
Ok,

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
ij>

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:

 Database.java

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.
0
 
LVL 47

Expert Comment

by:for_yan
Comment Utility

Strange...
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.
0
 

Author Comment

by:ninedoors19
Comment Utility
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.
ij>

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?
0
 
LVL 47

Expert Comment

by:for_yan
Comment Utility


This seems to be somthing similar:
http://forum.springsource.org/archive/index.php/t-44248.html

Well, yyou explicitly sepcify APP in the query...
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 47

Expert Comment

by:for_yan
Comment Utility
Read in that link at the bottom - seems something really relevant
0
 
LVL 86

Accepted Solution

by:
CEHJ earned 200 total points
Comment Utility
I don't see any CREATE TABLE statements ...
0
 

Author Comment

by:ninedoors19
Comment Utility
The CREATE TABLE statements are in the sql file I run with no errors.  Do you wan to see that file?
0
 
LVL 86

Expert Comment

by:CEHJ
Comment Utility
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).
0
 

Author Comment

by:ninedoors19
Comment Utility
CEHJ,

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" (
	"shot_id" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, 
	"game_id" INTEGER NOT NULL, 
	"team_id" INTEGER NOT NULL, 
	"gametime" INTEGER NOT NULL, 
	"period" SMALLINT 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:

 
CREATE TABLE shots (
	shot_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, 
	game_id INTEGER NOT NULL, 
	team_id INTEGER NOT NULL, 
	gametime INTEGER NOT NULL, 
	period SMALLINT 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

Nick
0
 
LVL 47

Assisted Solution

by:for_yan
for_yan earned 150 total points
Comment Utility
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
0
 
LVL 92

Assisted Solution

by:objects
objects earned 150 total points
Comment Utility
> 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
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MySQL 11 66
groovy example issue 10 64
How to export photos individually from a OneNote 2016 39 71
Word 2010 mail merge 3 31
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
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 implement Singleton Design Pattern in Java.

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now