Top 10 Cool New Features In SQL Anywhere 11

by

Breck Carter

Overview

SQL
Anywhere 11 has a surprisingly large number of new features, especially when
you consider the huge number of features introduced only two years ago in SQL
Anywhere 10. This article presents a Top 10 list of new features in SQL
Anywhere 11 that developers are likely to find most interesting. Some of the
features that didn’t make the Top 10 list are described as well, including
features that might be important to management but aren’t necessarily cool as
far as developers are concerned. Also included is a short list of features that
are more weird than wonderful.

Once again,
it’s time for the SQL Anywhere Oscars, when folks tune in to see which of their
favorite features got picked, and which ones got left out. And to see some
weird stuff… it’s all here, the Top 10 list of cool new features in SQL
Anywhere 11.

The Trouble With 11 Is 10

When SQL
Anywhere 10 was released two years ago, I wrote that the number "10"
was a big problem in itself: it was difficult to choose which ten out of the
many dozens of cool new features to talk about.

With SQL
Anywhere 11, the number "10" is still giving me grief, as in
"I’m still trying to learn about version 10, and here comes 11!" I
agree with this request posted on the version 11 beta forum: "Add more
hours to my day" to spend figuring out all the new stuff.

Here’s an
example of how fast SQL Anywhere is changing: it’s only been a short while
since I first used OPENXML to parse and load giant XML files directly into SQL
Anywhere tables, and that feature’s been around since 9.0.1. As soon as I
posted a "how to use OPENXML" article in April, it became the Number
One most popular page on my SQL Anywhere blog, and it’s stayed that way ever since.

Here’s
another example: using HTTP sessions to maintain state with SQL Anywhere’s built-in
web server was introduced in version 10, and it was just the other week that I
got around to working with it. No, it wasn’t on my Top 10 list for version 10, big
mistake… it’s really cool!

Having
said all that, version 11 is not quite as ambitious as 10, so picking the Top
10 was not so hard. But first, it’s time for some people to take offense when
they read about some of their favorite features that got left out.

Time to Take Offense

Here are
a few of the new features in SQL Anywhere 11 that didn’t make the Top 10 list.

Support
for the .NET Entity Framework and LINQ is a big, important new feature, yes,
but not cool, at least not to me. If you are going to use a relational
database, you should learn and use the language of relational databases…
which is SQL, not some obfuscating wrapper like LINQ. Don’t try to tell me that
this is a reasonable way to express a simple WHERE clause: Customer customer =
this._db.Customers.Where( "it.ID = @ID", new ObjectParameter(
"ID", id ) ).First();

Full text
searching and regular expressions are two new features that you can use instead
of LIKE predicates. The first one’s very cool (full text search) and it’s in the
Top 10 list, but not regular expressions. It’s going to be a very long time
before I learn what "positive lookbehind zero-width assertion" means,
or code something like ‘(?<=new\\s)york’ in an otherwise readable SELECT. If you already know about regular expressions,
you might be happy, but maybe not if you grew up worrying about
maintainability.

Here’s
one I hope I’ll regret leaving out: DCX, which stands for
"DocCommentXchange", which is the windy name for hypertext links from
the Help to a website that lets you read other people’s comments on individual
topics… and enter your own. The DCX interface looks very much like the Help,
so when you click on a link you’ll think you’re looking at the same thing (see
Figure 1): same topics, laid out the same way, same Contents, Index and Search
tabs. What’s new is the comments area in each topic, which shows you what other
people have said and which lets you login and add your own. Also new is the
Recent Comments tab on the left. Today, the dcx.sybase.com website runs on a
SQL Anywhere 10 database. Soon, it will be running on SQL Anywhere 11, using
the built-in HTTP server instead of Apache, and using PHP stored procedures
instead of a PHP client server application… maybe it will even use full text
search instead of LIKE. I hope they’ll get all that done, and that’s when I’ll
start wishing I’d included DCX in the Top 10.

Here’s
another tough choice: Background synchronization support in UltraLite. That
means when you’re deploying a sackload of code to a handheld device, you don’t
have to jump through hoops to design an application that keeps your users from
messing things up by continuing to work while a MobiLink synchronization is in
progress. You can now start synchronizations on a second thread and UltraLite
will only upload changes that have already been committed; any changes made
after that will wait until the next sync. The reason this is cool? It’s the
Number One reason for moving to SQL Anywhere 11, for my very first client who’s
making the move. Not enough to make the Top 10 list, but enough to get
mentioned here.

Here are
a couple of very important under-the-hood features:

• Index-only
  retrieval, where SQL Anywhere won’t even look at the table if the data needed
  to satisfy a query is found in an index
• Multiple index
  scan where up to 4 different indexes on the same table may be used to satisfy a
  query instead of the current limit of one index per table.

Some
queries will run much faster because of these features, which is why they’re
mentioned here. They’re under the hood, that’s why they’re not in the Top 10
Cool list… it’s like when folks are shopping for a minivan, they want to see the
cupholders, not hear about the camshafts.

MySQL can
be used for the consolidated database in a MobiLink synchronization setup.
There, I said it, MobiLink now works with MySQL. I really don’t understand why
anyone uses MySQL, it’s an administrative nightmare of settings and options and
pitfalls and confusion, and it’s NOT free, not when you count support. But,
it’s popular. And now you can use SQL Anywhere in a MySQL shop to implement
self-administering remote databases that synchronize via MobiLink with your
legacy MySQL database. Speaking of legacy, MobiLink now also works with DB2 on
the mainframe. Not cool, unless it’s a way for you to get SQL Anywhere in the
door, then it’s very cool.

Top 10 Cool New Features

The order’s
not important… it’s hard enough picking ten, there’s no point in trying to
rank them.

1. UNLOAD to a Variable

The LOAD
and UNLOAD statements now work with variables as well as files. I don’t mean
just using a variable to hold a file specification, I mean loading and
unloading data stored in variables as well as files. You can UNLOAD all the
rows and columns of a table into a single string variable, and the reverse:
LOAD a table from a string:

BEGIN

   DECLARE s LONG VARCHAR;

   UNLOAD TABLE t1 TO s;

   LOAD TABLE t2 FROM s;

END;

Here are
some advantages of using variables instead of files:

• Improved
  concurrency because variables are local to connections whereas files are
  global.
• Improved
  flexibility because SQL Anywhere has many powerful functions for manipulating
  strings but not many functions for fiddling with files.
• Improved speed
  because there’s no file I/O involved… but don’t get carried away and try to
  use UNLOAD LOAD as a substitute for INSERT SELECT, it’s not that fast.

Yes, it
works with UNLOAD SELECT as well as UNLOAD TABLE, so you can write a query to
get complete control over what is stored in the variable.

This
feature is personally embarrassing to me. For years, I’ve been asking for it
and when I first got my hands on it I couldn’t figure out what to do with it!
The reason was that over the intervening years other features had became
available to solve my problems, the most important being the enhanced LIST
aggregate function. Using the LIST and STRING functions together with LIST’s
delimiter and ORDER BY clauses makes it possible to perform set-oriented string
manipulation. For example, you can join several tables and turn the data into a
formatted HTML web page with a single SELECT.

Look, I’m
not the sharpest knife in the drawer. Just because someone gives me a new
feature doesn’t mean that right away I’m going to know what it’s good for. That
was true with LIST, and it’s true with UNLOAD to a variable. However, once
someone shows me, woohoo! I’m off to the races! That’s what happened when Ivan
Bowman showed me how to combine this new feature with the next one in the list.

2. FROM OPENSTRING

The new
OPENSTRING clause lets you name a file or variable in the FROM clause of a
SELECT and have SQL Anywhere treat the data inside that file or variable as a
set of rows and columns. That’s a little bit like using a proxy table to treat
a file like a table via ODBC remote access middleware, but it’s way easier and
more flexible, not to mention that OPENSTRING lets you use variables as well as
files.

This
feature is also embarrassing to me. At first, I thought it was one of those
funky XML things, and the word "open" is really off-putting these
days with the way it’s used and over-used for everything. So, I skipped right
over OPENSTRING thinking "yeah, right, any day now we’re going to start
saying OpenCorpse instead of roadkill."

Then Ivan
Bowman of Wikipedia "IvanAnywhere" fame changed my mind with this
example using OPENSTRING together with UNLOAD SELECT into a variable:

"One
neat use case I’ve wondered about is the following (I’m using SYSDOMAIN here
just for the sake of an example):

CREATE VARIABLE @var LONG VARCHAR;

UNLOAD

   SELECT *

     FROM SYSDOMAIN

INTO VARIABLE @var;

CREATE VIEW V AS

   SELECT *

     FROM OPENSTRING ( VALUE @var ) WITH

             ( domain_id   SMALLINT,

               domain_num  VARCHAR ( 128 ),

               type_id     SMALLINT,

               “precision” SMALLINT )

          AS T;

What the
above code does is copy a result set into a connection-level variable @var, and
then uses a view V to refer to that result set. You could create the value for
@var in a connect procedure.

This
could be a good way to manage local option settings or connection-local caches
of hot data (avoiding contention on hot rows). I’d want to be careful about how
that type of thing is used, but it is a really neat tool to have in your back
pocket. You could even create INSTEAD OF triggers on the view to make it look
more like a table.

The
OPENSTRING feature also seems like a perfect fit for some cases of dynamic IN
lists, where the IN list is specified as a string. It even allows using a
multi-column IN list, something that is not directly supported in SQL Anywhere.

OPENSTRING
may also have some interesting uses when combined with LIST. It could appear as
a lateral join, decoding a single column value into a result set. I’ve used
sa_splitlist() for a number of interesting solutions in the past, and
OPENSTRING covers all of those and more because it decodes rows, not just
columns, it is much faster, and it offers more parsing options for handling
more complex data.

Amen,
Ivan, on that last point… it’s hard to predict what folks will do with new
features. Sometimes, they take off, like the LIST function, sometimes not so
much, like the syntactic nightmare that is the ANSI standard recursive union. I’m
thinking OPENSTRING could be one of the popular ones, and UNLOAD into a
variable as well.

Ivan
mentioned another recent feature, one that didn’t make it on to any Top 10 list
because it was introduced without any fanfare in version 10.0.1: INSTEAD OF triggers
let you write triggers on tables and views that execute instead of the
triggering action. In other words, if an INSTEAD OF INSERT ON T trigger exists,
when an INSERT T statement executes it fires the trigger but it does not insert
the row. Any action you want as a result of the INSERT T has to be coded inside
the trigger.

The cool
part is it lets you write triggers on views, even views that are not themselves
updatable. Whether or not the view is updatable doesn’t matter because the
triggering INSERT, UPDATE or DELETE isn’t going to do an insert, update or
delete itself if a corresponding INSTEAD OF trigger exists, and you are free to
code whatever you want inside the trigger (except for a COMMIT, of course, that
restriction stands).

3. Immediate Materialized Views

Here’s an
example of a materialized view used to create a list of employees by
department:

CREATE MATERIALIZED VIEW DeptList AS

SELECT Departments.DepartmentID,

       Employees.EmployeeID,

       Departments.DepartmentName,

       Employees.GivenName,

       Employees.Surname,

       Employees.Phone

  FROM Departments

       INNER JOIN Employees

          ON Employees.DepartmentID = Departments.DepartmentID;

All
materialized views have to be initialized before they can be used:

REFRESH MATERIALIZED VIEW DeptList;

With an
ordinary (manual) materialized view, if you change the underlying table but don’t
do another REFRESH you’ll see old data:

UPDATE Employees SET DepartmentID = 400 WHERE EmployeeID = 1336;

SELECT *

  FROM DeptList

 ORDER BY DepartmentName,

       Surname,

       GivenName;

DepartmentID  EmployeeID  DepartmentName  GivenName  Surname  Phone

============  ==========  ==============  =========  =======  ==========

300           1336        Finance         Janet      Bigelow  6175551493

Starting
with SQL Anywhere 11, you can change the view so that any updates to the
underlying tables are immediately applied to the data stored in the
materialized view; here are the steps to do that:

TRUNCATE TABLE DeptList;

CREATE UNIQUE INDEX xid ON DeptList (

   DepartmentID,

   EmployeeID );

ALTER MATERIALIZED VIEW DeptList IMMEDIATE REFRESH;

REFRESH MATERIALIZED VIEW DeptList;

The
TRUNCATE TABLE is necessary if the manual view has already been populated with
data: the view has to be empty before changing it to immediate. The CREATE
UNIQUE INDEX is another requirement for immediate views: SQL Anywhere has to be
able to find the rows when it needs to update them. The ALTER changes the
view’s refresh mode to IMMEDIATE, and a one-time REFRESH is needed to initially
populate the view… yes, that’s a long list of steps, but you only have to do
them once.

Now, if
you update a base table and query the view, the new data shows up right away
without the need for another explicit REFRESH:

UPDATE Employees SET DepartmentID = 400 WHERE EmployeeID = 1336;

SELECT *

  FROM DeptList

 ORDER BY DepartmentName,

       Surname,

       GivenName;

DepartmentID  EmployeeID  DepartmentName  GivenName  Surname  Phone

============  ==========  ==============  =========  =======  ==========

400           1336        Marketing       Janet      Bigelow  6175551493

Here’s
the real reason materialized views are cool: you don’t have to know the view
exists to get the benefits. Even though the following query uses the base
tables, Figure 2 shows that SQL Anywhere used the DeptList view instead:

SELECT Departments.DepartmentName,

       Employees.GivenName,

       Employees.Surname,

       Employees.Phone

  FROM Departments

       INNER JOIN Employees

          ON Employees.DepartmentID = Departments.DepartmentID;

4. Backward Compatibility is Back

If you
have a database created with version 10 software, you can run it with SQL
Anywhere 11 without having to rebuild. That’s important with very large
databases: you can start testing with version 11 without committing to a
time-consuming no-going-backward rebuild.

When
version 10 was introduced, it came as a surprise that rebuilding was a
prerequisite for any database created with earlier versions. With version 9
software, folks were used to the fact that it could be used with older database
files, even ones created with version 5.5. Version 10 broke that compatibility
path, and it was done for a very good reason: to jettison a lot of legacy code
from the database engine, eliminating the maintenance effort that code
required, and freeing that time to be spent in areas that matter a lot more…
like cool new features.

With
version 11 a new compatibility path has
started: no rebuild is required when going from 10 to 11. Earlier than that,
say, if you’re moving from 9 to 11, you still have to rebuild.

Moreover,
here’s an important point: If performance is critical to you, it’s still a good
idea to rebuild as soon as you commit to moving from 10 to 11. The only way to
take advantage of improvements in the new database file format introduced with
version 11 is to do a full unload and reload.

5. Stored Procedures in Perl, PHP, C#, VB

With
Version 11 of SQL Anywhere you can now write stored procedures in nine
different languages: Perl, PHP, C# and Visual Basic have been added, and
support continues for Java, C, C++ and two flavors of SQL, ANSI/Watcom and Transact.
Actually, the number might be higher than 9; for example, a .Net 2.0 CLR
version of Fortran should work, as long as you understand that "should
work" is how speakers of Tech Support Language say "you might be the
first one to actually try it."

The
details of stored procedure support vary for different languages; for example,
VB can return result sets but Perl can only return LONG VARCHAR strings.

It is
also important to note that procedures written in any of the non-SQL languages
now run in executable environments that are completely separate from the
database server itself. That means if your procedure crashes, it won’t take the
database down with it. Once again, details vary; for example, you get one
external Perl environment set up for each connection, but with VB, there is
only one external environment started for each database. Figure 3 gives an
overview of the differences in support among the various languages.

     Figure 3: Differences In Language Support

              1 external

              environment  Procedure can

              per…       return a…

     =======  ===========  =============

     Perl     connection   LONG VARCHAR

     PHP      connection   LONG VARCHAR

     C#, VB   database     result set

     Java     database     result set

     C, C++   connection   result set

     SQL      n/a          result set

The
ability to safely call procedures written in different languages is a big deal,
almost worth its own entry in the Top 10 list. Previously, C and C++ procedures
ran inside the database server, with catastrophic results if your code went
crazy: not just one failed user connection, but also a crashed server affecting
everyone.

Another
big deal is the ability to use the current database connection inside a
procedure written in one of the non-SQL languages. In other words, you don’t
have to start a new connection to pass a SQL statement back to the database
that called the procedure. In previous versions, you couldn’t issue any SQL
statements at all from inside a C or C++ stored procedure, even if you were willing
to start a new connection, and now you have same-connection database access
with all the languages.

This is
cool stuff, I’ve got a sackload of code I’d like to embed in databases without
having to jump through hoops to pass data back and forth. Here’s one example:
embedding the API for a file comparison utility to work on documents stored as
blobs in the database, so SQL Anywhere’s built-in HTTP server can be used to
display the comparison results via web service calls from the browser.