Microsoft SQL Server 2000

Back - OpenBase Up - Installation

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 2000.

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.

Microsoft SQL Server

Microsoft SQL Server is a database available for Windows NT, 2000 and XP servers. It represents a proprietary, single platform solution whose data is not easily transferrable to other operating systems. Information about MS SQL Server 2000 and downloads are available at:

http://www.microsoft.com/

A successful setup and installation of MS SQL Server 2000 is assumed to have occurred.

Microsoft and JDBC

Microsoft recently (Nov. 2001) began providing JDBC software to access SQL Server 2000. Unfortunately, the driver does not handle all data types properly and hence cannot be used with Test Pilot.

In the meantime, we support the use of the JSQLConnect JDBC driver from JNetDirect, another company well respected in this field. It is the configuration of the JSQLConnect JDBC driver that will be discussed here. product info

If you mention that it will be used for Test Pilot when you purchase JSQLConnect, the great people at JNetDirect will grant you a discount.

Test Pilot Database Setup

Your database server's administrator should create a MS SQL Server 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 naming the database TESTPILOT and creating a testpilot account with an appropriate password for accessing the database. No other account need access to the database.

Note that if you MS SQL Server 2000 is running on a separate computer than the Test Pilot web server, you'll need to configure MS SQL Server 2000 to permit connections from the web server. Performance will be adversely affected when running the database server and the web server on separate computers.

Once, the database it created, an ODBC data source must be configured using the ODBC Data Source control panel, to allow access by other applications including Test Pilot and access by other computers if that is your need.

JDBC Driver Library

JSQLConnect is a JDBC driver for MS SQL Server 2000. The filename for the driver is JSQLConnect.jar. The driver is available at its Internet home at

http://www.j-netdirect.com/

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\MSSQL\JSQLConnect.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 com.jnetdirect.jsql.JSQLDriver Thus, Test Pilot's driver initialization parameter must be set as follows:

driver=com.jnetdirect.jsql.JSQLDriver

JDBC URL

To connect to a MS SQL Server 2K database, using the JSQLConect JDBC driver, you must give Test Pilot a URL to use to access the database. The URL is formulated as follows:

jdbc:JSQLConnect://DATABASE-SERVER/database=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 ODBC Data Source 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:JSQLConnect://server.edu/database=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.