5 The Sybase Driver

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

Driver Classes

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

  • XA: weblogic.jdbcx.sybase.SybaseDataSource

  • Non-XA: weblogic.jdbc.sybase.SybaseDriver

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:weblogic:sybase://hostname:port[;property=value[;...]]

where:

  • hostname is the TCP/IP address or TCP/IP host name of the server to which you are connecting. See Using IP Addresses for details on using IP addresses.

    Note:

    Untrusted applets cannot open a socket to a machine other than the originating host.
  • port is the number of the TCP/IP port.

  • property=value specifies connection properties. For a list of connection properties and their valid values, see Sybase Connection Properties.

For example:

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

J2EE Connector Architecture Resource Adapter Class

The ManagedConnectionFactory class for the Informix resource adapter is:

com.weblogic.resource.spi.InformixManagedConnectionFactory

Sybase Connection Properties

Table 5-3 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.

Note:

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 5-1 Sybase Connection Properties

Property Description

AccountingInfo

Accounting information to be stored in the database. This value sets the CURRENT CLIENT_ACCTNG register (DB2 for Linux/UNIX/Windows) or the CLIENT ACCTNG register (DB2 for z/OS and DB2 for iSeries) in the database.This value is for database administration/monitoring purposes

Data Type: String

Valid Values: string where string is the accounting information. The default value is an empty string.

AlternateServers

A list of alternate database servers that is used to failover new or lost connections, depending on the failover method selected. See the FailoverMode property for information about choosing a failover method.

Data type: String

Valid Values:

(servername1[:port1][;property=value[;...]

,servername2[:port2][;property=value[;...]]]...]

The server name (servername1, servername2, and so on) is required for each alternate server entry. Port number (port1, port2, and so on) and connection properties (property=value) are optional for each alternate server entry. If the port is unspecified, the port number of the primary server is used. If the port number of the primary server is unspecified, the default port number of 2003 is used. Optional connection properties are DatabaseName and InformixServer.

Example: The following URL contains alternate server entries for server2 and server3. The alternate server entries contain the optional DatabaseName property.

jdbc:weblogic:sybase://server1:50000;DatabaseName=TEST; User=test;Password=secret;AlternateServers=(server2:50000; DatabaseName=TEST2,server3:50000;DatabaseName=TEST3)

Default: None

ApplicationName

The name of the application to be stored in the database. This value sets the clientapplname and program_name values in the sysprocesses table. This value is used for database administration/monitoring purposes.

Valid Values: string where string is the name of the application. Your database may impose character length restrictions on the value. If the value exceeds a restriction, the driver truncates it.

Data Type: String

Default is empty string.

AuthenticationMethod

Determines which authentication method the driver uses when establishing a connection. If the specified authentication method is not supported by the database server, the connection fails and the driver throws an exception.

Valid values: kerberos | userIdPassword

Data Type: string

If 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 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

Determines the method used to execute batch operations.

Valid values: true | false

Data type: boolean

If 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 false (the default), the driver uses the JDBC 3.0-compliant batch mechanism.

The default is false.

See Batch Inserts and Updates.

BulkLoadBatchSize

Provides a suggestion to the driver for the number of rows to load to the database at a time when bulk loading data. Performance can be improved by increasing the number of rows the driver loads at a time because fewer network round trips are required. Be aware that increasing the number of rows that are loaded also causes the driver to consume more memory on the client.

NOTES:

  • This property suggests the number of rows regardless of which bulk load method is used: using a DDBulkLoad object or using bulk load for batch inserts.

  • The DDBulkObject.setBatchSize() method overrides the value set by this property.

Valid Values: x where x is a positive integer. The default is 2048.

Data Type: long

ClientHostName

The host name of the client machine to be stored in the database. This value sets the clienthostname and hostname values in the sysprocesses table. This value is used for database administration/monitoring purposes.

Valid Values: string where string is the host name of the client machine. Your database may impose character length restrictions on the value that is set by this property. If the value exceeds a restriction, the driver truncates it. Default is empty string.

Data Type: String

ClientUser

The user ID to be stored in the database. This value sets the clientname value in the sysprocesses table in the database. This value is used for database administration/monitoring purposes.

Valid Values: string where string is a valid user ID. Your database may impose character length restrictions on the value that is set by this property. If the value exceeds a restriction, the driver truncates it. Default is empty string.

Data Type: String

CodePageOverride

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

The number of times the driver retries connections to a database server until a successful connection is established.

Valid values: 0 | x where x is any positive integer.

Data type: int

If 0, the driver does not try to reconnect after the initial unsuccessful attempt.

If x, the driver retries connection attempts the specified number of times. If a connection is not established during the retry attempts, the driver returns an exception that is generated by the last database server to which it tried to connect.

The ConnectionRetryDelay property specifies the wait interval, in seconds, used between attempts.

The default is 5.

ConnectionRetryDelay

The number of seconds the driver waits before retrying connections to a database server when ConnectionRetryCount is set to a positive integer.

Valid values: 0 | x where x is the amout of time, in seconds.

Data type: int

If 0, the driver does not delay between retries.

If x, the driver waits between connection retry attempts the specified number of seconds.

The default is 1.

ConvertNull

Controls how data conversions are handled for null values.

Valid values: 0 | 1

Data type: int

If 1, the driver checks the data type being requested against the data type of the table column storing the data. If a conversion between the requested type and column type is not defined, the driver generates an "unsupported data conversion" exception regardless of the data type of the column value.

If 0, the driver does not perform the data type check if the value of the column is null. This allows null values to be returned even though a conversion between the requested type and the column type is undefined.

The default is 1.

Database

Alias for DatabaseName.

DatabaseName

The name of the database to which you want to connect.

Valid Values: string where string is the name of a Sybase database.Default NoneData Type: StringAlias: Database property. If both the Database and DatabaseName properties are specified in a connection URL, the last property that is 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:datadirect:sybase://server1:1433;DatabaseName=jdbc; Database=acct;User=test;Password=secret

EnableBulkLoad

Specifies whether the driver uses the native bulk load protocols in the database instead of the batch mechanism for batch inserts. Bulk load bypasses the data parsing that is usually done by the database, providing an additional performance gain over batch operations. This property allows existing applications with batch inserts to take advantage of bulk load without requiring changes to the application code.

Valid Values: true | false

If true, the driver uses the native bulk load protocols for batch inserts. Any value set for BatchPerformanceWorkaround is ignored.

If false, the driver uses the batch mechanism for batch inserts.

Data Type: boolean

Default is false.

EnableCancelTimeout

Determines whether a cancel request sent by the driver as the result of a query timing out is subject to the same query timeout value as the statement it cancels.

Valid Values: true | false

Data Type: boolean

If 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, the driver sends a cancel request 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 false (the default), the cancel request does not time out.

EncryptionMethod

Determines whether SSL encryption is used to encrypt and decrypt data transmitted over the network between the driver and database server.

Valid values: noEncryption | SSL

Data Type: String

If noEncryption, 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 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

The default is noEncryption.

ErrorBehavior

Determines how the driver handles errors returned from stored procedures.

Valid values: exception | warning | raiseerrorwarning

Data type: String

If exception, the driver throws an exception when it encounters stored procedure errors, including RAISERRORs.

If warning, the driver returns stored procedure errors, including RAISERRORs, as SQLWarnings.

If raiseerrorwarning, the driver returns RAISERRORs as SQLWarnings and throws exceptions for other stored procedure errors.

NOTE: By default, older 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

The default is exception.

FailoverGranularity

Determines whether the driver fails the entire failover process or continues with the process if exceptions occur while trying to reestablish a lost connection. This property is ignored if FailoverMode=connect.

Valid Values: nonAtomic | atomic | atomicWithRepositioning | disableIntegrityCheck

If nonAtomic, the driver continues with the failover process and posts any exceptions on the statement on which they occur.

If atomic, the driver fails the entire failover process if an exception is generated as the result of restoring the state of the connection. If an exception is generated as a result of restoring the state of work in progress, the driver continues with the failover process, but generates an exception warning that the Select statement must be reissued.

If atomicWithRepositioning, the driver fails the entire failover process if any exception is generated as the result of restoring the state of the connection or the state of work in progress.

If disableIntegrityCheck, the driver does not verify that the rows restored during the failover process match the original rows. This value is applicable only when FailoverMode=select.

Data Type: String

Default is nonAtomic.

FailoverMode

Specifies the type of failover method the driver uses.

Valid Values: connect | extended| select

If connect, the driver provides failover protection for new connections only.

If extended, the driver provides failover protection for new and lost connections, but not any work in progress.

If select, the driver provides failover protection for new and lost connections. In addition, it preserves the state of work performed by the last Select statement executed on the Statement object.

NOTES:

  • The AlternateServers property specifies one or multiple alternate servers for failover and is required for all failover methods.

  • The FailoverGranularity property determines which action the driver takes if exceptions occur during the failover process.

  • The FailoverPreconnect property specifies whether the driver tries to connect to multiple database servers (primary and alternate) at the same time.

Data Type: String

Default is connect.

FailoverPreconnect

Specifies whether the driver tries to connect to the primary and an alternate server at the same time. This property is ignored if FailoverMode=connect.

Valid Values: true | false

If true, the driver tries to connect to the primary and an alternate server at the same time. This can be useful if your application is time-sensitive and cannot absorb the wait for the failover connection to succeed.

If false, the driver tries to connect to an alternate server only when failover is caused by an unsuccessful connection attempt or a lost connection. This value provides the best performance, but your application typically experiences a short wait while the failover connection is attempted.

NOTE: The AlternateServers property specifies one or multiple alternate servers for failover.

Data Type: boolean

Default is false.

HostNameInCertificate

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.

NOTES:

  • If SSL encryption or certificate validation is not enabled, this property is ignored.

  • If SSL encryption and validation is enabled and this property is unspecified, the driver uses the server name specified in the connection URL or data source of the connection to validate the certificate.

Valid Values: host_name | #SERVERNAME# where host_name is a valid host name.

If host_name, the driver compares the specified host name to the DNSName value of the SubjectAlternativeName in the certificate. If a DNSName value does not exist in the SubjectAlternativeName 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 that is 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 in the SubjectAlternativeName or if the certificate does not have a SubjectAlternativeName, the driver compares the host name to the CN part of the certificate's Subject name. If the values do not match, the connection fails and the driver throws an exception. 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.

Data Type: String

Default is empty string.

ImportStatementPool

Specifies the path and file name of the file to be used to load the contents of the statement pool. When this property is specified, statements are imported into the statement pool from the specified file. If the driver cannot locate the specified file when establishing the connection, the connection fails and the driver throws an exception.

Valid Values: string where string is the path and file name of the file to be used to load the contents of the statement pool.

Data Type: String

Default is 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. If the execution of a SQL command fails, the connection attempt also fails and the driver throws an exception indicating which SQL command or commands failed.

Valid Values: command[[;command]...] where command is a SQL command.

NOTE: Multiple commands must be separated by semicolons. In addition, if this property is specified in a connection URL, the entire value must be enclosed in parentheses when multiple commands are specified.

Example: The following connection URL sets the handling of null values to the Sybase default and allows delimited identifiers:

jdbc:datadirect:sybase://server1:5000; InitializationString=(set ANSINULL off; set QUOTED_IDENTIFIER on);DatabaseName=test

Data Type: String

InsensitiveResultSetBufferSize

Determines the amount of memory used by the driver to cache insensitive result set data.

Valid Values -1 | 0 | x

Data Type: int

If -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 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 x, where x is a positive integer that specifies the size (in KB) of the memory buffer used to cache 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)

JavaDoubleToString

Determines whether the driver uses its internal conversion algorithm or the JVM conversion algorithm when converting double or float values to string values.

Valid Values: true | false

If true, the driver uses the JVM algorithm when converting double or float values to string values.

If false, the driver uses its internal algorithm when converting double or float values to string values. Using this value 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.

JDBCBehavior

Determines how the driver describes database data types that map to the following JDBC 4.0 data types: NCHAR, NVARCHAR, NLONGVARCHAR, NCLOB, and SQLXML. In addition, it controls whether the PROCEDURE_NAME column returned by DatabaseMetadata.getProcedures() and DatabaseMetadata.getProcedureColumns() contains procedure name qualifiers. This property is applicable only when the application is using Java SE 6.

Valid Values: 0 | 1

Data Type: int

If 0, the driver describes the data types as JDBC 4.0 data types when using Java SE 6. Additionally, the PROCEDURE_NAME column does not contain procedure name qualifiers in the specific_name column. For example, for the fully qualified procedure name 1.sp_productadd, the driver would return sp_productadd instead of sp_productadd;1.

If 1, the driver describes the data types using JDBC 3.0-equivalent data types, regardless of JVM. This allows your application to continue using JDBC 3.0 types in a Java SE 6 environment. Additionally, the PROCEDURE_NAME column contains procedure name qualifiers. For example, for the fully qualified procedure name 1.sp_productadd, the driver would return sp_productadd;1

Default is 1.

LoadBalancing

Determines whether the driver uses client load balancing in its attempts to connect to the database servers (primary and alternate). You can specify one or multiple alternate servers by setting the AlternateServers property.

Data Type: boolean

Valid Values: true | false

If true, the driver uses client load balancing and attempts to connect to the database servers (primary and alternate) in random order. The driver randomly selects from the list of primary and alternate servers which server to connect to first. If that connection fails, the driver again randomly selects from this list of servers until all servers in the list have been tried or a connection is successfully established.

If false, the driver does not use client load balancing and connects to each server based on their sequential order (primary server first, then, alternate servers in the order they are specified). The default value is false.

LoginTimeout

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.

Valid Values: 0 | x where x is a positive integer.

Data Type: int

If 0, the driver does not time out a connection request. The default value is 0.

If x, the driver waits for the specified number of seconds before returning control to the application and throwing a timeout exception.

LongDataCacheSize

Determines whether the driver caches long data (images, pictures, long text, or binary data) in result sets. To improve performance, you can disable long data caching if your application retrieves columns in the order in which they are defined in the result set.

Valid Values: -1 | 0 | x where x is a positive integer.

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

MaxPooledStatements

The maximum number of pooled prepared statements for this connection. Setting MaxPooledStatements to an integer greater than zero (0) enables the driver's internal prepared statement pooling, which is useful when the driver is not running from within an application server or another application that provides its own prepared statement pooling.Data Type: int

Valid Values: 0 | x where x is a positive integer.

If set to 0, the driver's internal prepared statement pooling is not enabled. The default value is 0.

If set to x, the driver enables the Statement Pool Monitor and uses the specified value to cache a certain number of prepared statements that are created by an application. If the value set for this property is greater than the number of prepared statements used by the application, all prepared statements that are created by the application are cached. Because CallableStatement is a sub-class of PreparedStatement, CallableStatements also are cached.

Example: If the value of this property is set to 20, the driver caches the last 20 prepared statements that are created by the application.

MaxStatements

An alias for the MaxPooledStatements property.

PacketSize

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.

Valid values: -1 | 0 | x where x is an integer from 1 to 127.

Data Type: int

If -1, the driver uses the maximum packet size that is used by the database server.

If 0, the driver uses the default maximum packet size used by the database server.

If x, 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 your application sends queries that only retrieve small result sets, you may want to use a packet size smaller than the maximum packet size that is configured on the database server. If a result set that contains only one or two rows of data does not completely fill a larger packet, performance will not improve by setting the value to the maximum packet size.

See Performance Considerations for information about configuring this property for optimal performance.

The default is 0.

Password

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

Valid Values: string where string is a valid password. The password is case-sensitive.

Data type: String

PortNumber (Required)

The TCP port of the primary database server that is listening for connections to the Sybase database. This property is supported only for data source connections.

Valid values: port where port is the port number. The default varies depending on operating system.

Data type: int

PrepareMethod

Determines whether stored procedures are created on the server for prepared statements.

Valid Values: StoredProc | StoredProclfParam | Direct.

Data type: String

If StoredProc, a stored procedure is created when the statement is prepared and is executed when the prepared statement is executed.

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

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.

The default is StoredProclfParam.

See Performance Considerations for information about configuring this property for optimal performance.

ProgramID

The product and version information of the driver on the client to be stored in the database. This value is stored locally and is used for database administration/monitoring purposes.

Data Type: String

Valid Values: DDJVVRRM where:

  • VV identifies a 2-digit version number (with high-order 0 in the case of a 1-digit version).

  • RR identifies a 2-digit release number (with high-order 0 in the case of a 1-digit release).

  • M identifies a 1-character modification level (0-9 or A-Z).

  • Default is 0000016a.

Example: DDJ04100

QueryTimeout

Sets the default query timeout (in seconds) for all statements created by a connection.

Valid Values -1 | 0 | x where x is a number of seconds.

Data type: int

If -1, the query timeout functionality is disabled. The driver silently ignores calls to the Statement.setQueryTimeout() method.

If 0, the default query timeout is infinite (the query does not time out).

If x, 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.

The default is 0.

ResultSetMetaDataOptions

Determines whether the driver returns table name information in the ResultSet metadata for Select statements.

Valid Values: 0 | 1

Data Type: int

If 0 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 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.

Default is 0.

SelectMethod

A hint to the driver that determines whether the driver requests a database cursor for Select statements. Performance and behavior of the driver are affected by this property, which is defined as a hint because the driver may not always be able to satisfy the requested method.

Valid Values: direct | cursor

Data Type: String

If 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 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 in IPv4 or IPv6 format, or the server name (if your network supports named servers) of the primary database server. This property is supported only for data source connections.

Valid Values: string where string is a valid IP address or server name.

Data type: String

ServicePrincipalName

Specifies the service principal name to be used by the driver for Kerberos authentication. For Sybase, the service principal name is the name of a server that is configured in your Sybase interfaces file. If you set this property, you also must set the value of the AuthenticationMethod property to Kerberos. When Kerberos authentication is not used, this property is ignored.

Valid Values: string where string is a valid service principal name. This name is case-sensitive.

Data type: String

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 or server/sybase125ase1

See Authentication for more information about using authentication with the Sybase driver.

SpyAttributes

Enables Spy to log detailed information about calls issued by the driver on behalf of the application.

Valid Values: (spy_attribute[;spy_attribute]...) where spy_attribute is any valid DataDirect Spy attribute. See "Tracking JDBC Calls with WebLogic JDBC Spy" for more details.

Data Type: String

Default: None.

If coding a path on Windows to the log file in a Java string, the backslash character (\) must be preceded by the Java escape character, a backslash. For example: log=(file)C:\\temp\\spy.log.

Example: The following value instructs the driver to log all JDBC activity to a file using a maximum of 80 characters for each line:

(log=(file)/tmp/spy.log;linelimit=80)

TransactionMode

Controls how the driver delimits the start of a local transaction.

Valid Values: implicit | explicit

Data Type: String

If implicit, the driver uses implicit transaction mode. This means that Sybase, not the driver, automatically starts a transaction when a transactionable statement is executed. Typically, implicit transaction mode is more efficient than explicit transaction mode because the driver does not have to send commands to start a transaction and a transaction is not started until it is needed. When TRUNCATE TABLE statements are used with implicit transaction mode, Sybase may roll back the transaction if an error occurs. If this occurs, use the explicit value for this property.

If explicit, the driver uses explicit transaction mode. This means that the driver, not Sybase, starts a new transaction if the previous transaction was committed or rolled back.

Default is implicit.

TrustStore

Specifies the directory of the truststore file to be used when SSL server authentication is used. The truststore file contains a list of the Certificate Authorities (CAs) that the client trusts.

This value overrides the directory of the truststore file specified by the javax.net.ssl.trustStore Java system property. If this property is not specified, the truststore directory is specified by the javax.net.ssl.trustStore Java system property.

This property is ignored if ValidateServerCertificate=false.

Valid Values: string where string is the directory of the truststore file.

Data Type: String

Default: None

TrustStorePassword

Specifies the password of the truststore file to be used when SSL server authentication is used. The truststore file contains a list of the Certificate Authorities (CAs) that the client trusts.

This value overrides the password of the truststore file specified by the javax.net.ssl.trustStorePassword Java system property. If this property is not specified, the truststore password is specified by the javax.net.ssl.trustStorePassword Java system property.

This property is ignored if ValidateServerCertificate=false.

Valid Values: string where string is a valid password for the truststore file.

Data Type: String

Default: None

UseAlternateProductInfo

Determines if the driver will perform additional processing to return more accurate information for the DatabaseMetaData.getDatabaseProductName() and DatabaseMetaData.getDatabaseProductVersion() methods.

Valid Values: true | false

Data Type: boolean

If true, the driver makes an additional query to select the value of @@version and returns only the product name information from the string it receives when getDatabaseProductName() is called. When getDatabaseProductVersion() is called, the entire string is returned. For example:

Adaptive Server Enterprise/12.5.1/EBF 11428/P/NT (1X86)/OS 4.0/ase1251/1823/32-bit/OPT/Wed Sep 17 11:10:54 2003

If false, the driver returns the information that it receives from the server during the login process. Previous versions of the driver returned this information.

Default is false.

User

The user name that is used to connect to the Sybase database. A user name is required only if security is enabled on your database. Contact your system administrator to get your user name.

Valid Values: string where string is a valid user name. The user name is case-insensitive.

Data Type: String

Default: None

ValidateServerCertificate

Determines whether the driver validates the certificate that is sent by the database server when SSL encryption is enabled (EncryptionMethod=SSL). When using SSL server authentication, any certificate that is sent by the server must be issued by a trusted Certificate Authority (CA). Allowing the driver to trust any certificate that is returned from the server even if the issuer is not a trusted CA is useful in test environments because it eliminates the need to specify truststore information on each client in the test environment.

Valid Values true | false

Data Type: boolean

If true, the driver validates the certificate that is sent by the database server. Any certificate from the server must be issued by a trusted CA in the truststore file. If the HostNameInCertificate property is specified, the driver also validates the certificate using a host name. The HostNameInCertificate property is optional and provides additional security against man-in-the-middle (MITM) attacks by ensuring that the server that the driver is connecting to is the server that was requested.

If false, the driver does not validate the certificate that is sent by the database server. The driver ignores any truststore information that is specified by the TrustStore and TrustStorePassword properties or Java system properties.

Default is true.


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.

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

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. See rss for more information about using prepared statement pooling to optimize performance.

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:

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

Data Types

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

Table 5-2 Sybase Data Types

Sybase Data Type JDBC Data Type

BIGINTFoot 1 

BIGINT

BINARY

BINARY

BIT

BIT

CHAR

CHAR

DATEFoot 2 

DATE

DATETIME

TIMESTAMP

DECIMAL

DECIMAL

FLOAT

FLOAT

IMAGE

LONGVARBINARY

INT

INTEGER

MONEY

DECIMAL

NUMERIC

NUMERIC

REAL

REAL

SMALLDATETIME

TIMESTAMP

SMALLINT

SMALLINT

SMALLMONEY

DECIMAL

SYSNAME

VARCHAR

TEXT

LONGVARCHAR

TIMEFoot 3 

TIME

TIMESTAMP

VARBINARY

TINYINT

TINYINT

UNICHARFoot 4 

CHAR

NOTE: If JDBCBehavior=0, the data type depends on the JVM used by the application: NCHAR (if using Java SE 6) or CHAR (if using another JVM).

UNITEXTFoot 5 

LONGVARCHAR

NOTE: If JDBCBehavior=0, the data type depends on the JVM used by the application: LONGNVARCHAR (if using Java SE 6) or LONGVARCHAR (if using another JVM).

UNIVARCHARFoot 6 

VARCHAR

NOTE: If JDBCBehavior=0, the data type depends on the JVM used by the application: NVARCHAR (if using Java SE 6) or VARCHAR (if using another JVM).

UNSIGNED BIGINTFoot 7 

DECIMAL

UNSIGNED INTFoot 8 

BIGINT

UNSIGNED SMALLINTFoot 9 

INTEGER

VARBINARY

VARBINARY

VARCHAR

VARCHAR


Footnote 1 Supported only for Sybase 15.

Footnote 2 Supported only for Sybase 12.5 and higher.

Footnote 3 Supported only for Sybase 12.5 and higher

Footnote 4 Supported only for Sybase 12.5 and higher

Footnote 5 Supported only for Sybase 15.

Footnote 6 Supported only for Sybase 12.5 and higher

Footnote 7 Supported only for Sybase 15.

Footnote 8 Supported only for Sybase 15.

Footnote 9 Supported only for Sybase 15.

Note:

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 Appendix B, "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:

  • 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 and MIT Kerberos.

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

Perform the following steps to configure the user ID and password:

  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 5-3 before you configure the driver for Kerberos authentication.

Table 5-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

  • 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 J2SE documentation for information about setting configuration options in this file

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.

    Note:

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

    The krb5.conf file installed with the Oracle 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, you must grant security permissions to the application and driver. See Permissions for Kerberos Authentication for an example.

Specifying User Credentials for Kerberos Authentication

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:weblogic: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:

  • If the application uses Kerberos authentication from a UNIX or Linux client

  • If the application uses Kerberos authentication from a Windows client and Kerberos authentication is provided by MIT Kerberos

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.

Note:

Connection hangs can occur when the driver is configured for SSL and the database server does not support SSL. You may want to set a login timeout using the LoginTimeout property to avoid problems when connecting to a server that does not support SSL.

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.

Client Information for Connections

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

This information can be used for database administration and monitoring purposes. See rss.

SQL Escape Sequences

See Appendix C, "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.

Note:

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:

  • Provides random access to data

  • Allows searching for patterns in the data, such as retrieving long data that begins with a specific character string

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

Batch Inserts and Updates

The Sybase driver provides the following batch mechanisms:

  • A JDBC-compliant mechanism that uses code in the driver to execute batch operations. This is the default mechanism used by the Sybase driver.

  • A mechanism that uses the Sybase native batch functionality. This mechanism may be faster than the standard mechanism, particularly when performance-expensive network roundtrips are an issue. 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.

To use the Sybase native batch mechanism, set the BatchPerformanceWorkaround connection property to true.

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:

  • CachedRowSets

  • FilteredRowSets

  • WebRowSets

  • JoinRowSets

  • JDBCRowSets

J2SE 1.4 or higher is required to use rowsets with the driver.

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

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:

  • When using an Insert statement that contains no parameters, the Sybase 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 Sybase driver supports the following form of the Connection.prepareStatement() method to instruct 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 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 Oracle 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:

  • Use the InitializationString property to specify the SQL command set ANSINULL off. For example, the following URL ensures that the handling of null values is restored to the Sybase default for the current connection:

    jdbc:weblogic:sybase://server1:5000;
    InitializationString=set ANSINULL off;DatabaseName=test
    
  • Explicitly execute the following statement after the connection is established:

    SET ANSINULL OFF
    

Sybase JTA Support

Before you can use the Sybase XA driver in a global transaction, you must first set up your Sybase server to support global transactions. See "Configuring a Sybase Server for XA Support" in Programming JTA for Oracle WebLogic Server.

Configuring Failover

Use the following procedure to configure failover:

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

      See rss

  2. Choose a failover method by setting the FailoverMode connection property. The default method is connection failover (FailoverMode=connect).

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

  4. Optionally, configure the connection retry feature. See rss.

  5. 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 Informix driver specifies connection information for the primary and alternate servers using a connection URL:

jdbc:datadirect:sybase://server1:4100;DatabaseName=TEST;User=test; Password=secret;AlternateServers=(server2:4100;DatabaseName=TEST2, server3:4100;DatabaseName=TEST3)

In this example:

...server1:4100;DatabaseName=TEST...

is the part of the connection URL that specifies connection information for the primary server. Alternate servers are specified using the AlternateServers property. For example:

...;AlternateServers=(server2:4100;DatabaseName=TEST2, server3:4100;DatabaseName=TEST3)

Similarly, the same connection information for primary and alternate servers specified using a JDBC data source would look like this:

Example 5-1 Example JDBC Data Source Configuration

SybaseDataSource mds = new SybaseDataSource();
mds.setDescription("My SybaseDataSource");
mds.setServerName("server1");
mds.setPortNumber(4100);
mds.setDatabaseName("TEST");
mds.setUser("test");
mds.setPassword("secret");
AlternateServers=(server2:4100;DatabaseName=TEST2,
server3:4100;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 value of the AlternateServers property is a string that has the format:

((servername1[:port1][;property=value][,servername2[:port2] [;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.

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

  • property=value is either of the following connection properties: DatabaseName or InformixServer. These connection properties are optional for each alternate server entry. For example:

    jdbc:datadirect:sybase://server1:4100;DatabaseName=TEST;User=test; Password=secret;AlternateServers=(server2:4100;DatabaseName=TEST2, server3:4100)

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:sybase://server1:4100;DatabaseName=TEST;User=test; Password=secret;AlternateServers=(server2:4100,server3:4100)

Specify Connection Retry

Connection retry allows the Informix 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:sybase://server1:4100;DatabaseName=TEST;User=test; Password=secret;AlternateServers=(server2:4100;DatabaseName=TEST2, server3:4100;DatabaseName=TEST3);ConnectionRetryCount=2; ConnectionRetryDelay=5

In this example, if a successful connection is not established on the Sybase 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 table summarizes the connection properties that control how failover works with the Informix driver.

Table 5-4 Summary: Failover Properties for the Informix Driver



AlternateServers

One or multiple alternate database servers. An IP address or server name identifying each server is required. Port number and the DatabaseName connection property are optional. If the port number is unspecified, the port number specified for the primary server 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 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.

ServerName

IP address or server name of the primary database server. This property is supported only for data source connections.


Bulk Load

The driver supports WebLogic 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. See rss.