DatabaseMetaData.searchStringEscape

[Geoff hendrey <ge...@yahoo.com>]

On the advice I got back from my last email, I tried to use getIdentfierQuoteString (which returns ").


So I tried escaping the undescore like this:
m.getColumns(null, null, tableName,  "%\"_\"XXX"); //find columns ending in _XXX

but that doesn't work, nor does is really seem to make any sense. I really need the searchEscapeString. Without it, how can anyone use getColumns? It's *very* common for table names to have an underscore, but unless I can escape the underscore, it will be treated as a wildcard for any single character.

I must be missing something.

-geoff

Re: DatabaseMetaData.searchStringEscape

[Kristian Waagan <Kr...@Sun.COM>]

Geoff hendrey wrote:
> On the advice I got back from my last email, I tried to use getIdentfierQuoteString (which returns ").
> 
> 
> So I tried escaping the undescore like this:
> m.getColumns(null, null, tableName,  "%\"_\"XXX"); //find columns ending in _XXX
> 
> but that doesn't work, nor does is really seem to make any sense. I really need the searchEscapeString. Without it, how can anyone use getColumns? It's *very* common for table names to have an underscore, but unless I can escape the underscore, it will be treated as a wildcard for any single character.
> 
> I must be missing something.

Hello Geoff,

Actually, I don't think there is any support for using an escape 
character in the metadata calls. DatabaseMetaData.getSearchStringEscape 
will always return the empty string, and the queries used do not have an 
ESCAPE-clause.

I quickly hacked the code by adding the ESCAPE-clause, and then I were 
able to make it work by using "%\\_%" as the pattern. By specifying 
another character, you can get away with things like "%#_%". A comment 
in the code suggests that Derby does not have a default escape 
character, but I don't know anything about that.


Unless there already is a Jira for this, maybe you could add a request 
for it?
(https://issues.apache.org/jira/browse/DERBY)
Adding the functionality is easy, but I have not considered any update 
issues and how to change or "disable" the escape character if that is 
needed.


A workaround would be to get a set of columns with getColumns, then do 
the filtering you can't do in the database because of the escape 
problems in your application code...



regards,
-- 
Kristian

> 
> -geoff
> 
> 

Re: DatabaseMetaData.searchStringEscape

[David Van Couvering <da...@vancouvering.com>]

Yes, you're right, identifierQuoteString doesn't make sense in this
case.  I thought you were trying to quote the entire identifier.

I don't have an answer for you though, sorry!

David

On 8/4/07, Geoff hendrey <ge...@yahoo.com> wrote:
> On the advice I got back from my last email, I tried to use getIdentfierQuoteString (which returns ").
>
>
> So I tried escaping the undescore like this:
> m.getColumns(null, null, tableName,  "%\"_\"XXX"); //find columns ending in _XXX
>
> but that doesn't work, nor does is really seem to make any sense. I really need the searchEscapeString. Without it, how can anyone use getColumns? It's *very* common for table names to have an underscore, but unless I can escape the underscore, it will be treated as a wildcard for any single character.
>
> I must be missing something.
>
> -geoff
>
>
>