ENGLISH | JAPANESE |
The goal of this part is to show you how best we can combine Database and prolog. We have shown you how to call prolog on Servlet. We will show you in next section how to connect prolog to Relational Database on Servlet/Java, but before that we have to learn how to use Relational Database on Servlet/Java.
The first question should be, then, "Which database shall we employ?" Sincerely speaking, we do not have a best solution because there is no free, open-source relational database runnable on Windows 9X. We may try one of the followings:
My first choice is the first option, that is, we install MySQL on a Linux machine and call the database from our PC running on Windows9X. Why? well, I suppose that you work on Windows9X only for developing software, not for running your server to respond to the requests from public. In the end you may want to install your web applications on more reliable machines, i.e, a PC running on Linux. It makes sense, therefore, to install the database management system on the PC running on Linux, to which you will import your web applications for public access. Duplicating a database on your PC for development and the other running as webserver, is not good idea at all.
It is more sensitive question, why we opt for MySQL, not for PostgreSQL. Generally speaking, MySQL is smaller and efficient than PostgreSQL, but some of the functionalities available with PostgreSQL are missing. I believe that MySQL is sufficient to building web applications for experimentation. We may have to employ PostgreSQL to build bigger systems in the future, but for the moment we only build small systems to study how we can import ideas from AI to Web. For rapid prototyping, we prefer MySQL for its efficiency to PostgreSQL.
There are a number of good texts on JDBC. I do therefore not go into detail in the following and only explain the points which you might miss.
You can download the package from MySQL Page. You can find in the package a detailed instruction for installing mySQL on Linux.
After installing MySQL server, you have to add yourself to the user list. The crucial point in registering yourself is to grant you to get an access to the server from a remote PC other than localhost. If you allow yourself only to login the server on localhost, you will never establish a connection from your PC to the server through LAN.
Consult the section 6.13 titled "Adding new user privileges to MySQL" of MySQL Rererence Manual (mine is Version 3.23.7-alpha). To repeat the explanation, you can grant yourself the previlege to login the server with the right to do anything you like from any machine on the net as follows:
shell> mysql -u root -p Enter password: (Here you enter the password for root) Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 38 to server version: 3.22.27 Type 'help' for help. mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> GRANT ALL PRIVILEGES ON *.* TO fuji@"%" IDENTIFIED BY 'YourPassword' WITH GRANT OPTION;Note that you should type in an appropriate password for 'YourPassword'. The symbol, '%', in the above works as wild card in SQL, thus 'fuji@"%"' means that the user 'fuji' is allowed to enter from any computer. Beaware that you must always specify the host name after then to operate on data on the server even when you login it locally, that is, you have to login the server as below:
shell> mysql -h ks15e0f00 -u fuji -p(Type your host name for 'ks15e0f00'.) Your access to particular databasese will be denied, otherwise.
Check finally if you have registered yourself as you intended. That is, you login the server as explained above, choosing the database, 'mysql'. Execute the command, 'select * from user;', then. You will see your host privilege displayed as '%' in the first column if everything went well.
My preference goes to MM MySQL JDBC Driver developed by Mark Matthews. Download the package from his web page. The latest is the version 2.0.2, whose file you can find as mm.mysql-2.0.2-bin.jar (10.7.2000). To install the driver, simply place the jar file in your classpath.
We go through very basics of MySQL. The material given here is apparently insufficient even to build toy systems. The reader not familiar with relational databases and SQL, is strongly rocommended to consult other texts on these topics.
We build a sample database which will store the pairs of people who stand in 'child_of' relation. Let 'mysqltest' be the database. To create the database, you type in as follows:
mysql> CREATE DATABASE mysqltest; Query OK, 1 row affected (0.00 sec)Try 'show databases' command to list up the databases on your server.
Before creating a table in a database, you have to first choose the database as your working database. To choose 'mysqltest' database, type in the command as below:
mysql> use mysqltest; Database changed
We create a table to keep records on the people who stand in child_of relation. An image of the table is depicted as follows:
child | parent |
---|---|
joe | ralf |
mary | joe |
steve | joe |
mysql> CREATE TABLE child_of -> (child CHAR(10), -> parent CHAR(10)); Query OK, 0 rows affected (0.09 sec) mysql> show tables; +---------------------+ | Tables in mysqltest | +---------------------+ | child_of | +---------------------+ 1 row in set (0.00 sec)
You add to the table the pair of 'joe' and 'ralf' as follows:
mysql> INSERT into child_of (child, parent) VALUES('joe', 'ralf'); Query OK, 1 row affected (0.00 sec) mysql> select * from child_of; +-------+--------+ | child | parent | +-------+--------+ | joe | ralf | +-------+--------+ 1 row in set (0.00 sec)Other pairs can be inserted to the table as well:
mysql> INSERT into child_of (child, parent) VALUES('mary', 'joe'); Query OK, 1 row affected (0.00 sec) mysql> INSERT into child_of (child, parent) VALUES('steve', 'joe'); Query OK, 1 row affected (0.00 sec) mysql> select * from child_of; +-------+--------+ | child | parent | +-------+--------+ | joe | ralf | | mary | joe | | steve | joe | +-------+--------+ 3 rows in set (0.00 sec)
We use the JDBC driver to connect to our mySQL server from Java programs. We assume that you have already installed mm.mysql, a JDBC driver for MySQL, from Mark Matthews' web page. Installation is simple; you only need to place mm.mysql-2.0.2-bin.jar in your classpath.
We show you the simplest program which logs in our MySQL server to read and display all the records in child_of table of mysqltest database. Below is the sample code:
/* A sample code to connect to mySQL server using mm.mysql(2.0.2) */ import java.io.*; import java.sql.*; public class JDBCSample { public static void main( String argv[] ) { try { Class.forName( "org.gjt.mm.mysql.Driver" ); String url = "jdbc:mysql://ks15e0f00/mysqltest?user=YourID&password=YourPassword"; Connection con = DriverManager.getConnection( url ); Statement select = con.createStatement(); String sql = "select * from child_of"; sql = new String(sql.getBytes("SJIS"), "8859_1" ); ResultSet res = select.executeQuery( sql ); int count = 0; while( res.next() ) { count++; String result = res.getString( "child" ) + "\t" + res.getString( "parent" ); result = new String( result.getBytes("8859_1"), "SJIS" ); System.out.println( result ); } System.out.println( "Record count=" + count ); select.close(); con.close(); } catch ( Exception e ) { e.printStackTrace(); } } }You must replace your machine name for 'ks15e0f00', your userID for 'yourID', and your password 'YourPassword'. (Your userID and password are for loging in MySQL server, not to enter the Linux machine.) If you are familiar with the programing using JDBC, there must be no mystery in the code. We explain some details of the code below.
Before going into detail, we had better show you how it works. You have to compile JDBCSample.java by typing 'javac JDBCSample.java'. You can then execute the program as follows:
~/docs/fp/programs/jdbc> java JDBCSample joe ralf mary joe steve joe Record count=3
You have to use the packages under java.sql.
import java.io.*; import java.sql.*;
The first line loads the driver for connecting the MySQL server. The second line specifies the database to connect with its machine name and the name of the database (i.e., 'mysqltest'), followed by your userID and password. The third line establishes the connedtion.
Class.forName( "org.gjt.mm.mysql.Driver" ); String url = "jdbc:mysql://ks15e0f00/mysqltest?user=YourID&password=YourPassword"; Connection con = DriverManager.getConnection( url );
The first line prepares for the statement to be sent to MySQL server. The second line construct the query sentence. The third line will convert a query coded in SJIS (Japanese) to binary. (We store data coded in Japanese as binary on our MySQL server. You do not need to include this line if you will not use Japanese characters.) The last line execute the query to receive the result to 'res'.
Statement select = con.createStatement(); String sql = "select * from child_of"; sql = new String(sql.getBytes("SJIS"), "8859_1" ); ResultSet res = select.executeQuery( sql );
We go read out the result line by line. The first line sets the counter to zero, which will be referred to for printing out the number of lines retrieved at end. In the while loop, we read out the data in the columns of 'child' and 'parent' and concatenate them to store the result in 'result'. The fourth line is for converting the data format to SJIS from binary. The result is printed out to the last line. At end, the number of lines is displayed.
int count = 0; while( res.next() ) { count++; String result = res.getString( "child" ) + "\t" + res.getString( "parent" ); result = new String( result.getBytes("8859_1"), "SJIS" ); System.out.println( result ); } System.out.println( "Record count=" + count );
We finally finish the query and close the connection.
select.close(); con.close();