Thread: How to retrieve column names for tables with underscores

Hi,

this is probably more of a generic JDBC question, but since I'm trying to solve this with Postgres I'm posting it here:

I have two tables

table1_  (yes there is an underscore at the end)
table12

Now when I try to retrieve the columns for "table_" the columns of table12 are also returned because the table name
parameterfor DatabaseMetaData.getColumns() is used as a LIKE expression. 

Is there a way (preferrably standard JDBC) to avoid the LIKE evaluation with the getColumns() call e.g. by specifying
anescape character?. I tried to replace the underscore with the standard backslash escape ("table1\_") but that didn't
work.

Or is the only reliable way to check the value of the returned TABLE_NAME whether it matches my input value?

Thanks
Thomas

Hi Thomas,

Am Donnerstag, 24. März 2011 schrieb Thomas Kellerer:
> backslash escape ("table1\_") but that didn't work.
IMHO it should be "table1\\_"?

Bye Thomas

On Thu, 24 Mar 2011, ml-tb wrote:

> Hi Thomas,
>
> Am Donnerstag, 24. M?rz 2011 schrieb Thomas Kellerer:
>> backslash escape ("table1\_") but that didn't work.
> IMHO it should be "table1\\_"?
>

The escape needed depends on the value of standard_conforming_strings.
You should use this call to find the escape needed:

http://download.oracle.com/javase/6/docs/api/java/sql/DatabaseMetaData.html#getSearchStringEscape()

Kris Jurka

Kris Jurka wrote on 24.03.2011 21:18:
>> Am Donnerstag, 24. M?rz 2011 schrieb Thomas Kellerer:
>>> backslash escape ("table1\_") but that didn't work.
>> IMHO it should be "table1\\_"?
>>
>
> The escape needed depends on the value of standard_conforming_strings. You should use this call to find the escape
needed:
>
> http://download.oracle.com/javase/6/docs/api/java/sql/DatabaseMetaData.html#getSearchStringEscape()
>
That works perfectly.

Actually the fact that "it didn't work" right way was that I used replaceAll() instead of replace() somehow forgetting
thatreplaceAll() needs a regex... 

Interesting enough, the setting of standard_conforming_strings does not seem to affect this at all. Am I missing
something?

Thanks
Thomas

> Actually the fact that "it didn't work" right way was that I used
> replaceAll() instead of replace() somehow forgetting that replaceAll() needs
> a regex...
>
> Interesting enough, the setting of standard_conforming_strings does not seem
> to affect this at all. Am I missing something?

Doesn't seem to affect what, exactly? It definitely affects how
backslashes are interpreted, and unless the current version of the
JDBC driver injects the 'E' escape syntax (with the 'E' prefix,
backslash escape sequences are interpreted regardless of the setting)
in a cavalier manner into the LIKE expression comparisons for
getColumns() (the one I have handy doesn't seem to), you should see
different behavior.

---
Maciek Sakrejda | System Architect | Truviso

1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com

On 3/24/2011 1:45 PM, Thomas Kellerer wrote:
>
> Kris Jurka wrote on 24.03.2011 21:18:
>> The escape needed depends on the value of
>> standard_conforming_strings. You should use this call to find the
>> escape needed:
>>
>>
>> http://download.oracle.com/javase/6/docs/api/java/sql/DatabaseMetaData.html#getSearchStringEscape()
>
>>
>
> Interesting enough, the setting of standard_conforming_strings does
> not seem to affect this at all. Am I missing something?

Nope, that's my memory failing me.  The comment in getSearchStringEscape
escape gives some details on why this doesn't depend on
standard_conforming_strings.  Turning a search pattern into a literal
happens as a separate step and it's at that time you need to know the
value of s_c_s.

     public String getSearchStringEscape() throws SQLException
     {
         // This method originally returned "\\\\" assuming that it
         // would be fed directly into pg's input parser so it would
         // need two backslashes.  This isn't how it's supposed to be
         // used though.  If passed as a PreparedStatement parameter
         // or fed to a DatabaseMetaData method then double backslashes
         // are incorrect.  If you're feeding something directly into
         // a query you are responsible for correctly escaping it.
         // With 8.2+ this escaping is a little trickier because you
         // must know the setting of standard_conforming_strings, but
         // that's not our problem.

         return "\\";
     }

Kris Jurka

Maciek Sakrejda wrote on 24.03.2011 22:02:
>> Interesting enough, the setting of standard_conforming_strings does not seem
>> to affect this at all. Am I missing something?
>
> Doesn't seem to affect what, exactly?

The escape character returned by getSearchStringEscape()

>It definitely affects how
> backslashes are interpreted, and unless the current version of the
> JDBC driver injects the 'E' escape syntax (with the 'E' prefix,
> backslash escape sequences are interpreted regardless of the setting)
> in a cavalier manner into the LIKE expression comparisons for
> getColumns() (the one I have handy doesn't seem to), you should see
> different behavior.

Regardless of the setting of standard_conforming_strings passing \_ to getColumns() always works properly.

See Kris' answer for details.

Regards
Thomas

>>It definitely affects how
>> backslashes are interpreted, and unless the current version of the
>>JDBC driver injects the 'E' escape syntax (with the 'E' prefix,
>>backslash escape sequences are interpreted regardless of the setting)
>>in a cavalier manner into the LIKE expression comparisons for
>> getColumns() (the one I have handy doesn't seem to), you should see
>>different behavior.

>Regardless of the setting of standard_conforming_strings passing \_ to
getColumns() always works properly.


Standard conforming strings makes sequences such as '\n' be treated as
literals unless you prefix an "E".  When you use LIKE the '_' and '%' take
on special properties (which they do not have in a regular string) -
regardless of standard conforming strings since the use of LIKE means you
intend to make use of those special characters.  In order to avoid the
special meaning of those two characters LIKE defines an escape character.
Since PostgreSQL controls both, you request the escape from PostgreSQL and
it provides what you need to perform the escape properly based upon the
current system configuration.

I hope this helps and that I'm not getting anything really wrong...