Connect to Apache Derby databases using Jython

Programming in Jython allows you to take advantage of the elegance of the Python programming language while maintaining access to Java packages and features. Learn how easy it is to use Jython to access and manipulate Apache Derby relational databases.

Share:

Bob Gibson (bgibson@us.ibm.com), Advisory software engineer, IBM

Author photoBob Gibson is an Advisory Software Engineer who has over a quarter-century experience in numerous software-related roles at IBM, including Architect, Developer, Tester, Instructor, and Technical Support. He is currently a team leader for the technical support group responsible for IBM's 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.



17 February 2005

Overview

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

Jython is a 100% Pure Java implementation of the Python programming language. When used with Derby, we have the tools needed to write high-level, dynamic, object-oriented scripts that seamlessly integrate with the Java platform. This article shows how you how easily this can be accomplished.

This article is not meant to be an introduction to Jython, Apache Derby, or SQL. It will, however, show how to use existing freely available technology, Jython, to manipulate Derby relational databases using two different techniques.


Preparation

Follow these steps to configure a simple environment on a Windows® XP system.

Table 1: Installation/initialization steps
Action TakenDescriptionComment/Result
Install Javaverion 1.4.2 from developerWorksInstalled to (something like) C:\j2sdk1.4.2
Verify installOpen a command prompt, and execute:

...> java -Version

Result (something like):

java version "1.4.2"

Install Jythonverion 2.1 from Jython.orgInstalled to C:\Jython
Verify installOpen a command prompt, and execute:

...> C:\Jython\jython.bat --version

Result (something like):

Jython 2.1 on java

Define file associationAt a command prompt, execute:

...> assoc .jy=Jython.File

Defines file extension association
Verify file associationAt a command prompt, execute:

...> assoc .jy

Result:

.jy=Jython.File

Define file type commandAt a command prompt, execute:

...> ftype Jython.File="C:\Jython\jython.bat" "%1" %*

Defines how to "execute" a Jython command
Verify file type commandAt a command prompt, execute:

...> ftype Jython.File

Result:

"C:\Jython\jython.bat" "%1" %*

Define ".jy" as an executable file extension
  1. Click the Start button
  2. Right-click the My Computer entry
  3. Select Properties
  4. Select the Advanced tab
  5. Click on the Environment Variables button
  6. In the "System Variables" section, scroll down and select the PATHEXT entry
  7. Click on the Edit button
  8. Add ;.jy to the end of the input field
  9. Click on the OK button to close the "Edit System Variable" dialog box
  10. Click on the OK button to close the "Environment Variables" dialog box
  11. Click on the OK button to close the "System Properties" dialog box
Adds to the list of "executable" file extensions
Verify PATHEXT valueOpen a new command prompt, and execute:

...> set pathext

Result (something like):

PATHEXT=.com;.exe;.bat;...;.jy

Install Apache Derby files
  1. Point browser to the Apache Derby Project page
  2. Select the Downloads link
  3. Select the Official Release download link
  4. Select the appropriate download (for example, incubating-derby-10.0.2.1-bin.zip)
  5. Perform a virus scan of the downloaded files
  6. Unzip (or Untar) the downloaded file
  7. Copy or move the expanded directory structure to C:\Derby
Derby files installed into C:\Derby
Optional: Install IBM Cloudscape files
  1. Point your browser to Susan Cline's article on developerWorks.
  2. Select the link appropriate for the platform version you want to download
  3. Register, if you don't already have a valid userid and password
  4. Download the installation and instruction files
  5. Perform virus scan of the downloaded files
  6. Use the installation instructions to install the software (e.g., to C:\Cloudscape)
Cloudscape files installed into C:\Cloudscape

Why install Cloudscape?

There are a number of reasons to install both IBM Cloudscape and Apache Derby. I find the most compelling reason to be the fact that IBM Cloudscape includes:

  • Documentation in PDF format (which makes for easier searching)
  • Sample programs
  • Sample databases
  • Command files for executing the utilities, for example:
    FileDescription
    dblookData Definition Language (DDL) Generation Utility (also called a schema dump tool).
    ijinteractive JDBC scripting tool for running scripts against a database.
    sysinfoUtility that provides information about your version of Cloudscape and your environment.
    *NetworkServer*.batcommand files starting and stopping Cloudscape as a Network Server.

lsPath.jy - a Jython script

This script does not use Derby, but it will help you to verify your installation, and will prove useful as well. I find it difficult to read environment variable values like PATH and CLASSPATH because they contain a number of entries (i.e., directory and maybe file names) separated with a single delimiter character. I wrote the lsPath script to display each individual entry on a separate line.

I was able to write this script to be platform independent, which means that it works on both UNIX® and Windows® systems.

Unfortunately, there is a minor annoyance with scripts such as lsPath.jy on Windows. If we want to redirect the output on UNIX, we simply use something intuitive, like:

...> lsPath >outputfile

This syntax will not work in the Windows, due to the way that the Windows command interpreter (i.e., cmd.exe) handles redirection for file-type associations like we defined in Table 1. However, there is a work-around. Instead of using the shorthand notation that we defined to invoke the script, we type out the entire command:

...> C:\Jython\Jython lsPath.jy >outputfile

If the directory containing Jython.bat (e.g., C:\Jython) is in the PATH environment variable, it is even simpler:

...> Jython lsPath.jy >outputfile

Listing 1: lsPath.jy
"""lsPath - Display delimited environment variables in an easy to read format"""

from os  import environ, path, pathsep
from sys import argv

Name  =  path.split( argv[ 0 ] )[ 1 ] 
if not Name : Name = "lsPath.jy"
Usage = "Usage: jython " + Name + " [Path-Environment-Variable]"

def main( EnvVar = None ) :
  """lsPath()
Display Environment variable values in an easier to read format"""
  if EnvVar == None :
    EnvVar = "PATH"
  else :
    EnvVar = EnvVar.upper()
  try :
    Path = environ[ EnvVar ].split( pathsep )
    print EnvVar + "="
    for dir in Path :
      print "  " + dir
  except :
    print "Unknown ENV variable: " + EnvVar
  
if __name__ == "__main__" :
  if len( argv ) == 1 :
    main()
  elif len( argv ) == 2 :
    main( argv[ 1 ] )
  else :
    print Usage

Using this script, we can verify that Jython and Java have been successfully installed. Also notice how much easier it is to read and understand the output of the progam than it is to try and figure out environment variable values using the Windows SET command.

Figure 1: Comparing SET and lsPath.jy output
C:\Programs\Jython> set PATH
Path=C:\Python24;C:\WINDOWS;C:\WINDOWS\system32;C:\WINDOWS\System32\Wbem;C:\PROG
RA~1\IBM\INFOPR~1;C:\PROGRA~1\ThinkPad\Utilities;C:\PROGRA~1\IBM\TRACEF~1;C:\PRO
GRA~1\PERSON~1;C:\PROGRA~1\IBM\SQLLIB\BIN;C:\PROGRA~1\IBM\SQLLIB\FUNCTION;C:\j2s
dk1.4.2_01\bin;C:\Utilities
PATHEXT=.com;.exe;.bat;.cmd;.py;.pyo;.pyc;.pyw;.jy

C:\Programs\Jython> lsPath
PATH=
  C:\Python24
  C:\WINDOWS
  C:\WINDOWS\system32
  C:\WINDOWS\System32\Wbem
  C:\PROGRA~1\IBM\INFOPR~1
  C:\PROGRA~1\ThinkPad\Utilities
  C:\PROGRA~1\IBM\TRACEF~1
  C:\PROGRA~1\PERSON~1
  C:\PROGRA~1\IBM\SQLLIB\BIN
  C:\PROGRA~1\IBM\SQLLIB\FUNCTION
  C:\j2sdk1.4.2_01\bin
  C:\Utilities

C:\Programs\Jython> lsPath pathext
PATHEXT=
  .com
  .exe
  .bat
  .cmd
  .py
  .pyo
  .pyc
  .pyw
  .jy

C:\Programs\Jython> set classpath
CLASSPATH=.;C:\PROGRA~1\IBM\SQLLIB\java\db2java.zip;C:\PROGRA~1\IBM\SQLLIB\java\
db2jcc.jar;C:\PROGRA~1\IBM\SQLLIB\java\sqlj.zip;C:\PROGRA~1\IBM\SQLLIB\bin;C:\PR
OGRA~1\IBM\SQLLIB\java\common.jar

C:\Programs\Jython> lsPath classpath
CLASSPATH=
  .
  C:\PROGRA~1\IBM\SQLLIB\java\db2java.zip
  C:\PROGRA~1\IBM\SQLLIB\java\db2jcc.jar
  C:\PROGRA~1\IBM\SQLLIB\java\sqlj.zip
  C:\PROGRA~1\IBM\SQLLIB\bin
  C:\PROGRA~1\IBM\SQLLIB\java\common.jar

Starting a Derby Network Server

Now that we have Jython working, we turn to the Apache Derby database. First we see how to use Derby as a Network Server, and later we see how to use embedded Derby databases. The reasons and circumstances associated with determining whether to use an embedded or network server database are outside the scope of this article. For that kind of information it would be best if you started by reading the Derby documentation.

Using the Cloudscape™ script files as an example, we can write our own simple Derby Server scripts that start and stop our Derby database. To start the Derby Network Server program, invoke the following script and specify "start" as a parameter. For example: ...> NetworkServerControl start

It is best if the fully qualified path to the Apache Derby installation doesn't contain blanks. If the path contains blanks (e.g., C:\Program Files\Derby), then the short name (or tilda) form should be used instead (i.e., C:\PROGRA~1\Derby). See the setNetworkServerCP.bat that comes with Cloudscape for how this can be done dynamically within a script.

The TITLE command is used to change the information displayed on the window title bar to show what action is being performed in the window.

The primary reason for having the Derby directory as the current working directory ensures that all Derby databases are located here.

Listing 2: NetworkServerControl.bat
@ECHO OFF

TITLE NetworkServerControl: %*

setlocal
set DERBY=C:\Derby
set LIB=%DERBY%\lib
set CP=%LIB%\derby.jar;%LIB%\derbytools.jar;%LIB%\derbynet.jar

cd %DERBY%

java -cp "%CP%" org.apache.derby.drda.NetworkServerControl %*
endlocal

TITLE Command prompt

Stopping the Derby Network Server

To determine what options exists for the NetworkServerControl program, we invoke it with no arguments. When we do that, we can see that in order to stop the Derby Network Server requires that we specify "shutdown" as the parameter. For example: ...> NetworkServerControl shutdown

Please note that when we start the Derby Network Server, it continues to wait for incoming connection requests until it is stopped. So, we have to open a separate command window in order to invoke the NetworkServerControl program to execute other requests. We need another command window, even to stop or shut down, the Derby Network Server.

One of the benefits of naming the batch (.bat) file using the Java class being invoked (i.e., NetworkServerControl) is realized when we specify an unrecognized parameter to the script. The documentation needed to resolve the problem will, therefore, be associated with the matching class.

Figure 2: NetworkServerControl - Usage information
C:\Derby> 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> NetworkServerControl start
Server is ready to accept connections on port 1527.

*** Open a separate command window to execute the following ***

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

C:\Derby> NetworkServerControl shutdown
Shutdown successful.

Connecting to the Derby Network Server

As we saw in Figure 2, we can use NetworkServerControl to not only have the Derby Network server wait for incoming connection requests, but also demonstrate that the connection requests are possible. Jython scripts need something else before they are able to connect to the Derby Network Server. What we need is a JDBC driver. Fortunately for us, a suitable one is readily available from developerWorks.

After retrieving the JDBC driver zip file, and expanding it into the lib directory under Derby, we need to update our CLASSPATH to include these driver files.

To update the CLASSPATH, use the set command.

Just in case you are wondering, the JCC in the file names stands for Java Common Client.

Figure 3: Updated CLASSPATH
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, we can use one of the other utilities that is 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 use the ij utility, we can again take advantage of the scripts that are provided with Cloudscape. In addition to reviewing and studying the ij script, I copied the Cloudscape\demo\databases\sample directory to the Derby directory.

By default passwords are not authenticated, so the userid is used to define the initial schema for the connection. Since connections to the database can only occur from the same machine, this shouldn't be too much of a security exposure or concern. The schema identifies the tables within the database that will be accessed by default. This allows us to "SELECT * FROM ORG" instead of "SELECT * FROM SAMP.ORG".

Note that the JDBC URL references to the Derby Network Server mean that it must be started before a connection can be established.

Figure 4: ij session
C:\Derby> java -Dij.user=SAMP -Dij.password=SAMP org.apache.derby.tools.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;

Using Jython to interactively connect to Derby

Let's do the same thing with Jython that we just did using the ij utility. Fortunately, Jython comes with the zxJDBC package, which is a "nearly 100% Python DB API 2.0 compliant interface for database connectivity in Jython". Figure 5 shows how we can use this package to connect to, and interact with Derby.

You should be able to correlate each of the Jython statements shown here to the ij session shown above in Figure 4. The only piece that is implicit in the ij session that is explicit here is the identification of the JDBC driver. By including the db2jcc*.jar files in the classpath, ij is able to use them to establish the connection.

For the moment, don't worry about the values contained in cursor.description tuples. We'll discuss them in more detail a little bit later.

Note: For some reason, the connections to the database default to autocommit disabled. This means that we have to issue the db.commit() before we can successfully close the database object with db.close(). If you want to see what happens if we try to close the database without executing a commit, take a look at Figure 10, which is where we show this same example using embedded Derby.

After using the interactive Jython session to try things out, we can simply and easily copy the appropriate statments to a file, which can then be executed by the interpreter. Or, we can even compile them, and then have the Java Virtual machine execute the byte codes without having to interpret the script each time.

Figure 5: Jython session
C:\Derby> jython
Jython 2.1 on java1.4.2_01 (JIT: null)
Type "copyright", "credits" or "license" for more information.
>>> from com.ziclix.python.sql import zxJDBC
>>> url = 'jdbc:derby:net://localhost:1527/sample'
>>> driver = 'com.ibm.db2.jcc.DB2Driver'
>>> user = password = 'SAMP'
>>> db = zxJDBC.connect( url, user, password, driver )
>>> cursor = db.cursor()
>>> cursor.execute( 'SELECT * FROM ORG' )

The end of each "for" statement is indicated with an empty line
(i.e., press <Enter>).

>>> for field in cursor.description : print field
...
('DEPTNUMB', 5, 6, None, 5, 0, 0)
('DEPTNAME', 12, 14, None, None, None, 1)
('MANAGER', 5, 6, None, 5, 0, 1)
('DIVISION', 12, 10, None, None, None, 1)
('LOCATION', 12, 13, None, None, None, 1)
>>> result = cursor.fetchall()
>>> for row in result : print row
...
(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')
>>> print str( len(result) ) + ' rows selected'
8 rows selected
>>> cursor.close()
>>> db.commit()
>>> db.close()

To exit the Jython interpreter, press Ctrl-Z.

>>> ^Z

After placing these same statements into zxORGtable.jy, we see how
easy it is to use Jython scripts to connect to a Derby database.

C:\Derby> zxORGtable
table field descriptions:
------------------------
('DEPTNUMB', 5, 6, None, 5, 0, 0)
('DEPTNAME', 12, 14, None, None, None, 1)
('MANAGER', 5, 6, None, 5, 0, 1)
('DIVISION', 12, 10, None, None, None, 1)
('LOCATION', 12, 13, None, None, None, 1)

ORG tables rows:
---------------
(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

zxJDBC Cursor description details

It's time to describe the field contents of cursor.description, as seen in Figure 5. One of the results of a successfully executed SQL query is that values are assigned to the description attribute for each returned field. This allows programs to determine information about the fields which exist for each of the selected rows. For each field in the result, a table is provided that contains the information found in Table 2. Unfortunately, as you can see, the Datatype is a number and as such is a little awkward to work with in numeric form. Fortunately, a Java API exists for dealing with these values, without knowing, or caring exactly what number is used.

Table 2: Cursor tuple contents
Field NameChar stringAll capital name of field
DatatypeNumericValues from java.sql.Types
Display SizeNumericwidth in characters
Internal SizeNoneNot provided by zxJDBC package - always "None"
PrecisionNumericOnly defined for numeric data types
ScaleNumericOnly defined for numeric data types
isNullableNumericBoolean value: 0 = False; 1 = True

If we are not actually interested in the numeric values, and only interested in determining how to deal with specific kinds of data, we can make use of the information available from the java.sql package. Listings 3 and 4 show some ways in which this information might be used.

Listing 3: Numeric SQL datatypes
from java.sql import Types as types
...
NumTypes = [
  types.BIGINT  ,
  types.DECIMAL ,
  types.DOUBLE  ,
  types.FLOAT   ,
  types.INTEGER ,
  types.NUMERIC ,
  types.REAL    ,
  types.SMALLINT,
  types.TINYINT  
]
...
# To test if a specific variable (Type) is numeric, you can simply:
if Type in NumTypes :
  # Handle numeric type entries here
else :
  # Handle non-numeric type entries here

If you are interested in associating a name with each of the possible SQL datatypes, you may want to use something like the dictionary shown in Listing 4.

Listing 4: Displaying datatype names
...
TypeName = {
  types.ARRAY        : 'ARRAY'        ,
  types.BIGINT       : 'BIGINT'       ,
  types.BINARY       : 'BINARY'       ,
  types.BIT          : 'BIT'          ,
  types.BLOB         : 'BLOB'         ,
  types.CHAR         : 'CHAR'         ,
  types.CLOB         : 'CLOB'         ,
  types.DATE         : 'DATE'         ,
  types.DECIMAL      : 'DECIMAL'      ,
  types.DISTINCT     : 'DISTINCT'     ,
  types.DOUBLE       : 'DOUBLE'       ,
  types.FLOAT        : 'FLOAT'        ,
  types.INTEGER      : 'INTEGER'      ,
  types.JAVA_OBJECT  : 'JAVA_OBJECT'  ,
  types.LONGVARBINARY: 'LONGVARBINARY',
  types.LONGVARCHAR  : 'LONGVARCHAR'  ,
  types.NULL         : 'NULL'         ,
  types.NUMERIC      : 'NUMERIC'      ,
  types.OTHER        : 'OTHER'        ,
  types.REAL         : 'REAL'         ,
  types.REF          : 'REF'          ,
  types.SMALLINT     : 'SMALLINT'     ,
  types.STRUCT       : 'STRUCT'       ,
  types.TIME         : 'TIME'         ,
  types.TIMESTAMP    : 'TIMESTAMP'    ,
  types.TINYINT      : 'TINYINT'      ,
  types.VARBINARY    : 'VARBINARY'    ,
  types.VARCHAR      : 'VARCHAR'      
}
...
# To display the kind of type used, you could use something like:
print 'Type( ' + str( Type ) + ' ) = ' + TypeName[ Type ]

Another connection technique

In Figure 5 we saw how to connect to a Derby database using the zxJDBC package. If you need to write scripts that more closely conform to the ResultSet interface defined by the java.sql package, then you might prefer the technique shown in Figure 6.

Those with really sharp eyes will note that this isn't exactly what was done using the zxJDBC package. However, it is close enough for the moment.

The RSMD and printRSMD routines were written as part of UserTables.jy, which is mentioned in the next section. I've included RSMD.py in the Jython2DerbySamples.zip file, just in case you want to duplicate this interactive session.

Figure 6: Another Jython session
C:\Derby> jython
Jython 2.1 on java1.4.2_01 (JIT: null)
Type "copyright", "credits" or "license" for more information.
>>> from java.sql import DriverManager
>>> from RSMD import printRSMD, RSMD
>>> from java.lang import Class
>>> from java.util import Properties
>>> url    = 'jdbc:derby:net://localhost:1527/sample'
>>> driver = 'com.ibm.db2.jcc.DB2Driver'
>>> props  = Properties()
>>> props.setProperty( 'user', 'SAMP' )
>>> props.setProperty( 'password', 'SAMP' )
>>> jcc    = Class.forName( driver ).newInstance()
>>> conn   = DriverManager.getConnection( url, props )
>>> stmt   = conn.createStatement()
>>> Query  = 'SELECT * FROM ORG'
>>> rs     = stmt.executeQuery( Query )
>>> rsmd   = RSMD( rs )
>>> printRSMD( rsmd, Query )

 Fields contained in: SELECT * FROM ORG

| Size  |  Label                 |Type |Type Name
+-------+------------------------+-----+--------------------
|      6|DEPTNUMB                |    5|SMALLINT
|     14|DEPTNAME                |   12|VARCHAR
|      6|MANAGER                 |    5|SMALLINT
|     10|DIVISION                |   12|VARCHAR
|     13|LOCATION                |   12|VARCHAR

>>> rowCount = 0

Indentation (i.e., whitespace) in Jython is significant.  To show
which lines are within the "while" group, each begins with 2 spaces.
The end of the while group is indicated with an empty line
(i.e., press <Enter>).

>>> while ( rs.next() ) :
...   rowCount += 1
...   row = ( rs.getInt( 1 ), rs.getString( 2 ), rs.getInt( 3 ), rs.getString( 4
 ), rs.getString( 5 ) )
...   print row
...
(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')
>>> print '\n' + str( rowCount ) + ' rows selected'

8 rows selected
>>> rs.close()
>>> stmt.close()
>>> conn.close()

To exit the Jython interpreter, press Ctrl-Z.

>>> ^Z

Placing these statements into ORGtable.jy, again, we see how
easy it is to use Jython to connect to a Derby database.

C:\Derby> ORGtable

 Fields contained in: SELECT * FROM ORG

| Size  |  Label                 |Type |Type Name
+-------+------------------------+-----+--------------------
|      6|DEPTNUMB                |    5|SMALLINT
|     14|DEPTNAME                |   12|VARCHAR
|      6|MANAGER                 |    5|SMALLINT
|     10|DIVISION                |   12|VARCHAR
|     13|LOCATION                |   12|VARCHAR

(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

All together now

I have included two programs written while exploring Jython and Derby. I wanted to write a program that displayed information about the user tables that existed in a Derby database. So, I wrote a zxJDBC and non-zxJDBC version of the same program, (zxUserTables.jy and UserTables respectively), just to get an idea of what was involved to do each version. These programs produce identical output. The beginning of the output is shown in Figure 7.

Much earlier, I made note of the fact tht the zxJDBC package, by default, opens a database connection without enabling autocommit. If you look at zxUserTables.jy, you will find how to enable autocommit. After successfully connecting to the database, simply add a db.autocommit = 1 statement.

Figure 7: Start of output from UserTables program
C:\Derby> UserTables

 Fields contained in: * FROM SYS.SYSTABLES, SYS.SYSSCHEMAS

| Size  |  Label                 |Type |Type Name
+-------+------------------------+-----+--------------------
|     36|TABLEID                 |    1|CHAR
|    128|TABLENAME               |   12|VARCHAR
|      1|TABLETYPE               |    1|CHAR
|     36|SCHEMAID                |    1|CHAR
|      1|LOCKGRANULARITY         |    1|CHAR
|     36|SCHEMAID                |    1|CHAR
|    128|SCHEMANAME              |   12|VARCHAR
|    128|AUTHORIZATIONID         |   12|VARCHAR

|TableName
+--------------------
|SAMP.PROJECT
|SAMP.STAFF
|SAMP.CL_SCHED
|SAMP.EMP_ACT
|SAMP.ORG
|SAMP.EMP_PHOTO
|SAMP.IN_TRAY
|SAMP.SALES
|SAMP.EMP_RESUME
|SAMP.EMPLOYEE
|SAMP.DEPARTMENT

11 rows selected

 Fields contained in: SAMP.PROJECT

| Size  |  Label                 |Type |Type Name
+-------+------------------------+-----+--------------------
|      6|PROJNO                  |    1|CHAR
|     24|PROJNAME                |   12|VARCHAR
|      3|DEPTNO                  |    1|CHAR
|      6|RESPEMP                 |    1|CHAR
|      7|PRSTAFF                 |    3|DECIMAL
|     10|PRSTDATE                |   91|DATE
|     10|PRENDATE                |   91|DATE
|      6|MAJPROJ                 |    1|CHAR

...

Embedded Derby databases

There are times when a Network Server may not be necessary, or appropriate. If you have a simple database application, it may be reasonable to have your Java relational database package executing within the same Virtual Machine as your application. With Derby, this is quite easy to do. The only differences necessary to use an embedded Derby database instead of a Derby Network client are in the:

  • CLASSPATH
  • Driver
  • Database URL

What does this mean? To begin with, we have a different CLASSPATH. Figure 3 shows what needs to be included to access a Derby Network Server. As seen in Figure 8, for an embedded Derby, we no longer need the "IBM DB/2 JDBC Universal Driver" related JAR files.

Figure 8: CLASSPATH for embedded Derby
C:\Derby> lsPath classpath
CLASSPATH=
  C:\Cloudscape\lib\derby.jar
  C:\Cloudscape\lib\derbytools.jar

As we saw in Figure 4, our ij session to connect to a Derby Network Server database was very straightforward. Figure 9 shows this same ij session, this time using embedded Derby.

Note that the only real difference is in the URL. This is because the ij utility is able to determine the appropriate driver based upon the URL.

Figure 9: ij session using embedded Derby
C:\Derby> java -Dij.user=SAMP -Dij.password=SAMP org.apache.derby.tools.ij
ij version 10.0 (C) Copyright IBM Corp. 1997, 2004.
ij> connect 'jdbc:derby: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;

As was seen before, ij figures out which driver should be used based upon the URL. For the Jython code, as we can see in Figure 10, we have to explicitly identify the appropriate driver for the specified URL.

As noted above, the only differences between this, and what was seen in Figure 5 are the URL, and the Driver values.

Also note what happens when you try to close the database without having issued db.commit() for the default (autocommit disabled) environment.

Figure 10: Jython session using zxJDBC and embedded Derby
C:\Derby> jython
Jython 2.1 on java1.4.2_01 (JIT: null)
Type "copyright", "credits" or "license" for more information.
>>> from com.ziclix.python.sql import zxJDBC
>>> url = 'jdbc:derby:sample'
>>> driver = 'org.apache.derby.jdbc.EmbeddedDriver'
>>> user = password = 'SAMP'
>>> db = zxJDBC.connect( url, user, password, driver )
>>> cursor = db.cursor()
>>> cursor.execute( 'SELECT * FROM ORG' )

Remember, to end of each "for" statement, press <Enter>.

>>> for field in cursor.description : print field
...
('DEPTNUMB', 5, 6, None, 5, 0, 0)
('DEPTNAME', 12, 14, None, None, None, 1)
('MANAGER', 5, 6, None, 5, 0, 1)
('DIVISION', 12, 10, None, None, None, 1)
('LOCATION', 12, 13, None, None, None, 1)
>>> result = cursor.fetchall()
>>> for row in result : print row
...
(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')
>>> print str( len( result ) ) + ' rows selected'
8 rows selected
>>> cursor.close()
>>> db.close()
Traceback (innermost last):
  File "<console>", line 1, in ?
Error: Invalid transaction state. [SQLCode: 20000], [SQLState: 25000]
>>> db.commit()
>>> db.close()
>>> ^Z

Summary

In this article, we have seen that there is access to an easy-to-use relational database, available free of charge. We've also seen that we can simply and easily develop high-level, dynamic, object-oriented programs in Jython that seamlessly integrate with the Java platform. In addition, we have seen how easy it is to write scripts that connect to a Derby Network Server, or to use an embedded Derby server executing within the same virtual machine as our application.


Downloads

DescriptionNameSize
Script to display environment variable valueslsPath.jy  ( HTTP | FTP )1,438 bytes
Example bat file for controlling derby serverNetworkServerControl.bat  ( HTTP | FTP )280 bytes
Script to display contents of SAMP.ORG tablezxORGtable.jy  ( HTTP | FTP )3,170 bytes
Displaying SAMP.ORG table without zxJDBC packageORGtable.jy  ( HTTP | FTP )6,503 bytes
Display details about user tables without zxJDBCUserTables.jy  ( HTTP | FTP )14,200 bytes
Display details about user tables using zxJDBCzxUserTables.jy  ( HTTP | FTP )14,745 bytes
Archive of all sample programsJython2DerbySamples.zip  ( HTTP | FTP )11,358 bytes

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, Open source
ArticleID=50931
ArticleTitle=Connect to Apache Derby databases using Jython
publish-date=02172005