I am trying to load data from a csv file in to MySQL table using gateway script. This was working fine until I added OPTIONALLY ENCLOSED BY ‘"’ ESCAPED BY ‘"’ to remove " characters from some of the fields. PFB code line and gateway error. The same sql query works fine in MySQL Workbench. Any suggestions? Thanks.
system.db.runPrepUpdate(“LOAD DATA LOCAL INFILE ‘C:/BIMS Import/Tankinfo.csv’ INTO TABLE ign_custom.bimsdata FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY '”’ ESCAPED BY ‘"’ LINES TERMINATED BY ‘\r\n’ IGNORE 1 LINES")
Gateway Error:
com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File “”, line 6, in ’ IGNORE 1 LINES, [null], , , false, false) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:364) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:262) at sun.reflect.GeneratedMethodAccessor63.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(LOAD DATA LOCAL INFILE ‘C:/BIMS Import/Tankinfo.csv’ INTO TABLE ign_custom.bimsdata COLUMNS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ’ ESCAPED BY ’ LINES TERMINATED BY ’ ’ IGNORE 1 LINES, [null], , , false, false)
at org.python.core.Py.JavaError(Py.java:495)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:364)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:262)
at sun.reflect.GeneratedMethodAccessor63.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.python.core.PyReflectedFunction.call(PyReflectedFunction.java:186)
at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction.call(ScriptManager.java:438)
at org.python.core.PyObject.call(PyObject.java:387)
at org.python.core.PyObject.call(PyObject.java:391)
at org.python.pycode._pyx68.f$0(:55)
at org.python.pycode._pyx68.call_function()
at org.python.core.PyTableCode.call(PyTableCode.java:165)
at org.python.core.PyCode.call(PyCode.java:18)
at org.python.core.Py.runCode(Py.java:1275)
at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:657)
at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:616)
at com.inductiveautomation.ignition.common.script.TagChangeScriptExecutor$TagChangeExecutionCallback.execute(TagChangeScriptExecutor.java:187)
at com.inductiveautomation.ignition.common.script.TagChangeScriptExecutor$TagChangeExecutionCallback.execute(TagChangeScriptExecutor.java:134)
at com.inductiveautomation.ignition.common.util.SerialExecutionQueue$PollAndExecute.run(SerialExecutionQueue.java:99)
at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
at java.util.concurrent.FutureTask.run(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Caused by: org.python.core.PyException: Traceback (most recent call last): File “”, line 6, in ’ IGNORE 1 LINES, [null], , , false, false) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:364) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:262) at sun.reflect.GeneratedMethodAccessor63.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(LOAD DATA LOCAL INFILE ‘C:/BIMS Import/Tankinfo.csv’ INTO TABLE ign_custom.bimsdata COLUMNS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ’ ESCAPED BY ’ LINES TERMINATED BY ’ ’ IGNORE 1 LINES, [null], , , false, false)
… 25 common frames omitted
Caused by: java.lang.Exception: Error executing system.db.runPrepUpdate(LOAD DATA LOCAL INFILE ‘C:/BIMS Import/Tankinfo.csv’ INTO TABLE ign_custom.bimsdata COLUMNS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ’ ESCAPED BY ’ LINES TERMINATED BY ’ ’ IGNORE 1 LINES, [null], , , false, false)
… 24 common frames omitted
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Field separator argument is not what is expected; check the manual
at sun.reflect.GeneratedConstructorAccessor129.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1053)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4120)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4052)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2503)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2794)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2458)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2375)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2359)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRPreparedStatement.executeUpdate(SRConnectionWrapper.java:952)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper.runPrepUpdate(SRConnectionWrapper.java:182)
at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities._runPrepStmt(GatewayDBUtilities.java:108)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:260)
… 22 common frames omitted