Sql If Exist in a Script

Sql If Exist in a Script

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.

Full Error report

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Marcus Vance
Author

Marcus Vance

Marcus Vance is a cybersecurity auditor and technology writer dedicated to educating the public about online safety, data privacy regulations, enterprise security, and emerging cyber threats.