[infinispan-issues] [JBoss JIRA] (ISPN-2548) JDBCCacheStore doesn't work propertly with MSSql

[RH Bugzilla Integration (JIRA)]

    [ https://issues.jboss.org/browse/ISPN-2548?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12736695#comment-12736695 ] 

RH Bugzilla Integration commented on ISPN-2548:
-----------------------------------------------

Anna Manukyan <amanukya at redhat.com> made a comment on [bug 879635|https://bugzilla.redhat.com/show_bug.cgi?id=879635]

Also, please note - that the tests were working properly with JDG6.1.0.ER2 (Infinispan-5.2.0.Beta2 version).

I've noticed that on 9th of November, there were some changes done in TableManipulation to tableExists() method, where the query for defining whether the table exists in DB or not is generated according to the database type. 

So we just tried the previous query for SQL_SERVER database, to find out whether the issue is really in Infinispan or it is on database side, and our change worked. 

The current code is (org.infinispan.loaders.jdbc.TableManipulation.tableExists(Connection connection, String tableName) line 123:

...........
 case SQL_SERVER:
   query = "SELECT count(*) from (SELECT TOP (1) 1 FROM " + tableName + ") T";
   break;
...........


We changed to:

.............
 case SQL_SERVER:
   query = "SELECT count(*) FROM " + tableName;
   break;
.............

This solution works.
                
> JDBCCacheStore doesn't work propertly with MSSql 
> -------------------------------------------------
>
>                 Key: ISPN-2548
>                 URL: https://issues.jboss.org/browse/ISPN-2548
>             Project: Infinispan
>          Issue Type: Feature Request
>          Components: Loaders and Stores
>    Affects Versions: 5.2.0.Beta4
>            Reporter: Anna Manukyan
>            Assignee: Mircea Markus
>
> The functional tests for JDBCCacheStore using MSSQL2008 R2 as a store, are failing. 
> In case if the configuration is set with properties:
> .addProperty("dropTableOnExit", "false")
> .addProperty("createTableOnStart", "true") 
> The following exception arrise:
> {code}
>  org.infinispan.loaders.CacheLoaderException: com.microsoft.sqlserver.jdbc.SQLServerException: There is already an object named 'edg_bin____defaultcache' in the database.
> 	at org.infinispan.loaders.jdbc.TableManipulation.executeUpdateSql(TableManipulation.java:187)
> 	at org.infinispan.loaders.jdbc.TableManipulation.createTable(TableManipulation.java:160)
> 	at org.infinispan.loaders.jdbc.TableManipulation.start(TableManipulation.java:262)
> 	at org.infinispan.loaders.jdbc.binary.JdbcBinaryCacheStore.doConnectionFactoryInitialization(JdbcBinaryCacheStore.java:514)
> 	at org.infinispan.loaders.jdbc.binary.JdbcBinaryCacheStore.start(JdbcBinaryCacheStore.java:102)
> 	at org.infinispan.loaders.CacheLoaderManagerImpl.start(CacheLoaderManagerImpl.java:159)
> 	... 104 more
> {code}
> Please note, that MSSql database is clean before the test run, this means that the table is not there. But the exception is in place anyway.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira