Problem from uploading data to MySQL using LOAD DATA INFILE

[bokechal]

Hi,
I have a data text file that I'd like to upload to a table in MySQL DB.

I tried to execute this simple code in the Script Console

system.db.runPrepUpdate("LOAD DATA INFILE 'F:\\output_temp.txt' INTO TABLE mro.expense_distribution_detail FIELDS TERMINATED BY '|'")

and got below error.

[quote]Java Traceback:

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.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.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:430)

at org.python.core.PyObject.__call__(PyObject.java:320)

at org.python.pycode._pyx313.f$0(<buffer>:15)

at org.python.pycode._pyx313.call_function(<buffer>)

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 org.python.core.Py.exec(Py.java:1319)

at org.python.util.PythonInterpreter.exec(PythonInterpreter.java:215)

at org.python.util.InteractiveInterpreter.runcode(InteractiveInterpreter.java:89)

at org.python.util.InteractiveInterpreter.runsource(InteractiveInterpreter.java:70)

at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$InterpreterWorker.doInBackground(JythonConsole.java:419)

at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$InterpreterWorker.doInBackground(JythonConsole.java:407)

at javax.swing.SwingWorker$1.call(Unknown Source)

at java.util.concurrent.FutureTask.run(Unknown Source)

at javax.swing.SwingWorker.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: java.lang.Exception: Error executing system.db.runPrepUpdate(LOAD DATA INFILE 'F:\output_temp.txt' INTO TABLE mro.expense_distribution_detail FIELDS TERMINATED BY '|', [null], , , true, false)

... 26 more

Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: SQL error for "LOAD DATA INFILE 'F:\output_temp.txt' INTO TABLE mro.expense_distribution_detail FIELDS TERMINATED BY '|'": Incorrect string value: '\x96 5PM....' for column 'description' at row 44

at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:327)

at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:301)

at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:258)

at com.inductiveautomation.ignition.client.script.ClientDBUtilities._runPrepStmt(ClientDBUtilities.java:137)

at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:260)

... 24 more

Caused by: java.sql.SQLException: Incorrect string value: '\x96 5PM....' for column 'description' at row 44

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:998)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3847)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3783)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2447)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2594)

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545)

at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1901)

at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2113)

at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2049)

at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2034)

at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)

at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRPreparedStatement.executeUpdate(SRConnectionWrapper.java:871)

at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.RunPrepStmt.runUpdateQuery(RunPrepStmt.java:65)

at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractUpdateQuery.run(AbstractUpdateQuery.java:34)

at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractDBAction.invoke(AbstractDBAction.java:76)

at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:345)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)

at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:85)

at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:837)

at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:583)

at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)

at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:548)

at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:226)

at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1160)

at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:511)

at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)

at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1092)

at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)

at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52)

at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:134)

at org.eclipse.jetty.server.Server.handle(Server.java:518)

at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:308)

at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:244)

at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:273)

at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:95)

at org.eclipse.jetty.io.SelectChannelEndPoint$2.run(SelectChannelEndPoint.java:93)

at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.produceAndRun(ExecuteProduceConsume.java:246)

at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.run(ExecuteProduceConsume.java:156)

at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:654)

at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:572)

at java.lang.Thread.run(null)

Traceback (most recent call last):
File "", line 14, in
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.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.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 INFILE 'F:\output_temp.txt' INTO TABLE mro.expense_distribution_detail FIELDS TERMINATED BY '|', [null], , , true, false)[/quote]
When I tried the same sql statement in MySQL workbench, all data got uploaded with no error.

This is the line of data that got the error. I notice that the error is one in bold face below ("description" column).

44|2017-01-24 16:35:54.193000|"NOV-17"|"88526"|"SCREEN SPE USA LLC"|"0237847-IN"|2016-10-31 00:00:00|"USD"|5266.67|"Y"|5266.67|"FR6N35517HT"||"8020600.1143.22.20.00.00.00"|"8020600"|"Expense"|"Outside Services"|"On call service contract $63,200 for one year and one FSE. SCREEN will provide one FSE on call 7 days a week 8AM – 5PM. Guaranteed minimum 4 hour response time. The contract period will be from October 3, 2016 through October 2, 2017."|"Renewal of oncall service contract support for SCREEN/DNS This will enable us to have onsite support for all of the DNS tracks we have onsiteon call coverage will be 7X8."|2016-10-31 00:00:00|2016-11-30 00:00:00|2016-10-31 00:00:00

The F:\output_temp.txt data file was parsed from the raw text data file.
Does anyone have any suggestion what I should do?

Best,

Ignition 7.8.4 (b2016082217)
Java8-73
Windows8

[pturmel]

[quote=“bokechal”]Incorrect string value: ‘\x96 5PM…’ for column ‘description’ at row 44[/quote]Note the encoding for the dash. Looks like a mismatch in the encoding between the assumed type for the file and the column you are inserting to. That dash is U+2013, not the normal U+002D hyphen. The utf8 encoding for U+2013 is 0xE2 0x80 0x93, which is probably getting corrupted by attempted encoding resolution. If the column can’t handle that special character (forgot to use utf8?), the server will reject it.

[bokechal]

Thank you for your response.

In my parser code, I have to encode with utf-8 before writing to a file. It’s working now.