Bug 1087218 - Infinispan issue: Make JDBC cache store use implicit schemas for database tables
Summary: Infinispan issue: Make JDBC cache store use implicit schemas for database tables
Keywords:
Status: CLOSED CURRENTRELEASE
Alias: None
Product: JBoss Enterprise Application Platform 6
Classification: JBoss
Component: Clustering
Version: 6.3.0
Hardware: Unspecified
OS: Unspecified
urgent
unspecified
Target Milestone: Pending
: EAP 6.3.0
Assignee: Paul Ferraro
QA Contact: Jitka Kozana
URL:
Whiteboard:
Depends On: 1087264
Blocks:
TreeView+ depends on / blocked
 
Reported: 2014-04-14 07:02 UTC by Boleslaw Dawidowicz
Modified: 2019-08-19 12:42 UTC (History)
3 users (show)

Fixed In Version:
Clone Of:
Environment:
Last Closed: 2019-08-19 12:42:35 UTC
Type: Bug
Embargoed:


Attachments (Terms of Use)


Links
System ID Private Priority Status Summary Last Updated
Red Hat Issue Tracker ISPN-1204 0 Major Resolved Make JDBC cache store use implicit schemas for database tables 2014-09-02 14:25:59 UTC

Description Boleslaw Dawidowicz 2014-04-14 07:02:07 UTC
The current code checks if a table exists thanks to con.getMetaData().getTables(...) which is totally DB schema dependent, your code allow us to specify the schema by prefixing the table name with the name of the schema in the config which is not really convenient in practice especially if we have a lot of config files. You could easily make your code fully DB schema independent by replacing the method org.infinispan.loaders.jdbc.TableManipulation.tableExists(Connection connection, String tableName) with this content:
   public boolean tableExists(Connection connection, String tableName) {
      assertNotNull(getTableName(), "table name is mandatory");
      Statement stmt = null;
      ResultSet trs = null;
      try {
         stmt = connection.createStatement();
         trs = stmt.executeQuery("SELECT count(*) FROM " + tableName);
         return trs.next();
      }
      catch (SQLException e) {
         if (log.isTraceEnabled()) {
            log.trace("SQLException occurs while checking the table " + tableName, e);
         }
         return false;
      }
      finally {
         JdbcUtil.safeClose(trs);
         JdbcUtil.safeClose(stmt);
      }
   }
I know that it is a much less elegant and standard approach but it allows to simplify so much the config that I think that it makes sense to at least think about at it more than one second. Feel free to resolve it as won't fix if you don't find it relevant.
NB1: We use the same approach in our product (EXOJCR-1374) with JBC and we successfully tested it on Oracle, MySQL, MS SQL, PostgreSQL, DB2 and Sybase
NB2: This patch works well on all listed DB only if auto commit is set to true which should be true in your case since it seems to be the exact same code as JBC

Comment 1 Boleslaw Dawidowicz 2014-04-14 07:03:17 UTC
Needed for JBoss Portal 6.2

Comment 2 Paul Ferraro 2014-09-02 14:23:56 UTC
The upstream issue has long since been fixed.  Please verify.

Comment 3 Richard Janík 2014-11-21 08:02:44 UTC
Verified for 6.4.0.DR9.


Note You need to log in before you can comment on or make changes to this bug.