Oracle Type 4 Driver Class
JDBC Type -4 driver example. The oracle.jdbc.driver.OracleDriver class is JDBC driver for Oracle database. The Connection type is the return type of.
- Oracle Type 4 JDBC drivers are Driver class files are included For the list of the connection properties specific to each Oracle Type 4 JDBC driver.
- The Oracle Type 4 JDBC MS SQL Server driver Driver Class. The driver classes for the Oracle Type 4 JDBC MS SQL the MS SQL Server driver supports the.
9/13
The following sections describe how to configure and use the Oracle Type 4 JDBC SQL Server driver:
Driver Class
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
Microsoft SQL Server URL
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.
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://server33;User test;Password secret
See Connecting to Named Instances for instructions on connecting to named instances.
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
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
SQL Server Connection Properties
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.
Performance Considerations
Setting the following connection properties for the SQL Server driver as described in the following list can improve performance for your applications.
EnableBulkLoad
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.
EncryptionMethod
Data encryption may adversely affect performance because of the additional overhead mainly CPU usage required to encrypt and decrypt data.
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.
LongDataCacheSize
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.
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.
PacketSize
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.
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 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.
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.
SnapshotSerializable
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.
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 6-2 lists the data types supported by the SQL Server driver and and how they are mapped to the JDBC data types.
See Appendix B, GetTypeInfo for more information about data types.
Returning and Inserting/Updating XML Data
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.
Returning XML Data
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://server33;DatabaseName jdbc;User test;
Password secret;XMLDescribeType longvarbinary
Character Data
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.
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.
Binary 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.
Inserting/Updating XML 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
ResultSet.updateBytes
ResultSet.updateBinaryStream
ResultSet.updateBlob
The driver does not apply any data conversions when sending XML data to the database server.
Authentication
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.
Using the AuthenticationMethod 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.
Configuring SQL Server Authentication
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.
Configuring Kerberos Authentication
This section provides requirements and instructions for configuring Kerberos authentication for the Microsoft SQL Server driver.
Product Requirements
Verify that your environment meets the requirements listed in Table 6-3 before you configure the driver for Kerberos authentication.
Configuring the Driver
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.
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:
Specifying User Credentials for Kerberos Authentication
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 ;
// do something with the results
Obtaining a Kerberos Ticket Granting Ticket
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.
Configuring NTLM Authentication
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.
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.
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://server21;
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.
Data Encryption
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.
Using SSL with Microsoft SQL Server
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.
Configuring SSL Encryption
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.
DML with Results Microsoft SQL Server 2005 and Higher
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 ;
Reauthentication
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.
Client Information for Connections
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.
Isolation Levels
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.
Using the Snapshot Isolation Level Microsoft SQL Server 2005 and Higher
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 ;
Using Scrollable Cursors
The SQL Server driver supports scroll-sensitive result sets, scroll-insensitive result sets, and updatable result sets.
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, 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.
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.
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.
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
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.
Distributed Transaction Cleanup
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.
Transaction Timeout
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.
Explicit Transaction 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.
Large Object LOB Support
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.
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 .
Stored Procedures
The SQL Server driver does not support returning parameter metadata for stored procedure arguments.
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
J2SE 1.4 or higher is required to use rowsets with the driver.
See 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.
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.
Null Values
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://server33;
InitializationString set ANSI_NULLS off;
DatabaseName test
Explicitly execute the following statement after the connection is established:
SET ANSI_NULLS OFF
Configuring Failover
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://server33; DatabaseName TEST;User test;Password secret; AlternateServers server33;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.
Specifying Primary and Alternate Servers
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://server33;DatabaseName TEST;User test; Password secret;AlternateServers server33;DatabaseName TEST2, server33;DatabaseName TEST3
In this example:
server33;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 server33;DatabaseName TEST2, server33;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 server33;DatabaseName TEST2,
server33;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 instance33;DatabaseName TEST2, server3 instance33;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:
mds.setServerName server1 instance1 ;
mds.setAlternateServers server2 instance33;
DatabaseName TEST2,server3 instance33;
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 server33;DatabaseName TEST2, server33;DatabaseName TEST3
jdbc:weblogic:sqlserver://server1 instance33;DatabaseName TEST; User test;Password secret;AlternateServers server2 instance33; DatabaseName TEST2,server3 instance33;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://server33;DatabaseName TEST;User test; Password secret;AlternateServers server33,server33
Specifying Connection Retry
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://server33;DatabaseName TEST;
User test;
Password secret;
AlternateServers server33;
DatabaseName TEST2, server33;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.
Failover Properties
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.
Bulk Load
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.
JDBC 4 types of JDBC drivers Type 1 : oracle.jdbc.driver.OracleDriver - for loading driver To find the host and port, follow the path in Russ labs .
6 The MS SQL Server Driver
//type iv driver specially for oracle import java.sql. ; type 4 driver with oracle 9i. Class.java:164.
