Dynamic Insert Query not working

Alex_Ford-King · November 12, 2024, 2:46pm

I am working on taking data from an excel document and importing it into our database. I have this Script so far

# Import necessary Java classes
from org.apache.poi.ss.usermodel import WorkbookFactory, CellType
from java import io

# Set the path to your Excel file
excel_file_path = "C:\Users\portmain\Desktop\Book1.xlsx"

# Open the Excel file using Apache POI
workbook = WorkbookFactory.create(io.File(excel_file_path))
sheet = workbook.getSheetAt(0)  # Get the first sheet

# Read the header row (assumes first row is header)
header_row = sheet.getRow(0)
headers = [cell.getStringCellValue() for cell in header_row if cell.getCellType() == CellType.STRING]
print headers
# Prepare to read and insert data into the database
data = []
for row in range(1, sheet.getPhysicalNumberOfRows()):  # Skip header row
    row_data = []
    current_row = sheet.getRow(row)
    if current_row is None:  # Skip empty rows
        continue
    for cell in current_row:
        if cell.getCellType() == CellType.STRING:
            row_data.append(cell.getStringCellValue())
        elif cell.getCellType() == CellType.NUMERIC:
            row_data.append(cell.getNumericCellValue())
        elif cell.getCellType() == CellType.DATETIME:
            row_data.append(cell.getDatetimeCellValue())
        elif cell.getCellType() == CellType.FLOAT:
            row_data.append(cell.getFloatCellValue())    
        elif cell.getCellType() == CellType.BOOLEAN:
            row_data.append(cell.getBooleanCellValue())
        else:
            row_data.append(None)  # Handle empty or unknown types
    if row_data:  # Avoid adding empty rows
        data.append(row_data)
print(row_data)
# Close the workbook after reading
workbook.close()

# Database connection details
db_connection = "POC_Ignition_Scada"  # Replace with your connection name
table_name = "EvisData"  # Replace with your table name

# Prepare the SQL statement for inserting data
for row in data:
    # Construct the SQL INSERT statement
    sql = "INSERT INTO {} ({}) VALUES ({});".format(
        table_name,
        ", ".join(headers),  # Header names as columns
        ", ".join(["?"] * len(headers))  # Placeholder for values
    )
print("Constructed SQL Query:", sql)

system.db.runPrepUpdate(sql, row, db_connection)

print("Data imported into database successfully!")

the script in successfully getting data from the excel document but the query is not working. My insert query looks like this

Insert Into 

	EvisData (id, Arrival, Average, Batch, Birds, Breed, Bruised, Condemns, Farmer, Legs, Rework, Runts, Skin, Start, Trailer, Wings, Yellow)


Values

	(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

When I try to run the query i get an error saying the value for parameter 1 is not set.

Any help or suggestions would be greatly appreciated, Thanks!

dkhayes117 · November 12, 2024, 2:56pm

~~Try changing this line to~~

~~system.db.runPrepUpdate(sql, row, database=db_connection)~~
EDIT: scratch that, I thought database was a keyword arg for some reason.

Kevin.Herron · November 12, 2024, 2:57pm

row isn't a reference to anything that I can see.

Transistor · November 12, 2024, 3:06pm

You also don't use row_data after going to all the trouble of generating it.

I suspect that you mean to run one query for each row. That means you need to indent.

Alex_Ford-King:

for row in data:
    # Construct the SQL INSERT statement
    sql = "INSERT INTO {} ({}) VALUES ({});".format(
        table_name,
        ", ".join(headers),  # Header names as columns
        ", ".join(["?"] * len(headers))  # Placeholder for values
    )
    print("Constructed SQL Query:", sql)

    system.db.runPrepUpdate(sql, row, db_connection)  # <---- indent!

You can insert all (with some limitation, I imagine) the rows in one go. See "Insert Multiple Rows" at W3Schools:

Alex_Ford-King · November 12, 2024, 3:14pm

I changed it to row_data and got the same results.

Alex_Ford-King · November 12, 2024, 3:15pm

I tried to us an indent but it gave a DEDENT error. Also I'm fine with adding it 1 row at a time as the excel document will be added to daily so I will eventually set it up to be ran at the end of every day

MMaynard · November 12, 2024, 3:27pm

The runPrepUpdate line has to be indented to be inside the for loop.
Can you post the results of your print statements?
And what version of SQL are you running? MySQL? MSSQL?PostGres?

Alex_Ford-King · November 12, 2024, 3:36pm

It is giving me an error when i try to indent the runPrepUpdate line. Im using MySQL. My error line looks like this

[u'id', u'Arrival', u'Batch ', u'Birds', u'Breed', u'Bruised', u'Condemns', u'Farmer', u'Legs', u'Rework', u'Runts', u'Skin', u'Start', u'Trailer ', u'Wing ', u'Yellow']
[3.0, 5.0, 5.0, 5.0, 5.0, 5.0, 5.0, 5.0, 4.0, 4.0, 4.0, 4.0, 4.0, 4.0, 4.0, 4.0]
Java Traceback:
Traceback (most recent call last):
  File "<input>", line 55, in <module>
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392)

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

	at jdk.internal.reflect.GeneratedMethodAccessor156.invoke(Unknown Source)

	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

	at java.base/java.lang.reflect.Method.invoke(Unknown Source)

java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO EvisData (id, Arrival, Batch , Birds, Breed, Bruised, Condemns, Farmer, Legs, Rework, Runts, Skin, Start, Trailer , Wing , Yellow) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);, [3.0, 5.0, 5.0, 5.0, 5.0, 5.0, 5.0, 5.0, 4.0, 4.0, 4.0, 4.0, 4.0, 4.0, 4.0, 4.0], POC_Ignition_Scada, , false, false)


	at org.python.core.Py.JavaError(Py.java:545)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392)

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

	at jdk.internal.reflect.GeneratedMethodAccessor156.invoke(Unknown Source)

	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

	at java.base/java.lang.reflect.Method.invoke(Unknown Source)

	at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:190)

	at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction.__call__(ScriptManager.java:553)

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

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

	at org.python.pycode._pyx266.f$0(<input>:57)

	at org.python.pycode._pyx266.call_function(<input>)

	at org.python.core.PyTableCode.call(PyTableCode.java:173)

	at org.python.core.PyCode.call(PyCode.java:18)

	at org.python.core.Py.runCode(Py.java:1703)

	at org.python.core.Py.exec(Py.java:1747)

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

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

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

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

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

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

	at java.desktop/javax.swing.SwingWorker.run(Unknown Source)

	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

	at java.base/java.lang.Thread.run(Unknown Source)

Caused by: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO EvisData (id, Arrival, Batch , Birds, Breed, Bruised, Condemns, Farmer, Legs, Rework, Runts, Skin, Start, Trailer , Wing , Yellow) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);, [3.0, 5.0, 5.0, 5.0, 5.0, 5.0, 5.0, 5.0, 4.0, 4.0, 4.0, 4.0, 4.0, 4.0, 4.0, 4.0], POC_Ignition_Scada, , false, false)

	... 25 more

Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Connection Error: Datasource "POC_Ignition_Scada" does not exist in this Gateway.

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

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

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

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

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

	... 23 more

Caused by: java.sql.SQLException: Datasource "POC_Ignition_Scada" does not exist in this Gateway.

	at com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl.getConnection(DatasourceManagerImpl.java:148)

	at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities.getConnection(GatewayDBUtilities.java:101)

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

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

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

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

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

	at org.eclipse.jetty.servlet.ServletHolder$NotAsync.service(ServletHolder.java:1410)

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

	at org.eclipse.jetty.servlet.ServletHandler$ChainEnd.doFilter(ServletHandler.java:1665)

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

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

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

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

	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:223)

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

	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:221)

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

	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:176)

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

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

	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:174)

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

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

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

	at com.inductiveautomation.catapult.handlers.RemoteHostNameLookupHandler.handle(RemoteHostNameLookupHandler.java:121)

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

	at org.eclipse.jetty.rewrite.handler.RewriteHandler.handle(RewriteHandler.java:301)

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

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

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

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

	at org.eclipse.jetty.server.HttpChannel$RequestDispatchable.dispatch(HttpChannel.java:1598)

	at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:753)

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

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

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

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

	at org.eclipse.jetty.io.SelectableChannelEndPoint$1.run(SelectableChannelEndPoint.java:53)

	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.runTask(AdaptiveExecutionStrategy.java:421)

	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.consumeTask(AdaptiveExecutionStrategy.java:390)

	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.tryProduce(AdaptiveExecutionStrategy.java:277)

	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.run(AdaptiveExecutionStrategy.java:199)

	at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:411)

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

	at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.doRunJob(QueuedThreadPool.java:1194)

	at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1149)

	at java.lang.Thread.run(null)

Traceback (most recent call last):
  File "<input>", line 55, in <module>
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392)

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

	at jdk.internal.reflect.GeneratedMethodAccessor156.invoke(Unknown Source)

	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

	at java.base/java.lang.reflect.Method.invoke(Unknown Source)

java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO EvisData (id, Arrival, Batch , Birds, Breed, Bruised, Condemns, Farmer, Legs, Rework, Runts, Skin, Start, Trailer , Wing , Yellow) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);, [3.0, 5.0, 5.0, 5.0, 5.0, 5.0, 5.0, 5.0, 4.0, 4.0, 4.0, 4.0, 4.0, 4.0, 4.0, 4.0], POC_Ignition_Scada, , false, false)

EDIT: I was able to indent the runPrepUpdate line but still the same error. I believe i have something wrong with my query

MMaynard · November 12, 2024, 3:49pm

The error is right there.