[JBoss JIRA] (ISPN-1579) Jdbc cache store throwing SQL error : incorrect syntax near '@P0' - when using MS SQL Server
by Martin Gencur (Created) (JIRA)
Jdbc cache store throwing SQL error : incorrect syntax near '@P0' - when using MS SQL Server
--------------------------------------------------------------------------------------------
Key: ISPN-1579
URL: https://issues.jboss.org/browse/ISPN-1579
Project: Infinispan
Issue Type: Bug
Affects Versions: 5.1.0.BETA5
Reporter: Martin Gencur
Assignee: Martin Gencur
Stack trace:
{code}
12:11:46,067 ERROR [org.infinispan.loaders.jdbc.DataManipulationHelper] (MSC service thread 1-4) ISPN008007: SQL error while fetching all StoredEntries: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0'.
[java] at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:197) [sqljdbc4.jar:]
[java] at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1493) [sqljdbc4.jar:]
[java] at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:390) [sqljdbc4.jar:]
[java] at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:340) [sqljdbc4.jar:]
[java] at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4575) [sqljdbc4.jar:]
[java] at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1400) [sqljdbc4.jar:]
[java] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:179) [sqljdbc4.jar:]
[java] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:154) [sqljdbc4.jar:]
[java] at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:283) [sqljdbc4.jar:]
[java] at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
[java] at org.infinispan.loaders.jdbc.DataManipulationHelper.loadSome(DataManipulationHelper.java:245) [infinispan-cachestore-jdbc-5.1.0.BETA5.jar:]
[java] at org.infinispan.loaders.jdbc.stringbased.JdbcStringBasedCacheStore.loadLockSafe(JdbcStringBasedCacheStore.java:289) [infinispan-cachestore-jdbc-5.1.0.BETA5.jar:]
[java] at org.infinispan.loaders.LockSupportCacheStore.load(LockSupportCacheStore.java:153) [infinispan-core-5.1.0.BETA5.jar:]
[java] at org.infinispan.loaders.CacheLoaderManagerImpl.loadState(CacheLoaderManagerImpl.java:192) [infinispan-core-5.1.0.BETA5.jar:]
[java] at org.infinispan.loaders.CacheLoaderManagerImpl.preload(CacheLoaderManagerImpl.java:155) [infinispan-core-5.1.0.BETA5.jar:]
[java] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [:1.6.0_21]
[java] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) [:1.6.0_21]
[java] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) [:1.6.0_21]
[java] at java.lang.reflect.Method.invoke(Method.java:597) [:1.6.0_21]
[java] at org.infinispan.util.ReflectionUtil.invokeAccessibly(ReflectionUtil.java:203) [infinispan-core-5.1.0.BETA5.jar:]
[java] at org.infinispan.factories.AbstractComponentRegistry$PrioritizedMethod.invoke(AbstractComponentRegistry.java:825) [infinispan-core-5.1.0.BETA5.jar:]
[java] at org.infinispan.factories.AbstractComponentRegistry.internalStart(AbstractComponentRegistry.java:624) [infinispan-core-5.1.0.BETA5.jar:]
[java] at org.infinispan.factories.AbstractComponentRegistry.start(AbstractComponentRegistry.java:527) [infinispan-core-5.1.0.BETA5.jar:]
[java] at org.infinispan.factories.ComponentRegistry.start(ComponentRegistry.java:177) [infinispan-core-5.1.0.BETA5.jar:]
[java] at org.infinispan.CacheImpl.start(CacheImpl.java:473) [infinispan-core-5.1.0.BETA5.jar:]
[java] at org.infinispan.manager.DefaultCacheManager.createCache(DefaultCacheManager.java:654) [infinispan-core-5.1.0.BETA5.jar:]
[java] at org.infinispan.manager.DefaultCacheManager.getCache(DefaultCacheManager.java:543) [infinispan-core-5.1.0.BETA5.jar:]
[java] at org.infinispan.manager.DefaultCacheManager.getCache(DefaultCacheManager.java:557) [infinispan-core-5.1.0.BETA5.jar:]
[java] at org.jboss.as.clustering.infinispan.DefaultEmbeddedCacheManager.getCache(DefaultEmbeddedCacheManager.java:100) [jboss-as-clustering-infinispan-7.1.0.Beta1.jar:]
[java] at org.jboss.as.clustering.infinispan.DefaultEmbeddedCacheManager.getCache(DefaultEmbeddedCacheManager.java:91) [jboss-as-clustering-infinispan-7.1.0.Beta1.jar:]
[java] at org.jboss.as.clustering.infinispan.subsystem.CacheService.start(CacheService.java:89) [jboss-as-clustering-infinispan-7.1.0.Beta1.jar:]
[java] at org.jboss.msc.service.ServiceControllerImpl$StartTask.startService(ServiceControllerImpl.java:1824) [jboss-msc-1.0.1.GA.jar:]
[java] at org.jboss.msc.service.ServiceControllerImpl$StartTask.run(ServiceControllerImpl.java:1759) [jboss-msc-1.0.1.GA.jar:]
[java] at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) [:1.6.0_21]
[java] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) [:1.6.0_21]
[java] at java.lang.Thread.run(Thread.java:619) [:1.6.0_21]
[java] 12:11:46,072 ERROR [org.jboss.msc.service.fail] (MSC service thread 1-4) MSC00001: Failed to start service jboss.infinispan.default.memcachedCache: org.jboss.msc.service.StartException in service jboss.infinispan.default.memcachedCache: Failed to start service
[java] at org.jboss.msc.service.ServiceControllerImpl$StartTask.run(ServiceControllerImpl.java:1780) [jboss-msc-1.0.1.GA.jar:]
[java] at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) [:1.6.0_21]
[java] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) [:1.6.0_21]
[java] at java.lang.Thread.run(Thread.java:619) [:1.6.0_21]
[java] Caused by: org.infinispan.CacheException: Unable to invoke method public void org.infinispan.loaders.CacheLoaderManagerImpl.preload() on object
[java] at org.infinispan.util.ReflectionUtil.invokeAccessibly(ReflectionUtil.java:205)
[java] at org.infinispan.factories.AbstractComponentRegistry$PrioritizedMethod.invoke(AbstractComponentRegistry.java:825)
[java] at org.infinispan.factories.AbstractComponentRegistry.internalStart(AbstractComponentRegistry.java:624)
[java] at org.infinispan.factories.AbstractComponentRegistry.start(AbstractComponentRegistry.java:527)
[java] at org.infinispan.factories.ComponentRegistry.start(ComponentRegistry.java:177)
[java] at org.infinispan.CacheImpl.start(CacheImpl.java:473)
[java] at org.infinispan.manager.DefaultCacheManager.createCache(DefaultCacheManager.java:654)
[java] at org.infinispan.manager.DefaultCacheManager.getCache(DefaultCacheManager.java:543)
[java] at org.infinispan.manager.DefaultCacheManager.getCache(DefaultCacheManager.java:557)
[java] at org.jboss.as.clustering.infinispan.DefaultEmbeddedCacheManager.getCache(DefaultEmbeddedCacheManager.java:100)
[java] at org.jboss.as.clustering.infinispan.DefaultEmbeddedCacheManager.getCache(DefaultEmbeddedCacheManager.java:91)
[java] at org.jboss.as.clustering.infinispan.subsystem.CacheService.start(CacheService.java:89)
[java] at org.jboss.msc.service.ServiceControllerImpl$StartTask.startService(ServiceControllerImpl.java:1824) [jboss-msc-1.0.1.GA.jar:]
[java] at org.jboss.msc.service.ServiceControllerImpl$StartTask.run(ServiceControllerImpl.java:1759) [jboss-msc-1.0.1.GA.jar:]
[java] ... 3 more
[java] Caused by: java.lang.reflect.InvocationTargetException
[java] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [:1.6.0_21]
[java] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) [:1.6.0_21]
[java] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) [:1.6.0_21]
[java] at java.lang.reflect.Method.invoke(Method.java:597) [:1.6.0_21]
[java] at org.infinispan.util.ReflectionUtil.invokeAccessibly(ReflectionUtil.java:203)
[java] ... 16 more
[java] Caused by: org.infinispan.CacheException: Unable to preload!
[java] at org.infinispan.loaders.CacheLoaderManagerImpl.preload(CacheLoaderManagerImpl.java:157)
[java] ... 21 more
[java] Caused by: org.infinispan.loaders.CacheLoaderException: SQL error while fetching all StoredEntries
[java] at org.infinispan.loaders.jdbc.DataManipulationHelper.loadSome(DataManipulationHelper.java:254)
[java] at org.infinispan.loaders.jdbc.stringbased.JdbcStringBasedCacheStore.loadLockSafe(JdbcStringBasedCacheStore.java:289)
[java] at org.infinispan.loaders.LockSupportCacheStore.load(LockSupportCacheStore.java:153)
[java] at org.infinispan.loaders.CacheLoaderManagerImpl.loadState(CacheLoaderManagerImpl.java:192)
[java] at org.infinispan.loaders.CacheLoaderManagerImpl.preload(CacheLoaderManagerImpl.java:155)
[java] ... 21 more
[java] Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0'.
[java] at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:197)
[java] at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1493)
[java] at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:390)
[java] at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:340)
[java] at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4575)
[java] at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1400)
[java] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:179)
[java] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:154)
[java] at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:283)
[java] at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
[java] at org.infinispan.loaders.jdbc.DataManipulationHelper.loadSome(DataManipulationHelper.java:245)
[java] ... 25 more
{code}
This is caused by DataManipulationHelper class and its getLoadSomeRowsSql() method. SQL statement for SQL_SERVER needs parentheses around '?' character when substituting a variable in place of ? :
"SELECT TOP ? %s, %s FROM %s" -> "SELECT TOP (?) %s, %s FROM %s"
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
13 years, 1 month