search

datacleaner / DataCleaner

The premier open source Data Quality solution
GNU Lesser General Public License v3.0
598 stars 181 forks source link

CreateStagingTable fails when using SQL keywords as table names #752

Open tomaszguzialek opened 9 years ago

tomaszguzialek commented 9 years ago

Created a job with two source tables, a union of them and staging table at the end:

union-refresh-output-columns

Taken first name, last name from both tables and job title from the second one:

union-refresh-output-columns_02

When executing the job, getting such stacktrace:

`ERROR 11:09:49 JdbcUtils - Could not execute create table statement: CREATE TABLE PUBLIC.union (CONTACTLASTNAME VARCHAR, CONTACTFIRSTNAME VARCHAR, JOBTITLE VARCHAR): Syntax error in SQL statement "CREATE TABLE PUBLIC.UNION[*] (CONTACTLASTNAME VARCHAR, CONTACTFIRSTNAME VARCHAR, JOBTITLE VARCHAR) "; expected "identifier"; SQL statement:
CREATE TABLE PUBLIC.union (CONTACTLASTNAME VARCHAR, CONTACTFIRSTNAME VARCHAR, JOBTITLE VARCHAR) [42001-176]
org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "CREATE TABLE PUBLIC.UNION[*] (CONTACTLASTNAME VARCHAR, CONTACTFIRSTNAME VARCHAR, JOBTITLE VARCHAR) "; expected "identifier"; SQL statement:
CREATE TABLE PUBLIC.union (CONTACTLASTNAME VARCHAR, CONTACTFIRSTNAME VARCHAR, JOBTITLE VARCHAR) [42001-176]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:344)
    at org.h2.message.DbException.getSyntaxError(DbException.java:204)
    at org.h2.command.Parser.readIdentifierWithSchema(Parser.java:3033)
    at org.h2.command.Parser.readIdentifierWithSchema(Parser.java:3055)
    at org.h2.command.Parser.parseCreateTable(Parser.java:5699)
    at org.h2.command.Parser.parseCreate(Parser.java:4122)
    at org.h2.command.Parser.parsePrepared(Parser.java:351)
    at org.h2.command.Parser.parse(Parser.java:306)
    at org.h2.command.Parser.parse(Parser.java:278)
    at org.h2.command.Parser.prepareCommand(Parser.java:243)
    at org.h2.engine.Session.prepareLocal(Session.java:442)
    at org.h2.engine.Session.prepareCommand(Session.java:384)
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1188)
    at org.h2.jdbc.JdbcStatement.executeUpdateInternal(JdbcStatement.java:128)
    at org.h2.jdbc.JdbcStatement.executeUpdate(JdbcStatement.java:117)
    at org.apache.metamodel.jdbc.JdbcCreateTableBuilder.execute(JdbcCreateTableBuilder.java:59)
    at org.apache.metamodel.create.CreateTable.run(CreateTable.java:65)
    at org.apache.metamodel.jdbc.JdbcDataContext.executeUpdate(JdbcDataContext.java:784)
    at org.datacleaner.output.datastore.DatastoreOutputWriter.<init>(DatastoreOutputWriter.java:127)
    at org.datacleaner.output.datastore.DatastoreOutputWriterFactory.getWriter(DatastoreOutputWriterFactory.java:58)
    at org.datacleaner.extension.output.CreateStagingTableAnalyzer.createOutputWriter(CreateStagingTableAnalyzer.java:110)
    at org.datacleaner.extension.output.AbstractOutputWriterAnalyzer.init(AbstractOutputWriterAnalyzer.java:53)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.datacleaner.descriptors.AbstractMethodDescriptor.invoke(AbstractMethodDescriptor.java:79)
    at org.datacleaner.descriptors.InitializeMethodDescriptorImpl.initialize(InitializeMethodDescriptorImpl.java:44)
    at org.datacleaner.lifecycle.LifeCycleHelper.initialize(LifeCycleHelper.java:180)
    at org.datacleaner.job.tasks.InitializeTask.executeInternal(InitializeTask.java:83)
    at org.datacleaner.job.tasks.InitializeTask.executeInternal(InitializeTask.java:79)
    at org.datacleaner.job.tasks.InitializeTask.execute(InitializeTask.java:55)
    at org.datacleaner.job.concurrent.TaskRunnable.run(TaskRunnable.java:61)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:744)
tomaszguzialek commented 9 years ago

The name of the table was "union" which is a reserved word in SQL... Works with a different name. Should consider warning the user when reserved keywords are specified in the names.

LosD commented 8 years ago

My guess would be that we can escape the table names somehow (like using [tablename] in SQL Server), though that is probably something we need to do in MetaModel.