Search code examples
databaseubuntucommand-line-interfacederby

use derby ij against an existing database

While I'm having some trouble installing and setting up derby, I do have partial success:

root@dur:~/apache-james-3.0-beta4/var/store/derby# 
root@dur:~/apache-james-3.0-beta4/var/store/derby# ll
total 36
drwxr-xr-x 5 root root  4096 Aug 23 16:46 ./
drwxr-xr-x 5  501 staff 4096 Aug 23 02:07 ../
-rw-r--r-- 1 root root     4 Aug 23 02:07 dbex.lck
-rw-r--r-- 1 root root    38 Aug 23 02:07 db.lck
-rw-r--r-- 1 root root   187 Aug 23 16:46 derby.log
drwxr-xr-x 2 root root  4096 Aug 23 02:07 log/
drwxr-xr-x 2 root root  4096 Aug 23 02:07 seg0/
-rw-r--r-- 1 root root   868 Aug 23 02:07 service.properties
drwxr-xr-x 2 root root  4096 Aug 23 02:07 tmp/
root@dur:~/apache-james-3.0-beta4/var/store/derby# 
root@dur:~/apache-james-3.0-beta4/var/store/derby# java org.apache.derby.tools.ij
ij version 10.9
ij> quit;
root@dur:~/apache-james-3.0-beta4/var/store/derby#

How do I now query this particular database?

Solution

  • A full example, using Derby and Squirrel, presuming you have a Derby database already installed on the system:

    1. Install Squirrel SQL into $HOME/bin/squirrel
    2. Install Apache Derby into $HOME/bin/db-derby
    3. Run Squirrel (cd $HOME/bin/squirrel ; ./squirrel-sql.sh)
    4. Click Drivers to open the Drivers panel.
    5. Double-click Apache Derby Embedded.
    6. Click Extra Class Path.
    7. Click Add.
    8. Choose $DERBY_HOME/lib/derby.jar.
    9. Set Example URL to the database path (e.g., the directory containing seg0).
    10. Click OK to save.

    At this point the Apache Derby Embedded icon should no longer have a red X.

    Then:

    1. Click the Aliases tab.
    2. Set Name to: DerbyDB
    3. Ensure Driver is set to: Apache Derby Embedded
    4. Verify the URL is pointing to the directory containing seg0.
    5. Click OK (no username or password needed?).
    6. Click Connect.

    The database is now connected and you should see the schemas.

    Then:

    1. Press Ctrl+n to display a new SQL editor window, or click the SQL tab.
    2. Type: SELECT * FROM SYS.SYSTABLES
    3. Click the Running human icon (or press Ctrl+Shift+Enter) to execute the query.

    You can now execute SQL statements against the Derby database.

    Note: This does not technically answer the question, as this answer does not explain how to use the interactive command-line interface (ij). The answer proposes a viable alternative to querying the database using Squirrel's graphical interface.