|
Avoid Activation Errors By Changing Runtime View Object Settings Before Using Them, Not After
Lately, I've been asked to assist with several different customer service requests as well as a few OTN forum discussion threads where SQL exception problems during application module pooling state activation was occurring:
JBO-27122: SQLStmtException: <... SQL Statement ...>
For background on the ADF Business Components state management mechanism, please see section 28.2 The ADF Business Components State Management Facility in the ADF Developer's Guide for Forms/4GL Developers.
The problems all shared a common theme, and were related to the developer's use of view objects, where clauses, and bind variables in the presence of passivation/activation, which as described in section 28.8 Testing to Ensure Your Application Module is Activation-Safe of the Dev Guide, can be stress-tested by disabling application module pooling. The basic coding pattern that was getting these developer into trouble is the following:
During the span of a single HTTP request, for a given view object instance, the developers wrote code that was doing this:
- (Request begins and AM is acquired as described in section 29.2 Lifecycle of a Web Page Request Using Oracle ADF and JSF)
- Calling setWhereClause() on a view object instance that references N bind variables
- Calling setWhereClauseParam() to set the N values for those N bind variables
- Calling executeQuery()
- Calling setWhereClause(null) to clear where clause
- Calling setWhereClauseParams(null) to clear the where clause bind variables
- (AM is released as described in section 29.2 of the dev guide.)
The activation mechanism is designed, as best it can, to put your view object back into the state it was in when the last passivation occurred. To ensure that, we store in the state snapshot the values of the bind variables that were used for the last query execution. This is in addition to ones that are currently set on the rowset at the time of passivation. Normally, in an application where you are not dynamically resetting where clause and bind variables (especially the number of bind variables!) on each request, the set of bind variable values used for the last executeQuery and the set of bind variables current on the rowset at passivation time would be the same. We also store the user-supplied where clause on the view object related to the rowset at the time of passivation. However, given the usage pattern above, these sets of passivated bind variables are different!
When you disable AM pooling to stress-test the activation passivation mechanism, here's what happening.
- (Request begins and AM is acquired)
- Calling setWhereClause() on a view object instance that references N bind variables
- Calling setWhereClauseParam() to set the N values for those N bind variables
- Calling executeQuery()
- Calling setWhereClause(null) to clear where clause
- Calling setWhereClauseParams(null) to clear the where clause bind variables
- AM is released and vo instance state passivated with:
- N bind variables values that were used on last executeQuery()
- No bind variable values currently on the rowset (since they were cleared)
- No where clause (since it was cleared)
then on the next request the following occurs:
- Request begins, AM is acquired, and AM state reactivated
- Activation mechanism sets the user-defined where clause on the VO instance (which in this example is no where clause, since you cleared it)
- Activation mechanism sets the bind variable values that were used the last time the VO as executed before passivation (which in this example were the N bind variables from the where clause before it was removed)
- You likely get a SQL error at this point saying bind variables don't exist in the query, or something to that effect.
You will have more success by adopting a change of strategy for your dynamically changing where clauses and corresponding bind variables.
Instead of the pattern above of:
- (Request begins and AM is acquired)
- Calling setWhereClause() on a view object instance that references N bind variables
- Calling setWhereClauseParam() to set the N values for those N bind variables
- Calling executeQuery()
- Calling setWhereClause(null) to clear where clause
- Calling setWhereClauseParams(null) to clear the where clause bind variables
- (AM is released)
Instead if you were to do this instead:
- (Request begins and AM is acquired)
- Call setWhereClause(null) to clear where clause
- Call setWhereClauseParams(null) to clear the where clause bind variables
- Call setWhereClause() that references N bind variables
- Calling setWhereClauseParam() to set the N values for those N bind variables
- Calling executeQuery()
- (AM is released)
Then the user-defined where clause on the VO at the time of passivation will match the set of bind variable values used the last time the VO was executed before passivation, and that means no error will occur during activation.
Since as described in section 28.4.1 What State is Saved? of the dev guide, many of the view object's instance settings are saved in the passivation state snapshot and restored on activation, it's not advisable to change any of these settings just after executing the view object if you won't be re-executing the view object again during the same block of code (and so, during the same HTTP request, that is). Instead, change the view object instance settings the next time you need them to be different before executing the query.
If you are dynamically adding named where clause parameters to your view object instances, you might find the following helper method (to add to your ViewObjectImpl framework extension class) useful. It removes named bind variables that have been added to the view instance at runtime, without removing the ones that have been declaratively defined on the view definition at design time.
protected void clearWhereState() {
ViewDefImpl viewDef = getViewDef();
Variable[] viewInstanceVars = null;
VariableManager viewInstanceVarMgr = ensureVariableManager();
if (viewInstanceVarMgr != null) {
viewInstanceVars = viewInstanceVarMgr.getVariablesOfKind(Variable.VAR_KIND_WHERE_CLAUSE_PARAM);
if (viewInstanceVars != null) {
for (Variable v: viewInstanceVars) {
// only remove the variable if its not on the view def.
if (!hasViewDefVariableNamed(v.getName())) {
removeNamedWhereClauseParam(v.getName());
}
}
}
}
getDefaultRowSet().setExecuteParameters(null, null, true);
setWhereClause(null);
getDefaultRowSet().setWhereClauseParams(null);
} private boolean hasViewDefVariableNamed(String name) {
boolean ret = false;
VariableManager viewDefVarMgr = getViewDef().ensureVariableManager();
if (viewDefVarMgr != null) {
try {
ret = viewDefVarMgr.findVariable(name) != null;
}
catch (NoDefException ex) {
// ignore
}
}
return ret;
}
|
