You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "LUTTER, Steffen" <st...@sap.com> on 2012/07/17 17:54:45 UTC
derby getProcedureColumns
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(). 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
Re: derby getProcedureColumns
Posted by Rick Hillegas <ri...@oracle.com>.
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
>>
>
>
Re: derby getProcedureColumns
Posted by Katherine Marsden <km...@sbcglobal.net>.
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
>