Java Sybase Jdbc Connection Example

WebLogic Type 4 JDBC Drivers

The Sybase Driver

Note: 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
• 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 connection pool 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 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. The data type listed for each connection property is the Java data type used for the property value in a JDBC connection pool.

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.

 

---

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-4 lists configuration attributes for a sample WebLogic Server connection pool that uses the XA version of the driver.

Table 6-2 Connection Pool Attributes Using the Non- XA WebLogic Type 4 Sybase JDBC 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

Table 6-3 Connection Pool Attributes Using the XA WebLogic Type 4 Sybase JDBC Driver

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

---

Table 6-4 Connection Pool Attributes Using the XA WebLogic Type 4 Sybase JDBC Driver

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

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

bearuther1997.blogspot.com

Source: https://docs.oracle.com/cd/E13222_01/wls/docs81/jdbc_drivers/sybase.html

Share this post