Programming Information

Restrictions on and interpretations of JDBC standards

This section discusses how the jConnect implementation of JDBC deviates from the JDBC 1.x, 2.x, and 3.0 standards. The following topics are covered:

Using JDBC 3.0 method stubs

jConnect 6.x is compiled using JDK 1.4, which includes the JDBC 3.0 standard and all associated methods and interfaces. jConnect 6.05 has not yet implemented any of the JDBC 3.0 methods. This functionality is to be implemented incrementally in future EBF releases. An attempt to invoke a JDBC 3.0 method using jConnect 6.05 results in a SQLException indicating that the method has not yet been implemented. Check the Sybase Support Page at http://www.sybase.com/support for the latest EBF and software maintenance information.

Using Connection.isClosed and IS_CLOSED_TEST

According to section 11.1 of the JDBC 2.1 specification:"The Connection.isClosed method is only guaranteed to return "true" after Connection.close has been called. Connection.isClosed cannot be called, in general, to determine if a database connection is valid or invalid. A typical client can determine that a connection is invalid by catching the exception that is thrown when an operation is attempted."jConnect offers a default interpretation of the isClosed method that is different from the behavior that is defined in the spec. When you call Connection.isClosed, jConnect first verifies that Connection.close has been called on this connection. If close has been called, jConnect returns "true" for isClosed.However, if Connection.close has not been called, jConnect next tries to execute the sp_mda stored procedure on the database. The sp_mda stored procedure is part of the standard metadata that jConnect users must install when they use jConnect with a database.The purpose of calling sp_mda is so that jConnect can try to execute a procedure that is known (or at least, expected) to reside on the database server. If the stored procedure executes normally, then jConnect returns "false" for isClosed because we have verified that the database connection is valid and working. However, if the call to sp_mda results in a SQLException being thrown, jConnect catches the exception and returns "true" for isClosed because it appears that there is something wrong with the connection.If you intend to force jConnect to more closely follow the standard JDBC behavior for isClosed(), you can do so by setting the IS_CLOSED_TEST connection property to the special value "INTERNAL." The INTERNAL setting means that jConnect returns "true" for isClosed only when Connection.close has been called, or when jConnect has detected an IOException that has disabled the connection.You can also specify a query other than sp_mda to use when isClosed is called. For example, if you intend for jConnect to attempt a select 1 when isClosed is called, you can set the IS_CLOSED_TEST connection property to select 1.

Using Statement.close with unprocessed results

The JDBC specification is somewhat vague on how a driver should behave when you call Statement.execute and later call close on that same statement object without processing all of the results (update counts and ResultSets) returned by the Statement.For example, assume that there is a stored procedure on the database that does seven row inserts. An application then executes that stored procedure using a Statement.execute. In this case, a Sybase database returns seven update counts (one for each inserted row) to the application. In normal JDBC application logic, you would process those update counts in a loop using the getMoreResults, getResultSet and getUpdateCount methods. These are clearly explained on the java.sun.com Web site in the javadocs for the java.sql.* package.However, an application programmer might incorrectly choose to call Statement.close before reading through all of the returned update counts. In this case, jConnect sends a cancel to the database, which can have unexpected and unwanted side effects.In this particular example, if the application calls Statement.close before the database has completed the inserts, the database might not execute all of the inserts. It might stop, for example, after only five rows are inserted because the cancel is processed on the database before the stored procedure completes.The missing inserts would not be reported to you in this case. Future releases of jConnect may throw a SQLException when you try to close a Statement when there are still unprocessed results, but until then, jConnect programmers are strongly advised to adhere to the following guidelines:

When you call Statement.close, a cancel is sent to the server if not all the results (update counts and ResultSets) have been completely processed by you. In cases where you only executed select statements, this is fine. However, in cases where you executed insert/update/delete operations, this can result in not all of those operations completing as expected.
Therefore, you should never call close with unprocessed results when you have executed anything but pure select statements.
Instead, if you call Statement.execute, be sure your code processes all the results by using the getUpdateCount, getMoreResults and getResultSet methods.

Making adjustments for multithreading

If several threads simultaneously call methods on the same Statement instance, CallableStatement, or PreparedStatement--which Sybase does not recommend-- you must manually synchronize the calls to the methods on the Statement; jConnect does not do this automatically.

For example, if you have two threads operating on the same Statement instance--one thread sending a query and the other thread processing warnings--you must synchronize the calls to the methods on the Statement or conflicts may occur.

Using ResultSet.getCursorName

Some JDBC drivers generate a cursor name for any SQL query so that a string can always be returned. However, jConnect does not return a name when ResultSet.getCursorName is called, unless you either:

Called setFetchSize or setCursorName on the corresponding Statement, or
Set the SELECT_OPENS_CURSOR connection property to "true," and your query was in the form of SELECT... FOR UPDATE. For example:
```
select au_id  from authors for update
```

If you do not call setFetchSize or setCursorName on the corresponding Statement, or set the SELECT_OPENS_CURSOR connection property to "true," null is returned.

According to the JDBC 2.0 API (see Chapter 11, "Clarifications"), all other SQL statements do not need to open a cursor and return a name.

For more information on how to use cursors in jConnect, see "Using cursors with result sets".

Using setLong with large parameter values

Implementations of the PreparedStatement.setLong method set a parameter value to a SQL BIGINT datatype. Most Adaptive Server databases do not have an 8-byte BIGINT datatype. If a parameter value requires more than 4 bytes of a BIGINT, using setLong can result in an overflow exception.

New datatypes supported

jConnect supports the following new datatypes:

bigint - An exact numeric datatype designed to be used when the range of the existing int types is insufficient.
unsigned int - Unsigned versions of the exact numeric integer datatypes: unsignedsmallint, unsignedint, and unsignedbigint.
unitext - A variable-length datatype for Unicode characters.

Bigint datatype

Sybase supports bigint, which is a 64-bit integer datatype that is supported as a native ASE datatype. In Java, this datatype maps to java datatype long. To use this as a parameter, you can call PreparedStatement.setLong(int index, long value) and jConnect sends the data as bigint to ASE. When retrieving from a bigint column, you can use the ResultSet.getLong(int index) method.

Unitext datatypes

There are no API changes in jConnect for using the unitext datatype. jConnect can internally handle storage and retrieval of data from ASE when unitext columns are used.

Unsigned int datatypes

In this release, ASE has introduced unsigned bigint, unsigned int, and unsigned smallint as native ASE datatypes. Because, there are no corresponding unsigned datatypes in Java, you must set and get the next higher integer if you want to process the data correctly. For example, if you are retrieving data from an unsigned int, using the Java datatype int is too small to contain positive large values, and as a result, ResultSet.getInt (int index) might return incorrect data or throw an exception. To process the data correctly, you should get the next higher integer value ResultSet.getLong(). You can use the following table to set or get data.

ASE datatype	Java datatype
unsigned smallint	setInt(), getInt()
unsigned int	setLong(), getLong()
unsigned bigint	setBigDecimal(), getBigDecimal()

Using COMPUTE statements

jConnect does not support computed rows. In fact, results are automatically cancelled when a query contains a computed row. For example, the following statement is rejected:

SELECT name FROM sysobjects 
WHERE  type="S" COMPUTE COUNT(name)

To avoid this problem, substitute the following code:

SELECT name from sysobjects WHERE type="S"
SELECT  COUNT(name) from sysobjects WHERE type="S"

Executing stored procedures

If you execute a stored procedure in a CallableStatement object that represents parameter values as question marks, you get better performance than if you use both question marks and literal values for parameters. Also, if you mix literals and question marks, you cannot use output parameters with a stored procedure.

The following example creates sp_stmt as a CallableStatement object for executing the stored procedure MyProc:
```
CallableStatement  sp_stmt = conn.prepareCall( 
   "{call  MyProc(?,?)}");
```
The two parameters in MyProc are represented as question marks. You can register one or both of them as output parameters using the registerOutParameter methods in the CallableStatement interface.

In the following example, sp_stmt2 is a CallableStatement object for executing the stored procedure MyProc2.
```
CallableStatement  sp_stmt2 = conn.prepareCall( 
   {"call  MyProc2(?,'javelin')}");
```
In sp_stmt2, one parameter value is given as a literal value and the other as a question mark. You cannot register either parameter as an output parameter.

To execute stored procedures with RPC commands using name-binding for parameters, use either of the following procedures:

Use language commands, passing input parameters to them directly from Java variables using the PreparedStatement class. This is illustrated in the following code fragment:

// Prepare the statement
System.out.println("Preparing  the statement...");
String stmtString = "exec  " + procname + " @p3=?, @p1=?";
PreparedStatement  pstmt = con.preparedStatement(stmtString);
 
// Set  the values
pstmt.setString(1, "xyz");
pstmt.setInt(2,  123);
 
// Send the query
System.out.println("Executing  the query...");
ResultSet rs = pstmt.executeQuery();

With jConnect version 6.05 and later, use the com.sybase.jdbcx.SybCallableStatement interface, illustrated in this example:

import com.sybase.jdbcx.*;

....

// prepare  the call for the stored procedure to execute as an RPC

String execRPC = "{call " + procName + "  (?, ?)}";
SybCallableStatement scs = (SybCallableStatement)
con.prepareCall(execRPC);

// set  the values and name the parameters

// also  (optional) register for any output parameters
scs.setString(1,  "xyz");
scs.setParameterName(1, "@p3");
scs.setInt(2,  123);
scs.setParameterName(2, "@p1");

// execute  the RPC
// may also process the results  using getResultSet()
// and getMoreResults()

// see  the samples for more information on processing results
ResultSet  rs = scs.executeQuery();