Fetch out of squence error when insert query is run

Ignition
1

I am receiving the error below when I run the query shown below. I have disabled polling on the table component which the insert query is updating. Any help would be greatly appreciated.

Thanks

Error:

Traceback (most recent call last):
  File "<event:actionPerformed>", line 4, in <module>
  File "<custom-function insertDB>", line 111, in insertDB
  File "<custom-function insertDB>", line 111, in insertDB
  File "<custom-function insertDB>", line 111, in insertDB
  File "<custom-function insertDB>", line 111, in insertDB


com.inductiveautomation.ignition.client.gateway_interface.GatewayException: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: java.sql.SQLException: ORA-01002: fetch out of sequence

	caused by GatewayException: java.sql.SQLException: ORA-01002: fetch out of sequence

	caused by Exception: java.sql.SQLException: ORA-01002: fetch out of sequence


Ignition v8.1.35 (b2023120517)
Java: Azul Systems, Inc. 17.0.8

Query being ran:

INSERT INTO RMEDGER_ADDER
	(BASE_ALLOY,
	INGOT_SUPPLIER,
	INGOT_THICKNESS_COLD,
	INGOT_WIDTH_COLD,
	LAST_UPDATE,  
	LAST_UPDATE_USERNAME, 
	THRESHOLD,
	TARGET_CURRENT,
	EDGER_ADDER,
	SPECIAL_CODE,
	OFFSET_01,
	OFFSET_02,
	OFFSET_03,
	OFFSET_04,
	OFFSET_05,
	OFFSET_06,
	OFFSET_07,
	OFFSET_08,
	OFFSET_09,
	OFFSET_10,
	OFFSET_11,
	OFFSET_12,
	OFFSET_13,
	OFFSET_14,
	OFFSET_15,
	OFFSET_16,
	OFFSET_17,
	OFFSET_18,
	OFFSET_19,
	OFFSET_20,
	OFFSET_21,
	OFFSET_22,
	OFFSET_23,
	OFFSET_24,
	OFFSET_25,
	OFFSET_26,
	OFFSET_27,
	OFFSET_28,
	OFFSET_29,
	OFFSET_30,
	OFFSET_31)
VALUES
	(:Alloy,
	:Supplier,
	:Ingot_Thickness,
	:Ingot_Width,
	TO_DATE(:Last_Update_Time, 'YYYY-MM-DD HH24:MI:SS'),
	:Last_Update_User,
	:Threshold,
	:Target_Current,
	:Edger_Adder,
	:Special_Code,
	:Pass1,
	:Pass2,
	:Pass3,
	:Pass4,
	:Pass5,
	:Pass6,
	:Pass7,
	:Pass8,
	:Pass9,
	:Pass10,
	:Pass11,
	:Pass12,
	:Pass13,
	:Pass14,
	:Pass15,
	:Pass16,
	:Pass17,
	:Pass18,
	:Pass19,
	:Pass20,
	:Pass21,
	:Pass22,
	:Pass23,
	:Pass24,
	:Pass25,
	:Pass26,
	:Pass27,
	:Pass28,
	:Pass29,
	:Pass30,
	:Pass31)
2

I did verify that the query does work in Oracle Developer with no fetch out of sequence error.

3

I don't see anything obviously wrong with the query, but I haven't used Oracle in ages. The error itself is purely coming from Oracle's JDBC driver, so you will probably need to research it through them.

4

This topic has some helpful information:

I suspect you need to run this in an explicit transaction.

5

I tried running in an explicit transaction, but still got an error. Code below is what is ran once confirmation to insert is given.

from datetime import datetime
def currentDateTime():
	return str(datetime.now()).split('.')[0]
try:
	db = "HMORA2"
	
	#bind values for named query to current values in data entry boxes
	temp1 = self.getComponent('Pass1_15')
	temp2 = self.getComponent('Pass16_31')
	baseAlloy = self.getComponent('Base Alloy')
	specialCode = self.getComponent('Special Code')
	ingotSupplier = self.getComponent('Ingot Supplier')
	ingotWidth = self.getComponent('Ingot Width')
	ingotThickness = self.getComponent('Ingot Thickness')
	edgerAdder = self.getComponent('Edger Adder')
	targetCurrent = self.getComponent('Target Current')
	threshold = self.getComponent('Threshold')
	dtLastUpdate = self.getComponent('Date Last Update')
	lastUpdateUser = self.getComponent('Last Update User')
	table = self.getComponent('Power Table')

	#get loaded templates as list to access template values
	#capture current username of user during update along with the current time
	temp1List = temp1.getLoadedTemplates()
	temp2List = temp2.getLoadedTemplates()
	lastUpdateUser = system.security.getUsername()
	lastUpdateTime = currentDateTime()
	
	Pass1 = temp1List[0].getComponent('Numeric Text Field').floatValue
	Pass2 = temp1List[1].getComponent('Numeric Text Field').floatValue
	Pass3 = temp1List[2].getComponent('Numeric Text Field').floatValue
	Pass4 = temp1List[3].getComponent('Numeric Text Field').floatValue
	Pass5 = temp1List[4].getComponent('Numeric Text Field').floatValue
	Pass6 = temp1List[5].getComponent('Numeric Text Field').floatValue
	Pass7 = temp1List[6].getComponent('Numeric Text Field').floatValue
	Pass8 = temp1List[7].getComponent('Numeric Text Field').floatValue
	Pass9 = temp1List[8].getComponent('Numeric Text Field').floatValue
	Pass10 = temp1List[9].getComponent('Numeric Text Field').floatValue
	Pass11 = temp1List[10].getComponent('Numeric Text Field').floatValue
	Pass12 = temp1List[11].getComponent('Numeric Text Field').floatValue
	Pass13 = temp1List[12].getComponent('Numeric Text Field').floatValue
	Pass14 = temp1List[13].getComponent('Numeric Text Field').floatValue
	Pass15 = temp1List[14].getComponent('Numeric Text Field').floatValue
	Pass16 = temp2List[0].getComponent('Numeric Text Field').floatValue
	Pass17 = temp2List[1].getComponent('Numeric Text Field').floatValue
	Pass18 = temp2List[2].getComponent('Numeric Text Field').floatValue
	Pass19 = temp2List[3].getComponent('Numeric Text Field').floatValue
	Pass20 = temp2List[4].getComponent('Numeric Text Field').floatValue
	Pass21 = temp2List[5].getComponent('Numeric Text Field').floatValue
	Pass22 = temp2List[6].getComponent('Numeric Text Field').floatValue
	Pass23 = temp2List[7].getComponent('Numeric Text Field').floatValue
	Pass24 = temp2List[8].getComponent('Numeric Text Field').floatValue
	Pass25 = temp2List[9].getComponent('Numeric Text Field').floatValue
	Pass26 = temp2List[10].getComponent('Numeric Text Field').floatValue
	Pass27 = temp2List[11].getComponent('Numeric Text Field').floatValue
	Pass28 = temp2List[12].getComponent('Numeric Text Field').floatValue
	Pass29 = temp2List[13].getComponent('Numeric Text Field').floatValue
	Pass30 = temp2List[14].getComponent('Numeric Text Field').floatValue
	Pass31 = temp2List[15].getComponent('Numeric Text Field').floatValue
	try:
		#update query params
		path = "EdgerAdder/insertEdgerAdder"
		params = {
			"Alloy": str(baseAlloy.selectedStringValue),
			"Supplier": str(ingotSupplier.selectedStringValue),
			"Special_Code": str(specialCode.selectedStringValue),
			"Last_Update_Time": lastUpdateTime,
			"Last_Update_User": str(lastUpdateUser),
			"Threshold": float(threshold.text),
			"Target_Current": float(targetCurrent.text),
			"Edger_Adder": float(edgerAdder.text),
			"Ingot_Thickness": float(ingotThickness.text),
			"Ingot_Width": float(ingotWidth.text),
			"Pass1": Pass1,
			"Pass2": Pass2,
			"Pass3": Pass3,
			"Pass4": Pass4,
			"Pass5": Pass5,
			"Pass6": Pass6,
			"Pass7": Pass7,
			"Pass8": Pass8,
			"Pass9": Pass9,
			"Pass10": Pass10,
			"Pass11": Pass11,
			"Pass12": Pass12,
			"Pass13": Pass13,
			"Pass14": Pass14,
			"Pass15": Pass15,
			"Pass16": Pass16,
			"Pass17": Pass17,
			"Pass18": Pass18,
			"Pass19": Pass19,
			"Pass20": Pass20,
			"Pass21": Pass21,
			"Pass22": Pass22,
			"Pass23": Pass23,
			"Pass24": Pass24,
			"Pass25": Pass25,
			"Pass26": Pass26,
			"Pass27": Pass27,
			"Pass28": Pass28,
			"Pass29": Pass29,
			"Pass30": Pass30,
			"Pass31": Pass31
			}
		try:
			tx = system.db.beginNamedQueryTransaction(db)
			system.db.runNamedQuery(path, params)
			system.db.commitTransaction(tx)
			system.db.closeTransaction(tx)
		except Exception as e:
			self.error("Insert named query failed! {}".format(e), "NAMED QUERY ERROR")
	except Exception as e:
		self.error("Insert named query failed! {}".format(e), "NAMED QUERY ERROR")
#		system.db.refresh(table, "data")
	self.success("Insert Successful!", "INSERT CONFIRMATION")
except Exception as e:
	self.error("Insert Unsuccessful! {}".format(e), "INSERT ERROR")
6

The thing that is so strange to me is that the data is inserted but the error persists.

7

You aren't providing the transaction ID to the named query.

8

SQL throws java errors, so you'll need to catch those instead:

from java.lang import Throwable

try:
    ...
except Throwable as e:
    ...
9

That's my bad.

Updated code below and error persists.

            try:
				tx = system.db.beginNamedQueryTransaction(db)
				system.db.runNamedQuery(path, params, tx)
				system.db.commitTransaction(tx)
				system.db.closeTransaction(tx)
			except Exception as e:
				self.error("Insert named query failed! {}".format(e), "NAMED QUERY ERROR")
10

I finally resolved this issue.

I had my query type set to "Query" rather than "Update Query"

image