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

select hextoint('fffffffffffffff7');

This problem has been fixed. The server will now consistently return -9 for the result.

In furthering compatibility with Adaptive Server Enterprise, the following examples now return errors, since they contain invalid hexadecimal characters.

select hextoint('ffffJackf7')

This problem would have been noticed in the following scenario:

When the QAnywhere Agent was starting up while messages were being put into the message store, the QAnywhere Agent would have failed to start with the error:

E. 05/27 09:12:50. InternalError: User 'QA_USER' has the row in 'ml_qa_repository_content_client' locked

As well the problem would have occurred when the QAnywhere Agent was used to initialize a message store with store ID 'xyz' (-si -id xyz), and then the QAnywhere Agent was started with a different store ID (-id abc), the following error should have been given, but was not:

E. 05/31 12:05:29. QAnywhere Agent cannot be started with a client message store ID different than xyz with this message store.

This has been fixed so that now the message store ID is set, if it is specified, when the message store is initialized.

For example:

select 100000 * 100000

Previously, this returned an error message "Value 1410065408 out of range for destination". Now, the error message is the following: "Value 100000 * 100000 out of range for destination".

Further, after this change string values included in conversion or overflow error messages are enclosed in single quotes. If the string is truncated, an ellipsis (...) is used to indicate this.

For example:

select cast( '0123456789012345678901234567890123456789' as int )

would have given the error message: "Value 01234567890123456789012345678 out of range for destination" Now the error message is: "Value '0123456789012345678901234567890123...' out of range for destination". Similarly, an ellipsis is now used when printing binary values.

When NCHAR values were printed for error messages or plain text (explanation(), plan(), or graphical_plan()), the text was not represented correctly.

For example:

select cast( unistr('\u00df') as int )

would have caused the error message: "Cannot convert '脽' to a int". The text in the message was the result of cast( cast( unistr('\u00df') as binary ) as char). Now, the error is: "Cannot convert '�' to a int". The error message is now formed by converting from NCHAR collation to the database CHAR collation. If there are characters that can not be represented in the CHAR collation, they are replaced with substitution characters.

Also, when certain values were included in the result of a graphical_plan(), they could have generated an invalid XML result. For example, the following query previously generated an 'Invalid plan' error: select 'a\x1ab'. Characters that contain a byte value < 0x20 are now escaped and printed as '\xHH' where HH is the two-digit hex code for the byte. For example, in the "Select list" section of the graphical plan, the escaped text is now shown.

- REAL values converted to BINARY(3) or shorter

- DOUBLE values converted to BINARY(3) or shorter

- exact numerics converted to BINARY where a leading byte is truncated and the byte value is not zero

When converting exact numerics (bit, tinyint, smallint, unsigned smallint, int, unsigned int, bigint, unsigned bigint, numeric) to binary, the numeric value is first converted to one of the following types: INT, UNSIGNED INT, BIGINT, UNSIGNED BIGINT. If the target binary is smaller than 4 bytes for INT/UNSIGNED INT or smaller than 8 bytes for BIGINT/UNSIGNED BIGINT, then the most significant bytes of the value are truncated. For example:

SELECT CAST( 0x12345678 AS INT ) ival,

CAST( ival AS BINARY(3) ) bval

returns 0x345678 for bval. After this change, an error is raised if one of the truncated bytes is non-zero and the string_rtruncation option is on.

Further, when converting a too-long binary string to a number, errors are generated as follows:

- for REAL, if the binary string is not 4 bytes long

- for DOUBLE, if the binary string is not 8 bytes long

- for BIGINT/UNSIGNED BIGINT, if the binary string is longer than 8 bytes and a non-zero byte in the prefix would be truncated

- for INT/UNSIGNED INT, if the binary string is longer than 4 bytes and a non-zero byte in the prefix would be truncated

For example, the following generate errors:

CAST( 0x123456 AS REAL )

CAST( 0x1234567800 AS REAL )

CAST( 0x1234567800 AS INT )

but, the following do not generate errors:

CAST( 0x12345678 AS REAL )

CAST( 0x0012345678 AS INT )

CAST( 0x0000000000123456789abcdef0 as UNSIGNED BIGINT )

Windows platforms were not affected, since the length returned for 'CompactPlatformVer' is much less than 40 characters.

For example, the following query could cause the above problem:

select *

from (

select t1.a , (select t2.a from t2 where t2.b =t1.a) as x

from t1

order by t1.a

) v2

This error has been fixed. As a consequence, the results for unflattened derived tables are now Insensitive in Keyset-driven cursors.

- in a MESSAGE or PRINT statement

- in a RETURN statement of a function or procedure

- in a time/delay expression in a WAITFOR statement

- in an offset expression of a FETCH statement

This has been fixed so that the server now correctly returns the error "OMNI cannot handle expressions involving remote tables inside stored procedures"

QAnywhere makes no guarantees that a message scheduled for expiration will not be delivered, however, QAnywhere makes a best attempt to expire messages as they are scheduled. In this case, QAnywhere missed an opportunity to expire messages. This has been fixed so now, if a message was part of a previous failure to transmit messages, that will not stop the subsequent expiring of the message.

- if the request is for '/' then choose the 'root' service if it is defined

- first check for a service name for the entire URL

- interatively chop off one level from the URL at a time until a defined service is found with URL ON|ELEMENTS setting.

- if no service is found using the above, then select the 'root' service if it is defined with URL ON|ELEMENTS

For example:

If the request is for /a/b/c then the server will first look for a service named 'a/b/c', then 'a/b', and finally 'a'. With this fix, the engine will now also look for the 'root' service.

The latter three (a/b, a, and root) must all have been defined with URL ON or URL elements in order to be chosen in this situation.

Note that by defining a root service with URL ON|ELEMENTS, this service becomes a 'catch-all' service that will be used to process all requests (for the selected database) that do not match any other defined service.

character

dec

options

proc

reference

subtrans

These words are synonyms for other reserved words, and have now been added to the list returned by SQLGetInfo( SQL_KEYWORDS ).

(e.g. one had a single byte collation and the other one a multi-byte collation).

This has been fixed so that now the statistics of binary columns are only loaded if both databases have the same collation.

For example:

a wrapper function definition of

CREATE FUNCTION MyMeth (IN arg1 INT, IN arg2 varchar(255),IN arg3 INT )

RETURNS Int

EXTERNAL NAME 'TestClass.MyMethod(ILjava/lang/String;I)I'

LANGUAGE JAVA;

would have resulted in the error

Procedure 'TestClass.MyMethod(ILjava/lang/stringI)I' not found.

where as

EXTERNAL NAME 'TestClass.MyMethod (ILjava/lang/String;I)I'

would have worked. This has been fixed so that a space is no longer required between the method name and the left parenthesis.

-Delete rules specified and "Save"'ed using the "Server Delete Rules" tab now persist over a restart of the QAnywhere server.

-When creating a message using the "New Message" dialog, when specifying a custom boolean typed property on the "Properties" tab, only the value "0" could be entered to represent the false value. This was confusing, as the value was displayed as "false". Now the value "0" or "false" can be entered to represent the false value.

-When creating a message using the "New Message" dialog the "Content" tab now allows the compression level that should be applied to the content to be specified.

-When viewing a message in the right-hand panel using the "Content" tab, if the message was compressed, then the content would have been displayed rendering the compressed data as characters. Content is now displayed correctly even if the data is compressed.

-If a QAnywhere connector took more than 30 seconds when started using the "Start" menu item, the connector state would not have been updated on the right-hand summary panel. The latency has been changed to 60 seconds, and a new "Refresh" menu item has been added to allow refreshing of the current connector state at any time.

-Messages created and sent using the "Send message..." menu item would not trigger synchronization. Hence messages sent in this way would only be transmitted to the server when another synchronization trigger occurred. Now, sending such a message will act as a synchronization trigger. In fact, an additional menu item "Trigger message transmission" has been added to trigger message synchronization.

-The clients listed in the Clients tree view was only including clients that had sync'ed sometime in the past. If the client had messages destined for it, but had never sync'ed, it was not showing up in the tree view. The tree view has been fixed to include clients that have messages destined for it but have never sync'ed.

-The right-hand summary panel for a client displayed the client name on the far right. This formatting problem has been fixed.

Also, attempting to register a schedule containing a time between 12:00:00 and 12:59:59, would have resulted in the 12 being interpreted as 00. For example, START TIME '12:30:00' would have been interpreted as START TIME '00:30:00'.

Both of these problems have now been corrected.

As a simplified example consider the following:

drop table a;

CREATE TABLE a (

a1 INTEGER NOT NULL,

a2 INTEGER NOT NULL,

a3 INTEGER NOT NULL DEFAULT AUTOINCREMENT,

a4 INTEGER NOT NULL,

PRIMARY KEY ( a1, a2 ) );

INSERT INTO a VALUES( 1, 1, 1, 1);

INSERT INTO a VALUES( 2, 1, 2, 2);

commit;

INSERT a ON EXISTING UPDATE WITH AUTO NAME

SELECT 1 AS a1, 99 AS a2, 11 AS a4

union all

SELECT 2 AS a1, 1 AS a2, 88 AS a4;

The INSERT statement should

1. Insert a new rows into table a with PKEY <1,99>, and

2. Update the value of a.a4 to 88 in the row with PKEY <2,1>. The default column a.a3 in this row should now remain unchanged.

- The last time the client transmitted messages with the server it used the "scheduled" policy (using the qaagent command line argument -policy scheduled)

- The client message store had subsequently been deleted and re-created and then the client agent was started with the automatic policy (using the qaagent command argument -policy automatic).

This has been fixed. To work-around the problem, start the client agent with the "scheduled" policy. After a successful message transmission, the client can then be re-started with whatever policy is desired. The key is that after a client message store has been deleted and re-created, the first time the client agent should be started with the last policy it previously synchronized under.

CATALOGS: The CATALOGS rowset identifies the physical attributes associated with catalogs accessible from the DBMS. SQL Anywhere does not support the notion of catalogs as some other database systems do. With that in mind, the SQL Anywhere OLE DB provider will return a result set for CATALOGS containing all currently started databases. The following is an example.

CATALOG_NAME DESCRIPTION

AnotherSample c:\SQLAnywhere10\Samples\sample.db

demo c:\SQLAnywhere10\Samples\demo.db

The CATALOG_NAME column contains the database name. 燭he DESCRIPTION column contains the physical location of the database on the database server computer.

SCHEMATA: The SCHEMATA rowset identifies the schemas that are owned by a given user. 燭he following is an example of a SCHEMATA rowset 爎eturned by the SQL Anywhere OLE DB provider.

CATALOG_NAME SCHEMA_NAME SCHEMA_OWNER DEFAULT_CHARACTER_SET_CATALOG DEFAULT_CHARACTER_SET_SCHEMA DEFAULT_CHARACTER_SET_NAME

demo dbo dbo demo SYS windows-1252

demo GROUPO GROUPO demo SYS windows-1252

demo ml_server ml_server demo SYS windows-1252

demo rs_systabgroup rs_systabgroup demo SYS windows-1252

demo SYS SYS demo SYS windows-1252

The CATALOG_NAME column contains the name of the database to which you are currently connected. The SCHEMA_NAME and SCHEMA_OWNER columns contain identical values 爁or SQL Anywhere databases. The DEFAULT_CHARACTER_SET_CATALOG column always contains the name of the database to which you are currently connected since character sets are associated with databases. The DEFAULT_CHARACTER_SET_SCHEMA column is arbitrarily set to SYS since the character set in use for the database is not owned by anyone. The DEFAULT_CHARACTER_SET_NAME column contains the value of the "CharSet" database property

Note, to get this new functionality in existing databases, do the following:

9.0.2 - upgrade the databases by loading and running Scripts\oleschem.sql using Interactive SQL.

10.0.1 - run dbupgrad on each database, or connect to each database and run ALTER DATABASE UPGRADE. As an alternative, the databases can be upgraded by running Scripts\oleschem.sql using Interactive SQL.

11.0.0 - run dbupgrad on each database, or connect to each database and run ALTER DATABASE UPGRADE.

Some of the errors that might be characteristic of this problem are:

- Assertion 200602 - Incorrect page count after deleting pages from table 'table_name' in database 'database_name' - could occur during TRUNCATE TABLE or DROP TABLE.

- Database validation could report that an index has inaccurate leaf page count statistics.

- Database validation could report that a foreign key is invalid and that some primary key values are missing.

- Database validation could report that the rowcount in SYSTABLE is incorrect.

- Inconsistent row counts might be observed when querying the table sequentially versus via an index.

A workaround is to select:

"Deploy SQL Anywhere for Windows CE" from the Start Menu under: SQL Anywhere 8

ALTER TABLE t ALTER c1 int NULL

The server would have ignored the NULL, and left the column as "null not allowed". This has been corrected so that the server will now change the column specification to "null allowed" in this case. A work around is to not combine NULL with the datatype for the column as in:

ALTER TABLE t ALTER c1 NULL

This problem did not occur if the "Show multiple result sets" open was turned OFF (which is the default), nor did the problem occur if there was only one result set to export.

A crash was observed if the result sets were exported using the FIXED file format. Using other file formats typically resulted in the first result set being exported correctly, while the files for the subsequent result sets would be empty.

- The result set contained fewer rows than the configured maximum number of rows to fetch

- All column data was shorter than the configured truncation length.

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

This has been fixed, which also solves the following related problems:

- Under the conditions listed above, DATE columns were written in character fields in the .DBF file rather than using dBase/FoxPro "Date" fields.

- Warning messages about long column names being truncated would be displayed twice. Now they are displayed only once.

Note, his did not affect the processing of the Request-level log by the builtin procedures (ie. sa_get_request_times, sa_get_request_profile, etc.).

As an example, consider a local temporary table DELTEST that contains a single column, X. The statement

DELETE

FROM DELTEST

FROM DELTEST JOIN PRODUCT ON (DELTEST.X = PRODUCT.ID)

would cause the server to place long-term read locks on the product rows that successfully joined with the DELTEST table.

Note that this unnecessary locking occurs only with DELETE statements. INSERT and UPDATE statements are unaffected by this error.

non-deterministic.", even if the query had an ORDER BY clause. This has been fixed.

Note, this will not have happened if the complete query was forwarded to the remote

server, (ie. Full Pass-thru mode).

Also, when calculating the next execution time for rules with the ON { days of the week } clause, the scheduler mistakenly set the next day of execution to the 7th of the month, rather than 7 days in the future. This has been fixed as well.

The following conditions must have been met:

(1) there was at least one equality predicate between two expressions used in the ORDER BY clause

(2) the equality predicate appears in an ON condition of an outer join

(3) the expressions were both from the preserved side of the outer join

Example:

select T.* from

T left outer join R on T.e1 = T.e2 and T.e2 = T.e3 and R.e1 = 10

where T.e1 = T.e2

order by T.e1, T.e3, T.e2

- A BEGIN END block contained a nested BEGIN END block

- Inside the nesting block a local temporary table was declared (e.g. using execute immediate)

- The outer block declared a cursor on the temporary table and was opened inside the nested block but not closed in the nested block

- The cursor used an index on the temporary table

This has been fixed.

SQLSTATE_PARM_TOO_LONG changed from 28000 to 53W06

SQLSTATE_PASSWORD_TOO_SHORT changed from 28000 to 54W07

SQLSTATE_PASSWORD_TOO_LONG changed from 28000 to 54W08

SQLSTATE_INVALID_LOGON and SQLSTATE_INVALID_PASSWORD continue to return 28000

The function works fine if "true" or "false" was specified.

conn = CREATEOBJECT( 'ADODB.Connection' )

conn.ConnectionString = [Provider=ASAProv.90;uid=dba;pwd=sql;]

conn.Open

rs = CREATEOBJECT( 'ADODB.RecordSet' )

rs.ActiveConnection = conn

curs = CREATEOBJECT( 'CursorAdapter' )

curs.datasourcetype = 'ADO'

curs.datasource = rs

curs.SelectCmd = 'SELECT * FROM SYS.SYSTABLE WHERE table_id = 2'

curs.alias = 'ADO Test'

curs.cursorfill

BROWSE

FoxPro obtained the first row in the result set and then issued a RestartPosition() call to reposition to the first row in the result set. The cursor type for the query in the above example was FORWARD ONLY. With this type of cursor, the RestartPosition() procedure failed to reposition to the start of the result set. This problem has been fixed. RestartPosition() will now re-execute the query to position to the start of the result set when the cursor type is FORWARD ONLY.

begin exec my_proc ? out end

would have returned the correct results for the first time it was called, but second and subsequent calls would have return incorrect results. Updating the driver to version 4.20.0106 resolves the problem.

A workaround is to execute the procedure using standard syntax:

{ CALL my_proc(?) }

System.Data.OleDb.OleDbDataAdapter Ada=new System.Data.OleDb.OleDbDataAdapter ("select * from test",conn);

System.Data.OleDb.OleDbCommandBuilder CommandBuilder=new System.Data.OleDb.OleDbCommandBuilder (Ada);

System.Data.DataTable dt=new DataTable ();

Ada.FillSchema (dt,System.Data.SchemaType .Mapped );

System.Data.DataRow dr=dt.NewRow ();

dr["Col01"]=textBox1.Text ;

dt.Rows.Add (dr);

Ada.Update (dt);

If the text entered into "textBox1" was a multibyte character, it would not have been inserted correctly into column "Col01" of the "test" table when the database was using the UTF8 collation. This problem has now been fixed.

Note that the precision of unsigned bigint columns is still returned as 20, which is the correct value.

With these changes, a default transmission rule can be set such that every user that does not have an explicit transmission rule will use the default rule as its transmission rule. There are two ways to set the default transmission rule:

If you are using a transmission rule INI file, then create a section called ianywhere.server.defaultRules and set the transmission rule. For example, if the default transmission rule is to send only high-priority messages, then the following should be added to the transmission rules file:

[ianywhere.server.defaultRules]

auto = ias_Priority > 6

You must restart the server for this rule to take effect

To set the default transmission rule using QAnywhere Central, follow these steps:

Select Tools->Server store connect... and specify the necessary connection parameters to connect to the server

Select the Properties item in the left-hand panel

Right-click "Properties" in the left-hand panel and select "Create property"

In the "New Property" dialog enter ianywhere.server.defaultRules for the Client field, ianywhere.qa.server.rules for the Property field and the rule (example "auto=ias_Priority > 6") for the Value field.

You must restart the server for this rule to take effect

To set the default transmission rule using a Server message

C# Example:

QAManager mgr = �; // Initialize the QAManager

QAMessage msg = mgr.CreateTextMessage();

msg.SetStringProperty( 搃as_ServerPassword�, 換Anywhere� );

// Indenting and newlines are just for readability

msg.Text = �<?xml version="1.0" encoding="UTF-8"?>\n�

+ �<actions>\n�

+ � <SetProperty>\n�

+ � <prop>\n�

+ � <client>ianywhere.server.defaultRules</client>\n�

+ � <name>ianywhere.qa.server.rules</name>\n�

+ � <value>auto = ias_Priority > 6</value>\n�

+ � </prop>\n�

+ � </SetProperty>\n�

+ � <RestartRules>\n�

+ � <client>ianywhere.server.defaultRules</client>\n�

+ � </RestartRules>\n�

+ �</actions>\n�;

mgr.PutMessage( 搃anywhere.server\system�, msg );

Note that this mechanism has the advantage that you do not have to restart the server.

For example:

create procedure DBA.myproc( in arg1 int default 1, in arg2 int default 2, in arg3 int default 3)

begin

select arg1 + arg2 + arg3 from dummy;

end

To use the default values, set the parameters' value to SADefault.Value:

SAConnection conn = new SAConnection( "DSN=SQL Anywhere 10 Demo" );

conn.Open();

SACommand cmd = new SACommand( "DBA.myproc", conn );

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add( new SAParameter( "arg1", SADefault.Value ) );

cmd.Parameters.Add( new SAParameter( "arg2", SADefault.Value ) );

cmd.Parameters.Add( new SAParameter( "arg3", SADefault.Value ) );

int val = ( int ) cmd.ExecuteScalar();

A work-around is to delete the property from the INI file.

"Function '%1' has invalid parameter '%2' ('%3')"

SQLCODE -1090

Constant SQLE_INVALID_PARAMETER_W_PARM

SQLSTATE 08W94

Sybase error code 13424

ODBC 2 State 08004

ODBC 3 State 08004

The text of the error will specifiy the function name %1, the parameter name or

number %2, and the invalid parameter value or mode %3. A parameter value of 'NULL' means that a NULL parameter has been specified; 'UNKNOWN' means that value is unknown for the evaluation routine.

End synchronizing 'template_P1' for MobiLink user 'template_U1'

If more than one publication was specified on the MobiLink Client commandline (either together as -n P1,P2 or separately as -n P1 -n P2), then the publication reported in this message may have been incorrect, although processing of the synchronization would have continued correctly. Only the message was wrong. The message now reports the correct publication name or names.

A workaround is to write the function or procedure in using Watcom-SQL

Attempting to register server with LDAP

LDAP Error Referral (v2) received from ldap_search_s (LDAP server error: Referral: <LDAP URL>

LDAP registration failed

This has been fixed with the addition of the following new asaldap.ini file parameters:

read_authdn - the read-only authentication domain name. The domain name must specify an existing user object in the LDAP directory that has read access to the basedn. is only required if the LDAP server requires a non-anonymous binding before searching can be done. For example, this field will normally be required if Active Directory is used as the LDAP server. If this field is missing, the bind will be anonymous.

read_password - the password for authdn. This is only required on the client if read_authdn is used.

Note that this fix required changes to both the server and the client libraries. Thus applications using ODBC, embedded SQL and JDBC with the iAnywhere JDBC driver are affected. If a server containing this fix registers with an Active Directory server, applications without this fix will not be able to find it.

The following are the results for bigint, integer and numeric values:

'00000001' '00000001' '00000001'

'ffffffffffffffff' 'ffffffff' 'ffffffffffffffff'

'00000002' '00000002' '00000002'

'fffffffffffffffe' 'fffffffe' 'fffffffffffffffe'

'7fffffff' '7fffffff' '7fffffff'

'ffffffff80000001' '80000001' 'ffffffff80000001'

'ffffffff80000000' '80000000' 'ffffffff80000000'

'80000000' '00008000' '80000000'

'7fffffffffffffff' '00007fff' '7fffffffffffffff'

'8000000000000001' 'ffff8000' '8000000000000001'

'8000000000000000' 'ffff7fff' '8000000000000000'

This problem has been fixed. The following shows typical results now returned for bigint, integer and numeric values:

'0000000000000001' '00000001' '0000000000000001'

'ffffffffffffffff' 'ffffffff' 'ffffffffffffffff'

'0000000000000002' '00000002' '0000000000000002'

'fffffffffffffffe' 'fffffffe' 'fffffffffffffffe'

As well, with 9.0.2, passing a negative value to inttohex() would have resulted result in a "value out of range" error. This to has been corrected

- timestamps reported in the request level log could jump ahead approximately 25 days

- client applications could be incorrectly disconnected

This has been fixed.

The following 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 you can access and use similar to an SD slot. Therefore, call ULEnableFileDB to store UltraLite databases on either an internal drive (built-in storage card) or external slot (expansion card). Furthermore, use the existing connection parameter palm_db=CRID:n to specify the data store on either an internal drive or external slot. 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.

B. Synchronization Conduit

The conduit synchronizes the UltraLite database which is first found on the device. The database lookup sequence is first the record storage, then the file system including internal drive and external slot. Make the UltraLite database which you intend to synchronize available as the first one found in that sequence.

Note: This bug only affected opening a file by clicking on the "File/Run Script" menu item. Behavior when running as a command line program was not affected.

For example,(Note the -p switch is still required):

ulload -p CRID -c dbf=ul_udb_CRID.udb;palm_fs=vfs my_schema.xml

When deployed to the device, the database name must follow the pattern ul_udb_XXXX.udb where XXXX is the creator id of the application. Furthermore, the file must reside in the /PALM/UltraLite/ folder.

An example of the incorrect JAVA code follows:

PreparedStatement stmt = con.prepareStatement( "INSERT INTO t VALUES(?, ?)" );

stmt.setInt(1, 100);

stmt.setString(2, "row 1");

stmt.addBatch();

BigDecimal bd = new BigDecimal(200);

stmt.setBigDecimal(1, bd); // notice the switch in datatype when setting the variable value

stmt.setString(2, "row 2");

stmt.addBatch();

stmt.execute();

The above code would have inserted incorrect data for the second row in the batch, but will now throw an execption when the application performs the stmt.setBigDecimal() call.

Note that switching datatypes is perfectly acceptible during different executions of a batch. So, the following code will not generate an exception and will insert correct data:

PreparedStatement stmt = con.prepareStatement( "INSERT INTO t VALUES(?, ?)" );

stmt.setInt(1, 100);

stmt.setString(2, "row 1");

stmt.addBatch();

stmt.setInt(2, 200);

stmt.setString(2, "row 2");

stmt.addBatch();

stmt.execute(); // notice the execution of the first batch

BigDecimal bd = new BigDecimal(300);

stmt.setBigDecimal(1, bd); // notice the switch in datatype when setting the variable value

stmt.setString(2, "row 3");

stmt.addBatch();

stmt.execute();

The Query Analyzer forms a query that is syntactically incorrect for ASA and this results in a syntax error message.

For example, the following query will result in the error messages shown below.

asatest9..dba.sp_customer_list

Could not execute procedure 'sp_customer_list' on remote server 'asatest9'.

[OLE/DB provider returned message: Syntax error near '1' on line 1]

The Query Analyzer issues the SQL statement "{?=call "dba"."sp_customer_list";1}". The ";1" results in a syntax error.

This has been fixed. The iAnywhere OLEDB provider will now remove the ";1" from the statement as a work-around.

Note that the default heap size may be to small for some applications, but can be increase with the Java_heap_size database option.

Note that the default heap size may be to small for some applications, but can be increase with the Java_heap_size database option.

COLUMN_PRIVILEGES

TABLE_PRIVILEGES

FOREIGN_KEYS

The SQL scripts that implement these schema rowsets did not qualify the SYSGROUP or SYSTRIGGER tables with "SYS.". This has been corrected.

To install new versions of the OLEDB provider schema rowsets into a database, load and rerun scripts\oleschem.sql.

To install new versions of the OLEDB provider schema rowsets into a database, load and rerun scripts\oleschem.sql.

- The nested subquery had to qualify to be flattened by the engine (e.g., there were no ORDER BY or GROUP BY clauses in the nested subquery, the main query block didn't contain an OUTER JOIN, etc.)

- the EXISTS subquery could not have been flattened.

- the EXISTS subquery was correlated, and the outer reference was used in an equality predicate.

- the EXISTS subquery qualified to be evaluated as an uncorrelated subquery (i.e., the subquery optimization algorithm was used for the cost-based analysis of the subquery's evaluation methods)

For example:

SELECT *

FROM sales_order_items s

WHERE prod_id IN (

SELECT p1.id

FROM product p1

where p1.quantity > 0 and

EXISTS (

SELECT *

FROM product p2

where p2.quantity > 0 and p2.id =p1.id

GROUP BY p2.id

)

Now, when the UltraLite conduit is loaded by the HotSync Manager (on the desktop), it attempts to set the cache size using the value "CacheSize" if it is specified in the following registry key:

Software\Sybase\Adaptive Server Anywhere\<version>\Conduit\<CRID>

where <CRID> is the creator ID used in an UltraLite Palm application on remote. The size of "CacheSize" is in bytes, or the suffix k or K to indicate kilobytes or m or M to indicate megabytes, can be used. If this value is invalid, it is ignored and the old default cache size of 128KB is used. If the "CacheSize" value is not set, the UltraLite conduit will use the new default cache size of 4MB.

This is illustrated by the following SQL code.

BEGIN

DECLARE @denominator numeric(16,12), @time_calc numeric(22,12)

select @denominator = 12

select @time_calc = round(1.0/@denominator,12)

select @time_calc

END

The correct result is .083333333333, prior to this fix, the result was .083333000000.

jdbc:odbc:<data-source-name>[;<attribute-name>=<attribute-value>]*

...

E. 09/02 11:21:58. Error: at ianywhere.ml.jdbcodbc.IResultSetMetaData.getColumnCount(IResultSetMetaData.java:213)

E. 09/02 11:21:58. Error: at ianywhere.ml.notifier.Notifier.connectDB(Notifier.java:348)

E. 09/02 11:21:58. Error: at ianywhere.ml.notifier.Notifier.init(Notifier.java:299

...

This problem has been fixed, the call to getColumnCount is now delayed until after the request_cursor is executed.

For example, the following this sequence would have generated the 'permission denied' error:

create procedure userA.hello( in name char(10) )

begin

message 'Hello ' || name;

end;

grant execute on userA.hello to userC;

create function userB.world()

returns char(10)

begin

select 'world';

end;

grant execute on userB.world to userC;

create procedure userC.say_hello( )

begin

call userA.hello( userB.world() );

end;

call userC.say_hello();

Note, the value greater than 4 billion could have caused an out of range error if used with the procedure sa_conn_info.

Assertion failed: 108001 (9.0.2.3182) Attempting checkpoint while backup active

A flag indicating that a backup was in progress was not being reset if the backup failed. This has been corrected.

' Set CommandText equal to the stored procedure name.

objCmd.CommandText = "fred.ShowSalesOrderDetail"

objCmd.CommandType = adCmdStoredProc

objCmd.ActiveConnection = objConnection

A couple of problems arose when using this form of the stored procedure call. Internally, the OLE DB provider uses a SQL query to obtain information on the number of parameters to the stored procedure. It asked for information on a procedure name called "fred.ShowSalesOrderDetail" and there was no such procedure name since the owner name had not been separated from the procedure name. This problem has been fixed.

The second problem involved using the ADO parameter "Refresh" method as shown below in a Visual Basic example:

' Automatically fill in parameter info from stored procedure.

' This avoids having to do CreateParameter calls.

objCmd.Parameters.Refresh

The problem is related to the way ADO interprets the components of the stored procedure name. There are three basic forms recognized by ADO.

1. ProcedureName

2. Owner.Catalog

3. Owner.ProcedureName.Catalog

Here is how ADO interprets these forms.

The first form is what is most often used. When only a name appears (no qualifiers), it is interpreted to be the procedure name. This form poses no problems.

When the second form is used (two names separated by a period), the first name is assumed to be the owner name and the second name is assumed to be the catalog name. SQL Anywhere doesn't support the notion of catalogs. When the Refresh method is called, the OLE DB provider is presented with the following query by ADO.

EXECUTE sa_oledb_procedure_parameters inProcedureCatalog='Catalog',inProcedureSchema='Owner'

This query from ADO is meaningless to the SQL Anywhere OLE DB provider, as the procedure name is missing. ADO has interpreted the two components as a schema name (the owner) and a catalog name. The query returns information on all procedures owned by "Owner" which is of no use.

When the third form is used, the first name is assumed to be the owner name, the second name is assumed to be the procedure name. and the third name is assumed to be the catalog name. When the Refresh method is called, the OLE DB provider is presented with the following query by ADO.

EXECUTE sa_oledb_procedure_parameters inProcedureCatalog='Catalog',inProcedureSchema='Owner',inProcedureName='ProcedureName'

Since the procedure name and owner are present and since the catalog name is ignored by the OLE DB provider, this query will return the correct information about the procedure parameters. However, the actual procedure call will result is a syntax error since "call Owner.ProcedureName.Catalog" is not an acceptable form for a stored procedure call.

These problems can be avoided by not using the ADO "Refresh" method and, also, by avoiding the use of the three-part syntax. The best solution is to avoid the use of an owner qualification entirely since the "X.Y" form is misinterpreted by ADO.

- If the time span exceeds the value specified by Connection Lifetime, the connection will be destroyed when it is closed.

- If the time span does not exceed the value specified by Connection Lifetime, the connect will be returned to the pool when it is closed.

- An outer hash join operator was used in the query execution plan

- The build input to the hash join was larger than would fit into memory

- Further, the order of rows had to match a particular set of conditions, and memory usage in the rest of the query plan had to match a particular state

When the problem appeared, the set of missed rows would vary depending on current server state. This problem has been fixed.

- a failover server was specified with a second -x option,

- the communication address of the failover server [as in -x tcpip(<communication address>)] was longer than 255 characters, and

- communication with the primary server failed, causing an attempt to synchronize with the failover server.

This has been fixed. The problem is related to Engineering Case 405360.

select *

from dummy

where dummy_col = (select * from department where dept_id < 200 )

When the error was not given though, the extra columns were ignored. This has been fixed.

To use this new feature, users will need to have a valid Certicom ECC license, as well as ECC client and server certificates. New properties and methods have been added to the SyncParms pods object to support the new synchronization stream types.

* PODS Constant: UInt32 STREAM_TYPE_ECC_TCPIP - select this value for the SyncParms.setStream() method when synchronizing via TCPIP over ECC transport-layer security.

* PODS Constant: UInt32 STREAM_TYPE_ECC_HTTP - select this value for the SyncParms.setStream() method when synchronizing via HTTP over ECC transport-layer security. Note, this is NOT the same as HTTPS.

* PODS Method: setSecurityParms( String v ) - Sets the options required when using ECC transport-layer security. This method is relevant only when the sync stream type is set to be ECC_TCPIP or ECC_HTTP. A detailed explanation of this parm string can be found in the MobiLink documentation: MobiLink Clients -> UltraLite Synchronization Parameters -> Synchronization parameters -> Security Parameters synchronization parameter.

* PODS Method: String getSecurityParms() - Gets the option string for ECC security.

The UltraLite for M-Business Anywhere samples, Simple and CustDB, have been updated to demonstrate the use of these new methods and constants.

and there was a high rate of messages being delivered to the receiver client, resulting in several synchronizations per second. In this scenario it was possible that message properties or content obtained from a given QAMessage through the QAnywhere client API methods, may not have been correct, even though the messages were stored correctly in the message store database. This has been fixed.

For example,

SELECT name from Table where name < ? (name starts an ascending index)

SELECT name from Table where name > ? (name starts a descending index)

This has been corrected.

For example, if a statement such as:

ALTER TABLE DBA..T ADD c2 INT

was executed, the resulting transaction log would contain an invalid statement and recovery using the log would fail. This has been fixed so that when a statement such as the one above is executed, it will now result in a valid statement being recorded in the transaction log.

It is important that this file be copied over cp932ms.uct before any use of a database in the environment. In particular, it is recommended that this file copy occur immediately after deploying Adaptive Server Anywhere and a database and prior to running an application against the database. If the file copy occurs after the database has been in use, the altered character mappings may cause data values to change, even though the appearance of the characters remains unchanged. In a synchronization environment, these data changes may cause unexpected errors.

fixed.

1) Fetching multiple rows into a bound WCHAR column, which was too small for the column (thus truncating the data), could have copied the data into the wrong user buffer location. Data was filled correctly on the first fetch, but incorrectly on subsequent fetches, likely overrunning memory and possibly crashing the application.

2) Using SQLGetData to put data into a WCHAR buffer, where the client or database charset was multibyte, may have caused a subsequent SQLGetData on the same column to contain incorrect data. Also, indicator values may have been set incorrectly for SQLGetData or SQLFetch calls to fill WCHAR buffers when using multibyte character sets.

3) Using SQLGetData to put data into a WCHAR buffer with more than 64K WCHARs could have returned the wrong indicator, when there is no truncation.

4) Using SQLPutData to put more than 64K of data of ANY TYPE in one SQLPutData call, may have put incorrect data.

5) Calling SQLPutData with CHAR (not WCHAR) data and SQL_NTS length, only appended the first 256K of data. Appended data greater than 256K was lost.

6) Calling SQLPutData with WCHAR data and using odd lengths (i.e. partial characters), may have put incorrect data if the client or database charset was multibyte.

Also, the runtime now permits a ping synchronization if there are uncommitted transactions.

- a user-defined function was called from a procedure

- the function in turn called a procedure with an inout or output parameter

- the argument corresponding to the output parameter had a different datatype, requiring a conversion to be done

This has been fixed. A workaround is to ensure the argument datatype matches the type of the output parameter, or declare the parameter explicitly as IN if it was not intended to be INOUT.

SELECT T1.*

FROM

(select * from (select quantity from product UNION select quantity +10 from product ) as V (q)

) T1 (q)

WHERE EXISTS(

SELECT 1 FROM

(select max(id) as i,

sum(quantity) as qty

from product

group by color) T2

WHERE T1.q >T2.i AND T1.q=T2.qty )

This has now been fixed.

- the database used checksums;

- transactions spanned transaction logs;

- the last replication finished at a commit that was at the end of an offline transaction log.

This problem has been fixed.

"010P4: An output parameter was received and ignored."

This warning has now been suppressed since there is nothing the user can, or should, do about it.

For example:

select <many constant strings>, 'foo' 'foo'

from employee

group by 'foo', 'foo'

This has been fixed.

Please note the following behaviour change:

The ULConnectionParms control does not work with Visual Studio 2005. Instead, use the ULConnectionParms class programatically which functions exactly the same as the control. Make a ULConnectionParms object and set its properties. Finally, to get the connection string, call the ToString() method, as with the control.

The SyncProgressDialog wizards are not available for Visual Studio 2005. Users should visit codeXchange web-site for samples of the SyncProgressDialog.

Also note the the following behaviour changes have been observed in the .NET Compact Framework 2.0:

1. Decimal object scale preserved

In Decimal objects, scale (i.e. number of digits after decimal place) is preserved when converted to string even if the digits are zero.

As an example, the following piece of code will behave differently between .NET Compact Framework 1.x and 2.0. In this example, a decimal value of 5 is assumed to have been placed in a Decimal column that has a precision of 4 and a scale of 2.

ULDataReader dr = cmd.ExecuteReader();

dr.MoveFirst();

Decimal d = (Decimal) dr.GetValue( 1 );

Console.WriteLine( d );

Console Output under .NETF v1.x:

5

Console Output under .NETF v2.0:

5.00

2. ULRowUpdatedEventArgs and ULRowUpdatingEventArgs

In the ULRowUpdatedEventHandler method, getting the TableMapping property of the given ULRowUpdatedEventArgs object behaves differently when run with .NET v1.x and .NET v2.0. With .NET v1.x, null is returned when no TableMapping is associated with the event. With .NET v2.0, an empty TableMapping object is returned.

3. Deployment of resources on devices

iAnywhere.UltraLite.resources.dll and iAnywhere.Data.UltraLite.resources.dll

Consider the following directory structures:

Directory Structure #1

<curdir>\iAnywhere.Data.UltraLite.dll

<curdir>\iAnywhere.Data.UltraLite.resources.dll

Directory Structure #2

<curdir>\iAnywhere.Data.UltraLite.dll

<curdir>\en\iAnywhere.Data.UltraLite.resources.dll

With .NET v1.x, the resources dll could have been placed in the same directory as the executing assembly or in a directory with the name of its culture. That is, Directory Structure #1 and #2 are both acceptable.

With .NET v2.0, the resources dll must be placed in a directory with the name of its culture. That is, using Directory Structure #1, the Compact Framework will not be able to find the resources dll. Using #2 on the other hand, the Compact Framework will find the resources dll.

PLEASE NOTE THE FOLLOWING REDIRECTOR CONFIG FILE CHANGES:

THe MobiLink directive now supports the following formats for the directive value.

(This is the old format - which is still supported) - Existing application can continue to use this format. It's a simple and short format if applications are not interested in HTTPS

ML=my-pc:82

(This is the new format - which allows for more security options to be specified)

ML="host=my-pc;port=82;trusted_certificates=rsaroot.crt;https=true"

Please note the following with the new format:

1- There is quotation marks that bounds the value of the options list. It is mandatory that those quotes exist.

2- All Mobilink stream options are valid for the option list (since they simply get passed along to Mobilink), Only the 'https=' option though is specific to the redirector only.

2- The option, trusted_certificates (Plural), is where you specify the server root certificate. This certificate has to be PEM encoded x509 type, which is simply the supported Mobilink certificate format.

3- The option https=true has to be specified in order for the redirector to enable SSL at the lower communication layer. Any value other than true or if the option is omitted, the default is used, which is false.

-sl java { -Dianywhere.qa.db.upgradeDeliveryColumns=true }

During start-up, the QAnywhere server will change the schema. Note that the schema change should be considered irreversible. That is, it will not be possible to go back to an earlier version of the software. Once the schema change has completed, it is no longer necessary to start the QAnywhere server with the additional command line parameters.

Note, the database is not corrupt and will recover correctly when started with a server with this fix.

Note that starting with this build, HP-UX 11i IPF versions prior to HP-UX 11i v2 (HP-UX 11.23) are no longer supported.

For details on HP's HPUX 11i support, see:

http://www.hp.com/products1/unix/operating/oeurjuly05.html

Note that at least 1 message will be in each upload (if there are messages queued) and the QAnywhere Agent does not split messages. Therefore, the incremental upload size is an approximation, and it will be a poor approximation if there is a message to be uploaded that is many times larger than the incremental upload size.

Example: The default incremental upload size is 256K, so if the average message size is 256 bytes and the maximum message size is not too large, then approximately 1000 messages will get transferred to the server per synchronization. The synchronizations will continue until all queued messages are transferred to the server, or until the connection is broken. The batches of messages that were successfully transferred to the server will not be transferred again.

In addition, the HotSync dialog is now properly updated - the "Exchange" arrows indicator is kept progressing while the connection is kept alive.

A new feature has been implemented which will allow ULPod to tell how the string coming from the PODS engine is encoded, and use the correct conversion parameters to convert it to, or from, Unicode. This new feature is only enabled on Windows platforms, as M-Business Anywhere for Palm OS does not yet support UTF8.

Invalid option "<value>"

where <value> was the value the option was set to, (i.e. -e drs <value>). The drs option was being treated as a boolean value, when in fact it is a numeric value. This has been corrected, and the error will no longer be reported

<install dir>\win32\VS8\iAnywhere.Data.AsaClient.dll

<install dir>\win32\VS8\iAnywhere.Data.AsaClient.dll.config

<install dir>\win32\VS8\policy.9.0.iAnywhere.Data.AsaClient.dll

<install dir>\ce\VS8\iAnywhere.Data.AsaClient.dll

<install dir>\ce\VS8\iAnywhere.Data.AsaClient.gac

The ADO.NET reference manual has also been updated to describe the new features of ADO.NET 2.0. The new documentation files are:

<install dir>\docs\dbpaen9.chm

<install dir>\docs\pdf_en\dbpaen9.pdf

Compact Framework version 2.0 programs cannot be built with assemblies built with version 1.0. The following new files are versions of the existing .NET assemblies built with Compact Framework version 2.0.

<install dir>\ce\VS8\iAnywhere.QAnywhere.Client.dll

<install dir>\ce\VS8\iAnywhere.QAnywhere.Client.gac

<install dir>\ce\VS8\iAnywhere.QAnywhere.Client.xml

<install dir>\UltraLite\UltraLite.NET\ce\VS8\iAnywhere.Data.UltraLite.dll

<install dir>\UltraLite\UltraLite.NET\ce\VS8\iAnywhere.Data.UltraLite.xml

<install dir>\UltraLite\UltraLite.NET\ce\VS8\iAnywhere.UltraLite.dll

<install dir>\UltraLite\UltraLite.NET\ce\VS8\iAnywhere.UltraLite.xml

New files include

<install dir>\win32\iAnywhere.VSPackage.AsaClient.dll

<install dir>\win32\SetupVSPackage.exe

<install dir>\win32\1033\iAnywhere.VSPackage.AsaClientUI.dll

<install dir>\win32\VS8\iAnywhere.VSPackage.AsaClient.dll

<install dir>\win32\VS8\SetupVSPackage.exe

<install dir>\win32\VS8\1033\iAnywhere.VSPackage.AsaClientUI.dll

After installing the EBF, create a file called "temp.reg", and paste the following lines into it:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Sybase\Sybase Central\4.3\VMArgs]

"-Dswing.noxp"="1"

[HKEY_LOCAL_MACHINE\SOFTWARE\Sybase\Adaptive Server Anywhere\9.0\DBISQL\VMArgs]

"-Dswing.noxp"="1"

[HKEY_LOCAL_MACHINE\SOFTWARE\Sybase\Adaptive Server Anywhere\9.0\DBConsole\VMArgs]

"-Dswing.noxp"="1"

[HKEY_LOCAL_MACHINE\SOFTWARE\Sybase\Adaptive Server Anywhere\9.0\DBMLMon\VMArgs]

"-Dswing.noxp"="1"

and then run the file. If the "Fast Launcher" feature of Sybase Central or DBISQL has been enabled, log out and then log back in again for the changes to take effect.

While this procedure will prevent the programs from crashing, there will be some visual anomalies, such as an incorrect background color for the menu bar. These defects aside, the programs will function normally.

- In Smart Device and Windows projects, a reference to the iAnywhere.Data.UltraLite.dll is available in the "Add References" dialog box.

- The SyncProgressDialog can be added to a project by selecting Project, Add New Item.

- The ULConnectionParms controls are available, but must be added manually as described below due to a problem in Visual Studio 2005. If a Compact Framework DLL is added to the GAC before one of its controls is added to the VS2005 toolbox, the control will be permanently greyed out.

In the instructions below, <VS2005InstallDir> refers to the installation directory of Visual Studio 2005. For example, "C:\Program Files\Visual Studio 2005\"

To add the ULConnectionParms controls to the toolbox, do the following after the install:

1. Open the Visual Studio 2005 program.

2. Go to Tools, Choose Toolbox Items.

3. Note that there is already an iAnywhere.Data.UltraLite DLL with "Global Assembly Cache" as its Directory.

4. Add the following DLL by clicking Browse:

<VS2005InstallDir>\SmartDevices\SDK\CompactFramework\2.0\v2.0\WindowsCE\iAnywhere.Data.UltraLite.dll

5. Check ULConnectionParms for the two iAnywhere.Data.UltraLite DLLs from steps 3 and 4. Press OK.

6. Finally, in a command prompt (or the <Start>, Run dialog) , run

<VS2005InstallDir>\SDK\v2.0\Bin\gacutil.exe -i <VS2005InstallDir>\SmartDevices\SDK\CompactFramework\2.0\v2.0\WindowsCE\iAnywhere.Data.UltraLite.dll

I/O Fatal error: Unknown device error

Assertion failed: 201125 Read Error with OS error code: 64

The problem only occurs on certain machines with certain patches and/or hardware and/or drivers, the specifics of which have not been completely identified. The problem is related to scattered reads (aka "group reads") -- see the documentation to determine when the server may use scattered reads. The server now works around this OS problem by disabling scattered reads for files accessed by remote file sharing, or by UNC reference.

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

prepare_for_download

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

modify_last_download_timestamp

prepare_for_download

if( modify_last_download_timestamp script is defined

or prepare_for_download script is defined ) {

COMMIT

}

Previous documentation had the order of the scripts reversed. The above order was chosen because the last-download timestamp (LDT) affects the content of the download. If the LDT is being modified, it must be modified before the download logic kicks in (ie. in the prepare_for_download script or in the download scripts).

-sl java { -Dianywhere.qa.db.upgradeDeliveryColumns=true }

During start-up the QAnywhere server will change the schema. Note that the schema change should be considered irreversible. That is, it will not be possible to go back to an earlier version of the software. Once the schema change has completed, it is no longer necessary to start the QAnywhere server with the additional command line parameters.

SQLRETURN res = SQLColAttribute(hstmt, SQLUSMALLINT(i), SQL_DESC_COLUMN_TYPE, 0, 0, 0, 0);

switch ( res ) {

case SQL_SUCCESS:

case SQL_SUCCESS_WITH_INFO:

break;

default:

ODBC_error(henv,hdbc,hstmt);

break;

}

This problem has been fixed.

The ODBCINI and ODBCHOME environment variables are used to tell the ODBC driver and Data Source utility where to find the .odbc.ini file. If these environment variables were set to files that did not exist, a search for the .odbc.ini file was done in the user's home directory, and if not found, the path was searched. If a file was found, that file was used. If no file is found, one was created in the user's home directory. The -ns option now tells the Data Source utility not to search for the .odbc.ini file, but to use the existing environment variables to determine where the file should be. This is only useful when (a) the file specified by one or more of the environment variables does not exist, AND (b) a DSN is being created.

The -f option tells the Data Source utility to display the name of the .odbc.ini file that is being used. For example, "dbdsn -f -l" will display the name of the .odbc.ini file being used, and then list the DSNs in that file.

ALTER SYNCHRONIZATION USER ml_username DELETE ALL OPTION

or

ALTER SYNCHRONIZATION SUBSCRIPTION

TO publication-name

[ FOR ml_username, ... ]

DELETE ALL OPTION

This problem has now been fixed.

-sl java { -Dianywhere.qa.db.upgradeDeliveryColumns=true }

During start-up, the QAnywhere server will change the schema. Note that the schema change should be considered irreversible. That is, it will not be possible to go back to an earlier version of the software. Once the schema change has completed, it is no longer necessary to start the QAnywhere server with the additional command line parameters.

Workaround: After generating scripts with a new MobiLink script version, use File|Save to save the schema. This will update the underlying meta information file. Then generate the scripts again, the new MobiLink script version field will now be used.

The workaround for this problem is to interrupt the statement by clicking the "Stop" toolbar button.

setMBAServerWithMoreParms( String host, String port, String url_suffix, String additional )

This new feature came from requests to specify a proxy server's host/port with ULPod one-button-synch. This new method augments the existing SyncParms.setMBAServer() method by adding the new string argument "additional".

The new "additional" argument gives user a chance to specify additional stream parms, such as proxy host, proxy port, or other security-related parameters.

Note that this problem does affect QAnywhere server running against ASA, Oracle, DB@ or Microsoft SQL Server.

The documentation also did not indicate indicated how a user can change the default time and date formats used by the QAnywhere server. To set the time format, set the property ianywhere.qa.server.scheduleTimeFormat to a value describing the time format. This property can be set in several different ways. If the server uses a configuration file (using the -m <file-name> MobiLink command line option), then a line can be added to the file as follows:

ianywhere.qa.server.scheduleTimeFormat = hh:mm:ss a

This property can also be set using the GUI tool QAnywhere Central, by clicking on the server properties tab and adding the property. The time format values are identical to those used in Java by the java.text.SimpleDateFormat class (the default time format is HH:mm:ss):

Letter Time Component

a Am/pm marker (AM/PM)

H Hour in day (0-23)

k Hour in day (1-24)

K Hour in am/pm (0-11)

h Hour in am/pm (1-12)

m Minute in hour (0-59)

s Second in minute (0-59)

Time Format Example Time

HH:mm:ss z 12:08:56

h:mm a 12:08 PM

K:mm a 0:08 PM

hh:mm aaa 12:08 PM

HH:mm:ss 12:08:56

HHmmss 120856

To set the date format, set the property ianywhere.qa.server.scheduleDateFormat to a value describing the date format. This property can be set in several different ways. If the server uses a configuration file (using the -m <file-name> MobiLink command line option), then a line can be added to the file as follows:

ianywhere.qa.server.scheduleDateFormat = yy:MM:dd

This property can also be set using the GUI tool QAnywhere Central, by clicking on the server properties tab and adding the property. The date format values are identical to those used in Java by the java.text.SimpleDateFormat

class (the default date format is yyyy-MM-dd):

Letter Date or Time Component

y Year

M Month in year (ie July, Jul or 07)

d Day in month

Date Format Example Date

yyyy.MM.dd 2001.07.04

yyyy.MMMMM.dd 02001.July.04

d MMM yyyy 4 Jul 2001

yyMMdd 010704

Note that the software does not need to be upgraded your to use the two date and time formatting properties.

Note that RuntimeExceptions are logged to the QAnywhere Server (MobiLink) console and log file. Typically, RuntimeExceptions represent bugs, and probably indicate that you need a fix for the component issuing the exception, for example, the JDBC driver or JMS driver.

Visual Studio 2005 compilers define the wchar_t type as the internal __wchar_t type, instead of unsigned short*, which is what was used in the previous CE compilers. Because of this, apps compiled with Visual Studio 2005 would trigger unresolved external errors when linked against the UltraLite runtime. As a work-around, an application can cast all wchar_t, ul_wchar, and TCHAR variables passed into the runtime to unsigned short* explicitly. Another work-around is to use the -Zc:wchar_t- compiler option, which will cause wchar_t to be defined as unsigned short *, though, this won't work if the app links against other libraries such as the MFC library that are compiled with Visual Studio 2005 without this option.

Note that the Visual Studio 2005 compatible runtimes do not support sync encryption on 9.0.2.

create global temporary table ml_qa_clients

( client varchar(255) not null ) on commit preserve rows

/

create index ml_qa_clients_index on ml_qa_clients( client )

/

drop index ml_qa_delivery_client

/

create index ml_qa_delivery_client on ml_qa_delivery(client,syncstatus,maxstatus,status)

/

create or replace trigger ml_qa_delivery_before_trigger

before insert on ml_qa_delivery

for each row

begin

insert into ml_qa_clients (client) values( :new.client );

select substr(:new.address,2,instr(:new.address,'\')-2) into :new.clientaddress from dual;

select :new.status into :new.maxstatus from dual;

begin

select seqno, priority into :new.seqno, :new.priority

from ml_qa_repository where msgid = :new.msgid;

exception

when NO_DATA_FOUND then

select 0, 4 into :new.seqno, :new.priority from dual;

end;

end;

/

Note that this must be executed under the same database user initially used to create the MobiLink QAnywhere schema.

The following query demonstrates the problem using the demo database:

select T2.dept_id,

( if exists ( select 1 from sys.dummy T4 where T2.dept_head_id <> -1 ) then 1 endif ) as S2,

( select 1 from sys.dummy T5 where S2 > 0 ) as S3

from

rowgenerator T0 with ( no index )

join department as T2 with ( no index )

on T0.row_num*100 = T2.dept_id

This has been fixed.

[Sybase][ODBC Driver][Adaptive Server Anywhere]Server '<server-name>': [Microsoft][ODBC Driver Manager] Driver does not support this function (-660)

The Sybase Central plug-in was calling the ODBC function SQLPrimaryKeys() to determine which columns in the table were part of the primary key. Some ODBC drivers (such as Access and Excel) do not support this call. Now, if the remote server is of type

"generic", any failures when this call is made are ignored, and no primary key information is displayed.

Note, this problem also affected the Console utility, and the plug-in for Sybase Central.

Note, when attempting to connect to an UltraLite database created by a Palm OS application, and stored on the VFS, remember to include "palm_fs=vfs" in the connection string. This tells UltraLite that this Palm database uses the file store instead of the record store.

A work-around is to specify -ud on the server command line, although this has the side effect that the controlling application (e.g. dbspawn) will return/continue immediately before the server has fully started all of its databases, etc.

- a nullable column was being compared

- the nullable column occurred first in an index or foreign key

- the expression being compared was of a different type than the collumn and could be casted to the type of the column

- the index or foreign key could have been used as part of the plan to execute the query

This has now been corrected.

A full description of this problem is available at http://www.sybase.com/detail?id=1040117

A workaround is to use the network server to recover the database.

This has been fixed, and the switch now behaves as specified in the documentation.

event did fire during a database upgrade it was correctly not executed, but it was also incorrectly not scheduled for its next execution. This meant that the event would not have been executed again until the database was restarted. This has been fixed, so that now the event is rescheduled for the next execution.

SELECT str(table_id).str(creator).length(table_name) from systable

the server would have reported "Syntax error near '' ???". The server will now correctly report "Syntax error near 'length'".

Examples:

select distinct p.* from product p, (select prod_id, id, count(*) C from sales_order_items group by prod_id, id )

as DT, sales_order as so

where p.id = DT.prod_id and so.id = DT.id

COMMENT TO PRESERVE FORMAT ON VIEW test IS

{

CREATE VIEW test AS SELECT * FROM myTable;

}

In this example, dbisql would have sent only the portion of the statement "COMMENT ... myTable;" omitting the closing brace. This problem has been fixed.

Select *

From customer with( readpast )

Where state = 'NY'

READPAST is meaningful only at isolation level 1; it is ignored at all other isolation levels. As with other hints, the READPAST hint is respected only when the correlation name in the FROM clause refers to a base or global shared temporary table. In other situations (views, proxy tables, table functions) the READPAST hint is ignored. Queries within views may utilize READPAST as long as the hint is specified for a correlation name that is a base table.

The READPAST hint directs the server to ignore locked rows (that is, rows write-locked by other connections) rather than blocking. One way to think about READPAST is to pretend that addtiional predicate has been added to the query's WHERE clause; with the example above, akin to

Select *

From customer with( readpast )

Where state = 'NY' and (current row is not write locked by another transaction).

Consequently the READPAST hint is inconsistent with the behaviour of ANSI SQL isolation level 1, as uncommitted changes (locked rows) have an impact on the result set of a statement utilizing READPAST. As such, the use of READPAST can lead to anomalies due to the interaction of locking and predicate evaluation within the server. Subtle differences in this interaction from release to release of SQL Anywhere may yield different behaviours. The precise affect of READPAST may also depend on the optimization strategy determined by the query optimizer, particularly if the hint is specified only on a subset of the tables in the query. For example, consider the outer join query

SELECT * FROM R LEFT OUTER JOIN S (WITH READPAST) ON 1=0

In 9.0.2, a cursor stability lock is placed on the rows of R and S once the join is performed (assume nested loop join, JNLO, in this example). Further, assume that we find a single candidate S row to match the current R row, but that it is dirty (the row is write-locked by another transaction). The lock node will reject it, causing the server to fetch another row from the JNLO. Since the JNLO thinks it has found a match, it will not generate a null-supplied copy of the R row, and the server will fail to preserve the R row in the result. Hence, in this case, the query's result will fail to include all the rows of R, which it would if READPAST was not used.

READPAST can be used with all cursor types. However, KEYSET cursors must be used with caution. When initially populating the keyset through forward FETCH statements, READPAST will ignore locked rows as described above. However, a subsequent refetch of a previously FETCH 'd row may block if that underlying row was write-locked by another connection after the row was originally added to the keyset. The blocking can be avoided through the use of the BLOCKING option, but after receiving the ROW_LOCKED error (SQLCODE -210) the cursor will be in an invalid state.

READPAST can be useful when implementing a queue with a table, and there are multiple producers and consumers of the queue. Using READPAST can significantly improve concurrency in this scenario.

For example:

The derived table DT has two equality predicates on the column DT.X. The estimated number of rows of the join of DT with (T join S) is underestimated due to the fact that the selectivity estimations of the predicates 'T.X = DT.X' and 'S.X = DT.X' are multiplied.

select *

from T, S, (select count(*), R.x, R.y from R group by R.X, R.Y ) as DT( C, X, Y)

where T.X = DT.X and S.X = DT.X and T.X = R.X

std::wstring strParam1( 84, L'\x9F5F' );

// Create the parameters.

_ParameterPtr param1;

_variant_t val1( strParam1.c_str() );

param1 = cmd->CreateParameter( L"", adBSTR, adParamInput, (long)strParam1.length(), val1 );

cmd->Parameters->Append( param1 );

// Run the command.

_variant_t vtEmpty1( DISP_E_PARAMNOTFOUND, VT_ERROR );

_variant_t vtEmpty2( DISP_E_PARAMNOTFOUND, VT_ERROR );

cmd->Execute( &vtEmpty1, &vtEmpty2, adCmdStoredProc | adExecuteNoRecords );

When a variant BSTR object (val1) was converted to a BSTR object (the bound parameter), a new object was created and the memory for that object was lost. This problem has now been corrected.

if after its first execution, one of the referenced objects (table, view, proxy table, etc) had been dropped and recreated with a changed type. For example, a referenced table was dropped and a view with the same name was recreated. This has been fixed

INPUT INTO myTable FROM 'test.dat';

If the current directory is "C:\home", and the following is run:

DBISQL c:\test\test.sql

the INPUT statement is now able to resolve the relative file name "test.dat" to "c:\test\test.dat" by using the name of the folder which contains "test.sql", the file that contains the INPUT statement. Previously, "test.dat" would not have been found.

The INPUT statement could fail to resolve relative file names if the fast launcher option is turned on. The workaround is to turn off the fast launcher, or to use fully qualified file names in the .SQL files.

recordset.Open "Employees", connection, , , adCmdTable

recordset.AddNew

recordset.Fields.Item("EmployeeID") = 1000

recordset.Fields.Item("GivenName") = "Munroe"

recordset.Fields.Item("Surname") = "Marvin"

recordset.Fields.Item("DepartmentID") = 201

recordset.Fields.Item("Street") = "No fixed Street"

recordset.Fields.Item("City") = "No City"

recordset.Fields.Item("Salary") = 12000.00

recordset.Fields.Item("StartDate") = "2006-01-03"

recordset.Update

This has now been fixed.

- Use an unqualified user-defined function (UDF). If the current user does not include the UDF in the lookup path, an error is returned.

- Miss-spell a UDF name. There is no checking done when the column is added. Any use of the table fails.

- Create the column referencing a UDF then drop it. Even if 2. is fixed, there is no dependency tracking to prevent 3.

- Use a cast that gives an error at build time (e.g, TIME -> INT). The table causes an error unless the Conversion_error option is Off.

- As with 1. 2. or 3. , use a sub-select referencing a table, view or procedure that is unqualified or non-existent.

- Use features in the compute expression that cause SQL flagger errors to be returned. If the connection options give a flagger error, the table can not be used.

http://dcx.sybase.com/index.html#1201/en/dbusage/defining-computed-javause.html*d5e620

When the expression for a computed column gives an error, it does so for any query that references the table (even if the table is not modified by the statement.

As a best practice, computed columns should be defined with an expression that is computed in the same way for all connections and all users that have permission to read from the table.

There is an additional feature: "Signed .CAB files for Windows Mobile 5" in the desktop install.

In the CE deployment installer, there are now up to 5 platforms to choose from:

Build or Deploy a .CAB file for Windows Mobile 5

Build a .CAB file for ARM

Build a .CAB file for ARMV4T

Build a .CAB file for MIPS

Build a .CAB file for Pocket PC 2002 emulator (X86)

Selecting the Windows Mobile 5 option, will also allow the choice of deploying one of the two security-signed .CAB files -- one has support for Compact Framework 1.1, the other for Compact Framework 2.0. Alternatively, a custom .CAB file can still be built, but this will not be security-signed.

> OLE DB error trace [OLE/DB Provider 'ASAProv.90'

> IUnknown::QueryInterface returned 0x80004005:

> The provider did not give any information about the error.

NB DBISQL, MS SQL Query Analyser and ODBCTest process the same query successfully

Note, clients with this fix will not be able to connect through LDAP to servers without the fix, and vice versa.

The SOAPAction HTTP header is required by SA when a SOAP request is POSTed to a DISH service endpoint. The SOAPAction URL is the 'address' of the SOAP service endpoint for the request. Prior to this change an SA HTTP client was required to provide a namespace clause that defined an appropriate URL containing the database name for the endpoint service. Now it is now possible to access an SA SOAP endpoint by assuming the use of its default database. This only works in the simple case where the SA SOAP web service is not defined within a 'group' of a DISH service. When a soap endpoint is defined within a 'group', the SA SOAP client must specify the namespace to provide the appropriate database and group mapping.

The simple case is described with two services as follows:

create SERVICE "wsdl"

TYPE 'DISH'

AUTHORIZATION OFF

format 'concrete'

USER DBA;

create SERVICE "soapecho"

TYPE 'SOAP'

AUTHORIZATION OFF

USER dba

AS call soapecho_proc( :s1 );

The following are valid SA SOAP client procedure definitions that access the above soapecho service:

1) Access soapecho service directly (note, to stay consistent with the other cases the format clause describes the desired output format):

create procedure soapecho( s1 long varchar )

url 'http://localhost/soapecho'

format 'concrete'

type 'soap:doc';

2) Access indirectly through the wsdl DISH service and specifying the namespace (note, the server is expected to be running the sample.db):

create procedure soapecho( s1 long varchar )

url 'http://localhost/wsdl'

namespace 'http://localhost/sample'

type 'soap:doc';

3) With this change, namespace clause is no longer required:

create procedure soapecho( s1 long varchar )

url 'http://localhost/wsdl'

type 'soap:doc';

The above cases yield the following request characteristics:

CASE HTTP POST SOAPAction Namespace

1) POST /soapecho http://localhost/soapecho http://localhost

2) POST /wsdl http://localhost/sample/soapecho http://localhost/sample

3) POST/wsdl http://localhost/soapecho http://localhost

An example of a more complex case where the namespace clause for an SA SOAP client is required (closely resembles case 2) above):

create SERVICE "wsdl"

TYPE 'DISH'

AUTHORIZATION OFF

format 'concrete'

group 'abc'

USER DBA;

create SERVICE "abc/soapecho"

TYPE 'SOAP'

AUTHORIZATION OFF

USER dba

AS call soapecho_proc( :s1 );

The namespace is required because it identifies both the database and the group for the SA SOAP service endpoint:

create procedure soapecho( s1 long varchar )

url 'http://localhost/wsdl'

namespace 'http://localhost/sample/abc'

type 'soap:doc';

The resulting request is similar to case 2) with the exception that /abc is appended to the SOAPAction URL and the soap envelope namespace URI.

For example:

insert into T1 values ((select col1 from RemoteT1), (select col2 from RemoteT2))

This has been fixed so that now the server returns the error "Remote server does not have the ability to support this statement", if SET items are subselects in no passthrough mode.

To workaround this problem, the statement can be rewritten as an INSERT ... SELECT.

For example:

insert into T1 select (select col1 from RemoteT1), (select col2 from RemoteT2))

This problem does not affect image backups created with BACKUP DATABASE DIRECTORY or the DBBACKUP utility.

dbmlsrv9 ... -x http(...session_key=[cookie|header];...) ...

When the session_key is 'cookie', which is the default, an HTTP cookie with name JSESSIONID is used to track HTTP sessions. When the session key is 'header', an HTTP header is used. The 'header' option is useful if the cookie name of JSESSIONID conflicts with an existing cookie, in which case MobiLink will track HTTP synchronizations using a custom header entry that will not conflict with any other HTTP infrastructure. In order to implement this feature in the MobiLink server, changes were also required to the MobiLink client. Thus to get the new behaviour, clients need to be upgraded as well.

For example, when table is empty:

SELECT ifnull( sum(x), 0 ) FROM table

This has been corrected.

This problem would also have prevented the MobiLink server from starting. This has also been fixed by allowing the MobiLink server respect the "ack_timeout" setting as an alias to the "confirm_timeout" property. If both property name are used at the same time, the value associated with "confirm_timeout" will take effect.

A work around is to add SQL_UUID to the enum listed in ulapi.h just before SQL_MAX_INDEX.

InternalError: User 'ml_qa_user' has the row in 'ml_qa_repository_content_client' locked

Source statement: CREATE SYNCHRONIZATION SUBSCRIPTION TO ml_qa_user_group.ml_qa_repository_pub FOR "02600601376" OPTION sv='ml_qa_2'

The problem was that messaging cannot begin until the synchronization subscription is created in the message store, which cannot happen until the message store ID is known. This has been fixed so that the message store property "ias_StoreID" is set by the QAnywhere Agent when the message store ID has been initialized. A QAnywhere application can query this message store property and begin sending and receiving messages when the property value is defined.

+ INSERT succeeds

+ UPDATE & DELETE fail with MSSQL Msg 7352, Level 16

With IQ set to Force_Updatable_Cursor=ON, INSERT fails additionally:

+ UPDATE & DELETE continue to fail with the same error as above

+ INSERT fails with MSSQL Msg 7343, Level 16

The linkage between these two options is possibly not clear in the user interface. To clarify the relationship, the "Results" panel in the "Options" dialog has been updated to disable the option to show all result sets when run in console mode when "Show multiple result sets" is turned off. Note that in that case, "all" is the same as "last".

SELECT * FROM myTable;

OUTPUT TO 'c:\\test.txt';

Then an INSERT, UPDATE, or DELETE statement was executed, which would have caused the statements following the SELECT statement (in this case, the OUTPUT statement) to have been reexecuted. This has been fixed.

For example, a query of the form

SELECT * FROM table WHERE col-a LIKE ?

where col-a is a 5-character column with a value '12345', would have generated a row in the result set for a host input value of '1234567%' (because that value would be truncated to '12345').

Similar problems existed with other comparisons.

running services was available. This option has been removed for all services, except for

dbltm.

For example, the following query could give the wrong answer for some query execution plans:

create table TOB( c1 int, c2 int);

create index TOBx on TOB(c1,c2);

insert into TOB

select row_num c1, 20-c1

from rowgenerator where row_num <= 20;

commit

select *

from TOB T1 with( index(TOBx) ), TOB T2 with( index(TOBx) )

where T1.c1 = T2.c1

and T1.c2 in (15,16,17,18,19) and T1.c1 in (1,2,3,4,5)

order by T1.c2

The wrong answer could have been returned in some cases if a Merge Join was used for the above query. This problem has been fixed.

Note, an application that is dependent upon finding the .odbc.ini file in the current directory will need to explicitly include the current path or full filename. The environment variables ODBCINI and ODBC_INI can be used to specify the complete filename. The environment variables ODBCHOME, HOME and PATH can be used to specify the directory containing .odbc.ini. Consult the SQL Anywhere documentation for details.

Case 1: If a PrepStmt pods object was not explicitly closed, the SQL string stored in the object was not freed when an AvantGo client exited.

case 2: In SyncParms.getAuthenticationParms(), pods array elements used to construct the output array were not freed properly afterwards.

Engineering cases 409529, 415999, and 417415 described applying performance upgrades by using additional command line parameters on the QAnywhere server:

-sl java ( -Dianywhere.qa.db.upgradeDeliveryColumns=true )

As there were situations where it was not possible to start the MobiLink server with a database user having schema change permissions, the scripts:

patchasa1.sql

patchmss1.sql

patchora1.sql

were developed for applying the performance upgrades against Adaptive Server Anywhere, Microsoft SQL Server and Oracle consolidated databases respectively.

patchasa2.sql

patchmss2.sql

patchora2.sql

for Adaptive Server Anywhere, Microsoft SQL Server and Oracle consolidated databases respectively.

Note that the behaviour of the (foreground) blocking GetMessage methods has not changed - these have always efficiently waited for a message to be put on a queue.

This worked fine with ASA 9.0.1

(yes - another ASA behavior change fixed via PocketBuilder)

Note, similar problems with SQLConnect, SQLBrowseConnect, SQLDriverConnect, SQLAllocStmt/SQLAllocHandle and SQLFreeStmt/SQLFreeHandle have also been corrected.

The following Delphi code demostrates the problem:

procedure TForm1.Button1Click(Sender: TObject);

var

adoParams: Parameters;

nCnt: integer;

begin

mmoParams.Clear;

FAdoCommand.Set_ActiveConnection(ADOConnection1.ConnectionObject);

FAdoCommand.Set_CommandText("sp_test");

FAdoCommand.Set_CommandTimeout(30);

FAdoCommand.Set_CommandType(adCmdStoredProc);

adoParams := FAdoCommand.Get_Parameters();

adoParams.Refresh;

for nCnt := 0 to adoParams.Count - 1 do begin

mmoParams.Lines.Add(adoParams.Item[nCnt].Name);

end;

end;

This problem has been fixed.

create procedure f2c("temp" float, proxyurl long varchar )

url 'http://localhost/f2c' proxy '!proxyurl'; // where proxyurl = NULL

create procedure f2c("temp" float, schemehost long varchar, path long varchar )

url 'http://localhost/f2c' proxy '!schemehost!path'; // where schemehost = NULL and path = NULL

create procedure f2c("temp" float )

url 'http://localhost/f2c'; // proxy not defined

whereas:

create procedure f2c("temp" float, hostname long varchar, path long varchar )

url 'http://localhost/f2c' proxy '!http://!hostname/!path'; // does not resolve to NULL even if hostname = NULL and path = NULL

can never resolve to NULL due to the static text within the value of the PROXY attribute.

An empty string '' is not treated the same as a NULL. If used within one or more substitution parameters, the empty string will resolve to an empty string - not NULL. In the case of the first two examples, if all substitution parameters were empty strings, the proxy attribute would equal '' (empty string). Conversely, in the fourth example proxy would resolve to 'http:///', whether the substitution parameters were NULL or '' (empty string).

Default Behaviour of undefined (or resolve to NULL) Attributes

- ** type - 'SOAP:RPC'

- namespace - URI composed of scheme, hostname and port:

If type 'SOAP:DOC' Http SOAPAction header is composed of URI/operation, where operation is the soap service endpoint. e.g.: if the operation at localhost is login, then the namespace = 'http://localhost' and the SOAPAction : "http://localhost/login"

If type 'SOAP:RPC' resulting Http SOAPAction header is SOAPAction : ""

- url - The URI 'NULL' is invalid. SQLCODE = -980

- proxy - no proxy.

- clientport - use any free port.

- certificate - no certificate.

- *header - no user defined HTTP headers.

- *soapheader - no soap headers.

Notes:

* header and soapheader clauses are a new features in 10.0.

** type substitution parameters are not supported prior to 10.0.

For example:

select id,

sum( quantity) over (partition by size )

from product p

where exists( select 1 from sales_order_items s where s.quantity > p.quantity )

and size = 'Large'

order by id

1) the MobiLink synchronization was running with the rowset size greater than 1: the command line option -s X (X > 1) was used or the command line contained no -s option;

2) the deadlock occurred when the MobiLink synchronization server was applying the upload operations in multi-row mode and there was no deadlock when the server was retrying to apply the same operations for the current table in single-row mode.

This problem is now fixed.

The following examples demonstrate the problem:

select * from product p where p.id > any( select TOP 10 prod_id from sales_order_items s where s.quantity > p.quantity )

select * from product p where exists( select TOP 10 START AT 2 * from sales_order_items s where s.quantity > p.quantity )

The following example illustrates a relevant use case:

alter procedure f2c("temp" float )

url 'http://hostname:8081/f2c'

type 'SOAP:RPC'

proxy 'http://proxy-host:8080';

Windows Mobile' would have remained on the Windows Start menu. This has been fixed

Note, this problem also affects the Console utility and the plug-ins for Sybase Central.

For example (x is a remote table)

declare @x int

declare @y int

select @x =1

select @y =1

if (select x from x where y = @y) = @x + 1

print 'yes'

else

print 'no'

This has been fixed.

documentation, a message is always displayed when an error occurs. This change has been reversed, so that error messages are now displayed.

<?xml version="1.0" encoding="UTF-8"?><SOAP-ENV:Envelope...>

<SOAP-ENV:Body>

<tns:hello_svcResponse>

<tns:hello_svcResult xsi:type='xsd:string'><tns:rowset><tns:row><tns:hello>Hello World</tns:hello></tns:row></tns:rowset>

</tns:hello_svcResult>

<tns:sqlcode>0</tns:sqlcode>

</tns:hello_svcResponse>

</SOAP-ENV:Body>

</SOAP-ENV:Envelope>

A SOAP client will decode hello_svcResult element into a string as follows: <tns:rowset><tns:row><tns:hello>Hello World</tns:hello></tns:row></tns:rowset>

Note that using the view outside of an expression would have worked fine.

A new feature has now been implemented so that the SA server will allow connections to the default database, even though the Open Client login server name does not match the name of the default database, provided that the connection string does not involve starting a database (i.e. there is no DBF=), and the server is only running one database. If both of these conditions are met, then the Open Client connection will be allowed to the default database.

For example:

create table T (x tinyint);

create table R (x smallint);

SELECT * FROM R, (SELECT T.x FROM T GROUP BY T.x ) dt

WHERE R.x = dt.x

CurrentCacheSize Current cache size in kilobytes

Isolation_level Controls the locking isolation level

Optimization_goal Optimize queries for first row or all rows

Optimization_level Reserved

Optimization_workload Controls whether optimizing for OLAP or mixed queries

ProductVersion Product version

User_estimates Controls whether to respect user estimates

Note, logging graphical plans to the server log is enabled by starting the server with the -zx command line option, or by calling the system procedure sa_server_option:

sa_server_option ( 'RequestLogging', 'NONE' );

sa_server_option ( 'RequestLogFile', {file_name} ); -- append to file

sa_server_option ( 'RememberLastPlan', 'ON' );

sa_server_option ( 'LogExpensiveQueries', {x} ); -- cost {x} threshold in milliseconds

SELECT * FROM ASASERVER..dba.customer

would have caused the error:

Msg 7320, Level 16, State 2, Line 1 Cannot execute the query

However, the OPENQUERY form of SELECT works fine:

SELECT * FROM OPENQUERY( ASASERVER, 'SELECT * FROM customer')

This problem has now been fixed. Note, this problem does not appear with SQL Server 2000.

The following query demonstrates this problem:

SELECT row_num,

SS2

FROM

(

SELECT R.row_num,

( SELECT R.row_num FROM sys.dummy D1 ) AS SS1,

( SELECT SS1 + (100*row_num) from sys.dummy D2 ) AS SS2

FROM dbo.rowgenerator R

) DT

The problem can be worked around by including both subselects in the result set of the query.

For example:

INSERT INTO tab( pk, col1) SELECT max(pk)+1, 15;

SELECT sum( pk ), 'a constant' FROM tab GROUP BY col1;

This has now been corrected.

For example:

select cast( 0xF1234ABCD as bigint );

This has been fixed, as now an extra zero is added to the front of odd length hexadecimal strings. Note, hexadecimal strings that are odd in length, and too large to fit into a BIGINT, will still generate a syntax error.

The following is an example of the type of query that would be affected by this problem if used in a keyset-driven cursor:

select ( if D1.y is null then 0 endif ) as z

from T1 left outer join

( select T2.b,

cast( sum( if T2.c = any( select T3.e from T3 WITH ( INDEX(T3) )

where f in( 1001,1020,1040,1080,10100,10200,10400,10500,101000,102000))

then T2.d

endif) as numeric(12,4) ) as y

from T2

group by T2.b

) as D1

on T1.a = D1.b

The incorrect behaviour relies on the specific access plan selected. This problem has been fixed.

Note that we do not guarantee that duplicates will not be delivered from QAnywhere to JMS, but duplicates should only occur in rare situations, for example where the QAnywhere MobiLink server or the dbms or the JMS server was to crash.

- There was a problem accessing the consolidated database, for example, the consolidated database engine crashed,

- There were messages queued for delivery from QAnywhere to an external JMS server via the QAnywhere JMS connector,

- An upload message transmission had just occurred, containing messages meant for the QAnywhere JMS connector

If a shutdown was attempted at this point, it was possible that the QAnywhere MobiLink server would not shutdown. As a work around, either the database server would have to be brought back on line, or the QAnywhere MobiLink server process would have to be killed. No message data would be lost. This has now been fixed so that a shutdown should not hang under these circumstances.

As a work around, the connector property ianywhere.connector.incoming.retry.timeout can be set to a very large value, for example 1000000000. This property indicates how many times the QAnywhere connector will retry transferring a JMS message to QAnywhere before giving up. Unfortunately, the default value for this property was set to 5, which was much too low. It was not documented, improperly named, and had no support for specifying an infinite value. In recognition that this property's name implies a duration rather than a number of retries, a new property, ianywhere.connector.incoming.retry.max, has been added as a replacement for ianywhere.connector.incoming.retry.timeout. The property ianywhere.connector.incoming.retry.max indicates the maximum number of times the connector will retry transferring a JMS message to QAnywhere before giving up. The default is -1, which indicates that the connector will never give up. Please now use this new property.

Also, the fact that the QAnywhere connector had given up trying to transfer the message, was also not added to the dead message destination. Now, if the connector reaches the maximum number of subsequent failed attempts to transfer a JMS message to QAnywhere, the JMS message will be re-addressed to the ianywhere.connector.jms.deadMessageDestination property value.

Could not load dynamic library '<dll name>' [-620] ['WW006']

The external function DLL was being loaded using the name of the DLL encoded in the database's character set. This has been fixed so that the external function DLL is now loaded using the name of the DLL encoded in the native character set.

Since ulapi.c is a distributed source file, a work around for this problem is to remove the first call to UL_EXIT_RUNTIME() in ULTable::init().

New option documentation:

-su server option

Function: Set the password for the DBA user of the utility database (utility_db), or disables connections to the utility database.

Syntax: { dbsrv10 | dbeng 10 } -su password ...

Applies to: All operating systems and servers.

Description:

This options specifies the initial password for DBA user of the utility database. By using a value of NONE for the password, all connections to the utility database are disabled. You may wish to use dbfhide to obfuscate the password, and reference the obfuscated file on the command line.

If this option is not specified, the personal server allows connections with the DBA userid and any password. If this option is not specified, the network server does not allow connections to the utility database unless the util_db.ini file exists and the userid is DBA with a password which matches the password in the util_db.ini file. On a network server, if both -su and util_db.ini are used, util_db.ini is ignored.

Note util_db.ini is deprecated and will be removed a future version of SQL Anywhere. While connected to utility_db, the GRANT CONNECT TO DBA [IDENTIFIED BY 'new_password'] statement may be used to change the password for the DBA user of the utility database, and the REVOKE CONNECT FROM DBA statement can be used to disable connections to the utility_db.

Examples:

dbeng10 -su None demo.db

create util_db_pwd.cfg containing only the text "utilpwd"

dbfhide util_db_pwd.txt util_db_pwd_hide.txt

dbsrv10 -su @util_db_pwd_hide.txt -n my_server demo.db

See also:

- utility database

- util_db.ini

Note, a similar problem with the SQL Anywhere OLE DB provider on 64-bit platforms has also been fixed.

A workaround is to set the option MAX_PLANS_CACHED to zero (although this will hurt the performance of stored procedures).

support" check box was checked or not. This has been fixed.

Note, that apostrophes in database file names are not supported by CREATE DATABASE and other tools, so using apostrophes in database file names is not recommended.

non-threaded Unix clients, would have caused the application to hang. This has now been fixed.

In the course of fixing this problem a second memory leak was found that affected columns that had check constraints, but were not user defined datatypes. This has been fixed as well.

For example, running dbmlsync on 2006-08-18, against a remote database that contains "DELETE_OLD_LOGS = 10 days", will cause dbmlsync to only delete the offline transaction logs that were created on or before 2006-08-07. Any offline transaction logs that were created after 2006-08-07 will not be deleted, even if these offline transaction logs are no longer needed for synchronization.

- strings that begin with white space, or single or double quotes

- strings that end with white space

- strings that contain semicolons

Note, any other service name may contain a forward slash.

The option is used as follows:

dbmlsrv9 ... -x http(...;session_key=[cookie|header];...) ...

When the session_key is cookie, an HTTP cookie is used to track HTTP sessions. When the session key is header, an HTTP header is used. The default value is cookie.

In a related issue, the result of casting a value to a user-defined data type might not have been described with the appropriate user-defined domain.

Both these problems have been fixed.

READ test2.sql;

If the current directory is "C:\Home", and the following is run:

DBISQL c:\test\test.sql

the READ statement is now able to resolve the relative file name "test2.sql" to "c:\test\test2.sql" by using the folder name of the file which contains the READ statement. Previously, "test2.sql" would not have been found unless it was in the path, or SQLPATH.

START DATABASE 'db-file' ... DIRECTORY 'dbspace-directory'

When a dbspace directory is specified, the server will look only in that directory for the files for that database. For example, if the server is started in the same directory as all of the dbspaces, adding "-ds ." will tell the server to find the log and all dbspaces in the current directory.

During startup, a message will be displayed in the server messages window, e.g.:

Opening dbspace file 'path\sp1.db'

to indicate the location of the dbspace.

The workaround is to use ulload -i. This has been fixed by effectively invoking the -i functionality, but only for _no_sync tables. So now rows populated through ulload can be deleted, and they will be physically removed from the database on a commit.

For example:

SET TEMPORARY OPTION isql_show_multiple_result_sets = on;

This is not a new option per-se, but the ability to modify it from the SET statement is new.

A workaround is to change data source names such that the above condition is avoided.

Calling DBSynchronizeLog, would also have resulted in memory being left unfreed. The amount of memory left unfreed was proportional to the number of articles contained in all the publications involved in synchronization subscriptions in the database being synchronized.

Both of these memory leaks have now been fixed.

For example:

select table_name,

( select list(column_name) from syscolumn a where a.table_id = b.table_id ) cols

from systable b

where cols like 't%'

This has been fixed.

Note that if a server was running multiple databases, databases other than the one being backed up could have been affected.

For example, the following would have generated the syntax error:

select min( cast( 'fred'||dba.NewID() as varchar(25) ) )

from remote_t

where remote_t is a proxy table and NewID() is a user-defined function.

"There was an error reading the results of the SQL statement.

The displayed results may be incorrect or incomplete."

when attempting to display a result set which contained numeric data that was undisplayable. This error message has been replaced with the the textual representation of the value which is returned by the server. For example, the following statements now display a result set with one value ("?"), whereas previously, the result set column header and the error message above were displayed, with no values at all.

set temporary option precision='1';

set temporary option scale='0';

create variable v1 decimal;

create variable v2 decimal;

set v1=9;

set v2=9;

select v1*v2;

A work around is to create an instance of a ULConnection class before invoking DownloadFile().

For example:

// C#

ULConnection xxx = new ULConnection();

// If the compiler complains that xxx is never used, add the do nothing code below

if( xxx.State = ConnectionState.Open ) {

// will never happen

throw new OutOfMemoryException();

}

The behaviour is not going to be changed in 10.x and newer versions, as it is more consistent with what happens when the insert fails for a row that already exists, and the ON EXISTING clause was not specified. As an example, the following code will also cause autoincrement values to be skipped:

create table t1( c1 int primary key, c2 int default autoincrement );

insert into t1(c1) values(1);

insert into t1(c1) values(1);

insert into t1(c1) values(2);

The values inserted for the example above are:

1,1

1,3

select cast( cast( 'Jan 1 0001 12:00:00:000AM' as datetime ) as smallint )

should return the value 10101, but was failing with the error "cannot convert timestamp to a smallint". This issue has been fixed.

In a related issue, certain operations that should result in conversion errors were incorrectly succeeding. Consider the following script:

create table t1 (c1 char(1), c2 smallint)

go

create table t2 (c2 datetime)

go

insert into t1 values ('Y', 21)

go

insert into t2 values ('Jan  1 1995 12:00:00:000AM')

go

select c1 from t1 where c2 in (select c2 from t2)

go

It should fail with the error "ASA Error -157: Cannot convert 21 to a date", but was not. This has been fixed so that the appropriate error is generated.

menu item and toolbar button would not have been enabled until F2 was pressed, or the area outside the table editor was clicked. This has been fixed.

Note that selecting the model in the tree would avoid this problem.

isql_show_multiple_result_sets

isql_print_result_set

This has been fixed.

A workaround is to deploy to a file only for the consolidated, and in the resulting SQL file change the WHERE clause in the INSERT INTO statement that follows the CREATE TABLE statement for the shadow table, so that it excludes deleted rows. If the defaults are used, this would involve changing 'Y' to 'N'.

menu item and toolbar button would not have been enabled until F2 was pressed, or the area outside the Mapping Editor was clicked. This has been fixed.

fixed.

Note, this would only have occurred with servers running on Unix platforms.

A work around is to first drop or disable views that depend on tables and views owned by the user being dropped. This process can be easily implemented in SQL by making use of the 'ALTER TABLE .. DISABLE VIEW DEPENDENCIES' and 'ALTER VIEW ... DISABLE' statements.

Note, this same problem also affected the "Data" tab in the SQL Anywhere plug-in for Sybase Central, and has been fixed as well.

Note, userids and passwords must still be valid connection string values and must not contain leading or trailing spaces, leading single or double quotes, or any semi-colons.

For example:

select * from product p1

where EXISTS(select *

FROM product p2

left outer join (select p3.id, count(*) from product p3 group by p3.id ) as P(ID, c) ON P.id = p2.id

WHERE p2.id = p1.id )

Note, the problem does not exist if the remote server is created using one of the ODBC classes.

A workaround for this situation is to do the following:

1. Right click on the application in the task bar.

2. Select Move

3. Use the keyboard cursor keys to bring the window back to the visible desktop and hit Enter when it抯 properly positioned.

will continue to work as they do now. To get the new behaviour, set the value of this option to ON.

In versions of 10.x starting from 10.0.1, the default for this option will be ON. In the next major version, this option will be removed, and the new behaviour will always be used.

Note, this problem affected the Interactive SQL utility, which is fixed as well.

A schema upgrade is not currently allowed[SQL Offset: 0]

SQLState=ERROR, ErrorCode=-953

This was typically seen after executing a SELECT statement, whose result set was displayed in the "Results" pane, and either the result set was edited, or the "Show multiple result sets" option was on. This has been fixed.

A workaround would be to use the following keystrokes:

CTRL+INS Copy

SHIFT+INS Paste

SHIFT+DEL Cut

<column> LIKE <pattern>

where <column> is a column of exact numeric type <DOMAIN> and <pattern> is a constant string containing no wild-cards should generate a sargable predicate:

<column> = CAST( <pattern> AS <DOMAIN> )

For example, the query:

select * from systab where table_id like '1'

should consider using an index scan on systab.table_id = 1, but this inference was not performed. This has been fixed. See also Engineering case 336013.

If this loss is an issue, a database character set for which no loss occurs should be chosen. For example, choosing UTF8 on a Japanese machine will ensure that filenames are preserved.

We now make a better attempt at reporting character set conversion errors on database filenames.

In the ULPod documentation, and in the sample readme file, it is explicitly emphasized that only one type of the ULPod dll file should be deployed to the client device. It must be either the standard ulpod10.dll or the client/server version ulpodclient10.dll, but never both. The reason is that when the M-Business client browser AvantGo.exe launches, it will load ALL the dll files it can find in the pods directory. If both ulpod10.dll and ulpodclient10.dll are found in the pods directory, both are loaded into memory. Then when the JavaScript code tries to create a ULPod DatabaseManager object:

var DatabaseMgr = CreateObject( "iAnywhere.UltraLite.DatabaseManager.CustDB" );

both ulpod10.dll and ulpodclient10.dll are asked by the AvantGo JavaScript engine to claim ownership of the pods object name. The result is random. If ulpodclient10.dll is first called to claim the ownership, and if there is no uleng10.exe installed on the device, an error will be returned saying the UltraLite engine cannot be found.

To solve this problem, a new section has been added to the standard ULPod pods object namespace to clearly identify which ULPod DLL should be loaded. The previous ULPod name prefix was "iAnywhere.UltraLite.DatabaseManager", followed by the application name. The new rules now add ".Standalone" or ".Engine" after the previous ULPod name space, and before the application name.

Thus if the standard ULPod dll is required, the following call should be made:

var DatabaseMgr = CreateObject( "iAnywhere.UltraLite.DatabaseManager.Standalone.CustDB" );

if the client/server version of ULPod dll is required, the following call should be made:

var DatabaseMgr = CreateObject( "iAnywhere.UltraLite.DatabaseManager.Engine.CustDB" );

If no runtime-type is provided in the object name prefix, and both dlls are found in the pods directory, which ULPod dll is loaded will be non-deterministic as before.

Also, when connected to an UltraLite database, the tooltip for column headers for result sets showed the wrong size for non-numeric types that have a variable size. For example, the type of a BINARY(20) column was displayed as "binary(0)".

Both of these problems have now been fixed.

objCmd = CreateObject("ADODB.Command")

objCmd.CommandText = "Add_User"

objCmd.CommandType = adCmdStoredProc

objCmd.ActiveConnection = objConn

objCmd.Prepared = True

objCmd.Parameters.Append(objCmd.CreateParameter("Add_User", 3, 4))

objCmd.Parameters.Append(objCmd.CreateParameter("@userid", 200, 1, 25, "jbjones"))

objCmd.Parameters.Append(objCmd.CreateParameter("@first", 200, 1, 25, "John"))

objCmd.Parameters.Append(objCmd.CreateParameter("@last", 200, 1, 35, "Jones"))

objCmd.Parameters.Append(objCmd.CreateParameter("@phone", 200, 1, 18, "5558836300"))

objCmd.Parameters.Append(objCmd.CreateParameter("@extension", 200, 1, 6, "6300"))

An erroneous SQL statement like the following is generated.

?=call Add_User(?,?,?,?)

If the objCmd.Prepared = True statement was not included , or the Prepared property was set to False, then the problem did not appear. This has now been fixed.

achieved through new methods on the DatabaseManager object.

New API methods:

For SA 10

ULPodConnection DatabaseManager.openConnectionEx(string conString)

ULPodConnection DatabaseManager.reOpenConnectionEx(string conName)

For ASA 9

ULPodConnection DatabaseManager.createDatabaseEx(string conString)

ULPodConnection DatabaseManager.createDatabaseWithParmsEx(ConnectionParms parm)

ULPodConnection DatabaseManager.openConnectionEx(string conString)

ULPodConnection DatabaseManager.openConnectionWithParmsEx(ConnectionParms parm )

ULPodConnection DatabaseManager.reOpenConnectionEx(string conName)

The difference between the Ex() and non-Ex() functions is that the Ex() version

supports multiple connections, while the non-Ex functions do not.

SQL type DT_TIMESTAMP_STRUCT, and the ODBC type SQL_TIMESTAMP. If a stored timestamp value with an invalid year was converted to a string, the year component of the string would have contained asterisks (e.g. ****-02-28 ). This has now been fixed so that the server correctly generates the error SQLE_CONVERSION_ERROR (-157) for invalis timestamp structures, even when the option Conversion_error is On.

For example:

SQL Anywhere UltraLite Database Generation Tool Version 10.0.0.2716

Loading schema

Creating empty UltraLite database

Collation: 1252LATIN1

Done creating database

create table foo ( a integer primary key default autoincrement );

insert into foo on existing update select a from foo;

This has been fixed.

For convenience, calling the http_header() function and specifying the pseudo-header @HttpQueryString will return the query string component of the URL verbatim. The function call next_http_variable() can be used to cycle through all the keys, regardless of whether or not they contain a value.

A query string with no key and a value (e.g. http://localhost/x_service?=value) can only be processed by parsing the query string as it is returned from an http_header('@HttpQueryString') call.

Example:

http://localhost/a_service?h&0&name=value&=x

http_header('@HttpQueryString') returns: h&0&name=value&=x

An iteration of the next_http_variable function would return the following keys: h, 0 and name (x is ignored). Using the http_variable function, the value of h and 0 is '' (empty string) and the value of name is value.

This fix affects both the server and dbicu library - if the server starts a database that requires dbicu, then both the server and dbicu library must have this fix. If the server has this fix but dbicu does not, then the server will display the following message when it attempts to load the database:

"Unable to start database "<dbname>": dbicu library version mismatch"

objCmd.ActiveConnection = objConn

' Set CommandText equal to the stored procedure name.

objCmd.CommandText = "DBA.SProc"

objCmd.CommandType = adCmdStoredProc

' Automatically fill in parameter info for the stored procedure.

objCmd.Parameters.Refresh()

' Set the parameter values.

objCmd("@user_id") = "ianywhere"

objCmd("@password") = "test"

objCmd("@first") = "Iam"

objCmd("@last") = "Anywhere"

objCmd("@phone") = "5558836300"

objCmd("@extension") = "6300"

When ADO executeed the Refresh() method, the provider was asked to provide parameter information on the "SProc" stored procedure. A catalog name ("SProc") and schema owner name ("DBA") were passed in. Since catalog names are not supported and ignored, information on all stored procedures owned by DBA were returned to ADO.

This problem has been fixed. An incorrectly set property, DBPROP_CATALOGLOCATION, resulted in the parsing error. The property value has been corrected.

The following example illustrates the complexity of a search condition necessary for this problem to occur. However, the number of disjunctive clauses, and the number of predicates in each clause, are not alone a guarantee of exhibiting the problem:

select count(*)

from in_all

where

(d4=1 and d5=9 and d6=90 and d8=122)

and

(

(d7=4 and d8=122 and d9=138)

or (d8=122 and d9=112)

or (d3=57 and d7=4 and d8=122 and d10=4)

or (d8=122 and d9=65)

or (d3=76 and d7=1 and d8=122 and d9=62 and d10=2)

or (d3=62 and d4=1 and d7=1 and d10=2)

or (d3=76 and d4=1 and d5=9 and d7=3 and d10=1)

or (d3=55 and d4=1 and d5=9 and d6=90)

or (d3=124 and d7=4 and d8=122)

or (d4=1 and d7=2 and d8=122 and d9=37 and d10=1)

or (d3=22 and d7=1 and d8=122 and d10=2)

or (d3=76 and d4=1 and d5=9 and d8=122 and d9=37)

or (d3=31 and d7=1 and d8=122 and d9=59 and d10=2)

or (d3=57 and d4=1 and d7=1 and d8=122 and d9=59 and d10=2)

or (d3=110 and d4=1 and d5=9 and d10=4)

or (d3=76 and d7=1 and d8=122 and d9=23 and d10=3)

or (d3=150 and d7=1 and d8=122 and d9=23 and d10=2)

or (d3=36 and d7=1 and d10=2)

or (d3=30 and d4=1 and d7=2 and d8=122)

or (d4=1 and d7=5 and d8=122 and d9=136 and d10=1)

or (d3=76 and d4=1 and d5=9 and d6=90 and d7=2 and d10=4)

or (d3=110 and d8=122 and d9=59 and d10=1)

or (d4=1 and d5=9 and d7=3 and d8=122 and d9=37 and d10=4)

or (d3=61 and d4=1 and d5=9 and d8=122)

or (d3=91 and d7=3 and d10=4)

or (d3=216 and d7=1 and d10=2)

or (d4=1 and d5=9 and d7=4 and d8=122 and d9=37)

or (d3=30 and d4=1 and d8=122 and d9=23)

)

and date1 = 200406

This has been fixed.

The behaviour has not changed if a mime type is not specified. Parameter names and values (multiple parameters are permitted) are url encoded within the body of the http request.

Examples:

call sa_make_object( 'procedure', 'setmime' );

alter procedure setmime( val long varchar, mime long varchar, anurl long varchar)

url '!anurl'

type 'http:post:!mime';

from dbisql:

call setmime('hello, this is plain text', 'text/plain', 'http://localhost/someservice'); // POST body is not url encoded, it contains hello, this is plain text, Content-Type: text/plain

call setmime('<hello>this is xml</hello>', 'text/xml', 'http://localhost/someservice'); // POST body is not url encoded, it contains <hello>this is xml</hello>, Content-Type: text/xml

call setmime(null, 'text/plain', 'http://localhost/someservice'); // POST body is empty, Content-Type: text/plain, Content-Length: 0

- SQL Syntax of TYPE clause for create/alter stored procedure

HTTP:POST[:type/subtype]

- Error checking

Minimum error checking is provided for the mime type specification. A mime type specifier must contain a slash character. The colon delimiter may be padded on both sides by one or more spaces. Only a single parameter (after parameter substitutions) may contain the value used to populate the body of the HTTP request.

EXEC SQL SELECT ... INTO <hostvar list>;

EXEC SQL DROP ...;

The DROP would have been missing from the generated. This has been fixed.

mlsrv10 ... -xo http(...session_key=[cookie|header];...) ...

When the session_key is 'cookie', which is the default, an HTTP cookie with name JSESSIONID is used to track HTTP sessions. When the session key is 'header', an HTTP header is used. The 'header' option is useful if the cookie name of JSESSIONID conflicts with an existing cookie, in which case MobiLink will track HTTP synchronizations using a custom header entry that will not conflict with any other HTTP infrastructure. In order to implement this feature in the MobiLink server, changes were also required to the MobiLink client. Thus to get the new behaviour, clients need to be upgraded as well.

Note, the session_key option is supported by the -xo option only.

The following is a copy of the IPSM bug description from Intel:

Note that other ASA components or 3rd party applications which might write compiled-in strings to a file on IPSM will also be susceptible to this bug. For 3rd party applications (or for ASA components prior to this fix), the user can avoid the problem by copying the executables & DLLs into the RAM disk or onto another flash device and running them from there instead of from IPSM.

You cannot create a non-recurring schedule with a start time

in the past, since such a schedule can never trigger an event.

You must either specify a recurrence interval and/or days,

or change the start time to a time in the future.

information for dbspaces other than SYSTEM. For non-SYSTEM dbspaces, the detail information of the main dbspace was entered. If the drive type was "UNKNOWN", the detail information was incorrectly deleted when moving a LAST_DTT to DTT. This has now been fixed.

PREPARE

DESCRIBE

OPEN

FETCH ...

CLOSE

DROP

While the server can handle the statement being dropped before a cursor using that statement is closed, sa_get_request_times() could not. This has been fixed.

One possible workaround is to remove the lines representing the statement DROP from the request log before calling sa_get_request_times(). Assuming that statement handles are not re-used within the request log, this should allow the correct times to be obtained.

select cast( cast( 17 as numeric(5,2) ) as varbinary )

would result in error -157: "Cannot convert numeric to a varbinary." This has been fixed.

A C# example:

SAConnection conn = new SAConnection("dsn=SQL Anywhere 10 Demo;uid=dba;pwd=sql");

conn.Open();

SACommand cmd = new SACommand( "DELETE FROM Departments WHERE DepartmentID = ?", conn );

cmd.Parameters.Add( "p1", SADbType.Integer );

cmd.Parameters[0].Value = "xxx";

int rowsAffected = cmd.ExecuteNonQuery();

Note that SQL Anywhere just skips over the BOM even for UTF16. It does not currently allow the BOM to control the endian-ness of the data in the file (it is always assumed to be in platform endian). BOMs in UTF8 are meaningless but the Unicode standard does allow them. Some utilities such as Notepad on Windows put a BOM into files encoded in UTF8.

For detailed information on BOMs, see http://unicode.org/unicode/faq/utf_bom.html.

Note, this problem is a side effect of the changes made for Engineering case 443233. Both this problem, and the original problem for case 443233, have now been resolved.

Note, the Linux Itanium install is not affected by this problem.

corrected.

- it was running as a windowed application

- a .SQL file had been opened,

- the file had been modified in the "SQL Statements" field,

- the file had not yet been saved,

- when prompted whether to save the file or not, the "Yes" button was clicked.

This has been fixed.

Note that the network server is not affected by this.

Note that some text editors (e.g. Notepad) write the 3 byte header, but others do not.

Database backup not started

Database backup failed

These messages were not very informative. This has been fixed so that the following messages are now displayed:

Backup is unable to open database file '<file name>'

Database backup failed

If time information is important, then you should consider changing DATE columns to TIMESTAMP columns in your Oracle database.

listen for responses.

1) multiple connections must be executing JAVA at the same time,

2) the connection that executed JAVA last is the first one to return from JAVA,

3) that connection then subsequently disconnects,

4) no other connection executes JAVA after the connection in 3 above disconnects, and

5) one of the connections still executing JAVA tattempts to flush a message to System.out or System.err after the connection in 3 above disconnects.

This problem has now been fixed.

A workaround is to change one table mapping at a time.

Note, this problem did not occur when the graphical version of the integration component was used (dbmlsynccomg.dll).

- /tmp

- /tmp/.SQLAnywhere

- the value of the TMP environment variable, if set

- the value of the TMPDIR environment variable, if set

- the value of the TEMP environment variable, if set

- a symbolic link pointing to any of the above

When SATMP is set to such a non-standard location, the server and client will walk up the given directory path looking for directories owned by the current user with permissions set to 707, 770 or 700. For each directory found, the appropriate permissions (other, group, other+group respectively) will be set from the permission mask used to create temporary files.

For example, if the SATMP environment variable is set to: /tmp/restricted_permissions/sqlany, where restricted_permissions is a directory with permissions 700, then all files created in this directory will have permissions 700.

Previously, it was recommended to create QAnywhere client message stores with no transaction log. This is no longer the case, as the QAnywhere client is much more efficient when the message store is using a transaction log.

A workaround is to periodically disconnect and reconnect the connection.

Note, the fix will flag any index entry having a hash length between 242 and 248 as corrupt. Indexes with such entries can be found by running VALIDATE TABLE ... WITH EXPRESS CHECK ( or dbvalid -fx). Indexes with such entries will produce a message like

Index "t_l" has invalid entries - reorganizing the index may resolve the problem

Reorganizing the index by issuing a REORGANIZE INDEX command will fix the affected index entries (a validate with express check should run cleanly afterwards). Reorganizing the table is not recommended.

Note, that for this problem to occur, the application must install an asynchronous message handler. the Interactive SQL utility is one such application.

This problem has now been fixed.

For example:

SELECT id, c8 FROM T LEFT OUTER JOIN S on id = c8

If c8 was the 8th column in table S and the first row抯 value of id could not be found in c8, NULL would have been supplied for every value of c8 in the result set (even when S had a row where id = c8).

This has been fixed.

The defaults for the -ea option are as follows:

1. "none" if none of -ek, -ep, or -et are used

2. "AES" if -ek or -ep is used (with or without -et)

3. "simple" if -et is used without -ek or -ep

2) The -e option ('simple encryption') on dbinit is now deprecated. It will still be accepted in 10.x, but is not listed in the usage, and will be removed in the next major release. Instead, "-ea simple" should be used for the same behaviour.

ENCRYPTED OFF |

ON |

[ON] KEY key [ALGORITHM { AES | AES_FIPS } ] |

[ON] ALGORITHM { AES | AES_FIPS | SIMPLE } [KEY key]

The key and the algorithm can be specified in either order, and the algorithm can be specified without the key, although this is only useful for the 'simple' algorithm. The "SIMPLE" algorithm cannot be specified without a key. Algorithm names (AES, AES_FIPS, and SIMPLE) are case-insensitive.

This change only applies to the CREATE DATABASE statement; the CREATE ENCRYPTED FILE statement is unchanged.

For example:

SELECT * FROM OPENDATASOURCE('ASAProv.90','Data Source=dba_sql_90;User ID=dba;Password=sql')..dba.my_table

This has been fixed so that the OPENDATASOURCE procedure now works correctly.

The documentation mentions that the interface identifier is "Windows only", but this no longer applies. Although Linux (kernel 2.6.13 and higher) is the only OS thus far that appears to require that the interface identifier be specified, the option is available on all versions of Unix.

Below are some examples of entering an IPv6 address as the host parameter:

Global scope address, unique everywhere, so no interface index is required

-c "links=tcpip(Host=fd77:55d:59d9:56a:202:55ff:fe76:df19)" // no index required

-c "links=tcpip(Host=fd77:55d:59d9:56a:202:55ff:fe76:df19%2)" // all communication will be done through interface 2

-c "links=tcpip(Host=fd77:55d:59d9:56a:202:55ff:fe76:df19%eth0)" // all communication will be done through eth0

Link scope address, addresses are unique on each interface

-c "links=tcpip(Host=fe80::202:55ff:fe76:df19)" // possibly ambiguous (i.e. this host may exist through both eth0 and eth1)

-c "links=tcpip(Host=fe80::202:55ff:fe76:df19%2)" // not ambiguous because it must use interface 2

-c "links=tcpip(Host=fe80::202:55ff:fe76:df19%eth0)" // not ambiguous because it must use eth0

Note, that this problem could also have occurred if an implicit cast of a simple expression was repeated both inside and outside a window function.

For example:

select * from syscolumn c

where

(select Coalesce(sum(t.Count), 0) from systable t

where file_id = 1 and t.table_id = c.table_id) >= 0

This problem has now been corrected

CREATE TABLE T( id int not null primary key, c1 bigint, c2 varchar(200), c3 binary(10) )

The maximum row size for this table is approximately 222 bytes (4 for id + 8 for c1 + 200 for c2 + 10 for c3 = 222). (Note that internal data structures may add a few bytes to this value). This restriction was not properly enforced, so it was possible to create a table with a row size greater than 64K. Attempting to use this table would have caused the UltraLite application to crash. Now, attempting to create or alter a table that results in a table with a row size greater than 64K will generate an error; -1132 : "Maximun row size exceeeded" (SQLE_MAX_ROW_SIZE_EXCEEDED).

In cases where a database exists with a table already defined to have a maximum row size greater than 64K, UltraLite will no longer allow a connection to this database. This is necessary because the existence of this kind of table could cause database corruption. Unloading the database before applying this fix should allow existing schema and possibly data in other tables to be preserved.

For example:

update T with( index( I ) ) set a = a + 1

Would only have updated the first row returned by the index I. The server was bypassing optimization of queries with index hints, even if the index was not unique. Now, updates with index hints are bypassed only if there is a covered unique index.

Note, this problem affected all connections using the iAnywhere JDBC driver, regardless of whether or not the CharSet connection parameter was explicitly specified. This also affected the Java Tools, and actions like, for instance, creating a database from Sybase Central that had a non-ASCII, non-UTF-8 name.

This has been fixed.

- For SA, ASE, and MS SQL Server, the quoted_identifier option is now turned on by default at connect time and identifiers are always quoted.

- For Oracle, identifiers can always be quoted, so the server now always quotes identifiers in SQL generated for Oracle.

- For DB2 and generic ODBC remote servers, the old rules for quoting identifiers still apply. That is, quoting of identifiers is done based on whether or not the identifier contains special characters that require quoting.

In 10.0.0, the server now uses ICU and the character set "eucb5" used by the EUC_TAIWAN collation ended up using the "ibm-950_P110-1999" ICU character set which was distinct from (but close to) the ICU character set "windows-950-2000" used by the SQL Anywhere character set label "Big5" used by 950ZHO_TW.

The EUC_TAIWAIN collation now uses the EUC-TW character set and has had its character encoding specification corrected. EUC_TAIWAN databases created with 10.0.0 will continue to use the "eucb5" label for their character set; however, "eucb5" now maps to the windows-950-2000 ICU converter.

Although the EUC-TW character set was defined as "known by SQL Anywhere" (a client connection could request it, for example), the conversion tables for it were not shipped in the ICU dataset. Adding the EUC-TW conversion tables to ICU was the only part of this change made to 10.0.0.

Example:

AsaDataAdapter adapter = new AsaDataAdapter( "SELECT * FROM mytable", conn );

DataTable table = new DataTable();

adapter.Fill( table );

table.Rows[ 0 ][ 1 ] = "ABCDEFG";

AsaCommandBuilder cb = new AsaCommandBuilder( adapter );

adapter.Update( table );

This problem has been fixed.

Note that non-TDS based applications (i.e. applications that use ESQL, ODBC or the iAnywhere JDBC Driver) have always been able to fetch date and time data as DATE and TIME values.

- Devices with a sufficiently large heap (RAM) now use a RAM-based cache for VFS file access instead of a storage heap-based cache.

- A fix has been made to prevent a progress mismatch with MobiLink, if a reset was done at the right moment during a HotSync.

- A fix has also been made to resolve the problem where the HotSync conduit could have referenced freed memory (likely causing a crash), if the record database on the device was corrupt (missing pages), but caused no I/O errors.

For example, the following query (run on asademo.db):

select case when name in ('syscolumns') then name else 'OTHERS' end

from sysobjects

group by case when name in ('syscolumns') then name else 'OTHERS' end

order by case when name in ('syscolumns') then name else 'OTHERS' end

would have failed with the error "Function or column reference to 'name' in the ORDER BY clause is invalid". This problem has been fixed.

#if defined(UNDER_CE) && UNDER_CE < 300

#error "UltraLite applications for Windows CE must target Windows CE 3.0 or later"

#endif

Embedded Visual C++ 3.0 and 4.0 automatically defined UNDER_CE to be 300 and 420, respectively, but, unfortunately, Visual Studio 2005 defines UNDER_CE, but does not set it to the CE version, which was causing this check to fail. The workaround is to change the default settings in VS 2005 so that UNDER_CE is defined to $(CEVER).

This has been fixed by removing the check, so that the default settings of Visual Studio 2005 will work. For 10.0.1 and later, ulglobal.h will check that _WIN32_WCE is at least 300. Embedded Visual C++ 3.0 and 4.0 and Visual Studio 2005 all define _WIN32_WCE to the CE version by default.

with a "Server name already in use" error.

Note, if both servers were running the TCP/IP network protocol, which is the default, the error was correctly detected.

select count(*),

case when DepartmentName in ('R & D', 'R & D') then

DepartmentName

else

'Others'

end

from Departments

group by case when DepartmentName in ('R & D', 'Sales') then

DepartmentName

else

'Others'

end

The query would have returned results as though both IN lists were ('R & D', 'Sales'). This has been fixed so that now the query correctly returns the error -149: "Function or column reference to 'DepartmentName' must also appear in a GROUP BY"

A workaround for this problem is to specify "charset=utf-8" on the unload and reload connection parameters.

This has been fixed.

For example, the following query that should have returned several rows, would have returned at most one row:

SELECT t1.w FROM T t1 WHERE t1.x IN (SELECT max(t2.z) FROM T t2 WHERE t2.id = t1.id)

This has been fixed.

This cannot be done on UNIX systems unless the socket option SO_REUSEADDR is specified, which was not being done. This has been corrected.

This has now been fixed.

Note that a new dbicu10.dll is required for this fix.

A Visual Basic example:

cmd.CommandText = "INSERT INTO DateTable(cDate) values (?)"

cmd.CommandType = ADODB.CommandTypeEnum.adCmdUnknown

parm = cmd.CreateParameter("inDate", ADODB.DataTypeEnum.adDate, ADODB.ParameterDirectionEnum.adParamInput)

cmd.Parameters.Append(parm)

parm.Value = "2006-12-15"

cmd.Execute()

The value inserted would have been "2006-12-01".

This problem has been corrected, so that the correct date is now inserted.

This has been fixed.

java.lang.NumberFormatException: null

at java.lang.Integer.parseInt(Unknown Source)

at java.lang.Integer.valueOf(Unknown Source)

at ianywhere.ultralite.Index.<init>(Index.java:122)

at ianywhere.ultralite.Table._loadPrimaryKey(Table.java:481)

...

For example, attempting to connect to the database "application.udb" using the name "Application.udb", would have caused the problem to occur. This has been fixed.

The workaround is to specify the filename (directories do not need to match case) in the same case as the file was stored on disk.

correctly. Specifically, when input reached the end of a line, it would have wrapper over itself, instead of continuing on the next line. During initialization, wrapping was disabled on the terminal, and was not re-enabled on exit. This is now fixed by re-enabling wrapping on exit.

Further testing revealed that some runtime entry points did not handle other critical errors correctly, and would have caused the runtime to crash if a critical error had been signalled in a earlier call. These have been fixed as well.

Some self-registering DLLs require administrative privileges to register or unregister themselves. During a normal installation, the installer runs in an elevated state and a prompt for dbelevate will not be seen; however, if regsvr32 is used directly, the prompt may be seen. The SQL Anywhere OLE DBV provider, SAOLEDB, is Vista-aware and may request elevation when installed or uninstalled.

Note that the Remote-NDIS Host "IP address" and "Default gateway" properties must be configured on the Windows CE device in order for name resolution to work properly when connected on ActiveSync.

Here's an example:

using ( SAConnection conn = new SAConnection( "DSN=SQL Anywhere 10 Demo;UID=dba;PWD=sql" ) )

{

conn.Open();

for ( int i = 0; i < 1000; i++ )

{

SATransaction trans = conn.BeginTransaction();

SACommand cmd = new SACommand( "", conn, trans );

cmd.CommandText = string.Format( "INSERT INTO MyTable( id ) VALUES( {0} )", i );

cmd.ExecuteNonQuery();

trans.Commit();

}

}

The SAConnection.BeginTransaction method executes a SQL command to begin a new transaction, but it was not checking if the number of existing prepared statements had already exceeded the limit before executing the SQL. This has been fixed.

Cannot open transaction log directory "<log directory name>"

if the -c option was used to obtain the transaction log contents from a running server, and the log file included a path specification but the specified directory did not exist on the machine where dbtran was invoked. This has been fixed.

sent. This has been fixed.

Note that creating and using databases with security certificates worked, and the certificate was properly used. The problem was in unloading such a database and then reloading it, the certificate would have been lost. As a workaround, the certificate can be re-added from the original certificate file using the -t option to the UltraLite Load XML to Database utility (ulload), as in:

ulload -t <CertificateFile> -c dbf=newDB.udb saved.xml

Note that using a valid dotted reference like "select c1, u.f() from proxy_t", where f was a user-defined function owned by user u, would have worked, and will still work.

or 'T' can now be used to specify numeric values. For example, you can set a

dynamic trap to fire if the current cache size exceeds 200 MB using:

Trap1=1.3.6.1.4.1.897.2.1.1.11.1 > 200M

Note, the modifiers are case insensitive.

0xD800 )x2020 0x2020 ...

This problem has now been corrected.

A workaround is to use "select * from proc()" rather than "call proc", or to evaluate the expression into a variable before passing it as a call argument.

(1) The originator of a message did not see a 'received' status for a message that was received and acknowledged by another client,

(2) When a message arrived at a destination, the transmission status was not set to 'transmitted', so the status history did not show that the message was 'transmitted',

(3) The transmission status for system messages (push notifications and network status notifications) was not set to 'local', instead it was set to 'untransmitted',

(4) The transmission status for a message sent to a queue in the same message store was not set to 'local', instead it was set to 'untransmitted'.

These problems have now been corrected.

A work around is to increase the task limit (number of TASK threads) using the -gn option.

After this fix is applied, it is possible that indexes that were valid previously could now be considered corrupt. Running dbvalid will identify which indexes are corrupt and should be rebuilt. If the corruption is in a system index, it may be necessary to unload the database with older software, and reload it with a version with this fix.

select unistr( repeat( 'x', 1018 ) || '\u0041' )

The result should be identical to:

select repeat( 'x', 1018 ) || unistr( '\u0041' )

Instead, it was equal to repeat( 'x', 1018 ) || '\u0041'. This problem has been fixed.

SELECT RAND() a, RAND() b

would have incorrectly returned the same result for both a and b. This has been fixed.

Note, this problem did not affect non-deterministic user-defined functions.

As well, non-deterministic functions were being eliminated when ordering. For example, the query:

SELECT * FROM EMPLOYEE ORDER BY rand()

should now order the rows by the value of rand() computed for each row.

For example,

SELECT max( a ) FROM table ORDER BY max( a ) (table contains no rows)

This was fixed. A work-around woule be to recode the query as:

SELECT max( a ) FROM table ORDER BY 1.

A workaround is to deploy to a file and change column names to upper case.

This has been fixed so that a crash will not occur, and the startup process will attempt to continue as normal.

1. The "Messages" tab for a server store:

a) In the "Filter" panel

b) In the header for the first column

2. On the "General" page of a "Message Properties" dialog.

It's possible that this corruption could be prevented by increasing the server checkpoint urgency. The more often checkpoints occur the less likely one is to get a database with a very large checkpoint log.

As always it is important to maintain a tested backup and recovery strategy. Having a valid backup database in this case allows one to apply the current transaction log to that backup to recover all transactions.

New remote tables can be created (which adds a new row to the table mapping editor) by selecting File -> New Remote Tables. Remote tables can also be deleted (which removes the corresponding rows when the model is saved) by selecting the rows and then selecting Edit -> Delete, or clicking the Delete toolbar button, or pressing the Delete key. With these changes, the meanings of the table mapping editor's row header icons have changed. "New" (the yellow star) now means "Remote table is new" rather than "Table mapping is new"; "Delete" (the red X) now means "Remote table is marked for deletion", rather than "Table mapping is marked for deletion".

A new table mapping can be created by changing its Mapping Direction column from "Not Synchronized" to one of the other three values (Bi-directional, Download to remote only, or Upload to consolidated only) and choosing a consolidated table. Similarly, you delete a table mapping by changing its Mapping Direction to "Not Synchronized". In both cases, because the row in the table mapping editor has been changed, the "Edit" icon (the yellow

pencil) will appear in the row header. The "Edit" icon is also displayed when any other type of change is made to a table mapping, such as a change to the selected consolidated table or the download type.

The column mapping editor now has a Mapping Direction column between the remote and consolidated columns. This replaces the "Synchronized" check box column in previous versions. This change makes it harder to "unsynchronize" a column and easier to "delete" it; the former being far less common than the latter. The column mapping editor's row header icons have similar meanings to those in the table mapping editor, and remote columns can be deleted in the same way that remote tables are deleted. Remote columns can

only be added by adding a new remote table.

These changes have eliminated the inconsistencies between the table and column mapping editors.

more "H", "M", or "S" suffixes would always be present in the value, but it is not always true. This has been corrected.

Checking for available disk space ...

/bin/df: `\n.': No such file or directory

/bin/df: `\n.': No such file or directory

You do not have enough disk space in ''. Needed=0K Available=0K.

This would happen with "old" High-Availability keys. The newer High-Availability keys do not have this problem.

If you encounter this problem, you can either:

1. obtain a new High-Availability key that will fix the issue.

2. download the Developer edition from

http://www.ianywhere.com/forms/downloads/sql_anywhere/sqlany_10_developer_download.html

and provide the key(s) you already have during the install.

column-list had been changed. This would have only have occurred if some other column specified in the UPDATE's SET list changed its value.

For example:

create trigger T1_UA after update of C1 on T1

for each row

begin

message 'after update trigger on column C1 fired';

end;

insert into T1 ( C1, C2 ) values ( 'abc', 'xyz' );

The following UPDATE changes C2, but C1 does not change, so trigger T1_UA should not fire

update T1 set C1= 'abc', C2 = 'XYZ';

This has been fixed so that triggers no longer fire under these conditions.

For example, the following function meets the above criteria (though may not necessarily cause an engine crash in all cases). Here, p1() is a user-defined procedure.

CREATE FUNCTION f1()

RETURNS LONG VARCHAR

BEGIN

RETURN 'str1' || (SELECT * FROM p1()) || 'str2';

END

This issue has been fixed.

For example, the query:

select * from department

where dept_id = var1 OR dept_id = var2 OR dept_id = var3

can be transformed as follows:

select * from department

where dept_id IN ( var1, var2, var3 )

provided that var1,var2, and var3 are variables.

Additionally, predicate optimizations to simplify OR predicates may not have been taken. This could have impacted the choice to use an index scan during query execution. This also has been fixed.

Rules evaluation period

Schedule date format

Schedule time format

Management request address

Server administration password

Logging level

Compression level

Rather than showing their proper value, they always showed their default value. Even after changing a property, its new value would appear in the server's property dialog, but the new value would not be used by the MobiLink server. The plugin was setting the in-memory property set rather than the one in the persistent store. This has been fixed.

For example, if the following query was in a procedure, the server may have crashed executing the procedure:

select sum( R.z + ( select sum(DT.x + DT.y)

from (select T.x, R.y <<<<<-----R.x is an outerrefere

from T ) as DT(x, y) ))

from R

- dbmlsync (including QAnywhere agent synchronizations)

- UltraLite clients

- the MobiLink Monitor

- dblsn

- mlfiletransfer

The failure required that the last thing MobiLink server did on a connection, just before it was closed, was a read. In synchronization terms, this means that synchronizations with download ACKs were potentially affected, but synchronizations _without_ download ACKs were not. Persistent connections should not be affected by this problem. This has been fixed.

For example, the query:

select N'A', 'A'

should return a nchar(1) for column 1 and a char(1) for column 2. Instead, the same domain was incorrectly being selected for both columns:

select * from sa_describe_query( 'select ''A'', N''A'' ' )

This has been fixed.

The data on line n could not be imported.

[UltraLite database] Expression has unsupported data type.

SQLCODE=-624, ODBC 3 State="HY003"

UltraLite was attempting to bind two types that were not supported, SQL_FLOAT and SQL_GUID, but only when those columns were NULL. When those columns were not NULL, the rows would have been loaded as usual. Fixed by adding support for these types.

For example, the following query:

SELECT * FROM department D WHERE D.dept_name = N'R & D'

could have incorrectly been executed as:

SELECT * FROM department D WHERE D.dept_name = CAST( N'R & D' AS CHAR) AND CAST( D.dept_name AS NCHAR)= N'R & D'

This has been fixed. Such comparisons are now treated consistently, regardless of whether a sequential scan or an index is used, and regardless of whether the query bypassed optimization or not.

The above query is now correctly executed as:

SELECT * FROM department D WHERE D.dept_name = CAST( N'R & D' AS CHAR)

Note that NCHAR values may appear if there is a host variable that is bound at the client as SQL_C_WCHAR.

This fix further clarifies the rules of type inference when building expressions that have both CHAR and NCHAR columns. After this change, some expressions with a mix of CHAR and NCHAR types may return a different result type or a different answer than before. For each expression, SA tracks a flag coercible_collation that indicates whether the value can be coerced to CHAR when used in an expression containing a CHAR column. All constants, variables, and host variables are marked as coercible. All column references are marked as not coercible. A complex expression is coercible if all of its arguments are coercible. When combining arguments, the following rules are considered in order:

If there is any not-coercible NCHAR value, all CHAR values are converted to NCHAR

Else, if there is any not-coercible CHAR value, all NCHAR values are converted to CHAR

Else, if there is a mix of coercible CHAR and coercible NCHAR values, all CHAR values are converted to NCHAR

When NCHAR semantics are explicitly desired and a mix of CHAR/NCHAR types is present, use a cast on any CHAR column references in the operation. For example, the following comparison is done in the NCHAR domain:

SELECT * FROM department D where CAST( D.dept_name AS NCHAR) = N'R & D'

Since this change affects the results of queries, it may cause a query to return a different result than a server without the fix. If a materialized view was created with a server that does not contain the fix, stale results may be returned. If a materialized view contains NCHAR and CHAR values, the view columns may have different domains with a server containing this fix, and this may lead to a failure to match the materialized view (see QTS-454364). In this case, the materialized view must be recompiled with 'ALTER VIEW [ owner.]materialized-view-name RECOMPILE' and then refreshed with 'REFRESH MATERIALIZED VIEW [ owner.]materialized-view-name...before it can again be automatically matched (see also the section "Refreshing materialized views" in the SQL Anywhere� Server - SQL Usage book for information about how to refresh materialized views).

For example, the following sequence could have incorrectly lead to the error CANNOT_UPDATE_NULL_ROW:

create view ab

as select apk, ax, bpk, bfk, bx

from a left outer join b on bfk = apk;

update ab set ax='Z' where apk=2

This has been fixed.

select emp_profile..dependents..count(*) from emp_profile

This problem has now been fixed so that attempts to use an invalid dotted reference of the above form will result in an "invalid field reference" error.

Note, this problem only affected windowed operation, not console mode.

"No valid values found for the -w flag". The installer makes a call to the OS to get the size of the current window, which is then used to format the display of the install text. If this call returns an error, the window size is set to a default of 80x24. If however, the call does not return an error but returned a window size of 0x0, the installer would have tried to use this to format the text, leading to problem. This has been fixed by using the default 80x24 size for this case as well.

XML supports hexBinary and base64Binary representations for BINARY data types. A SQL Anywhere DISH service will now expose all SOAP service elements of type BINARY as base64Binary XML types. This applies to all three SOAP service formats: CONCRETE, DNET and XML. Prior to this change, DNET formatted soap services exposed BINARY data as hexBinary.

This fix resolves another potential problem: attempting to add a user with a null password. Calling the C++ method UltraLite_Connection::GrantConnectTo() with NULL as the second parameter could also have crashed the application. This has been resolved as well.

For example:

create procedure TNchar()

begin

select '\', N'\'

end

The TNChar procedure would have incorrectly returned a string with two backslash characters instead of one.

This has been fixed.

Note that hexadecimal escape sequences are interpreted in the CHAR character set, not the NCHAR character set. Use the UNISTR() function for unicode escapes.

NOTE: The interpretation of escape characters in NCHAR string literals has changed; if your database contains materialized views affected by this, the views should be refreshed.

Note, when deploying any of these executables, the .LIC file will need to be deployed as well

Note that these changes only apply to applications that use the C++ or embedded SQL interfaces and don't use the UltraLite engine. Applications that use the engine, or any of the components, did not have these problems since they always call ULEnableStrongEncryption internally.

Note that this problem does not affect non-TDS based connections.

The default dbmlsync command used in the automatically generated *_dbmlsync.bat file contains the "-qc" switch. This switch caused the window to automatically close upon completion of the synchronization, making it hard to see whether it was successful or not. This has been fixed so that it is no longer in the default command. It can be added if desired by adding "-qc" to the command line options in the Advanced Options for SQL Anywhere Remote Synchronization Client page of the Deploy Synchronization Model Wizard.

The automatically generated *_ulsync.bat had a "set PARMS=" line (intended for users to edit if they wanted), which should have been "set PARAMS", that meant that the %PARAMS% environment variable was not used with the ulsync command. The typo has been corrected.

For more information on the IN List algorithm, see the documentation:

SQL Anywhere� Server - SQL Usage

Query Optimization and Execution

Query execution algorithms

Miscellaneous algorithms

IN List

An error is now given if a filename is provided to one of the above statements and the filename is not a CHAR-based string. This can happen either if the N'' string literal prefix is used to denote an NCHAR string or if a variable is used and the variable is not CHAR-based.

1. A database server started in session 0 (ie as a Windows service or on the desktop of the primary login session) will be visible via shared memory to all sessions.

2. A database server will not start if there is already a server running by the same name in session 0 or in the current session. Note that the server _will_ start if there is a database server running with the same name in another session other than session 0.

3. When connecting via shared memory, clients will look for a server by the specified name in the current session first and then in session 0.

Note that starting a database server on the desktop of session 0 can prevent a server with the same name from starting as a service; however, this situation is rare because services are normally started when the system starts.

For example:

Select dateformat( �2007-02-15�, 慏DDdddddd, Mmmmmmmm� ) from dummy

This function would have incorrectly returned 搕hursday, february� instead of 揟hursday, February�. This has been corrected.

mlsrv10 ... -x https(...;fips=y)

Note that this is the full extent of the bug. The following will load the proper DLL/shared object:

mlsrv10 ... -x https( ...) -fips

mlsrv10 ... -x tls(...;fips=y)

mlsrv10 ... -x tls(...) -fips

Those requiring FIPS compliance for encryption are urged to always use the -fips option of mlsrv10 and/or on the server. They are also urged to remove the DLLs/shared objects that are not FIPS-compliant. Customers doing the latter would have seen the MobiLink server issue an error about being unable to load mlrsa_tls10.

A work-around for SA 10.0.0 is to copy the contents of the CD to a hard drive and run the install from there.

In order to support tmpfs filesystems, the server now automatically disables the use of Direct IO when the database file resides on a file system not supporting it. In such cases

an informational message will be sent to the server console and/or log file indicating that DirectIO has been disabled for that file.

Manager when displaying blob data from the UltraLite databases. The problem was seen when displaying the contents of a non-empty blob column. No data is returned, and DBISQL would have displayed the following Driver Manager error (SQLSTATE HY009):

[Microsoft][ODBC Driver Manager] Invalid argument value

This has now been fixed.

For example,

dblsn -z "c:\program files\tracker\"...

SQL Anywhere SOAP services with FORMAT 'XML' or FORMAT 'CONCRETE' no longer return NULL column values as empty XML elements. Empty XML elements are only returned for VARCHAR data types in order to preserve the empty string symantics. This change, along with Engineering case 455350, synchronizes the behaviour for all SOAP service formats.

For example, consider a database with the UTF8BIN NCHAR collation and 125LATIN1 CHAR collation:

create table TChar( x int primary key, c char(1), n nchar(1) );

insert into TChar values( 1, nchar(199), nchar(199) );

insert into TChar values( 2, nchar(231), nchar(231) );

commit

The following query should return one row:

select T1.x, T2.x

from TChar T1, TChar T2

where upper( T1.c ) = T2.n

and T1.x <> T2.x

but would have incorrectly returned no rows; this was equivalent to removing the upper() function.

This has been fixed, altough in cases where it is safe to do so, the functions UPPER(), LOWER(), UCASE() and LCASE() are still removed.

Note that client connections to a high-availability server were not a problem, they are able use TLS.

A SQL error occured when trying to check for Mobilink system setup in the consolidated

database. [Microsoft][SQL Native Client][SQL Server]The multi-part identifier "O.ID"

could not be found

This has been fixed.

For example:

select row_num, cast( cast( row_num as tinyint ) as varbit ) vb

from sa_rowgenerator(0,10)

where vb >= '00000100'

The above query would have returned the empty set; vb would have been converted to NUMERIC and compared to CAST('00000100' AS NUMERIC)==100. This has been fixed. Now, both arguments are converted to LONG VARBIT and compared as bit strings. The above query will now return the following:

row_num,vb

4,'00000100'

5,'00000101'

6,'00000110'

7,'00000111'

8,'00001000'

9,'00001001'

10,'00001010'

Further, when bit string values were displayed in plan text, they were displayed as a hexadecimal string that did not have a clear relationship to the bitstring value. For example, the bit string CAST( '00000100' AS VARBIT ) was previously displayed as 0x080000000100000004000000 in the plan text. Now, bit string values are displayed in the plan text as a string literal prefixed with B (for example, B'00001010'). The B prefix distinguishes bit strings in the plan text from character strings.

For example:

begin

declare @x long varchar;

declare @y long varchar;

set @x = 'abc';

set @y = @x;

set @y = @y || 'z';

end

It was possible for the variable @x to be incorrectly modified to be 'abcz'. In order for this problem to have occurred, the concatenation must have been of the form:

set @var = @var || 'string';

for some variable @var, and further, the variable @var must have shared the same string value as another variable. In cases where the problem may have occurred, it would occur intermittently depending on the current server state. It has now been fixed.

Rather than looking for fixed filenames "dbeng10.lic", "dbsrv10.lic", etc, the server now looks for a ".lic" file that has the same base filename as the executable that is being run. For example, if dbeng10.exe has been renamed to be "mydb.exe", the server will now look for the file "mydb.lic".

The QAnywhere Agent will not start if the client ID is more than 120 characters long. The maximum length for a QAnywhere client ID is now 120 characters. This is because the maximum length of dblsn user names is 128, and qaagent forms a dblsn user name as "ias_<client-id>_lsn".

11i v3 (version 11.31) as it required the deprecated library libcur_colr. This requirement for this library has been removed.

It is generally recommended that the database charset be used for unloading as it avoids both the overhead of unnecessary charset conversion and potentially lossy conversions.

For example, the query below had always, before this fix, the plan T<c1> which may be inefficient for some instances of the table T.

create table T (c1 int primary key );

.....

select count(*) from T where T.c1 IN (1,2,3,4,5,6,7,8,9);

Note that the problem did not happen if the application connected to a real database, instead of the utility database.

Column 'first_pkcol' in table 'sa_unloaded_table' cannot be NULL

This has been fixed. A workaround is to set the option to 'on' prior to unloading the database.

... mod_iaredirect: Error creating stream, code: 224, system code: 0

... mod_iaredirect: failed to initialize stream..

Note: The above error messages are an example of what would have appeared in an Apache redirector log. Similar messages would have appeared in the log for iPlanet or M-Business Anywhere.

This has been fixed.

For more details, see:

http://www.ianywhere.com/developer/technotes/sa_daylight_savings_time_change.html

As a workaround, connect to the primary server, insert a row into an existing table, and roll back the transaction. Then copy the transaction log to the mirror server. The mirror server should now start.

For more details, see:

http://www.ianywhere.com/developer/technotes/sa_daylight_savings_time_change.html