 | Level: Introductory Bob Gibson (bgibson@us.ibm.com), Advisory software engineer, IBM
17 Feb 2005 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.
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 Taken
|
Description
|
Comment/Result
| | Install Java | verion 1.4.2 from developerWorks
| Installed to (something like) C:\j2sdk1.4.2 | | Verify install | Open a command prompt, and execute:
...> java -Version
| Result (something like):
java version "1.4.2"
| | Install Jython | verion 2.1 from Jython.org
| Installed to C:\Jython | | Verify install | Open a command prompt, and execute:
...> C:\Jython\jython.bat --version
| Result (something like):
Jython 2.1 on java
| | Define file association | At a command prompt, execute:
...> assoc .jy=Jython.File
| Defines file extension association | | Verify file association | At a command prompt, execute:
...> assoc .jy
| Result:
.jy=Jython.File
| | Define file type command | At a command prompt, execute:
...> ftype Jython.File="C:\Jython\jython.bat" "%1" %*
| Defines how to "execute" a Jython command | | Verify file type command | At a command prompt, execute:
...> ftype Jython.File
| Result:
"C:\Jython\jython.bat" "%1" %*
| | Define ".jy" as an executable file extension |
- Click the Start button
- Right-click the My Computer entry
- Select Properties
- Select the Advanced tab
- Click on the Environment Variables button
- In the "System Variables" section, scroll down and select the PATHEXT entry
- Click on the Edit button
- Add ;.jy to the end of the input field
- Click on the OK button to close the "Edit System Variable" dialog box
- Click on the OK button to close the "Environment Variables" dialog box
- Click on the OK button to close the "System Properties" dialog box
| Adds to the list of "executable" file extensions | | Verify PATHEXT value | Open a new command prompt, and execute:
...> set pathext
| Result (something like):
PATHEXT=.com;.exe;.bat;...;.jy
| | Install Apache Derby files |
- Point browser to the Apache Derby Project page
- Select the Downloads link
- Select the Official Release download link
- Select the appropriate download (for example, incubating-derby-10.0.2.1-bin.zip)
- Perform a virus scan of the downloaded files
- Unzip (or Untar) the downloaded file
- Copy or move the expanded directory structure to C:\Derby
| Derby files installed into C:\Derby | |
Optional: Install IBM Cloudscape files |
- Point your browser to Susan Cline's article on developerWorks.
- Select the link appropriate for the platform version you want to download
- Register, if you don't already have a valid userid and password
- Download the installation and instruction files
- Perform virus scan of the downloaded files
- 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:
| 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 that provides information about your version of Cloudscape and your environment. | | *NetworkServer*.bat | command 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 Name | Char string | All capital name of field | | Datatype | Numeric | Values from java.sql.Types | | Display Size | Numeric | width in characters | | Internal Size | None | Not provided by zxJDBC package - always "None" | | Precision | Numeric | Only defined for numeric data types | | Scale | Numeric | Only defined for numeric data types | | isNullable | Numeric | Boolean 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 | Description | Name | Size | Download method |
|---|
| Script to display environment variable values | lsPath.jy | 1,438 bytes | FTP|HTTP |
|---|
| Example bat file for controlling derby server | NetworkServerControl.bat | 280 bytes | FTP|HTTP |
|---|
| Script to display contents of SAMP.ORG table | zxORGtable.jy | 3,170 bytes | FTP|HTTP |
|---|
| Displaying SAMP.ORG table without zxJDBC package | ORGtable.jy | 6,503 bytes | FTP|HTTP |
|---|
| Display details about user tables without zxJDBC | UserTables.jy | 14,200 bytes | FTP|HTTP |
|---|
| Display details about user tables using zxJDBC | zxUserTables.jy | 14,745 bytes | FTP|HTTP |
|---|
| Archive of all sample programs | Jython2DerbySamples.zip | 11,358 bytes | FTP|HTTP |
|---|
Resources
About the author | 
| | Bob 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.
|
Rate this page
| |
