There are multiple instances where ETL has been failing for multiple implementations whenever special characters are used in the concept name.
Technical Context:
Postgres allows all special characters
But when " need to be in a column then it needs to be prepended with a ". Difficult to write SQL queries in reports making it prone to mistakes
Cases already handled in code:
allowing special characters except double quotes by escaping
allowing concept names above 63 length by truncating
AC:
"(doube quotes) if present in names of concept, organisation, schema, db user to be removed when creating ETL tables or schema based on wherever it will fail. No need to handle in any of the above places, if it will not fail.
Logs:
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [alter table ctrith_iph.individual_child_mdat_12m rename column "FM31. Identifies the longer stick 3 out of 3 or 4 out of 5)" to "FM31. Identifies the "longer" stick 3 out of 3 or 4 out of 5)";] at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:99) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79) at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1538) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:393) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:431) at org.avniproject.etl.repository.SchemaMetadataRepository.lambda$applyChanges$24(SchemaMetadataRepository.java:298) at java.base/java.util.ArrayList.forEach(ArrayList.java:1511) at org.avniproject.etl.repository.SchemaMetadataRepository.applyChanges(SchemaMetadataRepository.java:298) at jdk.internal.reflect.GeneratedMethodAccessor65.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:568) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:702) at org.avniproject.etl.repository.SchemaMetadataRepository$$SpringCGLIB$$0.applyChanges(<generated>) at org.avniproject.etl.service.SchemaMigrationService.migrate(SchemaMigrationService.java:39) at jdk.internal.reflect.GeneratedMethodAccessor61.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:568) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:391) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:702) at org.avniproject.etl.service.SchemaMigrationService$$SpringCGLIB$$0.migrate(<generated>) at org.avniproject.etl.service.EtlService.runFor(EtlService.java:52) at org.avniproject.etl.service.EtlService.runFor(EtlService.java:35) at org.avniproject.etl.scheduler.EtlJob.execute(EtlJob.java:27) at org.quartz.core.JobRunShell.run(JobRunShell.java:202) at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573) Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "longer" Position: 152 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415) at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:335) at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:321) at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:297) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:292) at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94) at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:422) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:381) ... 36 more
Has failed with BadSQLGrammar exception for below cases:
alter table gvamp_uat.distribution add column "Is the activity available for linking?" text;
alter table ctrith_iph.individual_child_mdat_12m rename column "FM31. Identifies the longer stick 3 out of 3 or 4 out of 5)" to "FM31. Identifies the "longer" stick 3 out of 3 or 4 out of 5)"
alter table synctest.person rename column "Items required Text" to "Text"
https://avni.freshdesk.com/a/tickets/3647 more similar tickets
Need:
There are multiple instances where ETL has been failing for multiple implementations whenever special characters are used in the concept name.
Technical Context:
Cases already handled in code:
AC:
Logs:
Has failed with BadSQLGrammar exception for below cases: