Hi, I just saw this in DBDictionary:
> + protected transient Select sel = null; This type of state can't go into a DBDictionary instance; DBDictionaries are shared across multiple concurrent threads. So, this state needs to be passed down to the methods that need it on the stack, not the heap. -Patrick On 9/11/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Author: fancy > Date: Tue Sep 11 10:59:38 2007 > New Revision: 574653 > > URL: http://svn.apache.org/viewvc?rev=574653&view=rev > Log: > OPENJPA-360 SQL FOR UPDATE OF incorrectly generated for DB2 UDB version8.1 or > earlier and DB2 ISeries V5R3 or earlier. > These DB2 version also require the "optimize for <n> row" clause appear > before FOR UPDATE clause. > Due to this requirement, the OPTIMIZE clause will appear before FOR UPDATE > clause for all DB2 versions. > Also fixed the affected testcases. > Same fix retrofit to OpenJPA 1.0.x branch. > > Modified: > > openjpa/branches/1.0.x/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DB2Dictionary.java > > openjpa/branches/1.0.x/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DBDictionary.java > > openjpa/branches/1.0.x/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/jdbc/TestIsolationLevelOverride.java > > openjpa/branches/1.0.x/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/jdbc/TestOptimizeForClause.java > > openjpa/branches/1.0.x/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/jdbc/TestSelectForUpdateOverride.java > > openjpa/branches/1.0.x/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/test/SQLListenerTestCase.java > > Modified: > openjpa/branches/1.0.x/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DB2Dictionary.java > URL: > http://svn.apache.org/viewvc/openjpa/branches/1.0.x/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DB2Dictionary.java?rev=574653&r1=574652&r2=574653&view=diff > ============================================================================== > --- > openjpa/branches/1.0.x/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DB2Dictionary.java > (original) > +++ > openjpa/branches/1.0.x/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DB2Dictionary.java > Tue Sep 11 10:59:38 2007 > @@ -50,12 +50,12 @@ > public String optimizeClause = "optimize for"; > public String rowClause = "row"; > protected int db2ServerType = 0; > - protected static final int db2ISeriesV5R3OrEarlier = 1; > - protected static final int db2UDBV81OrEarlier = 2; > - protected static final int db2ZOSV8xOrLater = 3; > - protected static final int db2UDBV82OrLater = 4; > - protected static final int db2ISeriesV5R4OrLater = 5; > - private static final String forUpdateOfClause = "FOR UPDATE OF"; > + public static final int db2ISeriesV5R3OrEarlier = 1; > + public static final int db2UDBV81OrEarlier = 2; > + public static final int db2ZOSV8xOrLater = 3; > + public static final int db2UDBV82OrLater = 4; > + public static final int db2ISeriesV5R4OrLater = 5; > + private static final String forUpdate = "FOR UPDATE"; > private static final String withRSClause = "WITH RS"; > private static final String withRRClause = "WITH RR"; > private static final String useKeepUpdateLockClause > @@ -286,9 +286,12 @@ > * updateClause and isolationLevel hints > */ > protected String getForUpdateClause(JDBCFetchConfiguration fetch, > - boolean forUpdate) { > + boolean isForUpdate) { > int isolationLevel; > - StringBuffer forUpdateString = new StringBuffer(); > + // For db2UDBV81OrEarlier and db2ISeriesV5R3OrEarlier: > + // "optimize for" clause appears before "for update" clause. > + StringBuffer forUpdateString = new StringBuffer( > + getOptimizeClause(fetch)); > try { > // Determine the isolationLevel; the fetch > // configuration data overrides the persistence.xml value > @@ -297,16 +300,15 @@ > else > isolationLevel = conf.getTransactionIsolationConstant(); > > - if (forUpdate) { > + if (isForUpdate) { > switch(db2ServerType) { > case db2ISeriesV5R3OrEarlier: > case db2UDBV81OrEarlier: > - if (isolationLevel == > - Connection.TRANSACTION_READ_UNCOMMITTED) { > - forUpdateString.append(" ").append(withRSClause) > - .append(" ").append(forUpdateOfClause); > - } else > - forUpdateString.append(" > ").append(forUpdateOfClause); > + if (isolationLevel == > Connection.TRANSACTION_SERIALIZABLE) > + forUpdateString.append(" ").append(forUpdateClause); > + else > + forUpdateString.append(" ").append(forUpdate) > + .append(" ").append(withRSClause); > break; > case db2ZOSV8xOrLater: > case db2UDBV82OrLater: > @@ -341,7 +343,7 @@ > return forUpdateString.toString(); > } > > - public boolean isDB2UDBV82OrLater() throws SQLException { > + public boolean isDB2UDBV82OrLater() { > boolean match = false; > if ((databaseProductVersion.indexOf("SQL") != -1 > || databaseProductName.indexOf("DB2/") != -1) > @@ -350,8 +352,7 @@ > return match; > } > > - public boolean isDB2ZOSV8xOrLater() > - throws SQLException { > + public boolean isDB2ZOSV8xOrLater() { > boolean match = false; > if ((databaseProductVersion.indexOf("DSN") != -1 > || databaseProductName.indexOf("DB2/") == -1) > @@ -360,8 +361,7 @@ > return match; > } > > - public boolean isDB2ISeriesV5R3OrEarlier() > - throws SQLException { > + public boolean isDB2ISeriesV5R3OrEarlier() { > boolean match = false; > if (databaseProductName.indexOf("AS") != -1 > && ((maj == 5 && min <=3) || maj < 5)) > @@ -369,8 +369,7 @@ > return match; > } > > - public boolean isDB2ISeriesV5R4OrLater() > - throws SQLException { > + public boolean isDB2ISeriesV5R4OrLater() { > boolean match = false; > if (databaseProductName.indexOf("AS") != -1 > && (maj >=6 || (maj == 5 && min >=4))) > @@ -378,7 +377,7 @@ > return match; > } > > - public boolean isDB2UDBV81OrEarlier() throws SQLException { > + public boolean isDB2UDBV81OrEarlier() { > boolean match = false; > if ((databaseProductVersion.indexOf("SQL") != -1 > || databaseProductName.indexOf("DB2/") != -1) && > @@ -438,17 +437,16 @@ > } > } > > - public SQLBuffer toSelect(Select sel, boolean forUpdate, > - JDBCFetchConfiguration fetch) { > - SQLBuffer buf = super.toSelect(sel, forUpdate, fetch); > - > - if (sel.getExpectedResultCount() > 0) { > + protected String getOptimizeClause(JDBCFetchConfiguration fetch) { > + if (sel != null && sel.getExpectedResultCount() > 0) { > + StringBuffer buf = new StringBuffer(); > buf.append(" ").append(optimizeClause).append(" ") > .append(String.valueOf(sel.getExpectedResultCount())) > .append(" ").append(rowClause); > + return buf.toString(); > } > > - return buf; > + return ""; > } > > public OpenJPAException newStoreException(String msg, SQLException[] > causes, > > Modified: > openjpa/branches/1.0.x/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DBDictionary.java > URL: > http://svn.apache.org/viewvc/openjpa/branches/1.0.x/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DBDictionary.java?rev=574653&r1=574652&r2=574653&view=diff > ============================================================================== > --- > openjpa/branches/1.0.x/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DBDictionary.java > (original) > +++ > openjpa/branches/1.0.x/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DBDictionary.java > Tue Sep 11 10:59:38 2007 > @@ -319,6 +319,7 @@ > private Method _setBytes = null; > private Method _setString = null; > private Method _setCharStream = null; > + protected transient Select sel = null; > > public DBDictionary() { > fixedSizeTypeNameSet.addAll(Arrays.asList(new String[]{ > @@ -1982,6 +1983,7 @@ > */ > public SQLBuffer toSelect(Select sel, boolean forUpdate, > JDBCFetchConfiguration fetch) { > + this.sel = sel; > sel.addJoinClassConditions(); > boolean update = forUpdate && sel.getFromSelect() == null; > SQLBuffer select = getSelects(sel, false, update); > @@ -2192,11 +2194,11 @@ > * updateClause and isolationLevel hints > */ > protected String getForUpdateClause(JDBCFetchConfiguration fetch, > - boolean forUpdate) { > + boolean isForUpdate) { > if (fetch != null && fetch.getIsolation() != -1) { > throw new InvalidStateException(_loc.get( > "isolation-level-config-not-supported", > getClass().getName())); > - } else if (forUpdate && !simulateLocking) { > + } else if (isForUpdate && !simulateLocking) { > assertSupport(supportsSelectForUpdate, > "SupportsSelectForUpdate"); > return forUpdateClause; > } else { > @@ -3893,5 +3895,4 @@ > public String getCastFunction(Val val, String func) { > return func; > } > - > } > > Modified: > openjpa/branches/1.0.x/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/jdbc/TestIsolationLevelOverride.java > URL: > http://svn.apache.org/viewvc/openjpa/branches/1.0.x/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/jdbc/TestIsolationLevelOverride.java?rev=574653&r1=574652&r2=574653&view=diff > ============================================================================== > --- > openjpa/branches/1.0.x/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/jdbc/TestIsolationLevelOverride.java > (original) > +++ > openjpa/branches/1.0.x/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/jdbc/TestIsolationLevelOverride.java > Tue Sep 11 10:59:38 2007 > @@ -81,37 +81,23 @@ > > q.getResultList(); > if (dict instanceof DB2Dictionary) { > - if ((((DB2Dictionary) dict).getDb2ServerType() == 1) > - || (((DB2Dictionary) dict).getDb2ServerType()== 2)) { > + int db2server = ((DB2Dictionary) > dict).getDb2ServerType(); > + if (db2server == DB2Dictionary.db2ISeriesV5R3OrEarlier > + || db2server == DB2Dictionary.db2UDBV81OrEarlier) { > assertEquals(1, sql.size()); > - assertSQL("SELECT t0.id, t0.booleanField, > t0.byteField," > - + " t0.charField, t0.dateField, t0.doubleField," > - + " t0.floatField, t0.intField, t0.longField, " > - + "t0.shortField, t0.stringField FROM " > - + "AllFieldTypes t0 WHERE \\(t0.intField = > \\?\\) " > - + " FOR UPDATE OF"); > + assertContainsSQL(" FOR UPDATE"); > } > // it is DB2 v82 or later > - else if ((((DB2Dictionary) dict).getDb2ServerType() == 3) > - || (((DB2Dictionary) dict).getDb2ServerType() == 4)) > { > + else if (db2server == DB2Dictionary.db2ZOSV8xOrLater > + || db2server == DB2Dictionary.db2UDBV82OrLater) { > assertEquals(1, sql.size()); > - assertSQL("SELECT t0.id, t0.booleanField, > t0.byteField," > - + " t0.charField, t0.dateField, t0.doubleField," > - + " t0.floatField, t0.intField, t0.longField, " > - + "t0.shortField, t0.stringField FROM " > - + "AllFieldTypes t0 WHERE \\(t0.intField = > \\?\\) " > - + " FOR READ ONLY WITH RR USE AND KEEP " > + assertContainsSQL(" FOR READ ONLY WITH RR USE AND > KEEP " > + "UPDATE LOCKS"); > } > - else if (((DB2Dictionary) dict).getDb2ServerType() == 5) > { > + else if (db2server == > DB2Dictionary.db2ISeriesV5R4OrLater) { > assertEquals(1, sql.size()); > - assertSQL("SELECT t0.id, t0.booleanField, > t0.byteField," > - + " t0.charField, t0.dateField, t0.doubleField," > - + " t0.floatField, t0.intField, t0.longField, " > - + "t0.shortField, t0.stringField FROM " > - + "AllFieldTypes t0 WHERE \\(t0.intField = > \\?\\) " > - + " FOR READ ONLY WITH RR USE AND KEEP EXCLUSIVE > " > - + "LOCKS"); > + assertContainsSQL(" FOR READ ONLY WITH RR USE AND > KEEP" > + + " EXCLUSIVE LOCKS"); > } > else { > fail("OpenJPA currently only supports " > @@ -124,37 +110,25 @@ > .setIsolation(IsolationLevel.SERIALIZABLE); > em.find(AllFieldTypes.class, 0); > if (dict instanceof DB2Dictionary ) { > - if ((((DB2Dictionary) dict).getDb2ServerType() == 1) > - || (((DB2Dictionary) dict).getDb2ServerType()== 2)) { > + int db2server = ((DB2Dictionary) > dict).getDb2ServerType(); > + if (db2server == DB2Dictionary.db2ISeriesV5R3OrEarlier > + || db2server == DB2Dictionary.db2UDBV81OrEarlier) { > assertEquals(1, sql.size()); > - assertSQL("SELECT t0.booleanField, t0.byteField, " > - + "t0.charField, t0.dateField, t0.doubleField," > - + " t0.floatField, t0.intField, t0.longField," > - + " t0.shortField, t0.stringField FROM " > - + "AllFieldTypes t0 WHERE t0.id = \\? " > - + " FOR UPDATE OF optimize for 1 row"); > + assertContainsSQL(" optimize for 1 row FOR UPDATE"); > } > // it is DB2 v82 or later > - else if ((((DB2Dictionary) dict).getDb2ServerType() == 3) > - || (((DB2Dictionary) dict).getDb2ServerType() == 4)) > { > + else if (db2server == DB2Dictionary.db2ZOSV8xOrLater > + || db2server == DB2Dictionary.db2UDBV82OrLater) { > assertEquals(1, sql.size()); > - assertSQL("SELECT t0.booleanField, t0.byteField, " > - + "t0.charField, t0.dateField, t0.doubleField," > - + " t0.floatField, t0.intField, t0.longField," > - + " t0.shortField, t0.stringField FROM " > - + "AllFieldTypes t0 WHERE t0.id = \\? " > + assertContainsSQL(" optimize for 1 row" > + " FOR READ ONLY WITH RR USE AND KEEP UPDATE > LOCKS" > - + " optimize for 1 row"); > + ); > } > - else if (((DB2Dictionary) dict).getDb2ServerType() == 5) > { > + else if (db2server == > DB2Dictionary.db2ISeriesV5R4OrLater) { > assertEquals(1, sql.size()); > - assertSQL("SELECT t0.booleanField, t0.byteField, " > - + "t0.charField, t0.dateField, t0.doubleField," > - + " t0.floatField, t0.intField, t0.longField," > - + " t0.shortField, t0.stringField FROM " > - + "AllFieldTypes t0 WHERE t0.id = \\? " > + assertContainsSQL(" optimize for 1 row" > + " FOR READ ONLY WITH RR USE AND KEEP EXCLUSIVE" > - + " LOCKS optimize for 1 row"); > + + " LOCKS"); > } > else { > fail("OpenJPA currently only supports per-query" > > Modified: > openjpa/branches/1.0.x/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/jdbc/TestOptimizeForClause.java > URL: > http://svn.apache.org/viewvc/openjpa/branches/1.0.x/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/jdbc/TestOptimizeForClause.java?rev=574653&r1=574652&r2=574653&view=diff > ============================================================================== > --- > openjpa/branches/1.0.x/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/jdbc/TestOptimizeForClause.java > (original) > +++ > openjpa/branches/1.0.x/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/jdbc/TestOptimizeForClause.java > Tue Sep 11 10:59:38 2007 > @@ -81,22 +81,14 @@ > } > if (dict instanceof DB2Dictionary) { > assertEquals(1, sql.size()); > - assertSQL("SELECT t0.id, t0.booleanField, t0.byteField," + > - " t0.charField, t0.dateField, t0.doubleField, " + > - "t0.floatField, t0.intField, t0.longField, " + > - "t0.shortField, t0.stringField FROM AllFieldTypes " + > - "t0 WHERE \\(t0.intField = \\?\\) optimize for 8 > row"); > + assertContainsSQL(" optimize for 8 row"); > } > } > else { > em.find(AllFieldTypes.class, 0); > if (dict instanceof DB2Dictionary ) { > assertEquals(1, sql.size()); > - assertSQL("SELECT t0.booleanField, t0.byteField, " + > - "t0.charField, t0.dateField, t0.doubleField, " + > - "t0.floatField, t0.intField, t0.longField, " + > - "t0.shortField, t0.stringField FROM AllFieldTypes" + > - " t0 WHERE t0.id = \\? optimize for 1 row"); > + assertContainsSQL(" optimize for 1 row"); > } > > } > > Modified: > openjpa/branches/1.0.x/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/jdbc/TestSelectForUpdateOverride.java > URL: > http://svn.apache.org/viewvc/openjpa/branches/1.0.x/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/jdbc/TestSelectForUpdateOverride.java?rev=574653&r1=574652&r2=574653&view=diff > ============================================================================== > --- > openjpa/branches/1.0.x/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/jdbc/TestSelectForUpdateOverride.java > (original) > +++ > openjpa/branches/1.0.x/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/jdbc/TestSelectForUpdateOverride.java > Tue Sep 11 10:59:38 2007 > @@ -33,7 +33,14 @@ > extends SQLListenerTestCase { > > public void setUp() { > - setUp(AllFieldTypes.class, > + setUp( > +// "openjpa.ConnectionDriverName", > +// "org.apache.commons.dbcp.BasicDataSource", > + > + "openjpa.ConnectionProperties", > + > "DriverClassName=com.ibm.db2.jcc.DB2Driver,Url=jdbc:db2:demodb" > + ,"openjpa.jdbc.SynchronizeMappings", > + AllFieldTypes.class, > "openjpa.Optimistic", "false", > "openjpa.LockManager", "pessimistic", > "openjpa.ReadLockLevel", "none"); > @@ -57,37 +64,24 @@ > em.find(AllFieldTypes.class, 0); > assertEquals(1, sql.size()); > if (dict instanceof DB2Dictionary) { > - if ((((DB2Dictionary) dict).getDb2ServerType() == 1) > - || (((DB2Dictionary) dict).getDb2ServerType()== 2)) { > + int db2server = ((DB2Dictionary) dict).getDb2ServerType(); > + if (db2server == DB2Dictionary.db2ISeriesV5R3OrEarlier > + || db2server == DB2Dictionary.db2UDBV81OrEarlier) { > assertEquals(1, sql.size()); > - assertSQL("SELECT t0.booleanField, t0.byteField, " > - + "t0.charField, t0.dateField, t0.doubleField," > - + " t0.floatField, t0.intField, t0.longField," > - + " t0.shortField, t0.stringField FROM " > - + "AllFieldTypes t0 WHERE t0.id = \\? " > - + " FOR UPDATE OF optimize for 1 row"); > + assertContainsSQL(" optimize for 1 row FOR UPDATE"); > } > // it is DB2 v82 or later > - else if ((((DB2Dictionary) dict).getDb2ServerType() == 3) > - || (((DB2Dictionary) dict).getDb2ServerType() == 4)) { > + else if (db2server == DB2Dictionary.db2ZOSV8xOrLater > + || db2server == DB2Dictionary.db2UDBV82OrLater) { > assertEquals(1, sql.size()); > - assertSQL("SELECT t0.booleanField, t0.byteField, " > - + "t0.charField, t0.dateField, t0.doubleField," > - + " t0.floatField, t0.intField, t0.longField," > - + " t0.shortField, t0.stringField FROM " > - + "AllFieldTypes t0 WHERE t0.id = \\? " > - + " FOR READ ONLY WITH RS USE AND KEEP UPDATE LOCKS" > - + " optimize for 1 row"); > + assertContainsSQL(" optimize for 1 row" > + + " FOR READ ONLY WITH RS USE AND KEEP UPDATE > LOCKS"); > } > - else if (((DB2Dictionary) dict).getDb2ServerType() == 5) { > + else if (db2server == DB2Dictionary.db2ISeriesV5R4OrLater) { > assertEquals(1, sql.size()); > - assertSQL("SELECT t0.booleanField, t0.byteField, " > - + "t0.charField, t0.dateField, t0.doubleField," > - + " t0.floatField, t0.intField, t0.longField," > - + " t0.shortField, t0.stringField FROM " > - + "AllFieldTypes t0 WHERE t0.id = \\? " > + assertContainsSQL(" optimize for 1 row" > + " FOR READ ONLY WITH RS USE AND KEEP EXCLUSIVE > LOCKS" > - + " optimize for 1 row"); > + ); > } > else { > fail("OpenJPA currently only supports per-query > isolation " > > Modified: > openjpa/branches/1.0.x/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/test/SQLListenerTestCase.java > URL: > http://svn.apache.org/viewvc/openjpa/branches/1.0.x/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/test/SQLListenerTestCase.java?rev=574653&r1=574652&r2=574653&view=diff > ============================================================================== > --- > openjpa/branches/1.0.x/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/test/SQLListenerTestCase.java > (original) > +++ > openjpa/branches/1.0.x/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/test/SQLListenerTestCase.java > Tue Sep 11 10:59:38 2007 > @@ -78,6 +78,21 @@ > + " should not have been executed in SQL statements: " + > sql); > } > > + /** > + * Confirm that the executed SQL String contains the specified sqlExp. > + * > + * @param sqlExp the SQL expression. E.g., "SELECT BADCOLUMN .*" > + */ > + public void assertContainsSQL(String sqlExp) { > + for (String statement : sql) { > + if (statement.contains(sqlExp)) > + return; > + } > + > + fail("Expected regular expression <" + sqlExp + "> to be" > + + " contained in SQL statements: " + sql); > + } > + > public class Listener > extends AbstractJDBCListener { > > > > -- Patrick Linskey 202 669 5907
