Programming interfaces

New SQL Anywhere C API The SQL Anywhere C application programming interface (API) simplifies the creation of C and C++ wrapper drivers for several interpreted programming languages, including PHP, Perl, Python, and Ruby. The SQL Anywhere C API is layered on top of the DBLIB library and it was implemented with Embedded SQL.

Although it is not a replacement for DBLIB, the SQL Anywhere C API simplifies the creation of applications using C and C++. You do not need an advanced knowledge of embedded SQL to use the SQL Anywhere C API. See SQL Anywhere C API support.

New Python Database API (sqlanydb) The new Python Database API (sqlanydb) provides access to SQL Anywhere databases from scripts written in Python. The sqlanydb module implements, with extensions, the Python Database API specification, version 2.0. See Python support.

External environments SQL Anywhere now includes support for six external runtime environments: Java, Perl, PHP, CLR, embedded SQL, and ODBC. SQL Anywhere has had the ability to call compiled native functions written in C or C++ for some time. However, when these procedures are run by the server, the dynamic link library or shared object has always been loaded by the database server and the calls out to the native functions have always been made by the database server. The risk here is that if the native function causes a fault, then the database server will crash. Running compiled native functions outside the database server, in an external environment, eliminates these risks to the server. See SQL Anywhere external environment support.

A database upgrade is required to take advantage of this new feature. See SQL Anywhere Server upgrades.

PHP external environment support SQL Anywhere 11.0.0 includes a variety of pre-built binaries for various PHP versions including 5.1.1 through 5.1.6 and 5.2.0 through 5.2.6. If you have any one of these versions already installed on your server computer, then you should use the SQL Anywhere pre-built binaries instead of building the PHP external environment. Note that, for Linux and Solaris, both 32-bit and 64-bit versions of these binaries are provided. For Windows and other systems, only 32-bit versions are provided.

If you have a different PHP version installed than the ones listed above, then you must build the software, or switch your PHP version to one that matches a SQL Anywhere prebuilt version. For instructions on building the SQL Anywhere PHP module, see SQL Anywhere PHP extension.

Perl external environment support It is very important that you update your version of the SQL Anywhere Perl DBD driver before you try to use the Perl external environment. If you do not update your Perl DBD driver, then server-side Perl will not work.

Also, unlike PHP, SQL Anywhere does not include pre-built binaries for various versions of Perl. Source code for the SQL Anywhere Perl DBD driver is located in %SQLANY11%\SDK\perl. For instructions on building the SQL Anywhere Perl DBD driver, see Perl DBI support.

Web server support for UTF-8 URLs Previously, the web server decoded percent-encoded (%encoded) data within the request URL (or application/x-www-form-urlencoded data within the body of the request) into the database character set. Now, the contents of percent-encoded (%encoded) data is tested for UTF-8 sequences and converted to the database character set on a maximal extent basis. Any encoded data that is not UTF-8 is decoded and treated as if it is already in the database character set.

Client HTTP applications should send percent-encoded (%encoded) UTF-8 data exclusively. Note that ASCII is represented in UTF-8 as is. For example, a space is encoded as %20.

New client callback API A new client callback API has been added in support of the new client-side loading and unloading of data features. For embedded SQL, see DB_CALLBACK_VALIDATE_FILE_TRANSFER in db_register_a_callback function. For ODBC, see SA_REGISTER_VALIDATE_FILE_TRANSFER_CALLBACK in SQLSetConnectAttr extended connection attributes.

SQL_ATTR_CONNECTION_DEAD promptly detects dead connection Using ODBC's SQLGetConnectAttr call to get the SQL_ATTR_CONNECTION_DEAD attribute now gets the value SQL_CD_TRUE if the connection has been dropped even if no request has been made to the server since the connection was dropped. Determining if the connection has been dropped is done without making a request to the server, and the dropped connection is detected within a few seconds. The connection can be dropped for several reasons, for example, on an idle timeout. Before this change, SQL_ATTR_CONNECTION_DEAD only got the value SQL_CD_TRUE if the connection was disconnected or if ODBC driver made a request to the server (by calling SQLExecDirect for example) after the connection was dropped. See Getting connection attributes.

JDBC Driver now supports ResultSet.getBlob().getBinaryStream() The iAnywhere JDBC Driver currently supports the ResultSet.getBlob() method even though this method is optional in the JDBC specification. Support has been added for the optional ResultSet.getBlob().getBinaryStream() method. See JDBC 3.0/4.0 API support.

iAnywhere JDBC driver now accepts jdbc:ianywhere as URL header in addition to jdbc:odbc Previously, applications using the URL header jdbc:odbc could be reasonably certain that the JDBC Driver Manager would use the iAnywhere JDBC driver for making connections using this URL. However, recent versions of the Java VM have started to register the Sun JDBC-ODBC bridge as a JDBC driver, and since the Sun JDBC-ODBC bridge also accepts URLs beginning with jdbc:odbc, the chance of an application getting the Sun JDBC-ODBC bridge instead of the iAnywhere JDBC driver is quite high. To guarantee that the JDBC Driver Manager uses the iAnywhere JDBC driver instead of the Sun JDBC-ODBC bridge, the application should use the URL header jdbc:ianywhere instead. See Connections from a JDBC client application.

ODBC driver manager now accepts driver=iAnywhere Solutions 11 - Oracle The Unix ODBC driver manager now accepts driver=iAnywhere Solutions 11 - Oracle, and it loads the threaded iAnywhere ODBC driver for Oracle if the application is threaded. It does not load the driver if the application is non-threaded because the non-threaded iAnywhere ODBC driver for Oracle is not supported. See iAnywhere Solutions 12 - Oracle ODBC driver.

ODBC driver manager now accepts driver=UltraLite 11 The Unix ODBC driver manager already accepts driver=SQL Anywhere 10 and loads the SQL Anywhere ODBC driver (either threaded or non-threaded, depending on the application). The Unix ODBC driver manager now accepts driver=SQL Anywhere 11 and driver=UltraLite 11. For the UltraLite driver, the driver manager only loads the threaded version of the UltraLite ODBC driver because only the threaded version exists.

TDS connections enhancement The SQL Anywhere database server now allows TDS connections to the default database, even when the Open Client login server name does not match the name of the default database, if the connection string does not involve starting a database (that is, there is no DBF=...) and if the database server is only running one database.

Administration Tool launchers now easier to redeploy The launcher executables for the database tools (Sybase Central, DBISQL, DBConsole, ML Monitor) are now easier to redeploy. Registry entries and a set directory structure for the location of the JAR files are no longer required. Each executable needs to have an .ini file in the same directory (with the same name as the executable file) containing the details on how to load the tool. See Administration tool deployment.

SQL Anywhere .NET Data Provider now supports distributed transaction enlistment The .NET 2.0 framework introduced a new namespace System.Transactions, which contains classes for writing transactional applications. Client applications can create and participate in distributed transactions with one or multiple participants. Client applications can implicitly create transactions using the TransactionScope class. The connection object can detect the existence of an ambient transaction created by the TransactionScope and automatically enlist. Client applications can also create a CommittableTransaction and call the EnlistTransaction method to enlist.

This feature is supported by the SQL Anywhere .NET 2.0 Data Provider. Distributed transaction has significant performance overhead. It is recommended that you use database transactions for non-distributed transactions. See Transaction processing.

SQL Anywhere .NET Data Provider now supports named parameters The SQL Anywhere provider now supports named parameters in SACommand. If the user specifies all parameter names, the provider maps the parameter values when the command is executed. When you use named parameters, the order of parameters is not required to match the order of host variables.

SACommand cmd = new SACommand( "UPDATE MyTable SET name = :name WHERE id = :id", conn ); SAParameter p1 = new SAParameter( "id", SADbType.Integer ); p1.Direction = ParameterDirection.Input; p1.Value = 1; cmd.Parameters.Add( p1 ); SAParameter p2 = new SAParameter( "name", SADbType.Char, 40 ); p2.Direction = ParameterDirection.Input; p2.Value = "asdasd"; cmd.Parameters.Add( p2 ); cmd.ExecuteNonQuery();

Web services enhancements The following web services enhancements have been made in this release:

Extending web client service procedures of type HTTP:POST to allow a user-defined body The TYPE clause of the CREATE PROCEDURE and CREATE FUNCTION statements has been extended to allow the specification of a mime type. See CREATE FUNCTION statement [Web service] or CREATE PROCEDURE statement [Web service].
Extending web service client procedures to support the PUT, DELETE, and HEAD HTTP methods Web service client procedures and functions now support the PUT, DELETE and HEAD HTTP methods. The TYPE clause of the CREATE PROCEDURE and CREATE FUNCTION statements has been extended to support these methods. Similar to the POST method, PUT requires a content-type extension within the type clause and only a single (non-substitution) parameter is permitted. See CREATE SERVICE statement [HTTP web service], CREATE SERVICE statement [SOAP web service], CREATE FUNCTION statement [Web service], and CREATE PROCEDURE statement [Web service].
sa_http_php_page and sa_http_php_page_interpreted system procedures The new web service system procedures sa_http_php_page and sa_http_php_page_interpreted return the result of passing a PHP script through a PHP interpreter. See sa_http_php_page system procedure and sa_http_php_page_interpreted system procedure.
HTTP_BODY system function A new web service function has been added. The HTTP_BODY function returns the body of the HTTP request in binary form. See HTTP_BODY function [Web service].
WSDLC support for generating web service client SOAP procedures In addition to generating QAnywhere client-side SOAP interfaces for C# and JAVA, WSDLC now supports the generation of SQL SOAP (web service) client procedures for SQL Anywhere. WSDLC reads a WSDL1.1 compliant URL or file and generates procedures (or functions) with appropriate parameters and clauses that map to respective SOAP operations listed within the WSDL. The generated SQL statements are written to a SQL file. See iAnywhere WSDL compiler utility (wsdlc).
HTTP SOAP services defined with a FORMAT clause may be further qualified with EXPLICIT OFF or ON When creating an HTTP SOAP service, the default for the FORMAT clause is EXPLICIT ON. This means that the WSDL generated by a DISH service specifies explicit names and data types for each column returned within a result set. This allows SOAP client toolkits to automatically generate client-side objects and interfaces that represent the result set providing native access to the column values. Before this feature, column values could only be accessed as abstract XML data elements. That behavior can still be achieved by specifying EXPLICIT OFF.
For more information about how to define an EXPLICIT response object or the generic SimpleDataset, see CREATE SERVICE statement [SOAP web service] and Tutorial: Using JAX-WS to access a SOAP/DISH web service.
Support for JSON web services SQL Anywhere now supports web services that return JSON-formatted responses. See CREATE SERVICE statement [HTTP web service].

Logging web service clients The database server now supports logging web service client connections to an output file. You can specify the -zoc server option or use the WebClientLogFile and WebClientLogging properties with the sa_server_option system procedure to control logging and specify the location of the web service client log file. You can also disable the use of this feature with the -sf server option. See: