The following CLI commands with give an java.sql.SQLException: ORA-01425: escape character must be character string of length 1. CLI commands:- > var criteria = new ResourceCritiera(); > criteria.addFilterResourceTypeName('JBossAS Server'); > var resources = ResourceManager.findResourcesByCriteria(criteria); It appears that we are escaping the escape character in the Oracle query: SELECT r FROM Resource r WHERE ( r.inventoryStatus = InventoryStatus.COMMITTED AND LOWER( r.resourceType.name ) like 'JBossAS Server' ESCAPE '\\' ) *Issue tracker ticket#349326
Needs more investigation
Jay, first step:can you run the CLI test suite against oracle
This issue is mentioned on https://jira.jboss.org/jira/browse/JBPAPP-219 and the related Hibernate JIRA. Postgres 8.2 and earlier[0], MySQL, and possibly other databases treat a backslash in a regular string as an escape character even though the SQL standard says not to. Escaping the backslash, like the code in CriteriaQueryGenerator.getQueryString() does, makes it work for those databases but breaks some standard-conforming database like Oracle. For Postgres 8.2 (not sure about earlier versions), you can use the following to force standard compliance on like in 8.3 and later. Which in theory should make it work with no escaping the backslashes in the query. ALTER DATABASE rhq SET standard_conforming_strings='on' For reference, you can do this in MySQL 5.0.1 too or later with the NO_BACKSLASH_ESCAPES option. [0] http://www.postgresql.org/docs/8.2/static/runtime-config-compatible.html#GUC-STANDARD-CONFORMING-STRINGS
Database setups to test: 1) Oracle 10 2) PG with default settings 3) PG with standard_conforming_strings='on'
Also, Oracle (10g at least) appears not to support prefixing a string literal with E to indicate that it's backslash escaped when it's part of an escape clause. So "... ESCAPE E'\\'" won't work.
For reference, coding in this area also relates to: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2674
Fix provides mechanism for the criteria query generator to perform dbType specific logic. Add ability to store/retrieve defaultDatabaseType via DatabaseTypeFactory. Add db vendor specific handling of the ESCAPE character and allow property based override. Change behavior to handle Postgres (nonstandard) and Oracle (standard) differences.
This bug was previously known as http://jira.rhq-project.org/browse/RHQ-2445
commit 15c5ab632360523f7ad37112700a1f2220163fb0
qa -> jweiss
QA Verified, on build QA-8436: rhqadmin.redhat.com:7080$ resources one row Resource: id: 10003 name: jweiss-rhel1.usersys.redhat.com RHQ Server, JBoss AS 4.2.3.GA default (0.0.0.0:2099) version: 4.2.3.GA resourceType: JBossAS Server rhqadmin.redhat.com:7080$
Mass-closure of verified bugs against JON.