# Dynamic Insert Query not working

**URL:** <https://forum.inductiveautomation.com/t/dynamic-insert-query-not-working/98514>\
**Category:** Ignition\
**Tags:** perspective, scripting\
**Created:** [November 12, 2024, 2:46pm UTC](https://forum.inductiveautomation.com/t/dynamic-insert-query-not-working/98514 "2024-11-12T14:46:17Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![Alex\_Ford-King](https://avatars.discourse-cdn.com/v4/letter/a/ebca7d/32.png) [@Alex\_Ford-King](https://forum.inductiveautomation.com/u/Alex_Ford-King)\
**Post date:** [November 12, 2024, 2:46pm UTC](https://forum.inductiveautomation.com/t/dynamic-insert-query-not-working/98514/1 "2024-11-12T14:46:17Z")

</div>

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

```python
# 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

```python
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!

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [November 12, 2024, 2:56pm UTC](https://forum.inductiveautomation.com/t/dynamic-insert-query-not-working/98514/2 "2024-11-12T14:56:55Z")

</div>

> [@Alex\_Ford-King](#):
>
> `system.db.runPrepUpdate(sql, row, db_connection)`

~~Try changing this line to~~

~~

```python
system.db.runPrepUpdate(sql, row, database=db_connection)

```

~~EDIT: scratch that, I thought database was a keyword arg for some reason.

---

<div class="post-metadata">

**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\
**Post date:** [November 12, 2024, 2:57pm UTC](https://forum.inductiveautomation.com/t/dynamic-insert-query-not-working/98514/3 "2024-11-12T14:57:39Z")

</div>

> [@Alex\_Ford-King](#):
>
> `system.db.runPrepUpdate(sql, row, db_connection)`

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

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [November 12, 2024, 3:06pm UTC](https://forum.inductiveautomation.com/t/dynamic-insert-query-not-working/98514/4 "2024-11-12T15:06:50Z")

</div>

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](#):
>
> ```python
> 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:

> **[W3Schools.com](https://www.w3schools.com/sql/sql_insert.asp)**
>
> W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

---

<div class="post-metadata">

**Author:** ![Alex\_Ford-King](https://avatars.discourse-cdn.com/v4/letter/a/ebca7d/32.png) [@Alex\_Ford-King](https://forum.inductiveautomation.com/u/Alex_Ford-King)\
**Post date:** [November 12, 2024, 3:14pm UTC](https://forum.inductiveautomation.com/t/dynamic-insert-query-not-working/98514/5 "2024-11-12T15:14:25Z")

</div>

I changed it to row\_data and got the same results.

---

<div class="post-metadata">

**Author:** ![Alex\_Ford-King](https://avatars.discourse-cdn.com/v4/letter/a/ebca7d/32.png) [@Alex\_Ford-King](https://forum.inductiveautomation.com/u/Alex_Ford-King)\
**Post date:** [November 12, 2024, 3:15pm UTC](https://forum.inductiveautomation.com/t/dynamic-insert-query-not-working/98514/6 "2024-11-12T15:15:30Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\
**Post date:** [November 12, 2024, 3:27pm UTC](https://forum.inductiveautomation.com/t/dynamic-insert-query-not-working/98514/7 "2024-11-12T15:27:21Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Alex\_Ford-King](https://avatars.discourse-cdn.com/v4/letter/a/ebca7d/32.png) [@Alex\_Ford-King](https://forum.inductiveautomation.com/u/Alex_Ford-King)\
**Post date:** [November 12, 2024, 3:36pm UTC](https://forum.inductiveautomation.com/t/dynamic-insert-query-not-working/98514/8 "2024-11-12T15:36:46Z")

</div>

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

```python
[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

---

<div class="post-metadata">

**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\
**Post date:** [November 12, 2024, 3:49pm UTC](https://forum.inductiveautomation.com/t/dynamic-insert-query-not-working/98514/9 "2024-11-12T15:49:03Z")

</div>

> [@Alex\_Ford-King](#):
>
> `Caused by: java.sql.SQLException: Datasource "POC_Ignition_Scada" does not exist in this Gateway.`

The error is right there.