Home

	Expand Search

jConnect for JDBC Programmer's Reference

Chapter 2 Programming Information

Working with databases

Working with datatypes

Working with datatypes

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

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. 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.0 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 TextPointer getTextPtr(String columnName)

public TextPointer getTextPtr(int columnIndex)

Public methods in the TextPointer class

The com.sybase.jdbc 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

sendData(InputStream is, boolean log) - Updates an image column with data in the specified input stream.

sendData(InputStream is, int length, boolean log) - updates an image column with data in the specified input stream. length is the number of bytes being sent.

sendData(InputStream is, int offset, int length, boolean log) - updates an image column with data in the specified input stream, starting at the byte offset given in the offset parameter and continuing for the number of bytes specified in the length parameter.

sendData(byte[ ] byteInput, int offset, int length, boolean log) - updates a column with image data contained in the byte array specified in the byteInput parameter. The update starts at the byte offset given in the offset parameter and continues for the number of bytes specified in the length parameter.

Each method has a log parameter. The log parameter specifies whether image data is to be fully logged in the database transaction log. If the log parameter is set to true, the entire binary image is written into the transaction log. If the log parameter is set to false, the update is logged, but the image itself is not included in the log.

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( ) (see the example that follows the next paragraph). SybResultSet.getTextPtr( ) extracts text-pointer information, ignores image data, and creates a TextPointer object.

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. You can shortcut this process by using 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.

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.0 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)

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:

• There was not a way to have separate time and date datatypes.

• Dates prior to 1/1/1753 were illegal. Datetime values could hold only dates between 1/1/1753 and 12/31/9999.

• When JDBC clients used the setTime and setDate methods to insert java.sql.Time or java.sql.Date methods, the values were converted to datetime datatypes in the server. This conversion resulted in the addition of a default date or time to the insert value supplied by the client.

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

• Date values can now be between Jan. 1, 0001 and Dec. 31, 9999, exactly matching the allowable values in java.sql.Date.

• A direct mapping now exists between java.sql.Date and the date dataype, as well as between java.sql.Time and the time datatype.

The date and time datatypes can be used only with jConnect 5.5 and later. To use them, a jConnect client must set the driver version to 6. To do so, the client can use the SybDriver.setVersion() method, or use the JCONNECT_VERSION connection property.

Implementation notes

• You will not have to change any of the application code if you decide to use the date and time datatypes by setting the version to 6.

• If you select from a table that contains a date or time column, and you have not enabled date/time support in jConnect (by setting the version), the server will try to convert the date or time to a datetime value before returning it. This can cause problems if the date to be returned is prior to 1/1/1753. In that case, a conversion error will occur, and the database will inform you of the error.

• Adaptive Server Anywhere supports a date and time datatype, but the date and time datatypes are not yet directly compatible with those in Adaptive Server version 12.5.1 and later. Using jConnect, you should continue to use the datetime and smalldatetime datatypes when communicating with Adaptive Server Anywhere.

• The maximum value in a datetime column in Adaptive Server Anywhere is 1-1-7911 00:00:00.

• Using jConnect you will receive conversion errors if you attempt to insert dates prior to 1/1/1753 into datetime columns or parameters

• Refer to the Adaptive Server manuals for more information on the date and time datatypes; of special note is the section on allowable implicit conversions.

• If you use getObject( ) with an Adaptive Server date, time, or datetime column, the value returned will be, respectively, a java.sql.Date, java.sql.Time, or java.sql.Timestamp datatype.

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