I have a small spring boot 2 application, where I want to import a csv into Exasol using hibernates native query. Exasol offers its own SQL flavor to import a file from a remote server, hence the native query.
my repository:
@Component
public class ExasolRepositoryImpl implements ExasolRepository {
private EntityManager entityManager
public ExasolRepositoryImpl(EntityManager entityManager) {
this.entityManager = entityManager;
}
@Override
public int importCsv(String filename) {
Query nativeQuery = entityManager.createNativeQuery("IMPORT INTO mytable FROM CSV AT 'localhost:22/upload' USER 'user' IDENTIFIED BY 'pass' FILE :filename");
nativeQuery.setParameter("filename", filename);
return (int) nativeQuery.getSingleResult();
}
}
The repository Interface is just a regular interface (no CrudRepository). The Main looks like this:
@SpringBootApplication
public class DemoApplication {
public static void main(String[] args) {
SpringApplication.run(DemoApplication.class, args);
}
@Bean
public CommandLineRunner run(@Qualifier("exasolRepositoryImpl") final ExasolRepository exasolService){
String filename = "example.csv";
return args -> exasolService.importCsv(filename);
}
}
The error:
ava.lang.IllegalStateException: Failed to execute CommandLineRunner
at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:816) [spring-boot-2.1.4.RELEASE.jar:2.1.4.RELEASE]
at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:797) [spring-boot-2.1.4.RELEASE.jar:2.1.4.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:324) [spring-boot-2.1.4.RELEASE.jar:2.1.4.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1260) [spring-boot-2.1.4.RELEASE.jar:2.1.4.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1248) [spring-boot-2.1.4.RELEASE.jar:2.1.4.RELEASE]
at com.example.demo.DemoApplication.main(DemoApplication.java:13) [classes/:na]
Caused by: org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; SQL [IMPORT INTO mytable FROM CSV AT 'localhost:22/upload' USER 'user' IDENTIFIED BY 'pass' FILE ?]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:279) ~[spring-orm-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:253) ~[spring-orm-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:527) ~[spring-orm-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61) ~[spring-tx-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242) ~[spring-tx-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:153) ~[spring-tx-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688) ~[spring-aop-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at com.example.demo.ExasolRepositoryImpl$$EnhancerBySpringCGLIB$$32884c0.importCsv(<generated>) ~[classes/:na]
at com.example.demo.DemoApplication.lambda$run$0(DemoApplication.java:20) [classes/:na]
at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:813) [spring-boot-2.1.4.RELEASE.jar:2.1.4.RELEASE]
... 5 common frames omitted
Caused by: org.hibernate.exception.SQLGrammarException: could not prepare statement
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106) ~[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) ~[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113) ~[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:182) ~[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:148) ~[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1984) ~[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1914) ~[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1892) ~[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
at org.hibernate.loader.Loader.doQuery(Loader.java:937) ~[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:340) ~[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
at org.hibernate.loader.Loader.doList(Loader.java:2689) ~[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
at org.hibernate.loader.Loader.doList(Loader.java:2672) ~[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2506) ~[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
at org.hibernate.loader.Loader.list(Loader.java:2501) ~[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:338) ~[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2222) ~[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:1063) ~[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:170) ~[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1505) ~[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
at org.hibernate.query.internal.AbstractProducedQuery.getSingleResult(AbstractProducedQuery.java:1553) ~[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_212]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_212]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_212]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_212]
at org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:402) ~[spring-orm-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at com.sun.proxy.$Proxy64.getSingleResult(Unknown Source) ~[na:na]
at com.example.demo.ExasolRepositoryImpl.importCsv(ExasolRepositoryImpl.java:22) ~[classes/:na]
at com.example.demo.ExasolRepositoryImpl$$FastClassBySpringCGLIB$$51c05c86.invoke(<generated>) ~[classes/:na]
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:749) ~[spring-aop-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139) ~[spring-tx-5.1.6.RELEASE.jar:5.1.6.RELEASE]
... 10 common frames omitted
Caused by: java.sql.SQLException: syntax error, unexpected '?', expecting SIMPLE_STRING_LITERAL_ [line 1, column 93] (Session: 1633538183925809411)
It seems that the :filename is not replaced. I also tried positional parameters, but the result is the same.
I tried to follow via debugger into the Hibernate sources to see, where it happens, but could not find the issue.
My guess would be a problem with the custom hibernate dialect / Exasol jdbc driver
