Cloudscape 3.6.4 Release Notes

April 27, 2001

Upgrade Information

Cloudscape automatically upgrades databases from versions prior to 3.6 to Cloudscape Version 3.6 databases. However, there is no automatic upgrade path from Cloudscape Version 3.6 Beta databases to Version 3.6 (GA) databases.

For complete instructions on how to upgrade a database, see the Cloudscape Developer's Guide.

What's New in Version 3.6.4?

ALTER TABLE Extension

You can now use the ALTER TABLE statement to change a nullable column to not allow nulls.

What's New in Version 3.6.1?

3.6.1 features are not described in the main product documentation. They are described here instead.

New Escape Syntax for Scalar Functions

Beginning with 3.6.1, Cloudscape is supporting JDBC's SQL escape syntax for several scalar functions. Support for this syntax is a J2EE requirement.

{fn concat (CharacterExpression, CharacterExpression) }

Character string formed by appending the second string to the first; if either string is null, the result is NULL.

{fn sqrt (floatExpression) }

Square root of number.The return type for SQRT is the type of the float input. Only the built-in types REAL, FLOAT, and DOUBLE PRECISION are supported.

{fn abs (NumericExpression) }

Absolute value of number. The return type of this function is the type of the number. The return type for ABS is the type of the number input. All built-in numeric types are supported (DECIMAL, NUMERIC, DOUBLE PRECISION, INTEGER, LONGINT, REAL, FLOAT, SMALLINT, and TINYINT).

{fn locate(CharacterExpression, CharacterExpression [, startIndex] ) }

Position in the second CharacterExpression of the first occurrence of the first CharacterExpression, searching from the beginning, unless startIndex is specified, in which case the search begins from position there; the index starts with 1. It returns 0 if the string is not found. The return type for LOCATE is an integer.

{fn substring(CharacterExpression, startIndex, length) }

A character string formed by extracting length characters from the CharacterExpression beginning at startIndex; the index starts with 1. The return type for SUBSTRING is string.

In addition, the following new functions are also valid without the escape syntax:

ABSOLUTE(number)

SQRT(float)

LOCATE(CharacterExpression, CharacterExpression [,startIndex] )

SUBSTRING(CharacterExpression, startIndex, length)

LIKE Escape Character

The percent sign (%) and underscore (_) are metacharacters within SQL-J LIKE clauses. Beginning with 3.6.1, you can escape these characters (so that they are interpreted literally) with the JDBC escape clause, which allows you to specify an escape character:

WHERE CharacterExpression [ NOT ] LIKE
    CharacterExpressionWithWildCard
    [ ESCAPE escapeCharacter ]

Some examples:

-- find all rows in which a begins with the character "%"
SELECT a FROM tabA WHERE a LIKE '\%%' {escape '\'}
-- find all rows in which a ends with the character "_"
SELECT a FROM tabA WHERE a LIKE '%\_' {escape '\'};

NOTE: ? is not permitted as an escape character if the statement contains a dynamic parameter (?).

What's New in Version 3.6?

New Features

NOTE: For detailed information on this list of new features in Version 3.6, see the 3.6 version of Getting Started with Cloudscape. (Users who localize their installation will not get a 3.6 version of the documentation; they will have a 3.5 version of the documentation instead. To read the 3.6 Getting Started with Cloudscape in English, see http://www.cloudscape.com/support/doc_36/doc/html/gsprti.htm.)

Cloudscape

Cloudview

ij

Changes to Cloudscape That Might Affect Existing Applications

Cloudscape and Platforms

Cloudscape is 100% pure Java and will run on any certified JVM. For a list of JVMs that have been tested with Cloudscape Version 3.6, look for information on http://www.cloudscape.com/support/TechInfo. You will also find some platform-specific information that may make it easier to get started with Cloudscape on some unusual platforms.

ODBC Driver Compatibility

Cloudscape Version 3.6 is compatible with Cloudscape ODBC driver version 2.0 build 194.

Known Limitations and Bug Fixes

Cloudview Limitations

Cloudview Is Behind On Features

Bug no. 3427

Cloudview does not provide an interface for creating schemas.

Hashtable Overflows on Cloudview on JDK 1.1.7 and 1.1.8 on All Platforms

Bug no. 1572

Cloudview running in jdk1.1.7 and jdk1.1.8 gets hashtable overflows after a short period, on all platforms. This is a JVM bug and has been resolved in JDK 1.2.

Cloudview Window Shows Only Limited Number of Characters for Import/Export Separator Character

Bug no. 3548

Cloudview window shows only a limited number of characters for the separator character for delimited import or export. However, you can choose your own separator character by blanking out the field and adding your own separator character, for example, "|".

You Cannot View Contents of All Columns of SYS.SYSCOLUMNS or SYS.SYSDEPENDS As a Client to RmiJdbc Server

Bug no. 3622

Some of the columns in SYS.SYSCOLUMNS and SYS.SYSDEPENDS cannot be sent across the wire from RmiJdbc Server because of the way these classes are implemented. That means that in a Cloudview client to RmiJdbc Server, you cannot select these tables and click the Data tab.

When writing an SQL statement to view information in SYS.SYSCOLUMNS, instead of selecting the columns themselves, execute one of the supported methods on the columns. For examples of how to query these tables, see the Cloudscape Reference Manual.

Database Limitations

Incorrect Date Sorting

Bug no. 1683

Dates that are before the year 1000 and greater than 199 will sort later than years in the range 1000-1999.

setAsciiStream() does not work on LONG VARBINARY columns

Bug no. 2878

setAsciiStream() does not work on LONG VARBINARY columns; use setBinaryStream() instead.

Cloudscape Should Transform Queries That Use LIKE Keyword to Compare a National Column to a Constant

Bug no. 3578

For performance reasons, Cloudscape should transform queries like this one:

SELECT * FROM mytable WHERE mynvarcharcolumn LIKE 'constant'

Cloudscape should transform the LIKE operator to = so that the query executes faster. Currently Cloudscape makes this transformation in cases in which the column is a regular text column but not in cases in which the column is a national string column (NCHAR, NVARCHAR, and the like).

Bugs Fixed in Cloudscape Version 3.6

Bug No.

Description

Fixed in Version

1826

Preparing a statement in autocommit mode should release locks on system tables when prepare is done.

3.6

2739

Cloudscape error messages should specify the name of a constraint if a constraint is violated.

3.6

3214

A newly created table causes lock on a system table if no rows are inserted before attempting to select from the table.

3.6

3383

Streaming column should not materialize entire column in memory.

3.6

3495

When a deferred insert occurs caused by a trigger and autocommit is false, a duplicate row could be inserted.

3.6

3093

On Windows systems, German and Spanish extended characters may display as garbage characters.

3.6.1

3561

Optimizer Chooses Bad Plan for Some Hash Joins.

3.6.1

3566

Optimizer Sometimes Chooses Bad Plan for Three-Way Join with Subquery

3.6.1

3570, 3643

DatabaseMetaData Functions getColumns, getPrimaryKeys, and getImportedKeys Are Slow

3.6.1

3662

com.ms.security.SecurityExceptionEx When Creating a Database Using NT Service

3.6.1

3627

Stored Prepared Statements Do Not Work with jview

3.6.1

3703

Unsuccessful Creation of DatabaseMetaData Stored Prepared Statements Can Cause Corruption in System Tables.

3.6.1

3581

A Transaction's State Should be Stored with XAResource, Not with Transaction

3.6.1

3295

Cloudscape Should Allow Multiple XaResources to Call Start on Same XID

3.6.1

Documentation Errata

ODBC Documentation Lists Wrong Version Number

Bug no. 3204

The ODBC documentation erroneously states that the version 2.0 Cloudscape ODBC Driver is compatible only with Cloudscape Version 2.0. It should say that the version 2.0 ODBC Driver is compatible with all versions of Cloudscape 2.0 or higher.

Do Not Change Encryption Provider After Creating an Encrypted Database

Bug no. 3511

Cloudscape allows you to specify the encryption provider and algorithm when you create an encrypted database. Do not attempt to boot the database with a different provider; you must continue to use the provider with which you originally encrypted the database.

This is the expected behavior, but this warning may not be explicit enough in the documentation.

More Instructions on How to Use Import/Export with Autoincrement Defaults

Bug no. 3547

Cloudview online help and the Cloudscape Tools and Utilities Guide note that you can now use import/export with columns that have autoincrement defaults; the Cloudscape Tools and Utilities Guide states: "You can preserve the values in the autoincrement default column by dropping the default before importing the data and then re-adding the default when you are done importing."

Here are some more explicit instructions on how to do this.

  1. Export the data the way you normally would.
  2. Drop the autoincrement default.You can do this from Cloudview by deselecting "Auto Increment" for the column on the table tab. From SQL, you can do this with a statement like this:

    ALTER TABLE mytable MODIFY myautoincrementcolumn DEFAULT NULL

  3. Re-import the data the way you normally would.
  4. Re-add the autoincrement default. You can do this from Cloudview by selecting "Auto Increment" for the column on the table tab. From SQL, you can do this with a statement like this:

    ALTER TABLE mytable MODIFY myautoincrementcolumn DEFAULT AUTOINCREMENT

Clarification to Tuning Cloudscape

Appendix A, "Internal Language Transformations" to Tuning Cloudscape describes an internal transformation for performance reasons made to SQL statements that contain a LIKE predicate in which a column is compared to a character string that begins with a character constant. The text should clarify that this transformation is not made in the case in which the column is a national character type (NCHAR, NVARCHAR, and the like).