Saturday, June 30, 2012

The fRiDaY File - Cloudage

The fRiDaY File is late!

It's an outage!

No, it must be a cloudage!


That's it, a cloudage!

...never mind that Google hosts The fRiDaY File, not Amazon.

...never mind that that work didn't start on this post until Saturday morning.

...it's a cloudage!




Friday, June 29, 2012

Example: ROW_NUMBER()

Question: How do I check two columns to make sure they have the same ordering?

Answer: Why, that's EASY!

You just take the...

um...

you do a...

just a minnit...

Could you repeat the question, please?


Question: How do I make sure a table with two NOT NULL columns a and b yields the same row ordering no matter which column is used in the ORDER BY?

Whaddaya mean "the same row ordering"? What about duplicates?

OK, the "same row ordering for distinct column values".

The question can be restated as
How do I check that SELECT DISTINCT a, b FROM t ORDER BY a, b yields same result set as ORDER BY b, a?
Here's some data:
CREATE TABLE t (
   pkey   INTEGER NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
   a      INTEGER NOT NULL,
   b      INTEGER NOT NULL );

INSERT t ( a, b ) VALUES ( 3, 22 );
INSERT t ( a, b ) VALUES ( 4, 45 );
INSERT t ( a, b ) VALUES ( 1, 10 );
INSERT t ( a, b ) VALUES ( 3, 22 );
INSERT t ( a, b ) VALUES ( 2, 22 );
INSERT t ( a, b ) VALUES ( 4, 44 );
INSERT t ( a, b ) VALUES ( 5, 33 );
INSERT t ( a, b ) VALUES ( 3, 22 );
COMMIT;

SELECT * FROM t ORDER BY a, b;

pkey  a  b
3     1  10
5     2  22
1     3  22
4     3  22
8     3  22
6     4  44
2     4  45
7     5  33

The order of the pkey column isn't important, what's important is the order of a as compared with the order of b; here's what that looks like:
SELECT DISTINCT a, b FROM t ORDER BY a, b;
SELECT DISTINCT a, b FROM t ORDER BY b, a;

a  b     a  b
1  10    1  10
2  22    2  22
3  22    3  22
4  44    5  33
4  45    4  44
5  33    4  45

Right away, you can see "5, 33" is a problem because it appears at row 6 in one result set and row 4 in the other.

This suggests a brute-force solution:
Compare the ROW_NUMBER() values for the two result sets.

I'm sure YOU can do better than this...

WITH vdistinct AS
        ( SELECT DISTINCT
                 a, 
                 b
            FROM t ),
     vjoin AS
        ( SELECT *
            FROM ( SELECT a                                   AS a1, 
                          b                                   AS b1,  
                          ROW_NUMBER() OVER ( ORDER BY a, b ) AS row1 
                     FROM vdistinct ) AS t1
                 INNER JOIN
                 ( SELECT a                                   AS a2,  
                          b                                   AS b2,  
                          ROW_NUMBER() OVER ( ORDER BY b, a ) AS row2 
                     FROM vdistinct ) AS t2
                 ON t1.a1 = t2.a2
                AND t1.b1 = t2.b2 )
SELECT *
  FROM vjoin
 WHERE row1 <> row2
 ORDER BY row1, row2;

The WITH clause on lines 1 to 18 defines two local views, also known as "common table expressions", called vdistinct and vjoin.

The first local view on lines 1 to 5 takes care of the fact that the DISTINCT keyword applies to the entire SELECT list, and if we want all the distinct pairs of a and b, there can't be anything else (say, ROW_NUMBER()) in the SELECT DISTINCT list.

The second local view on lines 6 to 18y joins the first view to itself on matching values of a and b, with the difference being the two ROW_NUMBER() OVER ( ORDER BY ... ) items.

The final SELECT on lines 19 to 22 looks for ( a, b ) ( a, b ) pairs with different ROW_NUMBER() values... if the result set is empty, then columns a and b are OK...

...but if anything shows up, there's a problem:
a1  b1  row1  a2  b2  row2
4   44  4     4   44  5
4   45  5     4   45  6
5   33  6     5   33  4

There are three rows in the result set but only one problem, why?

Because the question only asked "Is there a problem here?"

Is that good enough?


In 1972 finding all the errors in one run was critically important because you had to wait hours, or overnight, for each test.

But it's not 1972 any more, and finding one error at a time is perfectly OK.

Consider this real-world example: Only lines 3, 4 and 5 had to be changed to use the WITH SELECT on a different table.
WITH vdistinct AS
        ( SELECT DISTINCT
                 article_number AS a, 
                 order_by_date  AS b
            FROM article ),
     vjoin AS
        ( SELECT *
            FROM ( SELECT a                                   AS a1, 
                          b                                   AS b1,  
                          ROW_NUMBER() OVER ( ORDER BY a, b ) AS row1 
                     FROM vdistinct ) AS t1
                 INNER JOIN
                 ( SELECT a                                   AS a2,  
                          b                                   AS b2,  
                          ROW_NUMBER() OVER ( ORDER BY b, a ) AS row2 
                     FROM vdistinct ) AS t2
                 ON t1.a1 = t2.a2
                AND t1.b1 = t2.b2 )
SELECT *
  FROM vjoin
 WHERE row1 <> row2
 ORDER BY row1, row2;

Over 100 rows were returned, not because of data-entry errors but because of a logic error in an automatic load process:
a1,b1,row1,a2,b2,row2
161.011000,'2005-10-01',243,161.011000,'2005-10-01',262
161.020000,'2005-08-21',244,161.020000,'2005-08-21',243
161.030000,'2005-08-21',245,161.030000,'2005-08-21',244
161.040000,'2005-08-21',246,161.040000,'2005-08-21',245
161.050000,'2005-08-21',247,161.050000,'2005-08-21',246
161.060000,'2005-08-21',248,161.060000,'2005-08-21',247
161.070000,'2005-08-21',249,161.070000,'2005-08-21',248
161.080000,'2005-08-21',250,161.080000,'2005-08-21',249
161.090000,'2005-08-21',251,161.090000,'2005-08-21',250
161.100000,'2005-08-21',252,161.100000,'2005-08-21',251
161.110000,'2005-08-21',253,161.110000,'2005-08-21',252
161.120000,'2005-08-21',254,161.120000,'2005-08-21',253
161.130000,'2005-08-21',255,161.130000,'2005-08-21',254
161.140000,'2005-08-21',256,161.140000,'2005-08-21',255
161.150000,'2005-08-21',257,161.150000,'2005-08-21',256
161.160000,'2005-08-21',258,161.160000,'2005-08-21',257
161.170000,'2005-08-21',259,161.170000,'2005-08-21',258

After the logic error was fixed, only six rows showed up:
a1,b1,row1,a2,b2,row2
303.000000,'2011-02-01',759,303.000000,'2011-02-01',764
304.000000,'2011-01-04',760,304.000000,'2011-01-04',759
304.010000,'2011-01-11',761,304.010000,'2011-01-11',760
304.020000,'2011-01-17',762,304.020000,'2011-01-17',761
304.030000,'2011-01-24',763,304.030000,'2011-01-24',762
304.040000,'2011-01-31',764,304.040000,'2011-01-31',763

Now that WAS a data-entry error... one error involving two pairs (303, 2011-02-01) and (304, 2011-01-04). It caused a cascading effect in the subsequent automatic load process, hence the six rows in the WITH SELECT result set.

In other words, when that single error was fixed, the WITH SELECT returned no rows - no problems!



Wednesday, June 27, 2012

Who is "anon."?

The "Authors" column is now all filled in on the SQL Anywhere Technical Documents page, and the article counts look like this:

SELECT COUNT(*)                  AS article_count,
       sa_split_list.row_value   AS author
  FROM article CROSS APPLY sa_split_list ( article.authors, ',<br>' )
 GROUP BY author
 ORDER BY article_count DESC,
       author ASC;

288  anon.
 18  Joshua Savill
 17  -
 15  Reg Domaratzki
 14  Eric Farrar
 12  David Fishburn
 10  Chris Kleisath
 10  Dave Neudoerffer
 10  Glenn Paulley
 10  Jason Hinsperger
  9  José Ramos
  8  Bill Hillis
  8  Robert Waywell
  7  Alex Reif
  7  Liam Cavanagh
  7  Tom Slee
  5  Breck Carter
  5  Dan Farrar
  4  Anil K. Goel
  4  G. N. Paulley
  4  Mike Paola
  4  Russ Chamberlain
  3  Graham Hurst
  3  Ivan T. Bowman
  3  Jim Graham
  3  Matthew Young-Lai
  2  Ali Chalhoub
  2  Anil Goel
  2  David Loop
  2  Jonathan Greisz
  2  Mark Culp
  2  Martyn Mallick
  2  Paul Fast
  2  Philippe Bertrand
  2  Steven McDowell
  1  Anisoara Nica
  1  Anthony Scian
  1  Aylwin Lo
  1  B. Lucier
  1  Berndt Hamboeck
  1  Bill McCaslin
  1  Bob Holt
  1  Brad Coomes
  1  Brendan Lucier
  1  Chris Irie
  1  Dan Comfort
  1  Dan Lowe
  1  Darren D.V. Vaillant
  1  David Carson
  1  Deanne Chance
  1  Douglas Navas
  1  Eric Giguère
  1  Eric Murchie-Beyma
  1  Evguenia Eflov
  1  Geno Coschi
  1  Hong Shi
  1  Ian Thain
  1  Jagdish Bansiya
  1  James Blackstock
  1  James Parker
  1  Jay Hennings
  1  Jay J. Hennings
  1  John Smirnios
  1  Kurt Lichtner
  1  Kurt Trushenski
  1  Mark Wright
  1  Matt Carrier
  1  Paul A. Horan
  1  Peter Bumbulis
  1  Sam Lakkundi
  1  Shannon White
  1  Stephen Beck
  1  Todd A. Loomis
  1  Todd Loomis
  1  Trish Genoe
  1  Walt Tallman
Even if you accept that Glenn Paulley and G. N. Paulley are one and the same person and not friendly-versus-formal twins, anon. still outranks everyone by a wide margin.

Joshua Savill comes in a distant second, beating NULL who is represented in the list by the "-" character indicating an article that's missing an url for one reason or another.

But,

and this is important: Reg Domaratzki is now the

Undisputed Iron Man of Techwave

broken foot or not:
WITH count_by_author AS
        ( SELECT COUNT(*) AS article_count,
                 article.authors AS "Iron Men"
            FROM article
           WHERE article.title LIKE 'Techwave %'
             AND article.authors <> '-'
           GROUP BY article.authors ),
     count_by_iron_men AS
       ( SELECT MAX ( count_by_author.article_count ) AS article_count
           FROM count_by_author )
SELECT count_by_author.*
  FROM count_by_author 
          INNER JOIN count_by_iron_men
                  ON count_by_iron_men.article_count = count_by_author.article_count
 ORDER BY count_by_author."Iron Men";

article_count  Iron Men
15             Reg Domaratzki

Dilbert.com




Monday, June 25, 2012

Example: CROSS APPLY and sa_split_list()

Question: How do I join a table with a column?

Not "How do I use a column to join a table with another table?", but "How do I join a table with the denormalized data in a column in the same table?"
Answer: An earlier example Pushing OPENSTRING and CROSS APPLY concentrated more on how OPENSTRING worked than on the wondrous benefits of CROSS APPLY.

Here's a simpler example, a table with a string column containing a comma-separated list of names:
CREATE TABLE t (
   pkey    INTEGER NOT NULL PRIMARY KEY,
   list    LONG VARCHAR NOT NULL );

INSERT t VALUES ( 1, 'Delmar, Ainslie, Genevieve, Marlon, Jordan' );
INSERT t VALUES ( 2, 'Hunter, Nissa, Briana, Electra, Khalil' );
INSERT t VALUES ( 3, 'Inari, Fabriane, Lisette, Calista' );
COMMIT;

The builtin sa_split_list() procedure can be used to turn a string like 'Delmar, Ainslie, Genevieve, Marlon, Jordan' into a multi-row table with one name per row.
SELECT * 
  FROM sa_split_list ( 'Delmar, Ainslie, Genevieve, Marlon, Jordan' );

line_num,row_value
1,Delmar
2, Ainslie
3, Genevieve
4, Marlon
5, Jordan

The question now becomes "How do I code sa_split_list ( t.list ) in the FROM clause?"

It can't be this easy!


The answer is "use CROSS APPLY":
SELECT *
  FROM t CROSS APPLY sa_split_list ( t.list );

pkey,list,line_num,row_value
1,Delmar, Ainslie, Genevieve, Marlon, Jordan,1,Delmar
1,Delmar, Ainslie, Genevieve, Marlon, Jordan,2, Ainslie
1,Delmar, Ainslie, Genevieve, Marlon, Jordan,3, Genevieve
1,Delmar, Ainslie, Genevieve, Marlon, Jordan,4, Marlon
1,Delmar, Ainslie, Genevieve, Marlon, Jordan,5, Jordan
2,Hunter, Nissa, Briana, Electra, Khalil,1,Hunter
2,Hunter, Nissa, Briana, Electra, Khalil,2, Nissa
2,Hunter, Nissa, Briana, Electra, Khalil,3, Briana
2,Hunter, Nissa, Briana, Electra, Khalil,4, Electra
2,Hunter, Nissa, Briana, Electra, Khalil,5, Khalil
3,Inari, Fabriane, Lisette, Calista,1,Inari
3,Inari, Fabriane, Lisette, Calista,2, Fabriane
3,Inari, Fabriane, Lisette, Calista,3, Lisette
3,Inari, Fabriane, Lisette, Calista,4, Calista

OK, that's pretty ugly,
  • let's SELECT only the columns we want

  • and ORDER the result set by the name values:
SELECT sa_split_list.row_value,
       t.pkey       
  FROM t CROSS APPLY sa_split_list ( t.list )
 ORDER BY sa_split_list.row_value, 
       t.pkey;

row_value,pkey
 Ainslie,1
 Briana,2
 Calista,3
 Electra,2
 Fabriane,3
 Genevieve,1
 Jordan,1
 Khalil,2
 Lisette,3
 Marlon,1
 Nissa,2
Delmar,1
Hunter,2
Inari,3

Aha! The names aren't in perfect order because the list isn't really "comma-separated", it's "comma-space-separated".

One solution (perhaps the correct solution in some cases) would be to code TRIM ( sa_split_list.row_value ) in the SELECT list.

Another solution is to change the default separator used by sa_split_list... and while we're at it, let's give row_value a more meaningful alias name, say, "name" :)
SELECT sa_split_list.row_value AS name,
       t.pkey       
  FROM t CROSS APPLY sa_split_list ( t.list, ', ' )
 ORDER BY sa_split_list.row_value, 
       t.pkey;

name,pkey
Ainslie,1
Briana,2
Calista,3
Delmar,1
Electra,2
Fabriane,3
Genevieve,1
Hunter,2
Inari,3
Jordan,1
Khalil,2
Lisette,3
Marlon,1
Nissa,2

In the real world columns don't often contain simple lists like this... what they DO contain is giant globs of denormalized crap stuff, like, say, long strings of <TD> and </TD> pairs... so don't forget, you've got CROSS APPLY in your tool kit.

Dilbert.com



Friday, June 22, 2012

The fRiDaY File - These must be GSA prices

The Early-Bird price for Techwave this year is US$2,295.

Then there's the hotel at US$179 per night, plus 12% tax, plus meals, plus airfare, plus all those taxes and fees.

Aren't you glad you don't have to worry about spending?





Example: Strip Trailing Zeros After Decimal Point

Question: How do I strip trailing zeros after the decimal point from a string containing a number?

Mind-Boggling Answer: This C# solution got 24 up-votes on StackOverflow, and the author has almost half-a-million reputation points for his 21,000 answers, so it must be right!

using System;
using System.Numerics;

public static class DecimalExtensions
{
    // Avoiding implicit conversions just for clarity
    private static readonly BigInteger Ten = new BigInteger(10);
    private static readonly BigInteger UInt32Mask = new BigInteger(0xffffffffU);

    public static decimal Normalize(this decimal input)
    {
        unchecked
        {
            int[] bits = decimal.GetBits(input);
            BigInteger mantissa = 
                new BigInteger((uint) bits[0]) +
                (new BigInteger((uint) bits[1]) << 32) +
                (new BigInteger((uint) bits[2]) << 64);

            int sign = bits[3] & int.MinValue;            
            int exponent = (bits[3] & 0xff0000) >> 16;

            // The loop condition here is ugly, because we want
            // to do both the DivRem part and the exponent check :(
            while (exponent > 0)
            {
                BigInteger remainder;
                BigInteger divided = BigInteger.DivRem(mantissa, Ten, out remainder);
                if (remainder != BigInteger.Zero)
                {
                    break;
                }
                exponent--;
                mantissa = divided;
            }
            // Okay, now put it all back together again...
            bits[3] = (exponent << 16) | sign;
            // For each 32 bits, convert the bottom 32 bits into a uint (which won't
            // overflow) and then cast to int (which will respect the bits, which
            // is what we want)
            bits[0] = (int) (uint) (mantissa & UInt32Mask);
            mantissa >>= 32;
            bits[1] = (int) (uint) (mantissa & UInt32Mask);
            mantissa >>= 32;
            bits[2] = (int) (uint) (mantissa & UInt32Mask);

            return new decimal(bits);
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            Check(6.000m);
            Check(6000m);
            Check(6m);
            Check(60.00m);
            Check(12345.00100m);
            Check(-100.00m);
        }

        static void Check(decimal d)
        {
            Console.WriteLine("Before: {0}  -  after: {1}", d, d.Normalize());
        }
    }
}

Gasp! [shudder]

...words cannot express the horror.

How about this instead?

Just... write some code to look for trailing zeros after the decimal point and remove them from the string.

Sound familiar? It's the original Question almost word for word.
CREATE FUNCTION strip_trailing_zeros_after_decimal_point (
   IN @input_number VARCHAR ( 100 ) )
   RETURNS VARCHAR ( 100 ) 
BEGIN
   DECLARE @output_number VARCHAR ( 100 );

   SET @output_number = TRIM ( COALESCE ( @input_number, '' ) );

   IF LOCATE ( @output_number, '.' ) > 0 THEN

      WHILE RIGHT ( @output_number, 1 ) = '0' LOOP
         SET @output_number = LEFT ( @output_number, LENGTH ( @output_number ) - 1 );
      END LOOP;

      IF RIGHT ( @output_number, 1 ) = '.' THEN
         SET @output_number = LEFT ( @output_number, LENGTH ( @output_number ) - 1 );
      END IF;

   END IF;
      
   RETURN @output_number;

END;
  • The SET on line 7 does a bit of input editing: it turns NULL into '' and gets rid of leading and trailing spaces.

  • The IF LOCATE on line 9 makes sure the function doesn't do anything more if there's no decimal point at all.

  • The WHILE loop on lines 11 to 13 zaps each trailing zero, one at a time, from the RIGHT end of the string.

  • The IF RIGHT ... SET on lines 15 to 17 gets rid of the decimal point if it ends up being the last thing on the line.
Not as sexy as "(int) (uint) (mantissa & UInt32Mask)", but it's readable and it does seem to work...
SELECT '000' AS x, strip_trailing_zeros_after_decimal_point ( x ) AS y
UNION
SELECT '123' AS x, strip_trailing_zeros_after_decimal_point ( x ) AS y
UNION
SELECT '123.' AS x, strip_trailing_zeros_after_decimal_point ( x ) AS y
UNION
SELECT '123.0' AS x, strip_trailing_zeros_after_decimal_point ( x ) AS y
UNION
SELECT '123.00' AS x, strip_trailing_zeros_after_decimal_point ( x ) AS y
UNION
SELECT '123.01' AS x, strip_trailing_zeros_after_decimal_point ( x ) AS y
UNION
SELECT '123.10' AS x, strip_trailing_zeros_after_decimal_point ( x ) AS y
UNION
SELECT '123.010' AS x, strip_trailing_zeros_after_decimal_point ( x ) AS y
UNION
SELECT '123.456' AS x, strip_trailing_zeros_after_decimal_point ( x ) AS y
UNION
SELECT '123.4560' AS x, strip_trailing_zeros_after_decimal_point ( x ) AS y
UNION
SELECT '123.45600' AS x, strip_trailing_zeros_after_decimal_point ( x ) AS y
UNION
SELECT '123000' AS x, strip_trailing_zeros_after_decimal_point ( x ) AS y
ORDER BY 1;

x         y
000       000
123       123
123.      123
123.0     123
123.00    123
123.01    123.01
123.010   123.01
123.10    123.1
123.456   123.456
123.4560  123.456
123.45600 123.456
123000    123000
...well, it works for trailing zeros, the "000" is a formatting question for another day.

And I'll bet you could do better! (even simpler, even more straightforward)

Dilbert.com




Wednesday, June 20, 2012

Botox for the Blog

Some items have been removed from this blog...

  • The big "Foxhound 1.2" ad is gone, but it something will be back when Version 2 ships.

  • The Search gadgets have been moved to the much-better Advanced Search page, joining others on the "links line" at the top of this page.

  • The Quantcast ego badge is gone... no, it had NOTHING to do with the fact traffic dropped in April :)

  • The blog lists have been [ahem] trimmed somewhat...
    WHEN active again THEN they will return;

In other news...

Work proceeds on the SQL Anywhere Technical Documents page on this blog:
  • Techwave 2004 presentations have been added... only Techwave 2003 remains, and their whereabouts are known.

  • A calculated "row number" column has been added to show that the list is now up to 499 articles with actual links.

  • Google seems to be indexing new entries as fast as they are added, so the Search gadget is quite effective.
The priority list looks like this:
  • keep up with new articles,

  • add the Techwave 2003 presentations,

  • fill in the Authors column, working from the top down (backwards in time), and

  • add entries from various blogs, again working backwards in time, starting with Chris Kleisath, Eric Farrar, Glenn Paulley, Jason Hinsperger and Tom Slee ...and ...and ...?


It's all in a database (of course!) so reports can be run...

Who's the Iron Man of Techwave?

WITH count_by_author AS
        ( SELECT COUNT(*) AS article_count,
                 article.authors AS "Iron Men"
            FROM article
           WHERE article.title LIKE 'Techwave %'
             AND article.authors <> '-'
           GROUP BY article.authors ),
     count_by_iron_men AS
       ( SELECT MAX ( count_by_author.article_count ) AS article_count
           FROM count_by_author )
SELECT count_by_author.*
  FROM count_by_author 
          INNER JOIN count_by_iron_men
                  ON count_by_iron_men.article_count = count_by_author.article_count
 ORDER BY count_by_author."Iron Men";

article_count  Iron Men
10             David Fishburn
10             Reg Domaratzki

Answer: Reg Domaratzki!

Sure, David Fishburn is currently tied with Reg for the most Techwave presentations in the list, but... David didn't play through the pain like Reg did!




Monday, June 18, 2012

Latest SQL Anywhere EBFs: 12.0.1 for (almost) everyone, plus direct links!

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: 11.0.1 and 12.0.1.

  • Note: Just because an older version or different platform isn't "fully supported" any more 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 active platforms...

HP-UX Itanium     12.0.1.3740 EBF ***   15 Jun 2012 ***  <<-- Look! Direct Links!
                  11.0.1.2753 EBF       13 Feb 2012

IBM AIX           12.0.1.3740 EBF ***   15 Jun 2012 ***  <<-- Thank you, Jeff Albion!
                  11.0.1.2753 EBF       13 Feb 2012

Linux x86/x64     12.0.1.3740 EBF ***   15 Jun 2012 ***
                  11.0.1.2811 EBF       17 May 2012

Mac OS            12.0.1.3577 EBF       24 Feb 2012
                  11.0.1.2449 EBF       29 Jun 2010

Solaris SPARC     12.0.1.3740 EBF ***   15 Jun 2012 ***
                  11.0.1.2811 EBF       14 May 2012

Solaris x64       12.0.1.3740 EBF ***   15 Jun 2012 ***
                  11.0.1.2753 EBF       13 Feb 2012

Windows x86/x64   12.0.1.3742 EBF ***   15 Jun 2012 ***
                  11.0.1.2808 EBF       22 May 2012

To see other choices you'll have to start at the top, here, and 
work your way down... pack a lunch!
Dilbert.com




Friday, June 15, 2012

The fRiDaY File - Spam, Spam, Spam

OK, I admit it, Google's better than I am at detecting spam.

Here's some anecdotal evidence from the spam folder:


This is the perfect blog for anyone who wants to know about this topic. You know so much its almost hard to argue with you (not that I really would want...HaHa). You definitely put a new spin on a subject that’s been written about for years. Great stuff, just great!
...stroke, stroke, stroke that ego, stroke!

I came THIS CLOSE to marking it "Not spam"... then I noticed who it was from: Camera Cell Phone Reviews (don't worry, it's not a real link any more)

As much as I'd like to believe Camera Cell Phone Reviews is a heavy user of SQL Anywhere, the truth is...

Google's right, I'm wrong. It's spam.


And the best part? Spam filtering is built in, no effort, no unstable plugin, no false positives, and nothing escapes.

I'm sure there's a good reason to use Wordpress but I can't think of one :)

And now, for your amusement...


These spammers might not be as good at social engineering as Camera Cell Phone Reviews but they're pretty good, and some of them show real effort:

It's going to be finish of mine day, but before ending I am reading this wonderful piece of writing to improve my experience. My website: Recover deleted mail Outlook without exchange server

 
  Anonymous
I've been browsing online more than 4 hours today, yet I never found any interesting article like yours. It's pretty worth enough for me. Personally, if all web owners and bloggers made good content as you did, the internet will be a lot more useful than ever before. my site > backup online sure

 
  Anonymous
I really love your blog.. Very nice colors & theme. Did you make this website yourself? Please reply back as I'm planning to create my own personal site and would like to find out where you got this from or exactly what the theme is called. Appreciate it! my website > Mosaics The Artists Taste

 
  Anonymous
I wish you continued success and a very nice page boyac? dekorasyon boya ustas?

 
  dekorasyon
Its highly informative. I would be visiting your blog hereafter regularly to gather valuable information. Chrome Stacks

 
  bala murugan
You know, most people write a report in Reporting Services to get anything worth it's salt you need to have some pretty advanced t-sql experience or have a good background in databases to be productive in SQL Reporting Services.

 
  SQL Reports
Nice Post about PowerBuilder Development . Thanks for sharing few of information .

 
  Nelson
Its really so nice post. thanks for sharing all about PowerBuilder

 
  Millan
I recently came across your blog and have been reading along. I thought I would leave my first comment. I don't know what to say except that I have enjoyed reading. Nice blog. I will keep visiting this blog very often. Thank You PowerBuilder development company
  jackob

Dilbert.com


Back up, start over, find another way

Sometimes you can solve a problem by backing up, starting over and taking a fresh look at the requirements.

How far should you back up? At least to the point you starting having trouble.

Here's a case in point:

Question: How do I get this trigger to work? I get SQLCODE -267 COMMIT/ROLLBACK not allowed within atomic operation.

CREATE TRIGGER trigger_before_insert_t2
   BEFORE INSERT ON t2
   REFERENCING NEW AS new_t2
   FOR EACH ROW
   WHEN ( new_t2.type NOT IN ( SELECT type FROM t1 ) )
BEGIN

   ALTER TABLE t2 
      DROP CONSTRAINT t2_is_a_child_of_t1;

   ALTER TABLE t1  
      DROP CONSTRAINT check_that_t1_type_exists_in_t2;

   INSERT t1 VALUES ( new_t2.type );

   ALTER TABLE t2  
      ADD CONSTRAINT t2_is_a_child_of_t1 
      FOREIGN KEY ( type )  
      REFERENCES t1;

   ALTER TABLE t1  
      ADD CONSTRAINT check_that_t1_type_exists_in_t2 
      CHECK ( type IN ( SELECT type FROM t2 ) );

END;

INSERT t2 VALUES ( 1, 100 );

COMMIT/ROLLBACK not allowed within atomic operation
SQLCODE=-267, ODBC 3 State="42000"

First Wrong Answer...

ALTER TABLE implies COMMIT, so try wrapping the ALTER TABLE statements with EXECUTE IMMEDIATE statements.

EXECUTE IMMEDIATE is wonderful for solving "you can't do that here" problems, like coding a FORWARD TO statement inside a stored procedure.

Yabbut...

SQLCODE -267 is not just a syntax issue, it is a semantic one:
  • The "INSERT t2" statement is an atomic operation,
  • and therefore, since the INSERT fires the trigger,
  • the whole trigger execution is part of the same atomic operation,
  • and since EXECUTE IMMEDIATE doesn't move the ALTER TABLE statements outside the trigger execution, say, to another thread or connection,
  • the ALTER TABLE statements and their implied commits are still inside the trigger execution
  • and therefore the ALTER TABLE statements and their implied commits are still within the atomic operation
  • which is not allowed.

Second Wrong Answer...

ALTER TABLE implies COMMIT, so try moving the code to a CREATE EVENT.

CREATE EVENT is wonderful for solving problems involving commits, like implementing nested transactions. When you use TRIGGER EVENT to execute an event, and that event does a commit, it doesn't affect the caller's transaction (where "caller" is the code that issued the TRIGGER EVENT)... the reason is, TRIGGER EVENT starts a new connection to run the event, and a transaction (inserts, updates, ..., commit) run on that connection is separate from the transaction running on the caller's connection.

TRIGGER EVENT is like a whole new user, one who works out of sight, doesn't need a keyboard or a monitor, and never asks for a raise.

Yabbut...

Events run asynchronously,
  • which means TRIGGER EVENT is a "fire and forget" operation with no guaranteed starting time and no guaranteed end time,
  • and not only that, it never returns to the caller
  • so the caller can't tell when it's safe to proceed.
So what's the big deal? Here's the big deal: The CREATE TRIGGER was coded with the BEFORE clause to guarantee the inner "INSERT t1" runs before the outer "INSERT t2".

If the "INSERT t1" is moved into a CREATE EVENT, it might (and probably will) execute after the outer "INSERT t2".

Are you sure? Let's try it!

In the following, all the code from the CREATE TRIGGER has been moved into a CREATE EVENT, and
  • the trigger uses the TRIGGER EVENT statement on lines 34 and 35 start the event,
  • the funky argument list on line 35 is used to pass the new value of t2.type to the event
  • where the EVENT_PARAMETER logic on lines 9 and 10 converts the funky argument back into an integer.
The code on lines 12 to 19 isn't quite "all the code" from the trigger; these two ALTER statements have been left out altogether because the SET TEMPORARY OPTION WAIT_FOR_COMMIT statement on line 42 is a much easier, safer and more efficient way to temporarily turn off foreign-key-checking:
ALTER TABLE t2 
      DROP CONSTRAINT t2_is_a_child_of_t1;

   ALTER TABLE t2  
      ADD CONSTRAINT t2_is_a_child_of_t1 
      FOREIGN KEY ( type )  
      REFERENCES t1;

CREATE EVENT event_on_insert_t2
HANDLER BEGIN

   DECLARE @string_type VARCHAR ( 128 );
   DECLARE @type        INTEGER;

   MESSAGE 'EVENT started' TO CONSOLE;

   SET @string_type = COALESCE ( EVENT_PARAMETER ( '@passed_type' ), '0' );
   SET @type        = CAST ( @string_type AS INTEGER );

   ALTER TABLE t1  
      DROP CONSTRAINT check_that_t1_type_exists_in_t2;

   INSERT t1 VALUES ( @type );

   ALTER TABLE t1  
      ADD CONSTRAINT check_that_t1_type_exists_in_t2 
      CHECK ( type IN ( SELECT type FROM t2 ) );

   MESSAGE 'EVENT finished' TO CONSOLE;

END;

CREATE TRIGGER trigger_before_insert_t2
   BEFORE INSERT ON t2
   REFERENCING NEW AS new_t2
   FOR EACH ROW
   WHEN ( new_t2.type NOT IN ( SELECT type FROM t1 ) )
BEGIN

   MESSAGE 'TRIGGER started' TO CONSOLE;

   TRIGGER EVENT event_on_insert_t2 
      ( @passed_type = STRING ( new_t2.type ) );

   MESSAGE 'TRIGGER finished' TO CONSOLE;

END;

MESSAGE 'Test started' TO CONSOLE;
SET TEMPORARY OPTION WAIT_FOR_COMMIT = 'ON';
MESSAGE 'INSERT started' TO CONSOLE;
INSERT t2 VALUES ( 1, 100 );
MESSAGE 'INSERT finished' TO CONSOLE;
COMMIT;

No joy!

The WAIT_FOR_COMMIT did work as advertised (it delayed the referential integrity error until the COMMIT)

...but the error still happened:

No primary key value for foreign key 't2_is_a_child_of_t1' in table 't2'
SQLCODE=-194, ODBC 3 State="23000"
Line 6, column 1
COMMIT

Test started
INSERT started
TRIGGER started
TRIGGER finished
EVENT started
INSERT finished

Why?

Here's why:
  • The "TRIGGER finished" message appeared before "EVENT started"
  • which means the trigger finished executing before the event even started,
  • and even worse, the "INSERT finished" appeared before "EVENT finished"
  • which probably means INSERT t2 (the child) ran before the event had time to INSERT t1 (the parent)
  • which probably means there was no row in t1 when it hit the COMMIT
  • which probably explains the "No primary key value for foreign key" error.
No, it definitely explains the error :)

But wait, it gets worse!

There's no "EVENT finished" message at all,
  • which means the event connection started but never finished
  • because the event connection was blocked by a "Lock on t1" (Foxhound said so).

  • But wait... no other connection touched t1!
  • Oh, yes they did... the connection with the CREATE TRIGGER executed "SELECT type FROM t1" in the WHEN clause
  • and even a read-only SELECT is enough to grab something called a "schema lock" on the table.
  • A schema lock is a pretty feeble kind of lock,
  • it doesn't prevent another connection from doing a SELECT,
  • it doesn't even block an UPDATE,
  • but a schema lock is strong enough to block ALTER TABLE (because the docs say so),
  • so... by the very fact that the trigger did a SELECT FROM t1,
  • the event was blocked before it even got started.

Talk about useless!

The event started late, and when it did run, it couldn't do anything.

It's time...

It's time to stop, back up, start over, find another way.

Back up all the way to the point the tables existed but not the CREATE TRIGGER:
(I know, you've been screaming "SHOW ME THE TABLES!" for quite some time now, so here they are.)

CREATE TABLE t1 (
   type            INTEGER NOT NULL PRIMARY KEY,
   CONSTRAINT check_that_t1_type_exists_in_t2 
      CHECK ( type IN ( SELECT type FROM t2 ) ) );

CREATE TABLE t2 (
   id              INTEGER NOT NULL PRIMARY KEY,
   type            INTEGER NOT NULL,
   CONSTRAINT t2_is_a_child_of_t1 
      FOREIGN KEY ( type )  
      REFERENCES t1 );

INSERT t1 VALUES ( 100 );

Constraint 'check_that_t1_type_exists_in_t2' violated: Invalid value in table 't1'
SQLCODE=-1091, ODBC 3 State="23000"

INSERT t2 VALUES ( 1, 100 );

No primary key value for foreign key 't2_is_a_child_of_t1' in table 't2'
SQLCODE=-194, ODBC 3 State="23000"
Here's what's going on:
  • There is a FOREIGN KEY relationship between t2 (the child) and t1 (the parent)
  • which means you can't INSERT t2 if there isn't already a single row in t1 with a matching value in type.
  • There is also a CHECK constraint which says the reverse:
  • you can't INSERT t1 if there isn't already one or more rows in t2 with a matching value in type.

Abort! Abort! I say again, Abort!


Or, if you prefer... Danger, Will Robinson!



How about Catch 22? You can't INSERT t2 before you INSERT t1, and you can't INSERT t1 before you INSERT t2.

From the end user's point of view, it's a perfectly reasonable request.

From a database designer's point of view, it's like those steenking fishhook foreign key relationships, only harder... and sharper... and more painful... like fishhooks in real life.



How about an epiphany?

Here's one: The WAIT_FOR_COMMIT option might not help with INSERT t1...

SET TEMPORARY OPTION WAIT_FOR_COMMIT = 'ON';

INSERT t1 VALUES ( 100 );

Constraint 'check_that_t1_type_exists_in_t2' violated: Invalid value in table 't1'
SQLCODE=-1091, ODBC 3 State="23000"

but it does let you get past INSERT t2 without an error, at least until you do a COMMIT...

SET TEMPORARY OPTION WAIT_FOR_COMMIT = 'ON';

INSERT t2 VALUES ( 1, 100 );

-- OK so far, but...

COMMIT;

No primary key value for foreign key 't2_is_a_child_of_t1' in table 't2'
SQLCODE=-194, ODBC 3 State="23000"
Line 7, column 1
-- OK so far, but...
COMMIT

The key word is AFTER

Here's another epiphany: Code a CREATE TRIGGER that runs after INSERT t2 finishes, but before the COMMIT, a trigger that does the INSERT t1 so the COMMIT doesn't crash out.

Step by step:
  1. Change the CREATE TRIGGER from BEFORE to AFTER,
  2. don't bother with EXECUTE IMMEDIATE,
  3. don't bother with CREATE EVENT,
  4. dump the ALTER TABLE statements so only thing left inside the trigger is the INSERT t1,
    CREATE TABLE t1 (
       type            INTEGER NOT NULL PRIMARY KEY,
       CONSTRAINT check_that_t1_type_exists_in_t2 
          CHECK ( type IN ( SELECT type FROM t2 ) ) );
    
    CREATE TABLE t2 (
       id              INTEGER NOT NULL PRIMARY KEY,
       type            INTEGER NOT NULL,
       CONSTRAINT t2_is_a_child_of_t1 
          FOREIGN KEY ( type )  
          REFERENCES t1 );
    
    CREATE TRIGGER trigger_after_insert_t2
       AFTER INSERT ON t2
       REFERENCING NEW AS new_t2
       FOR EACH ROW
       WHEN ( new_t2.type NOT IN ( SELECT type FROM t1 ) )
    BEGIN
       INSERT t1 ( type ) VALUES ( new_t2.type );
    END;
    
  5. use SET TEMPORARY WAIT_FOR_COMMIT = 'ON' before your INSERT t2 statements,
  6. and don't code INSERT t1 outside the trigger... let the trigger do it.
    SET TEMPORARY OPTION WAIT_FOR_COMMIT = 'ON';
    INSERT t2 VALUES ( 1, 100 );
    COMMIT;
    INSERT t2 VALUES ( 2, 100 );
    COMMIT;
    INSERT t2 VALUES ( 3, 200 );
    COMMIT;
    INSERT t2 VALUES ( 4, 200 );
    COMMIT;
    SET TEMPORARY OPTION WAIT_FOR_COMMIT = 'OFF';
    

Woohoo! It works!


SELECT * FROM t1;
SELECT * FROM t2;

type
100
200

id,type
1,100
2,100
3,200
4,200

Credits...


This article started life as a Q&A conversation "ALTER TABLE within trigger" on the SQL Anywere Forum.

Volker Barth suggested that "Instead of the "wait_on_commit" option, one might also declare the FK with the CHECK ON COMMIT clause..."

Thomas Duemesnil suggested using an INSTEAD OF trigger instead of... [snork! pun intended]... the traditional AFTER trigger used here.

I take full (dis)credit for the CREATE EVENT suggestion, for which this article is offered as penance.

Dilbert.com



Wednesday, June 13, 2012

Sometimes the tool you need is right in front of you


Email To: Coworkers
Subject: Remember that bug I had last week, the one where I was hoping for an epiphany?
No epiphany whatsoever.

Here's the symptom: When the main Foxhound service starts for the first time, it launches the Activation service and the Activation page is displayed.

When the user enters a registration code and clicks on a button on the Activation page, the HTML and JavaScript code was supposed to re-launch the Activation service to check the input, but instead it always launched the main Foxhound service again...

...and that service still thinks it's the "first time" and it launches the Activation service with an empty registration code... an endless loop from the user point of view.

Endless fiddling produced no improvement.

So, today, I used Google Chrome's debugging facility - the tool that's "right in front of you"...
  • right mouse on the page - Inspect element,

  • and when the bottom half of the display turns into a debugging frame,

  • switch to the Console tab to see error messages.
When I clicked on the misbehaving button, a red error message would flash momentarily on the Console display. By going click - click - click to get the message to flash - flash - flash, I could see it was saying the function submitF was undefined.

Aha! An HTML INPUT tag said to execute a JavaScript function called submitF() but the actual function was called submitF1().

One steenking character... bet that's never happened to YOU! :


Dilbert.com


It turns out you can tell the Chrome Console to keep the error message on-screen, by right mouse - Preserve log upon navigation:





Monday, June 11, 2012

COMPUTE: A Bed-Time Story

Question: Why do databases get so messed up over the years?

Answer: Because software rusts.

Once upon a time there was a software program that came in four "editions": Beta Edition, Evaluation Edition, Basic Edition and Extended Edition.

The Engineers were exceedingly lazy so instead of creating four different versions of the program, they only created one version, and they made it pretend to be whichever edition the customer asked for.

Whenever the program code needed to know which edition it was pretending to be, it would look inside this table:

CREATE TABLE edition (
   one_row_only   INTEGER NOT NULL DEFAULT 1 PRIMARY KEY CHECK ( one_row_only = 1 ),
   edition_name   VARCHAR ( 100 ) NOT NULL CHECK ( edition_name IN (
                     'Beta',
                     'Evaluation',
                     'Basic',
                     'Extended' ) ) );
For example...
IF EXISTS ( SELECT * FROM edition WHERE edition_name = 'Extended' ) THEN
   ... do some thing only the Extended Edition can do
END IF;
Then, one day, Marketing demanded that customers ask permission to use the Beta and Evaluation editions, and pay for the Basic and Extended editions. A registration key feature was added with a one-character code for the type of key:
CREATE TABLE edition (
   one_row_only   INTEGER NOT NULL DEFAULT 1 PRIMARY KEY CHECK ( one_row_only = 1 ),
   key_code       VARCHAR ( 1 ) NOT NULL CHECK ( key_code IN (
                     'B',        -- Beta
                     'E',        -- Evaluation
                     'S',        -- Basic
                     'X' ) ),    -- Extended
   edition_name   VARCHAR ( 100 ) NOT NULL CHECK ( edition_name IN (
                     'Beta',
                     'Evaluation',
                     'Basic',
                     'Extended' ) ) );

IF EXISTS ( SELECT * FROM edition WHERE edition_name = 'Extended' ) THEN
   ... do some thing only the Extended Edition can do
END IF;
A brilliant Alpha Engineer noticed
  • that key_code would always determine edition_name

  • and therefore edition_name was redundant, and dangerous,
and so, feeling full of Simplicity, Honor, Integrity and Truth, the Alpha Engineer eliminated edition_name:
CREATE TABLE edition (
   one_row_only   INTEGER NOT NULL DEFAULT 1 PRIMARY KEY CHECK ( one_row_only = 1 ),
   key_code       VARCHAR ( 1 ) NOT NULL CHECK ( key_code IN (
                     'B',        -- Beta
                     'E',        -- Evaluation
                     'S',        -- Basic
                     'X' ) ) );  -- Extended
This caused some grumbling among the Gamma Engineers who had to change the code:
IF EXISTS ( SELECT * FROM edition WHERE key_code = 'X' ) THEN
   ... do some thing only the Extended Edition can do
END IF;
Some time passed, and two new kinds of registration keys were introduced, R and U:
CREATE TABLE edition (
   one_row_only   INTEGER NOT NULL DEFAULT 1 PRIMARY KEY CHECK ( one_row_only = 1 ),
   key_code       VARCHAR ( 1 ) NOT NULL CHECK ( key_code IN (
                     'B',        -- Beta
                     'E',        -- Evaluation
                     'R',        -- Evaluation Renewal
                     'S',        -- Basic
                     'U',        -- Upgrade Basic To Extended
                     'X' ) ) );  -- Extended
Although key_code still determined edition, the program code had to be changed again and this caused more discontent among the Gammas:
IF EXISTS ( SELECT * FROM edition WHERE key_code IN ( 'U', 'X' ) ) THEN
   ... do some thing only the Extended Edition can do
END IF;
Then one day, Marketing dropped this bombshell:
  • the Beta Edition was eliminated,

  • the Evaluation Edition was replaced with the Rental Edition, and

  • four new "version upgrade" registration keys were introduced to make old customers pay for the new version.
Now they were back to only three editions, Rental, Basic and Extended... but with 8 different key_code values! (and the promise of more to come):
CREATE TABLE edition (
   one_row_only   INTEGER NOT NULL DEFAULT 1 PRIMARY KEY CHECK ( one_row_only = 1 ),
   key_code       VARCHAR ( 1 ) NOT NULL CHECK ( key_code IN (
                     'R',        -- Rental
                     'A',        -- Upgrade Basic from version 1.0 to version 2 
                     'C',        -- Upgrade Basic from version 1.1 to version 2
                     'S',        -- Basic
                     'D',        -- Upgrade Extended from version 1.0 to version 2 
                     'F',        -- Upgrade Extended from version 1.1 to version 2
                     'U',        -- Upgrade Basic To Extended
                     'X' ) ) );  -- Extended

"The peasants are revolting!"

Oh yes they were... you could tell the Gammas were disgruntled by the graffiti they coded into the program:
IF EXISTS ( SELECT * FROM edition WHERE key_code IN ( 'F', 'U', 'X', 'D' ) ) THEN
   ... do some thing only the Extended Edition can do
END IF;

To the rescue!

One of the Beta Engineers suggested using the CREATE TABLE COMPUTE clause to reintroduce edition_name without requiring any extra effort to insert or update the value:
CREATE TABLE edition (
   one_row_only   INTEGER NOT NULL DEFAULT 1 PRIMARY KEY CHECK ( one_row_only = 1 ),
   key_code       VARCHAR ( 1 ) NOT NULL CHECK ( key_code IN (
                     'R',        -- Rental
                     'A',        -- Upgrade Basic from version 1.0 to version 2 
                     'C',        -- Upgrade Basic from version 1.1 to version 2
                     'S',        -- Basic
                     'D',        -- Upgrade Extended from version 1.0 to version 2 
                     'F',        -- Upgrade Extended from version 1.1 to version 2
                     'U',        -- Upgrade Basic To Extended
                     'X' ) ),    -- Extended
   edition_name   VARCHAR ( 100 ) NOT NULL COMPUTE ( 
                     CASE 
                        WHEN key_code IN ( 'R'                ) THEN 'Rental' 
                        WHEN key_code IN ( 'A', 'C', 'S'      ) THEN 'Basic' 
                        WHEN key_code IN ( 'D', 'F', 'U', 'X' ) THEN 'Extended' 
                        ELSE                                         'Unknown'
                     END CASE ) ); 

INSERT edition ( key_code ) VALUES ( 'F' );

SELECT * FROM edition;

one_row_only,key_code,edition_name
1,'F','Extended'
The Gammas did have to make one more change, but it was a good change, back to the Old Way Of Doing Things when code was readable and programs were maintainable:
IF EXISTS ( SELECT * FROM edition WHERE edition_name = 'Extended' ) THEN
   ... do some thing only the Extended Edition can do
END IF;

And everyone lived happily ever after!

The moral of this story is that computed columns are your friend.

Dilbert.com



Friday, June 8, 2012

SQL Anywhere Advanced Search

No, this isn't an article about Full Text Search, it's a new home for some targeted web searches...

SQL Anywhere Technical Documents:
 
   
Help: Go to DocCommentXchange then choose a "Collection" and use the Contents, Index or Search tabs to find topics.
   
Version 12.0.1 Help:
 
   
SQL Anywhere Forum:
 
   
Glenn Paulley's blog:
 
   
This blog:
 
   
Foxhound:
 
   
Dilbert.com



Wednesday, June 6, 2012

New Gems From The Doc Mines

Here are the latest changes to the SQL Anywhere Technical Documents page on this blog:

...many thanks to Glenn Paulley for providing the HTMLinspiration!

More Good News!

The Google Custom Search facility seems to be working better: not only is it picking up new articles promptly but the weird behavior mentioned here seems to be gone...

... now when you search on "select", instead of only getting one or two hits you get "About 3,450,000 results".

Clearly, MapReduce has a sense of humor; if you try to scroll past page 5 the story changes: "About 46 results" :)






Monday, June 4, 2012

Example: OUTPUT TO From VB, Part 2

Part 1 of this article explained how to run SQL Anywhere's SELECT and OUTPUT TO statements via dbisql.exe from inside a Visual Basic application.

This article describes how to use the same technique to run much larger SQL scripts, scripts that might not fit on one command line...

...but first, here's a recap of Part 1:


Question: How do I write the result set from a SQL Anywhere 10 SELECT statement to a local tab-delimited text file from a Visual Basic 2008 application running on a client workstation?

Answer...
Here's the code:
Public Class Form1
    Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Shell("""C:\Program Files\SQL Anywhere 10\win32\dbisql.exe"" " _
                      & "-c ""ENG=ddd10;DBN=ddd10;UID=dba;PWD=sql"" " _
                      & "SELECT * FROM t1 ORDER BY pkey; " _
                      & "OUTPUT TO 'C:\data\select_output.txt' DELIMITED BY '\X09'")
    End Sub
End Class



Part 2: Run a SQL Script Via Interactive SQL


SQL Anywhere's dbisql utility let's you put SQL statements inside text files and then load-and-execute those scripts via the READ statement.

The READ statement...
Those last two features are great for building large hierarchies of SQL scripts that are customized at runtime, but this article only talks about running one single script from the command line.

Here's what the VB code looks like when the SELECT and OUTPUT TO statements have been moved into a script file:
Public Class Form1
    Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Shell("""C:\Program Files\SQL Anywhere 10\win32\dbisql.exe"" " _
                      & "-c ""ENG=ddd10;DBN=ddd10;UID=dba;PWD=sql"" " _
                      & "READ ENCODING Cp1252 ""C:\data\select_output.sql""")
    End Sub
End Class

Here's what the C:\data\select_output.sql file looks like (OK, it's not a "much larger SQL script" like was promised earlier, but it COULD be larger, with no limit):
SELECT * FROM t1 ORDER BY pkey; 
OUTPUT TO 'C:\data\select_output.txt' DELIMITED BY '\X09';

The whole business of the double-doublequotes and the "&" concatenation operator is explained in Part 1 of this article. For the record, the original dbisql.exe command line has been changed from this (CAUTION: these are one-line commands that have been split to avoid display problems on different browsers)
"C:\Program Files\SQL Anywhere 10\win32\dbisql.exe" 
-c "ENG=ddd10;DBN=ddd10;UID=dba;PWD=sql" 
SELECT * FROM t1 ORDER BY pkey; OUTPUT TO 'C:\data\select_output.txt' DELIMITED BY '\X09';
to this
"C:\Program Files\SQL Anywhere 10\win32\dbisql.exe" 
-c "ENG=ddd10;DBN=ddd10;UID=dba;PWD=sql" 
READ ENCODING Cp1252 "C:\data\select_output.sql"
and then massaged to fit inside the VB call to Shell().


The ENCODING Cp1252 is semantic sugar: On Windows computers it is sometimes necessary to specify the "code page" to get READ to work properly. This is like waving a dead chicken over the keyboard: it never hurts, it sometimes helps, and very few people care understand why.

Tip: Like the OUTPUT TO statement, the READ statement is executed by the Interactive SQL (dbisql.exe) utility, not by the SQL Anywhere database server. This can lead to confusion, like "Why can't I put an OUTPUT TO statement inside a stored procedure? Answer: Because stored procedures are executed inside the SQL Anywhere server, and the server doesn't understand OUTPUT TO."

The fog lifts once you realize that, from the server's point of view, dbisql.exe is just another steenking client application, and that OUTPUT TO and READ statements are parsed and executed on the client... the server never sees them.


Friday, June 1, 2012

Example: OUTPUT TO From VB

Question: How do I write the result set from a SQL Anywhere 10 SELECT statement to a local tab-delimited text file from a Visual Basic 2008 application running on a client workstation?

Wrong Answer: Use the UNLOAD TO statement - that writes the file onto the computer where the SQL Anywhere server is running, not the computer where the VB application is running. You could mess around with \\UNC\file specifications, but chances are you'll never get it to work... especially if the SQL Anywhere server is running as a service and doesn't have permission to do file I/O across the network.

Another Wrong Answer: Use UNLOAD INTO CLIENT FILE statement - nice try, but no cigar! The question said "SQL Anywhere 10" and the cross-border version of UNLOAD wasn't introduced until Version 11.

Right Answer: Well, one right answer, one that works with SQL Anywhere 10, and 11, and 12...

That's a whole lotta words to describe one line of code in VB:
Public Class Form1
    Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Shell("""C:\Program Files\SQL Anywhere 10\win32\dbisql.exe"" -c ""ENG=ddd10;DBN=ddd10;UID=dba;PWD=sql"" SELECT * FROM t1 ORDER BY pkey; OUTPUT TO 'C:\data\select_output1.txt' DELIMITED BY '\X09'")
    End Sub
End Class
...but ya gotta admit, it's one UGLY line of code, and it looks even worse in Visual Studio 2008 which doesn't wrap long lines of code (nor should it):


OK, so how about we make it "one statement" instead of "one line", with some line breaks to make it readable:
Public Class Form1
    Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Shell("""C:\Program Files\SQL Anywhere 10\win32\dbisql.exe"" " _
                      & "-c ""ENG=ddd10;DBN=ddd10;UID=dba;PWD=sql"" " _
                      & "SELECT * FROM t1 ORDER BY pkey; " _
                      & "OUTPUT TO 'C:\data\select_output.txt' DELIMITED BY '\X09'")
    End Sub
End Class

Here's how the two calls to Shell() were constructed:
  1. First, a Windows command was written and tested using a *.BAT file:
    "C:\Program Files\SQL Anywhere 10\win32\dbisql.exe" -c "ENG=ddd10;DBN=ddd10;UID=dba;PWD=sql" SELECT * FROM t1 ORDER BY pkey; OUTPUT TO 'C:\data\select_output.txt' DELIMITED BY '\X09';
    

  2. Then, the command was turned into a Visual Basic string literal by wrapping it in a pair of "doublequotes":
    ""C:\Program Files\SQL Anywhere 10\win32\dbisql.exe" -c "ENG=ddd10;DBN=ddd10;UID=dba;PWD=sql" SELECT * FROM t1 ORDER BY pkey; OUTPUT TO 'C:\data\select_output.txt' DELIMITED BY '\X09';"
    

  3. but that's wasn't right, because Visual Basic would choke on the single doublequotes inside the string, so those nested doublequotes had to be doubled-up like this:
    """C:\Program Files\SQL Anywhere 10\win32\dbisql.exe"" -c ""ENG=ddd10;DBN=ddd10;UID=dba;PWD=sql"" SELECT * FROM t1 ORDER BY pkey; OUTPUT TO 'C:\data\select_output1.txt' DELIMITED BY '\X09'"
    

  4. Then, the string was wrapped in a call to Shell():
    Shell("""C:\Program Files\SQL Anywhere 10\win32\dbisql.exe"" -c ""ENG=ddd10;DBN=ddd10;UID=dba;PWD=sql"" SELECT * FROM t1 ORDER BY pkey; OUTPUT TO 'C:\data\select_output1.txt' DELIMITED BY '\X09'")
    

  5. That executes OK, but it's hard to read, so the string was broken up into 4 lines:
    Shell("""C:\Program Files\SQL Anywhere 10\win32\dbisql.exe"" 
                  -c ""ENG=ddd10;DBN=ddd10;UID=dba;PWD=sql""  
                  SELECT * FROM t1 ORDER BY pkey;  
                  OUTPUT TO 'C:\data\select_output1.txt' DELIMITED BY '\X09'")
    

  6. but that didn't work because VB needs a space and an underscore " _" to break a statement onto two lines, like this:
    Shell("""C:\Program Files\SQL Anywhere 10\win32\dbisql.exe"" _ 
                  -c ""ENG=ddd10;DBN=ddd10;UID=dba;PWD=sql"" _  
                  SELECT * FROM t1 ORDER BY pkey; _  
                  OUTPUT TO 'C:\data\select_output1.txt' DELIMITED BY '\X09'")
    

  7. but that STILL didn't work because VB doesn't let you break a one string literal across four lines, so one separate string literal per line was required, like this:
    Shell("""C:\Program Files\SQL Anywhere 10\win32\dbisql.exe"" " _ 
                  "-c ""ENG=ddd10;DBN=ddd10;UID=dba;PWD=sql"" " _  
                  "SELECT * FROM t1 ORDER BY pkey; " _  
                  "OUTPUT TO 'C:\data\select_output1.txt' DELIMITED BY '\X09'")
    

  8. but (again) that STILL didn't work because Shell() needed the four string literals to be concatenated into one string argument, using the VB concatenation operator "&" like this:
    Shell("""C:\Program Files\SQL Anywhere 10\win32\dbisql.exe"" " _ 
                  & "-c ""ENG=ddd10;DBN=ddd10;UID=dba;PWD=sql"" " _  
                  & "SELECT * FROM t1 ORDER BY pkey; " _  
                  & "OUTPUT TO 'C:\data\select_output1.txt' DELIMITED BY '\X09'")
    

  9. ...and that works again (whew!)

But HOW does it work?

Here's how:
Public Class Form1
    Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Shell("""C:\Program Files\SQL Anywhere 10\win32\dbisql.exe"" " _
                      & "-c ""ENG=ddd10;DBN=ddd10;UID=dba;PWD=sql"" " _
                      & "SELECT * FROM t1 ORDER BY pkey; " _
                      & "OUTPUT TO 'C:\data\select_output.txt' DELIMITED BY '\X09'")
    End Sub
End Class
  • The VB Shell() function call starting on line 3 needs at least one argument, called "Pathname" in the docs: Name of the program to execute, together with any required arguments and command-line switches. Pathname can also include the drive and the directory path or folder. (There are three other optional arguments described in the docs, all of which look interesting... for another day.)

  • The string "C:\Program Files\SQL Anywhere 10\win32\dbisql.exe" specifies exactly where to find the SQL Anywhere 10 utility program dbisql.exe on the workstation. If you trust the PATH, you can code just use "dbisql.exe" or "dbisql" or even leave off the "quotes". In the code shown here, the "quotes" are necessary because of the spaces in the path specification, and the full path is specified because, well, it's been many years since the Windows PATH environment variable was stable and trustworthy.

  • The connection string on line 4 specifies how dbisql is to connect to the SQL Anywhere database. In this example, it's a simple connection to a local database, -c "ENG=ddd10;DBN=ddd10;UID=dba;PWD=sql", but it could easily include a LINKS parameter to point across the network to another computer.

  • The SELECT statement on line 5 shows how dbisql can be run in batch mode: just code your SQL statement right on the command line, and dbisql will execute it without opening up the GUI.

  • The Interactive SQL OUTPUT TO statement on line 6 shows how you can code more than one statement on the dbisql command line if you separate them with semicolons.

  • The SELECT statement is sent to the database, and SQL Anywhere returns the result set to dbisql. Then, dbisql runs the OUTPUT TO statement locally (inside dbisql, not on the SQL Anywhere server) to format and write the result set to a tab-delimited text file... which is what the DELIMITED BY '\X09' is for.