Solved

Java database program

Posted on 2011-02-23
13
982 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
[X]
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
13 Comments
 
LVL 86

Expert Comment

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

Expert Comment

by:for_yan
ID: 34961721

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
ID: 34963346
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 47

Expert Comment

by:for_yan
ID: 34963619

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
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.
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
ID: 34963763


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

Expert Comment

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

Accepted Solution

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

Author Comment

by:ninedoors19
ID: 34963916
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
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).
0
 

Author Comment

by:ninedoors19
ID: 34972214
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
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
0
 
LVL 92

Assisted Solution

by:objects
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
0

Featured Post

Webinar: Security & Encryption in the MySQL world

Join Percona’s Solutions Engineer, Dimitri Vanoverbeke as he presents “Security and Encryption in the MySQL world” on Thursday, July 6, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

695 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