db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jrgchip <c...@datamology.com>
Subject How many escapes needed for string argument to SYSCS_IMPORT_TABLE?
Date Fri, 30 Jan 2009 19:53:46 GMT

I am having trouble escaping the string value for the FILENAME argument to
the SYSCS_IMPORT_TABLE.

The following tests were run in IJ, but get the same results in a Java app.

The following command:
  CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'TEMPTBL',
'C:/Chip's/TERMS.dat', null, null, null, 0);
results in the following error:
  ERROR 42X01: Syntax error: Encountered "s" at line 1, column 61.

Understood...the apost in "Chip's" is not escaped.

But the following command with the escaped apost:
  CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'TEMPTBL',
'C:/Chip''s/TERMS.dat', null, null, null, 0);
results in the following error:
  ERROR 38000: The exception 'java.sql.SQLException: Syntax error:
Encountered "s" at line 2, column 99.' was thrown while evaluating an
expression.
  ERROR 42X01: Syntax error: Encountered "s" at line 2, column 99.

When I embed this in a Java app and capture the stack trace, I can see that
this failed in EmbedStatement.executeStatement()...whereas the original
error was in EmbedStatement.execute().  It is in execute() that the passed
SQL is originally parsed and prepared.  So it's clear that this attempt got
further.  But it looks like the SQL got parsed a second time and, now that
the original escape was removed, failed.

Turns out I have to use the following with the apost double-escaped:
  CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'TEMPTBL',
'C:/Chip''''s/TERMS.dat', null, null, null, 0);

This works, but how am I to know how many parses will be done by the engine? 
If twice, I have to use the 4 aposts.  But if only once, I'd have to use 2
aposts.  (And, if trice, I'd have to use 8 aposts!)

Is there doc about how to properly escape the passed string?  The doc I
found (re "Capitalization and special characters") doesn't say anything more
than "Within a character string, to represent a single quotation mark or
apostrophe, use two single quotation marks."

Besides, shouldn't the engine handle it all once I escape the first time?

-- 
View this message in context: http://www.nabble.com/How-many-escapes-needed-for-string-argument-to-SYSCS_IMPORT_TABLE--tp21754463p21754463.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.


Mime
View raw message