Script won't delete rows in database

Hello, I’m really new to Ignition 8 and programming in general. I’m trying to follow the instructions as per Simple Database Editor - Ignition User Manual 8.0 - Ignition Documentation with some changes as appropriate to my database (column names, etc). So far the function to add a line is working fine, but I can’t seem to make headway on the delete function. When I test the page and select lines and delete them, nothing happens. No error message, no rows get deleted. I tried using the Database Query browser and the Query itself works fine (deletes a row if I specify the Name or ID, or such). I’ve added my customized script here.

Hoping someone can see what I’ve done…

The code looks ok. It relies on the format of the table and the named query to be correct though.
To debug I’d put in a few print statements to see where its failing.
E.g. “print id” statement after it gets the id to make sure it’s getting the right value.

Also, have you tried testing the named query by itself in the Named Query testing tab, or from the ScriptConsole?

1 Like

On top of what @DavidWisely said, I would also add a variable to your system.db.runNamedQuery() function. For an update query, it should return an int representing how many records are affected.

nq = system.db.runNamedQuery("Customers/Customers Deleting", {"Customer_ID":id})
print nq

I also agree with @DavidWisely about testing the named query. I would test it in the Script Console to make sure it functions correctly when called from a script.

Are you sure your logic is right in line 8…

should it not be; Are you Sure?, 1

Unless I’m wrong, the 0 isn’t needed, if he had a 1 then it would give a yes/no/cancel option instead of just yes/no. The no still returns a 0 which would send him to the else statement. The cancel option would just allow a None to be returned.

If I had to guess, I think it probably has to do with the named query.

https://docs.inductiveautomation.com/display/DOC80/system.gui.confirm

When he clicks Yes (Yes I want to delete X rows), it returns a 1,

If he clicks no, it returns a 0,

If he clicks cancel, none is returned

So surely the logic should be Yes (1), delete my rows

That is what he has on line 8. He just didn’t call out the == 1 a the end of the if so anything other than 0 or none will be true, 0 or none will go to the else.

Yep sorry only skimmed what I linked, read and comprehended while you were typing. Agree with you :slight_smile:

re: DavidWisely yes I did test the query itself in both the database console and in Ignition, both will delete a single row.

When I test the script in Tools > Script Console (Is there another tool somewhere else? I am new to all this…) I get an error that I don’t get when running the test page:

Traceback (most recent call last):
File “”, line 2, in
NameError: name ‘event’ is not defined

Is this an error that will silently end the script or something?

The event comes from the component event handler, so this won’t exist in the script console. You can use the script console to test parts of your code that don’t require anything from the event or that references components from the Window, e.g. your function that deletes your rows

@Matrix_Engineering, yes Tools -> Script Console is correct.
As @nminchin said, you can test just parts of your script in the console.

I sugget this to test your named query:

id = 5 # use a number for a Customer_ID that you can delete
rowsAffected = system.db.runNamedQuery(“Customers/Customers Deleting”, {“Customer_ID”:id})
print rowsAffected

Check that it prints 1 if the named query is correct.

Once this is confirmed, put some print statements in your existing script to work out where it’s failing.
Something like this (paraphrasing):

selRows = event.source.parent.getCompent(‘Customer Power Table’ …
print “selRows”, selRows

if len(selRows) > 0:
if system.gui.confirm("Are you sure you want to delete …

  for row in selRows:
  	
  	id = event.source.aprent.getComponent('Customer Power Table' ....
  	print "id", id
  	
  	rowsAffected = system.db.runNamedQuery('Customers/Customers Deleting" ...
  	print "rowsAffected", rowsAffected

  system.db.refresh(event.source.parent.getComponent(....

else:
system.gui.messageBox( …

else:
system.gui.messageBox( …

Then execute the script in the designer.
You can see the print outs of the print statements in the Output Console:
View -> Panels -> Output Console

Or execute it in a client and view the output in the Diagnostic Window.
Display the diagnostic widow by pressing Ctrl+Shift+F7

The print statement returns a 0, so I checked the Query internally in the database (mariaDB) using:
DELETE FROM Customers WHERE Customers.Customer_ID = :id (substituted 50 for :id) and it deletes just row 50, successfully. This leaves me a little confused: the query works fine, but the script calling the query doesn’t?

When I added the print statements to see what’s being done, I got:
array(‘i’, [0])
52 #row being deleted
0 #number of rows deleted

The scripting for those results looks like:
# If a row is selected, ask for confirmation before deleting the rows.
if system.gui.confirm(“Are you sure you want to delete " + str(len(selRows)) + " row(s)?”, “Are You Sure?”, 0):
print selRows
# If the user gave confirmation, we need to loop through all of them.
for row in selRows:

        # For each selected row, we need to get the value of the id column in that row.
        id = event.source.parent.getComponent('Customer Power Table').data.getValueAt(row, "Customer_ID")
        print id 
        # Use the name of the row to delete it from the database table. Print the number of rows deleted.
        nq = system.db.runNamedQuery("Customers/Customers Deleting", {"Customer_ID":id})
        print nq

I’m confused why the script can identify the correct row (52) to delete, but then fails to delete it, using a query that is tested to work.

I really really appreciate everyone’s input on this, I know these must seem like baby steps. I enjoy learning new stuff, even including the frustrations!

Robb
edit* not sure why the formatting is all wrong, hopefully it’s readable.

Make sure that in your named query that you have it set as an Update Query and not just a Query

@dkhayes117 Yes, I confirmed that it is an update query. When I run the Query directly in Ignition it works, though I have to specify the row to be deleted, rather than the scripted :id but it does work.

Here is the Named Query:
DELETE FROM Customers WHERE Customers.Customer_ID = :id

It seems if the query works with a hardcoded id number, then you customer Ids are not what you are expecting. As @DavidWisely mentioned, print the id numbers with the script without running the query. You may also want to verify the id types with print(type(id)) and make sure your column names and parameter names are all correct.

I’m sure my types are correct, both in database (int2) and query (int2). I added the print type command, and it returns type ‘int’.

I’m wondering if the first returned line has some information:
array(‘i’, [0]) <-- is the 0 here the id that is being deleted, or is it just the element number of the array?For example if I select 4 items from the list, it returns as:
array(‘i’,[0, 1, 2, 3] which is the four items.

Edit: Found the reference in python, it is the elements of the array.

I use this exact code in a couple of projects and it works fine. The only possibility for the error has to be the your ids in the for loop imo. I recommend you post the dataset you are testing with if not sensitive.

Customers.sql (1.6 KB)

I think this is the table. Do you want to see the whole ignition project too? I’ve posted screen shots of what I thought was the relevant parts.

No don’t need the whole project, but I would like the actual dataset. When you select the table or whatever component it is, select the data property. When it is open use the copy button then paste that here

#NAMES
“Customer_ID”,“Customer_Name”,“Comments”
#TYPES
“I”,“str”,“str”
#ROWS”,“3”
“52”,“Koppers”,“Koppers Corporate”
“53”,“CPR”,“Canadian Pacific Railway”
“55”,“Testing”,“Testing - To delete”