topic Re: GetTableCountCmd not working with oracle db in 5.12 in Alfresco Archive

GetTableCountCmd not working with oracle db in 5.12

pgadecki — Tue, 19 Mar 2013 17:42:36 GMT

I have noticed that GetTableCountCmd command returns empty list after switching from 5.10 to 5.12 on oracle database.

Re: GetTableCountCmd not working with oracle db in 5.12

jbarrez — Wed, 20 Mar 2013 09:59:33 GMT

What exactly are you executing? Do you get an exception or just no results? How does your schema look?

Re: GetTableCountCmd not working with oracle db in 5.12

pgadecki — Wed, 20 Mar 2013 14:55:59 GMT

So first of all this command worked fine in activiti 5.10, I was using it in my process test-suite to determine if there are tables that have to be dropped and recreated between tests (It's not really important to solve this problem).
I was using my command executor:
Map<String, Long> tableCounts = BPMCommandExecutorProxy.execute(new GetTableCountCmd());
I did debug it, the magic should happen in the TableDataManager.getTablesPresentInDatabase()

public List<String> getTablesPresentInDatabase() {
    List<String> tableNames = new ArrayList<String>();
    Connection connection = null;
    try {
      connection = getDbSqlSession().getSqlSession().getConnection();
      DatabaseMetaData databaseMetaData = connection.getMetaData();
      ResultSet tables = null;
      try {
        log.debug("retrieving activiti tables from jdbc metadata");
        String databaseTablePrefix = getDbSqlSession().getDbSqlSessionFactory().getDatabaseTablePrefix();
        String tableNameFilter = databaseTablePrefix+"ACT_%";
        if ("postgres".equals(getDbSqlSession().getDbSqlSessionFactory().getDatabaseType())) {
          tableNameFilter = databaseTablePrefix+"act_%";
        }
        if ("oracle".equals(getDbSqlSession().getDbSqlSessionFactory().getDatabaseType())) {
          tableNameFilter = databaseTablePrefix+"ACT" + databaseMetaData.getSearchStringEscape() + "_%";
        }
        tables = databaseMetaData.getTables(null, null, tableNameFilter, getDbSqlSession().JDBC_METADATA_TABLE_TYPES);
        while (tables.next()) {
          String tableName = tables.getString("TABLE_NAME");
          tableName = tableName.toUpperCase();
          tableNames.add(tableName);
          log.debug("  retrieved activiti table name {}", tableName);
        }
      } finally {
        tables.close();
      }
    } catch (Exception e) {
      throw new ActivitiException("couldn't get activiti table names using metadata: "+e.getMessage(), e); 
    }
    return tableNames;
  }

But it seems that this:
tables = databaseMetaData.getTables(null, null, tableNameFilter, getDbSqlSession().JDBC_METADATA_TABLE_TYPES);returns just empty iterator, so there is no exception.
tableNameFilter is resolved to "ACT//_%"

I'm using Oracle 11g 64-bit, with successfully automatically created activiti db schema 5.12
There are multiple users in the database that also have their activiti tables.

Re: GetTableCountCmd not working with oracle db in 5.12

jbarrez — Thu, 21 Mar 2013 11:25:24 GMT

It seems very strange that

databaseMetaData.getTables(null, null, tableNameFilter, getDbSqlSession().JDBC_METADATA_TABLE_TYPES);

which is plain JDBC changes due to Activiti versions. Did you check the schema? Maybe something has altered the schema in a way that the filter doesn't match anymore (just guessing out loud here …)

Re: GetTableCountCmd not working with oracle db in 5.12

manchikantir — Tue, 06 Jan 2015 06:23:17 GMT

We have DB user name alias.
So relying on
databaseTablePrefix=ACTIVITI.
tablePrefixIsSchema=true

But with this Manage>Database is not working on Activiti-Explorer..

While debuggin came across .. getTablesPresentInDatabase() method in TableDatamanager is not return proper table names.

There is no check for tablePrefixIsSchema, so ending up with wrong tableNameFilter…

And databaseCatalog, databaseSchema are not handled with databaseMetaData.getTables() method..

Module: acitiviti-engine
Class: TableDataManager
Method: getTablesPresentInDatabase

Re: GetTableCountCmd not working with oracle db in 5.12

trademak — Thu, 08 Jan 2015 13:31:15 GMT

Can you create a unit test showing the issue and create a JIRA issue?

Thanks,