SQL Anywhere: Avoiding Quotes

Monday, January 31, 2011

Avoiding Quotes

Several SQL Anywhere procedures and functions take SQL statements as string arguments; for example,

sa_describe_query() returns information about all the columns in a result set,
rewrite() shows how the query optimizer is going to manipulate your query, and
graphical_plan() returns the execution plan used by the query engine.

If you have a long and complex SQL statement in your code, it can be a challenge to re-code it as a 'quoted string literal' in order to pass it to one of these functions... every embedded single quote must be doubled-up, and quotes that are already embedded inside string literals (and already doubled-up) must be quadrupled.

Here's an example:


SELECT STRING ( 
          '<HTML><HEAD><TITLE>',
          'All the demo database''s sales identifiers',
          '</TITLE></HEAD><TABLE>',
          LIST ( 
             STRING ( 
                '<TR><TD>',  SalesOrders.ID,
                '</TD><TD>', SalesOrderItems.LineID,
                '</TD><TD>', Products.ID,
                '</TD></TD>' ),
             ''
             ORDER BY SalesOrders.ID,
                   SalesOrderItems.LineID,
                   Products.ID ),
          '</TABLE></HTML>' )
  FROM SalesOrderItems
          INNER JOIN SalesOrders
                  ON SalesOrders.ID = SalesOrderItems.ID
          INNER JOIN Products
                  ON Products.ID = SalesOrderItems.ProductID

Here's how it has to be coded if you're going to pass the SELECT as a string literal to the rewrite() function:


SELECT rewrite ( '
SELECT STRING ( 
          ''<HTML><HEAD><TITLE>'',
          ''All the demo database''''s sales identifiers'',
          ''</TITLE></HEAD><TABLE>'',
          LIST ( 
             STRING ( 
                ''<TR><TD>'',  SalesOrders.ID,
                ''</TD><TD>'', SalesOrderItems.LineID,
                ''</TD><TD>'', Products.ID,
                ''</TD></TD>'' ),
             ''''
             ORDER BY SalesOrders.ID,
                   SalesOrderItems.LineID,
                   Products.ID ),
          ''</TABLE></HTML>'' )
  FROM SalesOrderItems
          INNER JOIN SalesOrders
                  ON SalesOrders.ID = SalesOrderItems.ID
          INNER JOIN Products
                  ON Products.ID = SalesOrderItems.ProductID
' )

But... there's an easier way: Copy the SELECT as-is (no doubling-up of the quotes required) to a separate text file, and then code xp_read_file() as the argument to rewrite().

Here, the SELECT has been copied to a text file called select.txt:


SELECT rewrite ( xp_read_file ( 'select.txt' ) )

By the way, here's what rewrite() had to say about the SELECT; no need for any joins at all since the SalesOrderItems table has all the columns that are needed:


select
  STRING(
  '<HTML><HEAD><TITLE>',
  'All the demo database''s sales identifiers',
  '</TITLE></HEAD><TABLE>',
  LIST(
  STRING(
  '<TR><TD>',SalesOrderItems_1.ID,
  '</TD><TD>',SalesOrderItems_1.LineID,
  '</TD><TD>',SalesOrderItems_1.ProductID,
  '</TD></TD>'),
  '' order by
  SalesOrderItems_1.ID asc,
  SalesOrderItems_1.LineID asc,
  SalesOrderItems_1.ProductID asc),
  '</TABLE></HTML>')
  from SalesOrderItems as SalesOrderItems_1

SQL Anywhere

Pages

Monday, January 31, 2011

Avoiding Quotes

No comments:

Categories