Bug 535785 (RHQ-2445)
| Summary: | CLI gives ORA-01425 error when sending criteria to Oracle 10g | ||
|---|---|---|---|
| Product: | [Other] RHQ Project | Reporter: | dsteigne |
| Component: | CLI | Assignee: | Jay Shaughnessy <jshaughn> |
| Status: | CLOSED CURRENTRELEASE | QA Contact: | Jeff Weiss <jweiss> |
| Severity: | medium | Docs Contact: | |
| Priority: | high | ||
| Version: | 1.3 | CC: | cwelton, dajohnso, jlivings |
| Target Milestone: | --- | ||
| Target Release: | --- | ||
| Hardware: | All | ||
| OS: | All | ||
| URL: | http://jira.rhq-project.org/browse/RHQ-2445 | ||
| Whiteboard: | Branch RHQ_1_3_0_GA_CP | ||
| Fixed In Version: | 2.4 | Doc Type: | Bug Fix |
| Doc Text: | Story Points: | --- | |
| Clone Of: | Environment: |
JON Server version 2.3 installed on Oracle 10g
|
|
| Last Closed: | 2010-08-12 16:55:44 UTC | Type: | --- |
| Regression: | --- | Mount Type: | --- |
| Documentation: | --- | CRM: | |
| Verified Versions: | Category: | --- | |
| oVirt Team: | --- | RHEL 7.3 requirements from Atomic Host: | |
| Cloudforms Team: | --- | Target Upstream Version: | |
| Embargoed: | |||
| Bug Depends On: | |||
| Bug Blocks: | 557793 | ||
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. |
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