Scout/HowTo/3.9/Modify a derby database

The Scout documentation has been moved to https://eclipsescout.github.io/.

The following how-to explains how you can add other columns to an existing table in your database. The database used in this example is the database from the MiniCrm Tutorial

Download and install Derby

Access Derby database
Eclipse Scout comes with the JDBC drivers and this tutorial comes with a database but in order to make changes to the database you need to download Derby itself.

Install a binary distribution such as db-derby-10.9.1.0-bin.zip. Switch to the bin subdirectory and start ij.bat. Connect to your database using the same connect string you used for the SQL service.

ij version 10.9
ij> connect 'jdbc:derby:c:/derbydb';
ij>

Troubleshooting
ERROR XJ040: Failed to start database 'c:/derbydb' with class loader sun.misc.Launcher$AppClassLoader...

ERROR XSDB6: Another instance of Derby may have already booted the database C:\DerbyDB

These error messages indicate that you might still have your Scout server running. It is locking the database. Stop the server in the Scout perspective and try again.

Operations on the database

Once connected here is how to examine the tables available:

 ij> show schemas;
 TABLE_SCHEM
 ------------------------------
 APP
 MINICRM
 ...

 ij> show tables in minicrm;
 TABLE_SCHEM         |TABLE_NAME                    |REMARKS
 ------------------------------------------------------------------------
 MINICRM             |COMPANY                       |
 MINICRM             |COMPANY_FIGURES               |
 MINICRM             |CRM_SEQ                       |
 ...
 
 ij> describe minicrm.company;
 COLUMN_NAME         |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
 ------------------------------------------------------------------------------
 COMPANY_NR          |DECIMAL  |0   |10  |5     |NULL      |NULL      |NO
 SHORT_NAME          |VARCHAR  |NULL|NULL|60    |NULL      |120       |NO
 ...

This is how you might have added the missing column:

ALTER TABLE minicrm.company ADD COLUMN my_new_column int;

Finally:

 ij> disconnect;
 ij> exit;