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

No comments: