"live" SQL table editing

Ignition
1

(this question is relative to the tech support phone message I left 5/25/2011, ~9:00 EDT)

I’ve been working on a table editor. I need to be able to compare the sequence number that got entered into a text field. If it matches the sequence number of another row, it should fail (so that you can’t have two rows with the same number). If it’s the same as the row that got picked, it should permit the change. Here’s what I have:

origpointer = event.source.parent.getComponent('Field_Pointer').text
seqpointer = event.source.parent.getComponent('Field_Pointer').text
sequence = event.source.parent.getComponent('Field_Sequence').text
prompt = event.source.parent.getComponent('Text Area_Prompt').text

if seqpointer != "" and system.db.runScalarQuery("SELECT COUNT(*) FROM prompt_file WHERE seqpointer = '%s'" % seqpointer) > 0 and seqpointer != origpointer:
	system.gui.messageBox("Sequence Pointer already exists.")
elif seqpointer == "":
	system.gui.messageBox("Pointer field cannot be empty.")
elif sequence == "":
	system.gui.messageBox("Sequence field cannot be empty.")
else:
	system.db.runPrepUpdate("UPDATE prompt_file SET `sequence` = ?, `prompt` = ? WHERE `seqpointer` = ?", [sequence, prompt, seqpointer])
	system.nav.closeParentWindow(event)

Basically, if pointer 87 is selected, it should allow pointer 87 to be edited. But if pointer 87 is selected, the user should not be able to change “87” to “88” if pointer 88 already exists. If “88” does NOT exist, then pointer 87 should become pointer 88. Does that make sense?

Any thoughts?

Thanks,

  • Kevin
2

Sorry, it’s not exactly clear to me what you’re trying to do, and above all, what’s not working correctly. One strange thing, though: It appears you’re assiging the exact same value to “origpointer” and “segpointer” - “event.source.parent.getComponent(‘Field_Pointer’).text”, and then checking to see if they’re different in the first if statement. Did you mean to pull one of the values from a different component?

Otherwise, if you could describe a bit more how it’s acting right now and what isn’t quite right about it, that might be helpful.

Regards,

3

I’ll try, Colby …

If the vwin export that I sent Mike re: ticket 2777 is still available, you can see what the pop-up is supposed to look like. The original (working) script looked like this:

seqpointer = event.source.parent.getComponent('Field_Pointer').text
sequence = event.source.parent.getComponent('Field_Sequence').text
prompt = event.source.parent.getComponent('Text Area_Prompt').text

if sequence == "":
	system.gui.messageBox("Sequence field cannot be empty.")
else:
	system.db.runPrepUpdate("UPDATE prompt_file SET `sequence` = ?, `prompt` = ? WHERE `seqpointer` = ?", [sequence, prompt, seqpointer])
	system.nav.closeParentWindow(event)

The pop-up has three fields: one for sequence pointer (e.g. 100), one for sequence (e.g. DRAIN), and one for prompt (e.g. PRESTART CHECKS COMPLETE; READY FOR SYSTEM DRAIN.). Those 3 fields represent columns in a SQL table. “sequence” is the Primary Key. We use this information to communicate to the operator what the PLC is doing while the skid is running.

For editing, the operator selects the row in the prompt table and clicks the edit button (much like the add/edit/delete row exercise in the 5-day Core Training). I originally had the sequence field disabled so as not to be able to change it. My senior programmer said it’d be nice to be able to change the sequence number when the situation calls for it.

Because “sequence” is the primary key, it can’t be empty. Similarly, I need a way to capture the sequence number and block the user from changing it to a sequence number that already exists (you shouldn’t be able to change prompt 87 to prompt 88 if prompt 88 already exists). The prompt can be changed under two conditions: if the sequence number doesn’t change (i.e. select prompt 87 and leave it as 87 when you click the save button), or if the sequence number changes to a number that doesn’t exist in the prompt table.

The point of assigning the sequence text twice was the premise that “seqpointer” might change, but I need to be able to compare it to the original sequence pointer of the row that got picked when the edit button was clicked.

The part that isn’t working is when the row number that a sequence pointer is being changed to already exists. If I edit row 87 and change the sequence number from 87 to 88, I should get a nasty-gram if number 88 already exists. Right now, it doesn’t. If 88 exists, hitting the save button overwrites it. I don’t want that.

Does that help clear it up at all?