Writing to SQL table with script

Ignition
1

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:

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:

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.

2

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.

	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))
3

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.

1 Like
4

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.

5

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.

1 Like
6

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()

7

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.

1 Like