7
Database access

Principle

This section briefly deals with the package java.sql that encapsulates an SQL interface for Java. Java programs use it to access adaptors that unify connections to commercial databases like Oracle, to more or less freely available databases like mSQL or mysql and with a bridge to ODBC databases like Access. This section elaborates on a paper by Silke Seehusen and Hans Timmermann.

The initial problem is to operate a database, install a suitable JDBC adaptor, and to test the resulting system. mSQL , mysql , and an ODBC source are used as databases. Sql is a relatively simple program to access arbitrary adaptors and execute SQL statements. JDBCTest from InterSolv is a very nice application to test adaptors and analyze the mapping of query and result problems to Java code.

mSQL is a relatively stable SQL system available in source that for example universities can use for free. There is a JDBC adaptor available.

Under Linux one compiles and installs mSQL as documented. The following is important for configuration:

/usr/local/Hughes/msql.conf

Host_Lookup = False

/usr/local/Hughes/msql.acl

database=telbook
read=*
write=*
host=*
access=local,remote

If the user msql has been created with /usr/local/Hughes as HOME, one starts as Admin_User the server and during the first session creates a database:

$ su - msql -c 'bin/msql2d' &
Mini SQL Version 2.0.3
Copyright (c) 1993-94 David J. Hughes
Copyright (c) 1995-97 Hughes Technologies Pty Ltd.
All rights reserved.

Loading configuration from '/usr/local/Hughes/msql.conf'.
Server process reconfigured to accept 214 connections.
Server running as user 'msql'.
Server mode is Read/Write.
$ PATH=$PATH:/usr/local/Hughes/bin
$ msqladmin create telbook
Database "telbook" created.

$ CLASSPATH=.:imaginary.zip; export CLASSPATH
$ msql() {
>   java -Djdbc.drivers=com.imaginary.sql.msql.MsqlDriver Sql "$@"
> }

	$ msql -s	show drivers
	drivers:
	com.imaginary.sql.msql.MsqlDriver

com.imaginary.sql.msql.MsqlException: Unknown database "test"

	$ msql -d telbook --	test connection
	$ msql -d telbook "
	> create table telnumbers (	make table
	>   name text(10) not null, num int not null
	> )" "
	> create unique index idx on telnumbers (num)"	make primary key
	create table telnumbers (
	name text(10) not null, num int not null
	)

1 row(s) affected.

create unique index idx on telnumbers (num)

1 row(s) affected.

	$ msql -d telbook -	add values interactively
	insert into telnumbers values ('axel', 2483)

1 row(s) affected.

insert into telnumbers values ('bernd', 2534)

1 row(s) affected.

	select name from telnumbers	query
	Row 1:  telnumbers.name: axel
	Row 2:  telnumbers.name: bernd
	select * from telnumbers where num = 2483
	Row 1:  telnumbers.name: axel
	telnumbers.num: 2483

Version 1.0.16 of mSQL was ported from OS/2 to Win32 and can be operated with the same mSQL JDBC adaptor, which was implemented in Java. For the installation one could use:

install.bat

SET MSQL_HOME=e:\apps\msql
SET MSQL_TCP_PORT=1114
SET SRV_MINERVA_DEBUG=
SET CLI_MINERVA_DEBUG=
SET MSQL_HOST=localhost
SET USER=root
SET EDITOR=

msql.acl

database=telbook
read=*
write=*
host=*
access=local,remote

Under Windows one starts the server in two DOS windows and in the first session creates a database:

> msqld
mSQL Server 1.0.16 for Windows NT starting ...
Couldn't open PID file: No such file or directory

> msqlsdm create telbook

Then one can use Windows or Linux to test the following:

$ msql() {
>   java -Djdbc.drivers=com.imaginary.sql.msql.MsqlDriver Sql
-u jdbc:msql://perky:1114/ "$@"
> }
$ msql -s -d telbook -
drivers:
com.imaginary.sql.msql.MsqlDriver
create table telnumbers ( name char(10), num int primary key )
1 row(s) affected.
insert into telnumbers values ('axel', 2483)
1 row(s) affected.
insert into telnumbers values ('bernd', 2534)
1 row(s) affected.
select * from telnumbers
Row 1:  telnumbers.name: axel
telnumbers.num: 2483
Row 2:  telnumbers.name: bernd
telnumbers.num: 2534

mysql is a multi-threaded SQL system available in binary and source that can be used freely. There is a JDBC Adaptor.

Under Linux one can do a binary installation of mysql from the archive in /usr/local/mysql. In the installation script an additional administrator can be added:

scripts/mysql_install_db

INSERT INTO user VALUES
('%','axel','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

The installation script should be run by root, it starts the server. One can start the server explicitly, too:

$ su root -c 'cd /usr/local/mysql && bin/mysql.server start'
Password:
Starting mysqld demon with databases from
/usr/local/mysql-3.20.32a-pc-linux-gnu-i586/data

There is an extensive log in data/host.log. In the first session one creates a database:

$ PATH=$PATH:/usr/local/mysql/bin
$ mysqladmin create telbook
Database "telbook" created.

The adaptor is in gwe.zip. The following can be tested:

$ CLASSPATH=.:gwe.zip; export CLASSPATH
$ mysql() {
>   java -Djdbc.drivers=gwe.sql.gweMysqlDriver Sql
-u jdbc:mysql://cindy:3333/ "$@"
> }
$ mysql -s -d telbook -
drivers:
gwe.sql.gweMysqlDriver
create table telnumbers (name varchar(10), num int primary key)
java.sql.SQLException: mysql exception: No columns returned.
insert into telnumbers values ('axel', 2483)
java.sql.SQLException: mysql exception: No columns returned.
insert into telnumbers values('bernd', 2534)
java.sql.SQLException: mysql exception: No columns returned.
select * from telnumbers
Row 1:  telnumbers.name: axel
telnumbers.num: 2483
Row 2:  telnumbers.name: bernd
telnumbers.num: 2534

ODBC is a standard that unifies access to some databases from Windows. If one installs Microsoft Access, one can additionally install the ODBC interface (Microsoft Desktop ODBC Driver) and then use a multitude of tables, among them text files, as datanbases. The control panel contains 32-Bit-ODBC

From there a database can be installed as a data source:

> set CLASSPATH=.
> java -Djdbc.drivers=sun.jdbc.odbc.JdbcOdbcDriver
Sql -s -u jdbc:odbc: -d telbook -
drivers:
sun.jdbc.odbc.JdbcOdbcDriver
create table telnumbers (name string, num integer constraint pk primary key)
-1 row(s) affected.
insert into telnumbers values('axel', 2483)
1 row(s) affected.
insert into telnumbers values('bernd', 2534)
1 row(s) affected.
select * from telnumbers
Row 1:  name: axel
num: 2483
Row 2:  name: bernd
num: 2534

JDBCTest is a Java application from InterSolv, which can interactively try very many operations and generate the relevant Java code.

06/May/1998