It is currently Sat Jul 26, 2014 11:13 am




Post new topic This topic is locked, you cannot edit posts or make further replies.  [ 13 posts ] 
 Delete records from a table. 
Author Message
Sergeant
Sergeant

Joined: Wed Aug 18, 2010 7:29 am
Posts: 47
Location: Little Rock, AR
Post Delete records from a table.
I'm trying to delete records from a table that the user selects. I'm able to delete the records from MySQL, but not Ignition. I've looked at the Editable Table window from the web site, but still can't make it work. I've attached snapshots of the database table, the popup window that allows the deletion and the script I'm trying to use with the delete button.

Thanks!


Attachments:
Delete Button Script.jpg
Delete Button Script.jpg [ 183.14 KiB | Viewed 2084 times ]
Popup window.jpg
Popup window.jpg [ 37.35 KiB | Viewed 2082 times ]
mysql data table.jpg
mysql data table.jpg [ 225.23 KiB | Viewed 2082 times ]
Fri Jun 22, 2012 6:02 am
Profile
Moderator
Moderator

Joined: Fri Mar 16, 2012 1:00 pm
Posts: 894
Location: Sacramento, CA
Post Re: Delete records from a table.
Need a little more information from you. What is the names of

The name of the window the table is on
The name of the table you want to update
The name of the popup window

at first glance everything looks ok, but I think your not referencing the window where the table is located. Just a little more info will make it a little more clear...

_________________
Greg Simpson
Inductive Automation
Technical Support Rep


Fri Jun 22, 2012 7:33 am
Profile
Sergeant
Sergeant

Joined: Wed Aug 18, 2010 7:29 am
Posts: 47
Location: Little Rock, AR
Post Re: Delete records from a table.
The popup window contains the table I want to update and the popup window name is User Email. The database table name I'm trying to change is emails.

Here is the script to open the popup window.

param1 = event.source.parent.UnitNumber
if event.source.parent.getComponent('UnitTable').selectedRow == -1: #No row selected in Unit Table
system.gui.warningBox("You must select a unit before modifying email table!","No Unit Selected")
else:
system.nav.openWindow('Popups/User Email', {'UnitNumber' : param1, 'UserType' : 'User'})
system.nav.centerWindow('Popups/User Email')

Here's a screen shot of the Overview screen that launches the popup window. I click on Change to execute the above script.


Attachments:
overview.jpg
overview.jpg [ 97.41 KiB | Viewed 2048 times ]
Fri Jun 22, 2012 8:08 am
Profile
Moderator
Moderator

Joined: Mon Jun 04, 2012 8:53 am
Posts: 440
Post Re: Delete records from a table.
Can you email in an export of your project to support at inductiveautomation dot com so we can take a closer look?

_________________
James Lorenz
QA/Build Systems Engineer
Inductive Automation


Fri Jun 22, 2012 8:48 am
Profile
Moderator
Moderator

Joined: Mon Jun 04, 2012 8:53 am
Posts: 440
Post Re: Delete records from a table.
I was able to look at your project and I think I figured it out. You want the email tables on the "Overview" window to update, right? Since those tables are not set to poll, you can manually refresh the table that has been altered using this at the bottom of your "Delete Email Button" script:

Code:
overviewWindow = system.gui.getWindow('Overview').getRootContainer()

if EmailRole == "User":
   system.db.refresh(overviewWindow.getComponent('CustomerEmailTable'), "data")
elif EmailRole == "Dist":
   system.db.refresh(overviewWindow.getComponent('DistEmailTable'), "data")
elif EmailRole == "Nass":
   system.db.refresh(overviewWindow.getComponent('NassEmailTable'), "data")


This will ensure that only the table that has been changed will be refreshed. You will need to put this script on your "Add Email Button" as well in order to refresh those tables when emails are added.

_________________
James Lorenz
QA/Build Systems Engineer
Inductive Automation


Fri Jun 22, 2012 11:31 am
Profile
Sergeant
Sergeant

Joined: Wed Aug 18, 2010 7:29 am
Posts: 47
Location: Little Rock, AR
Post Re: Delete records from a table.
I need to be able to delete an email from this table. I have not be able to do this.

I will add the script you posted to update my tables.

Thanks.


Mon Jun 25, 2012 5:39 am
Profile
Sergeant
Sergeant

Joined: Wed Aug 18, 2010 7:29 am
Posts: 47
Location: Little Rock, AR
Post Re: Delete records from a table.
Here is some additional information on my issue that you may need.

I would like to allow Ignition clients to be able to add/view/delete email address via a SQL table. I have created a Popup window "User Email" to do this. I am passing two parameters from the calling window "Overview" to the "User Email" popup. They are UnitNumber and UserType. The UnitNumber (int) tells me which unit to associate with a particular email address(foreign key to my units table). The UserType(string) tells me which group to associate with each email.

I have been able to add records to the MySQL table "emails", but I not been able to delete them from within Ignition. I am able to add/delete records from MySQL Workbench.

Please review the code I created in the Delete Email Button on the "User Email" popup. The code is shown in the following image posted earlier in the topic. "Delete Button Script.jpg " I believe the syntax is correct as I am getting no errors in either the console window or red bordered error window.

I also have tried using the Delete Query as shown in the User Manual and from the Editable Table window on the website with no success.

I am running Ignition V7.5 and MySQL V5.5 on CentOS.

Thanks


Wed Jun 27, 2012 7:45 am
Profile
Moderator
Moderator

Joined: Mon Jun 04, 2012 8:53 am
Posts: 440
Post Re: Delete records from a table.
The issue appears to be with your actionPerformed script on the Delete Email button. You do not need to add single quotes around the string values for EmailValue and EmailRole, the runPrepUpdate() function makes sure those strings are properly passed to the DB. So remove these lines:

Code:
EmailValue = "'"+EmailValue+"'"
EmailRole = "'"+EmailRole+"'"


And you should be ok. Adding those single quotes will search for the literal string with the single quotes attached, and naturally, none of the emails in your table will have those single quotes added in, which is why the delete query wasn't doing anything.

_________________
James Lorenz
QA/Build Systems Engineer
Inductive Automation


Wed Jun 27, 2012 8:12 am
Profile
Sergeant
Sergeant

Joined: Wed Aug 18, 2010 7:29 am
Posts: 47
Location: Little Rock, AR
Post Re: Delete records from a table.
James:

Made your changes. Same problem. Anything else we can try?

Thanks!


Thu Jun 28, 2012 7:21 am
Profile
Moderator
Moderator

Joined: Mon Jun 04, 2012 8:53 am
Posts: 440
Post Re: Delete records from a table.
Just to be clear, you're talking about the "Group Email Table" component on the "User Email" popup, right? I simplified the Delete Email script, since all you need to know to delete an item in the database is its primary key id (assuming it's unique, which based off of your database screenshot and your scripting, it should be). You also don't need to have a hidden component querying a PK value for the drop down value. Here is what I did:

    - changed the polling mode of the query for the "data" property binding of the "Group Email Table" to off (your script should have system.db.refresh in the add/delete scripts, so it only refreshes the table when necessary)
    - changed the query for that same "data" property to the following so it brings back the primary key with its associated data:
    Code:
    select emailkey, email

    - opened the table customizer for "Group Email Table" and hid the emailkey column
    - changed the script to reference the selectedValue (notSelectedStringValue) of dropdown list, which is the primary key of data to be deleted

Here is the new script for Delete Email, a lot simpler and just requires that primary key value from the dropdown list. Let me know if the data in the table still doesn't refresh properly for you.
Code:
table = event.source.parent.getComponent("Group Email Table")
EmailKeyValue = event.source.parent.getComponent('Delete Email Dropdown').selectedValue

system.db.runUpdateQuery("DELETE FROM emails WHERE EmailKey = %d" % EmailKeyValue)

event.source.parent.getComponent('Delete Email Dropdown').selectedValue = -1
system.db.refresh(table, "data")

overviewWindow = system.gui.getWindow('Overview').getRootContainer()

if EmailRole == "User":
   system.db.refresh(overviewWindow.getComponent('CustomerEmailTable'), "data")
elif EmailRole == "Dist":
   system.db.refresh(overviewWindow.getComponent('DistEmailTable'), "data")
elif EmailRole == "Nass":
   system.db.refresh(overviewWindow.getComponent('NassEmailTable'), "data")   

_________________
James Lorenz
QA/Build Systems Engineer
Inductive Automation


Thu Jun 28, 2012 8:29 am
Profile
Sergeant
Sergeant

Joined: Wed Aug 18, 2010 7:29 am
Posts: 47
Location: Little Rock, AR
Post Re: Delete records from a table.
Can we set up a GoToMeeting for this?


Thu Jun 28, 2012 9:01 am
Profile
Sergeant
Sergeant

Joined: Wed Aug 18, 2010 7:29 am
Posts: 47
Location: Little Rock, AR
Post Re: Delete records from a table.
Nevermind.

I misread your code, once entered correctly it work great!!!

Thanks for all your help on this.

Chris


Thu Jun 28, 2012 9:10 am
Profile
Moderator
Moderator

Joined: Mon Jun 04, 2012 8:53 am
Posts: 440
Post Re: Delete records from a table.  Topic is solved
No problem, glad you got it working.

_________________
James Lorenz
QA/Build Systems Engineer
Inductive Automation


Thu Jun 28, 2012 9:29 am
Profile
Display posts from previous:  Sort by  
Post new topic This topic is locked, you cannot edit posts or make further replies.  [ 13 posts ] 


Who is online

Users browsing this forum: No registered users and 2 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to: