On 7/17/12 6:28 PM, Katherine Marsden wrote:
On 7/17/2012 8:54 AM, LUTTER, Steffen wrote:
Hello,
I have an issue with stored procedures in derby and like to ask for
your confirmation. When querying the procedure columns, according to
the JDBC standard we escape pattern characters, like _ and % if
necessary. The escape character is here taken from the function
getSearchStringEscape().
Derby returns an empty string for getSearchStringEscape() and the
comments indicate we don't have a default escape value. I am not sure
why. I am also not understanding where = comes in the picture
according to the comment below:
/**
* This is the string that can be used to escape '_' or '%' in
* the string pattern style catalog search parameters.
we have no default escape value, so = is the end of the next line
* <P>The '_' character represents any single character.
* <P>The '%' character represents any sequence of zero or
* more characters.
* @return the string used to escape wildcard characters
*/
public String getSearchStringEscape() {
return "";
}
I think it would be worthwhile to file a Jira for Derby to allow
escape of the wildcard characters in the pattern unless someone
understands why we don't support it.
It may be significant that the special _ and % characters have the same
meaning in the SQL LIKE predicate. See part 2 of the SQL Standard,
section 8.5 (<like predicate>). There the Standard does not define an
escape character either. Instead, it allows an optional ESCAPE clause so
that applications can declare their own escape characters.
I don't think we can impose a default escape character without breaking
backward compatibility. It would probably be sufficient to configure the
escape character at the database (or higher) level by introducing a new
Derby property. I have filed
https://issues.apache.org/jira/browse/DERBY-5862 to track this issue.
Thanks,
-Rick
The problem is, that derby doesn’t seem to accept the escaping in
case of _ (underscore), and uses the escape characters within the
match which leads to the situation that the stored proc is not found.
Example:
We have a stored procedure MY_PROC.
getConnection ().getMetaData ().getProcedureColumns (null, null,
”MY\\_PROC” ,"%") => *Stored proc is not found*
getConnection ().getMetaData ().getProcedureColumns (null, null,
”MY_PROC” ,"%") => *Stored proc is found*
The first case is the problem, as the _ needs escaping. For the
second case it works, even though theoretically also procedures
called MY-PROC, MY+PROC, MYXPROC would match.
Have I overseen something? Can you confirm?
http://docs.oracle.com/javase/6/docs/api/java/sql/DatabaseMetaData.html
Many thanks in advance,
Steffen
_________________________________________________________________
*Steffen Lutter*| Developer | Semantic Layer | TIP BAT EIM | +33 1 41
25 38 68
