SQL Anywhere

Coding Good Comments: Explanations

noreply@blogger.com (Breck Carter) — Wed, 01 Feb 2012 08:01:00 +0000

The code comes first: If there's a choice to be made between writing clear, understandable program code and writing comments to explain less-than-readable code, comments should take a back seat.

The reason is simple: It's the code that determines what the program does, not the comments. Comments aren't even compiled, let alone executed. So it shouldn't come as a surprise that maintenance programmers concentrate on the code, often skipping over the comments altogether.

Sadly, however, even readable, understandable program code tells only part of the story: It only answers the question, "How is this program accomplishing its tasks?" Very few executable statements directly address the question, "What is this program doing?", with the possible exception of a CALL statement where the procedure name has been carefully crafted to answer the "What?"

There are no exceptions, however, when it comes to answering the questions, "Why is the program doing this thing?" or the much harder "Why isn't the program doing this other thing?"

So, comments come second, but

Explanations Are Important

Explanation: A concise comment describing the "What" and "Why" of a section of program code while avoiding the "How" which should be apparent from reading the code itself.

Example 1

Here is a short section of simple code (basically a SELECT FROM DUMMY) with several explanations, some good, some not so good:

------------------------------------------------------------
      -- Perform the basic heartbeat or canarian query.
      -- This is done to calculate the heartbeat time as well check the connection.
      -- Note: The target database can be stopped and restarted, and the monitor should just keep trying to connect.
      -- Note: When Foxhound itself is is stopped and restarted, reconnection to target databases *should* be automatic.

      SET @sql = STRING ( 'SELECT dummy_col INTO @dummy_col FROM ', @proxy_owner, '.proxy_DUMMY' );

      SET @canarian_query_started_at  = CURRENT TIMESTAMP;
      SET @canarian_query_finished_at = @canarian_query_started_at;

      EXECUTE IMMEDIATE @sql;

      ROLLBACK; -- release locks, recover from possible failed proxy connection

      SET @connected_ok = 'Y';
      SET @canarian_query_finished_at = CURRENT TIMESTAMP;

      MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' ', CONNECTION_PROPERTY ( 'Number' ), 
         ' 204-4 Monitor ', @sampling_id, ': First heartbeat OK' ) TO CONSOLE DEBUG ONLY;

      -- Note: Because the proxy_DUMMY table is the last proxy object created, a 
      -- successful query against proxy_DUMMY may taken as proof that a connection exists
      -- and is usable. This is important because a previous connection attempt
      -- may be dropped (timed out) after it created some but not all necessary proxy objects.
      -- Such a partly-established connection must be treated as no connection at all, and
      -- that is guaranteed by creating proxy_DUMMY last.

In the comment "This is done..." the phrase "as well as check the connection" provides extra information about the "What?" of this code, so that's OK as an explanation.

The phrase "calculate the heartbeat time" is a bit redundant, however... not wrong, but not really necessary given the preceding section title "Perform the basic heartbeat or canarian query."

The two one-line "Note:" comments are a bit other-worldly because they state true facts that have very little (nothing?) to do with nearby code. These score zero on the Comment Value Scale; not a positive score because they offer no real value, but not a negative score because at least they aren't wrong.

The long "Note: Because the proxy_DUMMY table is ..." paragraph scores very high, however, because it explains a very important "Why?" for this otherwise trivial-looking section of code.

Example 2

This example shows two separate explanations that were added to the code at two separate times.

The later paragraph "It is possible..." came first, chronologically speaking, to explain why the unusual step of calling GET_IDENTITY was performed instead of just letting the later INSERT assign the value... so far, so good, the original "Why?" is covered.

------------------------------------------------------------
-- Calculate candidate primary key for successful sample.

-- Note: The following discussion is moot, but @candidate_sample_set_number is still
--       assigned because later code refers to it.

-- It is possible for this event to take some time before inserting a row
-- in rroad_sample_set. During that time, it is also possible for sampling to be 
-- stopped, and a "sampling stopped" row to be inserted. The row inserted by this
-- event should have a lower primary key than the "sampling stopped" event, so it
-- cannot wait until the actual INSERT rroad_sample_set statement to calculate the
-- value of the autoincrement column sample_set_number. The following statement 
-- pre-assigns a value which will only be used for the INSERT of a "successful" 
-- sample set; if that INSERT is not performed because of some problem, there will
-- be a gap in the primary key sequence.

SET @candidate_sample_set_number = GET_IDENTITY ( 'rroad_sample_set' );

The second explanation, the previous "Note: ...", was added later to explain that GET_IDENTITY was being called through laziness rather than real need. This explanation isn't quite as thorough, but it does serve to prevent the new maintenance programmer from searching the code to find the reason GET_IDENTITY was called.

In a perfect world, the call to GET_IDENTITY would have been removed as soon as it became unnecessary. In an imperfect world, it's easier to add the "Note:" when the call became moot... not just because it's less work to write the comment than change the code, but also because comments aren't compiled so the "Note:" doesn't have to be tested.

Example 3

The following example almost got left out because at first glance it looked like just another bad explanation: Who needs to be told that sa_locks needs DBA authority?

------------------------------------------------------------
   -- Create proxy_sa_locks if necessary.
   -- This is only done after a full connection process.
   -- Note: DBA authority is needed to call sa_locks.

Well, with this application (Foxhound) whether the user id has DBA authority or not controls on how much detail the user's going to get, and this comment explains to the maintenance programmer that information about row locks is going to be missing from that detail if the user doesn't have DBA authority.

So, this "Note: DBA authority is needed to call sa_locks" looks like a "How?" but it's really a "What?", albeit on a grander scale: "What does the maintenance programmer need to know about this code?"

Example 4

Here is a breathtakingly-verbose "Note:" that explains a very simple-looking IF NOT EXISTS RETURN control structure:

------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
-- Step 5 - Check to see if sampling has been turned off. 
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------

IF  @ok = 'Y'  
AND @connected_ok = 'Y' THEN 

   -- Note: This code is an attempt to prevent a successful sample from appearing between 
   --       successive "Sampling stopped at" and "Sampling stopped" lines in the Monitor display.
   --       This process may already be executing the code above this point when sampling is stopped by
   --       a different process, and the @sample_started_at used by this process is calculated in the
   --       next section... which is after the the "Sampling stopped at" point calculated by the other
   --       process but before the eventual "Sampling stopped" point which will be calculated by some
   --       (probably much later) process.
   -- 
   --       In the following example, the sample at Apr 22 5:12:21 PM should not have been recorded:
   --          Apr 22 5:13:35 PM   1m 14.2s    0s / .1s ...
   --          Apr 22 5:13:21 PM   1m 18.8s   -- Sampling stopped --  
   --          Apr 22 5:12:21 PM   0s    0s / 0s    1 ...
   --          Apr 22 5:12:02 PM   1m 24.2s   -- Sampling stopped at --  
   --          Apr 22 5:10:38 PM   1m 10.1s    .1s / .1s ...
   --
   --       In the following example, the sample at 5:11:23 PM should not have been recorded:
   --          5:11:32 PM   8.5s    .1s / 0s ...
   --          5:11:27 PM    ALL CLEAR - Alert # 1: Foxhound has been unable to gather samples for 1m or longer. 
   --          5:11:25 PM   4m 54.7s   -- Sampling stopped --  
   --          5:11:23 PM   0s    0s / .1s ...
   --          5:06:30 PM   0s   -- Sampling stopped at --  
   --          5:06:30 PM   39.6s   -- Foxhound stopped --  
   --          5:05:50 PM   0s   -- Foxhound stopped at --  

   IF NOT EXISTS ( SELECT *
                     FROM rroad_sampling_options
                    WHERE rroad_sampling_options.sampling_id                = @sampling_id
                      AND rroad_sampling_options.sampling_should_be_running = 'Y' ) THEN

      RETURN; -- do not record this sample

   END IF;

Here's the reason for the long explanation: The bug very hard to find, and the fix so simple that no future maintenance programmer, not even the origial author, would understand the "Why?" of the IF-NOT-EXISTS-RETURN code without some help.

In fact, without the explanation, it's not clear that the code is even correct let alone necessary. Comments don't just save time, they can prevent mistakes (like removing a piece of code because it looks wrong.)

In this case the comment itself wasn't that hard to write: the sample data was gathered as part of the diagnostic effort, and the explanation was worked out carefully and even written down as part of that same effort... it took very little extra effort to copy the explantion into the code as an aid to future developers.

The Bottom Line

Explanations belong inside the code, not locked in the fading memory of the original author or lost inside some other document no developer will ever read.

That is, if you think explanations are important.

Folks who don't think explanations are important generally don't think readability's important either. That's why most code without comments is also unreadable... not because there are no comments, but because the author cares more about abstract measurements of "elegance" or believes that somehow readability contradicts correctness.

Latest SQL Anywhere EBFs - 11.0.1 For (Almost) Everyone!

noreply@blogger.com (Breck Carter) — Mon, 30 Jan 2012 08:01:00 +0000

The three asterisks "***" show what's new since the previous list.

Only EBFs for the latest fully-supported versions of SQL Anywhere are shown here: 10.0.1, 11.0.1 and 12.0.1.
Note: Some earlier versions are also fully supported but they are not shown here; e.g., the 12.0.0.2758 EBF for Windows was recently released. For more information see SQL Anywhere Supported Platforms and Engineering Support Status.
Also: Just because an older version isn't "fully supported" doesn't mean you can't download files (or ask questions, or get help), it just means there won't be any more new EBFs released. For a complete list of everything available for download see Downloads - EBFs / Maintenance.

Current builds for the most active platforms...

HP-UX Itanium    12.0.1.3523 EBF       15 Dec 2011
                 11.0.1.2745 EBF ***   24 Jan 2012 ***
                 10.0.1.3777 EBF       14 Oct 2008

IBM AIX          12.0.1.3523 EBF       15 Dec 2011
                 11.0.1.2745 EBF ***   26 Jan 2012 ***

Linux x86/x64    12.0.1.3519 EBF       20 Dec 2011
                 11.0.1.2745 EBF ***   24 Jan 2012 ***
                 10.0.1.4310 EBF       17 Jan 2012

Mac OS           12.0.1.3352 EBF       26 May 2011
                 11.0.1.2449 EBF       29 Jun 2010
                 10.0.1.4042 EBF       01 Oct 2010

Solaris SPARC    12.0.1.3523 EBF       15 Dec 2011
                 11.0.1.2745 EBF ***   26 Jan 2012 ***
                 10.0.1.4310 EBF       17 Jan 2012

Solaris x64      12.0.1.3523 EBF       15 Dec 2011
                 11.0.1.2745 EBF ***   24 Jan 2012 ***
                 10.0.1.3870 EBF       01 Apr 2009

Windows x86/x64  12.0.1.3537 EBF       18 Jan 2012
                 11.0.1.2744 EBF ***   26 Jan 2012 ***
                 10.0.1.4310 EBF       12 Jan 2012

[Download EBFs here] [Register for the Fuji Beta here]

...and for the other platforms:

HP-UX PA-RISC    10.0.1.3778 EBF                 16 Oct 2008

Linux Itanium    10.0.1.3777 EBF                 14 Oct 2008

NetWare          10.0.1.4036 EBF                 10 Mar 2010

Windows CE       10.0.1.4036 EBF                 09 Mar 2010

Windows Itanium  10.0.1 GA Upgrade from 10.0.0   26 Apr 2007

[Download EBFs here]

Coding Good Comments: Exhortations

noreply@blogger.com (Breck Carter) — Fri, 27 Jan 2012 08:01:00 +0000

Another kind of comment inside program code is the Exhortation: A warning or reminder for the maintenance programmer when making changes to code in the vicinity.

Exhortations may be the only kind of comment that deserves to be decorated with boxes, ALL-CAPS and/or exclamation! marks because they are the only kind of comment that may be more important than the surrounding program code... by definition all other comments are less important than the program code because

comments aren't compiled,
comments aren't tested,
comments aren't executed, so therefore
comments can easily be (and often are) wrong.

Exhortations are a bit different: they offer blunt warnings, not subtle guidance or explanations. When an exhortation becomes out-of-date, and therefore wrong, it's usually easy to detect and easy to resolve by simply deleting the comment.

When an exhortation is valid, however, it is often valuable to both newcomers (as a warning about a possible pitfall) and experienced programmers (as a reminder about that pitfall).

Example 1

Here's an example from inside a SQL script that contains CREATE TABLE and other definitions for tables that are subject to the "Data Upgrade" process when an old copy of the application database is upgraded to a new version:

-- ***********************************************************************
-- ***** EVERY CHANGE DOCUMENTED HERE MUST ALSO BE DOCUMENTED IN     *****
-- ***** 015c_rroad_data_upgrade_startup.sql EVEN IF IT IS           *****
-- ***** NOT SIGNIFICANT TO THE DATA UPGRADE PROCESS.                *****
-- ***********************************************************************

What it's saying is that every single schema change made in this module must be checked against code in the other module to see if anything has to be done over there as well.

The "MUST ALSO BE DOCUMENTED" part of the exhortation is saying that a record of that checking must be kept in the other module; in this case, the modification history comments are stored in both locations as evidence the change wasn't forgotten.

Example 2

Redundant code is sometimes just easier to deal with than to eliminate with complex logic. Here's an example of an exhortation that warns of the existence of a debugging version of a tble; when the original base table is altered, the debugging version probably needs to be changed as well:

   -- ********************************************************************************
   -- ***** WHEN MAKING CHANGES HERE, SEE ALSO THE TRACE_* VERSION OF THIS TABLE *****
   -- ********************************************************************************

Example 3

Here's an warning about NULL-versus-non-NULL values that was added to the code after several catastrophic mistakes were made; this warning may only benefit newcomers, but it doesn't hurt for experienced developers to be reminded as well:

   -- *****************************************************************************************************
   -- ***** DO NOT CHANGE NULL / NOT NULL CONSTRAINTS WITHOUT CHECKING EVERYWHERE THE VALUES ARE USED *****
   -- *****************************************************************************************************

This exhortation appears twice in the code, and so does the one from Example 2: both at the beginning and end of the associated CREATE TABLE statement to reduce the chances it'll be missed.

Example 4

Here's an example involving the same business rule being implemented by separate UPDATE statements:

         -- ***** CAUTION ****************************************************************************
         -- *****    When changing the UPDATE statement here, also check the similar UPDATE in   *****
         -- *****    204_rroad_monitor_sample.sql.                                               *****
         -- ******************************************************************************************

The business rule's the same, but the syntax and semantics of the UPDATE statements are different, and the logic required to implement a single common UPDATE was deemed not worth the effort, not when the changes are easy to make in two locations and an exhortation takes care of the "oops I missed that" problem.

Example 5

Here's an example of two alternate versions of the same table, only one of which exists at run time but both must be maintained... and most changes must be made to both versions.

Once again, the exhortation appears twice inside each table because they're quite long; the ellipsis '...' represents many columns.

IF '{DEBUG_MESSAGES}' = 'ON' THEN

   -- Include all constraints to facilitate development testing.

   --------------------------------------------------------------------------------------------
   -- CAUTION: If any changes are made here, make the same changes to the other CREATE TABLE. 
   --------------------------------------------------------------------------------------------

   CREATE TABLE rroad_sampling_options (

      sampling_id                          UNSIGNED INTEGER NOT NULL DEFAULT AUTOINCREMENT,

      ...

      --------------------------------------------------------------------------------------------
      -- CAUTION: If any changes are made here, make the same changes to the other CREATE TABLE. 
      --------------------------------------------------------------------------------------------

      PRIMARY KEY ( sampling_id ),
      UNIQUE ( selected_tab, 
               selected_name ),
      CONSTRAINT "NOT ( sampling_should_be_running = Y AND timed_out = Y )" CHECK ( NOT ( sampling_should_be_running = 'Y' AND timed_out = 'Y' ) ),
      CONSTRAINT "last_sample_started_at <= last_sample_finished_at" CHECK ( last_sample_started_at <= last_sample_finished_at ),
      CONSTRAINT "last_canarian_query_started_at <= last_canarian_query_finished_at" CHECK ( last_canarian_query_started_at <= last_canarian_query_finished_at ) );

ELSE

   -- Omit some constraints to improve performance.
   -- Not all constraints are omitted, just the ones that might affect performance.

   --------------------------------------------------------------------------------------------
   -- CAUTION: If any changes are made here, make the same changes to the other CREATE TABLE. 
   --------------------------------------------------------------------------------------------

   CREATE TABLE rroad_sampling_options (

      sampling_id                          UNSIGNED INTEGER NOT NULL DEFAULT AUTOINCREMENT,
      ...

      --------------------------------------------------------------------------------------------
      -- CAUTION: If any changes are made here, make the same changes to the other CREATE TABLE. 
      --------------------------------------------------------------------------------------------

      PRIMARY KEY ( sampling_id ),
      UNIQUE ( selected_tab, 
               selected_name ) );

END IF;

The Bottom Line

Exhortations should be blunt, simple and to the point, and if at all possible they should be positive: They should tell the developer what to do, not what to avoid.

Coding Good Comments: Section Titles

noreply@blogger.com (Breck Carter) — Wed, 25 Jan 2012 08:01:00 +0000

There are many kinds of comments inside program code, one of them being Section Titles: Comments that introduce the reader to a block of code with the primary purpose being to answer the question "Is this the code we're looking for?"

Folks who hate comments might say: "Different sections of code should appear in separate functions or procedures, with the section title becoming the procedure name."

In the real world, however,

not every section becomes a procedure,
section title comments tend to be longer and more descriptive than procedure names, and
applications with extremely high ratios of call-return-interfaces-to-lines-of-worker-code may become hard to understand and follow.

In other words, every real-world program can use section title comments, and this article is about writing good ones.

Example 1

Here's an example of a not-very-good section title; it's not good because it is vague, telling us only that the section involves sample sets rather than some other commonly-used data objects used in the application:

------------------------------------------------------------------------
-- Perform some standard sample set updates as required.

That section title is not good because the section itself violates the rule for high cohesion as the following nested subsection titles show:

   ------------------------------------------------------------------------------------------------------------------
   -- Update 1: Fix rroad_sampling_options.rroad_monitor_sample_loop_connection_number if necessary.

   ------------------------------------------------------------------------------------------------------------------
   -- Update 2: Update the last rroad_sample_set.sample_finished_at if necessary.

Each of the subsection titles is pretty good, but neither has much to do with the other except the fact they both mention sample sets... hence the vague section title. The two subsections appear together because they can share one FOR loop; perhaps this is an example of coincidental cohesion (the worst), perhaps it is communicational cohesion (the second best, when parts of a module are grouped because they operate on the same data), or somewhere in between, but it is certainly not functional cohesion (the best): when parts of a module are grouped because they all contribute to a single well-defined task of the module.

In this case, the vague outer section title is as good as it's going to get without changes to the actual code... but maybe it's not so bad after all: the vagueness itself can be taken as a warning that "coincidental cohesion lurks within".

In other words, the code ain't perfect so neither are the section titles.

Example 2

Here's a section title that's pretty specific, but not exactly helpful:

------------------------------------------------------------------------
-- Loop through each database that is trying to connect past the connection timeout interval.

Instead of describing what the code's doing, it's concentrating on the how: "loop through each database". The first line of code is a FOR loop so nobody needs to know the "how", it's the "what" of the section that should be described in the title.

Here's how the section title was revised:

------------------------------------------------------------------------
-- Stop sampling for connection timeouts.

Much better: "stop sampling" is a big deal in the world of database monitoring, and it's the whole reason this code section exists yet it wasn't even mentioned in the original title.

Example 3

Here's a section title which confuses the "what" and "how" for a long section of code:

------------------------------------------------------------------------------------
-- Process Alert #1: Loop through each database that...
--     should be sampled,
--     or has timed out, 
--     and has not had a recent successful sample.

The word "process" is unforgivably vague since it could mean "issue", "clear" or "cancel", or even "edit", or some combination of all of them; in this case the action is "issue". Also, "Alert #1" is probably appropriate only for someone already familiar with Foxhound, someone who knows that Alert #1 is "Database Unresponsive".

Here's the revision:

------------------------------------------------------------------------------------
-- Issue Alert #1: Database Unresponsive for each database that...
--     should be sampled,
--     or has timed out, 
--     and has not had a recent successful sample.

Example 4

Here is yet another vague "Loop through" section title on a FOR loop exhibiting lower-than-functional cohesion, together with the subsection titles:

------------------------------------------------------------------------
-- Loop through each database that...
--     should be sampled but isn't,  
--     has timed out and should be retried, or
--     still has user_request = 'Stop Sampling' even though sampling has stopped.

   ------------------------------------------------------------------------
   -- Exit immediately if Foxhound is shutting down.

   ------------------------------------------------------------------------
   -- Get the Sample Schedule settings for this moment in time.
   -- This information will be used in later sections.

   ------------------------------------------------------------------------
   -- Start sampling again after sample loop connection number was dropped.

   ------------------------------------------------------------------------
   -- Start sampling for timeout retry.

   ------------------------------------------------------------------------
   -- Check and reset user_request if sampling has already been stopped.

   ------------------------------------------------------------------------
   -- Stop sampling if required to by the sample schedule.

In this case the associated code was changed and enhanced to provide new features, and the section and subsection titles were changed as well.

The biggest change, however, was to the outer section title to make it clear that the subsections exhibit lower-than-functional cohesion:

------------------------------------------------------------------------
-- Start or stop sample sessions as required.

   ------------------------------------------------------------------------
   -- Exit immediately if Foxhound is shutting down.

   ------------------------------------------------------------------------
   -- Get the Sample Schedule settings for this moment in time.
   -- This information will be used in later sections.

   ------------------------------------------------------------------------
   -- Start sampling each database that should be sampled but isn't.

   ------------------------------------------------------------------------
   -- Start sampling if the sample schedule requires it.

   ------------------------------------------------------------------------
   -- Start sampling for timeout retry.

   ------------------------------------------------------------------------
   -- Check and reset user_request and schedule_request if sampling has already been stopped.

   ------------------------------------------------------------------------
   -- Stop sampling if required to by the sample schedule.

Also, the outer section title was stripped of its point-form overview of subsection descriptions: sometimes repetition helps but not here; the subsection titles are all that's needed.

Note that one of the lines above isn't really a section title, it's a warning to the reader:

-- This information will be used in later sections.

The Bottom Line

The Number One Reason, perhaps the only reason for Section Title Comments is to answer the question: "Is this the code we're looking for?"

Latest SQL Anywhere EBF - 12.0.1.3537 for Windows

noreply@blogger.com (Breck Carter) — Mon, 23 Jan 2012 08:01:00 +0000

The three asterisks "***" show what's new since the previous list.

Only EBFs for the latest fully-supported versions of SQL Anywhere are shown here: 10.0.1, 11.0.1 and 12.0.1.
Note: Some earlier versions are also fully supported but they are not shown here; e.g., the 12.0.0.2758 EBF for Windows was recently released. For more information see SQL Anywhere Supported Platforms and Engineering Support Status.
Also: Just because an older version isn't "fully supported" doesn't mean you can't download files (or ask questions, or get help), it just means there won't be any more new EBFs released. For a complete list of everything available for download see Downloads - EBFs / Maintenance.

Current builds for the most active platforms...

HP-UX Itanium    12.0.1.3523 EBF       15 Dec 2011
                 11.0.1.2724 EBF       12 Dec 2011
                 10.0.1.3777 EBF       14 Oct 2008

IBM AIX          12.0.1.3523 EBF       15 Dec 2011
                 11.0.1.2724 EBF       12 Dec 2011

Linux x86/x64    12.0.1.3519 EBF       20 Dec 2011
                 11.0.1.2724 EBF       14 Dec 2011
                 10.0.1.4310 EBF       17 Jan 2012

Mac OS           12.0.1.3352 EBF       26 May 2011
                 11.0.1.2449 EBF       29 Jun 2010
                 10.0.1.4042 EBF       01 Oct 2010

Solaris SPARC    12.0.1.3523 EBF       15 Dec 2011
                 11.0.1.2724 EBF       12 Dec 2011
                 10.0.1.4310 EBF       17 Jan 2012

Solaris x64      12.0.1.3523 EBF       15 Dec 2011
                 11.0.1.2724 EBF       12 Dec 2011
                 10.0.1.3870 EBF       01 Apr 2009

Windows x86/x64  12.0.1.3537 EBF ***   18 Jan 2012 ***
                 11.0.1.2724 EBF       20 Dec 2011
                 10.0.1.4310 EBF       12 Jan 2012

[Download EBFs here] [Register for the Fuji Beta here]

...and for the other platforms:

HP-UX PA-RISC    10.0.1.3778 EBF                 16 Oct 2008

Linux Itanium    10.0.1.3777 EBF                 14 Oct 2008

NetWare          10.0.1.4036 EBF                 10 Mar 2010

Windows CE       10.0.1.4036 EBF                 09 Mar 2010

Windows Itanium  10.0.1 GA Upgrade from 10.0.0   26 Apr 2007

[Download EBFs here]

The fRiDaY File - The Best Kind Of Error

noreply@blogger.com (Breck Carter) — Fri, 20 Jan 2012 20:01:00 +0000

Here's the story: The next version of Foxhound is getting a "Sample Schedule" feature whereby the database monitor sampling process can be turned on and off at different times of the day.

To keep it simple, the user input requires a "Y" for each 15-minute interval that samples are to be gathered, with "not Y" (like a dot) for other intervals.

When it came time to start testing (as it did on Monday), setting the schedule on and off every 15 minutes made it easy to see if the transitions were working:

So far so good... and of course there were bugs, lots of fixes, and testing started again on Wednesday.

But then, NOTHING worked... changing the setting to and from "Y" for the current 15-minute interval had NO EFFECT on the sampling process!

Arrrgh! After all that work, it had gotten WORSE!

Well, actually, no it hadn't...

...because [snork] changing the settings for Monday has no effect on testing done on Wednesday!

User Errors Are The Best Kind!

Latest SQL Anywhere EBFs - 10.0.1.4310 For Linux and SPARC

noreply@blogger.com (Breck Carter) — Fri, 20 Jan 2012 08:01:00 +0000

The three asterisks "***" show what's new since the previous list.

Only EBFs for the latest fully-supported versions of SQL Anywhere are shown here: 10.0.1, 11.0.1 and 12.0.1.
Note: Some earlier versions are also fully supported but they are not shown here; e.g., the 12.0.0.2758 EBF for Windows was recently released. For more information see SQL Anywhere Supported Platforms and Engineering Support Status.
Also: Just because an older version isn't "fully supported" doesn't mean you can't download files (or ask questions, or get help), it just means there won't be any more new EBFs released. For a complete list of everything available for download see Downloads - EBFs / Maintenance.

Current builds for the most active platforms...

HP-UX Itanium    12.0.1.3523 EBF       15 Dec 2011
                 11.0.1.2724 EBF       12 Dec 2011
                 10.0.1.3777 EBF       14 Oct 2008

IBM AIX          12.0.1.3523 EBF       15 Dec 2011
                 11.0.1.2724 EBF       12 Dec 2011

Linux x86/x64    12.0.1.3519 EBF       20 Dec 2011
                 11.0.1.2724 EBF       14 Dec 2011
                 10.0.1.4310 EBF ***   17 Jan 2012 ***

Mac OS           12.0.1.3352 EBF       26 May 2011
                 11.0.1.2449 EBF       29 Jun 2010
                 10.0.1.4042 EBF       01 Oct 2010

Solaris SPARC    12.0.1.3523 EBF       15 Dec 2011
                 11.0.1.2724 EBF       12 Dec 2011
                 10.0.1.4310 EBF ***   17 Jan 2012 ***

Solaris x64      12.0.1.3523 EBF       15 Dec 2011
                 11.0.1.2724 EBF       12 Dec 2011
                 10.0.1.3870 EBF       01 Apr 2009

Windows x86/x64  12.0.1.3519 EBF       20 Dec 2011
                 11.0.1.2724 EBF       20 Dec 2011
                 10.0.1.4310 EBF       12 Jan 2012

[Download EBFs here] [Register for the Fuji Beta here]

...and for the other platforms:

HP-UX PA-RISC    10.0.1.3778 EBF                 16 Oct 2008

Linux Itanium    10.0.1.3777 EBF                 14 Oct 2008

NetWare          10.0.1.4036 EBF                 10 Mar 2010

Windows CE       10.0.1.4036 EBF                 09 Mar 2010

Windows Itanium  10.0.1 GA Upgrade from 10.0.0   26 Apr 2007

[Download EBFs here]

Develop, Test, Release, ...

noreply@blogger.com (Breck Carter) — Wed, 18 Jan 2012 08:01:00 +0000

No amount of slick propaganda can rearrange the basic software development life cycle:

Develop, Test, Release, Repeat

No one can put Release before Develop, not unless they have a time machine. It is possible to put Release before Test but that is usually so unsuccessful that an impartial observer might say the Release never happened.

If the same staff are involved in both Develop and Test (as they often are, and perhaps should be) then a big problem can arise when the cycle reaches Repeat:

It's hard to go back to development when you've been testing for release.

During final testing the code is usually frozen and everyone is risk-averse. The only changes allowed are those required to fix problems, and not all problems are fixed... no one wants to make any changes that might break something else.

After the Big Release Day, it's time to Repeat which means go back to Development. For management, that's easy: it's all just one long schedule to them, just one day later, and there's probably been some slippage which means everyone's already late for the next Release.

For developers, it's not so easy... especially if they just came off the death march called Test. They're still risk-averse, and Development requires risks to be embraced: Concentrate on the enhancements without obsessing about the effect they might have on the rest of the program. They're also tired, and Development requires enthusiasm.

What the developers need, and often do not get, is an extra step to decompress and rejuvenate:

Develop, Test, Release, Relax, Repeat

A word to the wise for management: If developers don't get time to Relax, they'll take it anyway... perhaps not consciously, but through bad morale, lost productivity and extra sick time. And it might cost more in the long run.

Or... you could try rewards, see if that works...

Let's play Gotcha! 2012 Edition, Round 1

noreply@blogger.com (Breck Carter) — Mon, 16 Jan 2012 08:01:00 +0000

Welcome to the first round of the 2012 Edition of "Gotcha!", the self-testing quiz game for SQL Anywhere developers.

Let's get started right away...

You know all about DEFAULT AUTOINCREMENT, right?

Here's a warm-up question:

What does t contain after the following INSERT?
CREATE TABLE t ( auto UNSIGNED BIGINT NOT NULL DEFAULT AUTOINCREMENT );

INSERT t VALUES ( DEFAULT ); 

Answer: It contains 1! (you knew that, right?)

SELECT * FROM t ORDER BY auto;

auto
1

Here's another warm-up question:

SQL Anywhere 12 stores the most recent DEFAULT AUTOINCREMENT value for each column in SYSTABCOL.max_identity.

What is that value for t.auto?

Answer: It's 1 again!

CHECKPOINT;
SELECT max_identity FROM SYSTABCOL WHERE column_name = 'auto';

max_identity
1

Note: The CHECKPOINT is sometimes required to force SQL Anywhere to update SYSTABCOL.max_identity.

The rules for Gotcha!

You will be asked three skill-testing questions involving SQL Anywhere, and you must answer all three correctly to move on to the next round.

Two out of three right answers don't count, you have to get them all.

No computers!

You have to answer all the questions without looking anything up, and without running any actual tests. This is a test of your SQL Anywhere knowledge, not your prowess at Googling the docs.

Here we go...

Question One

What does t1 contain after this code runs?

CREATE TABLE t1 ( t1_auto UNSIGNED BIGINT NOT NULL DEFAULT AUTOINCREMENT );

INSERT t1 VALUES ( 9223372036854775806 );
INSERT t1 VALUES ( DEFAULT );

Listen to this while working on your answer, then scroll down to see if you got it right...

Here's the answer: While 9,223,372,036,854,775,806 is a pretty big number, the UNSIGNED BIGINT column in t1 can hold more than twice as much (18,446,744,073,709,551,614) so t1 looks like this:

SELECT * FROM t1 ORDER BY t1_auto;

t1_auto
9223372036854775806
9223372036854775807

Congratulations!

Question Two

What does this code do?

CREATE TABLE t2 ( t2_auto UNSIGNED BIGINT NOT NULL DEFAULT AUTOINCREMENT );

INSERT t2 VALUES ( 9223372036854775806 );
INSERT t2 VALUES ( DEFAULT );
INSERT t2 VALUES ( DEFAULT );

Listen to this while working on your answer, then scroll down to see if you got it right...

Careful now!

The 2012 Edition of Gotcha! is more difficult than it was last year!

Here are some clues:

Table t2 looks exactly the same as t1, only the names have changed.
The t2_auto column is still UNSIGNED BIGINT, and it will still hold values up to 18,446,744,073,709,551,614, and
that's wayyyyy bigger than 9,223,372,036,854,775,806.

Here's the answer: The third INSERT crashes!

Could not execute statement.
Column 't2_auto' in table 't2' cannot be NULL
SQLCODE=-195, ODBC 3 State="23000"
Line 34, column 1

Whoa!

We haven't even reached the Gotcha! question yet, what's going on? Inserting NULL is what DEFAULT AUTOINCREMENT tries to do when it runs out of numbers:

When the next value to be generated exceeds the maximum value that can be stored in the column to which the AUTOINCREMENT is assigned, NULL is returned. If the column has been declared to not allow NULLs, as is true for primary key columns, a SQL error is generated.

Here's the thing, though: UNSIGNED BIGINT works as expected, and so does the following code...

CREATE TABLE t2 ( t2_auto UNSIGNED BIGINT NOT NULL DEFAULT AUTOINCREMENT );

INSERT t2 VALUES ( 9223372036854775806 );
INSERT t2 VALUES ( DEFAULT );
INSERT t2 VALUES ( 9223372036854775808 );

SELECT * FROM t2 ORDER BY t2_auto;

t2_auto
9223372036854775806
9223372036854775807
9223372036854775808

...so the problem must be with DEFAULT AUTOINCREMENT.

Yes, indeed...

...the underlying cause is that SYSTABCOL.max_identity is defined as BIGINT so it can't hold any value larger than 9,223,372,036,854,775,807. Never mind that DEFAULT AUTOINCREMENT can't be used to generate negative values, signed BIGINT was chosen as the data type.

Never mind that the Help says any old integer will do, which, presumably, includes UNSIGNED BIGINT: "When using AUTOINCREMENT, the column must be one of the integer data types, or an exact numeric type."

Question Three

What does this code do?

CREATE TABLE t3 ( t3_auto UNSIGNED BIGINT NOT NULL DEFAULT AUTOINCREMENT );

INSERT t3 VALUES ( DEFAULT );
INSERT t3 VALUES ( DEFAULT );
INSERT t3 VALUES ( 9223372036854775808 );
INSERT t3 VALUES ( DEFAULT );

OK, maybe you're nervous now, maybe you didn't get Question 2 right, maybe you need a moment to relax...

Question 3 is really asking about what the last INSERT is going to do, the first three work just fine:

CREATE TABLE t3 ( t3_auto UNSIGNED BIGINT NOT NULL DEFAULT AUTOINCREMENT );

INSERT t3 VALUES ( DEFAULT );
INSERT t3 VALUES ( DEFAULT );
INSERT t3 VALUES ( 9223372036854775808 );

SELECT * FROM t3 ORDER BY t3_auto;

t3_auto
1
2
9223372036854775808

So what is the next INSERT t3 VALUES ( DEFAULT ) going to do?

Is it going to insert a row with t3_auto = 3?

Wouldn't that make sense? That's what DEFAULT GLOBAL AUTOINCREMENT does, isn't it? If you explicitly insert a value outside the partition, the next DEFAULT AUTOINCREMENT value is calculated as if the explicit INSERT never happened: "Default column values are not affected by values in the column outside the current partition".

So... what's the answer?

It's going to insert 3, isn't it?

Gotcha!

No, it doesn't insert 3, it crashes just like before:

CREATE TABLE t3 ( t3_auto UNSIGNED BIGINT NOT NULL DEFAULT AUTOINCREMENT );

INSERT t3 VALUES ( DEFAULT );
INSERT t3 VALUES ( DEFAULT );
INSERT t3 VALUES ( 9223372036854775808 );
INSERT t3 VALUES ( DEFAULT );

Could not execute statement.
Column 't3_auto' in table 't3' cannot be NULL
SQLCODE=-195, ODBC 3 State="23000"
Line 36, column 1

The reason is that the third INSERT, the one that explicitly inserts 9223372036854775808, sets SYSTABCOL.max_identity to this value:

SELECT max_identity FROM SYSTABCOL WHERE column_name = 't3_auto';

max_identity
9223372036854775807

That's NOT the last value that was inserted into t3, it can't store that because it wouldn't fit in a BIGINT. No, it stores the maximum possible positive BIGINT value, and that, folks, is the point of this whole game:

DEFAULT AUTOINCREMENT is limited to BIGINTs

Latest SQL Anywhere EBF - 10.0.1.4310 For Windows

noreply@blogger.com (Breck Carter) — Fri, 13 Jan 2012 08:01:00 +0000

The three asterisks "***" show what's new since the previous list.

Only EBFs for the latest fully-supported versions of SQL Anywhere are shown here: 10.0.1, 11.0.1 and 12.0.1.
Note: Some earlier versions are also fully supported but they are not shown here; e.g., the 12.0.0.2758 EBF for Windows was just released. For more information see SQL Anywhere Supported Platforms and Engineering Support Status.
Also: Just because an older version isn't "fully supported" doesn't mean you can't download files (or ask questions, or get help), it just means there won't be any more new EBFs released. For a complete list of everything available for download see Downloads - EBFs / Maintenance.

Current builds for the most active platforms...

HP-UX Itanium    12.0.1.3523 EBF       15 Dec 2011
                 11.0.1.2724 EBF       12 Dec 2011
                 10.0.1.3777 EBF       14 Oct 2008

IBM AIX          12.0.1.3523 EBF       15 Dec 2011
                 11.0.1.2724 EBF       12 Dec 2011

Linux x86/x64    12.0.1.3519 EBF       20 Dec 2011
                 11.0.1.2724 EBF       14 Dec 2011
                 10.0.1.4239 EBF       17 Jun 2011

Mac OS           12.0.1.3352 EBF       26 May 2011
                 11.0.1.2449 EBF       29 Jun 2010
                 10.0.1.4042 EBF       01 Oct 2010

Solaris SPARC    12.0.1.3523 EBF       15 Dec 2011
                 11.0.1.2724 EBF       12 Dec 2011
                 10.0.1.4239 EBF       17 Jun 2011

Solaris x64      12.0.1.3523 EBF       15 Dec 2011
                 11.0.1.2724 EBF       12 Dec 2011
                 10.0.1.3870 EBF       01 Apr 2009

Windows x86/x64  12.0.1.3519 EBF       20 Dec 2011
                 11.0.1.2724 EBF       20 Dec 2011
                 10.0.1.4310 EBF ***   12 Jan 2012 ***

[Download EBFs here] [Register for the Fuji Beta here]

...and for the other platforms:

HP-UX PA-RISC    10.0.1.3778 EBF                 16 Oct 2008

Linux Itanium    10.0.1.3777 EBF                 14 Oct 2008

NetWare          10.0.1.4036 EBF                 10 Mar 2010

Windows CE       10.0.1.4036 EBF                 09 Mar 2010

Windows Itanium  10.0.1 GA Upgrade from 10.0.0   26 Apr 2007

[Download EBFs here]

Coding Guidelines

noreply@blogger.com (Breck Carter) — Wed, 11 Jan 2012 08:01:00 +0000

If you can't get past the paywall to this article

Coding Guidelines: Finding the Art in the Science

Robert Green, Henry Ledgard, Communications of the ACM, December 2011

then try this version:

Professional coding guidelines
Ledgard, H., 2011 Unpublished report, University of Toledo

There's lots of good stuff, most of which I agree with, like making names pronounceable and using vertical alignment.

The comments about comments, however, seem a bit shallow, especially in the CACM article: examples of poorly-written comments in poorly-written code are used to conclude that comments should be avoided. The opposite is true: well-written comments are enormously helpful in explaining the "what" and "why" of a piece of code; no matter how readable the code is it only presents the "how".

Comments have other uses, sometimes historical: documenting as-yet-unsolved bugs, explaining why some unexpected or unusual change was made, even explaining why some change was not made.

To put this information anywhere other than in the code itself is to guarantee future maintenance programmers will never read it.

You Can't Do That In SQL!

noreply@blogger.com (Breck Carter) — Mon, 09 Jan 2012 08:01:00 +0000

Windows 7 Search sucks!

No, seriously...

...the Search feature in Windows 7 really sucks.

At least, it sucks for developers who work with ASCII text files and frequently want to search their code for some fixed string of text, like

Show me all the SQL scripts that contain the string literal 'Sampling Stopped'.

That's how the Windows XP Search used to work: WYTIWILF... what you typed is what it looked for.

So, after several months of increasing frustration, I decided to do something.

But what?

Drink the Unix Kool-Aid and learn to love grep for Windows?
switch from Wordpad to something fancy like Notepad++?
go big with something like Visual Studio for SQL?
or . . . build it using SQL Anywhere?

If you are familiar with this blog, especially if you've read Everything Looks Like a Database, the choice shouldn't come as any surprise:

Door Number Four: Build It Using SQL Anywhere

Here's what the SQL Anywhere solution looks like...

A simple browser display that lets you pick a search string, a file type and a folder:
and then lets you doubleclick on one of the files to open it in your editor of choice:

Here are the SQL Anywhere features that make it possible:

A Directory Access Server to implement a SELECT on the file names in the folder,
the xp_read_file() system procedure to read each file,
the LOCATE() function to search inside each file,
the xp_cmdshell() system procedure to launch the editor on a selected file, and
the CREATE SERVICE statement to run it all from within a browser using SQL Anywhere's built-in HTTP server.

Here's the code:

CREATE TABLE search_spec (
   one_row_only  INTEGER NOT NULL PRIMARY KEY CHECK ( one_row_only = 1 ),
   folder_spec   VARCHAR ( 200 ) NOT NULL,
   file_ext      VARCHAR ( 10 ) NOT NULL,
   search_for    VARCHAR ( 100 ) NOT NULL );

INSERT search_spec VALUES ( 1, '.\\', '.sql', '' );
COMMIT;

CREATE SERVER local_folder CLASS 'DIRECTORY' USING 'ROOT=C:\\;SUBDIRS=0';

CREATE EXTERNLOGIN DBA TO local_folder;

CREATE EXISTING TABLE local_file AT 'local_folder;;;.';

CREATE SERVICE search TYPE 'RAW' AUTHORIZATION OFF USER DBA 
   AS CALL search();

CREATE PROCEDURE search()
RESULT ( html_string LONG VARCHAR )
BEGIN

DECLARE @folder_spec              VARCHAR ( 200 );
DECLARE @file_ext                 VARCHAR ( 10 );
DECLARE @search_for               VARCHAR ( 100 );
DECLARE @action                   VARCHAR ( 10 );
DECLARE @file_name                VARCHAR ( 200 );
DECLARE @variable_name            LONG VARCHAR;
DECLARE @alter_server_statement   LONG VARCHAR;

DECLARE LOCAL TEMPORARY TABLE local_file_match (
   local_file_name    VARCHAR ( 260 ) NOT NULL PRIMARY KEY )
   NOT TRANSACTIONAL;

SELECT folder_spec,
       file_ext,
       search_for
  INTO @folder_spec,
       @file_ext,
       @search_for
  FROM search_spec;

SET @variable_name = NEXT_HTTP_VARIABLE ( NULL );
WHILE @variable_name IS NOT NULL LOOP
   CASE @variable_name 
      WHEN 'folder_spec' THEN SET @folder_spec = HTTP_VARIABLE ( @variable_name );
      WHEN 'file_ext'    THEN SET @file_ext    = HTTP_VARIABLE ( @variable_name );
      WHEN 'search_for'  THEN SET @search_for  = HTTP_VARIABLE ( @variable_name );
      WHEN 'action'      THEN SET @action      = HTTP_VARIABLE ( @variable_name );
      WHEN 'file_name'   THEN SET @file_name   = HTTP_VARIABLE ( @variable_name );
   END CASE;
   SET @variable_name = NEXT_HTTP_VARIABLE ( @variable_name );
END LOOP;

SET @folder_spec = TRIM ( @folder_spec );
SET @file_ext    = TRIM ( @file_ext );
SET @search_for  = TRIM ( @search_for );

IF RIGHT ( @folder_spec, 1 ) <> '\\' THEN
   SET @folder_spec = STRING ( @folder_spec, '\\' );
END IF;

IF  LENGTH ( @file_ext )  >= 1
AND LEFT ( @file_ext, 1 ) <> '.' THEN
   SET @file_ext = STRING ( '.', @file_ext );
END IF;

SET @action = COALESCE ( @action, 'Search' );

CASE @action

   WHEN 'Search' THEN

      UPDATE search_spec
         SET folder_spec = @folder_spec,
             file_ext    = @file_ext,
             search_for  = @search_for;

      COMMIT;

      SET @alter_server_statement = STRING ( 
         'ALTER SERVER local_folder USING ''ROOT=',
         @folder_spec,
         ';SUBDIRS=0''' );

      EXECUTE IMMEDIATE @alter_server_statement;

   WHEN 'Open' THEN

      CALL xp_cmdshell ( STRING ( 
                            'start "C:\\Program Files (x86)\\Windows NT\\Accessories\\wordpadx.exe" "',
                            @folder_spec, 
                            @file_name,
                            '"' ),
                         'no_output' );

END CASE;

FOR f_fetch AS c_fetch INSENSITIVE CURSOR FOR
SELECT local_file.file_name AS @local_file_name
  FROM local_file 
 WHERE local_file.file_name LIKE STRING ( '%', @file_ext )  
 ORDER BY local_file.file_name 
FOR READ ONLY
DO
   IF TRIM ( @search_for ) = '' THEN
      INSERT local_file_match VALUES ( @local_file_name );
   ELSE
      IF LOCATE ( xp_read_file ( STRING ( @folder_spec, @local_file_name ) ), @search_for ) > 0 THEN
         INSERT local_file_match VALUES ( @local_file_name );
      END IF;
   END IF;
END FOR;

CALL dbo.sa_set_http_header( 'Content-Type', 'text/html' );

SELECT STRING (
   '<HTML><HEAD>\x0d\x0a',
   '<TITLE>Search</TITLE>\x0d\x0a',
   '<META HTTP-EQUIV="Pragma" CONTENT="no-cache">\x0d\x0a',
   '<script Language="JavaScript">\x0d\x0a',
   'function searchF() {\x0d\x0a',
      'document.f.action = "search?action=Search";\x0d\x0a',
      'document.f.submit();\x0d\x0a',
      'return false; }\x0d\x0a',
   'function openF ( file_name ) {\x0d\x0a',
      'document.f.action = "search?action=Open&file_name=" + file_name;\x0d\x0a',
      'document.f.submit();\x0d\x0a',
      'return false; }\x0d\x0a',
   '</script>\x0d\x0a',
   '<STYLE> P { font-family: COURIER NEW; font-size: 8pt; margin-bottom: 0; margin-top: 0; } </STYLE>\x0d\x0a',
   '</HEAD>\x0d\x0a',
   '<BODY BGCOLOR=#FFFFFF>\x0d\x0a',

   '<FORM NAME="f" METHOD="POST"><P>\x0d\x0a',

   'Search for this exact string: <INPUT TYPE="TEXT" NAME="search_for" VALUE="',
   @search_for,
   '" SIZE=40 STYLE="font-family: COURIER NEW;"><P> \x0d\x0a',

   '...in all files with this extension: <INPUT TYPE="TEXT" NAME="file_ext" VALUE="',
   @file_ext,
   '" SIZE=10 STYLE="font-family: COURIER NEW;"><P> \x0d\x0a',

   '...in this folder: <INPUT TYPE="TEXT" NAME="folder_spec" VALUE="',
   @folder_spec,
   '" SIZE=50 STYLE="font-family: COURIER NEW;"><P>\x0d\x0a',

   '<INPUT TYPE="SUBMIT" ONCLICK="searchF();" VALUE="Search"><P>\x0d\x0a',

   LIST ( STRING (
             '<P ONDBLCLICK="openF ( ''',
             local_file_match.local_file_name,
             ''' );"> ',
             local_file_match.local_file_name,
             '\x0d\x0a' ),
          ''
          ORDER BY local_file_match.local_file_name ), 

   '</FORM></BODY></HTML> ' )
   FROM local_file_match; 

END;

Here are some notes on the code:

Lines 1 through 8: The search_spec table is the program's memory, allowing it to pass the most recent search parameters from session to session.
10: The CREATE SERVER statement creates the local_folder Directory Access Server which initially points at the root folder C:\ (but will be changed later via ALTER SERVER).
12: For security purposes, the Directory Access Server feature requires an EXTERNLOGIN to be created for each SQL user id that will be using it.
14: The local_file proxy table allows the search procedure to SELECT file information from the local_folder Directory Access Server.
16 and 17: The CREATE SERVICE statement names the search procedure as being responsible for responding to browser requests with raw HTTP.
43 through 53: This loop analyzes the HTTP parameters passed from the browser and stores the values in the appropriate local variables. You can also pass named parameters via the CREATE SERVICE and CREATE PROCEDURE statments, but in the long run it's easier to maintain a simple NEXT_HTTP_VARIABLE() loop like this one.
55 through 68: This code performs some cleanup and editing of the input paramenters.
70 through 97: This CASE statement processes the two possible actions: 'Search' when the Search button is pressed, and 'Open' when a file name is double-clicked.
81 through 86: The ALTER SERVER statement changes the 'ROOT=' option to point to the input folder.
90 through 95: The xp_cmdshell system procedure call opens the selected file using "Wordpad Classic". This exact code probably won't work for you unless you have installed the original Windows XP Wordpad utility as "wordpadx.exe" in this particular location. However, it will work if you change this line
```
'start "C:\\Program Files (x86)\\Windows NT\\Accessories\\wordpadx.exe" "',
```
to this
```
'notepad "',
```
99 through 113: This FOR loop stores the matching file names in the local temporary table local_file_match.
102: The LIKE predicate looks for the @file_ext string in the file names.
109: The LOCATE() function and xp_read_file system procedure look for the @search_for string inside the files themselves.
115: The sa_set_http_header system procedure call tells the browser that HTML text is coming next.
117 through 161: The SELECT returns a single HTML string to the browser. The \x0d\x0a characters are all unnecessary; they have been included here to make the HTML text more readable.
122 through 126: The JavaScript function searchF() and openF() allow the "Search" and "Open" actions to be customized. This may not be required in the case of searchF(), but with openF() the file_name parameter depends on where openF() was called from.
137 through 147: These INPUT TEXT tags define the three HTTP input parameters search_for, file_ext and folder_spec.
149: This INPUT SUBMIT tag calls searchF() to pass the action HTTP input parameter to the search service.
151 through 158: The SQL LIST() and STRING() functions builds a series of active, double-clickable P paragraph tags to display all the matching file names. Each ONDBLCLICK attribute calls openF() to pass the action and file_name HTTP input parameters to the search service.

Here are the Windows commands to create the SQL Anywhere database, start it with the HTTP server running on port 12345, compile the code using dbisql and then launch it in your browser:

"%SQLANY12%\bin32\dbinit.exe" ddd.db

"%SQLANY12%\bin32\dbspawn.exe" -f "%SQLANY12%\bin32\dbeng12.exe" -xs http(port=12345) ddd.db 

"%SQLANY12%\bin32\dbisql.com" -c "ENG=ddd;DBN=ddd;UID=dba;PWD=sql" READ ENCODING Cp1252 search.sql

START "" "http://localhost:12345/search"

Here's what the HTML looks like:

<HTML><HEAD>
<TITLE>Search</TITLE>
<META HTTP-EQUIV="Pragma" CONTENT="no-cache">
<script Language="JavaScript">
function searchF() {
document.f.action = "search?action=Search";
document.f.submit();
return false; }
function openF ( file_name ) {
document.f.action = "search?action=Open&file_name=" + file_name;
document.f.submit();
return false; }
</script>
<STYLE> P { font-family: COURIER NEW; font-size: 8pt; margin-bottom: 0; margin-top: 0; } </STYLE>
</HEAD>
<BODY BGCOLOR=#FFFFFF>
<FORM NAME="f" METHOD="POST"><P>
Search for this exact string: <INPUT TYPE="TEXT" NAME="search_for" VALUE="'Sampling Stopped'" SIZE=40 STYLE="font-family: COURIER NEW;"><P> 
...in all files with this extension: <INPUT TYPE="TEXT" NAME="file_ext" VALUE=".sql" SIZE=10 STYLE="font-family: COURIER NEW;"><P> 
...in this folder: <INPUT TYPE="TEXT" NAME="folder_spec" VALUE="C:\projects\foxhound\" SIZE=50 STYLE="font-family: COURIER NEW;"><P>
<INPUT TYPE="SUBMIT" ONCLICK="searchF();" VALUE="Search"><P>
<P ONDBLCLICK="openF ( '015b_rroad_other_startup.sql' );"> 015b_rroad_other_startup.sql
<P ONDBLCLICK="openF ( '019_rroad_setup_help.sql' );"> 019_rroad_setup_help.sql
<P ONDBLCLICK="openF ( '023_rroad_start_sampling.sql' );"> 023_rroad_start_sampling.sql
<P ONDBLCLICK="openF ( '203_rroad_monitor_sample_loop.sql' );"> 203_rroad_monitor_sample_loop.sql
</FORM></BODY></HTML>

It Could Be Simpler

Here's what the proxy table local_file looks like:

CREATE EXISTING TABLE DBA.local_file ( 
   permissions                          VARCHAR ( 10 ) NOT NULL,
   size                                 BIGINT NOT NULL,
   access_date_time                     TIMESTAMP NOT NULL,
   modified_date_time                   TIMESTAMP NOT NULL,
   create_date_time                     TIMESTAMP NOT NULL,
   owner                                VARCHAR ( 20 ) NOT NULL,
   file_name                            VARCHAR ( 260 ) NOT NULL,
   contents                             LONG BINARY NOT NULL 
) AT 'local_folder;;;.';

Not only does local_file contain the file_name, it contains the actual file contents as well!

That means instead of calling xp_read_file, the FOR loop on line 99 could simply SELECT the local_file.contents column and use that to LOCATE the search string.

Happy New Year!

noreply@blogger.com (Breck Carter) — Wed, 04 Jan 2012 08:01:00 +0000

Here are my resolutions for 2012:

10. Don't Buy A BlackBerry PlayBook

Every New Year's listicle should include at least one resolution that is possible to keep, this one is mine.

9. Make Time For Tea Time

...IPA is a tea, right?

8. Watch More TV

David Milch did Deadwood, what could go wrong with "Luck"?

7. Cut Back On The Carbs

Prime rib, or ribeye?
Yes, please!

6. Explore The World!

...starting in Scotland.

5. Let Sleeping Dogs Lie

Eat, sleep: what better role model than a dog?

4. Eat More Fruit

Cabernet Sauvignon, Merlot, and so forth.

3. Spend More Time Floating

Some folks jump in, swim around and then get out, thus missing the point of "pool" entirely.

2. Follow V8 Supercars

Like NASCAR but with actual competition.

...and last but not least:

1. Post More Examples Here

Latest SQL Anywhere EBFs - Santa Claus Edition

noreply@blogger.com (Breck Carter) — Mon, 26 Dec 2011 14:16:00 +0000

The three asterisks "***" show what's new since the previous list.

Only EBFs for the latest fully-supported versions of SQL Anywhere are shown here: 10.0.1, 11.0.1 and 12.0.1.
Note: Some earlier versions are also fully supported but they are not shown here; e.g., the 12.0.0.2758 EBF for Windows was just released. For more information see SQL Anywhere Supported Platforms and Engineering Support Status.
Also: Just because an older version isn't "fully supported" doesn't mean you can't download files (or ask questions, or get help), it just means there won't be any more new EBFs released. For a complete list of everything available for download see Downloads - EBFs / Maintenance.

Current builds for the most active platforms...

HP-UX Itanium    12.0.1.3523 EBF       15 Dec 2011
                 11.0.1.2724 EBF       12 Dec 2011
                 10.0.1.3777 EBF       14 Oct 2008

IBM AIX          12.0.1.3523 EBF       15 Dec 2011
                 11.0.1.2724 EBF       12 Dec 2011

Linux x86/x64    12.0.1.3519 EBF ***   20 Dec 2011 ***
                 11.0.1.2724 EBF       14 Dec 2011
                 10.0.1.4239 EBF       17 Jun 2011

Mac OS           12.0.1.3352 EBF       26 May 2011
                 11.0.1.2449 EBF       29 Jun 2010
                 10.0.1.4042 EBF       01 Oct 2010

Solaris SPARC    12.0.1.3523 EBF       15 Dec 2011
                 11.0.1.2724 EBF       12 Dec 2011
                 10.0.1.4239 EBF       17 Jun 2011

Solaris x64      12.0.1.3523 EBF       15 Dec 2011
                 11.0.1.2724 EBF       12 Dec 2011
                 10.0.1.3870 EBF       01 Apr 2009

Windows x86/x64  12.0.1.3519 EBF       20 Dec 2011 ***
                 11.0.1.2724 EBF       20 Dec 2011 ***

[Download EBFs here] [Register for the Fuji Beta here]

...and for the other platforms:

HP-UX PA-RISC    10.0.1.3778 EBF                 16 Oct 2008

Linux Itanium    10.0.1.3777 EBF                 14 Oct 2008

NetWare          10.0.1.4036 EBF                 10 Mar 2010

Windows CE       10.0.1.4036 EBF                 09 Mar 2010

Windows Itanium  10.0.1 GA Upgrade from 10.0.0   26 Apr 2007

[Download EBFs here]

Product Suggestion: User-Defined ERRORMSG()

noreply@blogger.com (Breck Carter) — Fri, 23 Dec 2011 08:01:00 +0000

Exception handling in SQL Anywhere procedures, triggers and events is quite powerful, of that there is no question.

When it comes to user-defined exceptions, however...

...not so much

Here's a demonstration:

Procedure p1 calls procedure p2.
Procedure p1 is called by an application (in this case ISQL).
When p2 executes it uses the SIGNAL statement to raise a user-defined exception.
That exception is captured by the EXCEPTION block in p2.
The exception block in p2 then
- displays the SQLCODE, SQLSTATE and ERRORMSG() on the database console log, and then
- uses the RESIGNAL statement to pass the exception up to procedure p1.
When the exception reaches p1 it is captured by the EXCEPTION block in p1.
The exception block in p1 then
- displays another message on the database console log and
- once again uses the RESIGNAL statement to pass the exception up to the application.
When the exception reaches the application (ISQL) a dialog box is displayed.

Here's the code:

CREATE PROCEDURE p1()
BEGIN
   DECLARE @sqlcode   INTEGER;
   DECLARE @sqlstate  VARCHAR ( 5 );
   DECLARE @errormsg  VARCHAR ( 32767 );

   CALL p2();

EXCEPTION

   WHEN OTHERS THEN

      SELECT SQLCODE, SQLSTATE, ERRORMSG() 
        INTO @sqlcode, @sqlstate, @errormsg;

      MESSAGE STRING ( 
         'EXCEPTION detected in p1() at ', 
         CURRENT TIMESTAMP, 
         ': SQLCODE = ', @sqlcode,  
         ', SQLSTATE = ', @sqlstate,  
         ', ERRORMSG() = ', @errormsg ) 
         TO CONSOLE;

      RESIGNAL;

END;

CREATE PROCEDURE p2()
BEGIN
   DECLARE @sqlcode   INTEGER;
   DECLARE @sqlstate  VARCHAR ( 5 );
   DECLARE @errormsg  VARCHAR ( 32767 );

   BEGIN
      DECLARE @exception EXCEPTION FOR SQLSTATE '99001';
      SIGNAL @exception;
   END;

EXCEPTION
   WHEN OTHERS THEN

      SELECT SQLCODE, SQLSTATE, ERRORMSG() 
        INTO @sqlcode, @sqlstate, @errormsg;

      MESSAGE STRING ( 
         'EXCEPTION detected in p2() at ', 
         CURRENT TIMESTAMP, 
         ': SQLCODE = ', @sqlcode,  
         ', SQLSTATE = ', @sqlstate,  
         ', ERRORMSG() = ', @errormsg ) 
         TO CONSOLE;

      RESIGNAL;

END;

CALL p1(); -- call from ISQL

Here's what appears in the database console log, followed by the dialog box:

EXCEPTION detected in p2() at 2011-12-22 15:16:54.381: SQLCODE = -297, SQLSTATE = 99001, ERRORMSG() = User-defined exception signaled
EXCEPTION detected in p1() at 2011-12-22 15:16:54.382: SQLCODE = -297, SQLSTATE = 99001, ERRORMSG() = User-defined exception signaled

That's all well and good, but the problem is this: only the SQLSTATE is user-defined. The SQLCODE and ERRORMSG() values are both fixed and pre-defined, and they mean exactly the same thing:

SQLCODE = -297 User-defined exception signaled
ERRORMSG() = User-defined exception signaled

Hey! We know that already!

In order to be truly useful, user-defined exceptions need some mechanism for providing a user-defined error message as well as a SQLSTATE code.

Even that ~~pathetic~~ limited Transact SQL RAISERROR statement has a mechanism for providing a user-defined message as well as an error code.

Let's play "Gotcha!" - the ACID Edition

noreply@blogger.com (Breck Carter) — Wed, 21 Dec 2011 08:01:00 +0000

Welcome to the ACID Edition of "Gotcha!", the self-testing quiz game for SQL Anywhere developers.

Let's get started right away...

Are your transactions atomic?

Are they consistent? isolated? durable?

Are they reentrant?

Are you sure?

Let's say you have created a SQL Anywhere web service to update your database, and this web service is launched when the user clicks on an INPUT TYPE="BUTTON" on a web page.

Let's also say you've designed the code carefully so all the database updates are performed within a single database transaction, with a single COMMIT after the updates are complete.

Furthermore, let's say you have carefully inspected the code, and you are sure of the following facts:

Each time your web service is executed, it either runs on a brand-new database connection or a pool connection that has been freshly sanitized (because that's how SQL Anywhere web services work),
if the web service finishes normally, by definition the COMMIT will have been executed, which means all the updates performed during the database transaction will have been successfully written to the database, and
if the web service terminates abnormally before reaching the COMMIT, SQL Anywhere will roll back all the updates performed during the database transaction (because that's what SQL Anywhere does when a connection terminates without doing a COMMIT).

Here's a simple demonstration that's just one step up from "Hello World":

Here's what the service does when it is invoked from the browser:

It deletes all 100 rows from a table called t, then
it re-inserts all 100 rows and finally
it displays them as ordered pairs inside parentheses.

Here's the code: One table, two procedures and one service; remember, this is a demonstration, so don't expect anything useful:

CREATE TABLE t (
   pkey   INTEGER NOT NULL PRIMARY KEY,
   data   INTEGER NOT NULL );

INSERT t SELECT row_num, row_num FROM sa_rowgenerator ( 1, 100 );
COMMIT;

CREATE PROCEDURE p()
BEGIN

DECLARE @sqlcode   INTEGER;
DECLARE @sqlstate  VARCHAR ( 5 );
DECLARE @errormsg  VARCHAR ( 32767 );

DELETE t;

WAITFOR DELAY '00:00:00.001';

INSERT t SELECT row_num, row_num FROM sa_rowgenerator ( 1, 100 );

WAITFOR DELAY '00:00:00.001';

COMMIT;

EXCEPTION
   WHEN OTHERS THEN
      SELECT SQLCODE, SQLSTATE, ERRORMSG() 
        INTO @sqlcode, @sqlstate, @errormsg;
      MESSAGE STRING ( 
         'DIAG ', @@SPID, ' ', CURRENT TIMESTAMP, ' EXCEPTION in p: SQLCODE = ', @sqlcode,  
         ', SQLSTATE = ', @sqlstate,  
         ', ERRORMSG() = ', @errormsg ) 
         TO CONSOLE;
      RESIGNAL;

END;

CREATE SERVICE s 
   TYPE 'RAW' AUTHORIZATION OFF USER DBA
   AS CALL s();

CREATE PROCEDURE s()
RESULT ( html_string LONG VARCHAR )
BEGIN
DECLARE @sqlcode   INTEGER;
DECLARE @sqlstate  VARCHAR ( 5 );
DECLARE @errormsg  VARCHAR ( 32767 );

MESSAGE STRING ( 'DIAG ', @@SPID, ' ', CURRENT TIMESTAMP, ' s started...' ) TO CONSOLE;

CALL p();

CALL dbo.sa_set_http_header( 'Content-Type', 'text/html' );

WAITFOR DELAY '00:00:00.001';

SELECT STRING (
   ' ',
   ' ',
   'Service s ',
   ' ',
   ' ',
   '',
   LIST ( STRING ( '( ', t.pkey, ', ', t.data, ' )' ), ', ' ORDER BY t.pkey ),
   '',
   ' ',
   '',
   ' ',
   ' ' )
FROM t;

EXCEPTION
   WHEN OTHERS THEN
      SELECT SQLCODE, SQLSTATE, ERRORMSG() 
        INTO @sqlcode, @sqlstate, @errormsg;
      MESSAGE STRING ( 
         'DIAG ', @@SPID, ' ', CURRENT TIMESTAMP, ' EXCEPTION in s: SQLCODE = ', @sqlcode,  
         ', SQLSTATE = ', @sqlstate,  
         ', ERRORMSG() = ', @errormsg ) 
         TO CONSOLE;

      ROLLBACK;

      RESIGNAL;

END;

As you can see, the code also explicitly captures any EXCEPTION raised in the code:

The outer EXCEPTION handler in procedure s displays the connection number for the service
together with the current connection number (@@SPID) and the SQLCODE, and then
it does a ROLLBACK and RESIGNAL before the service terminates.

The inner EXCEPTION handler in procedure p does the same thing except no ROLLBACK.

So far, so good

Every time you click on the "Save" button, it does the same thing and displays the same output:

What happens if you start pounding on the Save button, and the service gets cancelled and restarted before it can finish?

That's not a problem, right? The first execution of the service gets rolled back if it hasn't done the COMMIT yet, and the second service runs... unless it gets cancelled too.

But that's not a problem either, right?

Here's the skill-testing question:

What do you see in the browser window if your really start pounding on the Save button as fast as you can?

Don't rush, take your time...

Your code's been reviewed, it's ACID, reentrant, all those good things?

Right?

Shouldn't you see this no matter what?

Are you sure? Take your time...

Gotcha!

If you keep pounding on the Save button, eventually you will see this in the browser:

Here are all the diagnostic messages from the SQL Anywhere database server console:

DIAG 365 2011-12-19 10:05:00.924 s started...
DIAG 365 2011-12-19 10:05:01.093 EXCEPTION in p: SQLCODE = -299, SQLSTATE = 57014, ERRORMSG() = Statement interrupted by user
DIAG 366 2011-12-19 10:05:01.102 s started...
DIAG 365 2011-12-19 10:05:01.102 EXCEPTION in s: SQLCODE = -299, SQLSTATE = 57014, ERRORMSG() = Statement interrupted by user
DIAG 366 2011-12-19 10:05:01.208 EXCEPTION in p: SQLCODE = -193, SQLSTATE = 23W01, ERRORMSG() = Primary key for table 't' is not unique : Primary key value ('1')
DIAG 367 2011-12-19 10:05:01.253 s started...
DIAG 366 2011-12-19 10:05:01.256 EXCEPTION in s: SQLCODE = -193, SQLSTATE = 23W01, ERRORMSG() = Primary key for table 't' is not unique : Primary key value ('1')
DIAG 367 2011-12-19 10:05:01.419 EXCEPTION in p: SQLCODE = -299, SQLSTATE = 57014, ERRORMSG() = Statement interrupted by user
DIAG 368 2011-12-19 10:05:01.425 s started...
DIAG 367 2011-12-19 10:05:01.429 EXCEPTION in s: SQLCODE = -299, SQLSTATE = 57014, ERRORMSG() = Statement interrupted by user
DIAG 368 2011-12-19 10:05:01.539 EXCEPTION in p: SQLCODE = -193, SQLSTATE = 23W01, ERRORMSG() = Primary key for table 't' is not unique : Primary key value ('1')
DIAG 368 2011-12-19 10:05:01.547 EXCEPTION in s: SQLCODE = -193, SQLSTATE = 23W01, ERRORMSG() = Primary key for table 't' is not unique : Primary key value ('1')
DIAG 369 2011-12-19 10:05:01.589 s started...
DIAG 369 2011-12-19 10:05:01.740 EXCEPTION in p: SQLCODE = -299, SQLSTATE = 57014, ERRORMSG() = Statement interrupted by user
DIAG 370 2011-12-19 10:05:01.746 s started...
DIAG 369 2011-12-19 10:05:01.750 EXCEPTION in s: SQLCODE = -299, SQLSTATE = 57014, ERRORMSG() = Statement interrupted by user
DIAG 370 2011-12-19 10:05:01.864 EXCEPTION in p: SQLCODE = -193, SQLSTATE = 23W01, ERRORMSG() = Primary key for table 't' is not unique : Primary key value ('1')
DIAG 370 2011-12-19 10:05:01.872 EXCEPTION in s: SQLCODE = -193, SQLSTATE = 23W01, ERRORMSG() = Primary key for table 't' is not unique : Primary key value ('1')

Some of the exceptions are expected: SQLCODE = -299 happens when the service is cancelled by the browser. The second column (365, 366, etc) shows the connection number so you can see how the executions overlap.

The other exceptions are not expected: the SQLCODE = -193 primary key violations mean the DELETE / INSERT logic doesn't work properly when the Save button is given a vigorous workout.

Here's another interesting observation: the "SQL error" doesn't reach the browser until after the primary key exception has been raised several times. How do we know that? Because once the browser displays "SQL error" there's no Save button any more, and that clearly wasn't the case: the repeated "s started..." messages prove there was a Save button on screen long after the first primary key exception.

Locking, blocking, the atomic nature of transactions and the reentrant nature of SQL Anywhere executable code... none of that helps this application avoid a showstopper exception.

For extra points, explain why it happens and how to fix it.

Latest SQL Anywhere EBFs: 11.0.1.2724 and 12.0.1.3523 (revised)

noreply@blogger.com (Breck Carter) — Mon, 19 Dec 2011 08:01:00 +0000

The three asterisks "***" show what's new since the previous list.

Only EBFs for the latest fully-supported versions of SQL Anywhere are shown here: 10.0.1, 11.0.1 and 12.0.1.
Note: Some earlier versions are also fully supported but they are not shown here; e.g., the 12.0.0.2758 EBF for Windows was just released. For more information see SQL Anywhere Supported Platforms and Engineering Support Status.
Also: Just because an older version isn't "fully supported" doesn't mean you can't download files (or ask questions, or get help), it just means there won't be any more new EBFs released. For a complete list of everything available for download see Downloads - EBFs / Maintenance.

Current builds for the most active platforms...

HP-UX Itanium    12.0.1.3523 EBF ***   15 Dec 2011 ***
                 11.0.1.2724 EBF       12 Dec 2011
                 10.0.1.3777 EBF       14 Oct 2008

IBM AIX          12.0.1.3523 EBF ***   15 Dec 2011 ***
                 11.0.1.2724 EBF       12 Dec 2011

Linux x86/x64    12.0.1.3457 EBF       12 Oct 2011
                 11.0.1.2724 EBF ***   14 Dec 2011 ***
                 10.0.1.4239 EBF       17 Jun 2011

Mac OS           12.0.1.3352 EBF       26 May 2011
                 11.0.1.2449 EBF       29 Jun 2010
                 10.0.1.4042 EBF       01 Oct 2010

Solaris SPARC    12.0.1.3523 EBF ***   15 Dec 2011 ***
                 11.0.1.2724 EBF       12 Dec 2011
                 10.0.1.4239 EBF       17 Jun 2011

Solaris x64      12.0.1.3523 EBF ***   15 Dec 2011 ***
                 11.0.1.2724 EBF       12 Dec 2011
                 10.0.1.3870 EBF       01 Apr 2009

Windows x86/x64  12.0.1.3505 EBF       24 Nov 2011
                 11.0.1.2713 EBF       24 Nov 2011
                 10.0.1.4295 EBF       18 Nov 2011

[Download EBFs here] [Register for the Fuji Beta here]

...and for the other platforms:

HP-UX PA-RISC    10.0.1.3778 EBF                 16 Oct 2008

Linux Itanium    10.0.1.3777 EBF                 14 Oct 2008

NetWare          10.0.1.4036 EBF                 10 Mar 2010

Windows CE       10.0.1.4036 EBF                 09 Mar 2010

Windows Itanium  10.0.1 GA Upgrade from 10.0.0   26 Apr 2007

[Download EBFs here]

Another tool for the box

noreply@blogger.com (Breck Carter) — Fri, 16 Dec 2011 08:01:00 +0000

Here's another simple question:

Question: How do I select the top 10 rows in each partition of a table?"

Will CROSS APPLY work as well as it did before?

Here's the data. The table looks pretty much the same, except that the entry_id is autoincrementing instead of repeating and there's a lot more data; three partitions with 100 rows in each partition:

CREATE TABLE t ( 
   partition_id     INTEGER NOT NULL,
   entry_id         INTEGER NOT NULL DEFAULT AUTOINCREMENT,
   data             VARCHAR ( 10 ) NOT NULL,
   PRIMARY KEY ( entry_id ) );

BEGIN
   DECLARE @loop_counter INTEGER;
   SET @loop_counter = 1;
   WHILE @loop_counter <= 100 LOOP
      INSERT t ( partition_id, data ) VALUES ( 10, 'aaa' );
      INSERT t ( partition_id, data ) VALUES ( 20, 'bbb' );
      INSERT t ( partition_id, data ) VALUES ( 30, 'ccc' );
      SET @loop_counter = @loop_counter + 1;
   END LOOP;
   COMMIT;
END;

SELECT * FROM t ORDER BY entry_id;

partition_id,entry_id,data
10,1,'aaa'
20,2,'bbb'
30,3,'ccc'
10,4,'aaa'
20,5,'bbb'
30,6,'ccc'
10,7,'aaa'
20,8,'bbb'
30,9,'ccc'
...
partition_id,entry_id,data
10,298,'aaa'
20,299,'bbb'
30,300,'ccc'

Before tackling the "top 10" question, let's confirm that the CROSS APPLY answer to the previous question "How do I select the first row in each partition of a table?" works just as well on this table:

SELECT first_t.*
  FROM ( SELECT DISTINCT
                t.partition_id 
           FROM t
          ORDER BY t.partition_id ) AS unique_partition
  CROSS APPLY
       ( SELECT FIRST * 
           FROM t
          WHERE t.partition_id = unique_partition.partition_id
          ORDER BY t.entry_id ) AS first_t 
 ORDER BY first_t.partition_id;

Yes, it works:

partition_id,entry_id,data
10,1,'aaa'
20,2,'bbb'
30,3,'ccc'

Plus, it is clear from the SQL what its purpose is:

When the "( SELECT DISTINCT ... )" query is translated into English you get "select each distinct partition_id,"
when the "( SELECT FIRST ... )" query is translated into English you get "select the first row by entry_id for each row in the other result set," and then
when the combined CROSS APPLY query is translated by combining the other two translations, you get "select the first row by entry_id for each distinct partition_id"

which is pretty much a restatement of the original question: "How do I select the first row in each partition?"

It's not perfect; in a perfect world, every single query problem would be solved by a single SQL SELECT statement, not a combination of two queries. But, this is real life... and the original question did refer to two queries: "first row" and "each partition", and the CROSS APPLY contains exactly those two queries...

Not like the Old School solution which does funky things with MIN and GROUP BY, neither of which are even indirectly mentioned in the original question,

SELECT t.* 
  FROM t INNER JOIN ( SELECT partition_id     AS partition_id,
                             MIN ( entry_id ) AS min_entry_id
                        FROM t
                       GROUP BY partition_id
                    ) AS first_row
          ON t.partition_id = first_row.partition_id
         AND t.entry_id     = first_row.min_entry_id
 ORDER BY t.partition_id;

or the OLAP solution which introduced the OVER, WINDOW, PARTITION BY and nested ORDER BY clauses plus the RANK function and a WHERE clause to deal with the RANK result.

SELECT partition_id,
       entry_id,
       data 
  FROM ( select partition_id, 
                entry_id, 
                data,
                rank ( ) OVER partition_id_window AS entry_rank
           FROM t
         WINDOW partition_id_window AS (
                   PARTITION BY partition_id
                   ORDER BY entry_id
                ) ) DT
 WHERE entry_rank = 1
 ORDER BY partition_id;

Go ahead, translate either of those queries into English, see what you get!

Back on topic...

Question: How do I select the top 10 rows in each partition of a table?"

It turns out the CROSS APPLY solution scales just as well as the OLAP solution. With the OLAP solution, the "WHERE entry_rank = 1" predicate was simply changed to "entry_rank <= 10", and with the CROSS APPLY the "FIRST" simply needs to be changed to "TOP 10".

Once again, it works, and once again, the SQL matches the question:

SELECT first_t.*
  FROM ( SELECT DISTINCT
                t.partition_id 
           FROM t
          ORDER BY t.partition_id ) AS unique_partition
  CROSS APPLY
       ( SELECT TOP 10 * 
           FROM t
          WHERE t.partition_id = unique_partition.partition_id
          ORDER BY t.entry_id ) AS first_t 
 ORDER BY first_t.partition_id;

partition_id,entry_id,data
10,1,'aaa'
10,4,'aaa'
10,7,'aaa'
10,10,'aaa'
10,13,'aaa'
10,16,'aaa'
10,19,'aaa'
10,22,'aaa'
10,25,'aaa'
10,28,'aaa'
20,2,'bbb'
20,5,'bbb'
20,8,'bbb'
20,11,'bbb'
20,14,'bbb'
20,17,'bbb'
20,20,'bbb'
20,23,'bbb'
20,26,'bbb'
20,29,'bbb'
30,3,'ccc'
30,6,'ccc'
30,9,'ccc'
30,12,'ccc'
30,15,'ccc'
30,18,'ccc'
30,21,'ccc'
30,24,'ccc'
30,27,'ccc'
30,30,'ccc'

Sometimes "scalability" means "Will it handle millions of rows?", other times the definition is different: "Will it handle more complex problems as well as it handled simple ones?"

For the second definition, at least so far, the answer for CROSS APPLY is "yes".

If you can't JOIN 'em, ...

noreply@blogger.com (Breck Carter) — Wed, 14 Dec 2011 08:01:00 +0000

The question's simple, why should the answer be complicated?

Question: How do I select the first row in each partition of a table?

Here's the data:

CREATE TABLE t ( 
   partition_id     INTEGER NOT NULL,
   entry_id         INTEGER NOT NULL,
   data             VARCHAR ( 10 ) NOT NULL,
   PRIMARY KEY ( partition_id, entry_id ) );

INSERT t VALUES ( 10, 1, 'aaa' );
INSERT t VALUES ( 10, 2, 'bbb' );
INSERT t VALUES ( 10, 3, 'ccc' );

INSERT t VALUES ( 20, 1, 'ppp' );
INSERT t VALUES ( 20, 2, 'qqq' );
INSERT t VALUES ( 20, 3, 'rrr' );

INSERT t VALUES ( 30, 1, 'xxx' );
INSERT t VALUES ( 30, 2, 'yyy' );
INSERT t VALUES ( 30, 3, 'zzz' );

Here's what the result should look like:

SELECT [the first row in each partition of t];

partition_id,entry_id,data
10,1,'aaa'
20,1,'ppp'
30,1,'xxx'

If you didn't already know, could you tell what the question was by reading the answers in these earlier articles?

First, there was this Old School solution using MIN and GROUP BY:

SELECT t.* 
  FROM t INNER JOIN ( SELECT partition_id     AS partition_id,
                             MIN ( entry_id ) AS min_entry_id
                        FROM t
                       GROUP BY partition_id
                    ) AS first_row
          ON t.partition_id = first_row.partition_id
         AND t.entry_id     = first_row.min_entry_id
 ORDER BY t.partition_id;

Second, there was an OLAP solution using RANK and PARTITION BY:

SELECT partition_id,
       entry_id,
       data 
  FROM ( select partition_id, 
                entry_id, 
                data,
                rank ( ) OVER partition_id_window AS entry_rank
           FROM t
         WINDOW partition_id_window AS (
                   PARTITION BY partition_id
                   ORDER BY entry_id
                ) ) DT
 WHERE entry_rank = 1
 ORDER BY partition_id;

Both get points for being obscure, neither look much like an answer to the question.

Let's Try Again

Question: How do I select the first row in each partition of a table?

Let's start by breaking the question in two parts, and solving each part separately:

Question 1: How do I select all the unique values for partition_id?

Answer: Use DISTINCT when selecting all the values of partition_id.

There's no question what this code is doing, it's selecting all the unique values of partition_id just like the question asked:

SELECT DISTINCT
       t.partition_id 
  FROM t
 ORDER BY t.partition_id;

partition_id
10
20
30

Question 2: How do I select the first row in a single partition?

Answer: Use a WHERE clause to pick the partition, then FIRST and ORDER BY to select the first row.

Once again, it's clear from reading the code what the question was:

SELECT FIRST * 
  FROM t
 WHERE t.partition_id = 10
 ORDER BY t.entry_id;

partition_id,entry_id,data
10,1,'aaa'

What now?

Now comes the hard part: Combining those two result sets to get the answer to the original question.

Let's try this:

SELECT first_t.*
  FROM ( SELECT DISTINCT
                t.partition_id 
           FROM t
          ORDER BY t.partition_id ) AS unique_partition
  INNER JOIN
       ( SELECT FIRST * 
           FROM t
          WHERE t.partition_id = unique_partition.partition_id
          ORDER BY t.entry_id ) AS first_t 
 ORDER BY first_t.partition_id;

You knew that wouldn't work, didn't you? INNER JOIN needs an ON clause:

Could not execute statement.
There is no way to join 'unique_partition' to 'first_t'
SQLCODE=-146, ODBC 3 State="42000"
Line 1, column 1

Let's try the old-fashioned "comma join operator", it doesn't need an ON clause:

SELECT first_t.*
  FROM ( SELECT DISTINCT
                t.partition_id 
           FROM t
          ORDER BY t.partition_id ) AS unique_partition,
       ( SELECT FIRST * 
           FROM t
          WHERE t.partition_id = unique_partition.partition_id
          ORDER BY t.entry_id ) AS first_t 
 ORDER BY first_t.partition_id;

Different problem this time; you can't refer to a column in the left-hand side of a join in the right-hand side:

Could not execute statement.
Illegal reference to correlation name 'unique_partition'
SQLCODE=-824, ODBC 3 State="42S02"
Line 1, column 1

One last try; let's move the WHERE clause to an ON clause so INNER JOIN will work:

SELECT first_t.*
  FROM ( SELECT DISTINCT
                t.partition_id 
           FROM t
          ORDER BY t.partition_id ) AS unique_partition
  INNER JOIN
       ( SELECT FIRST * 
           FROM t
          ORDER BY t.entry_id ) AS first_t 
  ON first_t.partition_id = unique_partition.partition_id 
 ORDER BY first_t.partition_id;

Yeah, it "works" all right, but it only returns one row instead of three; the predicate really needs to be inside the inner SELECT, not the outer one:

partition_id,entry_id,data
10,1,'aaa'

You've seen that before, haven't you?

Let's be honest, you've seen those error messages and wrong results before, haven't you? Like when you were trying to solve a problem that should have been easy (like this one) but you had to fiddle around with complex subqueries or temporary tables or even (gasp!) cursor fetch loops, and you ended up with a solution that looked like it was designed for a whole different problem.

It turns out there is a join operator that makes both of those error messages go away:

it doesn't need an ON clause, and
it does allow references from the right-hand side back to the left-hand side.

It's called CROSS APPLY...

...and it looks like this:

SELECT first_t.*
  FROM ( SELECT DISTINCT
                t.partition_id 
           FROM t
          ORDER BY t.partition_id ) AS unique_partition
  CROSS APPLY
       ( SELECT FIRST * 
           FROM t
          WHERE t.partition_id = unique_partition.partition_id
          ORDER BY t.entry_id ) AS first_t 
 ORDER BY first_t.partition_id;

partition_id,entry_id,data
10,1,'aaa'
20,2,'bbb'
30,3,'ccc'

CROSS APPLY is like the comma-join and CROSS JOIN operators in that it doesn't need (and cannot have) an ON clause. It is different from other join operators in that predicates on the right-hand side can refer to columns on the left-hand side.

So, like comma-join and CROSS JOIN, the CROSS APPLY joins every single row on the left-hand side with every single row on the right-hand side...

but...

...exactly what constitutes "every single row on the right-hand side" depends on each row on the left-hand side.

In this case, for each row on the left-hand side, the set consisting of "every single row on the right-hand side" is boiled down to one single row, the first row with matching partition_id.

So, CROSS APPLY is like INNER JOIN with special powers.

References:

Latest SQL Anywhere EBFs: 11.0.1.2724

noreply@blogger.com (Breck Carter) — Tue, 13 Dec 2011 15:16:00 +0000

The three asterisks "***" show what's new since the previous list. Only EBFs for the three fully-supported versions of SQL Anywhere are shown here: 10.0.1, 11.0.1 and 12.0.1.

Current builds for the most active platforms...

HP-UX Itanium    12.0.1.3469 EBF       14 Oct 2011
                 11.0.1.2724 EBF ***   12 Dec 2011 ***
                 10.0.1.3777 EBF       14 Oct 2008

IBM AIX          12.0.1.3469 EBF       20 Oct 2011
                 11.0.1.2724 EBF ***   12 Dec 2011 ***

Linux x86/x64    12.0.1.3457 EBF       12 Oct 2011
                 11.0.1.2700 EBF       27 Oct 2011
                 10.0.1.4239 EBF       17 Jun 2011

Mac OS           12.0.1.3352 EBF       26 May 2011
                 11.0.1.2449 EBF       29 Jun 2010
                 10.0.1.4042 EBF       01 Oct 2010

Solaris SPARC    12.0.1.3469 EBF       20 Oct 2011
                 11.0.1.2724 EBF ***   12 Dec 2011 ***
                 10.0.1.4239 EBF       17 Jun 2011

Solaris x64      11.0.1.2724 EBF ***   12 Dec 2011 ***
                 10.0.1.3870 EBF       01 Apr 2009

Windows x86/x64  12.0.1.3505 EBF       24 Nov 2011
                 11.0.1.2713 EBF       24 Nov 2011
                 10.0.1.4295 EBF       18 Nov 2011

[Download EBFs here] [Register for the Fuji Beta here]

...and for the other platforms:

HP-UX PA-RISC    10.0.1.3778 EBF                 16 Oct 2008

Linux Itanium    10.0.1.3777 EBF                 14 Oct 2008

NetWare          10.0.1.4036 EBF                 10 Mar 2010

Windows CE       10.0.1.4036 EBF                 09 Mar 2010

Windows Itanium  10.0.1 GA Upgrade from 10.0.0   26 Apr 2007

[Download EBFs here]

Product Suggestion: Minimum Row Size

noreply@blogger.com (Breck Carter) — Fri, 09 Dec 2011 17:04:00 +0000

Question: Have you ever set PCTFREE for a table?

Likely Answer Number 1: "Never."

Likely Answer Number 2: "Don't think so, can't remember."

Likely Answer Number 3: "I set it to 100 once, can't remember why."

As SQL Anywhere performance tuning knobs go, the PCTFREE setting is one of most obscure and least used. It's been around forever, but nobody ever uses it, probably because nobody can figure out how to use it properly.

What the %#!@ is PCTFREE?

Here's a code example that sets PCTFREE to 50% for table t:

CREATE TABLE t
   pkey    INTEGER         NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
   data    VARCHAR ( 200 ) NOT NULL DEFAULT '',
   PCTFREE 50 );

Here's what the Help says:

PCTFREE clause - Specifies the percentage of free space you want to reserve for each table page. The free space is used if rows increase in size when the data is updated. If there is no free space in a table page, every increase in the size of a row on that page requires the row to be split across multiple table pages, causing row fragmentation and possible performance degradation.

The value percent-free-space is an integer between 0 and 100. The former value specifies that no free space is to be left on each page—each page is to be fully packed. A high value causes each row to be inserted into a page by itself. If PCTFREE is not set, or is later dropped, the default PCTFREE value is applied according to the database page size (200 bytes for a 4 KB (and up) page size). The value for PCTFREE is stored in the ISYSTAB system table.

Just what should PCTFREE be set to? Is 50% good? Is 40% better, or is 60%?

It doesn't help that PCTFREE is a percentage but the default is expressed in bytes... I think you can convert, but who cares? Is 200 bytes good? Or 100? Or 400? ...the answer is the same, "I don't know."

The problem is this: When designing a new cargo ship / warehouse / database, folks generally know how many tonnes / cubic feet / bytes it should store; i.e., the capacity, not the free space.

Taken down to the database page level, folks know (or can figure out) how big the average row is when it's inserted, and how big it will be after it's been updated. What they don't know is how to convert that to an efficient amount of free space to leave after inserting.

Why should we care?

It is a fairly common practice to insert empty rows and then update them later when actual data become available... easy is good.

Once inserted, a row can never be moved to a different page, so when a page becomes full of empty rows the first UPDATE that expands a column will split that row... splits are bad, hence PCTFREE.

Here's a thought...

...instead of (or in addition to) PCTFREE why not give the users a knob called MINROWSIZE which they can calculate as expected average row size after the row has been updated, and is to be used by SQL Anywhere as the minimum amount of table data space to be reserved for each row when it is inserted?

In this case, MINROWSIZE 25 means reserve 4 bytes for the integer and about 20 bytes for the string even though the INSERT is providing an empty row:

CREATE TABLE t
   pkey    INTEGER         NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
   data    VARCHAR ( 200 ) NOT NULL DEFAULT '',
   MINROWSIZE 25 );

INSERT t VALUES ( DEFAULT, DEFAULT );

Currently, when a new row is inserted the PCTFREE amount is compared to the current free space in the page when deciding if a new row will fit. The size of each existing row and whether or not it is expected to grow in size is not taken into account, just the total space used and the PCTFREE amount, so depending of the current state of affairs inside the page the ideal PCTFREE could be larger or smaller than the actual value.

With MINROWSIZE, SQL Anywhere would take into account existing rows that have still not used up their MINROWSIZE allocation, as well as the MINROWSIZE allocation for the new row, when making the decision.

With some effort, the user could provide a MINROWSIZE value that is meaningful and accurate... as opposed to picking the number for Lotto PCTFREE.

A Walking Tour of the Foxhound FAQ

noreply@blogger.com (Breck Carter) — Wed, 07 Dec 2011 08:01:00 +0000

Is there anything duller than documentation?

Let's see... how about reading Frequently Asked Questions?

Even worse?

How about a walking tour through some new FAQs? Not the big shiny What's New in Foxhound 1.2 entries, but the more obscure ones, like this one:

What environment variables are used by Foxhound?

FOXHOUND1 is the all-important "Foxhound drive-and-path" used by all the Foxhound shortcuts... it's Foxhound's version of %SQLANY12%, if you lose it you'll probably want to set it up again.

FOXHOUND1BIN is the esoteric variable you can set to force Foxhound to start up using the 32-bit version of SQL Anywhere if you have both versions installed... "esoteric" because you almost never need it, Foxhound runs just fine using the 64-bit version. But, if you need it, here's how to use FOXHOUND1BIN (no, you don't need it if only the 32-bit version of SQL Anywhere is installed, Foxhound will use that one automatically).

FOXHOUND1UPGRADE refers to the variable that controls how much data the "post-setup" process will copy from your old Foxhound database when you are upgrading to a new version. The environment variable version of FOXHOUND1UPGRADE sets the initial value, but it defaults to "ALL" and is easy to change during the installation process, so it is rarely used.... the environment variable is rarely used, that is, but the underlying value is very important.

Ooops...

...wait a minute, that wasn't a FAQ, that was three paragraphs about a FAQ. To experience the full measure of dullness you have to follow the links, this article is just a travelogue.

Speaking of FOXHOUND1UPGRADE, this FAQ tells the full story: How do the different FOXHOUND1UPGRADE values work?

There's a lot of logic behind upgrading... it's a big deal if you have a big database, especially if you don't need all the data any more. You can let the purge process slowly delete it over time, or you can let the upgrade process quickly delete-it-by-not-copying it, so "upgrading" to the same version of Foxhound suddenly makes sense: How do I shrink the size of the Foxhound database?

An "alias" feature has been added to process that compiles the FAQ so different questions can point to the same answer. For example, this somewhat obscure question "How can the Heartbeat time be larger than the Sample time?" is answered as part of this longer explanation "How do I measure latency and throughput?"

The FAQ alias feature is most useful in the alphanumeric Messages section where messages containing multiple phrases and numeric codes appear repeatedly in the list. Quite a few messages have two or three entries pointing to the same explanation; this one's the winner with 4 appearances in the sorted list:

23: I/O error Fatal error: A disk read from file "...\foxhound1.db" failed with error code: (23) -- transaction rolled back --

A disk read from file "...\foxhound1.db" failed with error code: (23) -- transaction rolled back --

I/O error Fatal error: A disk read from file "...\foxhound1.db" failed with error code: (23) -- transaction rolled back --

transaction rolled back: I/O error Fatal error: A disk read from file "...\foxhound1.db" failed with error code: (23)

Even more useful than the alias feature is the new Google "Custom Search Engine" link that appears on every single FAQ page:

Go ahead, give it a whirl, type in "adhoc" and do a search.

Speaking of adhoc reporting, it's not new with Foxhound 1.2 but it might as well be; it wasn't documented until now:

How do I run adhoc queries on the Foxhound database?

How do I see the schema for adhoc reporting?

How do I change the ADHOC password?

Plus, a bunch of new columns were added, and new views, to make queries easier; after all, it's all your data!

Here's one of the shorter FAQs:

Question: How do I backup my Foxhound database?

Answer: Use one or both of the Foxhound shortcuts:
All Programs 
   - Foxhound1 
      - Tools 
         - Backup Foxhound Database 
         - Backup Foxhound Transaction Log

The second shortcut, Backup Foxhound Transaction Log, is new to Foxhound 1.2.

So is this explanation

How do the Foxhound backup shortcuts work?

plus, at long last, an explanation of what to do with all those backups:

How do I restore the Foxhound database from a backup?

Some folks like the context-sensitive Help frame on the right side of the Foxhound display, some folks hate it, but one thing everyone hated was the "Hide Help / Show Help" buttons when they didn't work.

Now those buttons are gone, and if you want to turn the Help frame on and off you have to check the "Show Help" box on the Foxhound Options page.

If there was a contest for "Shortest FAQ" this would be a another contestant:

Question: How do I set up Foxhound to monitor a large number of target databases?

Answer: See Section 7. Manage Multiple Monitor Sessions on the Monitor Options page.

It used to be an endless affair starting and stopping 100 sampling sessions: click, click, click, [curse], click, click, click, [curse], ...

Now it's a matter of setting up a text file with all the connection strings, then clicking on one button to start them all, another to stop them all, with nothing stopping you from starting and stopping them individually like before.

And finally, we have the answer to this important question: How much overhead does the Foxhound connection add to the target database?

Here's an excerpt, possibly a candidate for Quote Of The Day:

"Generally speaking, if Foxhound's overhead causes any problems, the target database is already in serious difficulty for other reasons."

That's a true statement, not just for Foxhound but for any performance monitor product: If monitoring performance hurts performance, there is already a problem that needs to be dealt with.

Foxhound 1.2 Is Now Available

noreply@blogger.com (Breck Carter) — Mon, 05 Dec 2011 08:01:00 +0000

Foxhound is a browser-based health, availability and performance monitor for SQL Anywhere that displays everything you need to know at a glance.

... and it's FREE! (to upgrade, that is)

If you already have version 1.0 or 1.1, Foxhound 1.2 is now available as a free upgrade.

If not, you can get an evaluation copy or buy a copy here.

Here's the download,
here's the FAQ,
here are the Docs,
here are the basic Features, and

Here's the What's New . . .

Now it's easier to manage up to 100 Monitor sessions:

The "Alerts Criteria" page has been redesigned as the "Monitor Options" page.

See: The Monitor Options Page.
The Default Settings can now be edited apart from the options for any particular target database.

See: Monitor Options - Switch Display.
The new Force Default Settings On All Targets button on the Monitor Options page lets you "push" the default settings to all the targets, all at once. Previously, you could only "pull" the settings to each target one at a time with the Restore Default Settings button.

See: Save And Restore Alerts Criteria Defaults.
The Manage Multiple Monitor Sessions feature lets you specify batches of target databases, and the Start and Stop All Sessions buttons let you turn sampling on and off for each batch.
See: Manage Multiple Monitor Sessions.

The Monitor tab on the Foxhound Menu page is now easier to use, and more informative:

The lists of targets and outstanding Alerts have been vertically squished so you don't have to scroll down so far when you've got lot of targets.
The Disable Refresh and Enable Refresh buttons make it easier to scroll down to look at something.
More "at a glance" columns have been added:
- Active Alerts count, with a link down into the Alerts list
- Conns
- Waiting Req
- Locks Held, Blocked Conns
- CPU Time
- Temp Space
- Disk Reads, Writes
The Start All Sampling and Stop All Sampling buttons let you turn sampling on and off for all the targets.

See: The Monitor Tab.

Scrolling through gigabytes of data on the History page used to be slowwwww, like continental drift, now it's faster.

Plus, new scroll amounts have been added (500 samples and 3 hours):

« Newest « 500 « 100 « 20 « 1 sample         1 sample » 20 » 100 » 500 » Oldest » 
« Month « Week « Day « 3 Hours « 1 Hour   1 Hour » 3 Hours » Day » Week » Month »

...and, the "Freeze Frame Heights" button lets you resize and freeze the History page frames so they don't reset ("Grrr!") every time you scroll:

The database-level samples frame,
the blocked connections frame and
the current connections frame.

See: The History Page.

It's your data; now it's easier to run adhoc queries:

New views have been added for adhoc reporting.
A separate read-only "Adhoc Schema" database lets you see what the views and underlying tables look like.

New connection-level columns have been added to make queries easier to write:

LockRowID                         BIGINT NULL
blocked_connection_count          BIGINT NOT NULL DEFAULT 0
current_req_status                VARCHAR ( 100 ) NOT NULL DEFAULT ''             
cache_satisfaction                DECIMAL ( 30, 0 ) NOT NULL DEFAULT 0.0 
time_connected                    BIGINT NOT NULL DEFAULT 0
total_waits                       BIGINT NOT NULL DEFAULT 0 
waiting_time                      DECIMAL ( 30, 6 ) NOT NULL DEFAULT 0.0
transaction_running_time          BIGINT NOT NULL DEFAULT 0
time_since_last_request           BIGINT NOT NULL DEFAULT 0
index_satisfaction                DECIMAL ( 30, 0 ) NOT NULL DEFAULT 0.0

Except for LockRowID, all the new columns contain values that Foxhound used to calculate only when the data was displayed on the Monitor and History pages; now these values are calculated once and stored permanently.

See: How do I run adhoc queries on the Foxhound database?

Now you can use Gmail to send Alert emails as well as receive them.

See: How do I tell Foxhound to send Alert emails via the Gmail SMTP server smtp.gmail.com?

The data upgrade process now runs faster...

...as well as giving you control over how much data to upgrade:

The default is FOXHOUND1UPGRADE = ALL to copy all the data from your old Foxhound database to the new one.
If you choose FOXHOUND1UPGRADE = OPTIONS during the installation process, Foxhound will copy everything except the Monitor samples. When you start Foxhound again, all the old sample data will be gone but sampling will start again right away (assuming it was running before you installed the new version).
If you want to save the Monitor samples recorded since a particular date, specify FOXHOUND1UPGRADE = yyyymmmdd.
To save the samples recorded during the past n days, use FOXHOUND1UPGRADE = n.

What's really cool about the new upgrade process is you can use it to purge, shrink and reorganize the Foxhound database: just reinstall the same version of Foxhound with FOXHOUND1UPGRADE = yyyymmmdd or FOXHOUND1UPGRADE = n.

See:

How do the different FOXHOUND1UPGRADE values work?

How do I shrink the size of the Foxhound database?

Good news, bad news:

Foxhound still supports all versions of SQL Anywhere from 5.5 through 12.0.1.
Foxhound itself now runs on SQL Anywhere Version 12.0.1.
Foxhound will use the 64-bit version of SQL Anywhere 12.0.1 if it is installed. Previously, Foxhound ran only on the 32-bit version of SQL Anywhere 11.0.1.

See: How do I tell if Foxhound is running on the 32-bit or 64-bit version of SQL Anywhere?
You can force Foxhound to use the 32-bit version of SQL Anywhere by setting the FOXHOUND1BIN environment variable to Bin32... sometimes, it's just easier to do that than to try to figure out what's going wrong with ODBC and a legacy target database.

See: How do I start Foxhound with the 32-bit version of SQL Anywhere when both the 32-bit and 64-bit versions of SQL Anywhere are installed?
Bad news: Foxhound requires SQL Anywhere 12.0.1.3298 or later to run. And it still only runs on Windows. But none of that affects your target database: as long as Foxhound can establish an ODBC connection to your database, it can monitor the performance and display the schema.

See: Requirements.

The look-and-feel of the Foxhound Options page has changed to match the new improved Monitor Options window.

See: The Foxhound Options Page.

A single global "Enable Emails" setting has been added to the Foxhound Options page in case you want to turn off an accidental "alert storm".

See: Foxhound Options - Global Overrides.

New shortcuts have been added:

start - All Programs 
   Foxhound1
      Start Foxhound via Chrome
      Tools 
         Backup Foxhound Transaction Log
         Start Foxhound via Chrome - debug

See:

How do I start Foxhound?

How do I backup my Foxhound database?

Exception handling has been improved to allow Foxhound to continue operating after receiving bad data from a target database.

See: How do I report unexpected errors and exceptions?

The thirty-day Evaluation period can now be extended: just get a Evaluation Renewal key from breck.carter@gmail.com.

See: How do I get a copy of Foxhound?

The fRiDaY File - Mobility Mayhem

noreply@blogger.com (Breck Carter) — Fri, 02 Dec 2011 20:01:00 +0000

Texting!

Recalculating!

Texting!

Meet Mayhem, prerecorded...

...and in person:

How do you know when it's time to ship?

noreply@blogger.com (Breck Carter) — Fri, 02 Dec 2011 08:01:00 +0000

At some point, every piece of software has to "go out the door", into the hands of ~~unsuspecting~~ eager customers.

The question is, how do you know when that time has come?

When all the bugs are gone!

Yeah, sure, and if that was the case there wouldn't be any new software, ever, because the bugs are never all gone.

Fortunately, there are three simple criteria to be met before a software product is ready to ship:

1. All the new bugs are going into the "Future Fix" category.

Once upon a time, you were determined to fix each and every flaw that turned up, no matter how minor.

Now, it's not just a matter of "When will this bug get fixed?" (Not Now) but "Will this bug get fixed?"... that's when a Future Fix bug gets flagged with "Wait For Reproducible" which is the same as "No, Never Mind".

2. You're writing more FAQs than fixes.

Sometimes it's easier to cover an electrical outlet than to document why it's not a good idea to stick a fork into it... until you consider the fact that docs don't need to be tested but if you change the code you have to go back and run all . . . those . . . endless . . . regression tests again.

Plus, it's really easy to say "It's not a bug, it's a FAQ!" with a straight face, a lot easier than "It's not a bug, it's a feature!" even though the two statements are not that different.

Here's the third and final "Ready To Ship" criteria:

3. You just wrote the same FAQ a second time.

...and it's not as well-written as the first one!

That's when you know, it's time to ship.