use derby ij against an existing database

Question

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?

Answer 1

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.

Answer 2

I still don't know how to really query the db itself, but it's fairly simple to connect, apparently:

root@dur:~/apache-james-3.0-beta4/var/store/derby# 
root@dur:~/apache-james-3.0-beta4/var/store/derby# pwd
/root/apache-james-3.0-beta4/var/store/derby
root@dur:~/apache-james-3.0-beta4/var/store/derby# 
root@dur:~/apache-james-3.0-beta4/var/store/derby# ll
total 24
drwxr-xr-x 4 root root  4096 Aug 23 18:24 ./
drwxr-xr-x 5  501 staff 4096 Aug 23 02:07 ../
-rw-r--r-- 1 root root  1092 Aug 23 18:24 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
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> connect 'jdbc:derby:/root/apache-james-3.0-beta4/var/store/derby';
ij> 
;
ij> show schema;
ERROR 42X01: Syntax error: Encountered "show" at line 1, column 1.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> 
;
ij> show schemas;
TABLE_SCHEM                   
------------------------------
APP                           
NULLID                        
SQLJ                          
SYS                           
SYSCAT                        
SYSCS_DIAG                    
SYSCS_UTIL                    
SYSFUN                        
SYSIBM                        
SYSPROC                       
SYSSTAT                       

11 rows selected
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                 |JAMES_DOMAIN                  |                    
APP                 |JAMES_MAIL                    |                    
APP                 |JAMES_MAILBOX                 |                    
APP                 |JAMES_MAIL_PROPERTY           |                    
APP                 |JAMES_MAIL_USERFLAG           |                    
APP                 |JAMES_RECIPIENT_REWRITE       |                    
APP                 |JAMES_SUBSCRIPTION            |                    
APP                 |JAMES_USER                    |                    
APP                 |OPENJPA_SEQUENCE_TABLE        |                    

31 rows selected
ij> quit;
root@dur:~/apache-james-3.0-beta4/var/store/derby#