Programming Information

Working with databases

This section discusses database issues relevant to jConnect and includes these topics:

Implementing high availability failover support

jConnect version 6.0 and later supports the failover feature available in Adaptive Server version 12.0 and later.

Note   Sybase failover in a high availability system is a different feature than "connection failover." Sybase strongly recommends that you read this section very carefully if you want to use both.

Overview

Sybase failover allows you to configure two version 12.0 or later Adaptive Servers as companions. If the primary companion fails, the devices, databases, and connections for that server can be taken over by the secondary companion.

You can configure a high availability system either asymmetrically or symmetrically:

In either setup, the two machines are configured for dual access, which makes the disks visible and accessible to both machines.You can enable failover in jConnect and connect a client application to an Adaptive Server configured for failover. If the primary server fails over to the secondary server, the client application also automatically switches to the second server and reestablishes network connections.

Note   Refer to Using Sybase Failover in High Availability Systems Manual for Adaptive Server for more detailed information.

Requirements, dependencies, and restrictions

Implementing failover in jConnect

Implement failover support in jConnect using one of the following two methods:

Logging in to the primary server

If an Adaptive Server is not configured for failover or cannot grant a failover session, the client cannot log in, and the following warning displays: 

'The server denied your request  to use the high-availability feature. Please reconfigure your database,  or do not request a high-availability session.'

Failing over to the secondary server

When failover occurs, the SQL exception JZ0F2 is thrown:

'Sybase high-availability failover has  occurred. The current transaction is aborted, but the connection  is still usable. Retry your transaction.'

The client then automatically reconnects to the secondary database using JNDI.

Note that:

Failing back to the primary server

At some point, the client fails back from the secondary server to the primary server. When failback occurs is determined by the System Administrator who issues sp_failback on the secondary server. Afterward, the client can expect the same behavior and results on the primary server as documented in "Failing over to the secondary server".

Performing server-to-server remote procedure calls

A Transact-SQL language command or stored procedure running on one server can execute a stored procedure located on another server. The server to which an application has connected logs in to the remote server, and executes a server-to-server remote procedure call.

An application can specify a "universal" password for server-to-server communication, that is, a password used in all server-to-server connections. Once the connection is open, the server uses this password to log in to any remote server. By default, jConnect uses the password of the current connection as the default password for server-to-server communications.

However, if the passwords are different on two servers for the same user, and that user is performing server-to-server remote procedure calls, the application must explicitly define passwords for each server it plans to use.

jConnect versions 4.1 and later include a property that enables you to set a universal "remote" password or different passwords on several servers. jConnect allows you to set and configure the property using the setRemotePassword method in the SybDriver class: 

Properties  connectionProps = new Properties();

public  final void setRemotePassword(String serverName, 
    String  password, Properties connectionProps

To use this method, the application must import the SybDriver class, then call the method:

import com.sybase.jdbcx.SybDriver;
SybDriver  sybDriver = (SybDriver)
    Class.forName("com.sybase.jdbc3.jdbc.SybDriver").newInstance();
sybDriver.setRemotePassword
    (serverName,  password, connectionProps);

Note   To set different remote passwords for various servers, repeat the preceding call (appropriate for your version of jConnect) for each server.

This call adds the given server name-password pair to the given Properties object, which can be passed by the application to DriverManager in DriverManager.getConnection (server_url, props).

If serverName is null, the universal password is set to password for subsequent connections to all servers except the ones specifically defined by previous calls to setRemotePassword.

When an application sets the REMOTEPWD property, jConnect no longer sets the default universal password.

Using wide table support for ASE 12.5 and later

Adaptive Server version 12.5 and later offers limits and parameters that are larger than in previous versions of the database server. For example:

To ensure that jConnect requests wide table support from the database, jConnect 6.0 and later users must make sure that JCONNECT_VERSION = 6 the default setting.

Note   jConnect continues to work with an Adaptive Server version 12.5 and later if you set the version to earlier than JCONNECT_VERSION = 6. However, if you try selecting from a table that requires wide table support to fully retrieve the data, you may encounter unexpected errors or data truncation. You can also set the JCONNECT_VERSION = 6 or later, when you access data from a Sybase server that does not support wide tables. In this case, the server simply ignores your request for wide table support.

In addition to the larger number of columns and parameters, wide table support offers an extra benefit for jConnect users--a greater amount of ResultSetMetaData. For example, in versions of jConnect earlier than 6.0, the ResultSetMetaData methods getCatalogName, getSchemaName, and getTableName all returned "Not Implemented" SQLExceptions because that metadata was not supplied by the server. When you enable wide table support, the server now sends back this information, and the three methods return useful information.

Accessing database metadata

To support JDBC DatabaseMetaData methods, Sybase provides a set of stored procedures that jConnect can call for metadata about a database. These stored procedures must be installed on the server for the JDBC metadata methods to work.

If the stored procedures for providing metadata are not already installed in a Sybase server, you can install them using stored procedure scripts provided with jConnect:

Note   The most recent version of these scripts is compatible with all versions of jConnect.

See the Sybase jConnect for JDBC Installation Guide and Release Bulletin for complete instructions on installing stored procedures.

In addition, to use the metadata methods, you must set the USE_METADATA connection property to "true" (its default value) when you establish a connection.

You cannot get metadata about temporary tables in a database.

Note   The DatabaseMetaData.getPrimaryKeys method finds primary keys declared in a table definition (CREATE TABLE) or with alter table (ALTER TABLE ADD CONSTRAINT). It does not find keys defined using sp_primarykey.

Server-side metadata installation

Metadata support can be implemented in either the client (ODBC, JDBC) or in the data source (server stored procedures). jConnect provides metadata support on the server, which results in the following benefits:

Using cursors with result sets

jConnect implements many JDBC 2.0 cursor and update methods. These methods make it easier to use cursors and to update rows in a table based on values in a result set.

In JDBC 2.0, ResultSets are characterized by their type and their concurrency. The type and concurrency values are part of the java.sql.ResultSet interface and are described in its javadocs.

Table 2-5 identifies the characteristics of java.sql.ResultSet that are available in jConnect 6.05. When requested, jConnect 6.05 opens server side scrollable cursors when the server is ASE 15.0 or later.

Table 2-5: java.sql.ResultSet options available in jConnect 6.05
Concurrency Type
TYPE_FORWARD_
ONLY 		TYPE_SCROLL_
INSENSITIVE 		TYPE_SCROLL_
SENSITIVE
CONCUR_READ_ONLY Supported Supported Not available
CONCUR_UPDATABLE Supported Not available Not available

This section includes the following topics:

Creating a cursor

There are two methods for creating a cursor using jConnect:

Another way you can create cursors is to specify the kind of ResultSet you want returned by the statement, using the following JDBC method on the connection: 

Statement createStatement(int resultSetType,  int resultSetConcurrency)throws SQL Exception

The type and concurrencies correspond to the types and concurrencies found on the ResultSet interface listed in Table 2-5. If you request an unsupported ResultSet, a SQL warning is chained to the connection. When the returned Statement is executed, you receive the kind of ResultSet that is most like the one you requested. See the JDBC specification for more details on the behavior of this method.

If you do not use createStatement, the default types of ResultSet are:

To verify that the kind of ResultSet object is what you intended, use the following two ResultSet methods: 

int getConcurrency() throws SQLException;
int getType() throws SQLException;

Steps Creating and using a cursor

  1. Create the cursor using Statement.setCursorName or SybStatement.setFetchSize.
  2. Invoke Statement.executeQuery to open the cursor for a statement and return a cursor result set.
  3. Invoke ResultSet.next to fetch rows and position the cursor in the result set.

    The following example uses each of the two methods for creating cursors and returning a result set. It also uses ResultSet.getCursorName to get the name of the cursor created by SybStatement.setFetchSize. 
    // With conn as a  Connection object, create a 
// Statement  object and assign it a cursor using 
// Statement.setCursorName().
Statement  stmt = conn.createStatement();
stmt.setCursorName("author_cursor");

// Use  the statement to execute a query and return
// a  cursor result set.
ResultSet rs = stmt.executeQuery("SELECT  au_id,
          au_lname,  au_fname FROM authors
      WHERE  city = 'Oakland'");
while(rs.next())
{
...
}
 
// Create  a second statement object and use
// SybStatement.setFetchSize()to  create a cursor
// that returns 10 rows  at a time. 
SybStatement syb_stmt = conn.createStatement();
syb_stmt.setFetchSize(10);
 
// Use  the syb_stmt to execute a query and return
// a  cursor result set.
SybCursorResultSet rs2 =
      (SybCursorResultSet)syb_stmt.executeQuery
      ("SELECT  au_id, au_lname, au_fname FROM authors
       WHERE  city = 'Pinole'");
while(rs2.next())
{
...
}
 
// Get  the name of the cursor created through the 
// setFetchSize()  method.
String cursor_name = rs2.getCursorName();
 ...
    // For jConnect 6.0, create  a third statement
// object using the  new method on Connection, 
// and obtain  a SCROLL_INSENSITIVE ResultSet.
// Note:  you no longer have to downcast the
// Statement  or the ResultSet.
    Statement  stmt = conn.createStatement(
                 ResultSet.TYPE_SCROLL_INSENSITIVE,
                 ResultSet.CONCUR_READ_ONLY);
    ResultSet rs3 = stmt.executeQuery
   ("SELECT  ... [whatever]");
    // Execute any of the JDBC  2.0 methods that 
// are valid for read  only ResultSets.
    rs3.next();
rs3.previous();
rs3.relative(3);
rs3.afterLast();
    ...

Using JDBC 1.x methods for positioned updates and deletes

The following example shows how to use methods in JDBC 1.x to do a positioned update. The example creates two Statement objects, one for selecting rows into a cursor result set, and the other for updating the database from rows in the result set.

Note   Although this manual provides sample code relating to JDBC 1.0 and 2.0 methods, Sybase strongly suggests that you use JDBC 2.0 for ease of use and portability.

// Create two statement objects  and create a cursor
// for the result  set returned by the first 
// statement,  stmt1. Use stmt1 to execute a query 
// and  return a cursor result set.
Statement stmt1 = conn.createStatement();
Statement  stmt2 = conn.createStatement();
stmt1.setCursorName("author_cursor");
ResultSet  rs = stmt1.executeQuery("SELECT
    au_id,au_lname,  au_fname
    FROM  authors WHERE city = 'Oakland'
    FOR  UPDATE OF au_lname");
 
// Get  the name of the cursor created for stmt1 so 
// that  it can be used with stmt2.
String cursor = rs.getCursorName();
 
// Use  stmt2 to update the database from the 
// result  set returned by stmt1.
String last_name = new  String("Smith");
while(rs.next())
{
    if  (rs.getString(1).equals("274-80-9391"))
     {
        stmt2.executeUpdate("UPDATE authors "+
       "SET  au_lname = "+last_name +
       "WHERE  CURRENT OF " + cursor);
    }
}

Deletions in a result set

The following example uses Statement object stmt2, from the preceding code, to perform a positioned deletion: 

stmt2.executeUpdate("DELETE FROM  authors
          WHERE CURRENT OF " + cursor);

Using JDBC 2.0 methods for positioned updates and deletes

This section discusses JDBC 2.0 methods for updating columns in the current cursor row and updating the database from the current cursor row in a result set. Each is followed by an example.

Updating columns in a result set

JDBC 2.0 specifies a number of methods for updating column values from a result set in memory, on the client. The updated values can then be used to perform an update, insert, or delete operation on the underlying database. All of these methods are implemented in the SybCursorResultSet class.

Examples of some of the JDBC 2.0 update methods available in jConnect are:

void updateAsciiStream(String columnName,  java.io.InputStream x, 
   int length)  throws SQLException;
void updateBoolean(int  columnIndex, boolean x) throws 
   SQLException;
void updateFloat(int columnIndex, float x) throws  SQLException;
void updateInt(String  columnName, int x) throws SQLException;
void updateInt(int columnIndex, int x) throws  SQLException;
void updateObject(String columnName, Object x)  throws 
   SQLException;

Methods for updating the database from a result set

JDBC 2.0 specifies two new methods for updating or deleting rows in the database, based on the current values in a result set. These methods are simpler in form than Statement.executeUpdate in JDBC 1.x and do not require a cursor name. They are implemented in SybCursorResultSet: 

void updateRow()  throws SQLException;
void deleteRow() throws SQLException;

Note   The concurrency of the result set must be CONCUR_UPDATABLE. Otherwise, the above methods raise an exception. For insertRow, all table columns that require non-null entries must be specified.

Methods provided on DatabaseMetaData dictate when these changes are visible.

Example

The following example creates a single Statement object that is used to return a cursor result set. For each row in the result set, column values are updated in memory and then the database is updated with the new column values for the row. 

// Create a Statement object  and set fetch size to 
// 25. This creates  a cursor for the Statement 
// object  Use the statement to return a cursor
// result  set.
SybStatement syb_stmt = 
(SybStatement)conn.createStatement();
syb_stmt.setFetchSize(25);
SybCursorResultSet  syb_rs = 
(SybCursorResultSet)syb_stmt.executeQuery(
    "SELECT * from  T1 WHERE ...")
 
// Update each  row in the result set according to
// code  in the following while loop. jConnect 
// fetches  25 rows at a time, until fewer than 25 
// rows  are left. Its last fetch takes any 
// remaining  rows.
while(syb_rs.next())
{
  // Update  columns 2 and 3 of each row, where 
// column  2 is a varchar in the database and 
// column  3 is an integer.
  syb_rs.updateString(2,  "xyz");
syb_rs.updateInt(3,100);
//Now,  update the row in the database.
  syb_rs.updateRow();
}
// Create a Statement object  using the
// JDBC 2.0 method implemented  in jConnect 6.0
Statement stmt = conn.createStatement
(ResultSet.TYPE_FORWARD_ONLY,  ResultSet.CONCUR_UPDATABLE);
// In jConnect 6.0, downcasting  to SybCursorResultSet is not
// necessary.  Update each row in the ResultSet in the same
// manner  as above
while (rs.next())
{
rs.updateString(2, "xyz");
rs.updateInt(3,100);
  rs.updateRow();
// Use the Statement to return  an updatable ResultSet
ResultSet rs = stmt.executeQuery("SELECT * FROM  T1 WHERE...");
}

Deleting a row from a ResultSet

To delete a row from a cursor result set, you can use SybCursorResultSet.deleteRow as follows: 

 while(syb_rs.next())
 {
     int  col3 = getInt(3);
     if  (col3 >100)
     {
     syb_rs.deleteRow();
     }
 }

Inserting a row into a ResultSet

The following example illustrates how to do inserts using the JDBC 2.0 API. There is no need to downcast to a SybCursorResultSet. 

// prepare to insert
rs.moveToInsertRow();
// populate new row with column  values
rs.updateString(1, "New entry for col 1");
rs.updateInt(2,  42);
// insert  new row into db
rs.insertRow();
// return to current row in  result set
rs.moveToCurrentRow();

Using a cursor with a PreparedStatement object

Once you create a PreparedStatement object, you can use it multiple times with the same or different values for its input parameters. If you use a cursor with a PreparedStatement object, you must close the cursor after each use and then reopen the cursor to use it again. A cursor is closed when you close its result set (ResultSet.close). It is opened when you execute its prepared statement (PreparedStatement.executeQuery).

The following example shows how to create a PreparedStatement object, assign it a cursor, and execute the PreparedStatement object twice, closing and then reopening the cursor. 

// Create  a prepared statement object with a 
// parameterized  query.
PreparedStatement prep_stmt =
conn.prepareStatement(
"SELECT  au_id, au_lname, au_fname "+
"FROM  authors WHERE city = ? "+
"FOR UPDATE  OF au_lname");
 
//Create  a cursor for the statement.
prep_stmt.setCursorName("author_cursor");
 
// Assign  the parameter in the query a value. 
// Execute  the prepared statement to return a 
// result  set.
prep_stmt.setString(1, "Oakland");
ResultSet  rs = prep_stmt.executeQuery();
 
//Do  some processing on the result set.
while(rs.next())
{
    ...
}
 
// Close  the result, which also closes the cursor.
rs.close();
 
// Execute  the prepared statement again with a new 
// parameter value. 
prep_stmt.setString(1,"San  Francisco");
rs = prep_stmt.executeQuery();
// reopens cursor

Using TYPE_SCROLL_INSENSITIVE result sets in jConnect

jConnect supports only TYPE_SCROLL_INSENSITIVE result sets.

jConnect uses the Tabular Data Stream (TDS)--the Sybase proprietary protocol--to communicate with Sybase database servers. ASE 15.0 or later supports TDS scrollable cursors. For servers that do not support TDS scrollable cursors, jConnect caches the row data on demand, on the client, on each call to ResultSet.next. However, when the end of the result set is reached, the entire result set is stored in the client memory. Because this may cause a performance strain, Sybase recommends that you use TYPE_SCROLL_INSENSITIVE result sets only with ASE 15.0 or when the result set is reasonably small.

Note   When you use TYPE_SCROLL_INSENSITIVE ResultSets in jConnect, and the server does not support TDS scrollable cursors, you can only call the isLast method after the last row of the ResultSet has been read. Calling isLast before the last row is reached causes an UnimplementedOperationException to be thrown.

jConnect provides the ExtendResultSet in the sample2 directory; this sample provides a limited TYPE_SCROLL_INSENSITIVE ResultSet using JDBC 1.0 interfaces.

This implementation uses standard JDBC 1.0 methods to produce a scroll-insensitive, read-only result set, that is, a static view of the underlying data that is not sensitive to changes made while the result set is open. ExtendedResultSet caches all of the ResultSet rows on the client. Be cautious when you use this class with large result sets.

The sample.ScrollableResultSet interface:

The methods from the JDBC 2.0 API are:

boolean previous() throws SQLException;
boolean  absolute(int row) throws SQLException;
boolean relative(int  rows) throws SQLException;
boolean first()  throws SQLException;
boolean last() throws SQLException;
void  beforeFirst() throws SQLException;
void afterLast() throws  SQLException;
boolean isFirst() throws SQLException;
boolean  isLast() throws SQLException;
boolean isBeforeFirst() throws  SQLException;
boolean isAfterLast() throws SQLException;
int  getFetchSize() throws SQLException;
void setFetchSize(int  rows) throws SQLException;
int getFetchDirection() throws  SQLException;
void setFetchDirection(int direction) throws SQLException;
int  getType() throws SQLException;
int getConcurrency() throws  SQLException;
int getRow() throws SQLException;

To use the new sample classes, create an ExtendedResultSet using any JDBC 1.0 java.sql.ResultSet. Below are the relevant pieces of code (assume a Java 1.1 environment): 

// import  the sample files
import sample.*;
//import  the JDBC 1.0 classes
import java.sql.*;
// connect  to some db using some driver;
// create  a statement and a query;
// Get  a reference to a JDBC 1.0 ResultSet
ResultSet rs = stmt.executeQuery(_query);
// Create  a ScrollableResultSet with it
ScrollableResultSet srs = new  ExtendedResultSet(rs);
// invoke  methods from the JDBC 2.0 API
srs.beforeFirst();
// or  invoke methods from the JDBC 1.0 API
if (srs.next())
  String  column1 = srs.getString(1);

Figure 2-1 is a class diagram that shows the relationships between the new sample classes and the JDBC API.

Figure 2-1: Class diagram

See the JDBC 2.0 API at http://java.sun.com/products/jdbc/jdbcse2.html for more details.

Support for batch updates

Batch updates allow a Statement object to submit multiple update commands as one unit (batch) to an underlying database for processing together.

Note   To use batch updates, you must install the latest metadata scripts provided in the sp directory under your jConnect installation directory.

See BatchUpdates.java in the sample2 subdirectories for an example of using batch updates with Statement, PreparedStatement, and CallableStatement.

jConnect also supports dynamic PreparedStatements in batch.

Implementation notes

jConnect implements batch updates as specified in the JDBC 2.0 API, except as described here:

See Sun Microsystems, Inc. JDBC 2.0 API for more details on batch updates.

Updating a database from a result set of a stored procedure

jConnect includes update and delete methods that allow you to get a cursor on the result set returned by a stored procedure. You can then use the position of the cursor to update or delete rows in the underlying table that provided the result set. The methods are in SybCursorResultSet: 

void  updateRow(String tableName) throws SQLException;
void  deleteRow(String tableName) throws SQLException;

The tableName parameter identifies the database table that provided the result set.

To get a cursor on the result set returned by a stored procedure, you need to use either SybCallableStatement.setCursorName or SybCallableStatement.setFetchSize before you execute the callable statement that contains the procedure. The following example shows how to create a cursor on the result set of a stored procedure, update values in the result set, and then update the underlying table using the SybCursorResultSet.update method: 

// Create a CallableStatement  object for executing the stored 
// procedure. 
CallableStatement  sproc_stmt = 
   conn.prepareCall("{call  update_titles}");
 
// Set  the number of rows to be returned from the database with
// each  fetch. This creates a cursor on the result set.
(SybCallableStatement)sproc_stmt.setFetchSize(10);
 
//Execute  the stored procedure and get a result set from it.
SybCursorResultSet  sproc_result = (SybCursorResultSet) 
   sproc_stmt.executeQuery();
 
// Move  through the result set row by row, updating values in the
// cursor's  current row and updating the underlying titles table
// with  the modified row values. 
while(sproc_result.next())
{
   sproc_result.updateString(...);
   sproc_result.updateInt(...);
   ...
   sproc_result.updateRow(titles);
}

Working with datatypes

This section documents use of numeric, image, text, date, time, and char data.

Sending numeric data

jConnect has added the SybPreparedStatement extension to support the way Adaptive Server handles the NUMERIC datatype where precision (total digits) and scale (digits after the decimal) can be specified.The corresponding datatype in Java--java.math.BigDecimal--is slightly different, and these differences can cause problems when jConnect applications use the setBigDecimal method to control values of an input/output parameter. Specifically, there are cases where the precision and scale of the parameter must precisely match that precision and scale of the corresponding SQL object, whether it is a stored procedure parameter or a column.To give jConnect applications more control over the setBigDecimal method, the SybPreparedStatement extension has been added with this method: 

public  void setBigDecimal (int parameterIndex, BigDecimal X, int scale, 
  int  precision) throws SQLException

See the SybPrepExtension.java sample in the /sample2 subdirectories under your jConnect installation directory for more information.

Updating image data in the database

jConnect has a TextPointer class with sendData methods for updating an image column in an Adaptive Server or Adaptive Server Anywhere database. In earlier versions of jConnect, you had to send image data using the setBinaryStream method in java.sql.PreparedStatement. Now the TextPointer.sendData methods use java.io.InputStream and greatly improve performance when you send image data to an Adaptive Server database.

WARNING! The TextPointer class has been deprecated, that is, it is no longer recommended and may cease to exist in a future version of jConnect.If your data server is Adaptive Server version 12.5 or later or Adaptive Server Anywhere version 6.05 or later, use the standard JDBC form to send image data: 
PreparedStatement.setBinaryStream(int  paramIndex,
  InputStream  image)

To obtain instances of the TextPointer class, you can use either of two getTextPtr methods in SybResultSet:

Public methods in the TextPointer class

The com.sybase.jdbcx package contains the TextPointer class. Its public method interface is: 

public void sendData(InputStream  is, boolean log) 
   throws SQLException
public  void sendData(InputStream is, int length,
   boolean  log) throws SQLException
public void sendData(InputStream  is, int offset, 
   int length, boolean  log) throws SQLException
public void sendData(byte[] byteInput,  int offset, 
   int length, boolean log)  throws SQLEXception

where:

Steps Updating an image column with TextPointer.sendData

To update a column with image data:

  1. Get a TextPointer object for the row and column that you want to update.
  2. Use TextPointer.sendData to execute the update.

The next two sections illustrate these steps with an example. In the example, image data from the file Anne_Ringer.gif is sent to update the pic column of the au_pix table in the pubs2 database. The update is for the row with author ID 899-46-2035.


Getting a TextPointer object

text and image columns contain timestamp and page-location information that is separate from their text and image data. When data is selected from a text or image column, this extra information is "hidden" as part of the result set.

A TextPointer object for updating an image column requires this hidden information but does not need the image portion of the column data. To get this information, you need to select the column into a ResultSet object and then use SybResultSet.getTextPtr, which extracts text-pointer information, ignores image data, and creates a TextPointer object. See the following code for an example.

When a column contains a significant amount of image data, selecting the column for one or more rows and waiting to get all the data is likely to be inefficient, since the data is not used. To shortcut this process, use the set textsize command to minimize the amount of data returned in a packet. The following code example for getting a TextPointer object includes the use of set textsize for this purpose.

/*
 * Define  a string for selecting pic column data for author ID 
 * 899-46-2035.
 */
 String  getColumnData = "select pic from au_pix where  au_id = '899-46-2035'";
 
 /*
 * Use  set textsize to return only a single byte of column data
 * to  a Statement object. The packet with the column data will
 * contain  the "hidden" information necessary for creating a
 * TextPointer  object.
 */
 Statement stmt= connection.createStatement();
 stmt.executeUpdate("set  textsize 1");
 
 /*
 * Select  the column data into a ResultSet object--cast the 
 * ResultSet  to SybResultSet because the getTextPtr method is 
 * in  SybResultSet, which extends ResultSet.
 */
 SybResultSet  rs = (SybResultSet)stmt.executeQuery(getColumnData);
 
 /*
 * Position  the result set cursor on the returned column data 
 * and  create the desired TextPointer object.
 */
 rs.next();
 TextPointer  tp = rs.getTextPtr("pic");
 
 /* 
 * Now,  assuming we are only updating one row, and won't need
 * the  minimum textsize set for the next return from the server,
 * we  reset textsize to its default value.
 */
 stmt.executeUpdate("set  textsize 0");

Executing the
update with TextPointer.sendData

The following code uses the TextPointer object from the preceding section to update the pic column with image data in the file Anne_Ringer.gif. 

/*
 *First,  define an input stream for the file.
 */
 FileInputStream  in = new FileInputStream("Anne_Ringer.gif");
 
 /*
 * Prepare  to send the input stream without logging the image data 
 * in  the transaction log.
 */
 boolean  log = false;
 
 /*
 * Send  the image data in Anne_Ringer.gif to update the pic 
 * column  for author ID 899-46-2035.
 */
 tp.sendData(in,  log);

See the TextPointers.java sample in the sample2 subdirectories under your jConnect installation directory for more information.

Using text data

In earlier versions, jConnect used a TextPointer class with sendData methods for updating a text column in an Adaptive Server or Adaptive Server Anywhere database.

The TextPointer class has been deprecated, that is, it is no longer recommended and may cease to exist in a future version of Java.

If your data server is Adaptive Server 12.5 or later or Adaptive Server Anywhere version 6.05 or later, use the standard JDBC form to send text data:

PreparedStatement.setAsciiStream(int  paramIndex,
  InputStream  text, int length)

or

PreparedStatement.setUnicodeStream(int  paramIndex,
  InputStream  text, int length)

or

PreparedStatement.setCharacterStream(int  paramIndex,
  Reader  reader, int length)

Using date and time datatypes

Adaptive Server versions 12.5.1 and later offer support for the SQL date and time datatypes. Previously, Adaptive Server offered only support for the datetime and smalldatetime datatypes. These datatypes were limited for the following reasons:

The addition of the date and time datatypes provides the following advantages:

To use the date and time datatypes with jConnect 6.0, make sure your JCONNECT_VERSION property is set to "6" or later.

Implementation notes

Using char/varchar/text datatypes and getByte

Do not use rs.getByte on a char, univarchar, unichar, varchar, or text field unless the data is hex, octal, or decimal.

 

Copyright (C) 2005. Sybase Inc. All rights reserved.