Type 4 JDBC Drivers

      

The Sybase Driver

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

 

Database Version Support

The WebLogic Type 4 JDBC driver for Sybase (the “Sybase driver”) supports the following database versions:

XA connections are supported with the Sybase Adaptive Server Enterprise 12.0 and later versions only. XA connections are not supported on Sybase Adaptive Server 11.5 and 11.9.

 

Driver Classes

The driver class for the WebLogic Type 4 JDBC Sybase driver is:

Use these driver classes when configuring a JDBC data source in your WebLogic Server domain.

 

Sybase URL

The connection URL format for the Sybase driver is: 

jdbc:bea:sybase://hostname:port[;property=value[;...]]

where:

For example: 

jdbc:bea:sybase://server2:5000;User=test;Password=secre

 

Sybase Connection Properties

Table 7-1 lists the JDBC connection properties supported by the Sybase 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

All connection string property names are case-insensitive. For example, Password is the same as password. The data type listed for each connection property is the Java data type used for the property value in a JDBC data source.

Table 7-1 Sybase Connection Properties
Property Description
AuthenticationMethod {kerberos | userIdPassword}. Determines which authentication method the driver uses when establishing a connection. If set to kerberos, the driver uses Kerberos authentication. The driver ignores any user ID or password specified. If you set this value, you also must set the ServicePrincipalName property. If set to userIdPassword (the default), the driver uses user ID/password authentication. If a user ID and password is not specified, the driver throws an exception. The User property provides the user ID. The Password property provides the password. See Authentication for more information about using authentication with the Sybase driver. The default is userIdPassword.
BatchPerformanceWorkaround OPTIONAL {true | false}. Determines the method used to execute batch operations. If set to true, the driver uses the native Sybase batch mechanism. In most cases, using the native Sybase batch functionality provides significantly better performance, but the driver may not always be able to return update counts for the batch. If set to false (the default), the driver uses the JDBC 3.0-compliant batch mechanism. The default is false. See Batch Inserts and Updates.
CodePageOverride OPTIONAL The code page to be used by the driver to convert 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, CodePageOverride=CP950. 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.
ConnectionRetryCount OPTIONAL The number of times the driver retries connections to a database server until a successful connection is established. Valid values are 0 and any positive integer. If set to 0, the driver does not try to reconnect after the initial unsuccessful attempt. If an application sets a login timeout value (for example, using DataSource.loginTimeout), the login timeout takes precedence over this property. The ConnectionRetryDelay property specifies the wait interval, in seconds, used between attempts. The default is 5.
ConnectionRetryDelay OPTIONAL The number of seconds the driver waits before retrying connections to a database server when ConnectionRetryCount is set to a positive integer. The default is 1.
ConvertNull {1 | 0}. Controls how data conversions are handled for null values. If set to 1 (the default), 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 set to 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. The default is 1.
DatabaseName OPTIONAL The name of the database to which you want to connect. See also Database Connection Property.
EnableCancelTimeout {true | false}. 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. If set to true, the cancel request times out using the same timeout value, in seconds, that is set for the statement it cancels. For example, if your application calls Statement.setQueryTimeout(5) on a statement and that statement is cancelled because its timeout value was exceeded, a cancel request is sent that also will time out if its execution exceeds 5 seconds. If the cancel request times out, because the server is down, for example, the driver throws an exception indicating that the cancel request was timed out and the connection is no longer valid. If set to false (the default), the cancel request does not time out.
EncryptionMethod {noEncryption | SSL}. Determines whether SSL encryption is used to encrypt and decrypt data transmitted over the network between the driver and database server. If set to noEncryption (the default), data is not encrypted or decrypted. NOTE: Connection hangs can occur if the driver attempts to connect to a database server that requires SSL. You may want to set a login timeout using the LoginTimeout property to avoid problems when connecting to a server that requires SSL. If set to SSL, data is encrypted using SSL. If the database server does not support SSL, the connection fails and the driver throws an exception. When SSL is enabled, the following properties also apply: HostNameInCertificate TrustStore TrustStorePassword ValidateServerCertificate NOTE: Connection hangs can occur if the driver attempts to connect to a database server that 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. See Data Encryption for more information about configuring data encryption. See Performance Considerations for information about configuring this property for optimal performance. The default is noEncryption.
ErrorBehavior {exception | warning | raiseerrorwarning}. Determines how the driver handles errors returned from stored procedures. If set to exception (the default), the driver throws an exception when it encounters stored procedure errors, including RAISERRORs. If set to warning, the driver returns stored procedure errors, including RAISERRORs, as SQLWarnings. If set to raiseerrorwarning, the driver returns RAISERRORs as SQLWarnings and throws exceptions for other stored procedure errors. By default, previous versions of the Sybase driver converted errors returned from a stored procedure into SQLWarnings. Applications that relied on the driver converting errors to warnings can revert to that behavior by setting ErrorBehavior=warning. The default is exception.
HostNameInCertificate {host_name | #SERVERNAME#}. Specifies a host name for certificate validation when SSL encryption is enabled (EncryptionMethod=SSL) and validation is enabled (ValidateServerCertificate=true). This property is optional and 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. If a host name is specified, the driver compares the specified host name to the DNSName value of the SubjectAlternativeName in the certificate. If a DNSName value does not exist or if the certificate does not have a SubjectAlternativeName, the driver compares the host name with the Common Name (CN) part of the certificate's Subject name. If the values do not match, the connection fails and the driver throws an exception. If #SERVERNAME# is specified, the driver compares the server name specified in the connection URL or data source of the connection to the DNSName value of the SubjectAlternativeName in the certificate. If a DNSName value does not exist or if the certificate does not have a SubjectAlternativeName, the driver compares the host name to the CN parts of the certificate's Subject name. If the values do not match, the connection fails and the driver throws an exception. 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 uses the server name specified in the connection URL or data source of the connection to validate the certificate. If SSL encryption or certificate validation is not enabled, this property is ignored. See Data Encryption for information about configuring for authentication. The default is an empty 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. The following connection URL sets the handling of null values to the Sybase default: jdbc:bea:sybase://server1:5000;
InitializationString=set ANSINULL off;
DatabaseName=test 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. The following connection URL sets the handling of null values to the Sybase default and allows delimited identifiers: jdbc:bea:sybase://server1:5000;
InitializationString=(set ANSINULL off;
set QUOTED_IDENTIFIER on);DatabaseName=test 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.
InsensitiveResultSetBufferSize OPTIONAL {-1 | 0 | x}. Determines the amount of memory used by the driver to cache insensitive result set data. It must have one of the following values: If set to -1, the driver caches all insensitive result set data in memory. If the size of the result set exceeds available memory, an OutOfMemoryException is generated. Because the need to write result set data to disk is eliminated, the driver processes the data more efficiently. If set to 0, the driver caches all insensitive result set data in memory, up to a maximum of 2 GB. If the size of the result set data exceeds available memory, the driver pages the result set data to disk. Because result set data may be written to disk, the driver may have to reformat the data to write it correctly to disk. If set to x, where x is a positive integer, the driver caches all insensitive result set data in memory, using this value to set the size (in KB) of the memory buffer for caching insensitive result set data. If the size of the result set data exceeds the buffer size, the driver pages the result set data to disk. Because the result set data may be written to disk, the driver may have to reformat the data to write it correctly to disk. Specifying a buffer size that is a power of 2 results in more efficient memory use. The default is 2048 (KB). See Performance Considerations for information about configuring this property for optimal performance.
JavaDoubleToString {true | false}. Determines whether the driver uses its internal conversion algorithm or the JVM conversion algorithm when converting double or float values to string values. If set to true, the driver uses the JVM algorithm when converting double or float values to string values. If set to false (the default), the driver uses its internal algorithm when converting double or float values to string values. Setting the property to false improves performance; however, slight rounding differences can occur when compared to the same conversion using the JVM algorithm. These differences are within the allowable error of the double and float data types. The default is false.
LoginTimeout OPTIONAL The amount of time, in seconds, the driver waits for a connection to be established before returning control to the application and throwing a timeout exception. If set to 0 (the default), the driver does not time out a connection request.
LongDataCacheSize {-1 | 0 | x}. 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. If set to -1, the driver does not cache long data in result sets. It is cached on the server. Use this value only if your application retrieves columns in the order in which they are defined in the result set. If set to 0, the driver caches long data in result sets in memory. If the size of the result set data exceeds available memory, the driver pages the result set data to disk. If set to x, where x is a positive integer, the driver caches long data in result sets in memory and uses this value to set the size (in KB) of the memory buffer for caching result set data. If the size of the result set data exceeds available memory, the driver pages the result set data to disk. See Performance Considerations for information about configuring this property for optimal performance. The default is 2048.
PacketSize PacketSize={0 | x}. Determines the number of bytes for each database protocol packet transferred from the database server to the client machine (Sybase 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. If set to 0, the driver uses the default maximum packet size used by the database server. If set to x, an integer from 1 to 1024, the driver uses a packet size that is a multiple of 512 bytes. For example, PacketSize=8 means to set the packet size to 8 * 512 bytes (4096 bytes). NOTE: If SSL encryption is enabled using the EncryptionMethod property, any value set for the PacketSize property is ignored. See Performance Considerations for information about configuring this property for optimal performance. The default is 0.
Password The case-sensitive password used to connect to your Sybase database. A password is required only if security is enabled on your database. If so, contact your system administrator to get your password.
PortNumber The TCP port of the primary database server that is listening for connections to the Sybase database. The default varies depending on operating system. This property is supported only for data source connections.
PrepareMethod OPTIONAL {StoredProc | StoredProclfParam | Direct}. Determines whether stored procedures are created on the server for prepared statements. If set to StoredProc, a stored procedure is created when the statement is prepared and is executed when the prepared statement is executed. If set to StoredProcIfParam, a stored procedure is created only if the prepared statement contains one or multiple parameter markers. In this case, it is created when the statement is prepared and is executed when the prepared statement is executed. If the statement does not contain parameter markers, a stored procedure is not created and the statement is executed directly. If set to Direct, a stored procedure is not created for the prepared statement and the statement is executed directly. A stored procedure may be created if parameter metadata is requested. The default is StoredProclfParam. Setting this property to StoredProc or StoredProclfParam can improve performance if your application executes prepared statements multiple times because, once created, executing a stored procedure is faster than executing a single SQL statement. If a prepared statement is only executed once or is never executed, performance can decrease because creating a stored procedure incurs more overhead on the server than simply executing a single SQL statement. Setting this property to Direct should be used if your application does not execute prepared statements multiple times. See Performance Considerations for information about configuring this property for optimal performance.
QueryTimeout {positive integer | -1 | 0}. Sets the default query timeout (in seconds) for all statements created by a connection. If set to a positive integer, the driver uses the value as the default timeout for any statement created by the connection. To override the default timeout value set by this connection option, call the Statement.setQueryTimeout() method to set a timeout value for a particular statement. If set to -1, the query timeout functionality is disabled. The driver silently ignores calls to the Statement.setQueryTimeout() method. If set to 0 (the default), the default query timeout is infinite (the query does not time out). The default is 0.
ResultSetMetaDataOptions {0 | 1}. The Sybase driver can return table name information in the ResultSet metadata for Select statements if your application requires that information. 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.
SelectMethod OPTIONAL {Direct | Cursor}. A hint to the driver that determines whether the driver requests a database cursor for Select statements. Performance and behavior of the driver are affected by this property, which is defined as a hint because the driver may not always be able to satisfy the requested method. If set to direct (the default), the database server sends the complete result set in a single response to the driver when responding to a query. A server-side database cursor is not created. Typically, responses are not cached by the driver. Using this method, the driver must process the entire response to a query before another query is submitted. If another query is submitted (using a different statement on the same connection, for example), the driver caches the response to the first query before submitting the second query. Typically, the direct method performs better than the cursor method. If set to cursor, a server-side database cursor is requested. When returning forward-only result sets, the rows are retrieved from the server in blocks. The setFetchSize() method can be used to control the number of rows that are returned for each request. Performance tests show that, when returning forward-only result sets, the value of Statement.setFetchSize() significantly impacts performance. There is no simple rule for determining the setFetchSize() value that you should use. We recommend that you experiment with different setFetchSize() values to determine which value gives the best performance for your application. The cursor method is useful for queries that produce a large amount of data, particularly if multiple open result sets are used. See Performance Considerations for information about configuring this property for optimal performance. The default is Direct.
ServerName Specifies either the IP address or the server name (if your network supports named servers) of the primary database server. For example, 122.23.15.12 or SybaseServer. This property is supported only for data source connections.
ServicePrincipalName Specifies the case-sensitive service principal name to be used by the driver for Kerberos authentication. For Sybase, the service principal name is the name of a server configured in your Sybase interfaces file. If you set this property, you also must set the value of the AuthenticationMethod property to Kerberos. The value of this property can include the Kerberos realm name, but it is optional. If you do not specify the Kerberos realm name, the default Kerberos realm is used. For example, if the service principal name, including Kerberos realm name, is server/sybase125ase1@XYZ.COM and the default realm is XYZ.COM, valid values for this property are: server/sybase125ase1@XYZ.COM and server/sybase125ase1 When Kerberos authentication is not used, this property is ignored. See Authentication for more information about using authentication with the Sybase driver.
TrustStore 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. Override 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 ValidateServerCertificate=false.
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. Override 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 ValidateServerCertificate=false.
User The case-insensitive user name used to connect to your Sybase database. A user name is required only if security is enabled on your database. If so, contact your system administrator to get your user name.

 

Performance Considerations

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

 

BatchPerformanceWorkaround

The driver can use a JDBC 3.0-compliant batch mechanism or the native Sybase batch mechanism to execute batch operations. Performance can be improved by using the native Sybase batch environment, especially when performance-expensive network roundtrips are an issue. When using the native mechanism, be aware that if the execution of the batch results in an error, the driver cannot determine which statement in the batch caused the error. In addition, if the batch contained a statement that called a stored procedure or executed a trigger, multiple update counts for each batch statement or parameter set are generated. The JDBC 3.0-compliant mechanism returns individual update counts for each statement or parameter set in the batch as required by the JDBC 3.0 specification. To use the Sybase native batch mechanism, this property should be set to true.

 

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 returns images, pictures, long text, or binary data, you can disable caching for long data on the client if your application returns long data column values in the order they are defined in the result set. If your application returns 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.

 

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.

 

PrepareMethod

If your application executes prepared statements multiple times, this property should be set to StoredProc to improve performance because, once created, executing a stored procedure is faster than executing a single SQL Statement. If your application does not execute prepared statements multiple times, this property should be set to Direct. In this case, performance decreases if a stored procedure is created because a stored procedure incurs more overhead on the server than executing a single SQL statement.

 

ResultSetMetaDataOptions

By default, the Sybase 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 statements are true for your application, the best setting for this property is cursor, which means use server-side database cursors:

 

Data Types

Table 7-2 lists the data types supported by the Sybase driver and how they are mapped to JDBC data types.

Table 7-2 Sybase Data Types
Sybase Data Type JDBC Data Type
BIGINT1 BIGINT
BINARY BINARY
BIT BIT
CHAR CHAR
DATE2 DATE
DATETIME TIMESTAMP
DECIMAL DECIMAL
FLOAT FLOAT
IMAGE LONGVARBINARY
INT INTEGER
MONEY DECIMAL
NCHAR CHAR
NUMERIC NUMERIC
NVARCHAR VARCHAR
REAL REAL
SMALLDATETIME TIMESTAMP
SMALLINT SMALLINT
SMALLMONEY DECIMAL
SYSNAME VARCHAR
TEXT LONGVARCHAR
TIME2 TIME
TIMESTAMP VARBINARY
TINYINT TINYINT
UNICHAR2 CHAR
UNITEXT1 LONGVARCHAR
UNIVARCHAR2 VARCHAR
UNSIGNED BIGINT1 DECIMAL
UNSIGNED INT1 BIGINT
UNSIGNED SMALLINT1 INTEGER
VARBINARY VARBINARY
VARCHAR VARCHAR

1Supported only for Sybase 15.

2Supported only for Sybase 12.5 and higher.

FOR USERS OF SYBASE ADAPTIVE SERVER 12.5 AND HIGHER: The Sybase driver supports extended new limits (XNL) for character and binary columns—columns with lengths greater than 255. Refer to your Sybase documentation for more information about XNL for character and binary columns.

See GetTypeInfo, for more information about data types.

 

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 Sybase driver supports the following methods of authentication:

The driver's 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=kerberos, the driver uses Kerberos authentication when establishing a connection. The driver ignores any values specified by the User and Password properties.

When AuthenticationMethod=userIdPassword (the default), the driver uses user ID/password 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 User ID/Password Authentication

  1. Set the AuthenticationMethod property to userIdPassword. See Using the AuthenticationMethod Property for more information about setting a value for this property.

  2. Set the User property to provide the user ID.

  3. Set the Password property to provide the password.

 

Configuring Kerberos Authentication

This section provides requirements and instructions for configuring Kerberos authentication for the Sybase driver.

Product Requirements

Verify that your environment meets the requirements listed in Table 7-3 before you configure the driver for Kerberos authentication.

Table 7-3 Kerberos Authentication Requirements for the Sybase Driver
Component Requirements
Database server The database server must be administered by the same domain controller that administers the client and must be running Sybase 12.0 or higher
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 one of the following methods:

  • Windows Active Directory on one of the following operating systems:

    • Windows Server 2003

    • Windows 2000 Server Service Pack 3 or higher

  • MIT Kerberos 1.4.2 or higher
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.

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

To configure the driver:

  1. Set the AuthenticationMethod property to kerberos. See Using the AuthenticationMethod Property for more information about setting a value for this property.

  2. Set the ServicePrincipalName property to the case-sensitive service principal name to be used for Kerberos authentication. For Sybase, the service principal name is the name of a server configured in your Sybase interfaces file.

    The value of the ServicePrincipalName property can include the Kerberos realm name, but it is optional. If you do not specify the realm name, the default realm is used. For example, if the service principal name, including Kerberos realm name, is server/sybase125ase1@XYZ.COM and the default realm is XYZ.COM, valid values for this property are: 

    server/sybase125ase1@XYZ.COM

    and 

    server/sybase125ase1

  3. Modify the krb5.conf file to contain your Kerberos realm name and the KDC name for that Kerberos realm by editing the file with a text editor or by specifying the system properties, java.security.krb5.realm and java.security.krb5.kdc.

    If using 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:[BEA][Sybase JDBC Driver]Could not establish a connection using integrated security: No valid credentials provided

    The krb5.conf file installed with the WebLogic Type 4 JDBC drivers is configured to load automatically unless the java.security.krb5.conf system property is set to point to another Kerberos configuration file.

  4. If using Kerberos authentication with a Security Manager on a Java 2 Platform, grant security permissions to the application and driver. SeePermissions for Kerberos Authentication for an example.

 

Specifying User Credentials for Kerberos Authentication (Delegation of Credentials)

By default, when Kerberos authentication is used, the Sybase 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 user credentials other than the server 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.sybase.SybaseDriver");

         String url = "jdbc:bea:sybase://myServer:5000";

         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

 

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 and the Kerberos authentication is provided by Windows Active Directory, 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.

The application user must explicitly obtain a TGT in the following cases:

To explicitly obtain a TGT, the user must log onto the Kerberos server using the kinit command. 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.

 

Data Encryption

The Sybase 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.

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.

To configure SSL encryption:

  1. Set the EncryptionMethod property to SSL.

  2. 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).

  3. To validate certificates sent by the database server, set the ValidateServerCertificate property to true.

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

 

SQL Escape Sequences

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

 

Isolation Levels

The Sybase driver supports the Read Committed, Read Uncommitted, Repeatable Read, and Serializable isolation levels. The default is Read Committed.

 

Using Scrollable Cursors

The Sybase driver supports scroll-sensitive result sets only on result sets returned from tables created with an identity column. The Sybase driver also supports scroll-insensitive result sets and updatable result sets.

When the Sybase driver cannot support the requested result set type or concurrency, it automatically downgrades the cursor and generates one or more SQLWarnings with detailed information.

 

Large Object (LOB) Support

Although Sybase does not define a Blob or Clob data type, the Sybase 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:

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 Sybase driver provides the following batch mechanisms:

To use the Sybase native batch mechanism, set the BatchPerformanceWorkaround connection property to true. For more information about specifying connection properties, see Sybase Connection Properties.

 

Parameter Metadata Support

The Sybase driver supports returning parameter metadata for all types of SQL statements and stored procedure arguments.

 

ResultSet MetaData Support

If your application requires table name information, the Sybase driver can return table name information in ResultSet metadata for Select statements. By setting the ResultSetMetaDataOptions property to 1, the Sybase 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 Sybase 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 Sybase 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 Sybase 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 Sybase 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 Sybase 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 Sybase driver supports any JSR 114 implementation of the RowSet interface, including:

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.

 

Auto-Generated Keys Support

The Sybase driver supports retrieving the values of auto-generated keys. An auto-generated key returned by the Sybase 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 these values depends on whether you are using an Insert statement that contains parameters:

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 Sybase driver establishes a connection, the driver sets the Sybase database option ansinull to on. Setting ansinull to on ensures that the driver is compliant with the ANSI SQL standard and is consistent with the behavior of other WebLogic Type 4 JDBC drivers, which simplifies developing cross-database applications.

By default, Sybase 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 ansinull=off), the same comparison evaluates to true instead of false.

Setting ansinull 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 Sybase behavior for a connection in the following ways:

 

Sybase JTA Support

Before you can use the Sybase XA driver in a global transaction, first set up your Sybase server to support global transactions. See “ Set Up the Sybase Server for XA Support” in Programming WebLogic JTA.

 

Database Connection Property

The new Database connection property can be used as a synonym of the DatabaseName connection property.

If both the Database and DatabaseName connection properties are specified in a connection URL, the last of either property positioned in the connection URL is used. For example, if your application specifies the following connection URL, the value of the Database connection property would be used instead of the value of the DatabaseName connection property. 

jdbc:bea:sybase://server1:1433;DatabaseName=jdbc;Database=acct;

User=test;Password=secret