Re: Escape character weirdness

David,
There is a long story with this one, let me try to take a stab at it. First
of all the solution as you have discovered is to use the brackets for
escaping. The problem of course is that you can't return brackets from the
getSearchStringEscape method, they would be useless. There is one scenario
where it is necesary _and_ possible to work arround this limitation, and
that is in the metadata calls themselves. After scratching our heads at this
for a while this is what we came up with:

From the documentation:
http://msdn2.microsoft.com/en-US/library/ms378896(SQL.90).aspx

"This getSearchStringEscape method is specified by the getSearchStringEscape
method in the java.sql.DatabaseMetaData interface.

This method is used only for metadata pattern searches. It returns "\". A
string search pattern can escape wildcards ("%" and "_") and provide them as
literals by prepending a backslash. This translates "\%" to "[%]" and "\_"
to "[_]". "

Here is the problem. Sql Server uses brackets '[]' to escape. The JDBC spec
is very clear in requiring a single character to escape to be returned from
getSearchStringEscape (it would be useless for this method to return
brackets). The solution? like most drivers we are returning '\' as our
escape character, when you use this in your metadata methods to escape
wildcards we will do a single pass replacing all '\%' characters with '[%]'
to get this feature to work properly.

example: use the DatabaseMetaData.getColumns() to retrieve the columns'
information to a table. getColumns() uses search pattern. Wildcards
('_','%','[') wich must be masked. This is where you can use the
getSearchStringEscape method.

As always we are open to suggestions.
Thanks,

--
Angel Saenz-Badillos [MS] DataWorks
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging: http://weblogs.asp.net/angelsb/




"David Thielen" <david@xxxxxxxxxxxx> wrote in message
news:6iqn329pdcsn02snoqrmmot7m4iovvktdg@xxxxxxxxxx
Hi;

Yes, we are trying to do that. We have a reporting engine -
www.windwardreports.com and some of our customers need this -
specifically with the _ for now but I want to get them all working.

According to the SqlServer docs and the JdbcDriver, \% \_ etc should
work - but they don't.

??? - thanks - dave


On Tue, 11 Apr 2006 10:28:23 -0700, Joe Weinstein <joeNOSPAM@xxxxxxx>
wrote:


David Thielen wrote:


Hi;

This works:
SELECT * FROM demo WHERE (name LIKE 'Da[%]ve')

This dies not:
SELECT * FROM demo WHERE (name LIKE 'Da\%ve') - it returns no rows

Did you try:
SELECT * FROM demo WHERE (name LIKE 'Da\\%ve')


Yet DatabaseMetaData.getSearchStringEscape(); returns "\\".

Any ideas as to what is going on?

Sql Server 2000 and latest JDBC driver.

thanks - dave

Just to be clear, what are you trying to do? Do you have names that really
include the '%' character? You're right. I tried a 'LIKE ' query, trying
to specify the pattern with an escaped '%' to find values with a real '%'
in them, and couldn't....

I get:

Driver version is 1.0.809.102
We inserted 'JOE%JOE'
We inserted 'JOE_JOE'
trying select * from #foo where bar = 'JOE%JOE'
The specific value equals output is: 'JOE%JOE'
trying prepared select * from #foo where bar like 'JOE%JOE'
prepared query like output is: 'JOE%JOE'
prepared query like output is: 'JOE_JOE'
trying prepared select * from #foo where bar like 'JOE\\%JOE'
(finds nothing)
trying prepared select * from #foo where bar like 'JOE[%]JOE'
prepared query bracketed output is: 'JOE%JOE'
(finds the one row)

from this code:

c = d.connect("jdbc:sqlserver://JOE:1433", props );

DatabaseMetaData dd = c.getMetaData();
System.out.println("Driver version is " +
dd.getDriverVersion() );

Statement stmt = c.createStatement();
stmt.execute("create table #foo(bar varchar(50) primary key)");

PreparedStatement p = c.prepareStatement("insert into #foo
values(?)");
p.setString(1, "JOE%JOE");
p.executeUpdate();
p.setString(1, "JOE_JOE");
p.executeUpdate();
p.close();

ResultSet r = stmt.executeQuery("select * from #foo where bar
like '%'");
while (r.next()) System.out.println("We inserted '" +
r.getString(1) + "'");
r.close();

r = stmt.executeQuery("select * from #foo where bar =
'JOE%JOE'");
System.out.println("trying select * from #foo where bar =
'JOE%JOE'");
while (r.next()) System.out.println("The specific value equals
output is: '" + r.getString(1) + "'");
r.close();

p = c.prepareStatement("select * from #foo where bar like ?");
p.setString(1, "JOE%JOE");
r = p.executeQuery();
System.out.println("trying prepared select * from #foo where
bar like 'JOE%JOE'");
while (r.next()) System.out.println("prepared query like output
is: '" + r.getString(1) + "'");
r.close();
p.setString(1, "JOE\\\\%JOE");
r = p.executeQuery();
System.out.println("trying prepared select * from #foo where
bar like 'JOE\\\\%JOE'");
while (r.next()) System.out.println("prepared query escaped
output is: '" + r.getString(1) + "'");
r.close();

p.setString(1, "JOE[%]JOE");
System.out.println("trying prepared select * from #foo where
bar like 'JOE[%]JOE'");
r = p.executeQuery();
while (r.next()) System.out.println("prepared query bracketed
output is: '" + r.getString(1) + "'");
r.close();


david@at-at-at@windward.dot.dot.net
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com


.

Relevant Pages

  • Re: Help with Date Parameter
    ... "Enter Business Date of Report"? ... When I run the query, records for all dates are displayed instead of just ... You need to set the query criteria to: ... Brackets will not be necessary (but are still good ...
    (microsoft.public.access.queries)
  • Re: can this be done with a subquery?
    ... Your SQL is fine but it's approximately my first query. ... MaxAs LatestDate ... *further brackets* within a subquery ...
    (microsoft.public.access.queries)
  • Re: Now it works... now it doesnt... What is up with this query
    ... I would like to use this query again. ... FROM [SELECT ItemNumber, CountAS Stores ... The "save parser" chokes on any brackets within a subquery ...
    (microsoft.public.access.queries)
  • Re: crosstab query parameter error
    ... Access will at times add extra brackets without your help changing this ... If this is for your crosstab query, ... Reserved words or contain illegal characters to try to slide them past Jet ...
    (microsoft.public.access.queries)
  • Re: Getting query SQL from a JDBC PreparedStatement
    ... I want to get the SQL query text from ... a PreparedStatement (which is a SybPreparedStatement in this ... connection when asking for a PreparedStatement. ...
    (comp.lang.java.databases)