Example: UNLOAD INTO VARIABLE and EXECUTE IMMEDIATE

Way back in January I made a New Year's Resolution to "Post More Examples Here", so here we go (and I'm set for another year, right?)...

---

Question: How do I write a SQL Anywhere function that accepts a SELECT statement in a string parameter and returns a string containing the result set with each row delimited by [square brackets] and separated by commas, and adjacent columns separated by tilde '^' characters?

Here's a demonstration of how it should work:

CREATE TABLE person (
   first_name    VARCHAR ( 100 ) PRIMARY KEY,
   member_since  DATE,
   last_seen     DATE,
   visits        INTEGER );

INSERT person VALUES ( 'Joe',  '2009-11-10', '2012-02-01', 1  );
INSERT person VALUES ( 'Mary', '2009-11-08', '2012-01-31', 45 );
INSERT person VALUES ( 'Tom',  '2009-11-11', '2012-01-30', 12 );
COMMIT;

SELECT to_string ( 'SELECT * FROM Person ORDER BY first_name' ) AS result;

result
[Joe^2009-11-10^2012-02-01^1],[Mary^2009-11-08^2012-01-31^45],[Tom^2009-11-11^2012-01-30^12]

Answer: Phrases like "row delimited by" and "columns separated by" should make every SQL Anywhere developer think of the UNLOAD and OUTPUT statements... in fact, if you look up DELIMITED BY in the Help that's what you'll find.

In this case, OUTPUT won't work because it's unique to the ISQL utility and can't be embedded inside a function, so UNLOAD it will have to be.

Let's tackle the problem step-by-step:

Step 1: Build A Prototype UNLOAD SELECT

Here's a standalone UNLOAD built with a few clauses described in the Help:

UNLOAD
   SELECT * FROM Person ORDER BY first_name
   TO 'c:/temp/result.txt'
   ROW DELIMITED BY '],['
   DELIMITED BY '^'
   ESCAPES OFF
   HEXADECIMAL OFF
   QUOTES OFF;

The UNLOAD and TO clauses on lines 1 and 3 are wrapped around the SELECT to tell SQL Anywhere to send the entire result set to a text file.

The ROW DELIMITED BY clause on line 4 really should be called "ROW TERMINATED BY" because it tells SQL Anywhere to place the character string '],[' at the right end of each output row including the last. This isn't exactly what we want, but it's the only row-separator-or-terminator clause that UNLOAD gives us... so we'll take it for now and deal with it later.

The DELIMITED BY clause on line 5 really should be called "COLUMNS SEPARATED BY" because it tells SQL Anywhere to put the tilde '^' between adjacent columns... in this case, that's exactly what we want.

The remaining three clauses on lines 6 through 8 tell SQL Anywhere to write all the column values as simple strings with no extra formatting: no backslash-escape sequences, no \0x hexadecimal formatting and no 'single quotes' around string values.

Here's what the output file looks like... close, but no cigar:

Joe^2009-11-10^2012-02-01^1],[Mary^2009-11-08^2012-01-31^45],[Tom^2009-11-11^2012-01-30^12],[

Step 2: Fix The Formatting

There are two problems with the output: The leading '[' is missing from the first row and an extra ',[' is tacked on to the end of the last row.

Those problems can't be solved by fiddling with UNLOAD clauses, it requires code:

BEGIN
   DECLARE @result LONG VARCHAR;
   UNLOAD
      SELECT * FROM Person ORDER BY first_name
      INTO VARIABLE @result
      ROW DELIMITED BY '],['
      DELIMITED BY '^'
      ESCAPES OFF
      HEXADECIMAL OFF
      QUOTES OFF;
   SELECT STRING ( '[', LEFT ( @result, LENGTH ( @result ) - 2 ) ) AS result;    
END;

The INTO VARIABLE clause on line 5 puts the output from UNLOAD into a local variable instead of a file, and the SELECT STRING statement fixes the two problems by tacking a '[' onto the front and chopping off the trailing ',['.

Now the formatting is OK:

result
'[Joe^2009-11-10^2012-02-01^1],[Mary^2009-11-08^2012-01-31^45],[Tom^2009-11-11^2012-01-30^12]'

Step 3: Turn It Into A General-Purpose Function

The UNLOAD statement expects a SELECT statement, not a string containing a SELECT statement like the function's supposed to use...

...what to do, oh, what to do?

Answer: Turn the whole UNLOAD statement into a string and use EXECUTE IMMEDIATE to run it!

Here's how:

CREATE FUNCTION to_string (
   IN @select LONG VARCHAR )
   RETURNS  LONG VARCHAR 
BEGIN
   DECLARE @unload LONG VARCHAR;
   DECLARE @result LONG VARCHAR;
   SET @unload = STRING (
      'UNLOAD ',
      @select,
      ' INTO VARIABLE @result ',
      ' DELIMITED BY ''^'' ',
      ' ROW DELIMITED BY ''],['' ',
      ' ESCAPES OFF HEXADECIMAL OFF QUOTES OFF' );
   EXECUTE IMMEDIATE @unload;
   RETURN STRING ( '[', LEFT ( @result, LENGTH ( @result ) - 2 ) );    
END;

The SET statement on lines 7 through 13 builds the entire UNLOAD statement and the EXECUTE IMMEDIATE on line 14 runs it.

The input SELECT is added to the UNLOAD on line 9.

The delimiter strings on lines 11 and 12 have their single quotes ''doubled up'' because they are embedded in the outer 'string literals'.

The magic of EXECUTE IMMEDIATE allows you to embed a reference to a local variable inside the statement to be executed (INTO VARIABLE @result on line 10) and then refer to that variable again afterwards: the LEFT ( @result, ... on line 15.

---