I'f this is in the wrong area, kindly direct me to the right area I'm working with Ignition BY inductiveautomation, and trying to write a script that will update or insert based on the IF EXIST condition. I have a query that will insert a new record into my DB listed below, but now I want to be able to update the "setupsheet" if it already exist.
Code: queryValue1 = event.source.parent.getComponent('MoldName').selectedStringValue queryValue2 = event.source.parent.getComponent('MachineName').selectedStringValue queryValue3 = event.source.parent.getComponent('Text Area').text if system.gui.confirm("Steve! Are you sure you want to Save changes?", "Save Changes"): system.db.runPrepUpdate("INSERT INTO setup_sheetlist (MoldName,machineName,Setupsheet) VALUES ('%s','%s','%s')" % (queryValue1, queryValue2 , queryValue3), getKey=1) This is my query, its working in my DB but I'm a lost on how to implement it into the RunPrepUpdate,
Code: IF EXISTS( SELECT * FROM setup_sheetlist WHERE moldname='100SCX-3' AND Machinename='MM11') UPDATE setup_sheetlist SET setupsheet ='updated value 3' WHERE moldname='100SCX-3' AND Machinename='MM11' ELSE INSERT INTO setup_sheetlist(MoldName,machineName,Setupsheet) VALUES('100SCX-3','MM11','TRIAL 2') This is what I have so far, this gives me an error message
queryValue1 = event.source.parent.getComponent('MoldName').selectedStringValue queryValue2 = event.source.parent.getComponent('MachineName').selectedStringValue queryValue3 = event.source.parent.getComponent('Text Area').text if system.gui.confirm("Steve! Are you sure you want to Save changes?", "Save Changes"): system.db.runPrepUpdate("IF EXISTS( SELECT * FROM setup_sheetlist WHERE moldname='?' AND Machinename='?')UPDATE setup_sheetlist SET setupsheet ='?' WHERE moldname='?' AND Machinename='?'" ,[queryValue1,queryValue2,queryValue3,queryValue1,queryValue2] ) ELSE INSERT INTO setup_sheetlist (MoldName,machineName,Setupsheet) VALUES ('%s','%s','%s')" % (queryValue1, queryValue2 , queryValue3)) Error
Error: java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(IF EXISTS( SELECT * FROM setup_sheetlist WHERE moldname='?' AND Machinename='?')UPDATE setup_sheetlist SET setupsheet ='updated value 3' WHERE moldname='?' AND Machinename='?', [100SCX-3, MM09, 100SCX-3, MM09], , , false, false) caused by Exception: Error executing system.db.runPrepUpdate(IF EXISTS( SELECT * FROM setup_sheetlist WHERE moldname='?' AND Machinename='?')UPDATE setup_sheetlist SET setupsheet ='updated value 3' WHERE moldname='?' AND Machinename='?', [100SCX-3, MM09, 100SCX-3, MM09], , , false, false) caused by GatewayException: SQL error for "IF EXISTS( SELECT * FROM setup_sheetlist WHERE moldname='?' AND Machinename='?')UPDATE setup_sheetlist SET setupsheet ='updated value 3' WHERE moldname='?' AND Machinename='?'": The index 1 is out of range. caused by SQLServerException: The index 1 is out of range. 1 Answer
I should have passed the variable all at once at the end of the query instead of breaking it to parts.
system.db.runPrepUpdate("IF EXISTS( SELECT * FROM setup_sheetlist WHERE moldname=? AND Machinename=?)UPDATE setup_sheetlist SET setupsheet =? WHERE moldname=? AND Machinename=? ELSE INSERT INTO setup_sheetlist (MoldName,machineName,Setupsheet) VALUES (?,?,?)" , (queryValue1,queryValue2,queryValue3,queryValue1,queryValue2,queryValue1, queryValue2 , queryValue3 )) 
