The MS SQL Server Driver

The MS SQL Server Driver

The BEA WebLogic Type 4 JDBC MS SQL Server driver (the "SQL Server driver") supports Microsoft SQL Server 7.0 and SQL Server 2000 (including SP1, SP2, and SP3) on Windows via Tabular DataStream (TDS).
To use JDBC distributed transactions through JTA, install stored procedures for SQL Server. See Installing Stored Procedures for JTA for details. Note that the BEA WebLogic Type 4 JDBC MS SQL Server driver (the subject of this chapter) replaces the WebLogic jDriver for Microsoft SQL Server, which is deprecated. The new driver offers JDBC 3.0 compliance, support for some JDBC 2.0 extensions, and better performance. BEA recommends that you use the new BEA WebLogic Type 4 JDBC MS SQL Server driver in place of the WebLogic jDriver for Microsoft SQL Server.
The following sections describe how to configure and use the BEA WebLogic Type 4 JDBC MS SQL Server driver:
Driver Class
URL
MS SQL Server Connection Properties
Sample Connection Pool Configuration
Data Types
SQL Escape Sequences
Isolation Levels
Using Scrollable Cursors
Server-Side Updatable Cursors
Installing Stored Procedures for JTA
Large Object (LOB) Support
Batch Inserts and Updates
Auto-Generated Keys Support

Driver Class

The driver classes for the BEA WebLogic Type 4 JDBC MS SQL Server driver are:
XA: weblogic.jdbcx.sqlserver.SQLServerDataSource
Non-XA: weblogic.jdbc.sqlserver.SQLServerDriver

URL

Microsoft SQL Server supports multiple instances of a SQL Server database running concurrently on the same server. An instance is identified by an instance name.
To connect to a named instance using a connection URL, use the following URL format:
jdbc:bea:sqlserver://server_name\\instance_name 
Note that the first back slash character (\) in \\instance_name is an escape character.
where:
server_name is the IP address or hostname of the server.
instance_name is the name of the instance to which you want to connect on the server.
For example, the following connection URL connects to an instance named instance1 on server1:
jdbc:bea:sqlserver://server1\\instance1;User=test;Password=secret
The MS SQL Server driver also supports the AlternateServers option for connection failover. See Connection Failover with AlternateServers.

MS SQL Server Connection Properties

Table 4-1 lists the JDBC connection properties supported by the MS SQL Server driver, and describes each property. You can use these connection properties in a JDBC connection pool configuration in your WebLogic Server domain. To specify a property, use the following form in the JDBC connection pool configuration:
property=value
Note: All connection string property names are case-insensitive. For example, Password is the same as password.

Property

Description
DatabaseNameOPTIONAL The name of the database to which you want to connect.
LoginTimeout OPTIONAL The maximum time in seconds that attempts to create a database connection will wait. A value of 0 specifies that the timeout is the default system timeout if there is one; otherwise it specifies that there is no timeout.
Password A case-insensitive password used to connect to your SQL Server database. A password is required only if security is enabled on your database. If so, contact your system administrator to get your password.
PortNumberOPTIONAL The TCP port on which the database server listens for connections. The default is 1433.
SelectMethodOPTIONAL {direct | cursor}. A hint to the SQL Server driver that determines whether the driver should request a database cursor for Select statements. Performance and behavior of the driver are affected by this property, which is defined as a hint because the driver may not always be able to satisfy the requested method.
Direct - When the driver uses the direct method, the database server sends the complete result set in a single response to the driver when responding to a query. Normally, responses are not cached by the driver. Using this method, the driver must process all the response to a query before another query is submitted. If another query is submitted (using a different statement on the same connection, for example), the driver caches the response to the first query before submitting the second query. Because the driver must cache the responses when there are multiple active queries, avoid using the direct method when using multiple open result sets that contain large amounts of data.

Cursor - When the driver uses the cursor method, a server-side cursor is generated. The rows are fetched from the server in blocks. The JDBC Statement method setFetchSize can be used to control the number of rows that are retrieved for each request. Performance tests show that the value of setFetchSize significantly impacts performance when the cursor method is used. There is no simple rule for determining the setFetchSize value that you should use. We recommend that you experiment with different setFetchSize values to find out which value gives the best performance for your application. The cursor method is useful for queries that produce a large amount of data, particularly if multiple open result sets are used.
The default is direct.
Note: You must add selectMethod=cursor to the list of connection properties in the connection pool in order to use connections in a transactional mode. This enables your applications to have multiple concurrent statements open from a given connection, which is required for pooled connections.

Without setting selectMethod=cursor, this JDBC driver creates an internal cloned connection for each concurrent statement, each as a different DBMS user, which makes it impossible to concurrently commit transactions and may cause deadlocks.
SendStringParametersAsUnicodeOPTIONAL {true | false}. Determines whether string parameters are sent to the SQL Server database in Unicode or in the default character encoding of the database:
True - string parameters are sent to SQL Server in Unicode.

False - string parameters are sent in the default encoding, which can improve performance because the server does not need to convert Unicode characters to the default encoding. You should, however, use default encoding only if the parameter string data you specify is consistent with the default encoding of the database.
The default is true.
ServerName The name or IP address of the database server.To connect to a named instance, specify server_name\instance_name for this property, where server_name is the IP address and instance_name is the name of the instance to which you want to connect on the specified server.
User The case-insensitive user name used to connect to your SQL Server database. A user name is required only if security is enabled on your database. If so, contact your system administrator to get your user name.
WSIDOPTIONAL The workstation ID, which typically is the network name of the computer on which the application resides. If specified, this value is stored in the master.dbo.sysprocesses column, hostname, and can be returned by sp_who and the Transact-SQL HOST_NAME function.

Sample Connection Pool Configuration

Table 4-2 lists configuration attributes for a sample WebLogic Server connection pool that uses the non-XA version of the WebLogic Type 4 MS SQL Server JDBC driver. Table 4-3 lists configuration attributes for a sample WebLogic Server connection pool that uses the XA version of the driver.

Attribute

Value
URL jdbc:bea:sqlserver://dbserver1:1433
Driver Class Name weblogic.jdbc.sqlserver.SQLServerDriver
Properties user=username
DatabaseName=dbname
Password password
Target serverName

Attribute

Value
URL jdbc:bea:sqlserver://dbserver1:1433
Driver Class Name weblogic.jdbcx.sqlserver.SQLServerDataSource
Properties user=username
DatabaseName=dbname
selectMethod=cursor
SupportsLocalTransaction true
KeepXAConnTillTxComplete true
Password password
Target serverName

Data Types

Table 4-4 lists the data types supported by the SQL Server driver in SQL Server 7 and SQL Server 2000 and how they are mapped to the JDBC data types.

SQL Server Data Type

JDBC Data Type
binary BINARY
bit BIT
char CHAR
datetime TIMESTAMP
decimal DECIMAL
decimal() identity DECIMAL
float FLOAT
image LONGVARBINARY
int INTEGER
int identity INTEGER
money DECIMAL
nchar CHAR
ntext LONGVARCHAR
numeric NUMERIC
numeric() identity NUMERIC
nvarchar VARCHAR
real REAL
smalldatetime TIMESTAMP
smallint SMALLINT
smallint identity SMALLINT
smallmoney DECIMAL
sysname VARCHAR
text LONGVARCHAR
timestamp BINARY
tinyint TINYINT
tinyint identity TINYINT
uniqueidentifier CHAR
varbinary VARBINARY
varchar VARCHAR

Table 4-5 lists additional data types supported by SQL Server 2000 only.

SQL Server Data Type

JDBC Data Type
bigint BIGINT
bigint identity BIGINT
sql_variant VARCHAR

SQL Escape Sequences

See SQL Escape Sequences for JDBC, for information about the SQL escape sequences supported by the SQL Server driver.

Isolation Levels

SQL Server supports transaction isolation levels Read Committed, Read Uncommitted, Repeatable Read, and Serializable. The default is Read Committed.

Using Scrollable Cursors

The SQL Server driver supports scroll-sensitive result sets, scroll-insensitive result sets, and updatable result sets.
Note: When the SQL Server driver cannot support the requested result set type or concurrency, it automatically downgrades the cursor and generates one or more SQLWarnings with detailed information.

Server-Side Updatable Cursors

In most cases, using server-side updatable cursors is faster, but server-side updatable cursors cannot be used with insensitive result sets or with sensitive result sets that do not have a primary key. By default, the MS SQL Server driver allows insensitive result sets and sensitive result sets that do not contain a primary key to be updatable. To use server-side cursors when an updatable result set is requested, you set the UseServerSideUpdatableCursors connection property.
When the UseServerSideUpdatableCursors property is set to true and a scroll-insensitive updatable result set is requested, the driver downgrades the request to a scroll-insensitive read-only result set. Similarly, when a scroll-sensitive updatable result set is requested and the table does not contain a primary key, the driver downgrades the request to a scroll-sensitive read-only result set. In either case, a warning is generated.
When server-side updatable cursors are used with sensitive result sets that contain a primary key, any changes you make to the result set are visible. Using the default behavior of the driver, those changes would not be visible.

Installing Stored Procedures for JTA

To use JDBC distributed transactions through JTA, your system administrator should use the following procedure to install Microsoft SQL Server JDBC XA procedures. This procedure must be repeated for each MS SQL Server installation that will be involved in a distributed transaction.
Note: If you install a patch on your Microsoft SQL Server DBMS installation, reinstall the stored procedures for JTA (as described below).

To install stored procedures for JTA:
Copy the sqljdbc.dll and instjdbc.sql files from the WL_HOME\server\lib directory to the SQL_Server_Root/bin directory of the MS SQL Server database server, where WL_HOME is the directory in which WebLogic server is installed, typically c:\bea\weblogic81.
Note: If you are installing stored procedures on a database server with multiple Microsoft SQL Server instances, each running SQL Server instance must be able to locate the sqljdbc.dll file. Therefore the sqljdbc.dll file needs to be anywhere on the global PATH or on the application-specific path. For the application-specific path, place the sqljdbc.dll file into the <drive>:\Program Files\Microsoft SQL Server\MSSQL$<Instance 1 Name>\Binn directory for each instance.
From the database server, use the OSQL utility to run the instjdbc.sql script. The system administrator should back up the master database before running instjdbc.sql.
At a command prompt, use the following syntax to run instjdbc.sql:
OSQL -Usa -Psa_password -Sserver_name -ilocation\instjdbc.sql
where:
sa_password is the password of the system administrator.
server_name is the name of the server on which SQL Server resides.
location is the full path to instjdbc.sql. (You copied this script to the SQL_Server_Root/bin directory in step 1.)
The instjdbc.sql script generates many messages. In general, these messages can be ignored; however, the system administrator should scan the output for any messages that may indicate an execution error. The last message should indicate that instjdbc.sql ran successfully. The script fails when there is insufficient space available in the master database to store the JDBC XA procedures or to log changes to existing procedures.
Start the DTC (distributed transaction coordinator) service for the Microsoft SQL Server database.
Using the Microsoft SQL Server Enterprise Manager, create a SQL Server login on the database server. Grant the new login access to the database on the server with the following roles:
public
db_owner
db_accessadmin
db_securityadmin
db_ddladmin
db_datareader
db_datawriter
Figure 4-1 Granting Access and Permissions in the SQL Server Enterprise Manager

Use the username and password for this new login when configuring a JDBC connection pool in your WebLogic Server domain. See Table 4-3 for more details. WebLogic Server will create database connections in the JDBC connection pool using the database user account. Applications will request a database connection from the connection pool through a data source.
Note: See the WebLogic Server Release Notes for known issues about transactions and the Weblogic Type 4 JDBC MS SQL Server driver.

Large Object (LOB) Support

Although SQL Server does not define a BLOB or CLOB data type, the SQL Server driver allows you to retrieve and update long data, LONGVARBINARY and LONGVARCHAR data, using JDBC methods designed for Blobs and Clobs. When using these methods to update long data as Blobs or Clobs, the updates are made to the local copy of the data contained in the Blob or Clob object.
Retrieving and updating long data using JDBC methods designed for Blobs and Clobs provides some of the same advantages as retrieving and updating Blobs and Clobs. For example, using Blobs and Clobs:
Provides random access to data
Allows searching for patterns in the data, such as retrieving long data that begins with a specific character string
Allows determining the length of the data before the data is actually retrieved

To provide these advantages of Blobs and Clobs, data must be cached. Because data is cached, you will incur a performance penalty, particularly if the data is read once sequentially. This performance penalty can be severe if the size of the long data is larger than available memory.

Batch Inserts and Updates

When the SQL Server detects an error in a statement or parameter set in a batch Insert or Update, the SQL Server driver generates a BatchUpdateException and continues to execute the remaining statements or parameter sets in the batch. The array of update counts contained in the BatchUpdateException will contain one entry for each statement or parameter set. Any entries for statements or parameter sets that failed will contain the value Statement.EXECUTE_FAILED.

Auto-Generated Keys Support

The MS SQL Server driver supports retrieving the values of auto-generated keys. How you return those values depends on whether you are using an Insert statement that contains parameters:
When using an Insert statement that contains no parameters, the MS SQL Server driver supports the following form of the Statement.execute and Statement.executeUpdate methods to inform the driver to return the values of auto-generated keys:
Statement.execute (String sql, int autoGeneratedKeys)
Statement.executeUpdate (String sql, int autoGeneratedKeys)
When using a Insert statement that contains parameters, the MS SQL Server driver supports the following form of the Connection.prepareStatement method to inform the driver to return the values of auto-generated keys:
Connection.prepareStatement (String sql, int autoGeneratedKeys)

The application fetches the values of generated keys from the driver using the Statement.getGeneratedKeys () method.

Property	Description
DatabaseNameOPTIONAL	The name of the database to which you want to connect.
LoginTimeout OPTIONAL	The maximum time in seconds that attempts to create a database connection will wait. A value of 0 specifies that the timeout is the default system timeout if there is one; otherwise it specifies that there is no timeout.
Password	A case-insensitive password used to connect to your SQL Server database. A password is required only if security is enabled on your database. If so, contact your system administrator to get your password.
PortNumberOPTIONAL	The TCP port on which the database server listens for connections. The default is 1433.
SelectMethodOPTIONAL	{direct \| cursor}. A hint to the SQL Server driver that determines whether the driver should request a database cursor for Select statements. Performance and behavior of the driver are affected by this property, which is defined as a hint because the driver may not always be able to satisfy the requested method. Direct - When the driver uses the direct method, the database server sends the complete result set in a single response to the driver when responding to a query. Normally, responses are not cached by the driver. Using this method, the driver must process all the response to a query before another query is submitted. If another query is submitted (using a different statement on the same connection, for example), the driver caches the response to the first query before submitting the second query. Because the driver must cache the responses when there are multiple active queries, avoid using the direct method when using multiple open result sets that contain large amounts of data. Cursor - When the driver uses the cursor method, a server-side cursor is generated. The rows are fetched from the server in blocks. The JDBC Statement method setFetchSize can be used to control the number of rows that are retrieved for each request. Performance tests show that the value of setFetchSize significantly impacts performance when the cursor method is used. There is no simple rule for determining the setFetchSize value that you should use. We recommend that you experiment with different setFetchSize values to find out which value gives the best performance for your application. The cursor method is useful for queries that produce a large amount of data, particularly if multiple open result sets are used. The default is direct. Note: You must add selectMethod=cursor to the list of connection properties in the connection pool in order to use connections in a transactional mode. This enables your applications to have multiple concurrent statements open from a given connection, which is required for pooled connections. Without setting selectMethod=cursor, this JDBC driver creates an internal cloned connection for each concurrent statement, each as a different DBMS user, which makes it impossible to concurrently commit transactions and may cause deadlocks.
SendStringParametersAsUnicodeOPTIONAL	{true \| false}. Determines whether string parameters are sent to the SQL Server database in Unicode or in the default character encoding of the database: True - string parameters are sent to SQL Server in Unicode. False - string parameters are sent in the default encoding, which can improve performance because the server does not need to convert Unicode characters to the default encoding. You should, however, use default encoding only if the parameter string data you specify is consistent with the default encoding of the database. The default is true.
ServerName	The name or IP address of the database server.To connect to a named instance, specify server_name\instance_name for this property, where server_name is the IP address and instance_name is the name of the instance to which you want to connect on the specified server.
User	The case-insensitive user name used to connect to your SQL Server database. A user name is required only if security is enabled on your database. If so, contact your system administrator to get your user name.
WSIDOPTIONAL	The workstation ID, which typically is the network name of the computer on which the application resides. If specified, this value is stored in the master.dbo.sysprocesses column, hostname, and can be returned by sp_who and the Transact-SQL HOST_NAME function.

Attribute	Value
URL	jdbc:bea:sqlserver://dbserver1:1433
Driver Class Name	weblogic.jdbc.sqlserver.SQLServerDriver
Properties	user=username DatabaseName=dbname
Password	password
Target	serverName

Attribute	Value
URL	jdbc:bea:sqlserver://dbserver1:1433
Driver Class Name	weblogic.jdbcx.sqlserver.SQLServerDataSource
Properties	user=username DatabaseName=dbname selectMethod=cursor
SupportsLocalTransaction	true
KeepXAConnTillTxComplete	true
Password	password
Target	serverName

SQL Server Data Type	JDBC Data Type
binary	BINARY
bit	BIT
char	CHAR
datetime	TIMESTAMP
decimal	DECIMAL
decimal() identity	DECIMAL
float	FLOAT
image	LONGVARBINARY
int	INTEGER
int identity	INTEGER
money	DECIMAL
nchar	CHAR
ntext	LONGVARCHAR
numeric	NUMERIC
numeric() identity	NUMERIC
nvarchar	VARCHAR
real	REAL
smalldatetime	TIMESTAMP
smallint	SMALLINT
smallint identity	SMALLINT
smallmoney	DECIMAL
sysname	VARCHAR
text	LONGVARCHAR
timestamp	BINARY
tinyint	TINYINT
tinyint identity	TINYINT
uniqueidentifier	CHAR
varbinary	VARBINARY
varchar	VARCHAR