6 Replies Latest reply on Feb 16, 2017 8:13 PM by David Harrison

    Modeshape 5.3.x or 5.4.x with SQL Server?

    David Harrison Newbie

      Hello,

       

      I'm wondering if anyone else has tried using ModeShape 5.3 or 5.4 with SQL Server 2014 as jdbc persistence for both regular and binary content.

      I'm able to use PostgresSQL 9.5 without any problem.  However, for enterprise backup related purposes, it would be better if we I SQL Server.

       

      For initial tests,  I have a simple project with is just the REST api and the repository, built as a war file, and deployed into tomcat. This is based on the modeshape-examples for modeshape-tomcat-jdbc-example, modeshape-rest-example

       

      The first round of problems I encountered is that when ModeShape starts up and tries to initialize the database tables, a similar problem occurs as it previous did with Oracle.

      That is,  SQL Server those exceptions upon trying to create a the schema in the database. 

       

      I can get path the 1st set of error by doing the following

      1. Create a new class SqlServerStatements, modeled after OracleStatements.java
        1. It should ignore (or treat errors as warnings)  for these exceptions
          1. 2714/* object already exists*/,
          2. 3701/* object doesn't exist or cannot drop due to permissions*/);
      2. Modifying RelationalDb.java 's contructor to use the SqlServerStatements, when the database is SQLSERVER.
      3. Alter sqlserver_database.properties,   create_table statement,   content needs to be defined as varbinary with a length,  varbinary without specifying a length is creating a table column with a size of (1)  and then I cannot insert any content into it.
        create_table = CREATE TABLE {0} (ID NVARCHAR(255) PRIMARY KEY , \
          LAST_CHANGED DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, \
          CONTENT VARBINARY(max) NOT NULL)

      Now, the application appears to start up, but then gives me a similar error.  Upon going into the rest URL for the app, I get a repositoryException

      It appears that RelationalDb.runWithConnection is still calling the default DefaultStatements createTable instead of the SQLServerStatements createTable, because I'm still getting the same type of SQL Server exception being considered a hard-error.

       

      Current errors

      09:12:10,024 DEBUG Looking for JBoss Standalone JTA...

      09:12:10,027 DEBUG Looking for Atomikos JTA...

      09:12:10,032 DEBUG No javax.transaction.TransactionManager was found; falling back to LocalTransactionManager. Enable DEBUG logging for more information.

      09:12:10,087 DEBUG Creating table MODESHAPE_REPOSITORY...

      09:12:10,105 DEBUG Shutting down locking service...

      09:12:10,119 ERROR Cannot load the names of the available repositories.

      javax.jcr.RepositoryException: org.modeshape.persistence.relational.RelationalProviderException: com.microsoft.sqlserver.jdbc.SQLServerException: There is already an object named 'MODESHAPE_REPOSITORY' in the database.       

      at org.modeshape.jcr.JcrRepositoriesContainer$UrlRepositoryLookup.repository(JcrRepositoriesContainer.java:228)