Overview

Apache Derby is a relational database written in 100% Pure Java. The project, which was originally called Cloudscape™, was contributed to the Apache foundation by IBM in August 2004, and is currently in its incubation phase. This means you have access to an easy-to-use relational database, available free of charge under the Apache Software License.

Although Derby is written in Java, there are times when programs written in other languages need to access a Derby database. Python is an interpreted, high-level, object-oriented dynamically-typed programming language. The fact that the interpreter can be used interactively, or iteratively, provides us with an interesting rapid-prototype language and development/test environment. When combined with a few existing utilities, Python lets you write high-level, dynamic, object-oriented scripts for manipulating Derby relational databases quite easily. This article explains how to do this.

This article is not meant to be an introduction to Python, Apache Derby, or SQL. It will, however, show how to set up a Windows® system so that you can access and manipulate your Derby databases using Python.

Back to top

First steps

The first thing that you need to do is to install and configure Derby to function on a Windows system.

Table 1: Installing Derby

Why install both Derby and Cloudscape?

The main reason for installing Derby, as far as I can see, is that you can get access to the program source, and participate in the review and implementation of an open source project. There will probably be some differences between the "current" version of Cloudscape available from IBM, and the absolute "latest" version of Derby available from Apache. At this point, there may not be much of a difference. At some time, there may. So, you can decide whether to install Derby, or Cloudscape, or both.

There are a number of reasons for installing IBM Cloudscape. I find the most compelling to be the fact that IBM Cloudscape includes:

• Documentation in PDF format (which makes for easier searching)
• Sample programs
• Sample databases
• The necessary "IBM DB2 JDBC Universal Driver" (for example: db2jcc*.jar) jar files.
• Command files for executing the utilities, for example:
  
  

  dblook

  Data Definition Language (DDL) Generation Utility (also called a schema dump tool)

  ij

  Interactive JDBC scripting tool for running scripts against a database

  sysinfo

  Utility which provides information about your version of Cloudscape and your environment

  *NetworkServer*.bat

  Command files for starting and stopping Cloudscape as a Network Server

Derby directory structure & contents:

After installing Derby, the directory structure should contain the following:

Back to top

Starting the Derby Network server

For background information about configuring and using a Derby Network Server, take a look at the Derby documentation. Those of you, like myself, who are interested in quickly getting things up and running, can use the Cloudscape script files as an example, and write your own simple Derby Server scripts that start and stop your Derby database.

To make things even easier, I've included all of the following script files in the sample download file.

There are three ways to start the Derby Network Server program:

1. Manually (for example: setting up the CLASSPATH environment variable, and invoking the Java code to execute the NetworkServerControl class file with a "start" parameter). For example:

   set CLASSPATH=C:\Derby\lib\derby.jar;C:\Derby\lib\derbytools.jar;C:\Derby\lib\derbynet.jar cd C:\Derby\databases java org.apache.derby.drda.NetworkServerControl start

   
   

2. Using the NetworkServerControl.bat file, and specifying "start" as a parameter. For example:

   NetworkServerControl start

   
   

3. Using the startNetworkServer.bat file. Please note that since everything is done by this script, you could easily have a shortcut to this script on your desktop, or your quick launch toolbar. So, you could either use the shortcut icon, or you could start the script from a command prompt. For example:

   startNetworkServer

   
   

One attribute that seems to be shared among programmers is the propensity to look for, write, and use "quick and easy" ways to do things. In a way, that is what this article is all about. Since Windows batch (*.bat) files are a form of scripting, it is easy to understand why people are willing to have all of the "little details" identified and handled by a script file. Not only is it easy to use, it is also easier to remember.

This script file is invoked by other script files to add the appropriate Derby Java Archive (jar) files to the front of the CLASSPATH environment variable. Hence, the file name (for example: Set Network Server ClassPath):

set DERBY=C:\Derby

set DERBY_JARS=%DERBY%\lib\derby.jar
set DERBY_JARS=%DERBY_JARS%;%DERBY%\lib\derbytools.jar
set DERBY_JARS=%DERBY_JARS%;%DERBY%\lib\derbynet.jar

set CLASSPATH=%DERBY_JARS%;%CLASSPATH%

This script file executes the setNetworkServerContol program, passing any user-specified parameters. This allows us to have a common script file that can be used to issue any of the supported Network Control commands:

@ECHO OFF
setlocal
Title Derby: NetworkServerControl
cd C:\Derby\databases
call C:\Derby\bin\setNetworkServerCP.bat
java org.apache.derby.drda.NetworkServerControl %*
Title Windows command prompt
endlocal

This specialized script file can only be used to start the Derby Network Server. Much like the preceding script file, the NetworkServerControl program is invoked, having "start" specified as the only parameter:

@ECHO OFF
setlocal
Title Derby Network Server
cd C:\Derby\databases
call C:\Derby\bin\setNetworkServerCP.bat
java org.apache.derby.drda.NetworkServerControl start
Title Windows command prompt
endlocal

Regardless of the technique used to start Derby as a Network Server, if it is able to successfully start, the program will wait for connection requests from clients. As each connection is received, a message indicating this will be displayed.

Stopping the Derby Network Server

There are three (reasonable) ways to stop the Derby Network Server program:

1. Manually (for example: setting up the CLASSPATH environment variable, and invoking the Java code to execute the NetworkServerControl class file with a "shutdown" parameter). For example:

   set CLASSPATH=C:\Derby\lib\derby.jar;C:\Derby\lib\derbytools.jar;C:\Derby\lib\derbynet.jar java org.apache.derby.drda.NetworkServerControl shutdown

   
   

2. Using the NetworkServerControl.bat file, and specifying "shutdown" as a parameter. For example:

   NetworkServerControl shutdown

   
   

3. Using the stopNetworkServer.bat file. As with the startNetworkServer.bat, you could have a shortcut to it on your desktop, or your quick launch toolbar. So, you could either use the shortcut icon, or you could run the script from a command prompt. For example:

   stopNetworkServer

   
   

Note: Even though it is possible to terminate the Derby Network Server program or process window abnormally, doing this could possibly corrupt your database, and therefore should be highly discouraged.

To learn more about the actions that can be performed by NetworkServerControl program, you could look to the Derby documentation. Another way is to execute NetworkServerControl without specifying parameters. Doing this will cause it to display the usage information. As you can see in Figure 1, the parameter to use when requesting the Derby Network Server to stop is shutdown.

C:\Derby\bin> NetworkServerControl
No arguments given.
Usage: NetworkServerControl <commands>
Commands:
start [-h <host>] [-p <portnumber>]
shutdown [-h <host>][-p <portnumber>]
ping [-h <host>][-p <portnumber>]
sysinfo [-h <host>][-p <portnumber>]
runtimeinfo [-h <host>][-p <portnumber>]
logconnections {on|off}[-h <host>][-p <portnumber>]
maxthreads <max>[-h <host>][-p <portnumber>]
timeslice <milliseconds>[-h <host>][-p <portnumber>]
trace {on|off} [-s <session id>][-h <host>][-p <portnumber>]
tracedirectory <traceDirectory>[-h <host>][-p <portnumber>]

C:\Derby\bin> NetworkServerControl start
Server is ready to accept connections on port 1527.

*** Execute the following in a separate command window ***

C:\Derby\bin> NetworkServerControl ping
Connection obtained for host: localhost, port number 1527.

C:\Derby\bin> NetworkServerControl shutdown
Shutdown successful.

Back to top

Connecting to the Derby Network Server

As you saw in Figure 1, you can use NetworkServerControl for more than starting the Derby Network server. You can also use it to demonstrate that connectivity can be made (for example: via a "ping" request). Derby clients need to use a different interface than that used by the NetworkServerControl program. The interface provided for clients is a Java Database Connectivity (JDBC) interface. Before clients can connect to the Derby Network Server using this JDBC interface, you need to obtain a connectivity driver that supports this interface.

Support for this is provided by two JAR files:

1. db2jcc.jar
2. db2jcc_license_c.jar

Fortunately there are two places from which these files may be obtained.

1. You can either retrieve them from developerWorks:
   • Go to the "IBM DB2 JDBC Universal Driver, for Apache Derby Network Server" article.
   • Click the db2jcc_for_derby.zip link.
   • Click the Released product: IBM Cloudscape (IBM DB2 JDBC Universal Driver, for Cloudscape/Derby) link.

   • An advantage to installing Cloudscape is the fact that it includes all the necessary files, as well as documents how they should be used in the batch (*.bat) files.

     For those of you who are wondering, the JCC in the file names stands for Java Common Client.

     The lsPath command used in Figure 2 is, in fact, a Python program that is discussed in my Jython article.

     Click Continue.
   • Register, if necessary.
   • Click Sign-in.
   • Fill in your user ID and password, then click Submit.
   • Read the license agreement, then click I agree.
   • Download the db2jcc_for_derby.zip to your machine.
   • Run a virus scan on the downloaded file.
   • Extract the file contents.
2. Or, if you installed Cloudscape, you can simply copy the files from the C:\Cloudscape\lib directory.

In either case, the files (db2jcc.jar, and db2jcc_license_c.jar) need to be copied to the C:\Derby\lib directory.

C:\Derby> lsPath classpath
CLASSPATH=
  C:\Derby\lib\derby.jar
  C:\Derby\lib\derbytools.jar
  C:\Derby\lib\derbynet.jar
  C:\Derby\lib\db2jcc.jar
  C:\Derby\lib\db2jcc_license_c.jar

Using the ij utility

Now you can use one of the other utilities provided with Derby -- specifically, the ij utility -- to do more than simply connect to the Derby Network Server. In order to figure out how to run the ij utility, you can again take advantage of the scripts that are provided with Cloudscape. In addition to reviewing and studying the ij script, I copied the sample databases from C:\Cloudscape\demo\databases to the corresponding location under C:\Derby. For example:

C:\> xcopy C:\Cloudscape\demo\databases C:\Derby\databases /s /e
Does C:\Derby\databases specify a file name
or directory name on the target
(F = file, D = directory)? d
...
135 File(s) copied

C:\Derby\bin> ij
ij version 10.0 (C) Copyright IBM Corp. 1997, 2004.
ij> connect 'jdbc:derby:net://localhost:1527/sample';
ij> select * from org;
DEPTN&|DEPTNAME      |MANAG&|DIVISION  |LOCATION
-----------------------------------------------------
10    |Head Office   |160   |Corporate |New York
15    |New England   |50    |Eastern   |Boston
20    |Mid Atlantic  |10    |Eastern   |Washington
38    |South Atlantic|30    |Eastern   |Atlanta
42    |Great Lakes   |100   |Midwest   |Chicago
51    |Plains        |140   |Midwest   |Dallas
66    |Pacific       |270   |Western   |San Francisco
84    |Mountain      |290   |Western   |Denver

8 rows selected
ij> disconnect all;
ij> exit;

Back to top

The DB2 Runtime Client

The JDBC API that is provided by Derby is, not surprisingly, most useful for Java programs. If you want to connect to Derby using the JDBC Application Programming Interface (API), you should consider using Jython instead of Python.

Fortunately, there is an alternative. Susan Cline's developerWorks article, "Cloudscape and ODBC" (see Resources) describes how to use the DB2 Runtime Client to connect to the Derby Network Server using the JDBC interface and to provide an Open DataBase Connectivity (ODBC) interface to other programs that support it. ODBC is an open database access method which defines how programs can access and manipulate information in a database.

This article contains a Windows downloads section. Use the "DB2 Run-Time Client Lite, Version 8.2" link to access the installation instructions (see Part 3 of the readme file). Since you have already installed Derby, and copied the sample databases from Cloudscape, and used the ij utility to verify the JDBC connectivity to the Derby Network Server, you are now ready to configure the DB2 Runtime client.

Table 2: Setting up the DB2 Runtime Client

db2 => catalog tcpip node derby remote localhost server 1527
DB20000I  The CATALOG TCPIP NODE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is
refreshed.

db2 => catalog db sample at node derby authentication server
DB20000I  The CATALOG DATABASE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is
refreshed.

db2 => catalog db toursdb at node derby authentication server
DB20000I  The CATALOG DATABASE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is
refreshed.

db2 => connect to sample user SAMP using SAMP

   Database Connection Information

 Database server        = Apache Derby CSS10000
 SQL authorization ID   = SAMP
 Local database alias   = SAMPLE

db2 => select * from org

DEPTNUMB DEPTNAME       MANAGER DIVISION   LOCATION
-------- -------------- ------- ---------- -------------
      10 Head Office        160 Corporate  New York
      15 New England         50 Eastern    Boston
      20 Mid Atlantic        10 Eastern    Washington
      38 South Atlantic      30 Eastern    Atlanta
      42 Great Lakes        100 Midwest    Chicago
      51 Plains             140 Midwest    Dallas
      66 Pacific            270 Western    San Francisco
      84 Mountain           290 Western    Denver

  8 record(s) selected.

db2 => disconnect all
DB20000I  The SQL DISCONNECT command completed successfully.

db2 => quit
DB20000I  The QUIT command completed successfully.

C:\Program Files\IBM\SQLLIB\bin> exit

Back to top

Configuring ODBC

In order to access the DB2 configuration that you just defined, there is some additional ODBC configuration required.

1. Open the Windows Control Panel, using either:
   • Start > Settings > Control Panel or
   • Start > Control Panel
2. Select Administrative Tools, and open the ODBC Data Source Administrator:
3. On the User DSN tab, click Add.
4. Scroll down, and select the IBM DB2 ODBC DRIVER:
5. Click Finish.
6. In the IBM DB2 ODBC DRIVER - Add dialog box:
   1. Specify a Data source name (DSN) of sample.
   2. In the Database alias drop-down, select SAMPLE.
   3. Optionally specify a Description.
   4. Click OK.
7. You can use these same steps to define a Data source name for the toursDB database as well (of course using the appropriate database alias).
8. Click OK.

Back to top

Installing Python

You were probably wondering how long it was going to take me to get to the point of getting Python set up. Well, it's finally time for that.

Table 3: Installing Python

A little something extra

Now that you have Python installed, you can use it write some interesting and very useful scripts. One of the commands that I find myself using all of the time on Unix™ is the which command. For those who are unfamiliar with this command, it is used to identify the program that would be executed if you were to type the command.

For example: which java would display the complete path to the java executable.

Unfortunately, the which command isn't something that is available on Windows. You could search for it on the Internet, there are probably lots of implementations of this utility that are readily available. Or, you can see what would be needed to implement it in Python.

Here is an uncommented version of that program, based upon one provided with the Python installation. The commented version is available in the download section below.

How it works

It uses the values from the PATH and PATHEXT environment variables to search for a file with the specified name that exists in one of the directories in the %PATH% that ends with one of the executable extensions specified in %PATHEXT%. If one is found, the complete path to that executable is displayed.

C:\> type C:\Python24\which.py

import sys, os
from stat import *

def msg( str ):
    sys.stderr.write( str + '\n' )

def main():
    pathlist = os.environ[ 'PATH' ].split( os.pathsep )
    pathext  = os.environ[ 'PATHEXT' ].split( os.pathsep )

    if pathlist[ 0 ] != '.' :
        pathlist.insert( 0, '.' )

    for prog in sys.argv[ 1: ] :
        try :
            for dir in pathlist :
                filename = os.path.join( dir, prog )
                for ext in pathext :
                    program = filename + ext
                    try:
                        st = os.stat( program )
                        raise Exception( program )
                    except os.error:
                        continue
            msg( prog + ': not found' )
        except Exception :
            msg( program )

if __name__ == '__main__':
    main()

Let's find out what program gets executed
when you type "java" at the command prompt.

C:\> which java
C:\WINDOWS\system32\java.exe

Back to top

The missing link

At this point you have almost everything you need in order to access your Derby databases with Python scripts. The final piece that you need is a Python module that provides database support using the ODBC interface. The topic section on the Python Web site contains information about accessing databases using Python. The Database Modules link on this page includes that missing link (pun intended). The mxODBC package is a "... nearly 100% Python DB API compliant interface to databases that are accessible via the ODBC API."

Installing the mxODBC package

1. From the eGenix.com mx Extensions for Python page, click the Download link for the Base Package.
2. Click the Windows installer, Python 2.4 link.
3. Save the download file (for example: egenix-mx-base-2.0.6.win32-py2.4.exe).
4. Use your browser's back button, then select the Download link from the Commercial Package.
5. Click the Windows installer, Python 2.4 link.
6. Save the download file (for example: egenix-mx-commercial-2.0.7.win32-py2.4.exe).
7. Scan the downloaded files for viruses.
8. After a successful scan, execute each installer program.

On some systems, particularly Windows 2000 systems, you may see a warning during the installation of the mxODBC package telling you that the MDAC SDK software needs to be upgraded. If this is the case, you can use the following procedure to install the latest version of this code from Microsoft®.

1. Access the Microsoft ODBC page.
2. Select Downloads.
3. Click the MDAC 2.8 link.
4. Click the Continue button.
5. Optionally, validate your system, and click the Continue button.
6. Click the Download button.
7. Save the install executable (for example: MDAC_TYP.exe) on your system.
8. Run a virus scan of the install program.
9. After a successful scan, execute the install program.
10. Read the license agreement, select "I accept...", and click Next.
11. Click Finish to begin installation.
12. After the installation, reboot your machine.

Back to top

Using Python to interactively connect to Derby

Now you can try the same thing with the Python interpreter that you did earlier using the ij utility, and the DB2 CLP. This will show you how easy it is to use Python to connect to a Derby database. Remember to start the network server first, or you may get an error message.

Table 4: Connection keyword parameters

First, however, you'll need to know some parameters that are used in the connection request:

C:\> python
ActivePython 2.4.1 Build 245 (ActiveState Corp.) based on
Python 2.4.1 (#65, Mar 30 2005, 09:33:37) [MSC v.1310 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import mx.ODBC
>>> import mx.ODBC.Windows
>>> db = mx.ODBC.Windows.DriverConnect( 'DSN=sample;UID=SAMP;PWD=SAMP' )
>>> cursor = db.cursor()
>>> cursor.execute('select * from org')
>>> mx.ODBC.print_resultset( cursor )
Column 1 | Column 2         | Column 3 | Column 4    | Column 5
----------------------------------------------------------------------
10       | 'Head Office'    | 160      | 'Corporate' | 'New York'
15       | 'New England'    | 50       | 'Eastern'   | 'Boston'
20       | 'Mid Atlantic'   | 10       | 'Eastern'   | 'Washington'
38       | 'South Atlantic' | 30       | 'Eastern'   | 'Atlanta'
42       | 'Great Lakes'    | 100      | 'Midwest'   | 'Chicago'
51       | 'Plains'         | 140      | 'Midwest'   | 'Dallas'
66       | 'Pacific'        | 270      | 'Western'   | 'San Francisco'
84       | 'Mountain'       | 290      | 'Western'   | 'Denver'
>>> cursor.close()
>>> db.close()
>>>

To exit the Python interpreter, press Ctrl-Z.

>>> ^Z

Now that you have verified that things work as expected, you can
easily copy these same statements that you just tested interactively
into a file, e.g., mxORGtable.py, and use the python interpreter
to execute the script with no extra effort.  For example:

C:\> type mxORGtable.py
import mx.ODBC
import mx.ODBC.Windows
db = mx.ODBC.Windows.DriverConnect( 'DSN=sample;UID=SAMP;PWD=SAMP' )
cursor = db.cursor()
cursor.execute('select * from org')
mx.ODBC.print_resultset( cursor )
cursor.close()
db.close()

C:\> mxORGtable
Column 1 | Column 2         | Column 3 | Column 4    | Column 5
----------------------------------------------------------------------
10       | 'Head Office'    | 160      | 'Corporate' | 'New York'
15       | 'New England'    | 50       | 'Eastern'   | 'Boston'
20       | 'Mid Atlantic'   | 10       | 'Eastern'   | 'Washington'
38       | 'South Atlantic' | 30       | 'Eastern'   | 'Atlanta'
42       | 'Great Lakes'    | 100      | 'Midwest'   | 'Chicago'
51       | 'Plains'         | 140      | 'Midwest'   | 'Dallas'
66       | 'Pacific'        | 270      | 'Western'   | 'San Francisco'
84       | 'Mountain'       | 290      | 'Western'   | 'Denver'

C:\>

Which is exactly where you want to be.

Back to top

Summary

In this article, I have shown that with a bit of configuration work, you can set up a Windows environment that allows us to access Apache Derby, which is an easy to use, open-source relational database. In addition, you can develop high-level, dynamic, object-oriented programs in Python that allow us to connect to and manipulate a Derby.

Back to top

Download

Description	Name	Size	Download method
Archive of all sample programs	Python2DerbySamples.zip	3.8MB	FTP | HTTP | Download Director

Information about download methods

About the author

Bob Gibson is an Advisory Software Engineer who has over a quarter century of experience in numerous software related roles at IBM, including: Application Programmer, Architect, Developer, Instructor, Technical Support Analyst, and Tester. He is currently a team leader for the technical support group responsible for IBM WebSphere Distributed Application Server. He holds both a Bachelor of Science degree in Engineering Science and a Master of Science degree in Computer Science from the University of Virginia.

Rate this article

Comments

Back to top

Table of contents

• Overview
• First steps
• Starting the Derby Network server
• Connecting to the Derby Network Server
• The DB2 Runtime Client
• Configuring ODBC
• Installing Python
• The missing link
• Using Python to interactively connect to Derby
• Summary
• Download
• Resources
• About the author
• Comments

Local resources

• IBM Innovation Center - Austin, TX
• IBM Innovation Center - Chicago, IL
• IBM Innovation Center - Dallas, TX
• IBM Innovation Center - San Mateo, CA
• IBM Innovation Center - Waltham, MA
• Technical Briefings in North America

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers