db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: derby getProcedureColumns
Date Wed, 18 Jul 2012 13:30:34 GMT
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
>>
>
>


Mime
View raw message