SmartClient*Version:*v9.0p_2014-03-06/PowerEdition Deployment*(built*2014-03-06)
We have discovered an issue using SQLDataSource.getSQLClause() to generate SQL when using a datasource field in AdvancedCriteria that is specified using an includeFrom attribute in the datasource.
The code originally used DSRequest.execute() on all datasources to perform the operations, but we needed to add a transaction around the operations. The code executes in a background thread, so we can't use the RPCManager to manage the transaction.
We have a datasource that uses an includeFrom attribute to reference a foreign key column in another datasource. If I construct my DSRequest with AdvancedCriteria and pass it into SQLDataSource.getSQLClause(), the SQL is generated without the join clause on the datasource table referenced in the includeFrom. This creates invalid SQL because it is missing the join and it still uses the column name from the referenced table which doesn't exist in the primary table. If I call DSRequest.execute(), the correct SQL is generated and executed. I think DSRequest.execute() must be performing some extra logic to resolve includeFrom attributes that SQLDataSource.getSQLClause() does not perform.
Also, if I call SQLDataSource.getSQLClause() a second time after calling DSRequest.execute(), the proper SQL is returned by SQLDataSource.getSQLClause().
We have discovered an issue using SQLDataSource.getSQLClause() to generate SQL when using a datasource field in AdvancedCriteria that is specified using an includeFrom attribute in the datasource.
The code originally used DSRequest.execute() on all datasources to perform the operations, but we needed to add a transaction around the operations. The code executes in a background thread, so we can't use the RPCManager to manage the transaction.
We have a datasource that uses an includeFrom attribute to reference a foreign key column in another datasource. If I construct my DSRequest with AdvancedCriteria and pass it into SQLDataSource.getSQLClause(), the SQL is generated without the join clause on the datasource table referenced in the includeFrom. This creates invalid SQL because it is missing the join and it still uses the column name from the referenced table which doesn't exist in the primary table. If I call DSRequest.execute(), the correct SQL is generated and executed. I think DSRequest.execute() must be performing some extra logic to resolve includeFrom attributes that SQLDataSource.getSQLClause() does not perform.
Also, if I call SQLDataSource.getSQLClause() a second time after calling DSRequest.execute(), the proper SQL is returned by SQLDataSource.getSQLClause().
Code:
AdvancedCriteria criteria = null;
if (onlyAssortments != null) {
criteria = new AdvancedCriteria(DefaultOperators.And, new Criterion[] {
new SimpleCriterion("la_strategy_id", "equals", strategyId),
new SetCriterion("la_ast_id", "inSet", storeGradeIds)
});
} else {
criteria = new AdvancedCriteria(DefaultOperators.And, new Criterion[] {
new SimpleCriterion("la_strategy_id", "equals", strategyId)
});
}
request = new DSRequest("ccCalcWorkDS", DataSource.OP_FETCH, rpcManager);
request.setAdvancedCriteria(criteria);
List<Map> records = null;
ResultSet rs = null;
Statement statement = null;
String query = SQLDataSource.getSQLClause(SQLClauseType.All, request);
LOG.debug(query);
request.execute();
LOG.debug("after request run " + SQLDataSource.getSQLClause(SQLClauseType.All, request));
statement = connection.createStatement();
rs = statement.executeQuery(query);
records = SQLTransform.toListOfMaps(rs);
Comment