Issue running a SQL Delete

Ignition
1

I have a Power Table in my window. I am populating it using scripting and some variables. What I want to do next is have the ability to select a row in the table click a button and delete the row from the SQL database. I keep getting an error when I try this.

     number = system.tag.read("[Client]Delete_Number").value

     system.tag.write("[Client]Delete_Number",number)
     query = 'DELETE FROM SF_Paint_Schedule WHERE UID = ?'
     args = (number)
     system.db.runPrepUpdate(query,args) ```

This is the error I get. I notice it is doing some crazy stuff withe the variable. it should be 12.

Traceback (most recent call last):

  File "<event:actionPerformed>", line 8, in <module>



java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(DELETE FROM SF_Paint_Schedule WHERE UID = "?", [0, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0], , , false, false)


	caused by Exception: Error executing system.db.runPrepUpdate(DELETE FROM SF_Paint_Schedule WHERE UID = "?", [0, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0], , , false, false)
	caused by GatewayException: SQL error for "DELETE FROM SF_Paint_Schedule WHERE UID = "?"": The index 1 is out of range.
	caused by SQLServerException: The index 1 is out of range.

Ignition v7.9.9 (b2018081621)
Java: Oracle Corporation 1.8.0_181

Thanks in advance for the help.
2

The args parameter should be a list according to the documentation.

Try the following and see if it works:

query = 'DELETE FROM SF_Paint_Schedule WHERE UID = ?'
args = [number]
system.db.runPrepUpdate(query,args)
2 Likes
3

Perfect that done the trick.Thanks

4

It wasn’t a tuple. Parentheses around a single expression will not be a tuple. There must be at least one comma. A trailing comma inside the parentheses would make it a tuple, and it would have worked.

5

My mistake, I have corrected the post so that others will not be misled.

Thanks for the correction.

1 Like
6

So much for PEP20 "Readability counts."

7

Indeed. Parentheses are understood pretty much everywhere as expression grouping. Not so much for tuples. It is the comma that makes a tuple, and will do so without the parentheses if otherwise unambiguous. The parentheses are often needed to disambiguate, but leads to magical thinking. Try this yourself:

>>> my_var = 1, 2, 3
>>> my_var
(1, 2, 3)
>>>
8

The real confusing part for me, and why initially I got this wrong in this thread is because, you need the parenthesis in certain situations. The main one being to create an empty tuple, which uses only the parenthesis.

Interestingly enough if you declare an empty tuple and then try to print the type out like this:

my_var = ()
print type(my_var)

It actually throws an error stating that my_var is not declared.

They kind of missed the mark on this one imo.

My new found understanding that tuples are created using commas will help a lot going forward so I suppose I’ve gained that much out of this discussion.