Hello Thomas, Thank you for your response.
> No, currently the escape character can not be changed or disabled. I > will fix this in the next release. My plans are: > > - Allow to disable the escape mechanism using ESCAPE ''. This would be a proprietary feature of H2 (and maybe other databases like the above mentioned Oracle) and is fine, if the SQL-statements are running only on H2 i.e. need not be portable. > - Add a system property h2.defaultEscape (default: \) This fits my use case, if the default can also be disabled with this. Even though in case of a webapp (with embedded H2) within a servlet- engine like Tomcat the place for this configuration option is away from the expected place (web.xml) > - Add a feature request: "Compatibility: use different LIKE ESCAPE > characters depending on the mode (disable for Derby, HSQLDB, DB2, > Oracle)." This is very fine. I prefer this, because it can be specified in the connection string, which is already (i.e. even must be) specific for the used DB and (web)app. A heretical thought: What about a (new) Compatibility mode MSSQLServer (in which the default ESCAPE char is disabled)? Regards Charly On 5 Aug., 20:05, Thomas Mueller <[email protected]> wrote: > Hi, > > That's strange... I thought '\' is the standard escape character for > the SQL standard, but it looks like it's not. Currently PostgreSQL and > MySQL work like H2. The PostgreSQL documentation says you can disable > the escape character, but when I test it it doesn't > work:http://www.postgresql.org/docs/8.4/static/functions-matching.html#FUN... > "It's also possible to select no escape character by writing ESCAPE > ''" - this seems to work for Oracle however. > > > Is there another connection parameter I could use to disable the > > default escape char? .. Or could such connection parameter provided? > > No, currently the escape character can not be changed or disabled. I > will fix this in the next release. My plans are: > > - Allow to disable the escape mechanism using ESCAPE ''. > - Add a system property h2.defaultEscape (default: \) > - Add a feature request: "Compatibility: use different LIKE ESCAPE > characters depending on the mode (disable for Derby, HSQLDB, DB2, > Oracle)." > > I will also extend the documentation for LIKE ESCAPE: > > " > When comparing with LIKE, the wildcards characters are _ (any one character) > and % (any characters). The database uses an index when comparing with LIKE > except if the operand starts with a wildcard. To search for the characters % > and > _, the characters need to be escaped. The default escape character is > \ (backslash). > To select no escape character, use ESCAPE '' (empty string). > " > > Regards, > Thomas > > On Sun, Aug 2, 2009 at 1:06 PM, charly<[email protected]> wrote: > > > Hello, > > at the moment I am using MS Access and MS SQLserver as Databases > > (development and production). > > Since H2 seems to be much better than Access and other (java) > > databases, I would like to use H2 interchangeable for MS Access and > > SQLserver. > > I am using "LIKE" clauses without "ESCAPE" keywords, and Access and > > SQLserver do not have default escape characters so I have not dealt > > with escape characters. Yet H2 has the default character "\", which > > will generate an SQLException, if used in the like pattern without > > escaping itself. > > I have looked for a way to disable the default escape character: > > The following databases do not have a default escape character: > > > mssqlserver: > >http://msdn.microsoft.com/en-us/library/aa933232(SQL.80).aspx > > hsqldb: > >http://hsqldb.org/doc/guide/ch09.html > > derby: > >http://db.apache.org/derby/docs/dev/ref/ref-single.html#rrefsqlj23075 > > db2: > >http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.... > > oracle: > >http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/condit... > > > So I tried the H2 Compatibility Modes for these DBs (e.g. ;MODE=DB2 ) > > in the connection string, but I had no success in disabling the > > default escape char. > > > Is there another connection parameter I could use to disable the > > default escape char? .. Or could such connection parameter provided? > > Thanks. > > Best Regards > > Charly --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/h2-database?hl=en -~----------~----~----~----~------~----~------~--~---
