SQL Anywhere Feedback - Third Bi-Annual Request for UNLOAD TO String

Author

Third Bi-Annual Request for UNLOAD TO String

Breck Carter [TeamSybase]

2005-07-17, 1:23 pm

It is time for my 2005 bi-annual request for UNLOAD TABLE and UNLOAD
SELECT ... TO a string, in addition to TO a file which is currently
available.

Not a week goes by where I don't wish I could turn a SELECT into a
string, one row or many, for debugging and many other purposes such as
MobiLink diagnostics and HTML generation.

Java has its toString() functionality, and so should SQL.

Breck Of Constant Sorrow

===== 2003 Bi-Annual Request for UNLOAD TO String...

From: "Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com>
Newsgroups: sybase.public.sqlanywhere. product_futures_disc
ussion
Subject: LOAD and UNLOAD string variables
Date: 14 Sep 2003 13:23:33 -0700

LOAD and UNLOAD string variables

LOAD TABLE FROM, UNLOAD TABLE TO and UNLOAD SELECT TO a string
variable, perhaps with an option to specify a size limit on UNLOAD to
prevent runaway memory usage.

Reasons: LOAD and UNLOAD are very powerful and flexible, and useful
for many things besides loading and dumping raw data from and to
external files. For example, a single UNLOAD SELECT can create a
formatted document in HTML or XML. Some problems are easier to solve
with strings than with result sets. And OS files are difficult to use
reliably in a multi-user environment; e.g., naming, garbage
collection, etc. If strings local to the connection could be used,
rather than OS files, a new world of opportunity would open up.

Skeptics might argue that this would be a dangerous use of RAM. That
implies that because someone *might* code a badly-behaving program we
shouldn't *anyone* access to a new feature. If someone has the RAM,
let them use it in an efficient manner; the OS is using the same RAM
for the files but (AFAIK) a much less efficient manner.

Breck

bcarter@risingroad.com
Mobile and Distributed Enterprise Database Applications
www.risingroad.com

===== 2001 Bi-Annual Request for UNLOAD TO String...

Newsgroups: sybase.public.sqlanywhere. product_futures_disc
ussion
From: Breck Carter <NOSPAM__bcar...@bcarter.com>
Date: Sat, 03 Feb 2001 10:54:53 -0500
Local: Sat,Feb 3 2001 10:54 am
Subject: UNLOAD expression

First of all, a big THANK YOU to whoever thought of the "UNLOAD
select-statement" feature. It is GREAT!

And now, since unloading to a file is not much different than
unloading to a string (well, in MY simple mind anyway), how about
UNLOAD as an expression returning a LONG VARCHAR?

This would facilitate storing result sets as columns; e.g., building
an HTML file from a SELECT and storing it somewhere. Plus lots of
other stuff.

Here's an example of limited usefulness, only to show the syntax:

DECLARE @f LONG VARCHAR;

SET @f = UNLOAD
SELECT *
FROM EMPLOYEE
DELIMITED BY ' '
ESCAPES OFF
FORMAT ASCII
QUOTES OFF;

If you need brackets to ease parsing (say, when it's buried in a
SELECT list), so be it...

SET @f = UNLOAD (
SELECT *
FROM EMPLOYEE
DELIMITED BY ' '
ESCAPES OFF
FORMAT ASCII
QUOTES OFF );

It should be possible to correlate the query with the outside world so
it CAN be buried in a SELECT list and still be VERY useful.

Breck

Breck [TeamSybase]
www.bcarter.com
bcar...@bcarter.com

=====

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Mark Culp

2005-07-19, 9:23 am

What about changing your arbitrary select statement into
a derived table, and then using the list() operator to change
the whole thing into a string.

Example, say I have a select statement that has columns a, b, & c

select list( a || b || c )
from ( < my_big_select_statem
ent_with_cols_a_b_an
d_c> ) T;

Then, of course, you need to assign the result to a variable, as in:

select list( a || b || c )
into v
from ( < my_big_select_statem
ent_with_cols_a_b_an
d_c> ) T;
--
Mark Culp
SQLAnywhere Research and Development
-------------------------------------------------------------------------
** Whitepapers, TechDocs, bug fixes are all available through the **
** iAnywhere Developer Community at http://www.ianywhere.com/developer **
-------------------------------------------------------------------------

"Breck Carter [TeamSybase]" wrote:
>
> It is time for my 2005 bi-annual request for UNLOAD TABLE and UNLOAD
> SELECT ... TO a string, in addition to TO a file which is currently
> available.
>
> Not a week goes by where I don't wish I could turn a SELECT into a
> string, one row or many, for debugging and many other purposes such as
> MobiLink diagnostics and HTML generation.
>
> Java has its toString() functionality, and so should SQL.
>
> Breck Of Constant Sorrow
>
> ===== 2003 Bi-Annual Request for UNLOAD TO String...
>
> From: "Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com>
> Newsgroups: sybase.public.sqlanywhere. product_futures_disc
ussion
> Subject: LOAD and UNLOAD string variables
> Date: 14 Sep 2003 13:23:33 -0700
>
> LOAD and UNLOAD string variables
>
> LOAD TABLE FROM, UNLOAD TABLE TO and UNLOAD SELECT TO a string
> variable, perhaps with an option to specify a size limit on UNLOAD to
> prevent runaway memory usage.
>
> Reasons: LOAD and UNLOAD are very powerful and flexible, and useful
> for many things besides loading and dumping raw data from and to
> external files. For example, a single UNLOAD SELECT can create a
> formatted document in HTML or XML. Some problems are easier to solve
> with strings than with result sets. And OS files are difficult to use
> reliably in a multi-user environment; e.g., naming, garbage
> collection, etc. If strings local to the connection could be used,
> rather than OS files, a new world of opportunity would open up.
>
> Skeptics might argue that this would be a dangerous use of RAM. That
> implies that because someone *might* code a badly-behaving program we
> shouldn't *anyone* access to a new feature. If someone has the RAM,
> let them use it in an efficient manner; the OS is using the same RAM
> for the files but (AFAIK) a much less efficient manner.
>
> Breck
>
> bcarter@risingroad.com
> Mobile and Distributed Enterprise Database Applications
> www.risingroad.com
>
> ===== 2001 Bi-Annual Request for UNLOAD TO String...
>
> Newsgroups: sybase.public.sqlanywhere. product_futures_disc
ussion
> From: Breck Carter <NOSPAM__bcar...@bcarter.com>
> Date: Sat, 03 Feb 2001 10:54:53 -0500
> Local: Sat,Feb 3 2001 10:54 am
> Subject: UNLOAD expression
>
> First of all, a big THANK YOU to whoever thought of the "UNLOAD
> select-statement" feature. It is GREAT!
>
> And now, since unloading to a file is not much different than
> unloading to a string (well, in MY simple mind anyway), how about
> UNLOAD as an expression returning a LONG VARCHAR?
>
> This would facilitate storing result sets as columns; e.g., building
> an HTML file from a SELECT and storing it somewhere. Plus lots of
> other stuff.
>
> Here's an example of limited usefulness, only to show the syntax:
>
> DECLARE @f LONG VARCHAR;
>
> SET @f = UNLOAD
> SELECT *
> FROM EMPLOYEE
> DELIMITED BY ' '
> ESCAPES OFF
> FORMAT ASCII
> QUOTES OFF;
>
> If you need brackets to ease parsing (say, when it's buried in a
> SELECT list), so be it...
>
> SET @f = UNLOAD (
> SELECT *
> FROM EMPLOYEE
> DELIMITED BY ' '
> ESCAPES OFF
> FORMAT ASCII
> QUOTES OFF );
>
> It should be possible to correlate the query with the outside world so
> it CAN be buried in a SELECT list and still be VERY useful.
>
> Breck
>
> Breck [TeamSybase]
> www.bcarter.com
> bcar...@bcarter.com
>
> =====
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com

Breck Carter [TeamSybase]

2005-07-19, 9:23 am

On 19 Jul 2005 06:53:20 -0700, Mark Culp
< reply_to_newsgroups_
only_please_nospam_m
ark.culp@iAnywhere.com>
wrote:

>What about changing your arbitrary select statement into
>a derived table, and then using the list() operator to change
>the whole thing into a string.
>
>Example, say I have a select statement that has columns a, b, & c
>
>select list( a || b || c )
>from ( < my_big_select_statem
ent_with_cols_a_b_an
d_c> ) T;
>
>Then, of course, you need to assign the result to a variable, as in:
>
>select list( a || b || c )
>into v
>from ( < my_big_select_statem
ent_with_cols_a_b_an
d_c> ) T;

Oooooo, that's why you're a Code God and we're not :)

Derived tables can have ORDER BY which makes this all possible (that,
and the fact LIST is so powerful now):

BEGIN
DECLARE @s LONG VARCHAR;
SELECT LIST ( STRING ( x, ' * 2 = ', y, '\x0d\x0a' ), '' )
INTO @s
FROM ( SELECT row_num AS x,
row_num * 2 AS y
FROM RowGenerator
ORDER BY x DESC ) AS r;
UNLOAD SELECT @s TO 'c:\temp\s.txt' ESCAPES OFF QUOTES OFF;
END;

255 * 2 = 510
254 * 2 = 508
253 * 2 = 506
....

Breck

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Mark Culp

2005-07-19, 11:23 am

FWIW, you can also put the ORDER BY clause in the list()

BEGIN
DECLARE @s LONG VARCHAR;
SELECT LIST ( STRING ( x, ' * 2 = ', y, '\x0d\x0a' ), '' ORDER BY X DESC )
INTO @s
FROM ( SELECT row_num AS x,
row_num * 2 AS y
FROM RowGenerator ) AS r;
UNLOAD SELECT @s TO 'c:\temp\s.txt' ESCAPES OFF QUOTES OFF;
END;

Which may or may not be useful, depending on what you are doing :-)

- Mark

"Breck Carter [TeamSybase]" wrote:
>
> On 19 Jul 2005 06:53:20 -0700, Mark Culp
> < reply_to_newsgroups_
only_please_nospam_m
ark.culp@iAnywhere.com>
> wrote:
>
>
> Oooooo, that's why you're a Code God and we're not :)
>
> Derived tables can have ORDER BY which makes this all possible (that,
> and the fact LIST is so powerful now):
>
> BEGIN
> DECLARE @s LONG VARCHAR;
> SELECT LIST ( STRING ( x, ' * 2 = ', y, '\x0d\x0a' ), '' )
> INTO @s
> FROM ( SELECT row_num AS x,
> row_num * 2 AS y
> FROM RowGenerator
> ORDER BY x DESC ) AS r;
> UNLOAD SELECT @s TO 'c:\temp\s.txt' ESCAPES OFF QUOTES OFF;
> END;
>
> 255 * 2 = 510
> 254 * 2 = 508
> 253 * 2 = 506
> ...
>
> Breck
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com

Sponsored Links