SQL Anywhere: January 2011

Monday, January 31, 2011

Avoiding Quotes

Several SQL Anywhere procedures and functions take SQL statements as string arguments; for example,

sa_describe_query() returns information about all the columns in a result set,
rewrite() shows how the query optimizer is going to manipulate your query, and
graphical_plan() returns the execution plan used by the query engine.

If you have a long and complex SQL statement in your code, it can be a challenge to re-code it as a 'quoted string literal' in order to pass it to one of these functions... every embedded single quote must be doubled-up, and quotes that are already embedded inside string literals (and already doubled-up) must be quadrupled.

Here's an example:

<br>SELECT STRING ( <br>          '<HTML><HEAD><TITLE>',<br>          'All the demo database''s sales identifiers',<br>          '</TITLE></HEAD><TABLE>',<br>          LIST ( <br>             STRING ( <br>                '<TR><TD>',  SalesOrders.ID,<br>                '</TD><TD>', SalesOrderItems.LineID,<br>                '</TD><TD>', Products.ID,<br>                '</TD></TD>' ),<br>             ''<br>             ORDER BY SalesOrders.ID,<br>                   SalesOrderItems.LineID,<br>                   Products.ID ),<br>          '</TABLE></HTML>' )<br>  FROM SalesOrderItems<br>          INNER JOIN SalesOrders<br>                  ON SalesOrders.ID = SalesOrderItems.ID<br>          INNER JOIN Products<br>                  ON Products.ID = SalesOrderItems.ProductID<br>

Here's how it has to be coded if you're going to pass the SELECT as a string literal to the rewrite() function:

<br>SELECT rewrite ( '<br>SELECT STRING ( <br>          ''<HTML><HEAD><TITLE>'',<br>          ''All the demo database''''s sales identifiers'',<br>          ''</TITLE></HEAD><TABLE>'',<br>          LIST ( <br>             STRING ( <br>                ''<TR><TD>'',  SalesOrders.ID,<br>                ''</TD><TD>'', SalesOrderItems.LineID,<br>                ''</TD><TD>'', Products.ID,<br>                ''</TD></TD>'' ),<br>             ''''<br>             ORDER BY SalesOrders.ID,<br>                   SalesOrderItems.LineID,<br>                   Products.ID ),<br>          ''</TABLE></HTML>'' )<br>  FROM SalesOrderItems<br>          INNER JOIN SalesOrders<br>                  ON SalesOrders.ID = SalesOrderItems.ID<br>          INNER JOIN Products<br>                  ON Products.ID = SalesOrderItems.ProductID<br>' )<br>

But... there's an easier way: Copy the SELECT as-is (no doubling-up of the quotes required) to a separate text file, and then code xp_read_file() as the argument to rewrite().

Here, the SELECT has been copied to a text file called select.txt:

1	`<br>SELECT rewrite ( xp_read_file ( 'select.txt' ) )<br>`

By the way, here's what rewrite() had to say about the SELECT; no need for any joins at all since the SalesOrderItems table has all the columns that are needed:

<br>select<br>  STRING(<br>  '<HTML><HEAD><TITLE>',<br>  'All the demo database''s sales identifiers',<br>  '</TITLE></HEAD><TABLE>',<br>  LIST(<br>  STRING(<br>  '<TR><TD>',SalesOrderItems_1.ID,<br>  '</TD><TD>',SalesOrderItems_1.LineID,<br>  '</TD><TD>',SalesOrderItems_1.ProductID,<br>  '</TD></TD>'),<br>  '' order by<br>  SalesOrderItems_1.ID asc,<br>  SalesOrderItems_1.LineID asc,<br>  SalesOrderItems_1.ProductID asc),<br>  '</TABLE></HTML>')<br>  from SalesOrderItems as SalesOrderItems_1<br>

Saturday, January 29, 2011

The fRiDaY File

This was first published on March 22, 2006:

Your company isn't really as big as Amazon.com.

Friday, January 28, 2011

It Seems Like Only Yesterday

It seems like only yesterday when the Top 10 Cool New Features in SQL Anywhere 10 was published.

But it wasn't quite "yesterday", it was wayyyy back in 2006, and since then we've had the Top 10 Cool New lists for Version 11 (2008) and Version 12 (2010).

So it shouldn't come as any surprise...

but nevertheless it does

...to see the official End of Life Notice for SQL Anywhere Version 10.x effective on January 25, 2012.

What does that mean?

First, it means no more Version 10 EBFs after January 25, 2012.

Welllll, that's the official story. The real story might be somewhat different. Take the End of Life Notice for SQL Anywhere Version 9.0.x for example; it said the "End of Engineering Support" was coming on January 31, 2010.

And it did... sort of... on September 22, 2010 (almost eight months after End of Life) an EBF for 9.0.2 was released:

SQL Anywhere - Express Bug Fix, Update of 9.0.2 to build 3951 - 9.0.2 - 22 Sep 2010 - EBF/Patch

But no, you can't expect any EBFs after the End of Life date. What you probably can expect is "One Last EBF" right before End of Life, like the one released less than three weeks before the End of Life deadline for 9.0.2:

SQL Anywhere Studio - Express Bug Fix, Update of 9.0.2 to build 3924 (all components) - 9.0.2 - 07 Jan 2010 - EBF/Patch

Life After Death

The "End of Engineering Support" does not mean the "End of Technical Support"... it doesn't even mean the "End Of Technical Support by Engineering".

You will still be able to get your Version 10 questions answered after January 25, 2012... just like you can still get your Version 5.5 questions answered today.

It just means if you step on a bug in Version 9.0.2 after that date, you probably won't get a fix. A workaround, maybe, but not a fix.

Wednesday, January 26, 2011

Did you know?

Did you know that SQL Anywhere has a DESCRIBE TABLE statement?

<br>CREATE TABLE t (<br>   pkey   INTEGER           NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,<br>   data1  VARCHAR ( 100 )   NOT NULL DEFAULT '',<br>   data2  DECIMAL ( 11, 2 ) NOT NULL DEFAULT 0.0,<br>   data3  LONG BINARY       NOT NULL DEFAULT '' );<br><br>DESCRIBE TABLE t;<br><br>Column  Type          Nullable  Primary Key<br>pkey    integer          0           1<br>data1   varchar(100)     0           0<br>data2   decimal(11,2)    0           0<br>data3   long binary      0           0

Did you know that it's been there since Version 10?

How about this?

Did you know that SQL Anywhere has an sa_describe_query() procedure that returns all sorts of information about each and every column in a SELECT?

Did you know that sa_describe_query() has also been there since Version 10?

Did you also know that sa_describe_query() can do everything DESCRIBE TABLE can do, plus a lot more?

<br>SELECT * FROM sa_describe_query ( 'SELECT * FROM t', 1 );<br><br>column_number name  domain_id domain_name domain_name_with_size   width   scale declared_width user_type_id user_type_name correlation_name base_table_id base_column_id base_owner_name base_table_name base_column_name nulls_allowed is_autoincrement is_key_column is_added_key_column<br>      1       pkey      2     int         int                         4     0            4       (NULL)         (NULL)            t            723            1            DBA                t                pkey          false            true           true             false<br>      2       data1     9     varchar     varchar(100)              100     0          100       (NULL)         (NULL)            t            723            2            DBA                t                data1         false            false          false            false<br>      3       data2    27     decimal     decimal(11,2)              11     2           11       (NULL)         (NULL)            t            723            3            DBA                t                data2         false            false          false            false<br>      4       data3    12     long binary long binary        2147483647     0        32767       (NULL)         (NULL)            t            723            4            DBA                t                data3         false            false          false            false

Here's a big difference that might not be obvious: DESCRIBE TABLE is an ISQL statement so you'll see "Syntax error near 'describe'" if you try to use it inside a stored procedure, whereas sa_describe_query works just fine in both contexts (ISQL and SQL):

<br>CREATE PROCEDURE p ( IN @query LONG VARCHAR )<br>BEGIN<br>   SELECT name                  AS column_name,<br>          domain_name_with_size AS data_type<br>     FROM sa_describe_query ( @query, 1 )<br>    ORDER BY column_number;<br>END;<br><br>SELECT * FROM p ( 'SELECT * FROM t' );<br><br>column_name  data_type<br>pkey         int<br>data1        varchar(100)<br>data2        decimal(11,2)<br>data3        long binary

If the query involves a view, sa_describe_query() tells you where the columns are coming from (if it can):

<br>CREATE VIEW v AS<br>SELECT data1,<br>       data2 * 10,<br>       pkey<br>  FROM t;<br><br>SELECT name                                              AS view_column_name,<br>       domain_name_with_size                             AS data_type,<br>       STRING ( base_table_name, '.', base_column_name ) AS base_column <br>  FROM sa_describe_query ( 'SELECT * FROM v' )<br> ORDER BY column_number;<br><br>view_column_name  data_type      base_column<br>data1             varchar(100)   t.data1<br>expression        numeric(13,2)  .<br>pkey              int            t.pkey<br><br>SELECT name                                              AS view_column_name,<br>       domain_name_with_size                             AS data_type,<br>       STRING ( base_table_name, '.', base_column_name ) AS base_column <br>  FROM sa_describe_query ( 'SELECT * FROM SYSTAB' )<br> ORDER BY column_number;<br><br>view_column_name    data_type        base_column<br>table_id            unsigned int     ISYSTAB.table_id<br>dbspace_id          smallint         ISYSTAB.dbspace_id<br>count               unsigned bigint  ISYSTAB.count<br>creator             unsigned int     ISYSTAB.creator<br>table_page_count    int              ISYSTAB.table_page_count<br>ext_page_count      int              ISYSTAB.ext_page_count<br>commit_action       int              ISYSTAB.commit_action<br>share_type          int              ISYSTAB.share_type<br>object_id           unsigned bigint  ISYSTAB.object_id<br>last_modified_at    timestamp        ISYSTAB.last_modified_at<br>table_name          char(128)        ISYSTAB.table_name<br>table_type          tinyint          ISYSTAB.table_type<br>replicate           char(1)          ISYSTAB.replicate<br>server_type         tinyint          ISYSTAB.server_type<br>tab_page_list       long varbit      ISYSTAB.tab_page_list<br>ext_page_list       long varbit      ISYSTAB.ext_page_list<br>pct_free            unsigned int     ISYSTAB.pct_free<br>clustered_index_id  unsigned int     ISYSTAB.clustered_index_id<br>encrypted           char(1)          ISYSTAB.encrypted<br>last_modified_tsn   unsigned bigint  ISYSTAB.last_modified_tsn<br>file_id             smallint         ISYSTAB.dbspace_id<br>table_type_str      char(13)         .

Monday, January 24, 2011

Techwave 2011

Sybase Techwave 2011 is going to be co-located with the Las Vegas edition of SAP TechEd 2011 at the Venetian hotel (where I hear the rib steak is excellent) on Monday September 12 through Friday September 16 2011.

Friday, January 21, 2011

The fRiDaY File

This was first published on March 21, 2006:

All washed up

IvanAnywhere, meet TeleDork

Well, I don't care if it looks like Vlad The Impaler's vision of a Teletubbie...

... I Want One!

Wednesday, January 19, 2011

SQL Anywhere Debugger Beta

For more information, click here...

Monday, January 17, 2011

More Toad

No SQL Anywhere support in Toad yet, but AFAIK it's still coming.

In the meantime, there's a Toad for Sybase Webcast tomorrow...

and an Open Toad Beta running now...

...yes, I know, bad pun, BAD pun :)

Friday, January 14, 2011

The fRiDaY File

This was first published on February 27, 1996:

Yesterday a colleague suggested that Wally sounds just like me, but I don't see it myself.

p_drop_other_connections Revisited

You may have seen a little stored procedure in the newsgroups or out on the web called "p_drop_other_connections" which issues DROP CONNECTION commands for every connection except the one that called the procedure. It's useful when you want to grab complete control of a running SQL Anywhere server and do something important, like ALTER TABLE or CREATE INDEX on the Table From Hell that everyone's using:

/* How to get full control over a SQL Anywhere server...

(1) CALL sa_server_option ( 'ConnsDisabled', 'ON' );

    At this point, any attempt to open a new connection will
    get "Connections to database have been disabled". 
    However, current connections will still work until...

(2) CALL p_drop_other_connections(); 

(3) Do whatever you need to do.

(4) CALL sa_server_option ( 'ConnsDisabled', 'OFF' );

*/

Well, it turns out that the original p_drop_other_connections doesn't work at all on connections to other databases running on the same server: the DROP CONNECTION command gets SQLCode -121 Permission denied.

Here's a new version of p_drop_other_connections that does work, by sending DROP CONNECTION commands to the other databases where they will work without getting permission denied:

<br>CREATE PROCEDURE p_drop_other_connections ()<br>BEGIN<br><br>/* How to get full control over a SQL Anywhere server...<br><br>(1) CALL sa_server_option ( 'ConnsDisabled', 'ON' );<br><br>    At this point, any attempt to open a new connection will<br>    get "Connections to database have been disabled". <br>    However, current connections will still work until...<br><br>(2) CALL p_drop_other_connections(); <br><br>(3) Do whatever you need to do.<br><br>(4) CALL sa_server_option ( 'ConnsDisabled', 'OFF' );<br><br>*/<br><br>DECLARE @this_database_number     INTEGER;<br>DECLARE @this_connection_number   INTEGER;<br>DECLARE @database_name            VARCHAR ( 128 );<br>DECLARE @sql                      LONG VARCHAR;<br>DECLARE @sqlcode                  INTEGER;<br>DECLARE @sqlstate                 VARCHAR ( 5 );<br>DECLARE @errormsg                 VARCHAR ( 32767 );<br>DECLARE @connection_count         INTEGER;<br>DECLARE @dropped_count            INTEGER;<br><br>SET @this_database_number   = CONNECTION_PROPERTY ( 'DBNumber' );<br>SET @this_connection_number = CONNECTION_PROPERTY ( 'Number' );<br>SET @connection_count       = 0;<br>SET @dropped_count          = 0;<br><br>SET @database_name = ''; -- until filled in with an actual value<br><br>-- Create a remote server without the DBN filled in.<br><br>BEGIN<br>   DROP SERVER other_database;<br>   EXCEPTION WHEN OTHERS THEN -- ignore any exception<br>END;<br><br>CREATE SERVER other_database CLASS 'SAODBC' <br>   USING 'DRIVER=SQL Anywhere 11;DBN=unknown';<br><br>-- Loop through each connection on the server.<br><br>FOR f_fetch AS c_fetch INSENSITIVE CURSOR FOR<br>SELECT sa_conn_info.DBNumber AS @database_number, <br>       sa_conn_info.Number   AS @connection_number <br>  FROM sa_conn_info() <br> ORDER BY sa_conn_info.DBNumber,<br>       sa_conn_info.Number<br>FOR READ ONLY<br>DO<br><br>   SET @connection_count = @connection_count + 1;<br><br>   CASE<br><br>      WHEN @database_number   = @this_database_number<br>       AND @connection_number = @this_connection_number THEN<br><br>         -- Don't drop this connection.<br><br>      WHEN @database_number = @this_database_number THEN<br><br>         -- Drop local connection.<br><br>         BEGIN<br>            SET @sql = STRING ( <br>               'DROP CONNECTION ', <br>               @connection_number );<br>            MESSAGE @sql TO CLIENT;<br>            EXECUTE IMMEDIATE @sql;<br>            SET @dropped_count = @dropped_count + 1;<br>            EXCEPTION WHEN OTHERS THEN<br>               SELECT SQLCODE, SQLSTATE, ERRORMSG() <br>                 INTO @sqlcode, @sqlstate, @errormsg;<br>               MESSAGE STRING ( 'EXCEPTION: ', <br>                  @sqlcode, ', ', @sqlstate, ', ', @errormsg ) TO CLIENT;<br>         END;<br><br>      ELSE <br><br>         IF DB_PROPERTY ( 'Name', @database_number ) <> @database_name THEN<br><br>            -- Change the DBN for the remote server.<br><br>            SET @database_name = DB_PROPERTY ( 'Name', @database_number );<br><br>            BEGIN<br>               SET @sql = 'ALTER SERVER other_database CONNECTION CLOSE';<br>               MESSAGE @sql TO CLIENT;<br>               EXECUTE IMMEDIATE @sql;<br>               SET @sql = STRING ( <br>                  'ALTER SERVER other_database USING ''DRIVER=SQL Anywhere 11;DBN=',<br>                  @database_name,<br>                  '''' );<br>               MESSAGE @sql TO CLIENT;<br>               EXECUTE IMMEDIATE @sql;<br>               EXCEPTION WHEN OTHERS THEN<br>                  SELECT SQLCODE, SQLSTATE, ERRORMSG() <br>                    INTO @sqlcode, @sqlstate, @errormsg;<br>                  MESSAGE STRING ( 'EXCEPTION: ', <br>                     @sqlcode, ', ', @sqlstate, ', ', @errormsg ) TO CLIENT;<br>            END;<br><br>         END IF;<br><br>         -- Drop the remote connection.<br><br>         BEGIN<br>            SET @sql = STRING ( <br>               'FORWARD TO other_database ''DROP CONNECTION ',<br>               @connection_number,<br>               '''' );<br>            MESSAGE @sql TO CLIENT;<br>            EXECUTE IMMEDIATE @sql;<br>            SET @dropped_count = @dropped_count + 1;<br>            EXCEPTION WHEN OTHERS THEN<br>               SELECT SQLCODE, SQLSTATE, ERRORMSG() <br>                 INTO @sqlcode, @sqlstate, @errormsg;<br>               MESSAGE STRING ( 'EXCEPTION: ', <br>                  @sqlcode, ', ', @sqlstate, ', ', @errormsg ) TO CLIENT;<br>         END;<br>   <br>   END CASE;<br><br>END FOR;<br><br>BEGIN<br>   DROP SERVER other_database;<br>   EXCEPTION WHEN OTHERS THEN -- ignore any exception<br>END;<br><br>MESSAGE STRING ( @dropped_count, ' of ', @connection_count, ' connections dropped.' ) TO CLIENT;<br><br>END;

Here's what a test looks like:

CALL p_drop_other_connections();

ALTER SERVER other_database CONNECTION CLOSE
ALTER SERVER other_database USING 'DRIVER=SQL Anywhere 11;DBN=ddd11b'
FORWARD TO other_database 'DROP CONNECTION 26'
FORWARD TO other_database 'DROP CONNECTION 27'
DROP CONNECTION 30
ALTER SERVER other_database CONNECTION CLOSE
ALTER SERVER other_database USING 'DRIVER=SQL Anywhere 11;DBN=ddd11c'
FORWARD TO other_database 'DROP CONNECTION 28'
FORWARD TO other_database 'DROP CONNECTION 29'
5 of 6 connections dropped.

Here's the before-and-after...

before and after

Here are some notes on the code...

The CREATE SERVER statement at line 44 establishes the partial definition for a remote server that will be completed via ALTER SERVER statements later on. The CREATE SERVER statement doesn't actually DO anything, like connect to a remote server, but that's not important here... what's important is that CREATE SERVER doesn't have to be semantically perfect when it's executed.

The FOR statement at line 49 calls the sa_conn_info() procedure to get information about every single connection to every single database on the server.

The CASE statement starting at line 60 makes a three-way choice:

This connection? Don't do anything.
A different connection to this database? Issue a simple DROP CONNECTION.
A different database? Send a DROP CONNECTION to that database via FORWARD TO.

The BEGIN END blocks starting at lines 71, 93 and 114 are there to capture any exceptions and carry on with the next connection.

The EXECUTE IMMEDIATE statement at line 76 drops the local connection. If it gets an exception, the SET @dropped_count = @dropped_count + 1 at line 77 doesn't get executed; instead, the MESSAGE statement at line 81 displays the exception in the ISQL Messages tab.

The ALTER SERVER on lines 94 through 96 forces the remote server to disconnect from the previous database. This is necessary for the change made by the second ALTER SERVER on lines 97 through 102 to have any effect; otherwise, the old connection would remain in effect even though the USING clause has changed.

The FORWARD TO statement on lines 115 through 120 send the DROP CONNECTION statement over to the other database, where it's legal.

Wednesday, January 12, 2011

Syntax error on line 5475, column 0: Illegal character "\n" (10)

Back in my callow youth, when I learning to program, the bane of my existence was compilers that diagnosed syntax errors thusly:

ERROR IN ABOVE PROGRAM

Yes, parsers always SHOUTED in those days, no lower case on the printers... which wouldn't have helped anyway since keypunches were all upper case as well.

Now, everything's so much better, no more keypunches, and parsers don't shout any more.

But sometimes, the error messages are just as unhelpful:

$Syntax error on line 5475, column 0: Illegal character \n (10)$

So what does that mean? Especially when there is no line 5475, the code ends at 5474.

It means you've made a really simple mistake, like a missing quote, and you are being punished by the parser.

Here's one way to find the problem: Open the offending SQL file in dbisql, and run it so you get the same error. Then scroll to the bottom of the file; you will probably see that the syntax coloring is wrong: what should be black and blue is red, and what should be red is black and blue.

Keep scrolling upwards until the coloring is correct again, and at that point you should see where to put the missing quote:

IF @update_diagnostics = '
   THEN ''
   ELSE '\x0d\x0a'
   ENDIF,

Or where to take out the extra quote.

Or where to fix the

'stupid backquote`

and make it a

'proper quote'.

Monday, January 10, 2011

READ and PARAMETERS

The ISQL READ and PARAMETERS statements are wonderful things when you need to apply string substitutions to SQL code when it's being compiled or executed.

Here's how to use them:

Assign a name to each different string substitution value you want applied to a SQL file.
Code each name in a PARAMETERS statement at the top of the SQL file, like this:
```
PARAMETERS column_name, column_value, primary_key_value;
```
Code the parameter name in {curly braces} down in the body of the SQL file wherever you want the actual value to be substituted; for example:
```
UPDATE t SET t.{column_name} = '{column_value}' WHERE t.pkey = {primary_key_value};
```
Code the actual values in [square braces], left to right in the same order as the PARAMETERS statement, in a READ statement on the ISQL command line, like this:
```
"%SQLANY12%\bin32\dbisql.com"^
  -c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
  READ ENCODING Cp1252 p1.sql [data2] [Hello, world!] [3]
```
Run the ISQL command line to have the actual values substituted like this:
```
UPDATE t SET t.data5 = 'Hello, world!' WHERE t.pkey = 3;
```

Here's a demonstration; first, the table definition:

CREATE TABLE t (
   pkey  INTEGER NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
   data1  VARCHAR ( 100 ) NOT NULL DEFAULT '',
   data2  VARCHAR ( 100 ) NOT NULL DEFAULT '',
   data3  VARCHAR ( 100 ) NOT NULL DEFAULT '' );

INSERT t DEFAULT VALUES;
INSERT t DEFAULT VALUES;
INSERT t DEFAULT VALUES;
INSERT t DEFAULT VALUES;
COMMIT;

SELECT * FROM t ORDER BY pkey;

pkey,data1,data2,data3
1,'','',''
2,'','',''
3,'','',''
4,'','',''

Here's a procedure in a file called p1.sql:

PARAMETERS column_name, column_value, primary_key_value;

CREATE PROCEDURE p1()
BEGIN
   UPDATE t SET t.{column_name} = '{column_value}' WHERE t.pkey = {primary_key_value};
   COMMIT;
END;

Here's the dbisql command used to compile p1.sql:

"%SQLANY12%\bin32\dbisql.com"^
  -c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
  READ ENCODING Cp1252 p1.sql [data2] [Hello, world!] [3]

Here's a test of the procedure p1:

CALL p1();
SELECT * FROM t ORDER BY pkey;

pkey,data1,data2,data3
1,'','',''
2,'','',''
3,'','Hello, world!',''
4,'','',''

Now, supposing you have lots and lots of SQL files, many or all of them needing PARAMETERS substitution, and you want run one ISQL command line to compile them all. Here's how:

Create a master SQL file containing one READ statement for each of the other files.
Code a master PARAMETERS statement at the top of the master SQL file to define the complete set of substitution values across all the other files.
Use nested [{square and curly braces}] to pass the actual values from the READ statement on the master ISQL command line, down through the READ statements inside the master SQL file, and on to the other SQL files.

Here's a sample master.sql file:

PARAMETERS column_name, column_value, primary_key_value, global_database_id;
READ ENCODING Cp1252 p1.sql [{column_name}] [{column_value}] [{primary_key_value}];
READ ENCODING Cp1252 p2.sql [{global_database_id}] [{column_name}] [{primary_key_value}];

Here's the master ISQL command line:

"%SQLANY12%\bin32\dbisql.com"^
  -c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
  READ ENCODING Cp1252 master.sql [data2] [Hello, world!] [3] [123]

Here's the new p2.sql file:

PARAMETERS global_database_id, column_name, primary_key_value;

CREATE PROCEDURE p2()
BEGIN
   SET OPTION PUBLIC.global_database_id = '{global_database_id}';
   UPDATE t SET t.{column_name} = UPPER ( t.{column_name} } WHERE t.pkey = {primary_key_value};
   COMMIT;
END;

Here's a test of p1 and p2:

CALL p1();
CALL p2();
SELECT DB_PROPERTY ( 'GlobalDBId' );
SELECT * FROM t ORDER BY pkey;

DB_PROPERTY('GlobalDBId')
'123'

pkey,data1,data2,data3
1,'','',''
2,'','',''
3,'','HELLO, WORLD!',''
4,'','',''

Tip: If you're using SQL Anywhere 10 or later, and you see an error like this,

Could not execute statement.
  Invalid setting for option 'global_database_id'
  SQLCODE=-201, ODBC 3 State="42000"
  File: "test1.sql" on line 3, column 40
  SET OPTION PUBLIC.Global_database_id = '{value}'

try wrapping the offending statement in a BEGIN END block to force ISQL to handle the string substitution properly.

For example, change your SQL file from this (which works OK in SQL Anywhere 9 but throws SQLCODE = -201 in versions 10, 11 and 12)...

PARAMETERS value;

SET OPTION PUBLIC.Global_database_id = '{value}';

MESSAGE STRING ( 'GlobalDBId = ', DB_PROPERTY ( 'GlobalDBId' ) ) TO CLIENT;

to this...

PARAMETERS value;

BEGIN
   SET OPTION PUBLIC.Global_database_id = '{value}';

   MESSAGE STRING ( 'GlobalDBId = ', DB_PROPERTY ( 'GlobalDBId' ) ) TO CLIENT;
END;

which works in all four versions of SQL Anywhere to display this...

GlobalDBId = 123
Execution time: 0 seconds

Friday, January 7, 2011

The fRiDaY File

This was first published on March 20, 2006:

DB2

STFW

Here's a wall-mounted electrical timer that controls some outdoor lights. When it's working those lights turn on at dusk and off at dawn:

When it's not working (which is right after anyone messes with it) the lights turn on and off at the wrong times.

The definition of "anyone" includes "me"... after hours of messing with it, I realized that I had to RTFM.

Trouble was, no manual. No visible model number either, not even a manufacturer's name. A friend suggested "take the photo to Home Depot and buy another timer... that way you'll get a manual."

But that was then...

...and this is 2011.

It ain't RTFM any more, the new rule is STFW.

So I took the word "timer" plus the only words visible on the timer itself (off clear on) and did a Google search.

I did this even though I was sure that "off" and "on" would be useless noise as far as Google was concerned...

timer off clear on

GADZOOKS!

The first result was exactly what I was looking for!

STFW Rules!

What does this have to do with SQL Anywhere?

Nothing, except that not all answers to SQL Anywhere questions are found in the SQL Anywhere manuals. Sometimes they are found elsewhere.

That's what STFW means: Look elsewhere. Look everywhere.

For example: How do I stop dbmlsync from a command line?

How do I stop dbmlsync from a command line?

That search didn't work so well a few days ago, but this generalized version did: How do I kill a Windows task from a command line?

That answer ("it's called taskkill, and it's from Microsoft") led to this search: taskkill site:microsoft.com

Question: When Google becomes self-aware, will anyone notice?

Wednesday, January 5, 2011

The Incredible Shrinking Database: A Quiz

How well do you know how SQL Anywhere works, when it comes to using disk space? Here's a quiz to test your knowledge...

But first, the setup...

The following table shows 4 stages in the history of a SQL Anywhere 12.0.0.2601 database.

The "File Size" column shows how big the main database file was at each stage, based the FileSize database property.
The "Free Space In File" column show how much free space existed in the database file, based on FreePages database property.
The "Disk Space Usage For All Tables" column shows how disk space was allocated for table data, blob extensions and indexes. These numbers were calculated by Foxhound based on data from the SYSTAB and SYSPHYSIDX tables.
The "Table From Hell" column shows how disk space was allocated for The One Big Table in this database. These numbers were also calculated by Foxhound based on SYSTAB and SYSPHYSIDX.
Stage 1 is the starting point, with over 3 million rows of data pretty much filling up all 475 megabytes of disk space in the main database file.
Stage 2 was reached after a series of DELETE statements got rid of almost all of the rows in the Table From Hell, followed by a COMMIT and a CHECKPOINT.
Stage 3 was reached after the SQL Anywhere server was stopped and restarted.
Stage 4 was reached after the database was rebuilt via dbunload, dbinit and dbisql.

And now, the Quiz...

Why did the File Size more than double from 475M to 1.1G between stages 1 and 2?
Why did the Table From Hell's disk space usage only drop from 440M to 368M between stages 1 and 2 even though 97% of the rows were deleted?
Hint: Don't pay too much attention to the "bytes per row" numbers. They are based on the disk space allocated to the table, not the actual data stored in each row. In other words, no, the rows didn't suddenly change in size, nor did the DELETEs just get rid of short rows.
Why did the Free Space In File only grow to 122M in stage 2 even though so much data was deleted?
Why did the File Size drop so dramatically between stages 2 and 3?
Why did the Free Space In File more than double in size between stages 2 and 3?
Why did the disk space allocated to the Table From Hell drop at all between stages 2 and 3?
Why is the file still roughly the same size in stage 3 that it was in stage 1, even though the Table From Hell only takes up half as much disk space?
Why did the disk space allocated to the Table From Hell drop so dramatically between stages 3 and 4?
Why did the File Size drop so dramatically between stages 3 and 4?
Why is the Free Space In File so small in stage 4?

Ten questions, ten points each. When I find out what all the answers are, I'll post them.

"WHAT!?!? What do you mean, you don't know all the answers?"

That's right.

And I'll give out 10 Bonus Points if you can point out which questions I'm not sure of.

Hint: I'm pretty sure of numbers 9 and 10.

...and I'm pretty sure the only people who will score 100 work in Engineering. Or 110 :)

Monday, January 3, 2011

Be Very Afraid Of Floating Point Numbers

What does this statement in the Help mean, exactly?

Values held as DOUBLE are accurate to 15 significant digits, but may be subject to rounding error beyond the fifteenth digit.

In the classroom or laboratory, it probably means this:

Values held as DOUBLE are accurate to 15 significant digits, but may be subject to rounding error beyond the fifteenth digit.

In the real world, where people write programs to do real things with real data, it means this:

Values held as floating point cause all sorts of problems and should be avoided at all costs. When you can't avoid them, convert them. When you can't convert them, don't trust them: Test them, check them, and fix them when you find errors in the data. Do it in your code, don't wait for your users to notice drifting data six months down the road.

Here's an example that doesn't involve any arithmetic, so we can't blame the algorithms:

An application written using Microsoft Access has a large number of columns with the "Data Type" defined as "Number" and the "Field Size" set to "Double" instead of "Long Integer" or "Decimal" or something safe and manageable.
One of these columns is the primary key for a table with over 3 million rows.
The SQL Anywhere 12 migration wizard is used to convert the Access database into a SQL Anywhere database, and it dutifully defines the primary key as DOUBLE... after all, what else can it do? That's what Access says it is.
After migrating, most of the primary key values look like integers; they have values like 11, 21 and 3208547315.
However, over a million rows have values that look like this: 1020661200.9999999, 1020661224.0000001, 3108547316.0000005 and 3108547313.9999995.

Yes, those are all way out past the fifteenth digit.

I don't care where the problem comes from, Access, SQL Anywhere or the migration process. This kind of crap always happens when you mess with floating point, and in this case I just want it expunged, excised, gone, done with.

I'm not going to take a chance with a situation like this...

1	`<br>SELECT ( SELECT COUNT() FROM t WHERE pkey - TRUNCNUM ( pkey, 0 ) = 0 ) AS "Integers", <br> ( SELECT COUNT() FROM t WHERE pkey - TRUNCNUM ( pkey, 0 ) <> 0 ) AS "Not Integers";<br><br>Integers Not Integers<br>1,917,541 1,252,134<br>`

I'm going to bite the bullet, do this...

1	`<br>UPDATE t<br> SET pkey = ROUND ( pkey, 0 )<br> WHERE pkey - TRUNCNUM ( pkey, 0 ) <> 0;<br><br>1252134 row(s) updated<br>Execution time: 272.574 seconds<br>`

Foxhound Monitor showing UPDATE t SET pkey = ROUND

Then I'm going to UNLOAD that table, change the data type from DOUBLE to BIGINT, and reload.

Or do an ALTER TABLE, that might be fun.

Either way, no more DOUBLE.

No more fear.

Pages

Monday, January 31, 2011

Saturday, January 29, 2011

Friday, January 28, 2011

but nevertheless it does

What does that mean?

Life After Death

Wednesday, January 26, 2011

How about this?

Monday, January 24, 2011

Friday, January 21, 2011

... I Want One!

Wednesday, January 19, 2011

Monday, January 17, 2011

Friday, January 14, 2011

Wednesday, January 12, 2011

Monday, January 10, 2011

Friday, January 7, 2011

But that was then...

GADZOOKS!

STFW Rules!

Wednesday, January 5, 2011

But first, the setup...

And now, the Quiz...

"WHAT!?!? What do you mean, you don't know all the answers?"

Monday, January 3, 2011

Categories