7/04/2010

Some basic steps using Derby 10.5

The following is some basic steps of using derby bundled with GlassFish.

1. start derby database server:

$GLASSFISH_HOME/bin/asadmin start-database
or start it on a non-default port number:

$GLASSFISH_HOME/bin/asadmin start-database --dbport 1528
2. make sure environment variable DERBY_HOME is set:
$ echo $DERBY_HOME
/Users/root/v3/glassfish/../javadb
3. start derby ij (or ij.bat), connect to derby server:
$ $DERBY_HOME/bin/ij
ij version 10.5
ij> connect 'jdbc:derby://localhost:1527/sun-appserv-samples;user=APP;password=APP;create=true';

ij> select count(*) COUNT from sys.systables;
COUNT
-----------
24

1 row selected

ij> select OWNERID from EJB__TIMER__TBL;
ij> delete from EJB__TIMER__TBL;

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 |SYSROLES |
SYS |SYSROUTINEPERMS |
SYS |SYSSCHEMAS |
SYS |SYSSTATEMENTS |
SYS |SYSSTATISTICS |
SYS |SYSTABLEPERMS |
SYS |SYSTABLES |
SYS |SYSTRIGGERS |
SYS |SYSVIEWS |
SYSIBM |SYSDUMMY1 |
APP |LEAGUE |
APP |PLAYER |
APP |TEAM |
APP |TEAMPLAYER |

24 rows selected

ij> describe player;
COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
------------------------------------------------------------------------------
PLAYER_ID |VARCHAR |NULL|NULL|255 |NULL |510 |NO
NAME |VARCHAR |NULL|NULL|255 |NULL |510 |YES
POSITION |VARCHAR |NULL|NULL|255 |NULL |510 |YES
SALARY |DOUBLE |NULL|2 |52 |NULL |NULL |NO

4 rows selected

ij> CREATE TABLE FOO (FOO_ID BIGINT, FOO_NAME VARCHAR(25), DESCRIPTION VARCHAR(64));
0 rows inserted/updated/deleted

ij> insert into foo values(1, 'foo1', 'foo1, that is.');
1 row inserted/updated/deleted

ij> select * from foo;
FOO_ID |FOO_NAME |DESCRIPTION
---------------------------------------------------------------------------------------------------------------
1 |foo1 |foo1, that is.

1 row selected

ij> update foo set foo_name='foo1 new name' where foo_id=1;
1 row inserted/updated/deleted

ij> delete from foo where foo_id=1;
1 row inserted/updated/deleted

ij> select count(*) count from foo;
COUNT
-----------
0

1 row selected

ij> drop table foo;
0 rows inserted/updated/deleted

To start ij and connect to a database in single step:
java -classpath $GLASSFISH_HOME/../javadb/lib/derbyrun.jar -Dij.connection.sample="jdbc:derby://localhost:1527/sun-appserv-samples;user=APP;password=APP;create=true" org.apache.derby.tools.ij
When starting derby with GlassFish "asadmin start-database" command, the default host is localhost, the default port number is 1527, and the default derby system home is set to $GLASSFISH_HOME/databases. This is also the directory where derby.log and database files will be stored. To overwrite the defaults with the following options:
asadmin start-database --dbhost dell-01 --dbport 3333 --terse=true --dbhome $HOME/derby

4 comments:

.Net Web Service said...

Very nice and interesting post...
Thank you for the post...

Anonymous said...

THANX A LOT IT WAS VERY HELPFULL

Steve Smith said...

Great and Useful Article.

Online Java Training

Java Online Training India

Java Online Course

Java EE course

Java EE training

Best Recommended books for Spring framework

Java Interview Questions








Java Course in Chennai

Java Online Training India

Vũ Diệu Linh said...

I enjoyed on reading your blog post. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. Please visit my website, Friv 4000 Games is where all the free friv games.
Friv 4000