Skip navigation.
Downloads | Product Documentation | Support

WebLogic Type 4 JDBC Drivers

# The MS SQL Server Driver

The following sections describe how to configure and use the BEA WebLogic Type 4 JDBC SQL Server driver:

- SQL Server Database Version Support
- Driver Class
- URL
- Connecting to Named Instances
- SQL Server Connection Properties
- Performance Considerations
- 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
- Parameter Metadata Support
- ResultSet MetaData Support
- Rowset Support
- Auto-Generated Keys Support

Note: 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.

# SQL Server Database Version Support

The BEA WebLogic Type 4 JDBC MS SQL Server driver (the "SQL Server driver") supports the following database management system versions:

- Microsoft SQL Server 7.0
- Microsoft SQL Server 2000 (including SP1, SP2, and SP3a)
- Microsoft SQL Server 2000 Desktop Engine (MSDE 2000)
- Microsoft SQL Server 2000 Enterprise Edition (64-bit)

To use JDBC distributed transactions through JTA, you must install stored procedures for SQL Server. See Installing Stored Procedures for JTA for details.

# 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

To connect to a Microsoft SQL Server database, use the following URL format:

>     jdbc:bea:sqlserver://dbserver:port

# Connecting to Named Instances

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: 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;Pasword=secret

# SQL Server Connection Properties

Table 4-1 lists the JDBC connection properties supported by the SQL Server driver, and describes each property. You can use these connection properties in a JDBC data source configuration in your WebLogic Server domain. To specify a property, use the following form in the JDBC data source configuration:

>     property=value

Note: All connection string property names are case-insensitive. For example, Password is the same as password.

Table 4-1 SQL Server Connection Properties

Property | Description
--- | ---
AlwaysReportTriggerResults OPTIONAL | {true | false}. Determines how the driver reports results generated by database triggers (procedures that are stored in the database and executed, or fired, when a table is modified). If set to true, the driver returns all results, including results generated by triggers. Multiple trigger results are returned one at a time. Use the Statement.getMoreResults method to retrieve individual trigger results. Warnings and errors are reported in the results as they are encountered. If set to false, the driver does not report trigger results if the statement is a single Insert, Update, or Delete statement. In this case, the only result that is returned is the update count generated by the statement that was executed (if errors do not occur). Although trigger results are ignored, any errors generated by the trigger are reported. Any warnings generated by the trigger are enqueued. If errors are reported, the update count is not reported. The default is false.
CodePageOverride OPTIONAL | Specifies the code page the driver uses when converting character data. The specified code page overrides the default database code page. All character data retrieved from or written to the database is converted using the specified code page. The value must be a string containing the name of a valid code page supported by your Java Virtual Machine, for example, CodePageOverride=CP950. If a value is set for the CodePageOverride property and the SendStringParametersAsUnicode property is set to true, the driver ignores the SendStringParametersAsUnicode property and generates a warning. The driver always sends parameters using the code page specified by CodePageOverride if this property is specified.
ConnectionRetryCount OPTIONAL | The number of times the driver retries connections to a database server until a successful connection is established. Valid values are 0 and any positive integer. If set to 0, the driver does not retry a connection to the list of database servers if a connection is not established on the driver's first pass through the list. The default is 0.
ConnectionRetryDelay OPTIONAL | The number of seconds the driver waits before retrying connection attempts when ConnectionRetryCount is set to a positive integer. The default is 3.
DatabaseName OPTIONAL | The name of the database to which you want to connect.
HostProcess OPTIONAL | The process ID of the application connecting to Microsoft SQL Server. The value of this property appears in the hostprocess column of the master.dbo.sysprocesses table and may be useful for database administration purposes. The default is 0.
InsensitiveResultSetBufferSize OPTIONAL | {-1 | 0 | x}. Determines the amount of memory used by the driver to cache insensitive result set data. It must have one of the following values: If set to -1, the driver caches all insensitive result set data in memory. If the size of the result set exceeds available memory, an OutOfMemoryException is generated. Because the need to write result set data to disk is eliminated, the driver processes the data more efficiently. If set to 0, the driver caches all insensitive result set data in memory, up to a maximum of 2 GB. If the size of the result set data exceeds available memory, the driver pages the result set data to disk. Because result set data may be written to disk, the driver may have to reformat the data to write it correctly to disk. If set to x, where x is a positive integer, the driver caches all insensitive result set data in memory, using this value to set the size (in KB) of the memory buffer for caching insensitive result set data. If the size of the result set data exceeds the buffer size, the driver pages the result set data to disk. Because the result set data may be written to disk, the driver may have to reformat the data to write it correctly to disk. Specifying a buffer size that is a power of 2 results in more efficient memory use. The default is 2048 (KB).
NetAddress OPTIONAL | The Media Access Control (MAC) address of the network interface card of the application connecting to Microsoft SQL Server. The value of this property appears in the net_address column of the master.dbo.sysprocesses table and may be useful for database administration purposes. The default is 000000000000.
Password | A case-insensitive password used to connect to your Microsoft SQL Server database.
PortNumber OPTIONAL | The TCP port of the primary database server that is listening for connections to the Microsoft SQL Server database. The default is 1433.
ProgramName OPTIONAL | The name of the application connecting to Microsoft SQL Server. The value of this property appears in the program_name column of the master.dbo.sysprocesses table and may be useful for database administration purposes. The default is an empty string.
SelectMethod OPTIONAL | {direct | cursor}. A hint to the driver that determines whether the driver requests 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. A server-side database cursor is not created. Typically, 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. Typically, the Direct method performs better than the Cursor method. Cursor—When the driver uses the Cursor method, a server-side cursor is requested. The rows are retrieved from the server in blocks when returning forward-only result sets. 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. BEA recommends that you experiment with different setFetchSize values to determine 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.
SendStringParametersAsUnicode OPTIONAL | {true | false}. Determines whether string parameters are sent to the Microsoft SQL Server database in Unicode or in the default character encoding of the database. If set to true, string parameters are sent to Microsoft SQL Server in Unicode. If set to 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 the same as the default encoding of the database. The default is true. If a value is specified for the CodePageOverride property and this property is set to true, this property is ignored and a warning is generated.
ServerName | Specifies either the IP address or the server name (if your network supports named servers) of the primary database server. For example, 122.23.15.12 or SQLServerServer. 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 Microsoft SQL Server database.
UseServerSideUpdatableCursors | {true | false}. Determines whether the driver uses server-side cursors when an updatable result set is requested. If set to true, server-side updatable cursors are created when an updatable result set is requested. If set to false, the default updatable result set functionality is used. The default is false. See Server-Side Updatable Cursors for more information about using server-side updatable cursors.
WSID OPTIONAL | 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 hostname column of the master.dbo.sysprocesses table and can be returned by sp_who and the Transact-SQL HOST_NAME function. The value can be useful for database administration purposes. The default is an empty string.
XATransactionGroup OPTIONAL | The transaction group ID that identifies any transactions initiated by the connection. This ID can be used for distributed transaction cleanup purposes.

# Performance Considerations

Setting the following connection properties for the SQL Server driver as described in the following list can improve performance for your applications:

## InsensitiveResultSetBufferSize

To improve performance when using scroll-insensitive result sets, the driver can cache the result set data in memory instead of writing it to disk. By default, the driver caches 2 MB of insensitive result set data in memory and writes any remaining result set data to disk. Performance can be improved by increasing the amount of memory used by the driver before writing data to disk or by forcing the driver to never write insensitive result set data to disk. The maximum cache size setting is 2 GB.

## MaxPooledStatements

To improve performance, the driver's own internal prepared statement pooling should be enabled when the driver does not run from within an application server or from within another application that does not provide its own prepared statement pooling. When the driver's internal prepared statement pooling is enabled, the driver caches a certain number of prepared statements created by an application. For example, if the `MaxPooledStatements` property is set to 20, the driver caches the last 20 prepared statements created by the application. If the value set for this property is greater than the number of prepared statements used by the application, all prepared statements are cached.

## ResultSetMetaDataOptions

By default, the SQL Server driver skips the additional processing required to return the correct table name for each column in the result set when the `ResultSetMetaData.getTableName()` method is called. Because of this, the `getTableName()` method may return an empty string for each column in the result set. If you know that your application does not require table name information, this setting provides the best performance.

See ResultSet MetaData Support for more information about returning ResultSet metadata.

## SelectMethod

In most cases, using server-side database cursors impacts performance negatively. However, if the following variables are true for your application, the best setting for this property is cursor, which means use server-side database cursors:

- Your application contains queries that retrieve large amounts of data.
- Your application executes a SQL statement before processing or closing a previous large result set and does this multiple times.
- Large result sets returned by your application use forward-only cursors.

## SendStringParametersAsUnicode

If all the data accessed by your application is stored in the database using the default database character encoding, setting `SendStringParametersAsUnicode` to false can improve performance.

## UseServerSideUpdatableCursors

In most cases, using server-side updatable cursors improves performance. However, this type of cursor cannot be used with insensitive result sets or with sensitive results sets that are not generated from a database table that contains a primary key.

See Server-Side Updatable Cursors for more information about using server-side updatable cursors.

# Data Types

Table 4-2 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.

Table 4-2 Data Types Supported by SQL Server 7 and SQL Server 2000

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-3 lists additional data types supported by SQL Server 2000 only.

Table 4-3 Addition Data Types Supported by SQL Server 2000

SQL Server Data Type | JDBC Data Type
--- | ---
bigint | BIGINT
bigint identity | BIGINT
sql_variant | VARCHAR

See GetTypeInfo for more information about data types.

# SQL Escape Sequences

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

# Isolation Levels

The SQL Server driver supports the Read Committed, Read Uncommitted, Repeatable Read, and Serializable isolation levels. 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

The SQL Server driver can use client-side cursors or server-side cursors to support updatable result sets. By default, the SQL Server driver uses client-side cursors because this type of cursor can work with any result set type. Using server-side cursors typically can improve performance, but server-side cursors cannot be used with scroll-insensitive result sets or with scroll-sensitive result sets that are not generated from a database table that contains a primary key. To use server-side cursors, set the `UseServerSideUpdatableCursors` property to true.

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 from which the result set was generated does not contain a primary key, the driver downgrades the request to a scroll-sensitive read-only result set. In both cases, a warning is generated.

When server-side updatable cursors are used with sensitive result sets that were generated from a database table that contains a primary key, any changes you make to the result set are visible. Using the default behavior of the driver (`UseServerSideUpdatableCursors=false`), 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, you must reinstall the stored procedures for JTA (as described below). Also, some WebLogic Server service packs include driver updates and may require that you reinstall the stored procedures for JTA (as described below).

## To install stored procedures for JTA:

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

2. From the database server, use the ISQL 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`:

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

# Large Object (LOB) Support

Although Microsoft SQL Server does not define a Blob or Clob data type, the SQL Server driver allows you to retrieve and update long data, specifically 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

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

The SQL Server driver implementation for batch Inserts and Updates is JDBC 3.0 compliant. When the SQL Server driver detects an error in a statement or parameter set in a batch Insert or Update, it 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 contain one entry for each statement or parameter set. Any entries for statements or parameter sets that failed contain the value Statement.EXECUTE_FAILED.

# Parameter Metadata Support

The SQL Server driver supports returning parameter metadata as described in this section.

## Insert and Update Statements

The SQL Server driver supports returning parameter metadata for the following forms of Insert and Update statements:

- `INSERT INTO foo VALUES (?, ?, ?)`
- `INSERT INTO foo (col1, col2, col3) VALUES (?, ?, ?)`
- `UPDATE foo SET col1=?, col2=?, col3=? WHERE col1 operator? [{AND | OR} col2 operator ?]`

where `operator` is any of the following SQL operators: =, <, >, <=, >=, and <>.

## Select Statements

The SQL Server driver supports returning parameter metadata for Select statements that contain parameters in ANSI SQL 92 entry-level predicates, for example, such as COMPARISON, BETWEEN, IN, LIKE, and EXISTS predicate constructs. Refer to the ANSI SQL reference for detailed syntax.

Parameter metadata can be returned for a Select statement if one of the following conditions is true:

- The statement contains a predicate value expression that can be targeted against the source tables in the associated FROM clause. For example:

>     SELECT * FROM foo WHERE bar > ?

In this case, the value expression "bar" can be targeted against the table "foo" to determine the appropriate metadata for the parameter.

- The statement contains a predicate value expression part that is a nested query. The nested query's metadata must describe a single column. For example:

>     SELECT * FROM foo WHERE (SELECT x FROM y
>        WHERE z = 1) < ?

The following Select statements show further examples for which parameter metadata can be returned:

>     SELECT col1, col2 FROM foo WHERE col1 = ? and col2 > ?

>     SELECT ... WHERE colname = (SELECT col2 FROM t2
>        WHERE col3 = ?)

>     SELECT ... WHERE colname LIKE ?

>     SELECT ... WHERE colname BETWEEN ? and ?

>     SELECT ... WHERE colname IN (?, ?, ?)

>     SELECT ... WHERE EXISTS(SELECT ... FROM T2 WHERE col1 < ?)

ANSI SQL 92 entry-level predicates in a WHERE clause containing GROUP BY, HAVING, or ORDER BY statements are supported. For example:

>     SELECT * FROM t1 WHERE col = ? ORDER BY 1

Joins are supported. For example:

>     SELECT * FROM t1,t2 WHERE t1.col1 = ?

Fully qualified names and aliases are supported. For example:

>     SELECT a, b, c, d FROM T1 AS A, T2 AS B WHERE A.a = ?
>        and B.b = ?"

# ResultSet MetaData Support

If your application requires table name information, the SQL Server driver can return table name information in ResultSet metadata for Select statements. By setting the `ResultSetMetaDataOptions` property to 1, the SQL Server driver performs additional processing to determine the correct table name for each column in the result set when the `ResultSetMetaData.getTableName()` method is called. Otherwise, the `getTableName()` method may return an empty string for each column in the result set.

When the `ResultSetMetaDataOptions` property is set to 1 and the `ResultSetMetaData.getTableName()` method is called, the table name information that is returned by the SQL Server driver depends on whether the column in a result set maps to a column in a table in the database. For each column in a result set that maps to a column in a table in the database, the SQL Server driver returns the table name associated with that column. For columns in a result set that do not map to a column in a table (for example, aggregates and literals), the SQL Server driver returns an empty string.

The Select statements for which ResultSet metadata is returned may contain aliases, joins, and fully qualified names. The following queries are examples of Select statements for which the `ResultSetMetaData.getTableName()` method returns the correct table name for columns in the Select list:

>     SELECT id, name FROM Employee

>     SELECT E.id, E.name FROM Employee E

>     SELECT E.id, E.name AS EmployeeName FROM Employee E

>     SELECT E.id, E.name, I.location, I.phone FROM Employee E,

>        EmployeeInfo I WHERE E.id = I.id

>     SELECT id, name, location, phone FROM Employee,

>        EmployeeInfo WHERE id = empId

>     SELECT Employee.id, Employee.name, EmployeeInfo.location,

>        EmployeeInfo.phone FROM Employee, EmployeeInfo

>        WHERE Employee.id = EmployeeInfo.id

The table name returned by the driver for generated columns is an empty string. The following query is an example of a Select statement that returns a result set that contains a generated column (the column named "upper").

>     SELECT E.id, E.name as EmployeeName, {fn UCASE(E.name)}

>        AS upper FROM Employee E

The SQL Server driver also can return schema name and catalog name information when the `ResultSetMetaData.getSchemaName()` and `ResultSetMetaData.getCatalogName()` methods are called if the driver can determine that information. For example, for the following statement, the SQL Server driver returns "test" for the catalog name, "test1" for the schema name, and "foo" for the table name:

>     SELECT * FROM test.test1.foo

The additional processing required to return table name, schema name, and catalog name information is only performed if the `ResultSetMetaData.getTableName()`, `ResultSetMetaData.getSchemaName()`, or `ResultSetMetaData.getCatalogName()` methods are called.

# Rowset Support

The SQL Server driver supports any JSR 114 implementation of the RowSet interface, including:

- CachedRowSets
- FilteredRowSets
- WebRowSets
- JoinRowSets
- JDBCRowSets

See http://www.jcp.org/en/jsr/detail?id=114 for more information about JSR 114.

# Auto-Generated Keys Support

The SQL Server driver supports retrieving the values of auto-generated keys. An auto-generated key returned by the SQL Server driver is the value of an identity column.

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