ringframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [ MERGE INTO external_term as target USING stg_external_term as source ) WHEN NOT MATCHED AND source.batch_id >= 0 and source.batch_id <= 99 THEN INSERT (code,nam e,start_date,end_date,report_year) VALUES ( source.code,source.name,source.start _date,source.end_date,source.report_year) WHEN MATCHED AND source.batch_id >= 0 and source.batch_id <= 99 THEN UPDATE SET target.code=source.code,target.name=so urce.name,target.start_date=source.start_date,target.end_date=source.end_date,ta rget.report_year=source.report_year;]; nested exception is java.sql.BatchUpdateE xception: Incorrect syntax near ')'. at org.jasig.ssp.util.importer.job.staging.SqlServerExternalTableUpsertW riter.write(SqlServerExternalTableUpsertWriter.java:151) ~[ssp-data-importer-imp l-1.0.0.jar:1.0.0]

Apereo Issues | Dan McCallum | 3 years ago
  1. 0

    Had a case recently when primary keys went missing in a SSP database, including keys on {{external_\*}} and {{stg_external_\*}} tables. This resulted in a very obscure failure mode b/c the Importer (not irrationally) doesn't guard against situations where it can't find a PK in the db for any given {{external_\*}} / {{stg_external_\*}} pair. While this should be a rare occurrence, it _has_ happened in the wild, and the Importer should error out more gracefully. Here was the obscure error from the field. Note the malformed SQL: {noformat} 12:11:44.623 [taskExecutor-1] ERROR o.s.batch.core.step.AbstractStep - Encounter ed an error executing the step org.springframework.retry.ExhaustedRetryException: Retry exhausted after last at tempt in recovery path, but exception is not skippable.; nested exception is org .jasig.ssp.util.importer.job.staging.NotSkippableException: org.springframework. jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [ MERGE INTO ext ernal_term as target USING stg_external_term as source ) WHEN NOT MATCHED AND so urce.batch_id >= 0 and source.batch_id <= 99 THEN INSERT (code,name,start_date,e nd_date,report_year) VALUES ( source.code,source.name,source.start_date,source.e nd_date,source.report_year) WHEN MATCHED AND source.batch_id >= 0 and source.bat ch_id <= 99 THEN UPDATE SET target.code=source.code,target.name=source.name,targ et.start_date=source.start_date,target.end_date=source.end_date,target.report_ye ar=source.report_year;]; nested exception is java.sql.BatchUpdateException: Inco rrect syntax near ')'. at org.springframework.batch.core.step.item.FaultTolerantChunkProcessor$ 5.recover(FaultTolerantChunkProcessor.java:406) ~[spring-batch-core-2.2.2.RELEAS E.jar:na] at org.springframework.retry.support.RetryTemplate.handleRetryExhausted( RetryTemplate.java:435) ~[spring-retry-1.0.2.RELEASE.jar:na] at org.springframework.retry.support.RetryTemplate.doExecute(RetryTempla te.java:304) ~[spring-retry-1.0.2.RELEASE.jar:na] at org.springframework.retry.support.RetryTemplate.execute(RetryTemplate .java:188) ~[spring-retry-1.0.2.RELEASE.jar:na] at org.springframework.batch.core.step.item.BatchRetryTemplate.execute(B atchRetryTemplate.java:217) ~[spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.step.item.FaultTolerantChunkProcessor. write(FaultTolerantChunkProcessor.java:423) ~[spring-batch-core-2.2.2.RELEASE.ja r:na] at org.springframework.batch.core.step.item.SimpleChunkProcessor.process (SimpleChunkProcessor.java:199) ~[spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.step.item.ChunkOrientedTasklet.execute (ChunkOrientedTasklet.java:75) ~[spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransact ionCallback.doInTransaction(TaskletStep.java:395) ~[spring-batch-core-2.2.2.RELE ASE.jar:na] at org.springframework.transaction.support.TransactionTemplate.execute(T ransactionTemplate.java:130) ~[spring-tx-3.2.0.RELEASE.jar:3.2.0.RELEASE] at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkCo ntext(TaskletStep.java:267) ~[spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.scope.context.StepContextRepeatCallbac k.doInIteration(StepContextRepeatCallback.java:77) ~[spring-batch-core-2.2.2.REL EASE.jar:na] at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult (RepeatTemplate.java:368) ~[spring-batch-infrastructure-2.2.2.RELEASE.jar:na] at org.springframework.batch.repeat.support.RepeatTemplate.executeIntern al(RepeatTemplate.java:215) ~[spring-batch-infrastructure-2.2.2.RELEASE.jar:na] at org.springframework.batch.repeat.support.RepeatTemplate.iterate(Repea tTemplate.java:144) ~[spring-batch-infrastructure-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.step.tasklet.TaskletStep.doExecute(Tas kletStep.java:253) ~[spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep .java:195) ~[spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.partition.support.TaskExecutorPartitio nHandler$1.call(TaskExecutorPartitionHandler.java:139) [spring-batch-core-2.2.2. RELEASE.jar:na] at org.springframework.batch.core.partition.support.TaskExecutorPartitio nHandler$1.call(TaskExecutorPartitionHandler.java:136) [spring-batch-core-2.2.2. RELEASE.jar:na] at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source) [na:1.6 .0_45] at java.util.concurrent.FutureTask.run(Unknown Source) [na:1.6.0_45] at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source ) [na:1.6.0_45] at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [n a:1.6.0_45] at java.lang.Thread.run(Unknown Source) [na:1.6.0_45] Caused by: org.jasig.ssp.util.importer.job.staging.NotSkippableException: org.sp ringframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [ MERGE INTO external_term as target USING stg_external_term as source ) WHEN NOT MATCHED AND source.batch_id >= 0 and source.batch_id <= 99 THEN INSERT (code,nam e,start_date,end_date,report_year) VALUES ( source.code,source.name,source.start _date,source.end_date,source.report_year) WHEN MATCHED AND source.batch_id >= 0 and source.batch_id <= 99 THEN UPDATE SET target.code=source.code,target.name=so urce.name,target.start_date=source.start_date,target.end_date=source.end_date,ta rget.report_year=source.report_year;]; nested exception is java.sql.BatchUpdateE xception: Incorrect syntax near ')'. at org.jasig.ssp.util.importer.job.staging.SqlServerExternalTableUpsertW riter.write(SqlServerExternalTableUpsertWriter.java:151) ~[ssp-data-importer-imp l-1.0.0.jar:1.0.0] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.6. 0_45] at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) ~[na:1.6. 0_45] at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[na: 1.6.0_45] at java.lang.reflect.Method.invoke(Unknown Source) ~[na:1.6.0_45] at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflecti on(AopUtils.java:317) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJo inpoint(ReflectiveMethodInvocation.java:183) ~[spring-aop-3.2.2.RELEASE.jar:3.2. 2.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed( ReflectiveMethodInvocation.java:150) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEAS E] at org.springframework.aop.support.DelegatingIntroductionInterceptor.doP roceed(DelegatingIntroductionInterceptor.java:132) ~[spring-aop-3.2.2.RELEASE.ja r:3.2.2.RELEASE] at org.springframework.aop.support.DelegatingIntroductionInterceptor.inv oke(DelegatingIntroductionInterceptor.java:120) ~[spring-aop-3.2.2.RELEASE.jar:3 .2.2.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed( ReflectiveMethodInvocation.java:172) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEAS E] at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynami cAopProxy.java:204) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE] at com.sun.proxy.$Proxy0.write(Unknown Source) ~[na:na] at org.springframework.batch.item.support.CompositeItemWriter.write(Comp ositeItemWriter.java:51) ~[spring-batch-infrastructure-2.2.2.RELEASE.jar:na] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.6. 0_45] at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) ~[na:1.6. 0_45] at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[na: 1.6.0_45] at java.lang.reflect.Method.invoke(Unknown Source) ~[na:1.6.0_45] at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflecti on(AopUtils.java:317) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJo inpoint(ReflectiveMethodInvocation.java:183) ~[spring-aop-3.2.2.RELEASE.jar:3.2. 2.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed( ReflectiveMethodInvocation.java:150) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEAS E] at org.springframework.aop.support.DelegatingIntroductionInterceptor.doP roceed(DelegatingIntroductionInterceptor.java:132) ~[spring-aop-3.2.2.RELEASE.ja r:3.2.2.RELEASE] at org.springframework.aop.support.DelegatingIntroductionInterceptor.inv oke(DelegatingIntroductionInterceptor.java:120) ~[spring-aop-3.2.2.RELEASE.jar:3 .2.2.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed( ReflectiveMethodInvocation.java:172) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEAS E] at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynami cAopProxy.java:204) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE] at com.sun.proxy.$Proxy8.write(Unknown Source) ~[na:na] at org.springframework.batch.core.step.item.SimpleChunkProcessor.writeIt ems(SimpleChunkProcessor.java:175) ~[spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.step.item.SimpleChunkProcessor.doWrite (SimpleChunkProcessor.java:151) ~[spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.step.item.FaultTolerantChunkProcessor$ 3.doWithRetry(FaultTolerantChunkProcessor.java:330) ~[spring-batch-core-2.2.2.RE LEASE.jar:na] at org.springframework.retry.support.RetryTemplate.doExecute(RetryTempla te.java:255) ~[spring-retry-1.0.2.RELEASE.jar:na] ... 21 common frames omitted Caused by: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; b ad SQL grammar [ MERGE INTO external_term as target USING stg_external_term as s ource ) WHEN NOT MATCHED AND source.batch_id >= 0 and source.batch_id <= 99 THEN INSERT (code,name,start_date,end_date,report_year) VALUES ( source.code,source. name,source.start_date,source.end_date,source.report_year) WHEN MATCHED AND sour ce.batch_id >= 0 and source.batch_id <= 99 THEN UPDATE SET target.code=source.co de,target.name=source.name,target.start_date=source.start_date,target.end_date=s ource.end_date,target.report_year=source.report_year;]; nested exception is java .sql.BatchUpdateException: Incorrect syntax near ')'. at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTra nslate(SQLStateSQLExceptionTranslator.java:98) ~[spring-jdbc-3.2.2.RELEASE.jar:3 .2.2.RELEASE] at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslat or.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~[spring-jdbc-3.2.2 .RELEASE.jar:3.2.2.RELEASE] at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslat or.translate(AbstractFallbackSQLExceptionTranslator.java:80) ~[spring-jdbc-3.2.2 .RELEASE.jar:3.2.2.RELEASE] at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslat or.translate(AbstractFallbackSQLExceptionTranslator.java:80) ~[spring-jdbc-3.2.2 .RELEASE.jar:3.2.2.RELEASE] at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java: 407) ~[spring-jdbc-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.j ava:557) ~[spring-jdbc-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.jasig.ssp.util.importer.job.staging.SqlServerExternalTableUpsertW riter.write(SqlServerExternalTableUpsertWriter.java:136) ~[ssp-data-importer-imp l-1.0.0.jar:1.0.0] ... 50 common frames omitted Caused by: java.sql.BatchUpdateException: Incorrect syntax near ')'. at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.ja va:947) ~[jtds-1.2.4.jar:1.2.4] at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingSt atement.java:297) ~[commons-dbcp-1.2.2.jar:1.2.2] at org.springframework.jdbc.core.JdbcTemplate$1BatchUpdateStatementCallb ack.doInStatement(JdbcTemplate.java:538) ~[spring-jdbc-3.2.2.RELEASE.jar:3.2.2.R ELEASE] at org.springframework.jdbc.core.JdbcTemplate$1BatchUpdateStatementCallb ack.doInStatement(JdbcTemplate.java:529) ~[spring-jdbc-3.2.2.RELEASE.jar:3.2.2.R ELEASE] at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java: 396) ~[spring-jdbc-3.2.2.RELEASE.jar:3.2.2.RELEASE] ... 52 common frames omitted {noformat}

    Apereo Issues | 3 years ago | Dan McCallum
    ringframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [ MERGE INTO external_term as target USING stg_external_term as source ) WHEN NOT MATCHED AND source.batch_id >= 0 and source.batch_id <= 99 THEN INSERT (code,nam e,start_date,end_date,report_year) VALUES ( source.code,source.name,source.start _date,source.end_date,source.report_year) WHEN MATCHED AND source.batch_id >= 0 and source.batch_id <= 99 THEN UPDATE SET target.code=source.code,target.name=so urce.name,target.start_date=source.start_date,target.end_date=source.end_date,ta rget.report_year=source.report_year;]; nested exception is java.sql.BatchUpdateE xception: Incorrect syntax near ')'. at org.jasig.ssp.util.importer.job.staging.SqlServerExternalTableUpsertW riter.write(SqlServerExternalTableUpsertWriter.java:151) ~[ssp-data-importer-imp l-1.0.0.jar:1.0.0]
  2. 0

    Had a case recently when primary keys went missing in a SSP database, including keys on {{external_\*}} and {{stg_external_\*}} tables. This resulted in a very obscure failure mode b/c the Importer (not irrationally) doesn't guard against situations where it can't find a PK in the db for any given {{external_\*}} / {{stg_external_\*}} pair. While this should be a rare occurrence, it _has_ happened in the wild, and the Importer should error out more gracefully. Here was the obscure error from the field. Note the malformed SQL: {noformat} 12:11:44.623 [taskExecutor-1] ERROR o.s.batch.core.step.AbstractStep - Encounter ed an error executing the step org.springframework.retry.ExhaustedRetryException: Retry exhausted after last at tempt in recovery path, but exception is not skippable.; nested exception is org .jasig.ssp.util.importer.job.staging.NotSkippableException: org.springframework. jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [ MERGE INTO ext ernal_term as target USING stg_external_term as source ) WHEN NOT MATCHED AND so urce.batch_id >= 0 and source.batch_id <= 99 THEN INSERT (code,name,start_date,e nd_date,report_year) VALUES ( source.code,source.name,source.start_date,source.e nd_date,source.report_year) WHEN MATCHED AND source.batch_id >= 0 and source.bat ch_id <= 99 THEN UPDATE SET target.code=source.code,target.name=source.name,targ et.start_date=source.start_date,target.end_date=source.end_date,target.report_ye ar=source.report_year;]; nested exception is java.sql.BatchUpdateException: Inco rrect syntax near ')'. at org.springframework.batch.core.step.item.FaultTolerantChunkProcessor$ 5.recover(FaultTolerantChunkProcessor.java:406) ~[spring-batch-core-2.2.2.RELEAS E.jar:na] at org.springframework.retry.support.RetryTemplate.handleRetryExhausted( RetryTemplate.java:435) ~[spring-retry-1.0.2.RELEASE.jar:na] at org.springframework.retry.support.RetryTemplate.doExecute(RetryTempla te.java:304) ~[spring-retry-1.0.2.RELEASE.jar:na] at org.springframework.retry.support.RetryTemplate.execute(RetryTemplate .java:188) ~[spring-retry-1.0.2.RELEASE.jar:na] at org.springframework.batch.core.step.item.BatchRetryTemplate.execute(B atchRetryTemplate.java:217) ~[spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.step.item.FaultTolerantChunkProcessor. write(FaultTolerantChunkProcessor.java:423) ~[spring-batch-core-2.2.2.RELEASE.ja r:na] at org.springframework.batch.core.step.item.SimpleChunkProcessor.process (SimpleChunkProcessor.java:199) ~[spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.step.item.ChunkOrientedTasklet.execute (ChunkOrientedTasklet.java:75) ~[spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransact ionCallback.doInTransaction(TaskletStep.java:395) ~[spring-batch-core-2.2.2.RELE ASE.jar:na] at org.springframework.transaction.support.TransactionTemplate.execute(T ransactionTemplate.java:130) ~[spring-tx-3.2.0.RELEASE.jar:3.2.0.RELEASE] at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkCo ntext(TaskletStep.java:267) ~[spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.scope.context.StepContextRepeatCallbac k.doInIteration(StepContextRepeatCallback.java:77) ~[spring-batch-core-2.2.2.REL EASE.jar:na] at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult (RepeatTemplate.java:368) ~[spring-batch-infrastructure-2.2.2.RELEASE.jar:na] at org.springframework.batch.repeat.support.RepeatTemplate.executeIntern al(RepeatTemplate.java:215) ~[spring-batch-infrastructure-2.2.2.RELEASE.jar:na] at org.springframework.batch.repeat.support.RepeatTemplate.iterate(Repea tTemplate.java:144) ~[spring-batch-infrastructure-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.step.tasklet.TaskletStep.doExecute(Tas kletStep.java:253) ~[spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep .java:195) ~[spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.partition.support.TaskExecutorPartitio nHandler$1.call(TaskExecutorPartitionHandler.java:139) [spring-batch-core-2.2.2. RELEASE.jar:na] at org.springframework.batch.core.partition.support.TaskExecutorPartitio nHandler$1.call(TaskExecutorPartitionHandler.java:136) [spring-batch-core-2.2.2. RELEASE.jar:na] at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source) [na:1.6 .0_45] at java.util.concurrent.FutureTask.run(Unknown Source) [na:1.6.0_45] at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source ) [na:1.6.0_45] at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [n a:1.6.0_45] at java.lang.Thread.run(Unknown Source) [na:1.6.0_45] Caused by: org.jasig.ssp.util.importer.job.staging.NotSkippableException: org.sp ringframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [ MERGE INTO external_term as target USING stg_external_term as source ) WHEN NOT MATCHED AND source.batch_id >= 0 and source.batch_id <= 99 THEN INSERT (code,nam e,start_date,end_date,report_year) VALUES ( source.code,source.name,source.start _date,source.end_date,source.report_year) WHEN MATCHED AND source.batch_id >= 0 and source.batch_id <= 99 THEN UPDATE SET target.code=source.code,target.name=so urce.name,target.start_date=source.start_date,target.end_date=source.end_date,ta rget.report_year=source.report_year;]; nested exception is java.sql.BatchUpdateE xception: Incorrect syntax near ')'. at org.jasig.ssp.util.importer.job.staging.SqlServerExternalTableUpsertW riter.write(SqlServerExternalTableUpsertWriter.java:151) ~[ssp-data-importer-imp l-1.0.0.jar:1.0.0] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.6. 0_45] at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) ~[na:1.6. 0_45] at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[na: 1.6.0_45] at java.lang.reflect.Method.invoke(Unknown Source) ~[na:1.6.0_45] at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflecti on(AopUtils.java:317) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJo inpoint(ReflectiveMethodInvocation.java:183) ~[spring-aop-3.2.2.RELEASE.jar:3.2. 2.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed( ReflectiveMethodInvocation.java:150) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEAS E] at org.springframework.aop.support.DelegatingIntroductionInterceptor.doP roceed(DelegatingIntroductionInterceptor.java:132) ~[spring-aop-3.2.2.RELEASE.ja r:3.2.2.RELEASE] at org.springframework.aop.support.DelegatingIntroductionInterceptor.inv oke(DelegatingIntroductionInterceptor.java:120) ~[spring-aop-3.2.2.RELEASE.jar:3 .2.2.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed( ReflectiveMethodInvocation.java:172) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEAS E] at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynami cAopProxy.java:204) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE] at com.sun.proxy.$Proxy0.write(Unknown Source) ~[na:na] at org.springframework.batch.item.support.CompositeItemWriter.write(Comp ositeItemWriter.java:51) ~[spring-batch-infrastructure-2.2.2.RELEASE.jar:na] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.6. 0_45] at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) ~[na:1.6. 0_45] at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[na: 1.6.0_45] at java.lang.reflect.Method.invoke(Unknown Source) ~[na:1.6.0_45] at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflecti on(AopUtils.java:317) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJo inpoint(ReflectiveMethodInvocation.java:183) ~[spring-aop-3.2.2.RELEASE.jar:3.2. 2.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed( ReflectiveMethodInvocation.java:150) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEAS E] at org.springframework.aop.support.DelegatingIntroductionInterceptor.doP roceed(DelegatingIntroductionInterceptor.java:132) ~[spring-aop-3.2.2.RELEASE.ja r:3.2.2.RELEASE] at org.springframework.aop.support.DelegatingIntroductionInterceptor.inv oke(DelegatingIntroductionInterceptor.java:120) ~[spring-aop-3.2.2.RELEASE.jar:3 .2.2.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed( ReflectiveMethodInvocation.java:172) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEAS E] at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynami cAopProxy.java:204) ~[spring-aop-3.2.2.RELEASE.jar:3.2.2.RELEASE] at com.sun.proxy.$Proxy8.write(Unknown Source) ~[na:na] at org.springframework.batch.core.step.item.SimpleChunkProcessor.writeIt ems(SimpleChunkProcessor.java:175) ~[spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.step.item.SimpleChunkProcessor.doWrite (SimpleChunkProcessor.java:151) ~[spring-batch-core-2.2.2.RELEASE.jar:na] at org.springframework.batch.core.step.item.FaultTolerantChunkProcessor$ 3.doWithRetry(FaultTolerantChunkProcessor.java:330) ~[spring-batch-core-2.2.2.RE LEASE.jar:na] at org.springframework.retry.support.RetryTemplate.doExecute(RetryTempla te.java:255) ~[spring-retry-1.0.2.RELEASE.jar:na] ... 21 common frames omitted Caused by: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; b ad SQL grammar [ MERGE INTO external_term as target USING stg_external_term as s ource ) WHEN NOT MATCHED AND source.batch_id >= 0 and source.batch_id <= 99 THEN INSERT (code,name,start_date,end_date,report_year) VALUES ( source.code,source. name,source.start_date,source.end_date,source.report_year) WHEN MATCHED AND sour ce.batch_id >= 0 and source.batch_id <= 99 THEN UPDATE SET target.code=source.co de,target.name=source.name,target.start_date=source.start_date,target.end_date=s ource.end_date,target.report_year=source.report_year;]; nested exception is java .sql.BatchUpdateException: Incorrect syntax near ')'. at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTra nslate(SQLStateSQLExceptionTranslator.java:98) ~[spring-jdbc-3.2.2.RELEASE.jar:3 .2.2.RELEASE] at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslat or.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~[spring-jdbc-3.2.2 .RELEASE.jar:3.2.2.RELEASE] at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslat or.translate(AbstractFallbackSQLExceptionTranslator.java:80) ~[spring-jdbc-3.2.2 .RELEASE.jar:3.2.2.RELEASE] at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslat or.translate(AbstractFallbackSQLExceptionTranslator.java:80) ~[spring-jdbc-3.2.2 .RELEASE.jar:3.2.2.RELEASE] at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java: 407) ~[spring-jdbc-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.j ava:557) ~[spring-jdbc-3.2.2.RELEASE.jar:3.2.2.RELEASE] at org.jasig.ssp.util.importer.job.staging.SqlServerExternalTableUpsertW riter.write(SqlServerExternalTableUpsertWriter.java:136) ~[ssp-data-importer-imp l-1.0.0.jar:1.0.0] ... 50 common frames omitted Caused by: java.sql.BatchUpdateException: Incorrect syntax near ')'. at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.ja va:947) ~[jtds-1.2.4.jar:1.2.4] at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingSt atement.java:297) ~[commons-dbcp-1.2.2.jar:1.2.2] at org.springframework.jdbc.core.JdbcTemplate$1BatchUpdateStatementCallb ack.doInStatement(JdbcTemplate.java:538) ~[spring-jdbc-3.2.2.RELEASE.jar:3.2.2.R ELEASE] at org.springframework.jdbc.core.JdbcTemplate$1BatchUpdateStatementCallb ack.doInStatement(JdbcTemplate.java:529) ~[spring-jdbc-3.2.2.RELEASE.jar:3.2.2.R ELEASE] at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java: 396) ~[spring-jdbc-3.2.2.RELEASE.jar:3.2.2.RELEASE] ... 52 common frames omitted {noformat}

    Apereo Issues | 3 years ago | Dan McCallum
    ringframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [ MERGE INTO external_term as target USING stg_external_term as source ) WHEN NOT MATCHED AND source.batch_id >= 0 and source.batch_id <= 99 THEN INSERT (code,nam e,start_date,end_date,report_year) VALUES ( source.code,source.name,source.start _date,source.end_date,source.report_year) WHEN MATCHED AND source.batch_id >= 0 and source.batch_id <= 99 THEN UPDATE SET target.code=source.code,target.name=so urce.name,target.start_date=source.start_date,target.end_date=source.end_date,ta rget.report_year=source.report_year;]; nested exception is java.sql.BatchUpdateE xception: Incorrect syntax near ')'. at org.jasig.ssp.util.importer.job.staging.SqlServerExternalTableUpsertW riter.write(SqlServerExternalTableUpsertWriter.java:151) ~[ssp-data-importer-imp l-1.0.0.jar:1.0.0]

    Root Cause Analysis

    1. ringframework.jdbc.BadSqlGrammarException

      StatementCallback; bad SQL grammar [ MERGE INTO external_term as target USING stg_external_term as source ) WHEN NOT MATCHED AND source.batch_id >= 0 and source.batch_id <= 99 THEN INSERT (code,nam e,start_date,end_date,report_year) VALUES ( source.code,source.name,source.start _date,source.end_date,source.report_year) WHEN MATCHED AND source.batch_id >= 0 and source.batch_id <= 99 THEN UPDATE SET target.code=source.code,target.name=so urce.name,target.start_date=source.start_date,target.end_date=source.end_date,ta rget.report_year=source.report_year;]; nested exception is java.sql.BatchUpdateE xception: Incorrect syntax near ')'. at org.jasig.ssp.util.importer.job.staging.SqlServerExternalTableUpsertW riter.write(SqlServerExternalTableUpsertWriter.java:151) ~[ssp-data-importer-imp l-1.0.0.jar:1.0.0]

      at sun.reflect.NativeMethodAccessorImpl.invoke0()
    2. Java RT
      Method.invoke
      1. sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      2. sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
      3. sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)[na:1.6.0_45]
      4. java.lang.reflect.Method.invoke(Unknown Source)[na:1.6.0_45]
      4 frames