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

[dharrison]

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)

[dharrison]

The latter problems I mentioned were caused by my custom build of modeshape-persistence-relational not being pickup up in the build of my POC application . The Pom specifies 5.3.0.Final. When I swapped out the the jar, the fix worked smoothly and without error .

i Am still curious to hear if others use any version modeshape with sql server.  Thanks!

[sabahattin]

Hi David,

i have the same problem with SQL Server.

... 42 morernCaused by: com.microsoft.sqlserver.jdbc.SQLServerException: There is already an object named 'MODESHAPE_REPOSITORY' in the database.

What kind of datasource did you use?

XA ?

regards Sabahattin

[dharrison]

Initially, I'm using just a sragiht javax.sql.DataSource  and the SQLServerDriver. I now have it working completely in 5.3.0.Final, but it required modifying two files and adding one in modeshape-persistentence-relational

Modify

• sqlserver_database.properties
• RelationalDb.java

Add

• SQLServerStatements.java

Here's a redacted context.xml file :

<?xml version="1.0" encoding="UTF-8"?>

<Context antiJARLocking="true">

    <Resource name="jdbc/modeshape"

              auth="Container"

              type="javax.sql.DataSource"

              username="REDACTED_USER"

              password="REDACTED_PASSWORD"

              driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"

              url="jdbc:sqlserver://REDACTED_SERVER:1433;databaseName=modeshape-dev"

              validationQuery="select 1"

              maxActive="25"

              maxIdle="2"/>

</Context>

[hchiorean]

If this is something which can be fixed in the main codebase (i.e. bug) please log a JIRA and we'll look at getting it in for 5.4. Thanks.

[dharrison]

Awesome. Please see the JIRA below,  which includes the patches to get it working.

Disclaimer: These are patches are stricly personal contributions, not that of my employer.

You can use any part or none, of the solution. Thanks!

[MODE-2664] SQL Server - JBoss Issue Tracker

[dharrison]

FYI, the fix for MODE-2664 does seem to work great in the Github modeshape master branch