New Features Since GA: SQL Anywhere 16

Since the SQL Anywhere 16.0 "General Availability" build 1324 was released, there have been a number of EBFs that contained new features as well as fixes. Chances are, this trend will continue, and there won't be any interim "16.1" or "16.0.1" releases before Version 17 is released (which won't be happening any time soon).

That means if you want to know What's New in SQL Anywhere 16 since it first came out, you have to read the "read me" file for the latest EBF... or read this excerpt, where some the more interesting entries have been highlighted)...

1444 The new TIMESTAMPADD and TIMESTAMPDIFF functions have been added to ODBC and JDBC {escape sequence} support.
1451 The MobiLink plug-in test window records changes in SQL files before applying them to the databases.
1453 The new MobiLink plug-in "Duplicate" item creates a copy of the synchronization model in the same project.
1453 The Relay Server Record affinity flag now allows the value 'x' to expire the affinity cookie for troubleshooting.
1484 The MobiLink server now supports consolidated databases running on Sybase IQ 16.0 servers.
1537 The Sybase Central Plug-in has a new "Show Inherited" to show inherited object provileges.
1537 CREATE INDEX for local temporary tables is now allowed on read-only databases.
1614 Support files for new versions of the PHP interpreter will be available as separate downloads in the future.
1648 The OData Producer now respects Content-Encoding and Accept-Encoding HTTP request headers
1665 The dbo.sp_list_directory() stored procedure returns 5 additional columns.
1670 Strong encryption now achieved using OpenSSL instead of Certicom.
1673 The geospatial method ST_BUFFER is now supported for all geometry types.
1674 The new BackupInProgress database property returns 'on' when a backup is running and 'off' otherwise.
1674 The new TimeWithoutClientConnection database property returns the time since a connection existed.
1675 The new READ_SERVER_FILE() system function reads a file on the server.
1688 Error detection has been improved for additional FROM clauses in Syntax 2 of the DELETE and UPDATE statements.
1691 The Oracle JRE previously shipped with SQL Anywhere 16 has now been replaced by the SAP JRE.
1691 The minimum supported CPU architecture for UltraLiteJ for Android is now ARMv7.

  MobiLink - Java Plugin for Sybase Central

    ================(Build #1453  - Engineering Case #733180)================

 In the MobiLink plug-in, the popup menu for a synchronization model now contains 
 a new item, “Duplicate”. This item creates a copy of the synchronization 
 model in the same project. The name the user provides is used for the name 
 of the copy, as well as the script version and publication name values of 
 the new synchronization model. This feature is useful, when there is a working 
 synchronization system and a copy of it is required as a starting place for 
 making the next version of the system.

    ================(Build #1451  - Engineering Case #733174)================

 When the test window in the MobiLink plug-in is opened, it first deploys 
 the synchronization model to the consolidated database and to a newly created 
 remote database.  In the past, changes were made directly to the databases 
 to prepare for synchronization.  This behavior is now changed so that SQL 
 files are generated containing the changes to be made.  The SQL files are 
 then automatically applied to the databases.  This is consistent with the 
 way deployment is handled when the deployment wizard is used. 
 
 This should result in no user visible change in behavior, but it will ensure 
 that going forward behavior seen when testing a synchronization model in 
 the test window is consistent with that seen when the model is actually deployed 
 using the deployment wizard.

  MobiLink - Relay Server

    ================(Build #1453  - Engineering Case #732958)================

 The existing affinity flag in the Relay Server Record has been extended to 
 carry a value of ‘x’ when the Relay Server told the client to expire the 
 affinity cookie. This can be useful for troubleshooting.

  MobiLink - iAS Branded ODBC Drivers

    ================(Build #1484  - Engineering Case #735343)================

 The MobiLink server now supports consolidated databases running on Sybase 
 IQ 16.0 servers.  For the recommended ODBC drivers for Windows and Linux, 
 please visit the following link: 
   http://www.sybase.com/detail?id=1011880 
 
 The Row Level Versioning (RLV) feature introduced in Sybase IQ 16.0 has 
 removed the “single-writer” limitation.  Therefore the IQ 16.0 server now 
 allows multiple connections modifying a RLV enabled table concurrently. Based 
 on testing, the upload would be ten times faster for synchronizations with 
 RLV enabled tables than with RLV disabled tables.  Therefore, in order to 
 get better upload performance, all sync tables are recommended to be RLV 
 enabled.  However, if there is any table that cannot be RLV enabled, for 
 instance a sync table that contains BLOBs and/or foreign keys, the upload 
 phase must be serialized.  This requirement can be achieved, if the begin_upload 
 connection script is written to include or to use the following SQL statement:
   LOCK TABLE table_name IN WRITE MODE WAIT time_string
 
 where table_name is the name of a table that is defined on the IQ store 
 and the time_string gives the maximum time period to lock the table.  The 
 table can be as simple as the one defined as:
   create table coordinate_upload ( c1 int )
 
 It is not required to have any data. If any of the other MobiLink server 
 transactions is required to modify any IQ tables, all of these transactions 
 must be serialized as well.  The same logic mentioned above can be used.  
 This technique is considered more efficient than retries on each of the transactions 
 by the MobiLink server.

  SQL Anywhere - ODBC Client Library

    ================(Build #1444  - Engineering Case #731978)================

 ODBC (and JDBC) escape sequence support has been enhanced to include the 
 following functions.
 
 {fn TIMESTAMPADD(<interval>, <integer-expr>, <timestamp-expr>)}
 
 Returns the timestamp calculated by adding <integer-expr> intervals 
 of type <interval> to <timestamp-expr>. Valid values of <interval> 
 are shown below.
 
 {fn TIMESTAMPDIFF(<interval>, <timestamp-expr1>, <timestamp-expr2>)}
 
 Returns the integer number of intervals of type <interval> by which 
 <timestamp-expr2> is greater than <timestamp-expr1>. Valid values 
 of <interval> are shown below.
 
 These escape functions are mapped directly to the SQL Anywhere DATEADD/DATEDIFF 
 functions.
 
 The <interval> type can be one of the following:
 <interval>                 SQL Anywhere DATEADD/DATEDIFF date-part 
 mapping
 =========================  ==========================================
 SQL_TSI_YEAR               YEAR
 SQL_TSI_QUARTER            QUARTER
 SQL_TSI_MONTH              MONTH
 SQL_TSI_WEEK               WEEK
 SQL_TSI_DAY                DAY
 SQL_TSI_HOUR               HOUR
 SQL_TSI_MINUTE             MINUTE
 SQL_TSI_SECOND             SECOND
 SQL_TSI_FRAC_SECOND        MICROSECOND
 
 Examples:
 // Number of days in February, 2013
 SELECT {fn TIMESTAMPDIFF(SQL_TSI_DAY, '2013-02-01T00:00:00', '2013-03-01T00:00:00'  
 )}
 28
 // Timestamp for 28 days after February 1, 2013
 SELECT {fn TIMESTAMPADD(SQL_TSI_DAY, 28, '2013-02-01T00:00:00' )}
 2013-03-01 00:00:00.000000

  SQL Anywhere - OData Server

    ================(Build #1648  - Engineering Case #746461)================

 The OData Producer now respects Content-Encoding and Accept-Encoding HTTP 
 request headers as specified by the HTTP 1.1 spec:
  http://www.w3.org/Protocols/rfc2616/rfc2616-sec14.html
 
 The Content-Encoding header is used by clients to indicate the encoding 
 of the request body. The Accept-Encoding header is used by clients to indicate 
 the preferred encoding of the response body.

  SQL Anywhere - Other

    ================(Build #1691  - Engineering Case #749465)================

 Previously, an Oracle JRE was shipped with the software for use by clients. 
 Now, the SAP JRE is shipped instead.
 
 Upgrading overwrites the JRE directory (%SQLANY16%\binXX\jre170) and its 
 subdirectories. If you are using certificates, then your certificate store 
 (%SQLANY16%\binXX\jre170\lib\security\cacerts) is overwritten, including 
 your certificates. Similarly, fonts you added to the %SQLANY16%\binXX\jre170\lib\fonts\fallback 
 directory to help display characters in the administration tools may be lost. 
 To minimize upgrading steps with regards to the JRE change, create a backup 
 copy of the JRE directory and all of its subdirectories before you upgrade 
 so that you can refer to or restore files (such as cacerts) from the backup, 
 as needed. To restore settings, use the java_vm_options option (SQL Anywhere), 
 and/or the -sl java option (MobiLink) to optimize your Java VM startup settings.

    ================(Build #1670  - Engineering Case #749256)================

 Strong encryption now achieved using OpenSSL
 --------------------------------------------
 
 Prior to this change, SQL Anywhere included a Certicom encryption module 
 that provided strong encryption used throughout the software. Now, SQL Anywhere 
 includes an OpenSSL encryption module for the strong encryption. The Certicom 
 encryption module has been removed.
 
 Read the following descriptions to determine how you may be impacted by 
 this change.
 
 FIPS encryption now requires the private key of an identity file to be encrypted 
 using AES 
 
 -  OpenSSL FIPS supports AES encryption for the private key of an identity 
 file. New servers using the OpenSSL FIPS encryption module will not start 
 when using an identity file that has its private key encrypted with 3DES. 
 You must re-encrypt the identity file using AES. To do this, run a command 
 similar to the following using an upgraded viewcert utility:
 
 viewcert -p -o new-file-name -op new-password -ip old-password old-file-name
 
 The new and old passwords can be the same.
 
 -  The sample server identity file (rsaserver.id) and client identity file 
 (rsaclient.id) have been modified so that the private keys are encrypted 
 using AES rather than 3DES.
 
 - Versions of the server that use the Certicom encryption module will not 
 start when using an identity file that has its private key encrypted using 
 AES. Trusted root certificate files specified using trusted_certificates 
 do not need to be modified.
 
 Self-signed certificates must now have the Certificate Signing attribute 
 set
  
 Self-signed certificates must now have the Certificate Signing attribute 
 set when using the identity encryption option (for example, the -x mlsrvXX 
 and -xs dbsrvXX options). To determine if a certificate has the Certificate 
 Signing attribute set, use the viewcert utility and look for the Certificate 
 Signing attribute in the Key Usage portion of the output. If your self-signed 
 certificates do not have the Certificate Signing attribute set, then you 
 must regenerate the certificates.
 
 Create Certificate utility (createcert) now uses AES encryption instead 
 of 3DES 
 
 The Create Certificate utility (createcert) now uses AES rather than 3DES 
 encryption for encrypting the private key in the server identity file.
 
 A new option, -3des, has been added to the Create Certificate utility. Use 
 this option when you want to create a 3DES-encrypted server identity file 
 that can be used by both new and old servers. Note that new servers running 
 in FIPS mode cannot start using 3DES-encrypted certificates; however, if 
 you are not running in FIPS mode, then you can use 3DES-encrypted certificates.
 
 View Certificate utility (viewcert) now uses AES encryption instead of 3DES 
 
 The View Certificate utility (viewcert) now uses AES rather than 3DES encryption 
 when you specify the -p option to PEM-encode the output and when you specify 
 the -ip and -op options to set the password.
 
 A new option, -3des, has been added to the View Certificate utility to allow 
 you encrypt output and passwords using 3DES instead of AES.
 
 Database server now loads the FIPS driver file, dbfipsXX.dll, at startup
  
 Previously, the 32-bit Windows database server loaded the FIPS driver file, 
 dbfipsXX.dll, only when needed. Now, the 32-bit Windows database server always 
 attempts to load dbfipsXX.dll at startup, and keeps it loaded for the life 
 of the server. If loading dbfipsXX.dll fails, then an error is returned only 
 when an attempt is made to use FIPS encryption.
 
 Deploying FIPS 
 
 If you are deploying FIPS encryption, then there are new shared libraries 
 to deploy; these files are included in your software. The former files, sbgse2.dll 
 and libsbgse2.so, are no longer installed by the software. The new files 
 to deploy are:
  - Windows 64-bit: libeay32.dll, ssleay32.dll, and msvcr100.dll
  - Windows 32-bit: libeay32.dll, ssleay32.dll, and msvcr90.dll
  - Linux: libcrypto.so and libssl.so
 
 Note: On Windows, although 32-bit and 64-bit FIPS-certified OpenSSL libraries 
 for encryption are provided, you must use the 64-bit libraries on a 64-bit 
 system.
  
 MobiLink-related changes and information 
 
 Connecting to a MobiLink server using client-side certificates now requires 
 the Digital Signature certificate attribute to be set 
 TLS/SSL connections to a MobiLink server using client-side certificates 
 now require the client-side certificate to have the Digital Signature attribute 
 set. If the attribute is not set, then the connection will fail.
 To determine if a certificate has the Digital Signature attribute set, use 
 the View Certificate utility (viewcert) and look for the Digital Signature 
 attribute in the Key Usage portion of the output. If your client-side certificates 
 do not have the Digital Signature attribute set, then you must regenerate 
 the certificates.
 
 FIPS-based end-to-end encryption now requires the private key to be encrypted 
 using AES 
 If the private key file provided to a MobiLink server by the e2ee_private_key 
 file option of the –x command-line option is encoded using 3DES and you are 
 running in FIPS mode, then the private key file needs to be regenerated with 
 the private key encrypted using AES.
 
 How to update a MobiLink deployment that uses non-FIPS TLS/SSL (includes 
 HTTPS) and client-side certificates 
 1. If your client-side identity certificates do not have the Digital Signature 
 attribute set and the client connects directly to the MobiLink server, then 
 you must regenerate and deploy client-side certificates with the Digital 
 Signature attribute set. 
 2. Update the server-side binaries. 
 3. Update the client-side binaries. 
 
 How to update a MobiLink deployment that uses FIPS, TLS/SSL (includes HTTPS) 
 and client-side certificates 
 These steps update the client identity certificates twice if the Digital 
 Signature attribute is missing from client-side identity certificates. This 
 procedure can make the update less disruptive because synchronizations can 
 continue without having to coordinate the client-side and server-side updates 
 to occur at the same time. 
 1. If your current client-side identity certificates do not have the Digital 
 Signature attribute set and the client connects directly to the MobiLink 
 server, then you must regenerate and deploy client-side certificates with 
 the Digital Signature attribute set. 
 2. Update the server-side binaries (remembering to include the new FIPS 
 driver files) and deploy server identity certificates with AES-encrypted 
 private keys. 
 3. Update the client-side binaries (remembering to include the new FIPS 
 driver files) and deploy client identity certificates with AES-encrypted 
 private keys.
  
 How to update a MobiLink deployment that uses FIPS and end-to-end encryption 
 1. Regenerate the primary key file referenced by the e2ee_private_key encryption 
 option. 
 2. Shut down the MobiLink server. 
 3. Update the MobiLink server binaries, remembering to include the new required 
 FIPS driver files. 
 4. Change the e2ee_private_key option to point to the new private key file 
 (or replace the old file), updating the e2ee_private_key_password, if required. 
 5. Restart the MobiLink server. 

  SQL Anywhere - Server

    ================(Build #1688  - Engineering Case #747805)================

 For Syntax 2 of the DELETE statement and Syntax 2 of the UPDATE statement 
 the error detection behaviour of the server has been improved. These two 
 syntax forms allow an additional FROM clause that may contain the table-name 
 of the updated or deleted table, for example:
 
             DELETE
             FROM  [owner.]table_1 [ [ AS ] correlation-name ]
             FROM  [owner.]table_1 [ [ AS ] correlation-name ] ...
             WHERE  ...
 and
             UPDATE  [owner.]table_1 [ [ AS ] correlation-name ]
             SET  columns_1 = ...
             FROM     [owner.]table_1 [ [ AS ] correlation-name ] ...
             WHERE ...
             
 If the DELETE or UPDATE clause and the additional FROM clause have a table 
 reference that contains the same table name, in the above example "table_1", 
 then the server can only decide whether both are identical table references 
 if one of the following conditions is true:
     - both table references are not qualified by specifying a user ID
     - both table references are qualified by specifying a user ID
     - both table references are specified with a correlation name
 
 In cases where the server cannot decide whether the above table references 
 are identical or not it will now return an SQL error to prevent the user 
 from unintended semantics like deleting and updating to many rows.

    ================(Build #1675  - Engineering Case #747798)================

 A new system function has been added, READ_SERVER_FILE(). This function reads 
 data from a specified file on the server and returns the full or partial 
 contents of the file as a LONG BINARY value.
 
 Syntax:
 READ_SERVER_FILE( filename ) [, start [ , length] ] 
 
 Parameters:
 - filename  LONG VARCHAR value indicating the path and name of the file 
             on the server. 
   
 - start     The start position of the file to read, in bytes. The first byte in 
        the file is at position 1. A negative starting position specifies 
        the number of bytes from the end of the file rather than from the 
        beginning. 
        * If start is not specified, a value of 0 is used. 
        * If start is zero and length is non-negative, a start value of 1 
   is used. 
        * If start is zero and length is negative, a start value of -1 is 
   used. 
   
 - length    The length of the file to read, in bytes. 
          * If length is not specified, the function reads from the starting 
   position to the end of the file. 
        * If length is positive, the function read ends length bytes to the 
   right of the starting position. 
        * If length is negative, the function returns at most length bytes 
   up to, and including, the starting position, from the left of the 
   starting position. 
 
 Returns:
 LONG BINARY
 
 Remarks:
 This function returns the full or partial (if start and/or length are specified) 
 contents of the named file as a LONG BINARY value. If the file does not 
 exist or cannot be read, NULL is returned. 
 
 filename is relative to the starting directory of the database server.
  
 The READ_SERVER_FILE function supports reading files larger than 2GB. However, 
 the returned content is limited to 2GB. If the returned content exceeds 
 this limit, a SQL error is returned. 
 
 If the data file is in a different character set, you can use the CSCONVERT 
 function to convert it. You can also use the CSCONVERT function to address 
 the character set conversion requirements you may have when using the 
 READ_SERVER_FILE server function. 
 
 If disk sandboxing is enabled, the file referenced in filename must in an 
 accessible location. 
 
 Privileges:
 When reading from a file on a client computer:
   * You must have the READ FILE system privilege.
   * You must have read permissions on the directory being read from.
 
 Standards: SQL/2008   Vendor extension. 
 
 Example:
 The following statement reads 20 bytes in a file, starting from byte 100 
 of the file.
   SELECT READ_SERVER_FILE( 'c:\\data.txt', 100, 20 )
 
 See also
   * xp_read_file system procedure
   * CSCONVERT function [String]
   * Disk sandboxing

    ================(Build #1674  - Engineering Case #747277)================

 A new database property, BackupInProgress, has been added. Querying the property 
 will return 'on' when there is a backup happening, and 'off' otherwise.

    ================(Build #1673  - Engineering Case #747205)================

 The geospatial method ST_BUFFER is now supported for all geometry types.  
 This method is compatible with the SQL/MM and OGC standards. ST_BUFFER returns 
 the ST_Geometry value that represents all points whose distance from any 
 point of an ST_Geometry value is less than or equal to a specified distance 
 in the given units.
 
 ST_GEOMETRY::ST_BUFFER( distance double, unit_name long varchar )
  - distance: The distance the buffer should be from the geometry value.  
 Must be greater than or equal to 0.
  - unit_name: The units in which the distance parameter should be interpreted. 
 Defaults to the unit of the spatial reference system.  The unit name must 
 match the UNIT_NAME column of a row in the ST_UNITS_OF_MEASURE view where 
 UNIT_TYPE is 'LINEAR'.
  - Returns the ST_Geometry value representing all points within the specified 
 distance of the original geometry.
 
 The ST_Buffer method generates a geometry that expands a geometry by the 
 specified distance. This method can be used, for example, to find all points 
 in geometry A that are within a specified distance of geometry B. The distance 
 parameter must be a positive value.  This method will return an error if 
 distance is negative. If the distance parameter is equal to 0, the original 
 geometry is returned. The ST_Buffer method is best used only when the actual 
 buffer geometry is required.  Determining whether two geometries are within 
 a specified distance of each other should be done using ST_WithinDistance 
 instead.

    ================(Build #1665  - Engineering Case #746935)================

 The dbo.sp_list_directory() stored procedure can be used to obtain information 
 about directories and files that are accessible to the SQL Anywhere Server. 
 Currently the sp_list_directory() procedure returns the following three columns:
 
 file_path long nvarchar the path of the server accessible file or directory
 file_type nvarchar(1) either F for file or D for directory
 file_size unsigned bigint the size of the file or NULL for directories
 
 In order to provide more information about the various files and directories, 
 dbo.sp_list_directory() has now been enhanced to return five additional columns. 
 These five additional columns are:
 
 owner   nvarchar(128)   the owner of the file or directory
 create_date_time* timestamp with time zone the date and time the file or 
 directory was created
 modified_date_time* timestamp with time zone the date and time the file 
 or directory was last modified
 access_date_time* timestamp with time zone the date and time the file or 
 directory was last accessed
 permissions  varchar(10)   the set of access permissions for the file or 
 directory
 
 All other aspects of dbo.sp_list_directory() – including the set of system 
 privileges and secure feature privileges – remain unchanged.
 
 A database either has to be upgraded or initialized in order for applications 
 to obtain this new information from dbo.sp_list_directory(). In addition, 
 if an upgraded or newly initialized database is subsequently moved to an 
 older version of the server, then the new columns will continue to be returned 
 but the values of the new columns will be NULL.

    ================(Build #1614  - Engineering Case #744027)================

 The SQL Anywhere PHP External Environment supports several versions of the 
 PHP interpreter. The SQL Anywhere install bundle includes a separate PHP 
 external environment dll or shared object for each supported version of PHP. 
 In addition, whenever support for a new version of the PHP interpreter is 
 added, the SQL Anywhere install bundle is updated to include the new PHP 
 external environment dll or shared object for the new version of the PHP 
 interpreter. Going forward, the SQL Anywhere install bundle will no longer 
 be updated with additional PHP external environment dlls or shared objects 
 when support for new versions of the PHP interpreter are added. Instead, 
 the new PHP external environment dlls and shared objects will now only be 
 available on the download site.

    ================(Build #1537  - Engineering Case #737497)================

 Previously, the CREATE INDEX statement for local temporary tables on read-only 
 nodes had been disallowed. This has been changed, and now local temporary 
 tables are the only tables where index creation is allowed on the read-only 
 databases.

    ================(Build #1473  - Engineering Case #734038)================

 The database property TimeWithoutClientConnection has been added.
 
 The description for this database property is:
 
 Returns the elapsed time in seconds since a CmdSeq or TDS client connection 
 to the database existed.  If there has not been a CmdSeq or TDS connection 
 since the database started then the time since the database started is returned.  
 If one or more CmdSeq or TDS connections are currently connected, 0 is returned.

  SQL Anywhere - Sybase Central Plug-in

    ================(Build #1537  - Engineering Case #739081)================

 Inherited object privileges can now be viewed for any table, view, procedure, 
 function, sequence generator, or dbspace via the “Privileges” tabs.
 
 Also, inherited object privileges can now be viewed for any user or role 
 via the “Table Privileges”, “View Privileges”, “Procedure Privileges”, “Sequence 
 Privileges”, and “Dbspace Privileges” tabs.
 
 In both cases, a new “Show Inherited” check box has been added to the tabs. 
 With the check box checked, the tabs show privileges that are inherited through 
 role inheritance, in addition to privileges that are granted explicitly.

  UltraLiteJ - Runtime

    ================(Build #1691  - Engineering Case #748717)================

 UltraLiteJ for Android no longer supports ARMv5 CPU architectures. The minimum 
 supported CPU architecture is now ARMv7.

---