I'd rather play golf

Each new SQL Anywhere release brings additional SQL functionality, some resulting from enhancements to the SQL standard, and some resulting from our own innovations to the product. In the forthcoming Version 12, for instance, SQL Anywhere will support the “distinct predicate” from the SQL/2008 standard that has the syntax X IS [ NOT ] DISTINCT [...]

[Read more →]

In November 1995 we launched the first version of SQL Anywhere (version 5.0) that offered support for Transact-SQL, in addition to SQL Anywhere’s existing dialect, which we continue to call Watcom SQL. (By the way, SQL Anywhere 5.0 was my first SQL Anywhere release, as I had joined the firm just a month earlier). There [...]

[Read more →]

In a previous article, I presented some examples of how one can SELECT rows from a dml-derived-table, a new SQL language feature of the SQL Anywhere 12 server, now in beta. In this post, I want to briefly describe some other ways in which one can exploit dml-derived-tables to simplify applications. The first thing to [...]

[Read more →]

In a previous post in May 2009 I expressed admiration for a SQL language feature in IBM’s DB2 product that permits one to use an update DML statement as a table expression in a query’s FROM clause. Here is a simple example to illustrate DB2′s syntax: 1 2 3 SELECT T_updated.* FROM NEW TABLE ( [...]

[Read more →]

After posting my recent article on the use of the RowGenerator system table, I received a welcome email from Jan-Eike Michels of IBM who, like me, sits on the DM32.2 committee for INCITS as the IBM representative for the SQL Standard: Hi Glenn, Just stumbled across your blog about the RowGenerator (http://iablog.sybase.com/paulley/2009/09/using-rowgenerator/) . I don’t [...]

[Read more →]

In SQL Anywhere, server, database, and connection-level options provide application control of various behaviours that can affect server operation and/or application-visible semantics. “Options” as implemented in SQL Anywhere are not part of the ISO SQL Standard, though the concept of support for “global variables” has been discussed within the SQL standards process. SQL Anywhere options [...]

[Read more →]

In the SQL:2008 standard, fixed-length character string values are blank padded. Blank padding occurs during storage of a fixed-length character string value when its original size is less than the declared width of the column. Blank padding also occurs when fixed-length character strings are compared using any string comparison predicate. For storage, here is the [...]

[Read more →]

The flagging of SQL statements is defined in the ANSI/ISO SQL:2008 standard as language features F812 (“basic” flagging) and F813 (“extended” flagging). Flagging is the notion of identifying non-conformance of specific SQL constructions with respect to the ANSI/ISO SQL standard. Here’s a simple example using the sample DEMO database with SQL Anywhere: 1 SELECT SQLFLAGGER( [...]

[Read more →]

The ANSI/ISO SQL Standard eliminated the BIT and BIT VARYING data types with the formal adoption of SQL:2003; the last standard to support them, including the BIT_LENGTH() function, was SQL:1999. The SQL:2003 standard retained the BOOLEAN type to hold the truth values TRUE and FALSE. The functionality provided by bit string arrays can be useful [...]

[Read more →]

SQL Anywhere version 11.0.0 introduced support for search conditions that include regular expression searching. There are two variants of regular expression search predicates that one can use, each with their own semantics: SIMILAR TO and REGEXP. SIMILAR TO The SIMILAR TO predicate is part of the 2008 ANSI/ISO SQL standard. However, the draft of the [...]

[Read more →]