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