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.
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?
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.
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.
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.
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.
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.
@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.
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
