Обсуждение: 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...