Chapter 2 Programming Information

Handling restrictions, limitations, and deviations from JDBC standards

This section discusses restrictions and limitations that apply to jConnect, including how the jConnect implementation of JDBC deviates from the JDBC 1.x and 2.0 standards. The following topics are covered:

Making adjustments for multithreading

If several threads simultaneously call methods on the same Statement instance, CallableStatement, or PreparedStatement--which we do not recommend-- you have to 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 have to 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 (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( ) may result in an overflow exception.

Using COMPUTE statements

jConnect does not support computed rows. 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. Further, 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 5.2, 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();