Notice: This Wiki is now read only and edits are no longer possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.
Difference between revisions of "EclipseLink/Bugs/325167"
< EclipseLink | Bugs
m (→Alternative 7: Use a query hint to disable the userDefined flag) |
m (→Alternative 7: Use a query hint to change the default bind parameter hash char) |
||
(8 intermediate revisions by the same user not shown) | |||
Line 136: | Line 136: | ||
*This may not be feasible and/or supported on all database platforms - some of which do not execute code. However those that support temporary tables (derby/H2 only support global ## temporary tables) should also support procedures | *This may not be feasible and/or supported on all database platforms - some of which do not execute code. However those that support temporary tables (derby/H2 only support global ## temporary tables) should also support procedures | ||
− | ===<font color="green">Alternative 7: Use a query hint to | + | ===<font color="green">Alternative 7: Use a query hint to change the default bind parameter hash char</font>=== |
+ | *See '''[https://bugs.eclipse.org/bugs/attachment.cgi?id=182121&action=diff 20101029 SVN Patch]''' | ||
*This procedure (after a group discussion) would allow the user to not have the native query treated as a Call - where (1-n) # delimiters are converted to ? parameters | *This procedure (after a group discussion) would allow the user to not have the native query treated as a Call - where (1-n) # delimiters are converted to ? parameters | ||
*20101029: Discussion with Chris and Andrei | *20101029: Discussion with Chris and Andrei | ||
Line 143: | Line 144: | ||
====Analysis==== | ====Analysis==== | ||
+ | *Essentially, this change involves making the currently hardcoded '''#''' delimiter - user definable into something like '''%''' for example. | ||
*I will need to familiarize myself with query hint usage and how it relates to our parameter processing first. | *I will need to familiarize myself with query hint usage and how it relates to our parameter processing first. | ||
− | *The user will be able to mix both our '''#''' hash parameter delimiter and use '''#''' exclusively for ''temporary table'' delimiters. | + | *The user will be able to mix both our '''#''' hash parameter delimiter and use '''#''' exclusively for ''temporary table'' delimiters by substituting '''%''' for '''#''' where they actually want parameter binding to still occur. |
− | **SQL = '''UPDATE ... WHERE id= | + | **SQL = '''UPDATE ... WHERE id=% AND (id IN (SELECT ... FROM #TEMP_TABLE))''' |
**converted after parameter binding to.. | **converted after parameter binding to.. | ||
**SQL = '''UPDATE ... WHERE id=32 AND (id IN (SELECT ... FROM #TEMP_TABLE))''' | **SQL = '''UPDATE ... WHERE id=32 AND (id IN (SELECT ... FROM #TEMP_TABLE))''' | ||
Line 169: | Line 171: | ||
</pre> | </pre> | ||
+ | |||
+ | ====Work Items==== | ||
+ | *Update and add new query hint wiki at http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Query_Hints | ||
+ | *Verify named parameter support using "''':'''" | ||
+ | *Verify escaping of "%" in the parameter name is handled - http://old.nabble.com/Issue-with-namedQuery-setParameter-and-string-which-contains-%22'%22-(single-quote)-td19606841.html | ||
+ | ====References==== | ||
+ | http://wiki.eclipse.org/Using_EclipseLink_JPA_Extensions_(ELUG)#Using_Named_Parameters_in_a_Native_Query | ||
===Alternative 8: use standard backslash escaping=== | ===Alternative 8: use standard backslash escaping=== |
Latest revision as of 22:17, 31 October 2010
Contents
- 1 Bug Analysis Document: 325167: Native Query Containing Temporary Table # Identifier is Translated to ? SP Parameter
- 1.1 Document History
- 1.2 Overview
- 1.3 Analysis
- 1.4 Design
- 1.4.1 Alternative 1: Disable select Query userDefined=True flags at Query Construction
- 1.4.2 Alternative 2: Disable userDefined=True by searching queryString for [from #]
- 1.4.3 Alternative 3: Subclass SQLCall for special temporary table query
- 1.4.4 Alternative 4: Extend translation code handling of #
- 1.4.5 Alternative 5: Delimit temporary table with Square brackets like 260263
- 1.4.6 Alternative 6: Use @variable for #temporary table name
- 1.4.7 Alternative 7: Use a query hint to change the default bind parameter hash char
- 1.4.8 Alternative 8: use standard backslash escaping
- 1.4.9 Alternative 9: Use boolean flag similar to shouldProcessTokenInQuotes from 217745
- 1.4.10 Alternative 10: Use existing eclipselink.jdbc.bind-parameters property to stop #temp_table parameter processing
- 1.4.11 Decision
- 1.5 Implementation
- 1.6 Testing
- 1.7 API
- 1.8 GUI
- 1.9 Config files
- 1.10 Documentation
- 1.11 Open Issues
- 1.12 Decisions
- 1.13 Future Considerations
- 1.14 References
Bug Analysis Document: 325167: Native Query Containing Temporary Table # Identifier is Translated to ? SP Parameter
Document History
Date | Author | Version Description & Notes |
---|---|---|
20100914 | Michael O'Brien | 1.0 Initial reproduction use cases |
20100927 | Michael O'Brien | Temporarily off project for 2 weeks - out of office |
20101022 | Michael O'Brien | Return to analysis - recommend Alternative #10 |
Overview
Problem Description
- Problem: When users do a native query on temporary tables that involve the # hash prefix - we break because we process the user defined query as if it involves parameters.
- Required fix: It would be nice if we could not process # into ? parameter markers in this case only
Constraints
- C1: There is a historical API that provides for EclipseLink native query users to use the # hash mark as a parameter marker. We may need to deprecate or remove this unpublished API.
- C2: Need to handle multiple # where
# = temporary table ## = global temporary table ### = OUT parameter type #### = INOUT parameter type
Concurrency and Thread Safety
Analysis
Problem Details
Debug and Stacktrace
- The following debug stacktrace illustrates the point that the following custom query hash (#) is converted to a (?) parameter in error.
- from
- UPDATE DPAR_SCPROCUNIT SET STATE = 1, PENDINGSTATE = 1, ALU_VERSION = 4 WHERE ((PROCUNIT_ID = 47) AND (ALU_VERSION = 3) AND (PROCUNIT_ID IN (SELECT PROCUNIT_ID FROM #
- to
- UPDATE DPAR_SCPROCUNIT SET STATE = 1, PENDINGSTATE = 1, ALU_VERSION = 4 WHERE ((PROCUNIT_ID = 47) AND (ALU_VERSION = 3) AND (PROCUNIT_ID IN (SELECT PROCUNIT_ID FROM ?
- from
DatasourceCall.java:563 /** * INTERNAL: * All values are printed as ? to allow for parameter binding or translation during the execute of the call. */ public void appendIn(Writer writer, DatabaseField field) { try { writer.write(argumentMarker()); this SQLCall (id=116) hasCustomSQLArguments true shouldProcessTokenInQuotes false sqlString "UPDATE DPAR_SCPROCUNIT SET STATE = 1, PENDINGSTATE = 1, ALU_VERSION = 4 WHERE ((PROCUNIT_ID = 47) AND (ALU_VERSION = 3) AND (PROCUNIT_ID IN (SELECT PROCUNIT_ID FROM #DPAR_SCPROCUNIT)))" (id=135) field DatabaseField (id=146) name "DPAR_SCPROCUNIT" (id=145) qualifiedName "DPAR_SCPROCUNIT" (id=145) GridClient [Java Application] org.eclipse.persistence.example.dataparallel.GridClient at localhost:63971 Thread [Main Thread] (Suspended) SQLCall(DatasourceCall).appendIn(Writer, DatabaseField) line: 567 SQLCall(DatasourceCall).translateCustomQuery() line: 422 SQLCall.translateCustomQuery() line: 283 SQLCall.prepareInternal(AbstractSession) line: 149 SQLCall(DatabaseCall).prepare(AbstractSession) line: 546 CallQueryMechanism.prepareCall() line: 132 CallQueryMechanism(DatasourceCallQueryMechanism).prepareExecuteNoSelect() line: 490 DataModifyQuery.prepare() line: 103 DataModifyQuery(DatabaseQuery).checkPrepare(AbstractSession, AbstractRecord, boolean) line: 548 DataModifyQuery(DatabaseQuery).checkPrepare(AbstractSession, AbstractRecord) line: 506 DataModifyQuery(DatabaseQuery).execute(AbstractSession, AbstractRecord) line: 714 DataModifyQuery(DatabaseQuery).executeInUnitOfWork(UnitOfWorkImpl, AbstractRecord) line: 682 RepeatableWriteUnitOfWork(UnitOfWorkImpl).internalExecuteQuery(DatabaseQuery, AbstractRecord) line: 2960 RepeatableWriteUnitOfWork(AbstractSession).executeQuery(DatabaseQuery, AbstractRecord, int) line: 1303 RepeatableWriteUnitOfWork(AbstractSession).executeQuery(DatabaseQuery, AbstractRecord) line: 1285 RepeatableWriteUnitOfWork(AbstractSession).executeQuery(DatabaseQuery, List) line: 1259 EJBQueryImpl<X>.executeUpdate() line: 534 GridClient.nativeQuery() line: 110 GridClient.main(String[]) line: 344 C:\opt\wls10340_0818\jrockit_160_20_D1.1.0-2119\bin\javaw.exe (2010-10-27 3:35:28 PM)
Design
Alternative 1: Disable select Query userDefined=True flags at Query Construction
- In the constructor
Alternative 2: Disable userDefined=True by searching queryString for [from #]
Alternative 3: Subclass SQLCall for special temporary table query
Alternative 4: Extend translation code handling of #
- This seems hacky and a little dangerous because of the optimization for # delimiter handling.
- The following experimental code only handles "from #" and not the multitude of other use cases possible using temporary tables. It would be better to use a lexical parser.
DatabaseQuery:1322 public void checkPrepare(AbstractSession session, AbstractRecord translationRow, boolean force) { if ((isReadQuery() || isDataModifyQuery()) && isCallQuery() && (getQueryMechanism() instanceof CallQueryMechanism) ... && ((translationRow == null) || translationRow.isEmpty())) { //&& !isTemporaryTableQuery()) { ... // 325167: experimental code to be discarded/refactored public boolean isTemporaryTableQuery() { boolean result = false; // explicit if (isCallQuery() && (getQueryMechanism() instanceof CallQueryMechanism)) { String queryString = ((CallQueryMechanism)getQueryMechanism()).getCall().getQueryString(); if(null != queryString) { Pattern pattern = Pattern.compile("[^ (]*[ ]*[(]*[fF][rR][oO][mM][ ]+#[^ ]*"); Matcher matcher = pattern.matcher(queryString); result = matcher.find(); } } return result; }
Alternative 5: Delimit temporary table with Square brackets like 260263
- This fix involves using square brackets like what was initially done for stored procedure parameters in bug # 260263 where we ended up using the @ delimiter in the final patch
[]
- see comment 6
Analysis 5
- square delimiters does not currently work like it did for stored procedures
- native query fragment
aBuffer.append("(SELECT PROCUNIT_ID FROM [#DPAR_SCPROCUNIT])))")
- still gets converted to ? parameters by CalQueryMechanism with a hanging beginning square bracket
(SELECT PROCUNIT_ID FROM [?)))
Alternative 6: Use @variable for #temporary table name
- We would need to create something like stored procedure code and set @table = #temporary_table
- Later we would reference it like execute("...from " + @table)
- This may not be feasible and/or supported on all database platforms - some of which do not execute code. However those that support temporary tables (derby/H2 only support global ## temporary tables) should also support procedures
Alternative 7: Use a query hint to change the default bind parameter hash char
- See 20101029 SVN Patch
- This procedure (after a group discussion) would allow the user to not have the native query treated as a Call - where (1-n) # delimiters are converted to ? parameters
- 20101029: Discussion with Chris and Andrei
- Allow user to use an alternate char like % for our # hash symbol used in EclipseLink
- We then will not process the # as a bound parameter
Analysis
- Essentially, this change involves making the currently hardcoded # delimiter - user definable into something like % for example.
- I will need to familiarize myself with query hint usage and how it relates to our parameter processing first.
- The user will be able to mix both our # hash parameter delimiter and use # exclusively for temporary table delimiters by substituting % for # where they actually want parameter binding to still occur.
- SQL = UPDATE ... WHERE id=% AND (id IN (SELECT ... FROM #TEMP_TABLE))
- converted after parameter binding to..
- SQL = UPDATE ... WHERE id=32 AND (id IN (SELECT ... FROM #TEMP_TABLE))
- not
- SQL = UPDATE ... WHERE id=32 AND (id IN (SELECT ... FROM ?)
- We need to add the query hint to EclipseLink
QueryHintsHandler.java (JPA) QueryHints (Core)
- The hint needs to be in the HashMap on jpa.QueryHintsHandler.mainMap if we wish to use it on our native query
aQuery = entityManager.createNativeQuery("update ..."); aQuery.setHint(QueryHints.PARAMETER_DELIMITER, "%");
Results
query DataReadQuery (id=157) parameterDelimiter "%" (id=153)
Work Items
- Update and add new query hint wiki at http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Query_Hints
- Verify named parameter support using ":"
- Verify escaping of "%" in the parameter name is handled - http://old.nabble.com/Issue-with-namedQuery-setParameter-and-string-which-contains-%22'%22-(single-quote)-td19606841.html
References
Alternative 8: use standard backslash escaping
- This may not be feasible - but I will test escaping the #
Alternative 9: Use boolean flag similar to shouldProcessTokenInQuotes from 217745
- See similar shouldProcessTokenInQuotes which surrounds shouldBindAddParameters in DatasourceCall
- See similar fix for 284884 : Quoted '?' symbol in expression literal causes ArrayIndexOutOfBoundsException in DatasourceCall.translateQueryString() in SVN rev # 4764
Alternative 10: Use existing eclipselink.jdbc.bind-parameters property to stop #temp_table parameter processing
- There is already an existing property that we can check that will allow us to not process the #temporary_table' custom SQL if the user sets this property. We could use the persistence unit level property or the QueryHint at the query level.
- Note: this fix will not automatically determine whether or not to process the # delimiter as a parameter - but it will allow the user to disable it for custom native queries in this case.
<property name="eclipselink.jdbc.bind-parameters" value="False"/> <!-- default is True -->
SQLCall.java:137 protected void prepareInternal(AbstractSession session) { //if (hasCustomSQLArguments()) { if (hasCustomSQLArguments() && session.getPlatform().shouldBindAllParameters()) { // 325167: do not process possible #temp table name ... translateCustomQuery(); ... super.prepareInternal(session);
Analysis
- We need to verify that override functionality via shouldIgnoreBindAllParameters() is preserved if the Query overrides the Session.
Decision
Pending code-review of Alternative #10 or #7.
Logs
Implementation
Logging
Testing
API
GUI
Config files
Documentation
Open Issues
Issue # | Owner | Description / Notes |
---|---|---|
I1 | mobrien | - |
Decisions
Issue # | Description / Notes | Decision |
---|---|---|