The Sybase Driver
The BEA WebLogic Type 4 JDBC Sybase driver is available in the WebLogic Server Service Pack 2 and later releases. It is not available with the WebLogic Server 8.1 GA and Service Pack 1 releases.
The following sections describe how to configure and use the BEA WebLogic Type 4 JDBC Sybase driver:
- Database Version Support
- Driver Classes
- Sybase URL
- Sybase Connection Properties
- Sample Connection Pool Configuration
- Data Types
- SQL Escape Sequences
- Isolation Levels
- Using Scrollable Cursors
- Large Object (LOB) Support
- Batch Inserts and Updates
Database Version Support
The BEA WebLogic Type 4 JDBC driver for Sybase (the "Sybase driver") supports the following database versions:
- Sybase Adaptive Server 11.5 and 11.9 running on Windows and UNIX via TDS (non-XA only)
- Sybase Adaptive Server Enterprise 12.0 and 12.5 running on Windows and UNIX via TDS (XA and non-XA)
Note: XA connections are supported with the Sybase Adaptive Server Enterprise 12.0 and later versions only. XA connections are not supported on Sybase Adaptive Server 11.5 and 11.9.
Driver Classes
The driver class for the BEA WebLogic Type 4 JDBC Sybase driver is:
XA: weblogic.jdbcx.sybase.SybaseDataSource
Non-XA: weblogic.jdbc.sqlserver.SybaseDriver
Use these driver classes when configuring a JDBC connection pool in your WebLogic Server domain.
Sybase URL
To connect to Sybase database, use the following URL format:
jdbc:bea:sybase://dbserver:port
The Sybase driver also supports the AlternateServers option for connection failover. See Connection Failover with AlternateServers.
Sybase Connection Properties
Table 6-1 lists the JDBC connection properties supported by the Sybase driver, and describes each property. You can use these connection properties in a JDBC connection pool configuration in your WebLogic Server domain. To specify a property, use the following form in the JDBC connection pool configuration:
property=value
Note: All connection string property names are case-insensitive. For example, Password is the same as password.
|
Property |
Description |
|---|---|
| BatchPerformanceWorkaroundOPTIONAL | {true | false}. Determines the method used to execute batch operations. When set to true, the native Sybase batch mechanism is used. When set to false, the JDBC 3.0-compliant batch mechanism is used. See Batch Inserts and Updates.The default is false. |
| DatabaseNameOPTIONAL | The name of the database to which you want to connect. |
| LoginTimeoutOPTIONAL | The maximum time in seconds that attempts to create a database connection will wait. A value of 0 specifies that the timeout is the default system timeout if there is one; otherwise it specifies that there is no timeout. |
| Password | The case-sensitive password used to connect to your Sybase database. A password is required only if security is enabled on your database. If so, contact your system administrator to get your password. |
| PortNumber | The TCP port on which the database server listens for connections. The default varies depending on operating system. |
| SelectMethodOPTIONAL | {direct | cursor}. Determines whether the driver should request 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.Direct - When the driver uses the direct method, the database server sends the complete result set in a single response to the driver when responding to a query. Normally, responses are not cached by the driver. Using this method, the driver must process all the 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. Because the driver must cache the responses when there are multiple active queries, avoid using the direct method when using multiple open result sets that contain large amounts of data.Cursor - When the driver uses the cursor method, a server-side cursor is generated. The rows are fetched from the server in blocks. The JDBC Statement method setFetchSize can be used to control the number of rows that are retrieved for each request. Performance tests show that the value of setFetchSize significantly impacts performance when the cursor method is used. There is no simple rule for determining the setFetchSize value that you should use. We recommend that you experiment with different setFetchSize values to find out 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.The default is direct. |
| ServerName | The name or IP address of the database server. |
| User | The case-insensitive user name used to connect to your Sybase database. A user name is required only if security is enabled on your database. If so, contact your system administrator to get your user name. |
Sample Connection Pool Configuration
Table 6-2 lists configuration attributes for a sample WebLogic Server connection pool that uses the non-XA version of the WebLogic Type 4 Sybase JDBC driver. Table 6-3 lists configuration attributes for a sample WebLogic Server connection pool that uses the XA version of the driver.
|
Attribute |
Value |
|---|---|
| URL | jdbc:bea:sybase://host:port |
| Driver Class Name | weblogic.jdbc.sybase.SybaseDriver |
| Properties |
user=username
PortNumber=port url=jdbc:bea:sybase://host:port ServerName=host DatabaseName=dbname |
| Password | password |
| Target | server or cluster name |
|
Attribute |
Value |
|---|---|
| URL | jdbc:bea:sybase://host:port |
| Driver Class Name | weblogic.jdbcx.sybase.SybaseDataSource |
| Properties |
user=username
PortNumber=port url=jdbc:bea:sybase://host:port ServerName=host DatabaseName=dbname |
| SupportsLocalTransaction | true (required only for local transactions) |
| Password | password |
| Target | server or cluster name |
Data Types
Table 6-4 lists the data types supported by the Sybase driver and how they are mapped to JDBC data types.
|
Sybase Data Type |
JDBC Data Type |
|---|---|
| binary | BINARY |
| bit | BIT |
| char | CHAR |
| datetime | TIMESTAMP |
| decimal | DECIMAL |
| float | FLOAT |
| image | LONGVARBINARY |
| int | INTEGER |
| money | DECIMAL |
| nchar | CHAR |
| numeric | NUMERIC |
| nvarchar | VARCHAR |
| real | REAL |
| smalldatetime | TIMESTAMP |
| smallint | SMALLINT |
| smallmoney | DECIMAL |
| sysname | VARCHAR |
| text | LONGVARCHAR |
| timestamp | VARBINARY |
| tinyint | TINYINT |
| unichar1 | CHAR |
| univarchar1 | VARCHAR |
| varbinary | VARBINARY |
| varchar | VARCHAR |
|
1 Sybase Adaptive Server Enterprise 12.5 only | |
Note: For users of Adaptive Server 12.5, the Sybase driver supports extended new limits (XNL) for character and binary columns - columns with lengths greater than 255. See GetTypeInfo for details.
SQL Escape Sequences
See SQL Escape Sequences for JDBC for information about the SQL escape sequences supported by the Sybase driver.
Isolation Levels
Sybase supports isolation levels Read Committed, Read Uncommitted, Repeatable Read, and Serializable. 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 retrieve and update long data, 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
- Allows determining the length of the data before the data is actually retrieved
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 3.0-compliant mechanism that uses code in the driver to execute batch operations. This is the standard mechanism.
- A mechanism that uses the Sybase native batch functionality. This mechanism may be faster than the standard mechanism, particularly when performance-expensive network round-trips 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. For more information about specifying connection properties, see Sybase Connection Properties.
Auto-Generated Keys Support
The Sybase driver supports retrieving the values of auto-generated keys. How you return those values depends on whether you are using an Insert statement that contains parameters:
- When using an Insert statement that contains no parameters, the Sybase driver supports the following form of the Statement.execute and Statement.executeUpdate methods to inform the driver to return the values of auto-generated keys:
- Statement.execute (String sql, int autoGeneratedKeys)
- Statement.executeUpdate (String sql, int autoGeneratedKeys)
- When using a Insert statement that contains parameters, the Sybase driver supports the following form of the Connection.prepareStatement method to inform the driver to return the values of auto-generated keys:
- Connection.prepareStatement (String sql, int autoGeneratedKeys)
The application fetches the values of generated keys from the driver using the Statement.getGeneratedKeys () method.
Sybase JTA Support
Before you can use the Sybase XA driver in a global transaction, first set up your Sybase server to support global transactions. See "Set Up the Sybase Server for XA Support in Programming WebLogic JTA.
|
|
|