Required suggestions on detecting number of affected rows by update name query

Ignition
,
1

The manual states that system.db.runNamedQuery() returns “an integer representing the number of rows affected when set to Update Query.”
My question is:
If an update/insert fails due to a database error (for example, a duplicate key constraint), does the function return 0, or does it always throw an exception?
From my testing, it seems to throw an error instead of returning 0.
I'm deciding how to structure my script and considering two approaches

Option A (Check for return value 0)

Params = {"database":"DB_NAME","param1":"value1","param2":"value2"}
Insert_result = system.db.runNamedQuery("folderName/myNamedQuery",params)
If Insert_result == 0:
	Alert.popup("Failure Message")

Option B (Catch the exception)

Params = {"database":"DB_NAME","param1":"value1","param2":"value2"}
Try:
	Insert_result = system.db.runNamedQuery("folderName/myNamedQuery",params)
exception:
	Alert.popup("Failure Message")

I'm trying to figure out in what scenarios function would actually return 0 for an update/insert query.
Am I missing something?
Any suggestions or improvements are welcome

2

Update a non-existent row, returns 0.

3

Valid.
What if its an insert query? Any possiblity to return 0.

4

If you use the RETURNING keyword in the query, you can return the entire inserted row or anything else you feel like writing.
The art of programming is to capture the expected and except the rest, if you try and capture the exception, you will always miss some crazy edge case.

1 Like
5

It returns the id of the row, unless you have a mutli column primary key or a uuid I think Ignition has that and you need to use one of Phil’s modules. But if you’re primary key is just a autoincrement integer, you get that back from INSERT queries.

Either the query runs and you will get back your updated rows/new ID, or it will throw an error.

You need to do the try/except to catch java/jython errors separately btw.

import java.lang.Throwable

try:
    # stuff
except Exception, e:
    # jython error, syntax, calling non existant libraries, jython exceptions
except java.lang.Throwable, e:
    # java - this is where db errors will propagate to, trying to insert
    # a null into a non-nullable column, unique constraint violation etc
finally:
    # close a transaction if you are using one
2 Likes