Top 10 Cool New Features In SQL Anywhere 11
November 3, 2008
by Breck Carter
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:
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:
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.
I'm curious to see what uses these new features will find in the real world."
-- Ivan T. Bowman, Distinguished Engineer, Sybase iAnywhere
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.