Bug 1323052

Description of problem:
If jBPM *Log tables are stored in a different schema than the current user of dashbuilder DB user, then opening Process & Task Dashboard via business-central results in error. This action tries to load the Dataset, by executing SQL queries - in wrong schema.

Version-Release number of selected component (if applicable):
BPM Suite 6.1.x
BPM Suite 6.2.x

How reproducible:
always

Steps to Reproduce:
I have reproduced this with Oracle DB as follows:

1) Create two oracle users (in oracle, everybody has its own schema)
2) Grant privileges for Schema User1 to User2
3) When defining datasource, configure user User2, but set User1 schema (by hibernate default_schema property)
4) Now the SQL queries will be executed against User2 schema, even though the jBPM tables are stored in schema User1 which results in "table not exist" errors.


Actual results:
When schema of jBPM tables differs from current dashbuilder user schema, the Process&Task dashboard doesn't function properly.

Expected results:
schema of jBPM tables can be different from dashbuilders user schema.

Additional info:

This is a class responsible for loading the dataset
https://github.com/droolsjbpm/dashboard-builder/blob/master/modules/dashboard-providers/dashboard-provider-sql/src/main/java/org/jboss/dashboard/dataset/sql/SQLDataSet.java#L97

It is executing SQL using JDBC, so no hibernate configuration has effect.  To my customer, I was able to provide a hot fix by including this little piece of code:

  String jbpmSchema = System.getProperty("jbpm.schema.oracle");
    if (jbpmSchema != null) {
        if (!jbpmSchema.isEmpty()) {
            String statement = "ALTER SESSION SET CURRENT_SCHEMA="+jbpmSchema;
            log.debug("Changing the schema for dataset:"+statement);
            conn.createStatement().execute(statement);
          }
        }

The above will ensure that all the subsequent statements will be executed in a schema of user's choice and not in the default user's schema. This code works only for oracle, so I won't be sending the PR with it, as more proper solution is required.