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