Monday, January 30, 2012

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

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]


Friday, January 27, 2012

Coding Good Comments: Exhortations

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.

Wednesday, January 25, 2012

Coding Good Comments: Section Titles

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?"


Monday, January 23, 2012

Latest SQL Anywhere EBF - 12.0.1.3537 for Windows

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]


Friday, January 20, 2012

The fRiDaY File - The Best Kind Of Error

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!


Dilbert.com



Latest SQL Anywhere EBFs - 10.0.1.4310 For Linux and SPARC

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]


Wednesday, January 18, 2012

Develop, Test, Release, ...

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...
Dilbert.com


Monday, January 16, 2012

Let's play Gotcha! 2012 Edition, Round 1

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



Friday, January 13, 2012

Latest SQL Anywhere EBF - 10.0.1.4310 For Windows

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]


Wednesday, January 11, 2012

Coding Guidelines

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.


Monday, January 9, 2012

You Can't Do That In SQL!

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?

  1. Drink the Unix Kool-Aid and learn to love grep for Windows?

  2. switch from Wordpad to something fancy like Notepad++?

  3. go big with something like Visual Studio for SQL?

  4. 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:
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.


Wednesday, January 4, 2012

Happy New Year!

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