I'm trying to call a SQL query (Oracle DB) to update a column in one table, when some columns in a linked table has certain values. My syntax works fine in the general code, but in my unit test, I get a "BadSQLGrammarException":
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [UPDATE (SELECT ex.STATUS FROM BRRS_EXECUTION ex, BRRS_DEMANDE dem WHERE dem.ID_DEMANDE=ex.ID_DEMANDE AND dem.UTILISATEUR=? AND dem.PROFIL=? AND dem.REGION=? AND dem.REGIME=? AND ex.ID_EXECUTION=?) SET STATUS=?]; nested exception is org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "UPDATE ([*]SELECT EX.STATUS FROM BRRS_EXECUTION EX, BRRS_DEMANDE DEM WHERE DEM.ID_DEMANDE=EX.ID_DEMANDE AND DEM.UTILISATEUR=? AND DEM.PROFIL=? AND DEM.REGION=? AND DEM.REGIME=? AND EX.ID_EXECUTION=?) SET STATUS=? "; expected "identifier"; SQL statement:
UPDATE (SELECT ex.STATUS FROM BRRS_EXECUTION ex, BRRS_DEMANDE dem WHERE dem.ID_DEMANDE=ex.ID_DEMANDE AND dem.UTILISATEUR=? AND dem.PROFIL=? AND dem.REGION=? AND dem.REGIME=? AND ex.ID_EXECUTION=?) SET STATUS=? [42001-190]
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:649)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:870)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:894)
at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:287)
at fr.cnamts.brrsm.services.app.majRequeteSAS.repository.impl.RequeteSASRepositoryOracle.deleteRequeteSAS(RequeteSASRepositoryOracle.java:191)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
at com.sun.proxy.$Proxy46.deleteRequeteSAS(Unknown Source)
at fr.cnamts.brrsm.services.app.majRequeteSAS.repository.RequeteSASRepositoryTest.testSupprimerRequete(RequeteSASRepositoryTest.java:157)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
at org.junit.rules.TestWatcher$1.evaluate(TestWatcher.java:55)
at org.junit.rules.RunRules.evaluate(RunRules.java:20)
at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:252)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:94)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:191)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:678)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)
Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "UPDATE ([*]SELECT EX.STATUS FROM BRRS_EXECUTION EX, BRRS_DEMANDE DEM WHERE DEM.ID_DEMANDE=EX.ID_DEMANDE AND DEM.UTILISATEUR=? AND DEM.PROFIL=? AND DEM.REGION=? AND DEM.REGIME=? AND EX.ID_EXECUTION=?) SET STATUS=? "; expected "identifier"; SQL statement:
UPDATE (SELECT ex.STATUS FROM BRRS_EXECUTION ex, BRRS_DEMANDE dem WHERE dem.ID_DEMANDE=ex.ID_DEMANDE AND dem.UTILISATEUR=? AND dem.PROFIL=? AND dem.REGION=? AND dem.REGIME=? AND ex.ID_EXECUTION=?) SET STATUS=? [42001-190]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
at org.h2.message.DbException.getSyntaxError(DbException.java:205)
at org.h2.command.Parser.readIdentifierWithSchema(Parser.java:3065)
at org.h2.command.Parser.readTableOrView(Parser.java:5270)
at org.h2.command.Parser.readSimpleTableFilter(Parser.java:764)
at org.h2.command.Parser.parseUpdate(Parser.java:707)
at org.h2.command.Parser.parsePrepared(Parser.java:454)
at org.h2.command.Parser.parse(Parser.java:304)
at org.h2.command.Parser.parse(Parser.java:276)
at org.h2.command.Parser.prepareCommand(Parser.java:241)
at org.h2.engine.Session.prepareLocal(Session.java:460)
at org.h2.engine.Session.prepareCommand(Session.java:402)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1188)
at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:72)
at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:276)
at fr.cnamts.brrsm.services.app.utils.test.jdbc.UnclosableConnection.prepareStatement(UnclosableConnection.java:58)
at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.createPreparedStatement(PreparedStatementCreatorFactory.java:238)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:627)
... 50 more
Apparently the problem is between the parenthesis and the select. It seems that it does not accept the "UPDATE (SELECT..." syntax. if you want more of this, here's the query (with constant for column and table names) and the function in which it's used:
private static final String UPDATE_REQUETE_SAS_SQL = "UPDATE (SELECT ex."+ COL_STATUS +
" FROM " + TABLE_BRRS_EXECUTION + " ex, " + TABLE_BRRS_DEMANDE + " dem "
+ "WHERE dem." + COL_ID_DEMANDE + "=ex." + COL_ID_DEMANDE + " "
+ "AND dem." + COL_UTILISATEUR + "=:UTILISATEUR "
+ "AND dem." + COL_PROFIL + "=:PROFIL "
+ "AND dem." + COL_REGION + "=:REGION "
+ "AND dem." + COL_REGIME + "=:REGIME "
+ "AND ex." + COL_ID_EXECUTION + "=:ID_EXECUTION)"
+ " SET " + COL_STATUS +"=:STATUS";
@Override
public void deleteRequeteSAS(RequeteSASEntity requeteSASEntity, String user, String region, String profil, String regime) {
// affectation des parametres nommés
final SqlParameterSource params = new MapSqlParameterSource()
.addValue(COL_STATUS, STATUS_SUPPRIME)
.addValue(COL_ID_EXECUTION, requeteSASEntity.getIdExecution())
.addValue(COL_UTILISATEUR, user)
.addValue(COL_REGION, region)
.addValue(COL_PROFIL, profil)
.addValue(COL_REGIME, regime);
// execution de l'update
jdbcTemplate.update(UPDATE_REQUETE_SAS_SQL, params);
}
And finally here's my test :
@Test public void testSupprimerRequete() throws IOException {
String mockUser = "QUAREFMETIER-00001";
String mockRegion = "99";
String mockProfil = "030";
String mockRegime = "01";
List<RequeteSASEntity> listReqInit = new ArrayList<RequeteSASEntity>();
listReqInit.add(requeteSASJdbc1);
listReqInit.add(requeteSASJdbc2);
requeteSASRepository.deleteRequeteSAS(requeteSASJdbc1, mockUser, mockRegion, mockProfil, mockRegime);
assertTrue(!listReqInit.isEmpty());
assertTrue(listReqInit.get(1).getStatus() == 7);
}
Of course for my tests I use a similar xml defined base, with the very same structure.
So where is the problem in my query and how can I correct it? Or, if it's a problem with the UPDATE (SELECT... syntax, how can I have a similar query which would work? Thank you.
