How many escapes needed for string argument to SYSCS_IMPORT_TABLE?

[jrgchip <ch...@datamology.com>]

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.

Re: How many escapes needed for string argument to SYSCS_IMPORT_TABLE?

[Knut Anders Hatlen <Kn...@Sun.COM>]

jrgchip <ch...@datamology.com> writes:

> 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?

Right, you should be able to do

  CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(...,'C:/Chip''s/TERMS.dat',...);

or

  PreparedStatement ps = c.prepareStatement(
    "CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(...,?,...)");
  ps.setString(X, "C:/Chip's/TERMS.dat"; // no escape needed

But because of a bug in the import code this doesn't work. I have logged
it as DERBY-4042.

The problem is that the SYSCS_IMPORT_TABLE procedure builds some SQL
statements internally, and then it just puts quotes around the strings
with no checking of whether the strings contain quotes. So currently the
string is parsed once more than you'd expect, and you need twice as many
quotes.

-- 
Knut Anders