Sybase数据库技术，数据库恢复专家

For EXECUTE IMMEDIATE, the new syntax is:

EXECUTE營MMEDIATE燵爀xecute-option燷爏tring-expression

execute-option:

牋WITH燪UOTES燵燨N爘燨FF燷

|燱ITH燛SCAPES爗燨N爘燨FF爙

| WITH RESULT SET { ON | OFF }

Now, if an EXECUTE IMMEDIATE statement is intended to be used to return a result set, it must be specified as follows:

EXECUTE IMMEDIATE WITH RESULT SET ON string-expression

If the "WITH RESULT SET ON" clause is included, the containing procedure is marked as returning a result set. Failure to include this clause will result in an error being reported when the procedure is called if the statement does produce a result set.

Note, these procedures now return additional columns, as described below.

sa_procedure_profile now has two additional result set columns:

foreign_owner char(128) // owner of foreign table for system triggers

foreign_table char(128) // name of foreign table for system triggers

As well, the object_type column can now have two additional values:

C - indicates that the object is an ON UPDATE system trigger

D - indicates that the object is an ON DELETE system trigger

If object_type is C or D, object_name is the name of the foreign key for which this system trigger was defined.

sa_procedure_profile_summary has two additional result set columns (same as above):

foreign_owner char(128) // owner of foreign table for system triggers

foreign_table char(128) // name of foreign table for system triggers

The object_type parameter to sa_procedure_profile_summary can now have the value 'S'. If this value is used, only rows corresponding to system triggers are returned.

- The column value in the first row must have been NULL,

- A subsequent row must have contained a string value that did not exceed the maximum size allowed for the column,

These must be then been followed at some point by a row containing a column value that needed to be truncated before insertion. This problem has now been fixed and a new server assertion ( number 100910 ) has been added to prevent this corruption from happening in the future.

This problem has been fixed, SQLGetDiagField() now returns the rowset number of the row with an error.

function or event.

Similarly, the system function Property_description() was returning th property name as the description. This function was being used by Sybase Central, so it would have shown the property name instead of a description.

Proper descriptions have now been added for all properties.

- ODBC client was being used

- the application was using the "widechar" ODBC interface

- the application did not explicitly set the character set for the connection

- the database character set was not equal to the OS character set of the server

- the client side was capable of translating from database character set to the client's character set

This has now been fixed.

A workaround is to manually issue the 'CREATE VIRTUAL INDEX' statement and then view the plan from dbisql using Shift+F5.

- the server may have crashed when using an aliased expression, subselect or subquery predicate

- an incorrect 'column not found' error when using a view column on the right-side of a SET item

- the wrong table instance may have been updated, when a table was referenced more than once

- no error returned when no row was updated

The exact behaviour of a positioned update was changed between versions 5.5 and version 6.0 and between 7.0 and 8.0 and later. Until now, none of these versions provided the correct semantics. This has been fixed.

Documentation Change:

The following change should be applied to the "UPDATE (positioned) statement [ESQL] [SP]" topic. Note that this describes only Syntax 2

UPDATE update-table, ...

SET set-item, ...

WHERE CURRENT OF cursor-name

update-table :

[owner-name.]table-or-view-name [[AS] correlation-name]

set-item :

[correlation-name.]column-name = expression

| [owner-name.]table-or-view-name.column-name = expression

Each update-table is matched to a table in the query for the cursor as follows:

If a correlation name is used in the update-table, it is matched to a table in the cursor's query that has the same table-or-view-name and the same correlation-name

Otherwise, if there is a table in the cursor's query that has the same table-or-view-name that does not have a correlation name specified or has a correlation name that is the same as the table-or-view-name, then the update table is matched wit this table in the cursor's query

Otherwise, if there is a single table in the cursor's query that has the same table-or-view-name as the update table, then the update table is matched with this table in the cursor's query.

Otherwise, an error is returned.

Each set-item is associated with a single update-table, and the corresponding column of the matching table in the cursor's query is modified.

The expression on the right-hand side of each set-item can refer to columns of the tables identified in the UPDATE list; they may not refer to aliases of expressions from the cursor's query, nor may they refer to columns of other tables of the cursor's query which do not appear in the UPDATE list. Subselects, subquery predicates, and aggregate expressions can not be used in the set-items.

- The subquery was a single-row group-by subquery computing the LIST() function (in the HAVING clause or the SELECT list).

- The subquery was a single-row group-by without a HAVING clause, and the subquery was used in an EXISTS() or NOT EXISTS() predicate.

- The subquery was a grouped subquery and the correlated expressions were used in equality predicates and had different domains than the expressions being equated.

These problem have now been fixed.

BytesReceivedUncomp,

PacketsReceivedUncomp,

BytesSent,

BytesSentUncomp,

PacketsSent,

PacketSentUncomp

These statistics are now updated on HTTP requests.

The Event source could have been "ASA", the server name, or the service name. The error or information message would have been something like:

The description for Event ID ( 1 ) in Source ( ASA ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event:

This message is now better formatted. The Event source is "ASA 9.0", and the message may be prefixed by the server name, or service name. The previous text will no longer be displayed.

Note, when deploying ASA servers to Windows NT, 2000. XP or 2003, the following registry key should be added so that Event Log messages are formatted correctly:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application\ASA 9.0

and within this key, the REG_SZ value name EventMessageFile and value data <path>\dblgen9.dll. (dblgen9.dll can be used regardless of the language being used).

Now, dbstop with the -y parameter, but without the -c parameter, will stop a server after a fatal error, as long as the server was started with the -gk command line option set to "all", (this is the default for the personal server). If the -y parameter is not used, dbstop will display the fatal error and prompt if the server should be stopped.

As well, if the opbject contained more than 100 such MBCS characters with 0x7b or 0x7d as follow-bytes, dbunload could have crashed.

These problems have been fixed.

Now, a negative Length parameter means "return at most nnn <characters | bytes> up to and including the start position". If Start is zero and Length is non-negative, a start value of 1 is used. If Start is zero and Length is negative, a start value of -1 is used.

For example:

select substr('abcdefgh',2,-1) ==> b

select substr('abcdefgh',2,-2) ==> ab

select substr('abcdefgh',2,-3) ==> ab

select substr('abcdefgh',2,-4) ==> ab

select substr('abcdefgh',0,2) ==> ab

select substr('abcdefgh',-2,-3) ==> efg

select substr('abcdefgh',-2,2) ==> gh

select substr('abcdefgh',0,-2) ==> gh

time_low unsigned long

time_mid unsigned short

time_hi_and_version unsigned short

clock_seq_hi_and_reserved unsigned small

clock_seq_low unsigned small

node character(6)

The server stores the unsigned long and unsigned short fields in big-endian byte order, where as the .NET structure stores the unsigned long and unsigned short fields in little-endian byte order.

This requires the byte order of the unsigned long and unsign short fields to be changed when inserting and retrieving UUID values, which was not being done. This problem has been fixed.

The new columns for the two procedures are:

TableId: table id of the table the index is on

IndexId: index id of the new index

= 0 for Primary Keys,

= SYSFOREIGNKEY.foreign_key_id for Foreign Keys

= SYSINDEX.index_id for all other indexes

IndexType: the type of the index

= "PKEY" for primary keys

= "FKEY" for foreign keys

= "UI" for unique indexes

= "UC" for unique constraints

= "NUI" for non unique indexes

The procedures will continue to behave as before for old databases. It will also be possible to revert to the old behaviour on newly created and/or updated databases by simply dropping the corresponding stored procedure and recreating it with the old result set.

The new columns provide more information and also make the result set much more useful for the purposes of joining with other catalog tables.

For more details see the Microsoft document Q117428. This problem has been fixed by renaming the file from main.c to main.cpp.

A workaround is to delete the 'DROP INDEX' statement in the generated script for system tables.

As a workaround, the database option Max_plans_cached can be set to 0 to get correct behaviour, although this may negatively impact performance.

Below is a sample C++ call to CoCreateInstance to initialize the ASA OLEDB provider.

CLSIDFromProgID(T2COLE(_T("ASAProv")), &clsid);

hr = CoCreateInstance( clsid, NULL,

CLSCTX_INPROC_SERVER,

IID_IDBInitialize,

(void**)&m_pIDBInitialize);

This problem is likely to occur on a Pocket PC 2003 device. The Pocket PC 2003 SDK (eg, \WinCE Tools\wce420\Pocket PC 2003) does not include the Microsoft Data Access 3.1 modules (like msdaer.dll, msdaeren.dll) that are present in older SDKs like the Pocket PC 2002 SDK (e.g., \WinCE Tools\wce300\Pocket PC 2002\dataaccess31\target\arm).

To help diagnose the problem, the ASA OLEDB provider has been changed to display a message box when it cannot access the Microsoft OLE DB Error Collection Service module.

The title of the message box is "Fatal Error".

The interior text of the message box will state something like "CoGetClassObject(CLSID_EXTENDEDERRORINFO) 0x8007007e ProgID: MSDAER.1". The hexadecimal number is the error code returned by CoGetClassObject(). In this example, the programmatic identifier associated with CLSID_EXTENDEDERRORINFO is "MSDAER.1".

The settings for CLSID_EXTENDEDERRORINFO can be examined in the system registry under HKEY_CLASSES_ROOT\CLSID\{C8B522CF-5CF3-11CE-ADE5-00AA0044773D}. Included is the path for the Microsoft OLE DB Error Collection Service module. This information can then be used to help determine if the required module is present on the system.

For example, the following query was affected:

select emp_id from employee key right outer join sales_order group by emp_id

This has been fixed.

For example:

WITH V AS ( SELECT dept_id, CASE dept_id WHEN 100 THEN 'A' ELSE 'B' END FROM department )

SELECT *

FROM rowgenerator R left outer join V on R.row_num = V.dept_id

WHERE R.row_num IN (99,100)

would have returned 'B' incorrectly instead of NULL for row 99. For the problem to have occurred, a branch of the CASE expression must have returned a non-NULL value when all of the columns from the underlying table were NULL. This problem did not affect Syntax 2 CASE expressions.

Similarly, if a column of a view was a string concatenation of the form " T.x || 'string' ", the resulting column would have been non-NULL, when it should be NULL.

These two problems have been fixed.

The crash would have occurred if the grouped query block was an immediate child of a set expression query, the GROUP BY clause contained a correlated subselect and the outer reference of the subselect was an alias defined in the grouped query

For example:

SELECT file_id A1

FROM SYSTABLE T

GROUP BY file_id, ( select row_num from rowgenerator R where R.row_num=A1 )

UNION ALL

SELECT 1

FROM DUMMY

A new upper bound of 15 sec is now imposed on the -i polling interval for UDP. So specifying an interval value higher than 15, it will be treated as 15 seconds internally.

Previously, the cache compared arguments for equality using the database comparison rules. For example, in a case-insensitive database, argument 'x' was compared equal to 'X'. This could lead to different answers than expected because the two strings are not identical. Now, arguments to the expression cache are only considered equal if they are identical.

columns in its WHERE clause in a way that the result set could not have had more than one row, then the estimated row count could still have been greater than 1. This has been fixed. Note, estimated row counts are always return as negative numbers.

For example, the second fetch should fail with a "cursor not open" error, but does not:

BEGIN

DECLARE c_fetch CURSOR FOR SELECT 1;

SET TEMPORARY OPTION CLOSE_ON_ENDTRANS = 'ON';

OPEN c_fetch;

FETCH c_fetch;

COMMIT;

FETCH c_fetch;

END;

As well, if the database option "Ansi_close_cursors_on_rollback" was set to ON, then the server could have crashed in some situations where a rollback occurred inside a call to a user defined function.

For example, the following would have caused a server crash:

create function DBA.foo()

returns int

begin

grant select on systable to non_existent_user;

return 1

end

create procedure bar()

begin

set temporary option ANSI_CLOSE_CURSORS_ON_ROLLBACK = 'on';

select foo()

end ;

select * from bar()

Both of these problems have now been resolved.

For example, the following query could have generated the failure:

create procedure P_Blob2( @x int )

begin

select @x || repeat('long string',1000) x

from rowgenerator

where row_num = @x

end

select x, sum(row_num)

from (

select row_num, DT.x

from rowgenerator R, lateral( P_Blob2(R.row_num) ) DT

union all

select 1, '' ) DT2

group by x

This has now been fixed.

This problem has been fixed by adding a two second wait for the shutdown event. If this two second timeout is reached, the message "The TCP listener failed to shutdown cleanly within the timeout period" will appear in the server's output log, likely more than once and with more than one listener type (for example TCP and UDP).

This problem occurred when the event which normally signals the listener thread to shutdown was lost, or intercepted by other software running on the machine. For personal servers using only shared memory connections, a workaround to this lost event problem is to use the -x none option.

Now, UltraLite will perform additional checks to ensure a file is actually an UltraLite database before proceeding to overwrite it. This means that UltraLite will no longer overwrite or drop a file which is not actually a database.

New errors reported for this case are as follows:

SQLE_INVALID_DATABASE is signaled when the file is not a database. The file is left as is.

SQLE_CANNOT_ACCESS_FILE is signaled when the nature of the file cannot be determined. The file is left as is. This can only happen if there is an I/O error reading the file (unlikely or symptomatic of much larger problems).

This was motivated by a possible security hole where the ActiveX version of UltraLite could be made to erase arbitrary files using a script in a web page. Note, this does not apply to Palm record-based stores.

Now, such queries fail with the error message: "Statement size or complexity exceeds server limits", (SQLCode -890). This error can be avoided by reducing the number of outer references from the subquery or by increasing the server page size.

In the example below, the outer reference T.a is used in a subselect of the COMPUTE clause for the column T.b:

create table T(

a int,

b char(10) not null COMPUTE (left( '0001', (select max(row_num) from rowgenerator where row_num = T.a )) ))

insert into T values (1, '1')

This problem affected only jConnect connections. A workaround is to use the iAnywhere JDBC Driver which did not suffer from this restriction.

Note, this restriction affects the ASA and Mobilink plug-ins for Sybase Central, dbconsole, and dbprdbg as well.

For example,the following query shows the problem on the asademo database if the JHO join method is selected:

select ( select '12' from dummy D1, dummy D2 ) SubQ1,

( select '13' from dummy D3, dummy D4 where SubQ1 > 10 ) SubQ2

from rowgenerator R1 left outer join employee E on R1.row_num = E.salary

where (R1.row_num between SubQ1 and 255,100)

and SubQ2 = '13'

This problem has now been fixed.

For example, running the following statements:

CREATE TABLE test ( c1 INTEGER, c2 INTEGER );

INSERT INTO test VALUES( 123, 456 );

SELECT c1, c2 FROM test;

OUTPUT TO test.sql FORMAT SQL;

READ test.sql

would have caused an error message which included the text "Table FROM not found". Note that there must be a blank in the column list -- "c1, c2" -- for the message to have appeared. As well, if the table contained NULL values, dbisql could have reported an internal error. This has now been fixed.

A work around is to change the ml_table MobiLink system table to not have a value of 0 for table_id (the primary key), by applying the following SQL commands to your DB2 consolidated database when the MobiLink server is not running (and only after verifying that ml_table has a row with table_id of 0):

create table tmp_id(id integer not null)

create table tmp_name(name varchar(128) not null)

insert into tmp_id select max(table_id)+1 from ml_table

insert into tmp_name select name from ml_table where table_id=0

insert into ml_table (table_id, name) select id, '_placeholder' from tmp_id

update ml_table_script set table_id = (select id from tmp_id) where table_id=0

delete from ml_table where table_id=0

update ml_table set name = (select name from tmp_name) where table_id=(select id from tmp_id)

drop table tmp_id

drop table tmp_name

commit

1) it had a constant or subselect in the select list of a derived table

2) the derived table was on the null supplying side of an outer join

3) the derived table had another derived table in its FROM clause

4) the second derived table was a query expression (ie UNION, INTERSECT or EXCEPT)

5) one of the branches of the query expression had a constant or subselect as the last item of its select list

For example:

select * from employee left outer join

(select 1 from (select emp_id from employee union select 1) dt) dt1(x) on 1=1

This has been fixed.

For example, executing the (following invalid statement:

SET TEMPORARY 123

would result in an error message, which is correct. Then executing the following valid statement:

SELECT CURRENT TIME

would result in a syntax error. The error dialog would contain the two statements run together:

SET TEMPORARY 123SELECT CURRENT TIME

This problem has been fixed.

Now the problem in determining the default collation has been corrected, as well as the cause of the crash.

This problem could have occured if a column was of type adLongVarWChar as in the following Visual Basic example:

If rsTempSource.Fields(strFieldName).Type = ADOR.DataTypeEnum.adLongVarWChar Then

rsTempTarget.Fields(strFieldName).Value = Trim(rsTempSource.Fields(strFieldName).Value)

rsTempTarget.UpdateBatch()

End If

Binding would have failed for a column of this type resulting in a "Count field incorrect" error. This problem has been corrected.

"Run time SQL error -- *** ERROR *** Assertion failed: 102501 (10.0.0.1267)

Work table: NULL value inserted into not-NULL column (SQLCODE: -300; SQLSTATE: 40000)"

This also affected INTERSECT implemented with hash join. This problem has been fixed.

Note, this problem was limited to connections that used the iAnywhere JDBC Driver.

Example:

The login time for all connections can be displayed with:

select connection_property('LoginTime',number)

from sa_conn_info()

Example of an EXISTS subquery which is not flattened after this fix:

select *

from T

where EXISTS( select 1 from R where R.X LIKE T.X + '%')

SELECT max(c1) FROM T

The C++ code generated included a method on the generated ULResultSet class that returned the column抯 SQL type (GetColumnSQLType()). The symbol generated for some timestamp expressions would have been SQL_TIMESTAMP_STRUCT which was not defined (the valid symbols are defined in ulapi.h).

This has been fixed so that the column SQL type is now defined as SQL_TIMESTAMP.

CREATE PROCEDURE product_proc()

BEGIN

DECLARE varname integer;

set varname = 1;

SELECT * from product

END;

SELECT description

FROM product_proc() p

WHERE EXISTS

(SELECT *

FROM sales_order_items s

WHERE s.prod_id >= p.id

AND s.id = 2001)

group by description

The symptom was unwanted duplicate rows in the result.

Now, the MobiLink server retries connecting on startup. Retries are once a minute for ten minutes. After this, failure to connect results in startup failure.

1) it contained an outer join

2) the null-supplying table was a grouped derived table

3) one of the select list items of the derived table was a constant or null-tolerant function

4) one of the tables in the FROM clause of the derived table was a view, that could have been flattened

This has been fixed.

A work-around is to increase the size of the server's stack using the -gs or -gss commandline options.

numeric_col <comparison operator> double_value

where numeric_col is a column of type NUMERIC (or DECIMAL), <comparison operator> is one of ( <, <=, =, => > ), and double_value is a value of type FLOAT, REAL, or DOUBLE. The server does such comparisons in the DOUBLE domain. The value of numeric_col is converted to a DOUBLE and compared to the double_value (promoted to type DOUBLE if necessary).

Since DOUBLE is an approximate data type, there are NUMERIC values that can not be precisely represented as a DOUBLE. For example, consider the following:

CREATE TABLE T( id int, n NUMERIC(30,6) );

INSERT INTO T VALUES( 1, 9007199254740992 );

INSERT INTO T VALUES( 2, 9007199254740993 );

CREATE VARIABLE double_value DOUBLE;

SET double_value = 9007199254740992;

SELECT * FROM T WHERE n = double_value;

The correct answer to this query is both rows of the table, because both 9007199254740992 and 9007199254740993 convert to the same DOUBLE value, and therefore compare equal to double_value when compared in the DOUBLE domain.

If, on the other hand, the server was to have used an index scan, it was equivalent to the following query:

select * from T where n = CAST( double_value as NUMERIC )

This latter query returned no rows because the value of CAST( double_value as NUMERIC ) is 9007199254740994. When compared as numerics, neither of the rows of T match this value.

The change to avoid selecting an index for this case guaranteed correct results, possibly at the cost of performance. An enhancement has now been implemented for the rules for determining whether an index can be selected, so that an index can be selected for some cases which are guaranteed to give the correct results. After this change, an index can be used if the following conditions are met:

1 the numeric column has a precision that is 15 or lower

2 the comparison operator is equality (=), or the value double_value is known at query open time and can be converted to a NUMERIC value without loss of precision.

That is, the double can be converted to a NUMERIC with precision and scale limited by the connection options PRECISION and SCALE such that CAST( CAST( double_value AS NUMERIC ) AS DOUBLE ) = double_value.

The value 15 is the DBL_DIG quantity for normalized floating point numbers, and represents the number of decimal digits that can be represented by a double without loss of precision. If a column is declared as NUMERIC with a precision higher than 15, then the column can contain values that can not be represented exactly by a DOUBLE type. Therefore, it is possible for an index scan to return the wrong answer in this case, and the server does not consider using an index for these cases.

by executing the statement "GRANT MEMBERSHIP IN GROUP SYS TO PUBLIC"

When Sybase Central connects, it executed a query that references the "SYSPROCPARM" table. This query did not qualify the table by the table owner "SYS", which meant it was not found, which caused the query to fail. This problem was fixed by qualifying the reference to "SYSPROCPARM" by adding the owner "SYS".

If the connection was a TCP/IP or an SPX connection and the client end of the connection was closed without first disconnecting, and -z wasn't used, the server would have displayed the message "Connection terminated abnormally; SERVER socket shut down" in the console. This has been fixed so that it displays "Connection terminated abnormally; client socket shut down."

DIAG [22001] [IBM][CLI Driver] CLI0109E String data right truncation. SQLSTATE=22001 (-99999)

This has been fixed.

procedure or view did not contain an open parenthesis after the name, which might have been the case if the procedure or view took no parameters and was in the Transact-SQL dialect. This has been fixed.

-121 "Permission denied: you do not have permission to update ... "

if the select list referenced columns of an updatable view. This problem has been fixed.

(1) the subquery qualifyed for being flattened

(2) the subquery contained an outer join.

(3) the preserved side of the outer join didn't have a join predicate with the null-supplying side beside the ON condition of the outer join.

(4) the preserved side of the outer join had a semijoin (i.e., JE or JHE) in the access plan for the query

For example:

If the following query had the plan: T<seq> JE R<seq> JNLO S<seq> incorrect result set might have been returned for certain instances of the tables T, R, and S.

select *

from T

where exists( select 1

from R left outer join S ON (R.x = S.x)

where S.y is NULL or S.y = T.y )

This has now been fixed.

There were numerous other instances where the character set of various strings was not tracked, converted, or maintained correctly. The following properties were also corrected:

db_property( 'alias' )

db_property( 'file' )

db_property( 'logname' )

db_property( 'logmirrorname' )

db_property( 'tempfilename' )

property( 'name' )

Also, cursor names were not being converted to database charset, but were left in the application's charset. The database name sent for a dbstop request was also left in the application's charset. The database name for a STOP DATABASE statement was left in the database charset, rather than the required OS charset. When autostarting a database, the database filename (DBF connection parameter) was converted to the database charset, rather than OS charset.

These problem have now been corrected.

with large databases. The problem was due to dynamic cache sizing not being enabled for servers running on Solaris 9 or 10, thus the server never grew the cache to accomodate large databases. This has now been fixed, dynamic cache sizing is now enabled when the server is run on Solaris 9 or 10. A work around is to start the server with a sufficiently large cache.

The server listens to UDP ports and responds to requests on these ports so that applications can locate the server by server name, even if the server starts on a TCP/IP port other than the default port (2638). Since Mac OS X, HP-UX, and Compaq Tru64 platforms do not allow multiple processes to bind to the same UDP port, connections to server running on these platforms, must specify the TCP/IP port number (via ServerPort) if the server is not using the default TCP/IP port (2638).

If the server's TCP/IP port number is 2638 (the default), the server listens to UDP port 2638, otherwise the server should listen to the same UDP port as the TCP/IP port. For the Mac OS X, HP-UX, and Compaq Tru64 platforms, the server should NOT listen to UDP port 2638, even though servers on other platforms do additionally listen to UDP port 2638. The reason servers on these platforms should not listen to UDP port 2638 is if a second server starts on TCP/IP port 2638, the UDP port 2638 must remain available for the second server.

Note that in order to connect over TCP/IP to a server using a TCP/IP port other than 2638 running on the Mac OS X, HP-UX or Compaq Tru64 platforms, the client must specify the port of the server.

For example, if the server is started with the command "dbsrv8 -n MyASAServer asademo.db", a client on the same subnet can find the server using the connection parameters "eng=MyASAServer;links=tcpip".

If the server is started on Mac OS X, HP-UX or Compaq Tru64 with the command "dbsrv8 -n SecondASAServer -x tcpip(port=7777) asademo.db", a client on the same subnet can find the server using the connection parameters "eng=SecondASAServer;links=tcpip(port=7777)". Note that if the server was running on a different platform, then the client would not need to specify the port TCP/IP parameter.

Additionally, on Mac OS X, HP-UX and Compaq Tru64 platforms, if a server was already using port 2638, and a second network server was started without the ServerPort parameter, this second network server should fail to start. Before this change the second network server would have chosen a different port and started. The reason the network server should fail to start in this case is so that user can specify the server's port number which all clients must also specify (if the server were allowed to start, the port number could change if the second server was restarted, causing clients to fail to connect in the future). Note that personal servers will start even if port 2638 is in use, since shared memory is normally used to connect to personal servers.

This has been fixed so that on Mac OS X, HP-UX and Compaq Tru64, servers listen to the correct UDP ports, and network servers fail to start if TCP/IP port 2638 is in use and no port is specified.

For example, a statement of the following form could have caused the problem:

select 1

+ (1+1+...+1) -- 10,000 '1' literals in the expression

+ ...

+ (1+1+...+1) -- above parenthesized expression repeated 11 or more times, depending on cache size

Requests could also fail with error -890 - "Statement size or complexity exceeds server limits", if the main heap grew to be near <maximum cache size>/<number of workers>.

These problems have now been fixed.

Description from issue 344313:

A query would have failed with an incorrect error message, under the following conditions:

1) it contained an outer join

2) the null-supplying table was a grouped derived table

3) one of the select list items of the derived table was a constant or null-tolerant function

4) one of the tables in the FROM clause of the derived table was a view, that could have been flattened

Note, this problem also affected MobiLink's dbmlsync.

Executables affected:

dbmlsync.exe,

dbremote.exe,

ssremote.exe,

ssqueue.exe,

dbltm.exe,

dblsn.exe,

qaagent.exe,

dbmlsrv9.exe

As a general rule, for query blocks using both ORDER BY and DISTINCT clauses, the ORDER BY expressions must reference only the expressions from the select list with DISTINCT clause.

For example:

select distinct emp_lname, isnull (city, ' ') as address

from employee

order by upper(address)

Example:

select *

from R

where '123' NOT IN (select T.x

from T)

For example:

create procedure "p"as begin return end

would have been saved as:

create procedure pas begin return end

This has been fixed.

For example, the following query would have given wrong results of this nature:

SELECT T1.*

FROM ( select row_num C1, row_num*1 C2 from rowgenerator) T1

LEFT JOIN ( systable T key join syscolumn C) ON T1.C2=0

Now, the mappings for user-defined characters in cp950 (Traditional Chinese) are included.

The initial fix introduced a potential incompatibility with older versions of DBISQL. Specifically, when connecting using the iAnywhere JDBC Driver, the values of the following options were always being set as indicated, regardless of the PUBLIC or per-user setting (if any):

Option Value

---------------- ---------

Date_format yyyy-mm-dd

Date_order ymd

Time_format hh:nn:ss

Timestamp_format yyyy-mm-dd hh:nn:ss.sssss

Now, the values of these options are taken from the per user setting (if set), or the PUBLIC setting (if there is no per user setting). If a custom login procedure has been created, these options are NOT explicitly modified by DBISQL.

fixed. Now the entire body of the reply will be in the requested character set.

This has been fixed. The first "parameter" of the error message is now replaced with the token where the error took place, and the second parameter is now replaced with the offset into the SQL statement string.

This has been fixed. The first "parameter" of the error message is now replaced with the token where the error took place, and the second parameter is now replaced with the offset into the SQL statement string.

The following conditions must have been met for a query to have exhibited this problem:

(1) the main query block was a grouped query on a view V1

(2) the main query block contained a local view predicate on V1 (e.g., "V1.col1 = constant")

(3) the view V1 contained other predicates that, with the help of the pushed predicate, could have been used to infer sargable predicates on base tables (e.g., "col1 = T.x")

(4) the view V1 was grouped as well

Example:

select V1.col2, count(*)

from V1

where V1.col1 = c

group by V1.col2

V1: select V2.col3, count(*)

from V2, T

where V2.col1 = T.x

group by V2.col3

V2: select *

form R1

UNION ALL

select *

from R2

Now there is an implicit rule associated with each user mentioned in the rules file that causes all messages to be filtered, except those explicitly allowed.

Example:

Before this change a rules file containing:

[someUser]

start time '12:00:00' every 6 hours = myPriority in ( 'low', 'medium' )

would have been equivalent to a rules file containing

[someUser]

automatic=

To get the correct behaviour previously, the rules file would have had to have been:

[someUser]

automatic = 1 > 2

start time '12:00:00' every 6 hours = myPriority in ( 'low', 'medium' )

Now, the rules file can be simply:

[someUser]

start time '12:00:00' every 6 hours = myPriority in ( 'low', 'medium' )

Note that before this change, the following rules worked as expected:

[someUser]

automatic = myPriority = 'high'

start time '12:00:00' every 6 hours = myPriority in ( 'low', 'medium' )

That is, there was only a problem when scheduled rules were used without automatic rules.

did not contain a table reference and a subsequent expression did contain a table reference.

For example:

message 'Version: ',string(( select @@version ))

This has been fixed.

To work around this problem, the 9.0.0 application must be rebuilt with 9.0.1 software. In particular, it is important to specify the correct version of the DBTOOLS library when setting up the a_sync_db structure:

dbSyncStruct.version=9000; // is the wrong way to initialize this field

dbSyncStruct.version=DB_TOOLS_VERSION_NUMBER; // is the correct way

This problem affects any application built with 9.0.0 and deployed with 9.0.1 installed, and any application built with 9.0.1 and deployed with 9.0.1 installed if the wrong version number is specified in the a_sync_db structure. These applications must be re-built with the 9.0.1 dbtools library and they must specify the correct DBTOOLS version number as shown above.

- subquery was used in a conjunct of the form 'expr theta (subquery)"

- subquery was a grouped query with DISTINCT aggregates

- subquery referenced only one base or derived table, or view T

- the same object T was used in the main query block

- the access plan chosen for this query used WINDOW operator

This has been fixed.

For example:

Select *

from T, R

where T.z = R.z and T.Y = (select sum(distinct T.X) from T)

The access plan must use WINDOW operator: R<seq> JH* Window [ T<seq>]

For example, the following query demonstrates the problem:

create table SQScroll(

pk int primary key,

x char(10),

y int

);

insert into SQScroll

select row_num, row_num, row_num from rowgenerator;

SELECT pk, x,

IF x IN ( SELECT ''||row_num FROM rowgenerator R WHERE S1.pk = R.row_num ) THEN '1' ELSE '0' ENDIF b_in,

IF x = ANY ( SELECT ''||row_num FROM rowgenerator R WHERE S1.pk = R.row_num ) THEN '1' ELSE '0' ENDIF b_any,

IF x = ALL ( SELECT ''||row_num FROM rowgenerator R WHERE S1.pk = R.row_num ) THEN '1' ELSE '0' ENDIF b_all

FROM SQScroll S1

ORDER BY 2,3,4,5

Note that this problem would only have occurred if the actual object was cut or copied from the Procedures and Functions, Triggers or Views folder, but would not have occurred if the object's source was simply copied from the editor in the right-pane.

1) There was an aliased constant in the select list of a derived table on the null supplying side of an outer join ('xxx' in the example below)

2) The derived table must have been joined to three or more tables, and the ON condition for one of the joins must have contained an alias reference to the constant in 1)

For example:

select 1

from systable aaa

牋  left outer join

牋  ( select 1 as xxx

牋牋  from systable eee

牋牋牋牋牋 left outer join

牋牋牋牋牋 systable bbb

牋牋牋牋牋 on 1 = xxx

牋牋牋牋牋牋牋牋牋牋牋 left outer join

牋牋牋牋牋牋牋牋牋牋牋牋牋牋牋牋牋牋 systable ccc on 1 = 1

牋  ) ddd

on 1=1;

This is now fixed, but a workaround is to duplicate the constant in the ON condition rather than making it an alias reference.

Note that a RAISERROR executed inside a trigger will still cause the triggering statement to fail.

Since the size of ul_length is 2, a workaround would be to simply add 1 to the return value of a GetSize<name> method generated for a BINARY column.

Example 1: Posting my message to my class

-l "action='post ''my message'' to ''my class''';"

Example 2: Posting my'message to my'class

-l "action='post my''''message to my''''class';"

Example 3: Also posting my'message to my'class

-l "action='post ''my''''message'' to ''my''''class''';"

selection page was about to be displayed. This has been fixed. Now, the table selection page is not available when unloading a pre-7.0.0 database, since the lists of tables and users are not available.

1. A crash on startup -- The Java VM may have reported that an exception occurred in the video card driver.

2. Painting problems -- On Windows XP, the task switcher that comes with Windows XP Powertoys caused the administration tools to paint incorrectly when switching through the list of tasks.

These problems have been fixed, but a workaround is to disable the computer's use of DirectDraw and Direct3D acceleration.

For example:

SELECT *

FROM

( select count(*)

from dbo.rowgenerator R1

) T1( c1 )

full JOIN (

select * from

rowgenerator R2

where R2.row_num < 2) R2 on R2.row_num = T1.c1

This has been corrected.

Note that these objects can still not be created using a wizard when all users are

filtered out, since only unfiltered users can be choosen as the owner of a new object.

select 1 from sys.systable having 1 = 0

that created a grouping operator with no aggregate functions or group by columns, would have caused a server crash. This has been fixed.

This is fixed.

For example, the following query would have failed this way:

SELECT *

FROM dbo.rowgenerator R1 LEFT JOIN

( select T1.a1

from ( select

T.table_name || 'def'

from dbo.rowgenerator R2, sys.systable T

) as T1(a1)

) T2

ON 1=0

This has now been fixed.

As a workaround, the Transact-SQL SELECT INTO statement can be split into two separate statements. The first declaring a local temporary table and the second being a non-Transact-SQL SELECT INTO statement.

For example:

SELECT T1.a1,

count( DISTINCT T1.a1 ) sumd0

FROM

( SELECT 1 a1

FROM dbo.rowgenerator ) T1

GROUP BY T1.a1

The following conditions must have occurred for a query to exhibit this problem:

1. the tautology was in the WHERE clause of the main query block, and was not in a conjunction

2. the column referenced in the tautology was declared not NULL

3. the column referenced in the tautology belonged to a NULL supplying table.

For example:

select count( *)

from product p1 left outer join product p2 on (p2.quantity < 0 )

where (p2.id < 10 or p2.id > 5 or p1.quantity > 100)

where "p2.id < 10 or p2.id > 5" is a tautology, p2.id is a column of the null-supplying table "product p2" and p2.id is declared NOT NULL.

The server's behaviour is now consistant, it generates a NULL value for an AUTOINCREMENT column if the next available value is out of range for the column.

1. The post action now turns a numeric string into message id implicitly, without going through message registration.

2. The post action will now try to interpret the destination as a window title, if finding a window class with the specified name failed.

New usage:

action=post <window message>|<id> to <window class name>|<window title>

execute var;

a syntax error should be reported, but was not. Instead, when the procedure or trigger was executed, the message "Invalid prepared statement type" was reported. A syntax error will now be given when the procedure or trigger is created. If the intent of the original statement was to treat the contents of the string variable "var" as a statement to be executed, EXECUTE IMMEDIATE must be used instead.

again, dbmlsync could have ontinuously failed to synchronize until it was shut down and restarted. The MobiLink server would simple have reported "Synchronization Failed", with no more information. This has now been fixed.

This option may be particularly useful when using Delphi, where binary columns are always zero-padded, but varbinary columns are not.

ulinit.exe 朿 揹sn=MyDsn� 杅 test.usm 杗 *

It was erroneously attempting to extract tables and views owned by dbo as well, resulting in the following output:

Error: Table sysalternates must have a primary key.

Error: Table syslogs must have a primary key.

Error: Table syscolumns must have a primary key.

Error: Table syscomments must have a primary key.

Error: Table sysconstraints must have a primary key.

Error: Table sysdepends must have a primary key.

Error: Table sysindexes must have a primary key.

Error: Table syskeys must have a primary key.

Error: Table sysobjects must have a primary key.

Error: Table sysprocedures must have a primary key.

Error: Table sysprotects must have a primary key.

Error: Table sysreferences must have a primary key.

Error: Table sysroles must have a primary key.

Error: Table syssegments must have a primary key.

Error: Table systhresholds must have a primary key.

This has now been fixed. A workaround is to define a publication that contains all the tables in the reference database that are to be in the UltraLite schema and specify that publication name after the 杗 command line option.

OFF means no prefetching is done.

CONDITIONAL (the default) means prefetching is done unless either the cursor type is sensitive, or the query includes a proxy table. For example, prefetching is done over a forward only cursor, but prefetching is not done over a sensitive cursor or a proxy table.

ALWAYS means prefetching is done even for sensitive cursor types or cursors involving a proxy table. Great care must be taken when using this setting. Using prefetch on a sensitive cursor changes the semantics of the cursor to asensitive (old values may be fetched if the value was updated between the prefetch and application's fetch). Also using prefetch on a cursor using a proxy table could cause the error -668 "Cursor is restricted to FETCH NEXT operations" to occur when the application attempts to re-fetch prefetched rows. The application will re-fetch prefetched rows in a number of cases, including after a rollback, on a fetch relative 0, if a fetch column is re-bound or bound for the first time after the first fetch, or in some cases when GET DATA is used.

If the DisableMultiRowFetch connection parameter is set to YES, the PREFETCH database option is ignored and no prefetching is done.

Note: prefetching is not used by OpenClient or jConnect connections.

For example, the following query demonstrates the problem, depending on the plan selected by the query optimizer:

select R1.row_num, rand(), number(*)+100

from rowgenerator R1

where exists ( select * from rowgenerator R2

where R2.row_num <> R1.row_num

and R2.row_num <= 2)

This problem has been fixed.

- the query contained a full outer join above another outer join (potentially with other joins in between).

- the WHERE clause must have been a conjunction containing a predicate, or a single predicate, of the form "expr IS NULL" where expr was a simple expression involving a single base column.

- the affected lower outer join must have been an ON clause of the exact form column=column, where one of the columns involved in the predicate was in the IS NULL expression.

For example:

SELECT * FROM TAB1

LEFT OUTER JOIN TAB2 ON TAB1.COL2 = TAB2.COL2

FULL OUTER JOIN TAB3 ON TAB2.COL3 = TAB3.COL3

WHERE TAB1.COL2 IS NULL

This has now been fixed. A workaround is to rewrite the query so that it does not meet all of the listed conditions.

dbmlsynccomg.dll is not marked as supporting self-registration. However, the function "DllRegisterServer" was found. Do you wish to attempt to register this file as a control?

If 'yes' was selected to register the control, the registration would succeed with no problems. This behaviour has been corrected, the message-box woll no longer appear.

Note, this problem was introduced in 8.0.2 build 4368.

In addition, the Translate to Transact-SQL menu item is now disabled for functions when connected to version 8 or earlier servers.

German, Chinese and Japanese locales, the first day of the week is Monday.

This has been fixed. The function will now return n characters, unless there are fewer characters available to return.

wizard can be used to create any valid view.

This has been fixed. The restartable download parameter should now always be correct.

For example:

dbmlsrv9 ... -x tcpip {ignore=lb1;ignore=123.45.56.67) ...

This causes MobiLink server to ignore requests from both the host at "lb1" and the IP address 123.45.56.67

select convert(long varchar,current date,13)

would have returned a string based on the Date_format option, rather than the format represented by the parameter 13. This has been fixed.

Now, getLastError will return an error code indicating an unexpected end of message has been reached.

- dbmlsync was being run so that the same subscription was synchronized repeatedly within the same run. This could occur if scheduling options were specified or if the restart parameter in the sp_hook_dbmlsync_end hook were being used.

- hovering was disabled. (ie the -p commandline option was used).

- an error occurred late in the download phase of the synchronization. The error would have had to occur in the sp_hook_download_end hook or later but before the download was committed.

In this case, if the next synchronization of the subscription occurred without shutting dbmlsync down, the last download time used would have indicated that the previous failed synchronization had a successful download, even though the download failed and was rolled back. This would have resulted in the remote not receiving data it required if timestamp based downloads were being used.

This has been fix, but should not have caused problems for systems using snapshot download.

Note, to properly convert data to UTF-16 AND HTML-encode it, both operations have to be done essentially at the same time, which is currently not possible. Because of this, the HTTP server will not send UTF-16 if requested.

For example:

Call GResSet.Edit()

GResSet.rdoColumns("Col2").Value = 2

Call GResSet.Update()

GResSet.MoveFirst() < ---- 'Error : Not Enough fields allocated in SQLDA

The same problem was reproducable when calling ODBC functions directly.

For example:

Set rowset size to 100.

SQLExtendedFetch (SQL_FETCH_FIRST) to obtain a rowset.

SQLSetPos (SQL_UPDATE) to change a row in the rowset.

SQLSetPos (SQL_REFRESH) to update a row in the rowset.

SQLExtendedFetch (SQL_FETCH_FIRST) to refetch the rowset. < ---- 'Error : Not Enough fields allocated in SQLDA

This problem has been fixed.

Note that the previous behaviour would not have allowed a user without dba authority to change another user's password. This change to sp_password() does not prevent someone from changing the current user's password if a DBISQL or Sybase Central session is left unattended, since that person could simply enter a GRANT statement to accomplish the change. An example situation where this change is beneficial is an application which provides a "change my password" feature using sp_password and prompts for the original password.

BEGIN TRANSACTION trans_name

the string "trans_name" would have been missing in the definition stored in the catalog. If a ROLLBACK TRANSACTION trans_name was executed later in the procedure, the error "Savepoint 'trans_name' not found" would have been issued. The name for the transaction is now included in the BEGIN TRANSACTION statement stored in the procedure's definition in the catalog

Notes:

1. Data source entries created using the Driver= parameter, where the driver is not an ASA driver, cannot then be read or listed by dbdsn

2. The Driver= parameter is already supported on Unix, but has a slightly different format - it simply specifies the fully-qualified path to the driver shared object.

For example:

select * from

(select rank() over (order by emp_id) r from employee

union all

select rank() over (order by emp_id) r from employee) dt

where r > 10'

This has been fixed.

For example:

qaagent.exe -x tcpip(host=abc.com) -x tcpip(host=def.com) -x tcpip(host=xyz.com)

The specified MobiLink servers are used to implement a failover scheme, such that if qaagent cannot connect to the first mentioned MobiLink server, qaagent will attempt to connect to another "failover" MobiLink server in the order they appear in the list. Qaagent will only connect to a particular MobiLink server, if it fails to connect to all the other MobiLink servers appearing previously in the list. This list traversal will occur every time qaagent attempts to synchronize messages with the server.

Qaagent has a "listener" component (using dblsn) that is used to receive indications from the MobiLink server that messages are available at the serevr for synchronizaton. The listener component only uses the first set of connection stream parameters specified using -x, the listener component does not failover. This means that qaagent can never receive push notifications from failover servers. If the failover servers are indeed different servers, then qaagent should be run with -ra command line option. When -ra is used, qaagent is allowed to sync with a server even if it has synced with another server -- normally this is restricted behaviour.

Note:

The failover capability of QAnywhere Agent does not work in the following situations:

- the agent is running on a cradled Windows CE device,

- the agent is configured to use TCP/IP for communication with the primary MobiLink server,

- the device is using ActiveSync for TCP/IP connections.

Because of the way TCP/IP is implemented for ActiveSync, the QAnywhere Agent believes that the TCP/IP connection to the primary MobiLink server always succeeds, even when the server is unavailable. This results in the failover MobiLink server never being used.

Failover works correctly in the case of a cradled CE device with an ethernet connection, when ActiveSync is not involved.

Updating rows without the entire primary key being in the result set, could have inadvertently modified or deleted more than one row.

Some examples, using the sample database (ASADemo):

1. SELECT * FROM customer

The query include all primary key columns from the

"customer" table, so the results are editable.

2. SELECT year, quarter FROM fin_data

The query does not include all of the primary key columns

("code" is missing), so the results are not editable.

The following C# fragment illustrates the problem.

IDbCommand cmd = dbConnection.CreateCommand();

cmd.CommandText = "insert into t ( value,value2 ) values (?,?)";

IDbDataParameter param1 = cmd.CreateParameter();

IDbDataParameter param2 = cmd.CreateParameter();

// uncomment this line for workaround

// param1.DbType = DbType.AnsiString

param1.Value = "ABC\x00"+"DEFG";

param2.Value = "ABCD\x00"+"EFG";

cmd.Parameters.Add(param1);

cmd.Parameters.Add(param2);

cmd.ExecuteNonQuery();

cmd.Dispose();

This has been fixed.

For example, the number of rows for an unique index on table T, on columns <T.A, T.B>, for which the fence posts were built using two equality predicates - "T.A = constant1" and "T.B = constant2" - was set to 1 row. If the selectivity of the predicate "T.A = constant1" was known to be 0%, that selectivity was incorrectly ignored.

This has been fixed.

CREATE/ALTER VIEW -- affected tables referenced in the view's query

CREATE INDEX

ALTER TABLE

TRUNCATE TABLE

GRANT

REVOKE

DROP VIEW

LOAD TABLE

Now, these statements recorded in the transaction log will now have the table name qualified with its owner name.

For example:

CREATE TABLE foo( c1 NUMERIC(6,0), ... );

CREATE TABLE bar( c1 int, ... );

CREATE INDEX idx on bar( c1 );

...

SELECT * from foo, bar where foo.c1 = bar.c1

The server could have returned incorrect results if all of the following were true:

- The join of foo and bar was done using Join Hash with foo being the build table and bar the probe table,

- The hash join node contained an alternative Join Nested Loops strategy that involved lookups using index bar.idx,

- The actual number of rows on the build side was such that the alternative Join Nested Loops strategy was in fact employed during query execution.

This has been corrected so the the server will return the correct result set.

For example:

select first id into #temptab from tab1

Now the query will proceed, but the original warning will be reported. A workaround is to test the query without the INTO clause and resolve the warning (for instance, by adding an ORDER BY clause to resolve the non-deterministic warning).

Getting ASA temp file very large (several GB) which fills up disk and crashes the server.

This problem has now been fixed, the runtime silently re-attempts to access the database file for a few seconds. If it fails after this time, then the card must have been manually ejected and it will return SQL error SQLE_DEVICE_IO_FAILED.

unaligned access to x, ip=y

was being displayed in the system log when the server, or one of the database utilities, was used, where x and y were some hex values.

These messages would not have caused any execution correctness problems, but may have degraded performance slightly.

This has been fixed.

Additionally, for some predicates on string columns with low selectivity, the server will now make better use of index probes to determine selectivities.

For example, the following query was unnecessarily using a sort on top of the hinted index scan:

select id from tab1 with( index( tab1 ) ) order by id desc;

but the following two queries were correctly recognizing that a scan of the index would supply the correct ordering:

select id from tab1 with( index( tab1 ) ) where id > -9999 order by id desc;

select id from tab1 with( index( tab1 ) ) order by id asc;

This problem has now been fixed. A workaround is to include a sargable predicate with 100% selectivity on the index (as in example 2 above).

- the index was a compressed B-tree index

- the index contained a character or binary key column

- the length of the search value for this key column was almost the page size, or was longer

- the search value for this key column was not a constant

This has now been fixed.

For example:

select emp_id

from employee

where emp_id in (select distinct sales_rep

from sales_order key join sales_order_items)

for read only

This has now been fixed.

Note that this will not happen for updateable queries, since join elimination cannot take place in that case. Cases where it may happen include, "for read only" queries and "insert into ... select ..." queries.

A workaround is to prevent join elimination either by specifying "for update", or by using additional columns from the eliminated table in a way that doesn't change the result.

For example:

select emp_id + (manager_id-manager_id)

from employee

where emp_id in (select distinct sales_rep

from sales_order key join sales_order_items)

for read only

This problem could have occurred if:

1. the last log operation in the latest offline transaction log was a "redo_release"; and

2. in the online transaction log, there is no log operation of commit, rollback, or release and no actual data to be replicated.

This problem is fixed now.

ALTER SERVER server CONNECTION CLOSE

where "server" is the name of the remote server.

This new feature is most useful in the case where the remote server has gone away or has dropped the connection due to a timeout and the server is not able to detect that the remote connection is no longer useable. Issuing the "ALTER SERVER server CONNECTION CLOSE" statement in such cases will let the server know that the remote connection is no longer useable and should be dropped. Once a remote connection is closed, the server will create a new connection to the remote server when one is needed.

Note that this statement does not drop all connections to the remote server, but only the remote connection associated with the local connection. Also, the user does not require DBA authority to dop a remote connection.

This has been fixed so that the server calculates licenses used accurately.

- the application used a different charset than the database

- the server had character set translation enabled

- the length parameter passed to SQLPutData was larger than SQL_ATTR_MAX_LENGTH

In this case the data is send as VARCHAR and the server does character set translation. This has now been fixed.

Now, a lower bound of 0.01% is enforced on the selectivity of frequencies that will be stored.

Note, that upgrading a 5.5 database using the Database Upgrade utility causes the jConnect objects to be replaced with ones owned by dbo, so an upgraded database does not have this problem.

Server: Msg 7317, Level 16, State 1, Line 1

OLE DB provider 'ASAProv.80' returned an invalid schema definition.

For example:

select * from ASA8.asademo.dba.customer

where "ASA8" is the name of the Linked Server, "asademo" is the catalog name, "dba" is the schema name and "customer" is the table name. This problem has been fixed, but the following must also be done in order to support a Linked Server query:

- When the Linked Server is defined, "Provider Options" must be selected. (this button is greyed out and unusable once the Linked Server has been defined). In the Provider Options dialog, the "Allow InProcess" option must be selected.

- ASA does not support catalogs, so the four part table reference must omit the catalog name (two consecutive periods with no intervening characters, ie select * from ASA8..dba.customer). Including a catalog name will result in the error: "Invalid schema or catalog specified for provider 'ASAProv.80'"

- The database must be updated to include the revised stored procedures found in the scripts\oleschem.sql directory. This file includes a new stored procedure dbo.sa_oledb_tables_info that is required for Linked Server support.

For example, if the column was BINARY(20) and it was bound as SQL_C_CHAR and the BufferLength value was 20, then the converted values were stored at offset 0, 20*1+20*1, 20*2+20*2, etc. which was 0, 40, 80, etc. This is incorrect. The column values must be stored at offset 0, offset BufferLength * 1, offset BufferLength * 2, etc. which, in the example, is 0, 20, 40, etc.

This problem has been fixed, but as a result. the application must ensure that the value for BufferLength for binary to character conversions, is double that of the actual column length, in order to avoid truncation. For example, if the column is BINARY(20) and it is bound as SQL_C_CHAR then the BufferLength value must be 40 (ie 2*20).

A similar problem also existed for UniqueIdentifier columns, bound as char (SQL_C_CHAR) or wide char (SQL_C_WCHAR), in which a 16-byte binary value is converted to a 36-byte character string complete with hyphens (e.g., "41dfe9e6-db91-11d2-8c43-006008d26a6f" ). The buffer offset calculation for this was also incorrect, and has also has been corrected.

"The source could not be shown for the procedure because the database filter is excluding it."

This has now been fixed.

1. It was not possible to differentiate between NULL and an empty string

2. It was not possible to predictably specify for a column that the DEFAULT value was to have been inserted

3. It was not possible to change a non-NULL value to a NULL value in a BIT column.

These problems have now been fixed. As part of this fix, the user interface controls used to enter table values have changed as follows:

1. BIT fields are now always shown using a text field. Previously, they were shown using a checkbox or a text field, depending on whether the connection was via jConnect or the iAnywhere JDBC Driver, and depending on the nullability of the column.

2. If a column allows NULL values, or has a default value defined, it is edited using a combobox. The drop-down list contains "(NULL)" and/or "(DEFAULT)" if applicable. If the column does not allow NULL values, and does not have a default, a plain text field is used.

3. If the "Return_date_time_as_string" option is OFF, TIME, DATE, and TIMESTAMP columns can now be edited using an optional time and date chooser. Note that this option is usually ON, so the new editors won't normally be see for these types.

Some other changes that were made to the result set display:

1. The table now displays tool tips for the column headers which shows the name of the column, its data type, whether it accepts null values, and its default value (if one is defined).

2. When editing a value, the column can now be resized and the value editor will not close. Previously, it did.

3. In DBISQL, changing the truncation length on the "Results" page of the "Options" dialog now takes effect immediately. Previously, it would not take effect until the next statement was executed.

4. In DBISQL, there is a new option for setting the string displayed to indicate the default value. By default, it is "(DEFAULT)". this can now be changed from the "Options"

dialog.

If a remote connection has become inactive or is no longer needed, a user can now close the remote connection by executing:

ALTER SERVER server CONNECTION CLOSE [CURRENT|ALL|connection_id]

where "server" is the name of the remote server.

ALTER SERVER server CONNECTION CLOSE, and

ALTER SERVER server CONNECTION CLOSE CURRENT

are the same and will drop the remote connection to the server associated with the local connection. The user does not require DBA authority in this case. Also, both ODBC and JDBC remote connections can be dropped using this syntax.

ALTER SERVER server CONNECTION CLOSE connection_id

will drop the remote ODBC connection associated with the local connection identified by connection_id. If the local connection identified by connection_id is not the current local connection, then the user does require DBA authority in this case. Attempting to drop a remote JDBC connection using this syntax will generate an error. Closing a remote JDBC connection for a local connection that is not the current connection, would require interacting with another connection's VM, which is not possible.

ALTER SERVER server CONNECTION CLOSE ALL

will drop all remote ODBC connections to the server. Attempting to drop all remote JDBC connections using this syntax will generate an error.

This feature is most useful in the case where the remote server has gone away or has dropped the connection(s) due to a timeout. In these cases, the server may not detect that the remote connection(s) is/are no longer useable. Issuing the "ALTER SERVER server CONNECTION CLOSE" statement in such cases will let the local server know that the connection(s) is/are no longer useable and should be discarded. Once a remote connection is closed, the server will create a new connection to the remote server when one is needed.

For example:

create procedure p( rstart bigint )

result ( x bigint )

begin

select row_num from dbo.rowgenerator;

end;

select * from p(1);

This has now been fixed. A workaround is to add some code to the procedure body so that it is not just a single select.

This problem has been corrected, the correct charset is noe used.

Attempts to execute a statement that contained an IN list with too many elements would have lead to the following assertion failure:

104010, "Internal vector size too large"

This was due to an IN list optimization that was failing. The maximum number of IN-list elements was limited by database page size. Now, IN-list size is limited by cache size. However, if an IN list is larger than the above limit, the IN-list optimization will not be used, which may impact performance, but the statement will not fail.

Attempts to execute a statement that referred to too many tables could have lead the to following assertion failure:

101504, "Memory allocation size too large"

The maximum number of tables is limited by database page size.

Attempts to execute a large statement could have lead to the assertion failure:

101503, "Heap index (%d) and heap header page counts (%d) disagree"

if the size of the associated heap was larger than 2^16 pages.

- connect timeout

- connection timeout

- connection lifetime

- connection reset

- enlist

- max pool size

- min pool size

- pooling

- persist security info

This has now been corrected, these parameters can now be quoted.

Note, both the client libraries and server must be updated.

The following Visual Basic code fragment illustrates the problem (the stored procedure mysp() returns two result sets):

adors.Open("call mysp()", adocon, ADODB.CursorTypeEnum.adOpenDynamic,

ADODB.LockTypeEnum.adLockReadOnly,

ADODB.CommandTypeEnum.adCmdText)

Label1.Text = adors.Fields(1).Value

adors = adors.NextRecordset() <--- adors was always Null after this

Label1.Text = adors.Fields(1).Value

For example:

SELECT emp_fname, emp_lname

FROM employee

ORDER BY emp_fname, emp_fname

SELECT emp_fname, emp_lname

FROM employee

ORDER BY 1, 1

This has now been fixed, the duplicate ORDER BY item will be ignored by the Query Editor's parser.

In the ASA manual, the link for the variance alias function points to the incorrect aggregate function

ASA SQL Reference

- SQL functions

- Alphabetic list of functions

- VARIANCE function [Aggregate]

An alias for VAR_SAMP. See STDEV_SAMP function [Aggregate].

The link points to the STDEV_SAMP function and not the VAR_SAMP function

Possible workaround might be:

ALTER PROCEDURE [ owner.]procedure-name SET HIDDEN

but this is for all users.

The motivation for this change was related to running the QAnywhere Agent on Windows CE devices, in that there is a limitation that there may be at most 32 processes running at one time. When this limit is reached, and a new application is launched, Windows CE will send a WM_CLOSE message to the application that appears in the list of running applications given by Settings/System/Memory/Running Programs. When the QAnywhere Agent is launched without -q, it appears in the list of running applications, and is eligible to receive a WM_CLOSE when another application is launched. Since, the QAnywhere Agent is to behave like a service when run on Windows CE devices, it is recommended that it be launched with -q.

This error is likely to have occurred only when the cache size was inadequate for the server's workload. A workaround. which will reduce the chances of the crash occurring, is to increase the cache size (if practical).

This behaviour is now fixed.

A workaround is to execute the procedure using only CALL statements or only SELECT statements. Another workaround is to add some useless code to the procedure body so that it consists of more than a single SELECT. For example, the following code could be added:

declare v int;

set v=1;

1 - a connection had begun a transaction with an Insert, Update or Delete

2 - a checkpoint subsequently occurred prior to a commit or rollback of the transaction

3 - another modification was made by the same connection on a table which had a trigger defined

4 - the modification was written to the redo log

5 - the server crashed or was killed prior to the next checkpoint

This has been fixed.

Following error message would have appeared in the qaagent logfile:

InternalError: There are still active database connections

QAnywhere Agent shutdown error -109 - shutdown dbeng9 manually

This is now fixed.

The application connected to the redirector, which then selected one of the MobiLink servers to send the request. If this server did not respond, the redirector detected this and marked the server as dead. The request would then have been routed to another MbiLink server. If a second application then connected to the redirector, and it initially happened to select the dead MobiLink server to service the request, it would eventually have been routed to a live MobiLink server, but a third application connecting to the redirector would have caused the application to hang.

Note that this problem could have occurred with a live MobiLink server, but was timing-dependent, in the following case: the live server would have had to have been marked dead, and the background thread that updates liveness state information, would not have updated the state yet. This was unlikely, unless the sleep interval for the background thread was high, and the number of requests was high. In that case, the state information was more likely to be out of date with respect to the MobiLink server when the third client connected.

These issues have been fixed.

Note, cases where this would have happened should have been very rare. Rearranging predicates in the WHERE clause of the query may be a workaround for the problem.

Note that a selectivity of 0% for a predicate may have resulted in a very poor plan for the query, given that all costed plans would have had comparable very low cost.

TCP/IP Link, error on UDP Listener rc=4, er=111, ...

The UDP listener will no longer stop if it receives this error (111 is ECONNREFUSED). The -z message will continue to be displayed though.

For example, given the procedure:

create procedure p1()

begin

with c1(a) as (select 1) select * from c1

end;

The query 'select * from p1()' would have failed with the error 'Table 'c1' not found'.

There are two possible workarounds

1) Add some code like "DECLARE varname integer; set varname = 1;" to the procedure to prevent inlining.

2) If the procedure takes no arguments, use a view instead.

This problem has now been fixed.

- The locked index pages would have remained permanently in cache, decreasing the amount of available cache.

- The engine would have hung at shutdown.

- Connections making updates, inserts or deletes to column values contained in the scanned index, such that the locked index pages need to be modified, would have blocked indefinitely.

This problem has now been fixed.

The partial synchronization is stored in a temporary file, which is deleted when the entire synchronization is downloaded and applied. If new messages become available for a client or are generated by the client in the time between when the last communication failure and the next synchronization, then any partially synchronized data is discarded.

would have appeared before the date "January 1, 2003". This has been fixed, dates, times and timestamps are now sorted by their actual value.

To work around the problem, execute the following SQL statement, against the consolidated database, with a user having DBA authority:

alter table ml_qa_repository_staging drop unique( seqno )

The unique index being dropped is unnecessary, and is the cause of the problem.

This behaviour exists in both the HTTP and HTTPS synchronization streams and is always enabled, but is not supported in Java UltraLite.

The meaning of the -dbfile option for Windows NT, 2000, or XP is unchanged; the standard meaning of a file path for these OS's. The default is also unchanged; the current working directory.

Another symptom of this problem was if failover had occurred, and new QAnywhere server had a different status of client's store-and-forward database. To work around this problem, use both the -rb and the -ra qaagent command line options, which together tell the QAnywhere server that the store-and-forward database is fine to use.

Now, the qaagent -ra and -rb command line options are implicit.

1. Incorrect row logged into the transaction and the undo logs,

2. Spurious "Index entry not found" errors,

3. Incorrect entries deleted from indexes, and so on.

This problem has now been fixed.

This is illustrated by the following Visual Basic code fragment:

SQLText = "Select * from customer"

myRS.Open(SQLText, myConn, adOpenDynamic, adLockBatchOptimistic, adCmdText)

If myRS.EOF Then

myRS.Close()

Else

myRS.MoveFirst()

End If

myRS.Requery()

This problem has now been fixed.

A workaround is to set the option to OFF, manually update any DATE values to eliminate the time component, then set the option to ON. A statement such as the following could be used:

update t set datecol = date(datecol)

where datepart(hour,datecol)<>0

or datepart(minute,datecol)<>0

Normally this option should be left at its default setting.

This problem would have occurred under the following conditions:

1. The value must have been bound with SQL_C_WCHAR.

2. The indicator length must have been set to the value's actual length (rather than SQL_NTS).

3. The column must have contained embedded NULL wide characters.

4. The table must reside in a database using the UTF8 collation.

This problem has now been fixed.

table in Oracle9. When we try to create a proxy table to referring to a physical table

containing large amount of data (approx. 100000 rows) it gives Assertion failed: 102800.

The table on the Oracle db is a partion table

Note, this problem occurred in all the Java Administration Tools.

A workaround is to edit the procedure definition (e.g. using Sybase Central) and add the following ORDER BY clause:

ORDER BY parm_id

Note:

If the QAnywhere Agent is connecting to an already running database server, rather than launching the database server itself, ensure that the database used by the agent as the message store is for the exclusive use of QAnywhere messaging applications. This becuase the QAnywhere Agent will perform operations on the transaction log of this database that assume that no other applications are using the database. It is possible though to have other databases, unrelated to QAnywhere messaging, running in the database server.

custom_header=<header >

where HTTP headers typically take the form

<header name>: <header value>

To specify multiple custom HTTP headers, use "custom_header" multiple times.

Note, "custom_header" is supported for both the HTTP and HTTPS synchronization streams, but is not supported in Java UltraLite.

set_cookie=<cookie name>=<cookie value> [, <2nd cookie name>=<2nd cookie value>, ... ]

Spaces are permitted between tokens.

The set_cookie parameter can be specified more than once in a synchronization parameters string.

set_cookie is supported for both the HTTP and HTTPS synchronization streams, but is not supported in Java UltraLite.

Now, prefetch is disabled in this case, and the cursor type is described to the client as INSENSITIVE MEMBERSHIP / SENSITIVE VALUES. In order to more closely match the requested INSENSITIVE semantics, the cursor work table is populated eagerly at open time.

This change allows bookmarks to be supported for the returned cursor.

disconnect from the server on shutdown.

A connection's temporary file quota is based on two factors: the maximum size of the temp file (i.e., the maximum size it can grow to), and the number of active database connections. The maximum size of the tempfile is calculated as the sum of its current size and the amount of disk space available on the partition containing it. When limit checking is enabled, a connection will be checked for quota violation only after the temp file has grown to 80% or more of its maximum size, AND it requests more temp file space. When this occurs, any connection that uses more than the maximum temp file space divided by the number of active connections will fail.

to the execution of the "savepoint" statement may not have been translated by the Log Translation utility dbtran. This would have occurred if the rollback operations had any trigger actions and the dbtran command line option -t was not used. This problem is now fixed.

For existing databases where incorrect statistics on some columns are suspect, the problem can be rectified by recreating the statistics on the suspect columns. If nothing is done, however, the server will automatically correct the faulty statistics over time.

- table operations that were not involved in synchronization, or

- global temporary table operations, or

- trigger actions that modified synchronization tables, but trigger actions were not requested to be uploaded, or

- trigger actions that modified tables that were not included in any synchronization publications; or

- rows that were deleted after "stop synchronization delete".

This problem is now fixed.

Note, that the remote requests must be simultaneous. A connection (or several connections) that make multiple remote requests that are serialized, will not cause a crash.

unlikely. This has now been fixed.

Note, as of this change the cryptographic library, (dbaes.dll on Windows, and libdbaes.so or libdbaes.sl on Unix), is no longer used and can be removed.

- Double quotes

- Control characters (any character less than 0x20)

- Double backslashes

Single backslashs can be used in an agentid only if it is used as an escape character.

Note it is still possible for the client to get errors when concurrent connects are done which require a server to autostart and different database files are involved, if the EngineName, ServerName or ENG parameter is not specified.

Synchronization Error.

Data was uploaded and committed, but an error occurred during download (SQLCode=SQLE_COMMUNICATIONS_ERROR: Communication error).

The SyncProgressDialog was relying on SyncProgressData.SyncResult.UploadOK to determine which message to display, but the SyncResult was only being updated at the end of synchronization. If a successfull synchronization occurred on the same connection, a subsequent synchronization would continue to see the results of the successful synchronization until the synchronization completed (in this case failing), at which time the SyncResult would have been correctly updated to reflect the result of the latter sync.

The SyncResult field of SyncProgressData is now properly updated during SyncProgressed callbacks.

A write failed with error code: (5), Access is denied.

Fatal error: Unknown device error

This has been fixed.

In addition, the following performance-related changes were also made:

- A warning message was added to the OUTPUT statement to warn about the inherent limitation of Excel 2.1 and Lotus files, that they cannot contain more than 65534 rows of data. Attempting to write more than this many rows, would have resulted in only the first 65534 rows being written to the file.

- When writing Lotus 1-2-3 files, progress messages are now displayed. Previously, the Lotus exporter worked silently.

- The algorithm which truncates long data when exporting to dBASE2 and Lotus files has been sped up. In one test case, the time taken to output 125 strings 2000 characters long went from about 8.5 seconds to 0.2 seconds

For example:

connect using 'con=a;eng=g1;links=spx(dobroadcast=NO;host=host1)'

connect using 'con=b;eng=g2;links=spx(dobroadcast=NO;host=host2)'

If done from the same application, the second connect would not have worked, as the PORT options would have been ignored, so host2 would not have been looked at.

This has now been fixed.

select (select systable.first_page from systable where systable.table_id = 1) as id0,

id0 as id1

from syscolumn

group by id1

The problem occurred under the following conditions:

- the query had a subselect in the select list or in the WHERE clause

- the subquery had an alias name ("id0" in the above query) and the alias name was aliased by a second alias name ("id0 as id1" see above), so that both alias names were syntaxtically identical

- the second alias name was part of a GROUP BY element

This problem has been fixed

On Hebrew Windows systems, the database server will choose 1255HEB as the collation for a new database, if no collation is specified. 1255HEB will now appear in the output from "dbinit -l", and in the list of collations in Sybase Central.

For example, for the query:

select * from T

where T.A = c1

and T.B = c2

if there existed an unique index i1 on columns T.A, T.X1, T.X2 and T.X3 and an index i2 on columns T.A and T.B, the query would have used index i1 instead of i2.

The collation is a byte-by-byte ordering which should give reasonable ordering for Arabic. It does not produce an ordering that follows all of the standard rules for ordering Arabic, as this is outside the capabilities of existing ASA collation support.

For existing databases, where incorrect statistics on some columns are suspected, the problem can be rectified by recreating the statistics on the suspect columns. If nothing is done however, the server will automatically correct the faulty statistics over time.

To avoid potential problems with column statistics, they will be dropped by the server on a column whose declared length is modified via an ALTER TABLE.

Note, this problem affected all of the graphical administration tools shipped with ASA 9.0.1, including Sybase Central, DBConsole, DBISQL, and the MobiLink Monitor.

The following Rowset properties were returning TRUE, indicating that the associated interface was supported. They now return FALSE, to indicate that the corresponding interfaces are not supported.

DBPROP_IChapteredRowset

DBPROP_IParentRowset

DBPROP_IRowsetFind

DBPROP_IRowsetIndex

DBPROP_IRowsetRefresh

DBPROP_IRowsetResynch

The Rowset property DBPROP_IRowsetIdentity, was returning TRUE, although the IRowsetIdentity interface was not supported. The IRowsetIdentity interface is now implemented. The property continues to return TRUE.

The following properties have been removed from the Rowset properties, as they are not Rowset properties, they are View properties.

DBPROP_IViewChapter

DBPROP_IViewFilter

DBPROP_IViewRowset

The Schema Rowset returned for DBSCHEMA_TABLE_CONSTRAINTS has been corrected.

The Schema rowset for DBSCHEMA_PROVIDER_TYPES now includes the types DBTYPE_R4 (FLOAT), DBTYPE_R8 (DOUBLE), and DBTYPE_GUID (UNIQUEIDENTIFIER).

The type TINYINT is now described as signed, previously it was described as unsigned.

The MAXIMUM_SCALE for DECIMAL and NUMERIC was NULL instead of 127. The MAXIMUM_SCALE for the SMALLINIT, TINYINT and INTEGER types is now NULL, not 0.

Schema Rowsets that returned column type information did not include support for the DBTYPE_GUID (UNIQUEIDENTIFIER) type, now they do.

In order to implement these changes, the stored procedures in scripts\oleschem.sql must replace the stored procedures in the database.

This problem would only have occurred when connecting using the iAnywhere JDBC Driver.

The IDBSchemaRowset::GetSchemas method now correctly returns bit masks rather than argument counts to indicate which restriction parameters are supported. When using linked tables with Microsoft SQL Server 2000 Distributed Queries, this problem manifested itself with the message "OLE DB provider 'ASAProv.xxx' returned an invalid schema definition".

The DBSCHEMA_TABLES_INFO Schema Rowset used by Microsoft SQL Server 2000 Distributed Queries is now supported.

To install the revised schema support, the stored procedures in scripts\oleschem.sql must replace the stored procedures in the database.

For example:

BACKUP DATABASE DIRECTORY 'd:\backup' HISTORY OFF

This change does not affect Java UltraLite.

The IDBSchemaRowset::GetSchemas method now correctly returns bit masks rather than argument counts to indicate which restriction parameters are supported. When using linked tables with Microsoft SQL Server 2000 Distributed Queries, this problem manifested itself with the message "OLE DB provider 'ASAProv.xxx' returned an invalid schema definition".

The DBSCHEMA_TABLES_INFO Schema Rowset used by Microsoft SQL Server 2000 Distributed Queries is now supported.

The stored procedures that implement the schema rowsets have been updated so that "uniqueidentifier" is used rather than "UNIQUEIDENTIFIER" .

The phrase "not transactional" in the sa_oledb_tableconstraints stored procedure has been changed to "on commit preserve rows".

To install the revised schema support, the stored procedures in scripts\oleschem.sql must replace the stored procedures in the database.

An example of bound columns will all NULL data pointers from ESQL is:

SQLDA *sqlda = alloc_sqlda( 1 );

sqlda->sqld = 1;

sqlda->sqlvar[0].sqltype = DT_INT;

sqlda->sqlvar[0].sqldata = NULL;

EXEC SQL EXECUTE stmt into descriptor into_sqlda;

This has been fixed so that an error is returned and the connection is not dropped.

For example, (VB.NET code):

Imports iAnywhere.Data.AsaClient

Module Module1

Sub Main()

Dim conn As AsaConnection

Dim cmd As AsaCommand

Dim i As Int32

Try

conn = New AsaConnection("uid=dba;pwd=sql;eng=asatest")

conn.Open()

for i = 1 to 2000

cmd = New AsaCommand("insert into ian values( 1 )", conn)

cmd.Prepared()

cmd.ExecuteNonQuery()

Next

Console.WriteLine("Inserted {0} rows", i)

conn.Close()

Catch e As Exception

Console.WriteLine(e.ToString())

End Try

End Sub

End Module

This problem has been fixed.

SQLRETURN SQLGetData(

SQLHSTMT StatementHandle,

SQLUSMALLINT ColumnNumber,

SQLSMALLINT TargetType,

SQLPOINTER TargetValuePtr,

SQLINTEGER BufferLength,

SQLINTEGER * IndPtr);

SQLGetData can be used to obtain the amount of data available by passing 0 for the BufferLength argument. The amount of data available is returned in the location pointed to by IndPtr. If the amount available cannot be determined, SQL_NO_TOTAL is returned. When the TargetType was SQL_C_WCHAR, the amount of available data was incorrect (a character count rather than byte count was returned). This has been fixed.

There were also some problems returning correct indicator values for databases using the UTF8 collation. This has also been fixed.

Examples:

1)

ASA Programming Guide

Introduction to Java in the Database

The runtime environment for Java in the database

Scope and persistence

------------------------------------------------------------------

SELECT java.lang.Math.abs(-342)

------------------------------------------------------------------

2)

ASA Programming Guide

Introduction to Java in the Database

The runtime environment for Java in the database

Java is case sensitive

------------------------------------------------------------------

SeLeCt java.lang.Math.random();

------------------------------------------------------------------

3)

ASA Programming Guide

Using Java in the Database

Special features of Java classes in the database

Returning result sets from Java methods

------------------------------------------------------------------

stmt.executeQuery (

"SELECT CAST( JName.lastName " +

"AS CHAR( 50 ) )" +

"FROM jdba.contact " );

- Open a second window, like when editing a stored procedure

- Make a change to the procedure, close the window, when it prompts to save changes, respond NO

- The window closes, but the Sybase Central window does not have focus

- pressing TAB or CTRL-TAB will caused the exception

This has now ben corrected

The following demonstrates the problem:

- Right click an existing table and choose Properties

- Select a column (that isn抰 in an index) and click the Edit button

- Rename the column

- Click OK on the Column property sheet.

- Click OK on the Table dialog and the crash may occur

This has been fixed.

result_set_schema->GetColumnId( "c4" )

would have resulted in zero being returned. This has been corrected.

For example:

select 1 from employee e1

where 1 = (select e1.emp_id from employee e2 group by e1.emp_id)

This has been fixed.

For example:

select cast( cast( 9.9 as double ) as char(30) )

would have returned '9.90000000000000036' instead of '9.9'. This has been corrected.

The following are the fields of ul_synch_info that could change during synchronization:

ul_stream_error stream_error;

ul_bool upload_ok;

ul_bool ignored_rows;

ul_auth_status auth_status;

ul_s_long auth_value;

ul_bool partial_download_retained;

For example:

INSERT INTO Tab( col_a, col_b) SELECT x AS col_a, y FROM AnotherTab

would have exhibited this behavior. This has been corrected.

Note if using a network server, Windows NT, 2000, XP or 2003 are recommended over Windows 95, 98 or ME, to ensure better performance and reliability.

attempt to validate the log and mirror log file names for the local computer, asking whether any required directories should be created. This was clearly wrong, since the database was destined for the Windows CE device, not the desktop computer.

This has been fixed. Now, when copying the database to the Windows CE device, the wizard indicates that the log and mirror log file names should be relative to the Windows CE machine. In addition, the wizard no longer attempts to validate these file names for the desktop machine. As a side-effect of this change, it is no longer possible to connect to

the database on the desktop computer if it is being copied to the Windows CE device, since the log and mirror log file names may be invalid.

dbmlsync was also affected.

The bug was introduced in the following versions and builds:

8.0.2 build 4409

9.0.0 build 1302

9.0.1 build 1852

This problem has been fixed.

Also, attempting to use the PUT statement on a remote table would have caused a server crash. An error is now given.

Example:

create table temp1 (nID int not null);

insert into temp1 (nID, nid2) values (1);

insert into temp1 (nID, nid2) values (1);

commit;

delete first from temp1; //deletes both rows

This problem has been corrected. A workaround is to attach a redundant ORDER BY clause to the statement.

delete first from temp1 order by 1+1; //deletes just one row

This problem would affect any application using ODBC or OLEDB, including Java-based applications using the JDBC-ODBC bridge (8.0) or iAnywhere JDBC Driver (9.0), including DBISQL and Sybase Central.

dbmlsync was also affected.

The bug was introduced in the following versions and builds:

8.0.2 build 4409

9.0.0 build 1302

9.0.1 build 1852

This problem has been fixed.

The same problem would affect any application using ODBC or OLEDB, including Java-based applications using the JDBC-ODBC bridge (8.0) or iAnywhere JDBC Driver (9.0), including DBISQL and Sybase Central.

The bug was introduced in the following versions and builds:

8.0.2 build 4409

9.0.0 build 1302

9.0.1 build 1852

This problem has been fixed.

"INTERNAL ERROR:occurred while storing a BLOB",

"Data truncated, column 12. (ODBC State = 01004,Native error code = 0, Column or parameter #12)

For example:

select graphical_plan( 'select * from openxml( graphical_plan(?), ''/'') with (type xml ''.'' ) ')

This is now fixed.

For example, the following query would have failed with SQLCODE -149:

Select distinct e.emp_lname + space(50) + '/' + e.emp_fname

from employee e, employee e2

where e.emp_id = e2.emp_id and e2.dept_id = 100 and (e.city = 'Needham' or e2.city = 'Burlington' )

order by e.emp_lname + space(50) + '/' + e.emp_fname

This problem has been corrected.

An index idx1 is considered a duplicate of the index idx2 if and only if the following conditions hold:

- idx1 and idx2 are defined on the same columns, having the same order, and having the property ASC/DESC exactly the same for each column.

- idx2 is a primary key index, but idx1 is not

OR idx2 is declared unique index and idx1 is not

OR idx2 is a foreign key index and idx1 is not

OR idx2 is declared a clustered index and idx1 is not

OR none of the above, in which case the engine randomly chooses idx1 to be the duplicate of idx2.

For example, if idx1 is declared as:

create index idx1 on T (T.A ASC, T.B DESC )

and idx2 is declared as :

create unique index idx2 on T (T.A ASC, T.B DESC)

then idx1 is considered a duplicate of idx2.

For base tables, an analysis of the index list is performed to flag the duplicate indexes when indexes are loaded, or an index is added or dropped.

Note 1:

The property of being a duplicate is used only by the optimizer when a statement is optimized.

Note 2:

It is strongly recommended to drop useless duplicate indexes whenever possible.

Note 3:

The properties of uniqueness and clusterness are derived from one index to another. In the example above, the engine derives that the index idx1 is also unique because idx2 is unique.

For example:

select *

from R

where R.X = (select max(T.X)

from T

where T.Y = R.Y)

The above query may be executed by computing the decorrelated subquery, equivalent to the following:

select R.*

from R, (select max(T.X), T.Y

from T

group by T.Y) as DT( max, Y)

where R.X = DT.max and R.Y = DT.Y

Note 1:

The decision to decorrelated subquery is done cost-based during optimization.

Note 2:

The crash would have occurred, if and only if a Sort Merge Join method was considered for joining the decorrelated subquery with the rest of the query block's tables. The Sort Merge Join strategy must have been chosen in the best plan for the crash to happen.

time the Connect dialog was opened, it would contain the previous connection information. For security reasons, this feature has been removed. Now, this information is no longer implicitly saved in the user's .scUserPreferences file. Instead, it is persisted in memory for the current Sybase Central session only. Note that the user can still use

connection profiles to explicitly persist connection information in the .scUserPreferences file.

This change also fixes a problem which could have caused the password to be incorrectly persisted as "***".

A workaround is to embed the insert in a compound statement, where the query is run and the inserts are done using a cursor ranging over the query.

This is a followup issue to Engineering Case 346886, which missed this special case.

item and toolbar button are enabled only when the clipboard contains

text.

E.... InternalError: Procedure 'ml_qa_set_global_property' not found

E.... Source statement:

E.... QAnywhere Agent failed to initialize parameters

I.... QAnywhere Agent stopped

This problem has been fixed.

The collation 950ZHO_HK provides support for the Windows Traditional Chinese character set CP950, plus Hong Kong Supplementary Character Set (HKSCS).

The collation 950ZHO_TW provides support for the Windows Traditional Chinese character set cp950, but doesn't support HKSCS. Ordering is based on byte-by-byte ordering of the Traditional Chinese characters. The collation 950TWN is now obsolete.

Please note that in version 9.0.0 and lower, 950TWN has no support for HKSCS, the new collation that is equivalent to it is 950ZHO_TW. In 9.0.1 and higher, 950TWN does support CP950 plus HKSCS and the new collation that is equivalent to it is 950ZHO_HK.

The reason for splitting 950TWN into two collations is that Microsoft Windows allows users to create their own characters in the End User Defined Character (EUDC) area in Windows code pages. HKSCS, is an extension to CP950, which defines its characters also in the EUDC area for CP950 and thus may conflict with existing private characters defined by users who don't use HKSCS and don't have HKSCS installed.

Note, this problem also affected HTTPS connections as well. The timeout in that case

is now set to the same as the idle timeout (the TIMEOUT parameter to HTTPS).

This also fixed a server crash that may have occurred if an older dbtls DLL was used.

select line_id as Q, count(*)

from sales_order_items

where quantity != 89999

group by line_id

having count(*) not in (select max(quantity) from product

group by Q, description )

order by 1, 2

It was necessary that both the outer block and the nested subquery both contain aggregation and/or GROUP BY clauses for this problem to have occurred. It has now been fixed.

You can disable the fast launcher by running:

dbisql -uninstall -terminate

scjview -uninstall -terminate

Collation 1252SPA is similar to 1252LATIN1, but causes N and � (N-tilde) to be sorted as separate characters. On a Windows system configured for Spanish, dbinit and the CREATE DATABASE statement will now default to this collation, and will also appear in the Sybase Central Create Database wizard for ASA.

Collation 874THAIBIN does not attempt to provide linguistically-correct sorting for Thai characters, but sorts the characters in binary order. The collation does provide character set mappings to cp874 (and TIS-620, which is compatible with cp874). On a Windows system configured for Thai, dbinit and the CREATE DATABASE statement will now default to this collation, and will also appear in the Sybase Central Create Database wizard for ASA.

These extraneous diagnostic messages have been removed. Please note that this diagnostic message can still validly occur under some circumstances and may be useful to help Technical Support diagnose other problems.

This change also prevents some spurious "connection ... terminated abnormally" messages.

wqora1919.dll

wqora19r.dll

wqora19s.dll

wqbas19.dll

wqbas19r.dll

wqicu19.dll

wqutl19.dll

wqutl19r.dll

For many collations, the returned string will change (get longer). So, an application may break if it checks for the entire string, rather than the specific two-letter language code.

BYTE m_lParm1[2]={0x10,0x5f);

would have been stored as the binary value 0x31303566 which is the original binary value stored as a hexadecimal string of characters. This has been fixed so that parameters are not converted from the user's type to a string. Instead, bound parameters are converted to the type specified by the application.

Note, this problem also affected Mobilink's Synchroniztion Client and the ASA Replication Agent

The following additional error codes are also now returned:

DB_E_NOTABLE

DB_E_PARAMNOTOPTIONAL

DB_E_DATAOVERFLOW

DB_E_CANTCONVERTVALUE

DB_E_TABLEINUSE

DB_E_ERRORSINCOMMAND

DB_SEC_E_PERMISSIONDENIED

Following are the specific conditions required to see this problem:

- Both an sp_hook_dbmlsync_end hook and an sp_hook_dbmlsync_delay hook have been coded.

- During a synchronization the delay hook was called one or more times. Those calls resulted in a total delay D and the maximum accumulated delay parameter was set to some value M.

- When the end hook is called it sets the 'restart' parameter to 'sync' or 'true' to restart the synchronization.

When the above conditions are met, the sum of delays caused by the delay hook was not being reset before the synchronization was restarted. As a result, on the restarted synchronization, the delay hook would not be called if D >= M. If D < M then the maximum delay that would be allowed before the synchronization occurred would be M - D when it should have been M.

The sum of delays is now reset before the synchronization is restarted so that the delay hook will have the same behavior on a restarted synchronization as it does on a first synchronization.

For example:

select T2_N.b

from T1, T2, T2_N

where T1.a = T2.b and T2.b = T2_N.b and

( T1.a = 1 or T1.a = 2 or T1.a = 3 or .... or T1.a = 20000)

The number of OR conditions to cause the crash depended on the available stack size. This problem has been fixed. Now these queries return an error "Statement size or complexity exceeds server limits".

The following steps demonstrate the problem:

- Create a new Schema File

- Right click on the schema file in the left pane of the application

- Close the schema file and choose not to save it

- Attempt to create a new schema file

The following could have caused this behaviour:

- Start the UltraLite Schema painter and create a new schema

- Right click on the schema in the left pane but do not choose any menu items

- Select the File menu and choose Close (when asked to save, click No)

- Select the File menu again and move over the items in the menu. The application could crash at this point.

remove the SET TEMPORARY OPTION statements.

For example:

The query below returned a result set which had the first column renamed "A2":

create table T1 ( A1 int primary key);

create table T2 ( A2 int,

foreign key (A2 ) references T1(A1) );

select A1

from T1 key join T2

group by A1

Note that the query was rewritten by the join elimination algorithm into:

select A2

from T2

group by A2

Now, the query is rewritten into:

select A2 as A1

from T2

group by A2

A work around for this problem is to alias all the columns referenced in the SELECT list with their own names.

For example, the query Q' below is not affected by this bug:

Q':

select A1 as A1

from T1 key join T2

group by A1

Now, SQLDisconnect() returns SQL_SUCCESS_WITH_INFO and sets the SQLSTATE to 01002 (Disconnect error).

The following Visual Basic code example demonstrates the problem:

Dim conn As new OleDbConnection()

Dim cmd As OleDbCommand

Dim reader As OleDbDataReader

Try

conn.ConnectionString = "Provider=ASAProv;uid=dba;pwd=sql;eng=asademo"

conn.Open()

cmd = New OleDbCommand("SELECT * FROM DEPARTMENT", conn)

reader = cmd.ExecuteReader()

While reader.Read()

Console.WriteLine(reader.GetInt32(0).ToString() + ", " _

+ reader.GetString(1) + ", " + reader.GetInt32(2).ToString())

End While

reader.Close()

reader = Nothing

conn.Close()

conn = Nothing

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

This problem has been fixed, the rgStatus array passed to the IAccessor::CreateAccessor method is now correctly initialized.

For example, given the following table:

create table T ( A int primary key,

B int);

the statement:

DELETE from T as T0

from T as T0 natural join T as T1

is equivalent to:

DELETE from T as T0

where T0.B = T0.B

This optimization was only done for SELECT statements when the table to be eliminated was not updatable. Now, this optimization is done for DELETE and UPDATE statements when the table to be eliminated is not the table to be modified.

fixed, now only read permission is required, unless the -d or -w options are used.

For example:

create procedure P1( arg int )

begin

with recursive v( a, b ) as (

select T1.a,T1.b from T1 where T1.a = arg

union all

select T1.a,T1.b from T1 join v on(T1.a = v.b)

)

select * into #temptab from v;

end

The problem only occurred when attempting to execute a CREATE DATABASE statement with other statements, as part of a batch, and affects DBISQL version 7.0.0 and later.

The COLLATION column was always empty. It now contains a 1 for ASCENDING ordering and a 2 for DESCENDING ordering.

The CLUSTERED column was always TRUE, even for a non-clustered index. This column now contain 0 unless the index is clustered, in which case it will contain a 1.

The INDEX_NAME column contained the additional string "(primary key)" when the index was based on a primary key. In the DBSCHEMA_PRIMARY_KEYS rowset (implemented by the sa_oledb_primary_keys stored procedure), the PK_NAME column does not contain this string.

Since the names were different, it was difficult to join the information in these two tables. Elsewhere the index name is reported using only the table name (in access plans for example). For these reasons, the INDEX_NAME column will no longer contain the string "(primary key)".

The following column names have been corrected:

FKTABLE_CAT is now FK_TABLE_CATALOG

FKTABLE_SCHEMA is now FK_TABLE_SCHEMA

PKTABLE_CAT is now PK_TABLE_CATALOG

PKTABLE_SCHEMA is now PK_TABLE_SCHEMA

These corrections are to the "oleschema.sql" file located in the "scripts" folder, which will be in effect for newly created databases. To implement the corrections to an existing database, connect to the database with Interactive SQL dbisql and run the contents of "oleschema.sql".

A work around would be to disable write caching in the Device Manager, via the Disk Properties tab in the Properties dialog, for the drives in question. (This might be necessary in any case for IDE drives).

A workaround for is to re-create the above databases after the install has completed. To do this, change to the appropiate directory and enter the following, (<dbfile is either qanyserv.db or qanywhere.db):

dberase -y <dbfile>

dbinit <dbfile>

A workaround is to use EXECUTE IMMEDIATE to create the view.

A workaround is to temporarily change the Display Properties of the machine to use the Windows Classic style while working with ISQL.

1) The maximum length of the column in characters, bytes, or bits, respectively, if one is defined. For example, a CHAR(5) column in an SQL table has a maximum length of five (5).

2) The maximum length of the data type in characters, bytes, or bits, respectively, if the column does not have a defined length.

3) Zero (0) if neither the column nor the data type has a defined maximum length.

4) NULL for all other types of columns.

As well, the CHARACTER_OCTET_LENGTH column contained incorrect values for LONG VARCHAR/LONG VARBINARY columns and parameters. The CHARACTER_OCTET_LENGTH column should contain the maximum length in bytes of the parameter if the type of the parameter is character or binary. A value of zero means the parameter has no maximum length. NULL for all other types of parameters.

In the PROCEDURE_COLUMNS and PROCEDURE_PARAMETERS rowsets, the column name should have been CHARACTER_OCTET_LENGTH rather than CHAR_OCTET_LENGTH.

In the PROVIDER_TYPES rowset, the COLUMN_SIZE column contained incorrect values for LONG VARCHAR/LONG VARBINARY types.

These problems have been corrected and will appear in the "oleschema.sql" file located in the "scripts" folder once the EBF has been applied. To implement the corrections to an existing database, connect to the database with Interactive SQL (dbisql) and run the contents of "oleschema.sql".

DBPROPVAL_DF_INITIALLY_DEFERRED 0x01

DBPROPVAL_DF_INITIALLY_IMMEDIATE 0x02

DBPROPVAL_DF_NOT_DEFERRABLE 0x03

These corrections will appear in the "oleschema.sql" file located in the "scripts" folder once the EBF has been applied. To implement the corrections to an existing database, connect to the database with Interactive SQL and load and run the contents of "oleschema.sql".

Now, the server will use a "guess" estimate of 50% for the selectivity of these predicates.

For example:

CREATE PROCEDURE test()

result( c1 varchar(254) )

begin

select repeat( 'abcdef', 1000 )

end

Notice that a varchar( 254 ) column is much too small to hold the result of repeat( 'abcdef', 1000 ). In this case, executing the procedure test would have resulted in an "Out of memory" exception.

- The index must have been comparison-based.

- One of the first few columns being indexed must have been a short character (or binary) column [the column must have been fully hashed]. This column must not have been the last column being indexed.

- The query must have contained a comparison involving this column [say with domain char(n)] and a string s with length > n whose n-byte prefix appeared as a value in the column.

This problemhas now been corrected.

"The procedure entry point, Module32Next could not be located in the dynamic link library KERNEL32.dll".

This error is typically reported when the version of the DLL found does not match the expected version, or the function has not been exported from the DLL. In this case, the problem was related to the later, as "Module32Next" is not a valid function on Windows NT. The code has been changed to only call this function if it exists.

For this problem to have occurred, a query must have contained a predicate of the form:

T.col = <expr>

The expression <expr> must have been an expression whose value was not known at query open time. For example, <expr> could have been a column of another table, or it could have been a function or expression that was not evaluated at optimization time. The predicate must have been the first predicate evaluated for the associated table scan, and the table T must have been scanned once with the value of <expr> being NULL. In these circumstances, the selectivity of 'T.col IS NULL' would be incorrectly set to 0. This has been fixed.

If an application opened a cursor over a query that contained an index scan with a single column in the index being searched for equality, the selectivity estimate could have been lowered incorrectly if the application scrolled the cursor using absolute fetches and did not visit all of the rows of the result set but ended the scan after the last row of the result set. This problem would have resulted in selectivity values being stored that were lower than expected, and could have lead the query optimizer to select poor execution plans by picking an index on this column instead of a better index. This problem has also been fixed.

For example:

select c from p() with (c char)

would have returned a column of type char(32767), rather than the correct type of char(1). A NUMERIC column, with no precision or scale argument, would have defaulted to (255,0) rather than the database defaults. In addition, a server crash may have occured when attempting to create a procedure containing a SELECT that had this problem. These problems have now been fixed.

dblsn.exe -l

This has been fixed.

In the example below, the SELECT query block will now be considered for caching:

for for_q as c_q dynamic scroll cursor for

select p.quantity as Q

from product p, sales_order_items s

where s.quantity = p.quantity

order by Q

do

set V = Q

end for;

Please see the following documentation for more information:

ASA SQL User's Guide

Query Optimization and Execution

How the optimizer works

Access plan caching

Error in file upgrad60.sql at line 2682 with sqlcode -140

SQL error (-140) -- User ID 'DBA' does not exist

The user DBA was unnecessarily being granted select authority on some tables. These GRANT statements have now been removed.

select 'a', 'a' from employee group by 'a', 'a'

where a constant string appears in both the select list and the GROUP BY clause, could have caused the server to crash. This has been fixed.

For example, given the following index:

create index NAME_COLOR on product( name, color);

the estimated number of rows returned by the query:

select count(*) from product

where color = 'white'

and name ='tee shirt'

with "color = 'white'" and "name ='tee shirt'" is computed by probing the index NAME_COLOR with the tuple ('tee shirt', 'white').

Note, if the index does not exist, the estimated number of rows with "color = 'white'" and "name ='tee shirt'" is computed as: [ size of table 'product'] X [selectivity of the predicate "color = 'white'"] X [selectivity of the predicate "name = 'tee shirt'"]. Such computation, unless the data is completely uncorrelated, can be inaccurate.

Example:

The query below:

select *

from dbo.rowgenerator R1 left outer join rowgenerator R2 ON ( R2.row_num = R1.row_num and R2.row_num = 10 )

where R1.row_num = 10

may have had the access plan:" R1<RowGenerator> JMO [ SORT [R2<RowGenerator> ]]". The SORT was unnecessary as all the rows generated by the null-supplying side "R2<RowGenerator> " had R2.row_num equal to 10, hence it was ordered already on R2.row_num column. Now, the plan should be " R1<RowGenerator> JMO [ R2<RowGenerator> ]".

Error: [-10002] ODBC: [DataDirect][ODBC 20101 driver]6008 (ODBC State = 4, Native error code = 0, Column or parameter #3)

This problem has been corrected.

Note that a DBA can determine whether or not capturing is in progress by viewing the server console.

If a third party HTTP server or proxy requires authentication, but no credentials are supplied, or if the supplied credentials are rejected, the synchronization attempt will fail and an appropriate error will be reported.

With Basic authentication, passwords are included in the HTTP headers in cleartext, so use HTTPS to encrypt the headers and protect the passwords.

Note that Digest authentication, which is also described in RFC 2617, is not currently supported.

For example:

select if emp_id in (1) then 0 else 1 endif

from employee

group by state

This problem is now fixed.

For example:

static char mybuff[80];

static int mytype;

// callback for messages

void SQL_CALLBACK my_msgproc(

void * sqlca,

unsigned char msg_type,

long code,

unsigned short len,

char * msg )

{

memcpy( mybuff, msg, len );

mybuff[ len ] = '\0';

mytype = msg_type;

}

// install the message handler for this connection

rc = SQLSetConnectAttr( dbc,

ASA_REGISTER_MESSAGE_CALLBACK,

(SQLPOINTER) &my_msgproc, SQL_IS_POINTER );

Then a SQL statement such as:

Message 'after' type status to client;

will invoke the ODBC client application's message handler.

The fix is the addition of four new command line options for the QAnywhere Client agent (qaagent):

-o <file> log output messages to file. Ex. -o c:\tmp\qaa.out outputs to files c:\tmp\qaa.out, c:\tmp\qaa_sync.out and c:\tmp\qaa_lsn.out

-ot <file> truncate file and log output messages to it. Ex. -ot c:\tmp\qaa.out truncates and outputs to files c:\tmp\qaa.out, c:\tmp\qaa_sync.out and c:\tmp\qaa_lsn.out

-os <size> rename log file to YYMMDDxx.<ext> and start a new file with the original name when log reaches <size> (minimum 10K, cannot use with -on). The value of <ext> depends on the log file being renamed.

-on <size> append .old to the log file name and start a new file with the original name when log reaches <size> (minimum 10K, cannot use with -os)

The QAnywhere Client will log its output to the specified file, The MobiLink Client dbmlsync, will log its output to the file suffixed by "_sync", and MobiLink Listener dblsn, will log its output to the file suffixed by "_lsn".

For example, if "-o c:\tmp\mylog.out" is specified then qaagent will log yo "c:\tmp\mylog.out", dbmlsync will log to "c:\tmp\mylog_sync.out" and dblsn will log to "c:\tmp\mylog_lsn.out".

The extension of the files created when using the -os option depends on the log being renamed. If the QAnywhere Agent log is being renamed, the extension is .qal. If the dbmlsync log is being renamed the extension is .dbs. If the dblsn log is being renamed the extension is .nrl.

If -verbose is specified without specifying -ot or -o, then the pre-fix logs are used: qaagent.log, qanycli.mls and dblsn.log, and -os and -on switches are ignored.

Note that this problem does not exist if using dbisql connected via jConnect. Also, if the "FORWARD TO" statement used single quotes instead, then both jConnect and the iAnywhere JDBC Driver will work fine.

An example:

The sargable predicate " t1.t1_id = (select MIN(t3.t1_id) from t3 where t3.t2_id=t2.t2_id)" can now be used to access the table t1 through a partial index scan on the primary key index "t1". Hence, the query may have the access plan

" t2<seq> JNL t1<t1> : GrByS[ t3<t3> ]" .

create table t1 ( t1_id int primary key);

create table t2 ( t2_id int );

create table t3 ( t1_id int, t2_id int primary key );

select * from t1, t2 on ( t2.t2_id = t1.t1_id +1 )

where t1.t1_id = (select MIN(t3.t1_id) from t3 where t3.t2_id=t2.t2_id)

For example, if a query had a WHERE clause which caused a conversion error, the application may never have received an error stating a conversion error occurred, but would have received the error "Cursor not in a valid state" instead.

This has been fixed so that the initial error, which put the cursor in an invalid state, is now returned to the application.

Note that a similar problem exists for applications running on Windows systems as well. However this problem exists in the ASA ODBC Driver and is addressed by Engineering Case 364608.

1) During the server startup sequence, the Developer Edition splash screen would appear for only a fraction of a second, then disappear. It should remain visible for three seconds, as it does when started manually.

2) The splash screen window would reappear if the system tray icon was used to restore the server window. It should appear only during the startup sequence.

3) The splash screen window was empty. The text did not appear.

These problems have been fixed. The splash screen will now contain the intended text, it will display for three seconds during the startup sequence, and will not reappear.

Note that a similar problem exists for applications running on Unix platforms as well. However this problem exists in the iAnywhere JDBC Driver and is addressed by Engineering Case 364379.

For example, the following query could generate the crash (depending on the access plan selected by the optimizer):

select count( distinct dept_name+'a' )

from ( select distinct dept_name

from department

where dept_id < 1 ) T

This problem has been fixed.

- There must have been more than about 125 rows in the table

- The rows had to have been selected using the keyboard

- The table was scrolled while selecting, past the initial 125 rows

- The "Show multiple result sets" option was OFF.

This problem has been fixed.

In a related issue, if rows were selected, then CTRL+C was pressed to copy them, extra lines of empty values would have been select after the last row. All the table data would have been copied correctly; the error was the addition of the blank rows. This has also been fixed.

/usr/lib/hpux32/dld.so: Unable to find library 'libstdc++.so.4'.

This has been fixed and a new CD-ROM is available.

There is also a workaround, create a symbolic link from libstdc++.so.5 to libstdc++.so.4:

ln -s libstdc++.so.5 libstdc++.so.4

This symlink should be removed once the installation has been completed.

The difference between Basic and Digest is that Digest employs various security mechanisms to protect the password and to protect from common types of attacks, while with Basic, the password is sent in clear text. However, HTTPS provides far better security than Digest, so it is recommended that Basic HTTP authentication be used with HTTPS for full security. The only situation where Digest authentication is really useful is when connecting through a client-side proxy which doesn't support HTTPS directly, but requires authentication.

For example:

create view products

as select p.* from

prod as p

where p.id =

any(select soi.prod_id from sales_order_items soi KEY JOIN sales_order so

where so.order_date > current date )

with check option

The following INSERT statement would have crashed the server:

insert into products (id) values ( 1001 )

- it was running without the command line option -s or with -s n, where n > 1;

- errors occurred during upload of table data;

- the handle_error (hand_odbc_error) script returned 1000; and

- the upload was a transactional upload.

This problem has been fixed.

Having a column specified multiple times in an index is not supported by UltraLite (and would not provide any performance benefits anyway). An error message is now displayed and the application exits.

The workaround to this problem is to remove the extra column definitions from the index definition.

select * from proxy_test p, (select PKID, C1 from openxml( @varxml, '//testxml/test')

with(PKID int 'PKID', C1 varchar(255) 'C1')) t where p.PKID = t.PKID

could have caused a server crash. This problem has been fixed.

ERROR at line 1:

ORA-06550: line 1, column 7:

PLS-00905: object FPSADMIN.ML_SCRIPT_UPGRADE is invalid

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

execute ml_script_upgrade -- failed

The problem is that Oracle 8i does not support the conversion from LONG to CLOB. This has been fixed by using an Oracle provided function TO_LOB() instead.

- Execute a query that contains a temp table

- Suspend and close the connection

- Re-connect to the database

- Apply a new schema file

This has been fixed.

For example:

SELECT DISTINCT b.key FROM tab_a a, tab_b b WHERE a.column = b.key

This situation requires a temporary table which will now be generated.

create global temporary table #test (col1 int, col2 int);

drop table #test;

create global temporary table #test (col1 int, col2 int);

drop table #test;

A workaround is to only use #table_name for creation of local temporary tables.

"ORA-01460: unimplemented or unreasonable conversion requested (ODBC State = HY000, Native error code = 1460)". Single row inserts worked fine. This only affected Windows systems, and has been fixed in version 4.20.00.81(B0067,U0062) of the driver.

The following files have been updated:

wqora19.dll

wqora19r.dll

wqora19s.dll

wqicu19.dlll

In the DSN setting, the option "Procedure Returns Results" must be selected.

- The statement plan was cached

- The table was declared as "ON COMMIT DELETE ROWS"

- The table had more than 100 pages when the plan was cached

- COMMIT was called before the statement was executed

This problem has been fixed. The crash could be avoided by setting the option 'Max_plans_cached' to 0.

Workarounds include dropping the procedure after the upgrade, or simply not performing the upgrade (since it should be unnecessary).

For example:

SET @c = (SELECT count(*) FROM t)

where t is a remote table. In this case, if the table t was empty, then a NOT FOUND warning would have been generated when the subquery was evaluated and instead of setting the variable @c to 0, the server would have returned the error "ASA Error -823: OMNI cannot handle expressions involving remote tables inside stored procedures ".

This problem has now been fixed.

For example:

The following query returned the error "ASA Error -94: Invalid type or field reference":

select * FROM A WHERE A.ID NOT IN ( SELECT B.ID FROM B );

Table B has the computed column "EntityAddressId" referencing the JAVA class "Address", and table A has a base table column named "Address". Note that the computed column doesn't have to be referenced in the query.

CREATE TABLE A

(

ID int,

"Address" varchar (10)

);

CREATE TABLE B

(

ID int,

"EntityAddressId" numeric(10,0) NULL COMPUTE (Address >> FindAddress(0, '/', 0)),

);

- it used an ANY, ALL, IN, or NOT IN subquery

- the subquery used a grouped derived table or view

- the derived table or view aliased an aggregate function

- the alias was used in another column of the derived table or view

For example:

select 1

where 1 not in ( select b from (select sum(1) as b, b + 1 as c) dt )

This problem has now been fixed.

ALTER {DOMAIN | DATATYPE} usertype RENAME newname

The name of the user type is updated in SYSUSERTYPE.

Note that any procedures, triggers, views or events which refer to the user data type must be recreated, as they will continue to refer to the old name. Renaming of Java data types is not permitted.

Note, If the temporary table was created without ON COMMIT PRESERVE ROWS and the table was non-empty, execution of the CREATE INDEX statement will now delete all of the rows in the table, because of the added commit. Due to the addition of the commit, a CREATE INDEX on a local temporary table can no longer be used in a trigger or within an atomic compound statement. will release any locks held by the connection, and will close cursors not opened WITH HOLD.

The derived table nodes appear where a derived table or view appeared in the query text and was not combined with the rest of the query by "flattening". Also, these derived table nodes appear where the query processor generates a derived table, for example as input to a UNION, INTERSECT, or EXCEPT.

The following query has three Derived table nodes in the graphical plan. One is associated with 'MyDerivedTable', which was specified in the query. Two others are generated for the inputs of the UNION. The derived table nodes can be used to find the expressions used as input to the UNION.

select emp_id from employee

union all

select dept_id from ( select top 5 dept_id from department order by dept_name desc ) MyDerivedTable

A work around is to convert the database schema definition from USM to XML, and back again:

ulxml.exe -toxml schema.usm schema.xml

rename schema.usm schema_old.usm

ulxml.exe -tousm schema.xml schema.usm

The new schema.usm will then allow creating a foreign key.

This problem was introduced by the changes for Engineering Case 364608.

Note that the use of an empty table having an autoincrement column, together with get_identity(), may still have resulted in values being re-used if the database was not shut down cleanly and values were allocated since the last checkpoint. Depending on how the values were used, it may have been possible to correct the starting value in a DatabaseStart event by calling sa_reset_identity() with the next value to use. For example:

declare maxval unsigned bigint;

set maxval = (select max(othercol) from othertab);

call sa_reset_identity('IDGenTab', 'DBA', maxval);

Note, this problem also affected the SMTP message link for the ASE Message Agent ssremote, on Unix platforms.

- Stop MobiLink and thus stop the Notifier.

- Use the MobiLink plugin to update the network_provider_id from 'ROGERS AT&T' to 'ROGERS'.

- Restart the MobiLink Server with the notifier.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

which correspond to setting the isolation level of the connection to 0, 1, 2, or 3 respectively.

For example:

CREATE TABLE tab (

rowid int not null primary key,

col_a int default global autoincrement( 10000 )

)

This has now been fixed.

1. UltraLite tables with TIME columns that defaulted to CURRENT TIME, or a specific date, was incorrectly generated.

2. A uniqueidentifier column that defaulted to NEWID was incorrectly generated as new_uuid.

3. Foreign key names were not being output in the ALTER TABLE ADD FOREIGN KEY statement.

NOTE: 6.x versions of dbconsol on all platforms fail to connect to servers after version 9.0.0 1223. This is still the case, and there is no workaround.

Note, this scenario could have occurred in a multithreaded application if all of the following conditions were true:

- the server was autostarted by a multithreaded application

- the main thread of the application signaled a child thread to shut down

- the child thread did a disconnect as part of shutting down

- the main thread did not wait for the child thread to complete before ending

This has been fixed so that the server will correctly autostop.

begin

declare foo long varchar;

set foo = 'ABCDabcd';

set foo = repeat( foo, 49152);

set foo = repeat( foo, 65536);

end;

The server will now stop the computation after the maximum allowed size of 2GB has been reached.

This issue has been fixed.

returning. This allows more reliable control over starting and stopping the QAnywhere

Agent under program control.

-853 "Cursor not in a valid state"

This has been fixed.

The following Visual Basic example calls a stored procedure with a String parameter.

Dim sendParam1 As String

sendParam1 = "20040927120000"

Dim cmd As ADODB.Command

cmd = New ADODB.Command

With cmd

.CommandText = "testproc1"

.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc

.ActiveConnection = myConn

.Prepared = True

.Parameters(0).Value = sendParam1

Call .Execute()

End With

An example of a stored procedure follows.

ALTER PROCEDURE "DBA"."testproc1" (in param1 varchar(30))

BEGIN

message 'in Parameter [' + param1 + ']';

END

already has a pointer to the table definition, acquiring an exclusive lock is no longer done.

Now, the data conversion error will be reported, but an error that the connection has been closed will not.

CTable<CAccessor<CSimpleAccessor> > dbSimple;

hr = dbSimple.Insert(1);

This problem has been fixed.

Note, new databases will have the corrected procedure, but to update existing databases, run the Upgrade utility dbupgrad.

- an error was handled on upload on the last table

- a download cursor was opened for the first time on any table

- a subsequent sync used the download table script without having an upload error handled, and there were multiple rows to download

This is now fixed.

- DBISQL was running as a windowed application

- not all the connection parameters were supplied on the command line, or in the SQLCONNECT environment variable, to complete the connection

- the "Connect" dialog was opened automatically when DBISQL started

- the connection was to a slow server

This has been fixed.

If a QAnywhere generated message was sent to a JMS client via the QAnywhere JMS connector, any ReplyToAddress specified on the QAnywhere message (using setReplyToAddress() in C++ or the ReplyToAddress property in C#) was mapped over to the JMS property QAReplyToAddress. This differed from the documentation, which indicated that the ReplyToAddress was mapped to the JMS property ias_ReplyToAddress. Similarly, the InReplyToID specified on the QAnywhere message (using setInReplyToID() in C++ or the InReplyToID property in C#) was mapped over to the JMS property QAInReplyToID rather than the documented ias_InReplyToID.

If a JMS message was sent to a QAnywhere client via the QAnywhere JMS connector, the JMSReplyTo specified on the JMS message (using setJMSReplyTo()) was mapped over to the QAMessage property ias_ReplyToAddress rather than to the actual ReplyToAddress of the message. Hence the ReplyToAddress could not be accessed by calling getReplyToAddress() in C++ or the ReplyToAddress property in C#. Similarly, the JMSCorrelationID specified on the JMS message (using setCorrelationID()) was mapped over to the QAMessage property ias_InReplyToID rather than to the actual InReplyToID of the message. Hence the InReplyToID could not be accessed by calling getInReplyToID() in C++ or the InReplyToID property in C#.

This problem has been fixed to behave as documented.

Now, ULISQL will use shfolder.dll if the entry point is not found in shell32.dll.

Only the name and type was specified for new columns when using Fill() with MissingSchemaAction.Add. AllowDBNull, AutoIncrement, ReadOnly, Unique were set only when Fill() with MissingSchemaAction.AddWithKey was used. Column properties are now set for all new columns.

When FillSchema() or Fill() with AddWithKey was used with a non-empty DataTable and the columns did not match the source properties with respect to AllowDBNull, AutoIncrement, and Unique, DataException, ArgumentException, and InvalidConstraintException might have occurred. Existing columns are now left as the user specified them.

If a data source column was flagged as autoincrementing, the DataColumn might have had its type corrupted, possibly resulting in a loss of data during Fill(). If the DataTable contained data, an ArgumentException was thrown, otherwise, an OverflowException might have been thrown during Fill(). Now exisiting columns do not have their properties modified by Fill() or FillSchema(). New autoincrementing columns of data source type SmallInt, Integer, BigInt, Decimal are flagged as AutoIncrement and may have their type sized up to prevent Fill() errors. Other new autoincrementing columns (in particular UnsignedBigInt) are not marked in the DataTable as AutoIncrement since .NET does not support AutoIncrement with non-signed integer types.

For example:

CREATE TABLE P(

one CHAR(4) NOT NULL,

two INTEGER NOT NULL PRIMARY KEY

);

CREATE TABLE F(

two INTEGER NOT NULL REFERENCES P(two),

);

CREATE TABLE R(

X INTEGER NOT NULL,

Y integer not NULL

);

CREATE PUBLICATION "Pub_F"

(

TABLE F WHERE two = any(select R.X from R where R.Y > 9999 )

);

SELECT PP.two FROM P as PP KEY JOIN F as FF where PP.two < 100

Would have reported Error -142: "Correlation name FF' not found"

The proper way to upgrade a non-ASA MobiLink consolidated database of version 6.0.x is to run the appropriate setup script from the MobiLink\setup directory, followed by the SQL script from the appropriate MobiLink\upgrade\6.0.x subdirectory.

The proper way to upgrade a non-ASA MobiLink consolidated database of version 7.0.x or greater is to run one SQL script from the appropriate MobiLink\upgrade\_version_ subdirectory for the version that is being upgraded from.

There are a few special rules for DB2. Consult the documentation for details, under:

What's New in SQL Anywhere Studio

Upgrading Software and Databases

Upgrading MobiLink

Upgrading your consolidated database

SELECT DISTINCT x1, x2, ..., xm FROM <table-expression>

could have resulted in the UltraLite analyzer generating more code than was necessary. In cases where the number of columns in the select-list was large (for instance 20), the generated function could have been too large for the CodeWarrior compiler to handle. This has now been fixed.

1. creating an UltraLite database would have failed with the error:

"UltraLite database creation failed. Error: cannot specify additional arguments"

2. generating MobiLink scripts, or ASA SQL, for that database would have failed with the error:

"Script generation failed. Possible installation or path issue. Error: only one XML file/sync block can be specified"

This problem has been corrected.

Now, unnamed columns are named "Column1", "Column2", etc.. As a workaround, columns may be named using aliasing in the SELECT statement.

Example (C#):

ULDataAdapter da = new ULDataAdapter( "SELECT MIN(SomeCol), MAX(SomeCol), SUM(SomeCol), AVG(SomeCol) AS Average FROM SomeTable", conn );

DataTableMapping dtm = da.TableMapping( "Table", "MyTable" );

dtm.ColumnMappings.Add( "Column1", "Min" );

dtm.ColumnMappings.Add( "Column2", "Max" );

DataSet ds = new DataSet();

da.Fill( dataSet );

// dataSet now has DataTable "MyTable" with columns "Min", "Max", "Column3", and "Average"

For example,

SELECT length( city ) AS city_length, count(*) FROM invoices GROUP BY city_length

was diagnosed as erroneous. This has been corrected so that alias names in the GROUP BY clause are now accepted.

ASA Error -823: OMNI cannot handle expressions involving remote tables inside stored procedures.

This problem has now been fixed so that these operators do now work in expressions involving remote tables.

An example:

CREATE TABLE R (

X integer not null,

Z integer not null,

Y integer NULL COMPUTE ((select count(*) from R as old where old.X = R.X))

)

select R.Z from R group by R.Z

This problem has been fixed. If the numeric value passed to ROUND() is a constant, the resulting data types percision is increased by one, (numeric(5,3) in the above example). If it is not a constant and the resulting value does not fit, then a SQLE_OVERFLOW_ERROR is generated if the option Convertion_error is set, otherwise NULL is returned.

-e opt1="val1";opt2=val2;...

even if all the extended options were valid. This problem is fixed now.

Error: File 'c:\program files\sybase\asa9\ce\arm.30\qastop.exe' not found.

This has been fixed.

Notes:

- if the user running dbsvc does not have Administrator privileges, then granting the privilege will fail. However, users without Administrator privileges cannot create services anyway

- if the -y option is used, then dbsvc will attempt to grant the "Login as a

service" privilege without prompting the user

For example, if the database was qanyserv.db, then the following command would fail:

dbunload -c "dbf=qanyserv.db;uid=dba;pwd=sql" -ar .

This problem has been fixed, but there are a couple work-arounds. Rather than using dbunload to upgrade a database from 9.0.1 to 9.0.2 one can use the tool dbupgrad. For example:

dbupgrad -c "dbf=qanyserv.db;uid=dba;pwd=sql"

If unload and reload of the database is required, then another work-around is to run dbunload, and then modify the reload.sql file to remove the reloading of rows into the tables dbo.ml_qa_repository and dbo.ml_qa_repository_props. These rows represent QAnywhere messages, so removing them will cause the reloaded database to lose those messages.

call sa_server_option('Request_level_logging','sql+plan+hostvars');

and a plan string in the request log exceeded approximately 325 bytes, then calling sa_get_request_times to process the resulting file may have resulted in the error:

Primary key for table 'satmp_request_time' is not unique

Whether or not the error was given depended on what other requests were active at the time the statement with the long plan was executed. The request log file will now be output correctly.

- The server could have autostopped a database when it should not have, or not autostopped a database when it should have.

- In rare timing dependent cases, the server could have deadlocked, asserted or possibly crashed, when a database was starting up or shutting down.

Also in rare timing dependent cases, the server could have asserted or possibly crashed if HTTP or HTTPS requests were made to a database that was starting up or shutting down.

A server crash at an inopportune moment could have resulted in a corrupt database. This was more likely to have occurred with 9.x servers, and with 8.x servers running 8.x databases. It was unlikely to have occurred with 8.x and earlier servers when running against 7.x or earlier databases.

This has now been fixed.

Now, for IN lists bigger than the above limit, the "IN list optimization" is not used, and a virtual table is not introduced. This may result in a sudden performance difference when the IN list size crosses this size threshold. It is not recommended to use very large IN lists.

The customer is requesting that this feature be added to the engine and not solely to dbisql since they use different means to administer users. We could capture and parse out the password from the "grant connect to..." out of the STMT_PREPARE and the STMT_EXECUTE_IMM and/or STMT_EXECUTE_ANY_IMM before it gets to the parser and then the logger.

Note, this problem was introduced in the following builds:

8.0.3 5128

8.0.2 4442

9.0.2 2528

9.0.0 1333

9.0.1 1887

The old behavior is now restored

1) Blobs were not being included in the estimate of the upload size.

2) When synchronizing a subscription for a user with more than one subscription, some operations were being included in the estimate that were not being uploaded.

On all 32-bit systems other than Windows, available address space is defined as 2GB less 256MB. On Windows, available address space is computed accurately on startup. Note

that each process on 32-bit Windows is given a total of 2GB of address space (that's an OS and 32-bit architecture limitation) except on Windows 32-bit Advanced Server, Enterprise Server and Datacenter Server, where they are given 3GB of address space in total provided "/3GB /PAE" is in the boot.ini and there is no more than 16GB of RAM installed. Again, these are OS and 32-bit architecture limitations. 32-bit programs running on Windows x64 Edition are given the full 4GB of address space. Available address space is then defined as total address space less address space in use by the database server at startup.

For AWE caches ('-cw' is on the command line), the definition of percentage notation has not changed and remains as a percentage of total physical memory on the system.

SELECT 1 FROM ( SELECT DT2.* FROM ( select 1 c ) DT1 ) DT2

should have given an error, since it occurred inside the derived table DT2. An error would only have been given if DT2 was specified with a column list. Now, an error will always be reported for this situation.

For example, the following might have caused such a deadlock.

select * from a,b where a.a2 = b.b2; //connection 1

select * from a,b where b.b2 = a.a2; //connection 2

This would only have occurred if there was no index or key on the columns a2 and b2. This would most likely have occurred on a multi-CPU machine. This has now been fixed.

For example, the following situation caused the problem:

set option public.ansi_update_constraints = 'off';

create table T1 ( a varchar(10), c varchar(10) compute ( a+'' ) );

create table T2 ( f int );

create table T3 ( z int );

select * from T3 left outer join

( T2 left outer join T1 on T2.f = T1.c )

on T3.z = T2.f

for update;

Result set not permitted in '<batch statement>'

The batch will now be executed correctly and its result set returned.

This is a further performance improvement to the issue originally addressed by Engineering Case 355123.

The workaround is to unload and reload the ASA database into a non-blank padded reference database.

Software\Sybase\Adaptive Server Anywhere\<version>\Conduit\<CRID>

where <CRID> is the creator ID that was used in an UltraLite Palm application on the remote. The "PluginDLL" value should be set to the plug-in DLL location.

The conduit plug-in DLL should have the exported function ULCondGetExtraStreamParms specified:

Prototype

bool ULCondGetExtraStreamParms( char * stream_parms, unsigned short buffer_len )

Parameters

stream_parms: Pointer to a buffer in which to return the extra stream parameters.

buffer_len: The length of *stream_parms in bytes.

Remarks

The function should return false on error; otherwise, return true on success. The extra parameters returned will be appended to the existing stream parameters in the UL database.

Further, if the lateral derived table was flattenable and there was a reference from the null-supplying side of the outer join to a table outside the lateral derived table, then the target of the reference needed to be treated as part of the preserved side. This was not happening, Therefore, the optimizer could, in some cases, choose an incorrect plan with the table in question appearing above the outer join rather than on its preserved side.

Both these issues have now been fixed.

As well, if a view was created with a "select * ...", the view definition (column VIEW_DEF in SYSTABLE) was unparsed without the space between the "select" and "*"; e.g. "select* ...".

Neither of these errors caused problems in ASA, but did cause problems for Powerdesigner. Both issues have been fixed.

DBUNLOAD -d -ac <connection-parameters-to-new-database>

The Unload utility would have attempted to create a dbspace for the new database and would have reported an error if the dbspace already existed. Now, dbunload will not attempt to create dbspaces when reloading into another database if the -d command-line option is used.

select xmlelement( name p, cast( html_encode('''') as xml ) )

However, the usertype "xml" is defined as long varchar which happens to be the return type of html_encode(). Therefore, the cast is silently dropped and the quoting happens anyway. This has been fixed.

UltraLite tries to optimize ABSOLUTE fetches by replacing them with RELATIVE ones (if it thinks it will be more efficient). The algorithm first verifies that no tables in the cursor have been modified, otherwise it does not attempt the optimization. The problem occurred due to the synchronization code not marking the tables as modified. Now they are.

Also, dropping a user-defined type required the name to have matching case, in a case sensitive database. This is no longer required.

Note, this same problem affected the QueryEditor in Sybase Central, and is fixed as well

Note, the server used with dbunload -ar must be on the same machine where dbunload is run, but dbunload -an can now be used against a remote server.

For example, the following INSERT could have generated the incorrect values:

insert into R

select 0-row_num, number(*)

from rowgenerator

where row_num <= 3

order by 1

For C#, the following enumeration type was added:

public enum QueueDepthFilter {

/// <summary>

/// Count both incoming and outgoing messages. System messages

/// and expired messages are not included in any queue depth

/// counts.

/// </summary>

ALL,

/// <summary>

/// Count only incoming messages. An incoming message is defined

/// as a message whose originator is different than the agent ID

/// of the message store.

/// </summary>

INCOMING,

/// <summary>

/// Count only outgoing messages. An outgoing message is defined

/// as a message whose originator is the agent ID

/// of the message store, and whose destination is not the

/// agent ID of the message store.

/// </summary>

OUTGOING

};

For C#, the following methods were added to QAManagerBase:

/// <summary>

/// Returns the total depth of all queues, based on a given filter.

/// <param name="filter">a filter indicating incoming messages,

/// outgoing messages, or all messages</param>

/// <exception cref="iAnywhere.QAnywhere.Client.QAException">

/// if there was an error

/// </exception>

/// <returns>the number of messages</returns>

/// </summary>

public int GetQueueDepth( QueueDepthFilter filter );

/// <summary>

/// Returns the depth of a queue, based on a given filter.

/// <param name="filter">a filter indicating incoming messages,

/// outgoing messages, or all messages</param>

/// <param name="address">the queue name</param>

/// <exception cref="iAnywhere.QAnywhere.Client.QAException">

/// if there was an error

/// </exception>

/// <returns>the number of messages</returns>

/// </summary>

public int GetQueueDepth( string address, QueueDepthFilter filter );

Similarly, for C++, the following methods were added to QAManagerBase:

/**

* Returns the total depth of all queues, based on a given filter.

* @param filter a filter indicating incoming messages, outgoing messages,

* or all messages

* @see QueueDepthFilter

* @return the number of messages, or -1 if there was an error

*/

virtual qa_int getAllQueueDepth(qa_short filter) = 0;

/**

* Returns the depth of a queue, based on a given filter.

* @param filter a filter indicating incoming messages, outgoing messages,

* or all messages

* @see QueueDepthFilter

* @param dest the queue name

* @return the number of messages in the queue, or -1 if there was an error

*/

virtual qa_int getQueueDepth(qa_const_string dest, qa_short filter) = 0;

For example:

create procedure P1 ()

begin

declare var varchar(128);

select first table_name into var from systable;

end

then

select * from P1()

would have crashed the server.

when attempting to install a new version of a Java class previously removed. This has been fixed.

For example, if Visual Studio .NET and Visual Studio .NET 2003 were installed and then Visual Studio .NET 2003 was removed, the UL.NET installer would still have thought 2003 was installed.

The changes for Engineering Case 368160 attempted to address this problem, but the failure could still have occurred as the UL.NET installer must use two different strategies to integrate into Visual Studio, and only one was correctly fixed. Now both are fixed.

Two changes were made to address this problem:

1. The "Show multiple result sets" setting was being ignored. Now the setting is respected, so by default, only the first result set is displayed.

2. Even if the "Show multiple result sets" setting is on, only the first 10 result sets will be displayed.

When trying to fetch data from a datawindow you get the error:

"S1090 - Invalid String or buffer length".

This issue doesn't appear in any combination of PB 1.5.2 and 2.0 with ASA 9.0.1. But with 9.0.2, the issue remains.

In addition, the "Unload Data..." menu item is now only available for base tables. It was previously available to proxy tables and global temporary tables as well, which was incorrect, as the Unload Data dialog can only be used to unload data from a base table.

For example, both of the following are now allowed:

dblsn.exe -x tcpip(host=localhost)

dblsn.exe -x tcpip{host=localhost}

For example, if the following SQL was executed:

grant connect to hurz identified by 'sql';

create service test type 'html' user hurz as select * from systable;

grant connect to newdba identified by 'sql';

grant DBA to newdba;

then both hurz and newdba (as well as any other user with DBA authority) could have connected to the service test.

For example, for the following request:

http://localhost/foo?id=1&id=2&id=3&id=4&id=5

http_variable() would return the values for id in the following order:

1, 5, 4, 3, 2

The function was adding variables (and their values), either after the first occurrence of the variable, or at the end of the list. Now, variables and their values are added after the last occurrence of the variable, or at the end of the list.

"Unable to open temporary file "MLSY\xxxx" -- No such file or directory"

and refused to start. This problem is now fixed.

This problem has been fixed.

- more than one window had been opened by clicking the "Window/New Window" menu item

- more than one window was closed in quick succession

This was was more likely to have occurred on busy machines. It has now been fixed.

Note, this problem only affected servers on Windows and Netware systems, it did not affect servers running on Unix systems.

While the problem exists on all platforms, the crash has only been seen on AIX systems.

For example, given a server started with the following command line options:

-xs http,https(port=443;dbn=db1),https(port=444;dbn=db2) db1.db db2.db

If "sec" was a secure service in db2, then HTTP requests for "/db2/sec" would have been redirected (via the 301 status code) to port 443 rather than 444. In most cases, this would result in a "404 Not Found" status, but could possibly have executed the wrong service. In this case, if db1 had a service called "db2/sec".

- The WAIT_FOR_COMMIT option must have been on as a temporary option (not a user-level option)

- A connection must have started a transaction

- A subsequent checkpoint must have occurred while the transaction started was still active (ie. had not been committed or rolled back).

- Additional operations on the transaction must have been performed that *required* the WAIT_FOR_COMMIT option and must have been written to disk (likely due to a commit)

- The server must have gone down dirty before any other checkpoint occurred.

This has been fixed, the Wait_for_commit option is now always on during recovery.

Note that the same problem could have occurred in the Sybase Central Index Consultant as well, although it is less likely. It has been fixed there as well.

For example, instead of generating a syntax error, the following will cause table 'bar' to contain one row:

CREATE SERVER asademo CLASS 'asaodbc' USING 'driver=Adaptive Server Anywhere 9.0;dbn=asademo';

CREATE TABLE foo(c1 int) AT 'asademo...';

create table bar( c1 int primary key );

insert into foo values(1);

insert into foo values(1);

insert into foo values(1);

commit;

insert into bar on existing skip select * from foo;

select * from bar

Note that in addition to getting a new server, a new version of jcatalog.sql will have to be run against the database with the UNIQUEIDENTIFER column, to fully implement this fix.

console. This has been fixed.

Note that at the time of this fix, NetWare 5.1 SP 8 was still in beta.

select

sum(emp_id) over (order by emp_fname, emp_lname),

sum(emp_id) over (partition by emp_lname),

sum(emp_id) over (partition by emp_lname, emp_fname)

from employee

Previously, it would only have used one. This has been fixed.

This was only likely to cause problems when connected to a database using jConnect, but it may possibly have affected the ODBC and ESQL drivers as well, and was most likely to affect servers running on Chinese and Japanese non-Windows OS versions.

For example, the following would have generated incorrect results:

BEGIN

DECLARE LOCAL TEMPORARY TABLE t ( row_number INTEGER );

INSERT t SELECT 2 + NUMBER(*) AS row_number ORDER BY dummy_col;

SELECT * FROM t;

END;

A sample code fragment follows:

DataTable Table = new DataTable(textTableName.Text);

OleDbDataAdapter adapter;

OleDbConnection connection = new OleDbConnection(textConnectionString.Text);

using (connection)

{

try

{

connection.Open();

adapter = new OleDbDataAdapter("select * from dba." + textTableName.Text + " where 1=0", connection);

adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

adapter.Fill(Table);

listBox1.Items.Clear();

foreach(DataColumn col in Table.PrimaryKey)

{

listBox1.Items.Add(col.ColumnName);

}

}

catch (Exception ex)

{

MessageBox.Show(ex.Message);

}

}

The DataTable PrimaryKey property is an array of columns that function as primary keys for the data table. This problem has been fixed.

One of the elements that ADO.Net uses in deciding whether a column belongs in this set is the column metadata rowset.

IColumnsRowset::GetColumnsRowset - Returns a rowset containing metadata about each column in the current rowset. This rowset is known as the column metadata rowset and is read-only. The optional Metadata Column DBCOLUMN_KEYCOLUMN is described to contain one of the values VARIANT_TRUE or VARIANT_FALSE or NULL.

VARIANT_TRUE � The column is one of a set of columns in the rowset that, taken together, uniquely identify the row. The set of columns with DBCOLUMN_KEYCOLUMN set to VARIANT_TRUE must uniquely identify a row in the rowset. There is no requirement that this set of columns is a minimal set of columns. This set of columns may be generated from a base table primary key, a unique constraint or a unique index.

VARIANT_FALSE � The column is not required to uniquely identify the row.

This column used to contain VARIANT_TRUE or VARIANT_FALSE. It now contains NULL since OLEDB cannot correctly set the value. As a result, ADO.Net uses other means for determining which columns belong in the PrimaryKey columns property.

Example:

declare local temporary table temp1(c1 int);

comment on table temp1 is 'my comment';

Now an error is returned when attempting to add a comment to a local temporary table.

For example:

"Running on Win98 build 67766222"

The correct OS build number is now displayed.

"[ODBC DB2 Wire Protocol driver]Unexpected Server Reply (Conversational Protocol error code: 146C (PRCCNVCD)). [-1217] ['HY000']"

This problem has been fixed in version 4.20.00.115(B0080,U0069). To upgrade this driver, the following files should be changed:

wqdb219.dll

wqdb219r.dll

This fix is for Windows systems only.

- If the extension (.udb or .pdb) of the database file was missed, it was not automatically added. Now a connection will be attempted with the file name as specified, and then with a .udb extension, and then finally with a .pdb extension.

- The browse button would initially display only .udb files. Now both .udb and .pdb files will be displayed.

- It was not possible to scroll the Filename combo box. This has been fixed.

1) the online transaction that contains the last replication/synchronization offset, had been renamed, say to X;

2) the offline log X contained transactions that started from an early log, say Y; and

3) the log Y contained transactions started from an earlier log, say Z.

Transaction log Z may have already been deleted. This problem is fixed now.

"[ODBC Sybase Wire Protocol driver][SQL Server] Arithmetic overflow during implicit conversion of NUMERIC value '-2.200000000' to a NUMERIC field."

The problem is due to the maximum precision of a numeric column on ASE server. This has been fixed in ASE 12.5.0.3. Version 4.20.00.63 of the ASE driver works around the problem. To upgrade to this driver, the following files need to be changed:

wqdb219.dll

wqdb219r.dll

wqase19s.dll

For example:

"select sum(T.x) from T"

This has been fixed. Depending on the desired cursor type, the server may change the cursor type accordingly, and return a warning. For example, under ODBC, if the desired cursor type for a cursor of a single groupby query is KEYSET-DRIVEN, the actual cursor type is determined to be STATIC.

For more information on cursor sensitivity please see:

ASA Programming Guide

Using SQL in Applications

Adaptive Server Anywhere

Cursor sensitivity overview

"Unable to convert the string ... from the system collation to the database collation".

The MobiLink client will no longer issue this warning if the operating system character set and the database character set are very similar (for example cp932 and sjis).

To help download scripts avoid this problem, the MobiLink server now sets the next "last download time" to the start time of the oldest open transaction. This ensures that all changes will be picked up in the next synchronization that occurs after they are committed. The Oracle account used the by the MobiLink server must have permission for the V_$TRANSACTION Oracle system view, otherwise the following warning message will be displayed:

Unable to access ORACLE table 'v_$transaction'. Access is needed in order to ensure no data is missed if using timestamp-based downloads.

Note that only SYS can grant this access, and it must be granted for the base V_$TRANSACTION view (V$TRANSACTION is a synonym for the V_$TRANSACTION view, and hence cannot have permission granted on it directly). The syntax is:

grant select on SYS.V_$TRANSACTION to <user_name>;

Workaround:

For earlier versions of the MobiLink server, equivalent functionality can be achieved with the following procedure:

The simplest way to make sure that all changes are downloaded, assuming a trigger is used to maintain a last_modifed marker for each row, is to change the next "last download time" to be the earliest start time of the open transactions. MobiLink offers the modify_next_last_dowload_timestamp connection event for modifying the next "last download time", but it is invoked at the end of the download transaction, and the new value needs to be determined at the beginning of the download transaction. So the value needs to be determined in the begin_download connection event, and used in the modify_next_last_download_timestamp event. In this example the value is stored in a package variable between the events.

Here are detailed instructions:

1. Ensure that the MobiLink connection (i.e. Oracle user) has permission to access the V$TRANSACTION Oracle system view. Only SYS can grant this access, and it must be granted for the base V_$TRANSACTION view (V$TRANSACTION is a synonym for the V_$TRANSACTION view, and hence cannot have permission granted on it directly):

grant select on SYS.V_$TRANSACTION to <user_name>;

It is essential for the user to have the permission directly, rather than through a role, because a stored procedure does not have role privileges.

If GRANT access is not wanted on the whole view, instead define a view that only allows access to the required start_time column, as in the following example:

create view transaction_start_times

as

select start_time from v$transaction;

grant select on transaction_start_times to public;

2. Create a package with a variable to hold the timestamp value (use DATE type instead of TIMESTAMP for Oracle 8), and procedures to set and get it (from MobiLink events):

create or replace package SyncVars

as

procedure SetDownloadTimestamp;

function GetDownloadTimestamp return timestamp;

end SyncVars;

create or replace package body SyncVars

as

DownloadTimestamp timestamp;

procedure SetDownloadTimestamp

as

begin

select nvl( min( to_timestamp( start_time, 'mm/dd/rr hh24:mi:ss' ) ), localtimestamp )

into DownloadTimestamp

from v$transaction;

end SetDownloadTimestamp;

function GetDownloadTimestamp return timestamp

as

begin

return DownloadTimestamp;

end GetDownloadTimestamp;

end SyncVars;

3. Add a begin_download connection script to determine the timestamp value:

exec ml_add_lang_connection_script( 'version?', 'begin_download', 'SQL','begin SyncVars.SetDownloadTimestamp(); end;' );

4. Add a modify_next_last_download_timestamp connection script to replace the timestamp that will be used by MobiLink:

exec ml_add_lang_connection_script( 'version?','modify_next_last_download_timestamp', 'SQL','begin ? := GetDownloadTimestamp(); end;' );

Then the table download_cursor scripts just need to use the usual 'WHERE last_modified >= ?' clause, since the value that MobiLink passes in for the question mark will be the timestamp value determined in the previous synchronization.

1) a blue screen error 76 with text "Process has locked pages",

2) event log messages indicating that a "driver is leaking locked pages",

3) ASA fatal errors indicating the reads or writes were failing with the error code 1453 (ERROR_WORKING_SET_QUOTA), and/or

4) other generic fatal read/write errors

Microsoft has fixed this problem in Service Pack 1 of Windows 2003. It is our understanding that no fix will be made by Microsoft prior to the release of Service Pack 1.

In order to prevent these serious consequences the database server can no longer be started on Windows 2003 pre-SP1 while using AWE. Any attempt to do so will result in a startup error "Windows 2003 does not properly support AWE caching before Service Pack 1"

At the time this description was written there was no existing Microsoft Knowledge Base (KB) article describing this issue.

CREATE FUNCTION "DBA"."MachineName" (out @iMachine char(255))

returns integer external name

'Win32MachineName@C:\\path\\mytools.dll'

begin

declare @iMachine char(255);

call dba.MachineName(@iMachine);

select @iMachine

end

If the actual function was written to accept more than one argument (e.g., int Win32MachineName( char *p1, char *p2 ) ) and the function employed the "callee pops the arguments" protocol, then the stack pointer will be improperly aligned upon return to the caller (the server in this case). It will point to a memory location 4 bytes higher than where it should.

After the user function was called. the server checked, and corrected, the stack misalignment. There was, however, a small window of a few instructions where the stack pointer was misaligned and the server makes some other function calls. This resulted in the top of the stack being corrupted. In previous versions of the server, this did not result in any permanent harm. In more recent versions of the server, an important pointer is correpted, resulting in a crash.

Now, the server pushes and pops extra guard words on the stack so that these unused stack positions will be corrupted instead. The server also now checks for a serious stack underflow and will issue an assertion (103701 "Function parameter list mismatch") and stop rather than crash.

Note, this problem applied to the MobiLink synchronization server and SQL Remote for ASA as well, and have also been fixed.

1. There were at least two predicates with subqueries referencing the same table T, and T was also referenced in the main query block. In the example below, t3 is referenced in both subqueries and in the main query block.

2. There was an index on the outer reference columns used in the subquery. In the example below, t3 has an index on the column t2_id which is the outer reference column in both subqueries.

Example:

select * from t1 join t3 as t2 on t2.t2_id = t1.t1_id +1

where t1.t1_id between (select MIN(t3.t1_id) from t3 where t3.t2_id=t2.t2_id)

and (select MAX(t4.t1_id) from t3 as t4 where t4.t2_id=t2.t2_id)

This problem has been fixed by doing a periodic check that the MobiLink server is reachable. This check is done only after a connection failure has occurred. Once the server is reachable again, the check is discontinued. The QAnywhere Agent will not direct dbmlsync to begin the synchronization process if it is known that the MobiLink server is unreachable.

SET REMOTE ftp OPTION "Public"."invalid_extensions" = 'exe,pif,dll,bat,cmd,vbs';

Also, the handling of reply-to addresses through the JMS connector was changed to match the documentation.

This has been fixed.

A workaround is to unload without the -an option.

1. When importing ASCII or FIXED files into an existing table, the column data types were always being displayed as "VARCHAR" on the last page. Now, the actual column types are displayed.

2. When importing FIXED data in an existing table, if fewer column breaks were placed so that fewer columns were defined than appeared in the actual table, the preview would still have shown columns for all of the columns in the database table. This was incorrect, and clicking on these extra columns would have caused Interactive SQL to crash. These extra columns are now no long displayed.

3. If the Import Wizard was closed by clicking the close box, it could still attempt to import the data. Now, clicking the close box is synonymous with clicking the "Cancel" button.

The workaround is to remove the repeated item.

The following describes the new server behaviour:

1. When the row does not exist, the new row is inserted as per the

牋 usual rules of the INSERT statement.

2. If the row exists, and ON EXISTING SKIP is specified, no changes

牋 are made to the row.

3. If the row exists, and ON EXISTING UPDATE is specified, the row is

牋 updated as par the following rules:

牋 (a) All columns that have been explicitly specified in the

牋牋牋 INSERT statement are updated with the specified values.

牋 (b) Columns with defaults that are meant to be changed on an

牋牋牋 UPDATE are modified accordingly. These special defaults include

牋牋牋 "DEFAULT TIMESTAMP", "DEFAULT UTC TIMESTAMP", and

牋牋牋 "DEFAULT LAST USER".

牋 (c) Columns with other defaults are not modified unless some

牋牋牋 of these are explicitly mentioned with a non default

牋牋牋 value in the INSERT statement, in which case these columns

牋牋牋 are modified as par rule 3(a) above.

牋 (d) Computed columns are re-evaluated and modified using the new row.

牋 (e) Any other columns are left unchanged.

INSERT ... ON EXISTING UPDATE [ DEFAULTS [ ON | OFF ] ] ...

Not using the DEFAULTS clause is equivalent to DEFAULTS OFF.

For example, if asatest.db existed, running:

dbeng9 asatest.db" (the trailing quote is intentional)

would generate the error: "Unknown error (xxxxx)".

This problem has now been fixed so that unexpected file errors now display the error: "Could not open/read database file: <file name>"

As an example the following returns a "table not found error" when the table CT1 referenced in the check constraint is not visible to the user executing the LOAD TABLE statement:

grant dba,resource to dbowner

CREATE TABLE dbowner.CT1 ( c1 integer NOT NULL )

CREATE TABLE dbowner.T2 ( c1 integer NOT NULL, check(not c1 = any(select c1 from CT1) ) )

LOAD TABLE dbowner.T2 ... CHECK CONSTRAINTS OFF ...

Note the syntax "EXEC SQL DECLARE ... CURSOR FOR :stmt_num" is thread safe (and is not affected by this problem), while the syntax "EXEC SQL DECLARE ... CURSOR FOR statement_name" is not thread safe (and cannot be made thread safe).

select dateadd(ss,2147483647,'2005-02-03 11:45:37.027')

would have resulted in:

1937-01-16 08:32:**.***

This has been fixed, now, the result is:

2073-02-21 14:59:44.027

The Datediff() function also produced incorrect results when the difference was close to the maximum or minimum 32-bit signed integer values and the time unit was second. For example:

select datediff(ss,'2073-02-21 14:59:45.027','2005-02-03 11:45:37.027')

would have resulted in a range error. This has also been fixed, the result is now:

-2147483648

1 - During the last message synchronization, the server committed the messages from the client but the connection with the client dropped before the client could receive the acknowledgement of that fact.

2 - A new client useed the same message store id as a previous client message id that was abandoned.

The problem was with the QAnywhere system procedure for handling primary key conflicts, ml_qa_handle_error. If QAnywhere is being using with an ASE back-end, the QAnywhere server schema can be patched by dropping and re-creating the procedure from the fixed syncase.sql, (or syncase125.sql), script.

Any new QAnywhere ASE server schemas created with the fixed scripts will not have the problem.

A similar bug was also fixed where error messages could have opened behind the main window under some circumstances.

select dateadd(ms,2147483647,'2005-02-03 21:45:37.027')

would have resulted in:

2005-01-10 01:15:**.***

This has been fixed, now, the result is:

2005-02-28 18:17:00.674

The Datediff() function also produced incorrect results when the difference was close to the maximum or minimum 32-bit signed integer values and the time unit was milliseconds. For example:

select datediff(ms,'2005-02-28 18:17:00.675','2005-02-03 21:45:37.027')

would have resulted in a range error. This has also been fixed, the result is now:

-2147483648

To use this method:

1) An INSERT statement should be prepared using one of the Connection.prepareStatement() methods,

2) The parameters for the prepared insert statement should be set and batched as follows:

for( ... ) // for loop to batch n sets of parameters

{

stmt.set?( 1, ? ); // where stmt is the prepared insert statement from step 1

stmt.set?( 2, ? );

...

stmt.set?(m , ? ); // note that there are m parameters in the statement

stmt.addBatch(); // adds the set or parameters to the batch and moves to the next row of parameters

}

3) The batch should then be executed using the PreparedStatement.executeUpdate() method

It should be noted that only the PreparedStatement.addBatch() method is supported and that the PreparedStatement.executeUpdate() method needs to be called to execute the batch. None of batch methods for the Statement object (i.e. Statement.addBatch(), Statement.clearBatch(), Statement.executeBatch() ) are supported since these methods are completely optional and not very useful. For such static batches, it is best to call Statement.execute() or Statement.executeQuery() on a single string with the batched statements wrapped inside a BEGIN...END.

NOTE 1: BLOB parameters are not supported in batches

NOTE 2: String/Binary parameters are supported but the size of the string/binary parameter is an issue. By default the string/binary parameter is restricted to 255 characters or 510 bytes. The reason for the restriction is due to the underlying ODBC protocol and will not be discussed here. For futher information, it is best to view the documentation on passing arrays of parameters in ODBC. If, however, an application needs to pass larger string or binary parameters within a batch, then an additional method has been provided to increase the size of the string/binary parameter. Note that this method must be called PRIOR to the first addBatch() call. If the method is called after the first addBatch() call, then the new size setting will be ignored. Hence, when calling this extra method to change the size of a string/binary parameter, the application needs to know ahead of time what the maximum string or binary value for that parameter will be.

To use this extra method, you must modify the "code" above as follows:

// need to cast stmt to an IPreparedStatment object in order to change the size of string/binary parameters

ianywhere.ml.jdbcodbc.IPreparedStatement _stmt = (ianywhere.ml.jdbcodbc.IPreparedStatement)stmt;

// now change the size of string parameter 4 from the default 255 characters to 300 characters

_stmt.setBatchStringSize( 4, 300 ); // note string parameters are in "characters"

// change the size of binary parameter 6 from the default 510 bytes to 750 bytes

_stmt.setBatchStringSize( 6, 750 ); // note binary parameters are in bytes

for( ... ) // for loop to batch n sets of parameters where n should not be too large

{

stmt.set?( 1, ? ); // where stmt is the prepared insert statement from step 1

stmt.set?( 2, ? );

...

stmt.set?(m , ? ); // note that there are m parameters in the statement

stmt.addBatch(); // adds the set or parameters to the batch and moves to the next row of parameters

}

The maximum string/binary size of a parameter should be modified with caution. If the maximum is set too high, then the additional memory allocation cost *will* probably override any performance gained from using the batch. Also, there is a good chance the application will not know ahead of time what the maximum string or binary value for a particular parameter is. The recommendation then is to not change the maximum string or binary size of a parameter, but rather to use the batch method until a string or binary value larger than the current/default maximum is encountered. The application can at that point call executeBatch() to execute the parameters that are currently batched, then call the regular set and executeUpdate() method to execute until the large string/binary parameters have been handled, then switch back into batch mode when smaller string/binary parameters are encountered.

12345678-1234-1234-1234-1234567890ab

With the option set to OFF, the hyphens will be omitted from the string.

Also, when converting a string to a uniqueidentifier value, the server now supports UUID strings both with and without hyphens.

Software installed on this platform would previously have failed to install with the message: "The program you have installed may not display properly because it was designed for a previous version of Windows Mobile Software". The install has been updated to suppress this warning. Any software which supports a version of Windows CE earlier than 4.21 will still issue this error. The server was also updated to support screen resizing.

This is a summary of UltraLite clarifications for the Palm OS platform.

A. Database

An internal file volume may also be present on some NVFS devices, such as Tungsten T5 handhelds, which can be accessed and used similar to an SD slot. Therefore, ULEnableFileDB is called to store UltraLite databases on either an internal drive (built-in storage card) or external slot (expansion card). Furthermore, the existing connection parameter palm_db=CRID:n is used to specify the data store on either an internal drive or external slot, where n is the volume ordinal from enumerating the mounted volumes, and the default value of n is 0. Tungsten T5, for example, has two volumes, the internal file volume and the SD slot volume. If the device cannot locate a volume with the user-specified volume ordinal, the SQLCODE -82 is reported when opening the database. ULEnablePalmRecordDB is called to store record-based UltraLite databases.

B. Synchronization Conduit

The conduit synchronizes the first UltraLite database which is found on the device. The database lookup sequence is first the record storage, then the file system including internal drive and external slot. The UltraLite database which is intended to be synchronize should be made available as the first one found in that sequence. The conduit has been designed to work like this, since the file-based data store support was first added to the Palm platform.

For example:

begin

declare local temporary table t(c varchar(32767));

declare rv varchar(32767);

insert into t values(repeat('*',5000));

select c into rv from t;

truncate table t;

select lcase(rv) from dummy;

end;

A work around is to concatenate a zero-length string to column value, i.e. use "select c || '' into rv from t" in the example above.

Diagram:

CLIENT <----HTTPS----> PROXY <----HTTPS----> IIS <-----> Redirector <-----> MobiLink

Workarounds:

1) set the option USER_ESTIMATES to DISABLED ( highly recommended ); OR

2) rewrite the KEY JOIN into a JOIN with an ON clause, which must contain the predicates of the form 'primary key column = foreign key column' for all primary key/foreign key columns.

Also, the INTTOHEX function now permits UNSIGNED BIGINT arguments.

For example:

select dateadd(hour,365*24*7923,'0001-01-01 21:45:37.027'),dateadd(hour,69399300,'0001-01-01 21:45:37.027')

would have resulted in:

'****-09-18 00:00:37.027' '9998-07-01 00:00:37.027'

This has been fixed. Now, the results are:

'7918-09-29 00:00:37.027' '7918-01-15 00:00:37.027'

Similarily, the Datediff() function produced incorrect results when the time unit was milliseconds, minutes, hours, days, or weeks.

For example,

select datediff(minute,'2005-02-03 21:45:37.027','6088-02-26 23:52:37.027')

resulted in a range error. This has been fixed. Now, the result is

2147483647

Now, extra information has been added to the message field of the exception thrown from Connection.Synchronize(). If a download UNIQUE constraint occurred inside UltraLite runtime, the conflicting table's name is added to the error parameters. ULPod will append the first three error parameters to the end of the SQLE_xxx" string, separated by ":". For example, the following message field of a synchronization exception "SQLE_SERVER_SYNCHRONIZATION_ERROR:-10013" tells that the MobiLink server error code is -10013.

For example:

select dateadd(day, 2250318 , '2005-02-16') would have returned 0000-01-00'

This has been fixed, an error message will now be returned.

Windows. XP SP1 Build 5.1.2600

- an autostarted database and server was stopped due to a disconnect, and the database had a page size of 2K or greater

- then a new connection was made immediately, that autostarted a server and the same database

- virus scanning software was being used

The server was ignoring errors when attempting to determine the page size of databases during a server autostart, and using a 1k page size if there were errors. Now the server correctly handles these errors and fails the server autostart.

This has been fixed so that the autostart either succeeds or returns the "Specified database file already in use" error.

liveness_timeout=N

where N is the number of seconds for the timeout. The default is to have no timeout (N=0). The synchronization will fail if the client waits N seconds without any data from MobiLink server. Users must be careful when setting this value. The value set should be short enough to timeout early enough for an impatient user, but set long enough for the longest delay between the MobiLink server receiving the upload and sending the download to the client. Note, if a synchronization is aborted due to a timeout, subsequent synchronizations should not be affected -- unless the MobiLink server is still processing the previous synchronization, in which case a "user already synchronizing" error will occur.

SQLE_CONVERSION_ERROR: Cannot convert System.Decimal to a Decimal

This has now been fixed.

- the CREATE/ALTER SYNCHRONIZATION USER statement