5 The Sybase Driver

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

Driver Classes
Sybase URL
J2EE Connector Architecture Resource Adapter Class
Sybase Connection Properties
Performance Considerations
Data Types
Authentication
Data Encryption
Client Information for Connections
SQL Escape Sequences
Isolation Levels
Using Scrollable Cursors
Large Object (LOB) Support
Batch Inserts and Updates
Parameter Metadata Support
ResultSet MetaData Support
Rowset Support
Auto-Generated Keys Support
NULL Values
Sybase JTA Support
Configuring Failover
Bulk Load

Driver Classes

The driver class for the WebLogic 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 `RAISERROR`s. If `warning`, the driver returns stored procedure errors, including `RAISERROR`s, 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 `atomicWithRepositionin`g, 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 `SubjectAlternativeNam`e 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`, `CallableStatement`s 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
EnableBulkLoad
EncryptionMethod
InsensitiveResultSetBufferSize
LongDataCacheSize
MaxPooledStatements
PacketSize
PrepareMethod
ResultSetMetaDataOptions
SelectMethod

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
BIGINT^Foot 1	BIGINT
BINARY	BINARY
BIT	BIT
CHAR	CHAR
DATE^Foot 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
TIME^Foot 3	TIME
TIMESTAMP	VARBINARY
TINYINT	TINYINT
UNICHAR^Foot 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).
UNITEXT^Foot 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).
UNIVARCHAR^Foot 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 BIGINT^Foot 7	DECIMAL
UNSIGNED INT^Foot 8	BIGINT
UNSIGNED SMALLINT^Foot 9	INTEGER
VARBINARY	VARBINARY
VARCHAR	VARCHAR

^Footnote 1Supported only for Sybase 15.

^Footnote 2Supported only for Sybase 12.5 and higher.

^Footnote 3Supported only for Sybase 12.5 and higher

^Footnote 4Supported only for Sybase 12.5 and higher

^Footnote 5Supported only for Sybase 15.

^Footnote 6Supported only for Sybase 12.5 and higher

^Footnote 7Supported only for Sybase 15.

^Footnote 8Supported only for Sybase 15.

^Footnote 9Supported 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:

Set the AuthenticationMethod property to userIdPassword. See Using the AuthenticationMethod Property for more information about setting a value for this property.
Set the User property to provide the user ID.
Set the Password property to provide the password.

Configuring Kerberos Authentication

This section provides requirements and instructions for configuring Kerberos authentication for the 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.

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:

Set the AuthenticationMethod property to kerberos. See Using the AuthenticationMethod Property for more information about setting a value for this property.
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
```
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 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.
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:

Set the EncryptionMethod property to SSL.
Specify the location and password of the truststore file used for SSL server authentication. Either set the TrustStore and TrustStore properties or their corresponding Java system properties (javax.net.ssl.trustStore and javax.net.ssl.trustStorePassword, respectively).
To validate certificates sent by the database server, set the ValidateServerCertificate property to true.
Optionally, set the HostNameInCertificate property to a host name to be used to validate the certificate. The HostNameInCertificate property provides additional security against man-in-the-middle (MITM) attacks by ensuring that the server the driver is connecting to is the server that was requested.

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

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:

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
Choose a failover method by setting the FailoverMode connection property. The default method is connection failover (FailoverMode=connect).
If FailoverMode=extended or FailoverMode=select, set the FailoverGranularity property to specify how you want the driver to behave if exceptions occur while trying to reestablish a lost connection. The default behavior of the driver is to continue with the failover process and post any exceptions on the statement on which they occur (FailoverGranularity=nonAtomic).
Optionally, configure the connection retry feature. See rss.
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.