Skip navigation.
Downloads | Product Documentation | Support

WebLogic Type 4 JDBC Drivers

# The Sybase Driver

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
- Performance Considerations
- Data Types
- 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

# 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
- Sybase Adaptive Server Enterprise 12.0, 12.5, and 12.5.1
- Sybase Adaptive Server Enterprise 15

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.sybase.SybaseDriver`

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

# Sybase URL

To connect to a Sybase database, use the following URL format:

>     jdbc:bea:sybase://dbserver:port

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

Property | Description
--- | ---
BatchPerformanceWorkaround OPTIONAL | {true | false}. Determines the method used to execute batch operations. If set to true, the native Sybase batch mechanism is used. If set to false, the JDBC 3.0-compliant batch mechanism is used. 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. The default is false. See Batch Inserts and Updates.
CodePageOverride OPTIONAL | Specifies the code page the driver uses when converting 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 Java Virtual Machine, for example, CodePageOverride=CP950.
ConnectionRetryCount OPTIONAL | The number of times the driver retries connections to a database server until a successful connection is established. Valid values are 0 and any positive integer. The default is 0.
ConnectionRetryDelay OPTIONAL | The number of seconds the driver waits before retrying connections to a database serverwhen ConnectionRetryCount is set to a positive integer. The default is 3.
DatabaseName OPTIONAL | The name of the database to which you want to connect.
InsensitiveResultSetBufferSize OPTIONAL | {-1 | 0 | x}. Determines the amount of memory used by the driver to cache insensitive result set data. It must have one of the following values: If set to -1, the driver caches all insensitive result set data in memory. If the size of the result set exceeds available memory, an OutOfMemoryException is generated. Because the need to write result set data to disk is eliminated, the driver processes the data more efficiently. If set to 0, the driver caches all insensitive result set data in memory, up to a maximum of 2 GB. If the size of the result set data exceeds available memory, the driver pages the result set data to disk. Because result set data may be written to disk, the driver may have to reformat the data to write it correctly to disk. If set to x, where x is a positive integer, the driver caches all insensitive result set data in memory, using this value to set the size (in KB) of the memory buffer for caching insensitive result set data. If the size of the result set data exceeds the buffer size, the driver pages the result set data to disk. Because the result set data may be written to disk, the driver may have to reformat the data to write it correctly to disk. Specifying a buffer size that is a power of 2 results in more efficient memory use. The default is 2048 (KB).
LoginTimeout OPTIONAL | 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 of the primary database server that is listening for connections to the Sybase database. The default varies depending on operating system.
PrepareMethod OPTIONAL | {StoredProc | StoredProclfParam | Direct}. Determines whether stored procedures are created on the server for prepared statements. If set to StoredProc, a stored procedure is created when the statement is prepared and is executed when the prepared statement is executed. If set to StoredProcIfParam, a stored procedure is created only if the prepared statement contains one or multiple parameter markers. In this case, it is created when the statement is prepared and is executed when the prepared statement is executed. If the statement does not contain parameter markers, a stored procedure is not created and the statement is executed directly. If set to Direct, a stored procedure is not created for the prepared statement and the statement is executed directly. A stored procedure may be created if parameter metadata is requested. The default is StoredProclfParam. Setting this property to StoredProc or StoredProclfParam can improve performance if your application executes prepared statements multiple times because, once created, executing a stored procedure is faster than executing a single SQL statement. If a prepared statement is only executed once or is never executed, performance can decrease because creating a stored procedure incurs more overhead on the server than simply executing a single SQL statement. Setting this property to Direct should be used if your application does not execute prepared statements multiple times.
SelectMethod OPTIONAL | {Direct | Cursor}. A hint to the driver that determines whether the driver requests a database cursor for Select statements. Performance and behavior of the driver are affected by this property, which is defined as a hint because the driver may not always be able to satisfy the requested method. 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. A server-side database cursor is not created. Typically, 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. Typically, the Direct method performs better than the Cursor method. Cursor—When the driver uses the Cursor method, a server-side database cursor is requested. The rows are retrieved from the server in blocks when returning forward-only result sets. 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 | Specifies either the IP address or the server name (if your network supports named servers) of the primary database server. For example, 122.23.15.12 or SybaseServer.
User | The case-insensitive user name used to connect to your Sybase database. A user name is required only if security is enabled on your database. If so, contact your system administrator to get your user name.

# Performance Considerations

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

- BatchPerformanceWorkaround
- InsensitiveResultSetBufferSize
- MaxPooledStatements
- PrepareMethod
- ResultSetMetaDataOptions

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

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

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

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

# Data Types

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

Table 6-2 Sybase Data Types

Sybase Database | Sybase Data Type | JDBC Data Type
--- | --- | ---
Sybase 11.5 and higher | 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
varbinary | VARBINARY
varchar | VARCHAR
Sybase 12.5 and higher | date | DATE
time | TIME
unichar | CHAR
univarchar | VARCHAR

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.

# 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 retrieve 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 3.0-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. For more information about specifying connection properties, see Sybase Connection Properties on page 6-2.

# Parameter Metadata Support

The Sybase driver supports returning parameter metadata for all types of SQL statements.

# 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

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

How you retrieve the values of auto-generated keys depends on whether the Insert statement you are using 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 an `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`.

# 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 DataDirect Connect for JDBC drivers, which simplifies developing cross-database applications.

By default, Sybase does not evaluate `NULL` values in SQL equality (=) comparisons in an ANSI SQL-compliant manner. For example, the ANSI SQL specification defines that `col1=null` always evaluates to false. Using the default database setting (`ansinull=off`), if the value of `col1` in the following statement is `NULL`, the comparison evaluates to true instead of false:

>     SELECT * FROM table WHERE col1 = NULL

Setting `ansinull` to on changes the default database behavior so that SQL statements must use `IS NULL` instead of `=NULL`. For example, using the Sybase driver, if the value of `col1` in the following statement is `NULL`, the comparison evaluates to true:

>     SELECT * FROM table WHERE col1 IS NULL

To restore the default Sybase behavior for a connection, your application can 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 "Set Up the Sybase Server for XA Support" in Programming WebLogic JTA.