# Writing to SQL table with script

**URL:** <https://forum.inductiveautomation.com/t/writing-to-sql-table-with-script/88384>\
**Category:** Ignition\
**Tags:** perspective\
**Created:** [April 4, 2024, 10:21pm UTC](https://forum.inductiveautomation.com/t/writing-to-sql-table-with-script/88384 "2024-04-04T22:21:13Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Pico\_Sankari](https://avatars.discourse-cdn.com/v4/letter/p/aeb1de/32.png) [@Pico\_Sankari](https://forum.inductiveautomation.com/u/Pico_Sankari)\
**Post date:** [April 4, 2024, 10:21pm UTC](https://forum.inductiveautomation.com/t/writing-to-sql-table-with-script/88384/1 "2024-04-04T22:21:13Z")

</div>

I'm trying to write to a SQL table using a script. I would like to iterate over data and write many lines to this table. I am currently running my script, getting an error relating to the first entry, and only having the first entry write to the table.  
This is the code I'm using:

```auto
query = "INSERT INTO NC_Causes (MeltNum, PartNum, StartLot, EndLot, NCCause, Furnace, Date) VALUES (?,?,?,?,?,?,?)"
NCCauses=[melt, PN, SL,EL, NCCause, 'M7', meltTime]
system.db.runPrepQuery(query, NCCauses)	

```

This is the end of the long error message I get:

```auto
java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepQuery(INSERT INTO NC_Causes (MeltNum, PartNum, StartLot, EndLot, NCCause, Furnace, Date) VALUES (?,?,?,?,?,?,?), , [6, 0, 0, 0, PD, , M7, Mon Feb 12 23:33:31 PST 2024], )

```

The entry referenced in the error message is actually getting written to the table, the script just stops after this. Is there any way to skip over the error and just keep the script iterating? This code is currently being run on the script console, but ideally would be run on a timer in the gateway.

---

<div class="post-metadata">

**Author:** ![ryan.white](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ryan.white/32/73193_2.png) [@ryan.white](https://forum.inductiveautomation.com/u/ryan.white)\
**Post date:** [April 4, 2024, 10:25pm UTC](https://forum.inductiveautomation.com/t/writing-to-sql-table-with-script/88384/2 "2024-04-04T22:25:20Z")

</div>

Wrap your call to `runPrepQuery` in a try/except statement. Note that in the example below, `PythonAsJavaException` is a custom class from pturmel's [later.py](https://www.automation-pros.com/ignition/later.py).

```auto
	from java.lang import Throwable
	query = "Some query text here"
	NCCauses = ["list", "of", "data", "to", "write"]

	try:
		system.db.runPrepQuery(query, NCCauses)

	except Throwable as t:
		logger.warn("Java error recording electrode shipping data", t)

	except Exception as e:
		logger.warn("Jython error recording electrode shipping data", shared.later.PythonAsJavaException(e))

```

---

<div class="post-metadata">

**Author:** ![ryan.white](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ryan.white/32/73193_2.png) [@ryan.white](https://forum.inductiveautomation.com/u/ryan.white)\
**Post date:** [April 4, 2024, 10:34pm UTC](https://forum.inductiveautomation.com/t/writing-to-sql-table-with-script/88384/3 "2024-04-04T22:34:46Z")

</div>

Looking at that error, you are somehow passing 8 parameter values to a prep query that is expecting only 7. The 6th item looks like a blank string. I would inspect however you are generating your parameter values.

---

<div class="post-metadata">

**Author:** ![Pico\_Sankari](https://avatars.discourse-cdn.com/v4/letter/p/aeb1de/32.png) [@Pico\_Sankari](https://forum.inductiveautomation.com/u/Pico_Sankari)\
**Post date:** [April 5, 2024, 3:25pm UTC](https://forum.inductiveautomation.com/t/writing-to-sql-table-with-script/88384/4 "2024-04-05T15:25:25Z")

</div>

This is not actually the case, the 5th item is the string ' PD, '. Using try/except gets around this, but it's a pretty crude way to do it.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [April 5, 2024, 3:32pm UTC](https://forum.inductiveautomation.com/t/writing-to-sql-table-with-script/88384/5 "2024-04-05T15:32:14Z")

</div>

Show us the complete error message. I suspect it is reporting "no result set" because you are using `system.db.runPrepQuery` when you should be using `system.db.runPrepUpdate`.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [April 5, 2024, 3:36pm UTC](https://forum.inductiveautomation.com/t/writing-to-sql-table-with-script/88384/6 "2024-04-05T15:36:28Z")

</div>

~~The value `PD, ` is the problem. You must escape that comma or omit it, because it is bein interpreted by the driver as a parameter delimiter which is resulting in the error you are seeing.~~

 

~~JDBC believes you are submitting 8 parameters while there are only seven replacements.~~

Nevermind.

That is very confusing error message due to the `','` in the parameter value.

@pturmel is definitely correct in that you should be using `system.db.runPrepUpdate()`

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [April 5, 2024, 3:41pm UTC](https://forum.inductiveautomation.com/t/writing-to-sql-table-with-script/88384/7 "2024-04-05T15:41:56Z")

</div>

> [@lrose](#):
>
> The value `PD, ` is the problem.

It isn't, or the SQL wouldn't have been run. We know it ran because the OP says the first row shows up in the table. The error breaks the loop.