SQL Query Help - Bulk Insert

jreynolds · January 9, 2018, 3:05pm

I’m getting a syntax error when I run the following query, from a button.

Query:

 getFrom=r'\\wtz-svr-fs-01\FileShare\Wentzville\SeqPublic\WEN-201 LOC\Container Management System\MARS_Import\TVS Updated CSV_Fixed.csv'
 rowTerm=r'\n'
 fieldTerm=r'~'
 errorPath=r'\\wtz-svr-fs-01\FileShare\Wentzville\SeqPublic\WEN-201 LOC\Container Management System\MARS_Import\myRubbishData.log'

 system.db.runPrepUpdate("""
 bulk insert import_Mars
 from ?
 with
 (
 rowterminator=?,
 fieldterminator=?,
 firstrow = 2,
 ERRORFILE = ? 
 )
 """,[getFrom,rowTerm,fieldTerm,errorPath])

Error:

 Traceback (most recent call last):
   File "<event:actionPerformed>", line 6, in <module>
 bulk insert import_Mars
 from ?
 with
 (
 rowterminator=?,
 fieldterminator=?,
 firstrow = 2,
 ERRORFILE = ? 
 )
 , [\\wtz-svr-fs-01\FileShare\Wentzville\SeqPublic\WEN-201 LOC\Container Management System\MARS_Import\TVS Updated CSV_Fixed.csv, \n, ~, \\wtz-svr-fs-01\FileShare\Wentzville\SeqPublic\WEN-201 LOC\Container Management System\MARS_Import\myRubbishData.log], , , false, false)
 	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:357)
 	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:255)
 	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(
 bulk insert import_Mars
 from ?
 with
 (
 rowterminator=?,
 fieldterminator=?,
 firstrow = 2,
 ERRORFILE = ? 
 )
 , [\\wtz-svr-fs-01\FileShare\Wentzville\SeqPublic\WEN-201 LOC\Container Management System\MARS_Import\TVS Updated CSV_Fixed.csv, \n, ~, \\wtz-svr-fs-01\FileShare\Wentzville\SeqPublic\WEN-201 LOC\Container Management System\MARS_Import\myRubbishData.log], , , false, false)

 	at org.python.core.Py.JavaError(Py.java:495)
 	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:357)
 	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:255)
 	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:438)
 	at org.python.core.PyObject.__call__(PyObject.java:404)
 	at org.python.core.PyObject.__call__(PyObject.java:408)
 	at org.python.pycode._pyx123.f$0(<event:actionPerformed>:6)
 	at org.python.pycode._pyx123.call_function(<event:actionPerformed>)
 	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.factorypmi.application.binding.action.ActionAdapter.runActions(ActionAdapter.java:183)
 	at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.invoke(ActionAdapter.java:284)
 	at com.inductiveautomation.factorypmi.application.binding.action.RelayInvocationHandler.invoke(RelayInvocationHandler.java:55)
 	at com.sun.proxy.$Proxy29.actionPerformed(Unknown Source)
 	at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
 	at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
 	at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
 	at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
 	at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
 	at java.awt.Component.processMouseEvent(Unknown Source)
 	at javax.swing.JComponent.processMouseEvent(Unknown Source)
 	at java.awt.Component.processEvent(Unknown Source)
 	at java.awt.Container.processEvent(Unknown Source)
 	at java.awt.Component.dispatchEventImpl(Unknown Source)
 	at java.awt.Container.dispatchEventImpl(Unknown Source)
 	at java.awt.Component.dispatchEvent(Unknown Source)
 	at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
 	at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
 	at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
 	at java.awt.Container.dispatchEventImpl(Unknown Source)
 	at java.awt.Window.dispatchEventImpl(Unknown Source)
 	at java.awt.Component.dispatchEvent(Unknown Source)
 	at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
 	at java.awt.EventQueue.access$500(Unknown Source)
 	at java.awt.EventQueue$3.run(Unknown Source)
 	at java.awt.EventQueue$3.run(Unknown Source)
 	at java.security.AccessController.doPrivileged(Native Method)
 	at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
 	at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
 	at java.awt.EventQueue$4.run(Unknown Source)
 	at java.awt.EventQueue$4.run(Unknown Source)
 	at java.security.AccessController.doPrivileged(Native Method)
 	at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
 	at java.awt.EventQueue.dispatchEvent(Unknown Source)
 	at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
 	at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
 	at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
 	at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
 	at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
 	at java.awt.EventDispatchThread.run(Unknown Source)
 Caused by: java.lang.Exception: Error executing system.db.runPrepUpdate(
 bulk insert import_Mars
 from ?
 with
 (
 rowterminator=?,
 fieldterminator=?,
 firstrow = 2,
 ERRORFILE = ? 
 )
 , [\\wtz-svr-fs-01\FileShare\Wentzville\SeqPublic\WEN-201 LOC\Container Management System\MARS_Import\TVS Updated CSV_Fixed.csv, \n, ~, \\wtz-svr-fs-01\FileShare\Wentzville\SeqPublic\WEN-201 LOC\Container Management System\MARS_Import\myRubbishData.log], , , false, false)
 	... 56 more
 Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: SQL error for "
 bulk insert import_Mars
 from ?
 with
 (
 rowterminator=?,
 fieldterminator=?,
 firstrow = 2,
 ERRORFILE = ? 
 )
 ": Incorrect syntax near '@P0'.
 	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:332)
 	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:306)
 	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:263)
 	at com.inductiveautomation.ignition.client.script.ClientDBUtilities._runPrepStmt(ClientDBUtilities.java:181)
 	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:253)
 	... 54 more
 Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0'.
 	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)
 	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454)
 	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:388)
 	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:338)
 	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)
 	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416)
 	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:185)
 	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:160)
 	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:306)
 	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.servlets.gateway.functions.RunPrepStmt.runUpdateQuery(RunPrepStmt.java:69)
 	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractUpdateQuery.run(AbstractUpdateQuery.java:39)
 	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractDBAction.invoke(AbstractDBAction.java:81)
 	at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:410)
 	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)

 Ignition v7.9.4 (b2017082911)
 Java: Oracle Corporation 1.8.0_102

Sanderd17 · January 9, 2018, 3:34pm

Hi,

First of all, please use backticks (`) to format code, it makes it a lot easier to read.

The error seems to be about the first argument (@P0), so you should probably try with hardcoded arguments first, and then try to replace those by variables. I don’t know to what extend the ?-replacement works with bulk inserts.

PGriffith · January 9, 2018, 3:59pm

This is definitely it. You may not be able to run bulk insert as a prepared statement in this way (up to the JDBC driver, not Ignition); so you may need to use string substitution to insert the filename rather than a parameter substitute. There's some discussion on this topic here:

Also, I cleaned up the code formatting on your post, but @Sanderd17 is definitely correct; wrapping in triple backticks (`) is the preferred way to post code.