bea.com | products | dev2dev | support | askBEA

Download Docs | Site Map | Glossary
Search

Configuring and Using WebLogic jDriver for Microsoft SQL Server (Deprecated)

Installing WebLogic jDriver for Microsoft SQL Server

This document tells you how to configure WebLogic jDriver for Microsoft SQL Server, BEA's pure-Java Type 4 JDBC driver for Microsoft SQL Server, and provides information about the following topics:

- Overview

- Before You Begin

- Installation Procedure

- WebLogic jDriver for SQL Server Connection Properties

- Using Connection Pools

- Verifying Your SQL Server Installation

- Setting a Port for SQL Server Connections

- Verifying the JDBC Driver With dbping

- For More Information

Note: The WebLogic jDriver for Microsoft SQL Server is deprecated. BEA recommends that you use the JDBC driver available from Microsoft. For more information, see "Installing and Using the SQL Server 2000 Driver for JDBC from Microsoft" in Programming WebLogic JDBC.

Overview

WebLogic jDriver for Microsoft SQL Server is a pure Java implementation of the Java Database Connectivity (JDBC) API, the industry standard for relational database access from Java clients.It provides Java clients with direct access to Microsoft SQL Server. The driver is available in two versions: one for SQL Server versions 6.5 and 7.0, and another for SQL Server 7.0 only. Both versions function identically, except as noted in the following section, Before You Begin.

Like all Type 4 JDBC drivers, WebLogic jDriver for Microsoft SQL Server is pure Java; it requires no vendor-supported client libraries. WebLogic jDriver for Microsoft SQL Server communicates directly with SQL Server through a TCP/IP network, using the SQL Server Tabular Data Stream protocol, so DB-Library does not have to be installed on a client computer.

Before You Begin

This section describes the differences between two versions of WebLogic jDriver for Microsoft SQL Server.

Which Version Should I Use?

BEA offers two versions of the WebLogic jDriver for Microsoft SQL Server drivers. One version supports Microsoft SQL Server versions 7.0 and 2000 (the default), and the other version supports Microsoft SQL Server version 6.5 and 7.0.

WebLogic jDriver for Microsoft SQL Server Version 7.0 and 2000

- Supports SQL Server 7.0 and 2000. The SQL Server responds to requests made from this driver as it does to requests from an SQL Server version 7.0 and 2000 client and implements the semantics of an SQL Server 7.0 and 2000. For instance (in contrast to the semantics of SQL Server version 6.5), the DBMS, by default, creates columns that allow null values.

- Supports new data types introduced with SQL Server Version 7.0 and 2000.

This version of the WebLogic jDriver for Microsoft SQL Server is preconfigured with WebLogic Server. You do not need to add any entries to your CLASSPATH to use this driver.

WebLogic jDriver for Microsoft SQL Server Versions 6.5 and 7.0

- Supports SQL Server Versions 6.5.

- Supports SQL Server 7.0 with the following restrictions:

  - The SQL Server responds as if the connection were coming from an SQL Server version 6.5 client, and implements the semantics of SQL Server version 6.5. For instance, when executing a CREATE TABLE SQL statement, the DBMS, by default, creates columns that do not allow null values. (This behavior is normal for SQL Server version 6.5.)

  - New SQL Server 7.0 data types are not supported.

To use this version of the WebLogic jDriver for Microsoft SQL Server, you must add the path to mssqlserver4v65.jar in your CLASSPATH. For instructions, see Installation Procedure on page 4.

Checking Licensing Functionality

To use WebLogic jDriver for Microsoft SQL Server, you must have the proper license. WebLogic jDriver for Microsoft SQL Server licensing functionality is included in the license file located in the BEA home directory where you installed this WebLogic Server. For example:

>     c:\bea\license.bea

If your license included WebLogic jDriver for Microsoft SQL Server when you installed or last updated your WebLogic Server license, no further action is required. If you are adding this functionality, you must get an updated license from your BEA sales representative. For instructions to update your license file, see Updating Your license.bea File in Installing BEA WebLogic Server.

Note: If you use WebLogic jDriver for Microsoft SQL Server when WebLogic Server is not running, you must include the path to the folder where license.bea resides in your CLASSPATH.

Installation Procedure

Microsoft SQL Server is bundled with your WebLogic Server distribution. For Version 7.0 and 2000, the weblogic.jar file includes the Micrososft SQL Server classes. No further steps are required. However, if you are using Version 6.5, you must pre-pend the mssqlserver4v65.jar file in the classpath as follows:

set CLASSPATH=%WL_HOME%\server\lib\mssqlserver4v65.JAR;
%WL_HOME%\server\lib\weblogic.jar;%CLASSPATH%

WebLogic jDriver for SQL Server Connection Properties

Table 1-1 WebLogic jDriver for SQL Server Connection Properties

Property | Description
--- | ---
appname | This property is passed to the DBMS where it is written to the sysprocesses table in the program_name column.
db | The name of the database to which you want to connect.
hostname | This property is prepended with "WebLogic" and is passed to the DBMS where it is written to the sysprocesses table in the hostname column.
password | The password for the database user name.
port | The TCP port on which the database server listens for connections. The default is 1433.
server | The name or IP address of the database server.
user | The user name used to connect to your SQL Server database.
useVarChars | By default, the driver prepends an "N" to any varchar argument values sent to the DBMS so that the DBMS reads all 16 bits of data. Otherwise, the DBMS assumes American 7-bit characters and strips the high bit. When useVarChars is set to true, the driver does not prepend varchar arguments with an "N." For customers who are not using multi-byte character sets, this option may improve performance. When set to false, an "N" is prepended to varchar arguments.

Using Connection Pools

If you are using WebLogic jDriver for Microsoft SQL Server with either WebLogic Server or WebLogic Express, you can set up a pool of connections to your SQL Server DBMS that will be established when WebLogic Server is started. Because the connections are shared among users, these connection pools eliminate the overhead of opening a new database connection for each user.

Your application then looks up a DataSource on the JNDI tree and requests a connection from the connection pool. When finished with the database connection, your application returns it to the connection pool.

Configuring a Connection Pool with WebLogic Server

1. Set your environment and start WebLogic Server. For more information, see Starting and Stopping WebLogic Servers in the Administration Guide.

2. Use the Administration Console to set up connection pools. To read about connection pools, see JDBC Components—Connection Pools, Data Sources, and MultiPools in the Administration Guide and Creating and Configuring a JDBC Connection Pool in the Administration Console Online Help.

Using the Connection Pool in Your Application

To use a connection pool, you must first establish a database connection. How you establish that connection depends on whether the application in which you want to use the connection pool as a client-side or a server-side application.

Client-Side Applications

To use a connection pool in a client-side application, establish the database connection by looking up a DataSource on the JNDI tree and then requesting a connection. For more information, see Configuring and Using DataSources in Programming WebLogic JDBC.

Server-Side Applications

To use a connection pool in a server-side application (such as a servlet), establish your database connection using a DataSource or by using the WebLogic pool or jts drivers. For more information, see "Programming Tasks" in Programming WebLogic HTTP Servlets.

Verifying Your SQL Server Installation

Note: Verify that you are using version 6.5 or 7.0 of Microsoft SQL Server. Older versions of SQL Server do not properly support JDBC metadata functions and have limited data type support.

To connect to SQL Server, you need the following information:

- Username and password of a valid SQL Server account

- Host name or IP number of the machine on which the SQL Server is running

- Address of the TCP/IP port at which the DBMS is listening for connection requests

  For Microsoft SQL Server, the default port number is 1433. Servers, however, can be configured to listen on any port number. Verify the port number from your configuration files. If you need help setting the port number, see the following section, "Setting a Port for SQL Server Connections."

Setting a Port for SQL Server Connections

You set the host name and port for SQL Server connections by creating an entry in the SQL Server configuration files. In the configuration files, a logical server name is associated with a server machine name and port number. WebLogic jDriver for Microsoft SQL Server does not use a logical server name; it uses only the host name and the port number.

You must have administrator privileges to change your SQL Server settings. To set the port:

1. Run MS SQL Server Setup.

2. Select Change Network Support.

3. Select TCP/IP.

4. Select the port you want to use, such as 1433.

Once you have set the port, you can verify, by using telnet, that the server is listening on that port. Enter the following command:

>     $ telnet hostname_or_IP_address port

For example, to check whether the SQL Server is listening on port 1433 of a computer named myhost, type:

>     $ telnet myhost 1433

If the server is not listening on the port, telnet displays an error message. If the server is listening on the port, telnet displays nothing; eventually, the host drops the connection.

You can test your login information by entering the following command:

>     $ isql -Uusername -Ppassword -Sserver

Verifying the JDBC Driver With dbping

You can use dbping, a WebLogic Java application, to verify that WebLogic jDriver for Microsoft SQL Server can connect to your SQL Server. Use the following commands to set your environment and to execute dbping:

>     WL_HOME\server\bin\setWLSEnv.cmd
>     java utils.dbping MSSQLSERVER4 username password
>        [database@]host[:port]

The arguments in this command line are defined as follows:

- WL_HOME is the directory where WebLogic Platform is installed, typically c:\bea\weblogic700.

- username is the name of the database user.

- password is the user's password.

- database (optional) is the SQL Server database to be used.

- host is the name or IP number of the computer on which SQL Server is running.

- port (optional) is the TCP/IP port on which the SQL Server is listening.

For example, the following command pings an SQL Server database called pubs on a computer named myhost, using the default TCP/IP port, the sa login, and a null password:

>     $ java utils.dbping MSSQLSERVER4 sa "" pubs@myhost

The output from the command includes code that you can use to connect to the database in a Java program.

For detailed instructions for using the dbping utility, see Using the WebLogic Java Utilities in the Administration Guide.

For More Information

This section provides references to documents and code examples that maybe helpful to you.

Documentation

For more information about using JDBC and jDrivers with WebLogic Server, see Programming WebLogic JDBC.

Code Examples

WebLogic Server provides several code examples to help you get started. Code examples are located in the SAMPLES_HOME\server\src\examples\jdbc\mssqlserver4 directory, where SAMPLES_HOME is the top-level directory for all samples and examples for the WebLogic Platform (c:\bea\weblogic700\samples, by default).

Using the SQL Server 2000 Driver for JDBC from Microsoft

The Microsoft SQL Server 2000 Driver for JDBC, available from Microsoft's MSDN Web site, is a Type 4 JDBC driver that supports a subset of the JDBC 2.0 Optional Package (see the driver documentation for details). This driver provides JDBC access to SQL Server 2000 through any Java-enabled applet, application, or application server.

The Microsoft SQL Server 2000 Driver for JDBC is available for download to all licensed SQL Server 2000 customers at no charge.

Downloading the Driver

To download the Microsoft SQL Server 2000 Driver for JDBC, go to the Microsoft MSDN Web site and follow the directions on the page.

Installing the Driver on a Windows System

1. Download the setup.exe file to a temporary directory on your local computer.

2. Run setup.exe from the temporary directory.

3. Follow the on-screen instructions.

Installing the Driver on a Unix System

1. Copy the mssqlserver.tar file to a temporary directory on your machine.

2. Change to the temporary directory and untar the contents of the file.

   Enter: tar -xvf mssqlserver.tar

   The contents of the tar file appear in the directory.

3. Run the installation script.

   Enter: install.ksh

4. You are asked if you want to view the license agreement.

   Press ENTER to proceed.

5. After viewing the license agreement, press ENTER to accept it or N to exit.

6. When asked to specify an installation directory, enter the full path to the directory.

7. The script displays a message when the installation is complete.