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

[Nicolas Filotto (JIRA)]

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

Nicolas Filotto commented on ISPN-2548:
---------------------------------------

This is so disappointing to revert this change just because of one query, this feature brought a lot of simplicity to the end user, as we did not have to deal with any db complexity such as the schema name. So now, we need again this kind of change https://github.com/infinispan/infinispan/commit/8a6363ebb2ac26bdbfa9de96550d4514ba011ab0, so if we intend to have several instances on the same database server, we need to define the schema name as prefix of all the tables which is just a real mess to configure when we have to deal with several different instances which is unfortunately my case, too bad for me :-(

FYI, the right query was actually {{query = "SELECT count(*) from (SELECT TOP (1) 1 as C FROM " + tableName + ") T";}}, only the alias was missing which seems to be required in case of MS SQL.
                
> JDBCCacheStore doesn't work propertly with MSSql 
> -------------------------------------------------
>
>                 Key: ISPN-2548
>                 URL: https://issues.jboss.org/browse/ISPN-2548
>             Project: Infinispan
>          Issue Type: Bug
>          Components: Loaders and Stores
>    Affects Versions: 5.2.0.Beta4
>            Reporter: Anna Manukyan
>            Assignee: Mircea Markus
>             Fix For: 5.2.0.Beta5, 5.2.0.Final
>
>
> 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