db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-5862) There is no way to escape the special _ and % characters in DatabaseMetaData search strings
Date Fri, 20 Jul 2012 15:01:39 GMT

    [ https://issues.apache.org/jira/browse/DERBY-5862?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13419194#comment-13419194

Rick Hillegas commented on DERBY-5862:

If we add a knob for configuring a search string escape character, then we could rewrite the
metadata queries in metadata.properties to take advantage of this character. Everywhere where
we currently say "LIKE ?" we could say instead "LIKE ? ESCAPE ?".

For backward compatibility, we would still need to handle the default case where there is
no search string escape character. Unfortunately we can't hand an empty string to the ESCAPE
clause. A query like the following raises an error because the SQL Standard specifies that
the ESCAPE argument must be a 1 character string (see part 2 of the Standard, section 8.5
<like predicate>, general rule 3.b.i.1):

    select tablename from sys.systables where tablename like ? escape '';

Note that the metadata queries run in a special "internal" mode. We could probably get away
with allowing an empty string for the ESCAPE argument when we are running in that special
"internal" mode.

A candidate solution for this issue would then be:

1) Add a knob so that applications can configure a search string escape character.

2) Add some special logic so that when we are running in "internal" mode, you can pass an
empty string to the ESCAPE clause. The meaning of this special internal syntax would be the
same as if you omitted the ESCAPE clause.

3) Change all of the "LIKE ?" clauses in metadata.properties to be "LIKE ? ESCAPE ?".

4) Change all of the code which invokes the metadata queries so that we stuff the second ?
with the result of calling DatabaseMetaData.getSearchStringEscape().

This solution seems straightforward but tedious.

A variation of this approach (to eliminate steps 3 and 4) would be to modify "internal" mode
(or create a special "metadata internal" mode). In this mode, the LIKE operator would always
add an ESCAPE clause when DatabaseMetaData.getSearchStringEscape() evaluates to something
other than the empty string.

In any event, we would want to write a battery of test cases to verify the escaping behavior
of all of the DatabaseMetaData calls which take search strings as arguments.

Addressing this issue appears to me to be at least a 2 week mini-project.

> There is no way to escape the special _ and % characters in DatabaseMetaData search strings
> -------------------------------------------------------------------------------------------
>                 Key: DERBY-5862
>                 URL: https://issues.apache.org/jira/browse/DERBY-5862
>             Project: Derby
>          Issue Type: Bug
>          Components: JDBC
>    Affects Versions:
>            Reporter: Rick Hillegas
> And DatabaseMetaData.getSearchStringEscape() returns an empty string. We should add a
way to configure a search string escape character. See the discussion on this email thread:

This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira


View raw message