MySQL

Back - UNIX / Linux Installation Up - Installation Next - OpenBase

Test Pilot implements the industry standard for Java database connectivity, JDBC. This permits Test Pilot to be able to use any manufacturer's database which supports access via JDBC. This includes nearly every database vendor with the exception of Microsoft, and third parties have stepped in to provide excellent JDBC connectivity to Microsoft's proprietary database, SQL Server.

Java software such as Test Pilot is written to load JDBC driver software and communicate though this driver with the database. Once a connection is established, the java application can use the database for data storage and retrieval. There are numerous advantages to this design. First, by using JDBC, applications can use databases from multiple manufacturers. Switching from one vendor to another is a very simple matter. Second, with a robust relational database engine on the back-end of an application, data integrity and manageability is greatly enhanced. Finally, the flexibility afford by the rich command set supported by JDBC drivers, powerful data queries and reporting are possible.

MySQL

MySQL is a FREE, open source database available for many computing platforms. It represents the most affordable solution for relational database services available today. Information about all MySQL distributionsand downloads are available at:

http://www.mysql.com/

Mac OS X users can find package distributions and excellent installation instructions at:

http://www.entropy.ch/software/macosx/mysql/

A successful setup and installation of MySQL is assumed to have occurred. Be sure to set an administrator password as most distributions of MySQL have no administrator pasword by default. The typical command to set the administrator password is:

mysqladmin -u root password new_password

Once you have set up this password, to log in as administrator, you can use the following command:

mysql -u root -p

You'll be challenged for the administrator password as set in the command above.

Test Pilot Database Setup

Your database server's administrator should create a MySQL database for Test Pilot. They should create at least one account that is permitted full access (read/write/create/delete/etc.) to that database. It is generally a good idea NOT to have spaces or punctuation characters in the database's name or the account name permitted to access the database.

Typically, we recommend creating a database named TESTPILOT. When logged into MySQL as the administrator, the typical command to create a databse for Test Pilot would be:

CREATE DATABASE TESTPILOT;

You'll need to create a MySQL testpilot account with an appropriate password for accessing the database. No other account needs to access to the database. When logged into MySQL as administrator, the following commands would establish an account named testpilot with a password of password with appropriate permissions (both commands are necessary):

GRANT ALL PRIVILEGES ON TESTPILOT.* TO testpilot@localhost IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON TESTPILOT.* TO testpilot@"%" IDENTIFIED BY 'password';

Of course, substitute an account and password of your choosing in the commands above. Note that if you MySQL is running on a separate computer than the Test Pilot web server, you'll need to configure MySQL to permit connections from the web server.

JDBC Driver Library

You will need to download a JDBC driver for MySQL. The jar file, entitled mysql_comp.jar should be placed in the Tomcat lib folder. The driver, which is available under the limited GNU Public License (LGPL) is always available at its Internet home at http://mmmysql.sourceforge.net/

To use it with Test Pilot, the servlet runner must be able to find it. By default, Tomcat can find any driver in the lib folder. Other servlet runners may need to be told where the driver is located. This typically involved adding the driver to the Java virtual machine's CLASSPATH.

For example, when configuring ServletExec, you could add the full path to this file to the CLASSPATH under ServletExec's VM Setting page. If the full path to the file was, C:\JDBC\DRIVERS\MySQL\mysql_comp.jar, then you would add that to the CLASSPATH using ServletExec's controls.

JDBC Driver Name

Within, the driver library file are a number of Java routines. Any Java program wishing to use the driver must know the name of the routine within the library to use. For this library, the driver name is org.gjt.mm.mysql.Driver Thus, Test Pilot's driver initialization parameter must be set as follows:

driver=org.gjt.mm.mysql.Driver

JDBC URL

To connect to a MySQL database, using the MySQL JDBC driver, you must give Test Pilot a URL to use to access the database. The URL is formulated as follows:

jdbc:mysql://DATABASE-SERVER/database-name

Substitute your server's name in the appropriate location. (use locahost, if your database is running on the same machine as your web server) Substitute the name of the database your administrator created for Test Pilot in the other location. It is this URL that you will enter into Test Pilot's initialization parameters. Consider the following example:

jdbc=jdbc:mysql://server.edu/TESTPILOT

This instructs Test Pilot to contact to the database server, server.edu and connect to the database TESTPILOT.

JDBC Account Name

Your database administrator should provide you with an account authorized to manipulate the new Test Pilot database. You must tell Test Pilot this account with the dbuser initialization parameter as follows:

dbuser=ACCOUNT

Substitute the account given to you by your administrator in place of ACCOUNT.

JDBC Account Password

Your database administrator should provide you with a password for the account authorized to manipulate the new Test Pilot database. You must tell Test Pilot this password with the dbpass initialization parameter as follows:

dbpass=PASSWORD

Substitute the account given to you by your administrator in place of PASSWORD.

JDBC Connection Pool

Test Pilot may open a number of connections with the database to broaden it data handling bandwidth and increase performance. You must tell Test Pilot how many connections to open with the dbpool initialization parameter as follows:

dbpool=10

Your administrator can tell you how many connections your account is permitted. A reasonable initial size is 5. You will need to tune this parameter as your usage scales.

Testing Your Connection

Once you have entered all the database initialization parameters into Test Pilot, you may test your setting with the following URL:

http://SERVER/servlet/TestPilot4/?db

Substitute your server name in the appropriate location. You'll receive a report as to whether your configuration has successfully connected to the database or why the connection was not established.