Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 17 » SQL Anywhere - Changes and Upgrading

What's new in SQL Anywhere Server 17.0

SQL Anywhere 17.0 introduces many new features and changes across the software in the areas of programming interfaces, security, performance, SQL support, catalog changes, and administration tools.


Check the readme.txt file for additional information about the software, including feature information that was added after the documentation was completed.

The readme.txt files for all of the SQL Anywhere support packages and patch-level upgrades can be found at published on SAP site.

Top features
  • OData support (database upgrade required)
    • The SQL Anywhere database server can act as an OData server

      This functionality replaces the OData Server utility.

      OData Producer information is stored in the database and OData server information is specified by network protocol options. See OData server architecture.

      The following changes have been made to the database server to support this feature:

    • Database column names in OData Producer service operations

      The ServiceOperationsColumnNames option allows service operations to use the result set column names from the database when naming the properties of the ComplexType used in the ReturnType. The default behavior is to generate the names of properties for complex types returned by service operations. See How to configure the OData server.

    • CSRF tokens

      A new CSRFTokenTimeout option enables the feature and represents the number of seconds that CSRF tokens are valid for. The CSRF tokens feature protects OData Producers from cross-site request forgery attacks. See How to protect against cross-site request forgery attacks.

    • Repeatable requests

      The repeatable request feature allows clients to handle unreliable HTTP communications with an OData Producer. If an OData Producer fails to receive a response to a data modification request, then the client can repeat the request without risking database corruption. See How to set up repeatable requests.

    • Extended OSDL support for OData Producer service models

      New OData Service Definition Language (OSDL) syntax adds the ability to perform the following tasks:

      • Explicitly set the name of tables that are exposed through an OData Producer.

      • Explicitly include or exclude columns.

      • Define entity sets with generated keys.

      • Define associations between entities, including complex associations that use an underlying association table.

      • Define navigation properties.

      • Define concurrency token on entities for optimistic concurrency control.

      • Define service operations to be exposed through an OData Producer.

      Note This feature was first released in a version 16 Support Package.

      See How to create an OData Producer service model.

    • Multiple OData Producer support

      The OData server now supports multiple customizable OData Producers that allow you to establish multiple database connections. Use the new embedded HTTP server option, Producers, in your OData server configuration to create OData Producers.

      Note This feature was first released in a version 16 Support Package.
    • Optimistic Concurrency Control (ETags)

      OData Producers now support Optimistic Concurrency Control as defined by versions 2.0 of the OData Specification. The concurrencytoken clause of the ENTITY OSDL statement is used to generate ETags that identify the state of an entity instance at the time the instance is requested.

      The SQL used to generate an ETag uses SHA256 hash functions and can be complex given the types of properties (columns) and number of properties included in the concurrency token.See ENTITY statement.
      Note This feature was first released in a version 16 Support Package.
    • Service Operations and valid OData identifiers

      OData Producers can accommodate most database procedures and functions that have parameters starting with the @ character.

      Service operation names and parameter names, with the exception of @name parameters, must be valid OData identifiers.

    • Deep inserts

      OData Producers now supports deep insert requests. These are requests to insert an entity where some links are not references to existing entities and contain new inlined entities that are also to be inserted. Deep inserts may be nested 10 deep.

  • Support added for point-in-time recovery (PITR) (database upgrade required)

    Restore a database to a specified time stamp or to an offset in the transaction log. The following changes have been made to allow you to use this feature:

    • Enhanced -ad database option

      Specify more than one directory for the transaction log location when restoring a database. To do this, use a semicolon as a delimiter between the directory names. See -ad database option.

    • New -ruo database option

      Recover to a specified offset in the transaction log by using this option. See -ruo database option.

    • New -ru database option

      Recover to a specified time stamp by using this option. See -ru database option.

    • Several command line utilities now report more time-related information

      The Transaction Log utility (dblog), the Log Translation utility (dbtran), and the Information utility (dbinfo), now include more time-related information in their output.

    • Several new database properties The following database properties have been added but are for internal use only: CurrentTimelineID, CurrentTimelineSignature, PreviousTimelineID, and TimelineBranchOffset.
    • New -ft option for the Translation Log utility (dblog)

      The Translation Log utility (dblog) has a new option, the -ft option, for specifying a timeline when reloading SQL Remote consolidated databases. See Transaction Log utility (dblog).

    See also:

  • Improvements to the parameterization of statements (database upgrade required)

    The following enhancements allow your applications to resolve identifiers and parameters for SQL statements at execution time. These enhancements improve security by reducing the need to use EXECUTE IMMEDIATE statements and by increasing protection against SQL injection. These enhancements also provide more flexibility to your application when the name of an object, or the value for a statement option, is not known until execution time.

    • Support added for indirect identifiers

      Specify a variable for the user name and/or object name in a statement by using an enhancement to the identifier syntax ('[@variable-name]'). Supported objects you can indirectly refer to in this manner include owners, tables, columns, and the new mutexes, and semaphores. See Indirect identifiers.

    • Support added for table reference variables (new TABLE REF data type)

      Create a variable of type TABLE REF to reference a base table, temporary table, or view, and then refer to the table reference variable instead of specifying the object name directly in DML statements and as parameters in functions and procedures. See TABLE REF data type.

    • Support added for variables in some statements

      The following statements now support variables for parameters in their syntax. The variables are resolved just prior to the statement being executed.

      • ALTER DATABASE statement (database upgrade required)

      • CREATE MIRROR SERVER statement (database upgrade required)

      • ALTER MIRROR SERVER statement (database upgrade required)

      • CREATE SUBSCRIPTION statement (database upgrade required)

      • CREATE PROCEDURE statement [Web services] (database upgrade required)

      • ALTER PROCEDURE statement (database upgrade required)

      • CREATE FUNCTION statement [Web services] (database upgrade required)

      • ALTER FUNCTION statement (database upgrade required)

      • CREATE EVENT statement (database upgrade required)

      • ALTER EVENT statement (database upgrade required)

      • CREATE EXTERNLOGIN statement

      • DROP EXTERNLOGIN statement

      • CREATE SERVER statement

      • ALTER SERVER statement

      • CREATE SEMAPHORE statement

      • DROP SEMAPHORE statement

      • NOTIFY SEMAPHORE statement

      • WAITFOR SEMAPHORE statement

      • CREATE MUTEX statement

      • DROP MUTEX statement

      • LOCK MUTEX statement

      • RELEASE MUTEX statement

  • Support added for user locks: mutexes and semaphores (database upgrade required)

    Build user-defined mutexes and semaphores into your application logic to achieve locking behavior and control and communicate the availability of resources. See Mutexes and semaphores.

    Several changes have been made to support this feature:

  • Support added for running a database in a simulated time zone (database upgrade required)

    You can now create a simulated time zone and set your database to use this time zone, if you want your database to behave as though it is running on a time zone other than the system time zone of the database server. See Time zone management.

    Several enhancements have been made to support this feature:
  • Node.js

    A Node.js driver is available for download from the Node Packaged Modules web site as well as GitHub. The Node.js JavaScript API can be used to connect to SQL Anywhere databases, issue SQL queries, and obtain result sets.

  • Support for JavaScript external environment added

    JavaScript stored procedures and functions can be called from the database in the same manner as user-defined SQL stored procedures and functions

    A JavaScript driver is now included with SQL Anywhere. The XS JavaScript API can be used to connect to databases, issue SQL queries, and obtain result sets. See The JavaScript external environment.

  • JavaScript

    A JavaScript driver is now included with SQL Anywhere. The XS JavaScript API can be used to connect to databases, issue SQL queries, and obtain result sets.

  • Enhancements to the CREATE TABLE statement (database upgrade required)
    • Using the OR REPLACE clause

      The CREATE TABLE statement now supports the use of the OR REPLACE clause. See CREATE TABLE statement.

    • Creating new tables based on the schema of another table

      You can now create a table based directly on the definition of another table; clone a table with additional columns, constraints, and LIKE clauses; or create a table based on a SELECT statement. See CREATE TABLE statement.

  • Support added for the %TYPE and %ROWTYPE attributes when creating or declaring variables, casting or converting values, creating or altering tables, and creating procedures

    Use the %TYPE and %ROWTYPE attributes to define the data type(s) based on the data type of other objects. When creating schema objects such as columns, use the %TYPE attribute to set the data type of the object you are creating or altering, to the data type of a column in a table or view. Use the %ROWTYPE attribute to set the data types to the composite data type for a row in a table or view. When creating variables, you can also use the %TYPE and %ROWTYPE attributes to set the data type to the data type of temporary objects such as variables and cursors. See %TYPE and %ROWTYPE attributes.

    The following statements and functions are enhanced by this feature:

    • CREATE TABLE statement (%TYPE attribute only)
    • ALTER TABLE statement (%TYPE attribute only)
    • CREATE PROCEDURE statement
    • ALTER PROCEDURE statement
    • CREATE FUNCTION statement
    • ALTER FUNCTION statement
    • CREATE DOMAIN statement (%TYPE attribute only)
    • CREATE VARIABLE statement
    • DECLARE statement
    • CAST function (%TYPE attribute only)
    • CONVERT function (%TYPE attribute only)
  • Enhancements to support SAP HANA (database upgrade or rebuild required)

    The following enhancements for creating, altering, and dropping SAP HANA remote servers have been added:

    • Support for SAP HANA syntax for creating, altering, and dropping remote servers using CREATE REMOTE SOURCE, ALTER REMOTE SOURCE, and DROP REMOTE SOURCE statements, instead of CREATE SERVER, ALTER SERVER, and DROP SERVER statements.

    • Support for SAP HANA syntax for specifying underscores in server class names. For example, you can specify HANAODBC or HANA_ODBC.

    • Support SAP HANA syntax for creating proxy tables using the CREATE VIRTUAL TABLE. See CREATE TABLE statement.

    See CREATE SERVER statement, ALTER SERVER statement, and DROP SERVER statement.