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