| Oracle® Fusion Middleware Type 4 JDBC Drivers for Oracle WebLogic Server 11g Release 1 (10.3.4) Part Number E13753-03 |
|
|
View PDF |
| Oracle® Fusion Middleware Type 4 JDBC Drivers for Oracle WebLogic Server 11g Release 1 (10.3.4) Part Number E13753-03 |
|
|
View PDF |
The following sections describe how to configure and use the Oracle Type 4 JDBC SQL Server driver:
Using the Snapshot Isolation Level (Microsoft SQL Server 2005 and Higher)
Note:
The Oracle 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. Oracle recommends that you use the new Oracle Type 4 JDBC MS SQL Server driver in place of the WebLogic jDriver for Microsoft SQL Server.The driver classes for the Oracle Type 4 JDBC MS SQL Server driver are:
XA: weblogic.jdbcx.sqlserver.SQLServerDataSource Non-XA: weblogic.jdbc.sqlserver.SQLServerDriver
To connect to a Microsoft SQL Server database, use the following URL format:
jdbc:weblogic:sqlserver://hostname:port[;property=value[;...]]
where:
hostname is the TCP/IP address or TCP/IP host name of the server to which you are connecting. See Using IP Addresses for details on using IP addresses.
Note:
Untrusted applets cannot open a socket to a machine other than the originating host.port is the number of the TCP/IP port.
property=value specifies connection properties. For a list of connection properties and their valid values, see SQL Server Connection Properties.
For example:
jdbc:weblogic:sqlserver://server1:1433;User=test;Password=secret
See Connecting to Named Instances for instructions on connecting to named instances.
Microsoft SQL Server and Microsoft SQL Server 2005 support 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:weblogic: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:weblogic:sqlserver://server1\\instance1;User=test;Pasword=secret
Table 6-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 6-1 SQL Server Connection Properties
| Property | Description |
|---|---|
|
AccountingInfo |
Accounting information to be stored in the database. This value is stored locally and is used for database administration/monitoring purposes Data type: string Valid Values: |
|
AlternateServers |
A list of alternate database servers that is used to failover new or lost connections, depending on the failover method selected. See the Data type: String Valid Values:
The server name (
Example The following URL contains alternate server entries for
Default: None Data type: String |
|
AlwaysReportTriggerResults |
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). For Microsoft SQL Server 2005, this includes triggers fired by Data Definition Language (DDL) events. Valid Values: If If
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 |
|
ApplicationName |
The name of the application to be stored in the database. For Microsoft SQL Server 2000 and higher, this value sets the program_name value in the sysprocesses table in the database. For Microsoft SQL Server 7, this value is stored locally. This value is used for database administration/monitoring purposes. Valid Values: NOTE: Your database may impose character length restrictions on the value. If the value exceeds a restriction, the driver truncates it. Default: empty string Data Type: String Alias: |
|
AuthenticationMethod |
Determines which authentication method the driver uses when establishing a connection. If the specified authentication method is not supported by the database server, the connection fails and the driver throws an exception. Valid Values: If If If If The Note: The values type4, type2, and none are deprecated, but are recognized for backward compatibility. We recommend that you use the kerberos, ntlm, and userIdPassword value, respectively, instead. See Authentication for more information about using authentication with the SQL Server driver. Default: Data Type: String |
|
BulkLoadBatchSize |
Provides a suggestion to the driver for the number of rows to load to the database at a time when bulk loading data. Performance can be improved by increasing the number of rows the driver loads at a time because fewer network round trips are required. Be aware that increasing the number of rows that are loaded also causes the driver to consume more memory on the client. NOTES:
Valid Values: Default: 2048 |
|
BulkLoadOptions |
Enables options of the bulk load protocol of which the driver can take advantage. Valid Values This value is the cumulative value of all enabled options. The following list describes the value and the corresponding option that is enabled:
If 0, all the options are disabled. Example: A value of 67 means the Default: 0 Data Type long |
|
ClientHostName |
The host name, or workstation ID, of the client machine to be stored in the database. For Microsoft SQL Server 2000 and higher, this value sets the hostname value of the sysprocesses table in the database. For Microsoft SQL Server 7, this value is stored locally. This value is used for database administration/monitoring purposes. Valid Values: NOTE: Your database may impose character length restrictions on the value. If the value exceeds a restriction, the driver truncates it. Default: empty string Data Typ:e String Alias: |
|
CodePageOverride |
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 JVM, for example, By default, the driver automatically determines which code page to use to convert Character data. Use this property only if you need to change the driver's default behavior. If a value is set for the |
|
ConnectionRetryCount |
The number of times the driver retries connection attempts to the primary database server, and if specified, alternate servers until a successful connection is established. NOTE: If an application sets a login timeout value (for example, using Valid Values: 0 | If 0, the driver does not try to reconnect after the initial unsuccessful attempt. If NOTE: The ConnectionRetryDelay property specifies the wait interval, in seconds, to occur between retry attempts. Example If this property is set to 2 and alternate servers are specified using the Default: 5 (seconds) Data Type: int |
|
ConnectionRetryDelay |
The number of seconds the driver waits between connection retry attempts when ConnectionRetryCount is set to a positive integer. Valid Values: 0 | If 0, the driver does not delay between retries. If Example: If Default: 1 (second) Data Type: int |
|
ConvertNull |
Controls how data conversions are handled for null values. Valid Values: 0 | 1 If 1, the driver checks the data type being requested against the data type of the table column storing the data. If a conversion between the requested type and column type is not defined, the driver generates an "unsupported data conversion" exception regardless of the data type of the column value. If 0, the driver does not perform the data type check if the value of the column is null. This allows null values to be returned even though a conversion between the requested type and the column type is undefined. Default: 1 Data Type: int |
|
Database |
An alias for the DatabaseName property. |
|
DatabaseName |
The name of the database to which you want to connect. Valid Values: Default: None Alias:
|
|
DescribeParameters |
Controls whether the driver attempts to determine, at execute time, how to send String parameters to the server based on the database data type. Sending String parameters as the type the database expects improves performance and prevents unexpected locking issues caused by data type mismatches. Valid Values: The If If Default: Data Type: String |
|
EnableBulkLoad |
Specifies whether the driver uses the native bulk load protocols in the database instead of the batch mechanism for batch inserts. Bulk load bypasses the data parsing that is usually done by the database, providing an additional performance gain over batch operations. This property allows existing applications with batch inserts to take advantage of bulk load without requiring changes to the application code.Valid Values: If If Default: Data Type: boolean |
|
EnableCancelTimeout |
Determines whether a cancel request sent as the result of a query timing out is subject to the same query timeout value as the statement it cancels. Valid Values: If If Default: Data Type: boolean |
|
EncryptionMethod |
Determines whether data is encrypted and decrypted when it is transmitted over the network between the driver and database server. NOTE: Connection hangs can occur when the driver is configured for SSL and the database server does not support SSL. You may want to set a login timeout using the LoginTimeout property to avoid problems when connecting to a server that does not support SSL. Valid Values: If set to If set to If set to If SSL is enabled, the following properties also apply:
NOTE: If SSL is enabled, the driver communicates with database protocol packets that are set by the server's default packet size. Any value set by the PacketSize property is ignored. Default: Data Type: String |
|
FailoverGranularity |
Determines whether the driver fails the entire failover process or continues with the process if exceptions occur while trying to reestablish a lost connection. This property is ignored if Valid Values: If set to If set to If set to If set to Default: Data Type: String |
|
FailoverMode |
Specifies the type of failover method the driver uses. Valid Values: If set to If set to If set to NOTES:
Default: Data Type: String |
|
FailoverPreconnect |
Specifies whether the driver tries to connect to the primary and an alternate server at the same time. This property is ignored if If set to If set to NOTE: The Default: Data Type: boolean |
|
HostNameInCertificate |
Specifies a host name for certificate validation when SSL encryption is enabled ( Valid Values: If a If Note: If multiple CN parts are present, the driver validates the host name against each CN part. If any one validation succeeds, a connection is established. If unspecified, the driver does not validate the host name in the certificate. If SSL encryption or certificate validation is not enabled, any value specified for this property is ignored. See Data Encryption for information about configuring for authentication. The default is an empty string. |
|
HostProcess |
An alias for the |
|
ImportStatementPool |
Specifies the path and file name of the file to be used to load the contents of the statement pool. When this property is specified, statements are imported into the statement pool from the specified file. If the driver cannot locate the specified file when establishing the connection, the connection fails and the driver throws an exception. Valid Values: Default: empty string Data Type: String |
|
InitializationString |
Specifies one or multiple SQL commands to be executed by the driver after it has established the connection to the database and has performed all initialization for the connection. If the execution of a SQL command fails, the connection attempt also fails and the driver throws an exception indicating which SQL command or commands failed. Valid Values: Multiple commands must be separated by semicolons. In addition, if this property is specified in a connection URL, the entire value must be enclosed in parentheses when multiple commands are specified. Example The following connection URL sets the handling of null values to the Microsoft SQL Server default and allows delimited identifiers:
Default: None Data Type: String |
|
InsensitiveResultSetBufferSize |
Determines the amount of memory used by the driver to cache insensitive result set data. Valid Values If set to If set to If set to Default: 2048 Data Type: int |
|
JavaDoubleToString |
Determines which algorithm the driver uses when converting a double or float value to a string value. By default, the driver uses its own internal conversion algorithm, which improves performance. Valid Values true | false If set to If set to Default: Data Type: boolean |
|
JDBCBehavior |
Determines how the driver describes database data types that map to the following JDBC 4.0 data types: Valid Values: If set to If set to For example, for the fully qualified procedure Default: 1 Data Type: int |
|
LoadBalancing |
Determines whether the driver uses client load balancing in its attempts to connect to the database servers (primary and alternate). You can specify one or multiple alternate servers by setting the Valid Values: If set to If set to Default: Data Type: boolean |
|
LoadLibraryPath |
Specifies the directory for the DLL for NTLM authentication. The driver looks for the DLL in the specified directory. NOTE: When you install the driver, the NTLM authentication DLLs are installed in the Valid Values: If unspecified, the driver looks for the DLL in a directory on the Windows system path defined by the Default: NoneData Type: String |
|
LoginTimeout |
The amount of time, in seconds, that the driver waits for a connection to be established before timing out the connection request. Valid Values If set to If set to Default: Data Type: int |
|
LongDataCacheSize |
Determines whether the driver caches long data (images, pictures, long text, or binary data) in result sets. To improve performance, you can disable long data caching if your application retrieves columns in the order in which they are defined in the result set. Valid Values: If set to If set to If set to See Performance Considerations for information about configuring this property for optimal performance. Default: 2048 Data Type: int |
|
MaxPooledStatements |
The maximum number of pooled prepared statements for this connection. Setting Valid Values: If set to If set to Default: Data Type: int Alias: |
|
MaxStatements |
An alias for the |
|
NetAddress |
The Media Access Control (MAC) address of the network interface card of the application connecting to Microsoft SQL Server. This value is a string up to a maximum of 12 characters. The value of this property may be useful for database administration purposes. This value is stored in the net_address column of the:
The default is 000000000000. |
|
PacketSize |
Determines the number of bytes for each database protocol packet transferred from the database server to the client machine (Microsoft SQL Server refers to this packet as a network packet). Adjusting the packet size can improve performance. The optimal value depends on the typical size of data inserted, updated, or returned by the application and the environment in which it is running. Typically, larger packet sizes work better for large amounts of data. For example, if an application regularly returns character values that are 10,000 characters in length, using a value of 32 (16 KB) typically results in improved performance. Valid Values: If set to If set to If set to x, an integer from 1 to 128, the driver uses a packet size that is a multiple of 512 bytes. For example, See Performance Considerations for information about configuring this property for optimal performance. The default is |
|
Password |
A case-insensitive password used to connect to your Microsoft SQL Server database. A password is required only if SQL Server authentication is enabled on your database. If so, contact your system administrator to obtain your password. Valid Values: Default: None Data Type: String See Authentication for more information about configuring authentication. |
|
PortNumber |
The TCP port of the primary database server that is listening for connections to the Microsoft SQL Server database. This property is supported only for data source connections. Valid Values: Default: 1433 Data Type: int |
|
ProgramID |
The product and version information of the driver on the client to be stored in the database. For Microsoft SQL Server 2000 and higher, this value sets the hostprocess value in the sysprocesses table. For Microsoft SQL Server 7, this value is stored locally. This value is used for database administration/monitoring purposes. Valid Values: DDJVVRRM where:
Example: DDJ04100 Default: empty string Data Type: String Alias: |
|
ProgramName |
An alias for the |
|
QueryTimeout |
Sets the default query timeout (in seconds) for all statements created by a connection. Valid Values: If set to If set to If set to Default: 0 Data Type: int |
|
ReceiveStringParameterType |
Specifies how the driver describes String stored procedure output parameters to the database. Valid Values: If set to If set to If set to Default: Data Type: String |
|
ResultSetMetaDataOptions |
The SQL Server driver can return table name information in the ResultSet metadata for Select statements if your application requires that information. Valid Values: 0 | 1 If set to 0 (the default) and the ResultSetMetaData.getTableName() method is called, the driver does not perform additional processing to determine the correct table name for each column in the result set. In this case, the getTableName() method may return an empty string for each column in the result set. If set to 1 and the ResultSetMetaData.getTableName() method is called, the driver performs additional processing to determine the correct table name for each column in the result set. The 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. See ResultSet MetaData Support for more information about returning ResultSet metadata. Default: 0 Data Type: int |
|
SelectMethod |
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. Valid Values: If set to If set to See Performance Considerations for information about configuring this property for optimal performance. Default: Data Type: String |
|
SendStringParametersAsUnicode |
Determines whether string parameters are sent to the Microsoft SQL Server database in Unicode or in the default character encoding of the database. Valid Values: If set to If set to If a value is specified for the See Performance Considerations for information about configuring this property for optimal performance. Default: Data Type: boolean |
|
ServerName (REQUIRED) |
Specifies either the IP address in IPv4 or IPv6 format, or the server name (if your network supports named servers) of the primary database server or named instance. For example, 122.23.15.12 or SQLServerServer. Valid Values: To connect to a named instance, specify This property is supported only for data source connections. See Connecting to Named Instances for more information about connecting to named instances. Defaul:t None Data Type: String |
|
SnapshotSerializable |
For Microsoft SQL Server 2005 and higher only. Allows your application to use Snapshot Isolation for connections. This property is useful for applications that have the Valid Values: If set to NOTE: To use If set to Data Type: boolean |
|
SpyAttributes |
Enables Spy to log detailed information about calls issued by the driver on behalf of the application. Spy is not enabled by default. Valid Values: ( See Appendix D, "Tracking JDBC Calls with WebLogic JDBC Spy." NOTE: If coding a path on Windows to the log file in a Java string, the backslash character (\) must be preceded by the Java escape character, a backslash. For example:
Data Typ:e String |
|
TransactionMode |
Controls how the driver delimits the start of a local transaction. Valid Values: If set to If set to Default: Data Type: String |
|
TruncateFractionalSeconds |
Determines whether the driver truncates timestamp values to three fractional seconds. For example, a value of the Valid Values: If set to If set to Default: true Data Type: boolean |
|
TrustStore |
Specifies the directory of the truststore file to be used when SSL server authentication is used. The truststore file contains a list of the Certificate Authorities (CAs) that the client trusts. This value overrides the directory of the truststore file specified by the javax.net.ssl.trustStore Java system property. If this property is not specified, the truststore directory is specified by the javax.net.ssl.trustStore Java system property. This property is ignored if Valid Values: Default: None Data Type: String |
|
TrustStorePassword |
Specifies the password of the truststore file to be used when SSL server authentication is used. The truststore file contains a list of the Certificate Authorities (CAs) that the client trusts. This value overrides the password of the truststore file specified by the javax.net.ssl.trustStorePassword Java system property. If this property is not specified, the truststore password is specified by the javax.net.ssl.trustStorePassword Java system property. This property is ignored if Valid Values: Default: None Data Type: String |
|
User |
The case-insensitive user name used to connect to your Microsoft SQL Server database. A user name is required only if SQL Server authentication is enabled on your database. If so, contact your system administrator to obtain your user name. Valid Values: Default: None Data Typ: e String |
|
UseServerSideUpdatableCursors |
Determines whether the driver uses server-side cursors when an updatable result set is requested. Valid Values: If set to If set to See Server-Side Updatable Cursors for more information about using server-side updatable cursors. See Performance Considerations for information about configuring this property for optimal performance. Default: Data Type: boolean |
|
ValidateServerCertificate |
Determines whether the driver validates the certificate sent by the database server when SSL encryption is enabled ( Valid Values: If set to If set to Truststore information is specified using the TrustStore and TrustStorePassword properties or by using Java system properties. See Data Encryption for information about configuring for authentication. Default: Data Type: boolean |
|
WSID |
An alias for the |
|
XATransactionGroup |
The transaction group ID that identifies any transactions initiated by the connection. This ID can be used for distributed transaction cleanup purposes. Valid Values: You can use the See Distributed Transaction Cleanup for more information about distributed transaction cleanup. Default: None Data Type: String |
|
XMLDescribeType |
Determines whether the driver maps XML data to the LONGVARCHAR or LONGVARBINARY data type. Valid Values: If set to If set to See Returning and Inserting/Updating XML Data for more information. Default: None Data Type: String |
Setting the following connection properties for the SQL Server driver as described in the following list can improve performance for your applications.
For batch inserts, the driver can use native bulk load protocols instead of the batch mechanism. Bulk load bypasses the data parsing usually done by the database, providing an additional performance gain over batch operations. Set this property to true to allow existing applications with batch inserts to take advantage of bulk load without requiring changes to the code.
Data encryption may adversely affect performance because of the additional overhead (mainly CPU usage) required to encrypt and decrypt data.
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.
To improve performance when your application retrieves images, pictures, long text, or binary data, you can disable caching for long data on the client if your application retrieves long data column values in the order they are defined in the result set. If your application retrieves long data column values out of order, long data values must be cached on the client. In this case, performance can be improved by increasing the amount of memory used by the driver before writing data to disk.
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.
Typically, it is optimal for the client to use the maximum packet size that the server allows. This reduces the total number of round trips required to return data to the client, thus improving performance. Therefore, performance can be improved if this property is set to the maximum packet size of the database server.
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.
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 return 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.
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.
You must have your Microsoft SQL Server 2005 or higher database configured for Snapshot Isolation for this connection property to work. See Using the Snapshot Isolation Level (Microsoft SQL Server 2005 and Higher) for details.
Snapshot Isolation provides transaction-level read consistency and an optimistic approach to data modifications by not acquiring locks on data until data is to be modified. This Microsoft SQL Server 2005 and higher feature can be useful if you want to consistently return the same result set even if another transaction has changed the data and 1) your application executes many read operations or 2) your application has long running transactions that could potentially block users from reading data. This feature has the potential to eliminate data contention between read operations and update operations. When this connection property is set to true (thereby, you are using Snapshot Isolation), performance is improved due to increased concurrency.
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.
Table 6-2 lists the data types supported by the SQL Server driver and and how they are mapped to the JDBC data types.
Table 6-2 Microsoft SQL Server Data Types
| Microsoft SQL Server Data Type | JDBC Data Type |
|---|---|
|
bigintFoot 1 |
BIGINT |
|
bigint identity Foot 2 |
BIGINT |
|
binary |
BINARY |
|
bit |
BIT |
|
char |
CHAR |
|
date |
DATE |
|
datetime |
TIMESTAMP |
|
datetime2 |
TIMESTAMP |
|
datetimeoffset |
VARCHAR |
|
decimal |
DECIMAL |
|
decimal() identity |
DECIMAL |
|
float |
FLOAT |
|
image |
LONGVARBINARY |
|
int |
INTEGER |
|
int identity |
INTEGER |
|
money |
DECIMAL |
|
nchar |
CHAR NOTE: If |
|
ntext |
LONGVARCHAR NOTE: If |
|
numeric |
NUMERIC |
|
numeric() identity |
NUMERIC |
|
nvarchar |
VARCHAR NOTE: If |
|
nvarchar(max)Foot 3 |
LONGVARCHAR NOTE: If |
|
real |
REAL |
|
smalldatetime |
TIMESTAMP |
|
smallint |
SMALLINT |
|
smallint identity |
SMALLINT |
|
smallmoney |
DECIMAL |
|
sql_variant Foot 4 |
VARCHAR |
|
sysname |
VARCHAR |
|
text |
LONGVARCHAR |
|
time |
TIMESTAMP |
|
timestamp |
BINARY |
|
tinyint |
TINYINT |
|
tinyint identity |
TINYINT |
|
uniqueidentifier |
CHAR |
|
varbinary |
VARBINARY |
|
varbinary(max) Foot 5 |
LONGVARBINARY |
|
varchar |
VARCHAR |
|
varchar(max) Foot 6 |
LONGVARCHAR |
|
xml Foot 7 |
LONGVARCHAR NOTE: If |
Footnote 1 Supported only for Microsoft SQL Server 2000 and higher.
Footnote 2 Supported only for Microsoft SQL Server 2000 and higher.
Footnote 3 Supported only for Microsoft SQL Server 2005
Footnote 4 Supported only for Microsoft SQL Server 2000 and higher.
Footnote 5 Supported only for Microsoft SQL Server 2005.
Footnote 6 Supported only for Microsoft SQL Server 2005
Footnote 7 Supported only for Microsoft SQL Server 2005
See Appendix B, "GetTypeInfo" for more information about data types.
For Microsoft SQL Server 2005 and higher, the SQL Server driver supports the xml data type. Which JDBC data type the xml data type is mapped to depends on whether the JDBCBehavior and XMLDescribeType properties are set:
If XMLDescribeType=longvarchar or XMLDescribeType=longvarbinary, the driver maps the XML data type to the JDBC LONGVARCHAR or LONGVARBINARY data type, respectively, regardless of the setting of the JDBCBehavior property.
If JDBCBehavior=1 (the default) and the XMLDescribeType property is not set, the driver maps XML data to the JDBC LONGVARCHAR data type.
If JDBCBehavior=0 and the XMLDescribeType property is not set, XML data is mapped to SQLXML or LONGVARCHAR, depending on which JVM your application is using. The driver maps the XML data type to the JDBC SQLXML data type if your application is using Java SE 6. If your application is using another JVM, the driver maps the XML data type to the JDBC LONGVARCHAR data type.
You can specify whether XML data is returned as character or binary data by setting the XMLDescribeType property. For example, consider a database table defined as:
CREATE TABLE xmlTable (id int, xmlCol xml NOT NULL)
and the following code:
String sql="SELECT xmlCol FROM xmlTable"; ResultSet rs=stmt.executeQuery(sql);
If your application uses the following connection URL, which specifies that the XML data type be mapped to the LONGVARBINARY data type, the driver would return XML data as binary data:
jdbc:weblogic:sqlserver://server1:1433;DatabaseName=jdbc;User=test; Password=secret;XMLDescribeType=longvarbinary
When XMLDescribeType=longvarchar, the driver returns XML data as character data. The result set column is described with a column type of LONGVARCHAR and the column type name is xml.
When XMLDescribeType=longvarchar, your application can use the following methods to return data stored in XML columns as character data:
ResultSet.getString() ResultSet.getCharacterStream() ResultSet.getClob() CallableStatement.getString() CallableStatement.getClob()
The driver converts the XML data returned from the database server from the UTF-8 encoding used by the database server to the UTF-16 Java String encoding.
Your application can use the following method to return data stored in XML columns as ASCII data:
ResultSet.getAsciiStream()
The driver converts the XML data returned from the database server from the UTF-8 encoding to the ISO-8859-1 (latin1) encoding.
Note:
This conversion caused by using the getAsciiStream() method may create XML that is not well-formed because the content encoding is not the default encoding and does not contain an XML declaration specifying the content encoding. Do not use the getAsciiStream() method if your application requires well-formed XML.If XMLDescribeType=longvarbinary, your application should not use any of the methods for returning character data described in this section. In this case, the driver applies the standard JDBC character-to-binary conversion to the data, which returns the hexadecimal representation of the character data.
When XMLDescribeType=longvarbinary, the driver returns XML data as binary data. The result set column is described with a column type of LONGVARBINARY and the column type name is xml.
Your application can use the following methods to return XML data as binary data:
ResultSet.getBytes() ResultSet.getBinaryStream() ResultSet.getBlob() ResultSet.getObject() CallableStatement.getBytes() CallableStatement.getBlob() CallableStatement.getObject()
The driver does not apply any data conversions to the XML data returned from the database server. These methods return a byte array or binary stream that contains the XML data encoded as UTF-8.
If XMLDescribeType=longvarchar, your application should not use any of the methods for returning binary data described in this section. In this case, the driver applies the standard JDBC binary-to-character conversion to the data, which returns the hexadecimal representation of the binary data.
The driver can insert or update XML data as character or binary data.
Your application can use the following methods to insert or update XML data as character data:
PreparedStatement.setString() PreparedStatement.setCharacterStream() PreparedStatement.setClob() PreparedStatement.setObject() ResultSet.updateString() ResultSet.updateCharacterStream() ResultSet.updateClob() ReultSet.updateObject()
The driver converts the character representation of the data to the XML character set used by the database server and sends the converted XML data to the server. The driver does not parse or remove any XML processing instructions.
Your application can update XML data as ASCII data using the following methods:
PreparedStatement.setAsciiStream() ResultSet.updateAsciiStream()
The driver interprets the data returned by these methods using the ISO-8859-1 (latin 1) encoding. The driver converts the data from ISO-8859-1 to the XML character set used by the database server and sends the converted XML data to the server.
Your application can use the following methods to insert or update XML data as binary data:
PreparedStatement.setBytes() PreparedStatement.setBinaryStream() PreparedStatement.setBlob() PreparedStatement.setObject() ResultSet.updateBytes() ResultSet.updateBinaryStream() ResultSet.updateBlob() ReultSet.updateObject()
The driver does not apply any data conversions when sending XML data to the database server.
Authentication protects the identity of the user so that user credentials cannot be intercepted by malicious hackers when transmitted over the network. See Authentication for an overview.
The SQL Server driver supports the following methods of authentication:
SQL Server authentication, or user ID/password authentication, authenticates the user to the database using a database user name and password provided by the application.
Kerberos authentication uses Kerberos, a trusted third-party authentication service, to verify user identities. Kerberos authentication can take advantage of the user name and password maintained by the operating system to authenticate users to the database or use another set of user credentials specified by the application.
This method requires knowledge of how to configure your Kerberos environment and supports Windows Active Directory Kerberos only.
NTLM authentication is a single sign-on Windows authentication method. This method provides authentication from Windows clients only and requires minimal configuration.
Except for NTLM authentication, which provides authentication for Windows clients only, these authentication methods provide authentication when the driver is running on any supported platform.
The AuthenticationMethod connection property controls which authentication mechanism the driver uses when establishing connections. See Using the AuthenticationMethod Property for information about setting the value for this property.
The AuthenticationMethod connection property controls which authentication mechanism the driver uses when establishing connections. When AuthenticationMethod=auto, the driver uses SQL Server authentication, Kerberos authentication, or NTLM authentication when establishing a connection based on the following criteria:
If a user ID and password is specified, the driver uses SQL Server authentication when establishing a connection. The User property provides the user ID. The Password property provides the password.
If a user ID and password is not specified and the driver is not running on a Windows platform, the driver uses Kerberos authentication when establishing a connection.
If a user ID and password is not specified and the driver is running on a Windows platform, the driver uses NTLM authentication when establishing a connection if the driver can load the DLL required for NTLM authentication. If the driver cannot load the DLL, the driver uses Kerberos authentication.
When AuthenticationMethod=kerberos, the driver uses Kerberos authentication when establishing a connection. The driver ignores any values specified by the User property and Password properties.
When AuthenticationMethod=ntlm, the driver uses NTLM authentication when establishing a connection if the driver can load the DLL required for NTLM authentication. If the driver cannot load the DLL, the driver throws an exception. The driver ignores any values specified by the User and Password properties.
When AuthenticationMethod=userIdPassword (the default), the driver uses SQL Server authentication when establishing a connection. The User property provides the user ID. The Password property provides the password. If a user ID is not specified, the driver throws an exception.
Set the AuthenticationMethod property to auto or userIdPassword (the default). See Using the AuthenticationMethod Property for more information about setting a value for this property.
Set the User property to provide the user ID.
Set the Password property to provide the password.
This section provides requirements and instructions for configuring Kerberos authentication for the Microsoft SQL Server driver.
Verify that your environment meets the requirements listed in Table 6-3 before you configure the driver for Kerberos authentication.
Table 6-3 Kerberos Authentication Requirements for the SQL Server Driver
| Component | Requirements |
|---|---|
|
Microsoft SQL Server database server |
The database server must be administered by the same domain controller that administers the client and must be running one of the following databases:
|
|
Kerberos server |
The Kerberos server is the machine where the user IDs for authentication are administered. The Kerberos server is also the location of the Kerberos KDC. Network authentication must be provided by Windows Active Directory on one of the following operating systems:
|
|
Client |
The client must be administered by the same domain controller that administers the database server. In addition, J2SE 1.4.2 or higher must be installed. |
During installation of the WebLogic Server JDBC drivers, the following files required for Kerberos authentication are installed in the WL_HOME/server/lib folder, where WL_HOME is the directory in which you installed WebLogic Server:
krb5.conf is a Kerberos configuration file containing values for the Kerberos realm and the KDC name for that realm. WebLogic Server installs a generic file that you must modify for your environment.
JDBCDriverLogin.conf file is a configuration file that specifies which Java Authentication and Authorization Service (JAAS) login module to use for Kerberos authentication. This file is configured to load automatically unless the java.security.auth.login.config system property is set to load another configuration file. You can modify this file, but the driver must be able to find the JDBC_DRIVER_01 entry in this file or another specified login configuration file to configure the JAAS login module. Refer to your JDK documentation for information about setting configuration options in this file
To configure the driver:
Set the driver's AuthenticationMethod property to auto (the default) or kerberos. See Using the AuthenticationMethod Property for more information about setting a value for this property.
Modify the krb5.conf file to contain your Kerberos realm name and the KDC name for that Kerberos realm. Modify the krb5.conf file by editing the file with a text editor or by specifying the system properties, java.security.krb5.realm and java.security.krb5.kdc.
Note:
In Windows Active Directory, the Kerberos realm name is the Windows domain name and the KDC name is the Windows domain controller name.For example, if your Kerberos realm name is XYZ.COM and your KDC name is kdc1, your krb5.conf file would look like this:
[libdefaults]
default_realm = XYZ.COM
[realms]
XYZ.COM = {
kdc = kdc1
}
If the krb5.conf file does not contain a valid Kerberos realm and KDC name, the following exception is thrown:
Message:[OWLS][SQLServer JDBC Driver]Could not establish a connection using integrated security: No valid credentials provided
The krb5.conf file installed with the WebLogic JDBC drivers is configured to load automatically unless the java.security.krb5.conf system property is set to point to another Kerberos configuration file.
If using Kerberos authentication with a Security Manager on a Java 2 Platform, you must grant security permissions to the application and driver. See Permissions for Kerberos Authentication for an example.
See the following URL for more information about configuring and testing your environment for Windows authentication with the SQL Server driver:
By default, the SQL Server driver takes advantage of the user name and password maintained by the operating system to authenticate users to the database. By allowing the database to share the user name and password used for the operating system, users with a valid operating system account can log into the database without supplying a user name and password.
There may be times when you want the driver to use a set of user credentials other than the operating system user name and password. For example, many application servers or Web servers act on behalf of the client user logged on the machine on which the application is running, rather than the server user.
If you want the driver to use a set of user credentials other than the operating system user name and password, include code in your application to obtain and pass a javax.security.auth.Subject used for authentication as shown in the following example.
import javax.security.auth.Subject;
import javax.security.auth.login.LoginContext;
import java.sql.*;
// The following code creates a javax.security.auth.Subject instance
// used for authentication. Refer to the Java Authentication
// and Authorization Service documentation for details on using a
// LoginContext to obtain a Subject.
LoginContext lc = null;
Subject subject = null;
try {
lc = new LoginContext("JaasSample", new TextCallbackHandler());
lc.login();
subject = lc.getSubject();
}
catch (Exception le) {
... // display login error
}
// This application passes the javax.security.auth.Subject
// to the driver by executing the driver code as the subject
Connection con =
(Connection) Subject.doAs(subject, new PrivilegedExceptionAction() {
public Object run() {
Connection con = null;
try {
Class.forName("com.ddtek.jdbc.sqlserver.SQLServerDriver");
String url = "jdbc:weblogic:sqlserver://myServer:1433";
con = DriverManager.getConnection(url);
}
catch (Exception except) {
... //log the connection error
return null;
}
return con;
}
});
// This application now has a connection that was authenticated with
// the subject. The application can now use the connection.
Statement stmt = con.createStatement();
String sql = "SELECT * FROM employee";
ResultSet rs = stmt.executeQuery(sql);
... // do something with the results
To use Kerberos authentication, the application user first must obtain a Kerberos Ticket Granting Ticket (TGT) from the Kerberos server. The Kerberos server verifies the identity of the user and controls access to services using the credentials contained in the TGT.
If the application uses Kerberos authentication from a Windows client, the application user is not required to log onto the Kerberos server and explicitly obtain a TGT. Windows Active Directory automatically obtains a TGT for the user.
If an application uses Kerberos authentication from a UNIX or Linux client, the user must log onto the Kerberos server using the kinit command to obtain a TGT. For example, the following command requests a TGT from the server with a lifetime of 10 hours, which is renewable for 5 days:
kinit -l 10h -r 5d user
where user is the application user.
Refer to your Kerberos documentation for more information about using the kinit command and obtaining TGTs for users.
This section provides requirements and instructions for configuring NTLM authentication for the Microsoft SQL Server driver.
Verify that your environment meets the requirements listed in Table 6-4 before you configure your environment for NTLM authentication.
Table 6-4 NTLM Authentication Requirements for the SQL Server Driver
| Component | Requirements |
|---|---|
|
Database server |
The database server must be administered by the same domain controller that administers the client and must be running on one of the following databases:
|
|
Domain controller |
The domain controller must administer both the database server and the client. Network authentication must be provided by NTLM on one of the following operating systems:
|
|
Client |
The client must be administered by the same domain controller that administers the database server and must be running on one of the following operating systems:
In addition, J2SE 1.4 or higher must be installed. |
Oracle Type 4 JDBC drivers provide the following NTLM authentication DLLs:
DDJDBCAuthxx.dll (32-bit)
DDJDBC64Authxx.dll (Itanium 64-bit)
DDJDBCx64Authxx.dll (AMD64 and Intel EM64T 64-bit)
where xx is a two-digit number.
The DLLs are located in the WL_HOME/server/lib directory (where WL_HOME is the directory in which you installed WebLogic Server). If the application using NTLM authentication is running in a 32-bit JVM, the driver automatically uses DDJDBCAuthxx.dll. Similarly, if the application is running in a 64-bit JVM, the driver uses DDJDBC64Authxx.dll or DDJDBCx64Authxx.dll.
To configure the driver:
Set the AuthenticationMethod property to auto (the default) or ntlm. See Using the AuthenticationMethod Property for more information about setting a value for this property.
By default, the driver looks for the NTLM authentication DLLs in a directory on the Windows system path defined by the PATH environment variable. If you install the driver in a directory that is not on the Windows system path, perform one of the following actions to ensure the driver can load the DLLs:
Add the WL_HOME/server/lib directory to the Windows system path, where WL_HOME is the directory in which you installed WebLogic Server.
Copy the NTLM authentication DLLs from WL_HOME/server/lib to a directory that is on the Windows system path, where WL_HOME is the directory in which you installed WebLogic Server.
Set the LoadLibraryPath property to specify the location of the NTLM authentication DLLs. For example, if you install the driver in a directory named "DataDirect" that is not on the Windows system path, you can use the LoadLibraryPath property to specify the directory containing the NTLM authentication DLLs:
jdbc:weblogic:sqlserver://server3:1521; DatabaseName=test;LoadLibraryPath=C:\DataDirect\lib;User=test;Password=secret
If using NTLM authentication with a Security Manager on a Java 2 Platform, security permissions must be granted to allow the driver to establish connections. See Permissions for Establishing Connections for an example.
The SQL Server driver supports SSL for data encryption. SSL secures the integrity of your data by encrypting information and providing authentication. See Data Encryption Across the Network for an overview.
Depending on your Microsoft SQL Server configuration, you can choose to encrypt all data, including the login request, or encrypt the login request only. Encrypting login requests, but not data, is useful for the following scenarios:
When your application needs security, but cannot afford to pay the performance penalty for encrypting data transferred between the driver and server.
Microsoft SQL Server 2005 only. When the server is not configured for SSL, but your application still requires a minimum degree of security.
Note:
When SSL is enabled, the driver communicates with database protocol packets set by the server's default packet size. Any value set by the PacketSize property is ignored.If your Microsoft SQL Server database server has been configured with an SSL certificate signed by a trusted CA, the server can be configured so that SSL encryption is either optional or required. When required, connections from clients that do support SSL encryption fail.
Although a signed trusted SSL certificate is recommended for the best degree of security, Microsoft SQL Server 2005 can provide limited security protection even if an SSL certificate has not been configured on the server. If a trusted certificate is not installed, the server will use a self-signed certificate to encrypt the login request, but not the data.
Table 6-5 shows how the different EncryptionMethod property values behave with different Microsoft SQL Server configurations.
Table 6-5 EncryptionMethod Property and Microsoft SQL Server Configurations
| Value | No SSL Certificate | SSL Certificate (SSL Optional) | SSL Certificate (SSL Required) |
|---|---|---|---|
|
noEncryption |
Login request and data are not encrypted. |
Login request and data are not encrypted. |
Connection attempt fails. |
|
SSL |
Connection attempt fails. |
Login request and data are encrypted. |
Login request and data are encrypted. |
|
requestSSL |
Login request and data are not encrypted |
Login request and data are encrypted |
Login request and data are encrypted. |
|
loginSSL |
Microsoft SQL Server 2005: Login request is encrypted, but data is not encrypted Microsoft SQL Server 2000: Connection attempt fails. |
Login request is encrypted, but data is not encrypted. |
Login request and data are encrypted. |
Choose the type of encryption for your application:
If you want the driver to encrypt all data, including the login request, set the EncryptionMethod property to SSL or requestSSL.
If you want the driver to encrypt only the login request, set the EncryptionMethod property to loginSSL.
Specify the location and password of the truststore file used for SSL server authentication. Either set the TrustStore and TrustStore properties or their corresponding Java system properties (javax.net.ssl.trustStore and javax.net.ssl.trustStorePassword, respectively).
To validate certificates sent by the database server, set the ValidateServerCertificate property to true.
Optionally, set the HostNameInCertificate property to a host name to be used to validate the certificate. The HostNameInCertificate property provides additional security against man-in-the-middle (MITM) attacks by ensuring that the server the driver is connecting to is the server that was requested.
The SQL Server driver supports the Microsoft SQL Server 2005 and higher Output clause for Insert, Update, and Delete statements. For example, suppose you created a table with the following statement:
CREATE TABLE table1(id int, name varchar(30))
The following Update statement updates the values in the id column of table1 and returns a result set that includes the old ID (replaced by the new ID), the new ID, and the name associated with these IDs:
UPDATE table1 SET id=id*10 OUTPUT deleted.id as oldId, inserted.id as newId, inserted.name
The driver returns the results of Insert, Update, or Delete statements and the update count in separate result sets. The output result set is returned first, followed by the update count for the Insert, Update, or Delete statement. To execute DML with Results statements in an application, use the Statement.execute() or PreparedStatement.execute() method. Then, use Statement.getMoreResults () to obtain the output result set and the update count. For example:
String sql = "UPDATE table1 SET id=id*10 OUTPUT deleted.id as oldId,
inserted.id as newId, inserted.name";
boolean isResultSet = stmt.execute(sql);
int updateCount = 0;
while (true) {
if (isResultSet) {
resultSet = stmt.getResultSet();
while (resultSet.next()) {
System.out.println("oldId: " + resultSet.getInt(1) +
"newId: " + resultSet.getInt(2) +
"name: " + resultSet.getString(3));
}
resultSet.close();
}
else {
updateCount = stmt.getUpdateCount();
if (updateCount == -1) {
break;
}
System.out.println("Update Count: " + updateCount);
}
isResultSet = stmt.getMoreResults();
}
The SQL Server driver supports reauthentication for Microsoft SQL Server 2005 and higher. The user performing the switch must have been granted the database permission IMPERSONATE.NOTE: Before performing reauthentication, applications must ensure that any statements or result sets created as one user are closed before switching the connection to another user. Your application can use the setCurrentUser() method in the ExtConnection interface to switch a user on a connection. The setCurrentUser() method accepts driver-specific reauthentication options. The reauthentication options supported for the SQL Server driver are:
CURRENT_DATABASE: Specifies the name of the current database. The value must be a valid Microsoft SQL Server database name.If the setCurrentUser() method is called and this option is specified as an empty string or is not specified, only the user is switched; the database is not switched.
REVERT_USER: {true | false}. Determines whether the driver reverts the current user to the initial user before setting the user to a new user for connections that have already reauthenticated. If set to true and the setCurrentUser() method is called, the driver reverts the current user to the initial user before setting the connection to the new user. For example, consider a connection that was initially created by User A and was later switched to User B. Before the connection could be further switched to User C, the driver reverts the connection back to User A and then sets it to User C. If set to false and the setCurrentUser() method is called, the driver does not revert the current user to the initial user before performing the switch. For example, if the connection was initially created by User A, switched to User B, and then switched to User C, the driver does not revert the user to User A before switching to User C.
The SQL Server driver allows applications to store and return the following types of client information associated with a particular connection:
Name of the application
User ID
Host name of the client
Additional accounting information, such as an accounting ID
Product name and version of the SQL Server driver
This information can be used for database administration and monitoring purposes.
See Appendix C, "SQL Escape Sequences for JDBC" for information about the SQL escape sequences supported by the SQL Server driver.
The SQL Server driver supports the following isolation levels for Microsoft SQL Server:
Read Committed with Locks (supported for Microsoft SQL Server 2005 only) or Read Committed
Read Committed with Snapshots (supported for Microsoft SQL Server 2005 only)
Read Uncommitted
Repeatable Read
Serializable
Snapshot (supported for Microsoft SQL Server 2005 only)
The default is Read Committed with Locks (Microsoft SQL Server 2005) or Read Committed.
You can use the Snapshot isolation level in either of the following ways:
Setting the SnapshotSerializable property changes the behavior of the Serializable isolation level to use the Snapshot isolation level. This allows an application to use the Snapshot isolation level with no or minimum code changes. See the description of this property in Table 6-1 for more information.
Importing the ExtConstants class allows you to specify the TRANSACTION_SNAPSHOT or TRANSACTION_SERIALIZABLE isolation levels for an individual statement in the same application. The ExtConstants class in the com.ddtek.jdbc.extensions package defines the TRANSACTION_SNAPSHOT constant. For example, the following code imports the ExtConstants class and sets the TRANSACTION_SNAPSHOT isolation level:
import com.ddtek.jdbc.extensions.ExtConstants; Connection.setTransactionIsolation(ExtConstants.TRANSACTION_SNAPSHOT);
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.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, the following changes you make to the result set are visible:
Own Inserts are visible. Others Inserts are not visible.
Own and Others Updates are visible.
Own and Others Deletes are visible.
Using the default behavior of the driver (UseServerSideUpdatableCursors=false), those changes would not be visible.
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.
To install stored procedures for JTA:
Copy the appropriate 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:\Oracle\Middleware\wlserver_10.x.
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 thesqljdbc.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 ISQL utility to run the instjdbc.sql script. As a precaution, have your system administrator 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.
Connections associated with distributed transactions can become orphaned if the connection to the server is lost before the transaction has completed. When connections associated with distributed transactions are orphaned, any locks held by the database for that transaction are maintained, which can cause data to become unavailable. By cleaning up distributed transactions, connections associated with those transactions are freed and any locks held by the database are released.
You can use the XAResource.recover method to clean up distributed transactions that have been prepared, but not committed or rolled back. Calling this method returns a list of active distributed transactions that have been prepared, but not committed or rolled back. An application can use the list returned by the XAResource.recover method to clean up those transactions by explicitly committing them or rolling them back. The list of transactions returned by the XAResource.recover method does not include transactions that are active and have not been prepared.
In addition, the SQL Server driver supports the following methods of distributed transaction cleanup:
Transaction timeout sets a timeout value that is used to audit active transactions. Any active transactions that have a life span greater than the specified timeout value are rolled back. Setting a transaction timeout allows distributed transactions to be cleaned up automatically based on the timeout value.
Explicit transaction cleanup allows you to explicitly roll back any transactions left in an unprepared state based on a transaction group identifier. Explicit transaction cleanup provides more control than transaction timeout over when distributed transactions are cleaned up.
To set a timeout value for transaction cleanup, you use the XAResource.setTransactionTimeout method. Setting this value causes sqljdbc.dll on the server side to maintain a list of active transactions. Distributed transactions are placed in the list of active transactions when they are started and removed from this list when they are prepared, rolled back, committed, or forgotten using the appropriate XAResource methods.
When a timeout value is set for transaction cleanup using the XAResource.setTransactionTimeout method, sqljdbc.dll periodically audits the list of active transactions for expired transactions. Any active transactions that have a life span greater than the timeout value are rolled back. If an exception is generated when rolling back a transaction, the exception is written to the sqljdbc.log file, which is located in the same directory as the sqljdbc.dll file.
Setting the transaction timeout value too low means running the risk of rolling back a transaction that otherwise would have completed successfully. As a general guideline, set the timeout value to allow sufficient time for a transaction to complete under heavy traffic load.
Setting a value of 0 (the default) disables transaction timeout cleanup.
The SQL Server driver allows you to associate an identifier with a group of transactions using the XATransactionGroup connection property. When you specify a transaction group ID, all distributed transactions initiated by the connection are identified by this ID.
Setting this value causes sqljdbc.dll on the server side to maintain a list of active transactions. Distributed transactions are placed in the list of active transactions when they are started and removed from this list when they are prepared, rolled back, committed, or forgotten using the appropriate XAResource methods.
You can use the XAResource.recover method to roll back any transactions left in an unprepared state that match the transaction group ID on the connection used to call XAResource.recover. For example, if you specified XATransactionGroup=ACCT200 and called the XAResource.recover method on the same connection, any transactions left in an unprepared state with a transaction group ID of ACCT200 would be rolled back.
If an exception is generated when rolling back a transaction, the exception is written to the sqljdbc.log file, which is located in the same directory as the sqljdbc.dll file.
When using explicit transaction cleanup, distributed transactions associated with orphaned connections, and the locks held by those connections, will persist until the application explicitly invokes them. As a general rule, applications should clean up orphaned connections at startup and when the application is notified that a connection to the server was lost.
Although Microsoft SQL Server does not define a Blob or Clob data type, the SQL Server driver allows you to return 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 returning 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.
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.
The SQL Server driver supports returning parameter metadata as described in this section.
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 <>.
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 = ?"
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.
The SQL Server driver supports any JSR 114 implementation of the RowSet interface, including:
CachedRowSets
FilteredRowSets
WebRowSets
JoinRowSets
JDBCRowSets
J2SE 1.4 or higher is required to use rowsets with the driver.
See http://www.jcp.org/en/jsr/detail?id=114 for more information about JSR 114.
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.
An application can return values of auto-generated keys when it executes an Insert statement. 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 instruct the driver to return values of auto-generated keys:
Statement.execute(String sql, int autoGeneratedKeys)
Statement.execute(String sql, int[] columnIndexes)
Statement.execute(String sql, String[] columnNames)
Statement.executeUpdate(String sql, int autoGeneratedKeys)
Statement.executeUpdate(String sql, int[] columnIndexes)
Statement.executeUpdate(String sql, String[] columnNames)
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 values of auto-generated keys:
Connection.prepareStatement(String sql, int autoGeneratedKeys)
Connection.prepareStatement(String sql, int[] columnIndexes)
Connection.prepareStatement(String sql, String[] columnNames)
An application can retrieve values of auto-generated keys using the Statement.getGeneratedKeys() method. This method returns a ResultSet object with a column for each auto-generated key.
When the Microsoft SQL Server driver establishes a connection, the driver sets the Microsoft SQL Server database option ansi_nulls to on. This action ensures that the driver is compliant with the ANSI SQL standard, which makes developing cross-database applications easier.
By default, Microsoft SQL Server does not evaluate null values in SQL equality (=) or inequality (<>) comparisons or aggregate functions in an ANSI SQL-compliant manner. For example, the ANSI SQL specification defines that col1=null as shown in the following Select statement always evaluates to false:
SELECT * FROM table WHERE col1 = NULL
Using the default database setting (ansi_nulls=off), the same comparison evaluates to true instead of false.
Setting ansi_nulls to on changes how the database handles null values and forces the use of IS NULL instead of =NULL. For example, if the value of col1 in the following Select statement is null, the comparison evaluates to true:
SELECT * FROM table WHERE col1 IS NULL
In your application, you can restore the default Microsoft SQL Server behavior for a connection in the following ways:
Use the InitializationString property to specify the SQL command set ANSI_NULLS off. For example, the following URL ensures that the handling of null values is restored to the Microsoft SQL Server default for the current connection:
jdbc:weblogic:sqlserver://server1:1433; InitializationString=set ANSI_NULLS off; DatabaseName=test
Explicitly execute the following statement after the connection is established:
SET ANSI_NULLS OFF
Use the following steps to configure failover:
Specify the primary and alternate servers:
Specify your primary server using a connection URL or data source.
Specify one or multiple alternate servers by setting the AlternateServers property.
NOTE: If using failover with Microsoft Cluster Server (MSCS), which determines the alternate server for failover instead of the driver, any alternate server specified must be the same as the primary server. For example:
jdbc:datadirect:sqlserver://server1:1433; DatabaseName=TEST;User=test;Password=secret; AlternateServers=(server1:1433;DatabaseName=TEST)
Choose a failover method by setting the FailoverMode connection property. The default method is connection failover (FailoverMode=connect).
If FailoverMode=extended or FailoverMode=select, set the FailoverGranularity property to specify how you want the driver to behave if exceptions occur while trying to reestablish a lost connection. The default behavior of the driver is to continue with the failover process and post any exceptions on the statement on which they occur (FailoverGranularity=nonAtomic).
Optionally, configure the connection retry feature.
Optionally, set the FailoverPreconnect property if you want the driver to establish a connection with the primary and an alternate server at the same time. The default behavior is to connect to an alternate server only when failover is caused by an unsuccessful connection attempt or a lost connection (FailoverPreconnect=false).
Connection information for primary and alternate servers can be specified using either one of the following methods:
Connection URL through the JDBC Driver Manager
JDBC data source
For example, the following connection URL for the SQL Server driver specifies connection information for the primary and alternate servers using a connection URL:
jdbc:weblogic:sqlserver://server1:1433;DatabaseName=TEST;User=test; Password=secret;AlternateServers=(server2:1433;DatabaseName=TEST2, server3:1433;DatabaseName=TEST3)
In this example:
...server1:1433;DatabaseName=TEST...
is the part of the connection URL that specifies connection information for the primary server. Alternate servers are specified using the AlternateServers property. For example:
...;AlternateServers=(server2:1433;DatabaseName=TEST2, server3:1433;DatabaseName=TEST3)
Similarly, the same connection information for the primary and alternate servers specified using a JDBC data source would look like this:
SQLServerDataSource mds = new SQLServerDataSource();
mds.setDescription("My SQLServerDataSource");
mds.setServerName("server1");
mds.setPortNumber(1433);
mds.setDatabaseName("TEST");
mds.setUser("test");
mds.setPassword("secret");
mds.setAlternateServers(server2:1433;DatabaseName=TEST2,
server3:1433;DatabaseName=TEST3)
In this example, connection information for the primary server is specified using the ServerName, PortNumber, and DatabaseName properties. Connection information for alternate servers is specified using the AlternateServers property.
The SQL Server driver also allows you to specify connections to named instances, multiple instances of a Microsoft SQL Server database running concurrently on the same server. If specifying named instances for the primary and alternate servers, the connection URL would look like this:
jdbc:weblogic:sqlserver://server1\\instance1;User=test;Password=secret; AlternateServers=(server2\\instance2:1433;DatabaseName=TEST2, server3\\instance3:1433;DatabaseName=TEST3)
Similarly, the same connection information to named instances for the primary and alternate servers specified using a JDBC data source would look like this:
SQLServerDataSource mds = new SQLServerDataSource();
mds.setDescription("My SQLServerDataSource");
mds.setServerName("server1\\instance1");
mds.setPortNumber(1433);
mds.setDatabaseName("TEST");
mds.setUser("test");
mds.setPassword("secret");
mds.setAlternateServers(server2\\instance2:1433;
DatabaseName=TEST2,server3\\instance3:1433;
DatabaseName=TEST3)
To connect to a named instance using a data source, you specify the named instance on the primary server using the ServerName property.
The value of the AlternateServers property is a string that has the format:
(servername1[:port1][;property=value][,servername2[:port2] [;property=value]]...)
or, if connecting to named instances:
(servername1\\instance1[;property=value][,servername2\\instance2 [;property=value
where:]]
servername1 is the IP address or server name of the first alternate database server, servername2 is the IP address or server name of the second alternate database server, and so on. The IP address or server name is required for each alternate server entry.
instance1 is the named instance on the first alternate database server, servername2 is the named instance on the second alternate database server, and so on. If connecting to named instances, the named instance is required for each alternate server entry.
port1 is the port number on which the first alternate database server is listening, port2 is the port number on which the second alternate database server is listening, and so on. The port number is optional for each alternate server entry. If unspecified, the port number specified for the primary server is used. If a port number is unspecified for the primary server, a default port number of 1433 is used.
property=value is the DatabaseName connection property. This property is optional for each alternate server entry. For example:
Password=secret;AlternateServers=(server2:1433;DatabaseName=TEST2, server3:1433;DatabaseName=TEST3)
or, if connecting to named instances:
jdbc:weblogic:sqlserver://server1\\instance1:1433;DatabaseName=TEST; User=test;Password=secret;AlternateServers=(server2\\instance2:1433; DatabaseName=TEST2,server3\\instance3:1433;DatabaseName=TEST3)
If you do not specify the DatabaseName connection property in an alternate server entry, the connection to that alternate server uses the property specified in the URL for the primary server. For example, if you specify DatabaseName=TEST for the primary server, but do not specify a database name in the alternate server entry as shown in the following URL, the driver tries to connect to the TEST database on the alternate server:
jdbc:datadirect:sqlserver://server1:1433;DatabaseName=TEST;User=test; Password=secret;AlternateServers=(server2:1433,server3:1433)
Connection retry allows the SQL Server driver to retry connections to the primary database server, and if specified, alternate servers until a successful connection is established. You use the ConnectionRetryCount and ConnectionRetryDelay properties to enable and control how connection retry works. For example:
jdbc:datadirect:sqlserver://server1:1433;DatabaseName=TEST;
User=test;
Password=secret;
AlternateServers=(server2:1433;
DatabaseName=TEST2, server3:1433;DatabaseName=TEST3);
ConnectionRetryCount=2; ConnectionRetryDelay=5
In this example, if a successful connection is not established on the SQL Server driver's first pass through the list of database servers (primary and alternate), the driver retries the list of servers in the same sequence twice (ConnectionRetryCount=2). Because the connection retry delay has been set to five seconds (ConnectionRetryDelay=5), the driver waits five seconds between retry passes.
The following section summarizes the connection properties that control how failover works with the SQL Server driver:
AlternateServers: One or multiple alternate database servers. An IP address or server name identifying each server is required. Port number and the connection property DatabaseName are optional. If the port number is unspecified, the port number specified for the primary server is used. If a port number is unspecified for the primary server, the default port number of 1433 is used.
ConnectionRetryCount: Number of times the driver retries the primary database server, and if specified, alternate servers until a successful connection is established. The default is 5.
ConnectionRetryDelay: Wait interval, in seconds, between connection retry attempts when the ConnectionRetryCount property is set to a positive integer. The default is 1.
DatabaseName: Name of the database to which you want to connect.
FailoverGranularity: Determines whether the driver fails the entire failover process or continues with the process if exceptions occur while trying to reestablish a lost connection. The default is nonAtomic (the driver continues with the failover process and posts any exceptions on the statement on which they occur).
FailoverMode: The failover method you want the driver to use. The default is connect (connection failover is used).
FailoverPreconnect: Specifies whether the driver tries to connect to the primary and an alternate server at the same time. The default is false (the driver tries to connect to an alternate server only when failover is caused by an unsuccessful connection attempt or a lost connection).
LoadBalancing: Sets whether the driver will use client load balancing in its attempts to connect to the database servers (primary and alternate). If client load balancing is enabled, the driver uses a random pattern instead of a sequential pattern in its attempts to connect. The default is false (client load balancing is disabled).
PortNumber: Port listening for connections on the primary database server. This property is supported only for data source connections. The default port number is 1433.
ServerName: IP address or server name for the primary database server. This property is supported only for data source connections.
The driver supports Bulk Load, a feature that allows your application to send large numbers of rows of data to the database in a continuous stream instead of in numerous smaller database protocol packets. Similar to batch operations, performance improves because far fewer network round trips are required. Bulk load bypasses the data parsing usually done by the database, providing an additional performance gain over batch operations.
|
 Copyright © 2007, 2010, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|