SQL statement inserting record twice

Ignition
1

This is my code. Everything works as intended except for the "system.db.runNamedQuery('M5Waffles/addM5COMP')" which is supposed to take values from a table and place them in another table, but it does it twice. This query is only in one section so I know I'm not calling it twice. I'm thinking maybe it's because it's in a loop?

The query for system.db.runNamedQuery('M5Waffles/updateM5CHANGE', {'BARCODE':Old}) reads "UPDATE TABLE1 SET CHANGE = 1 WHERE BARCODE = :BARCODE"

The query for system.db.runNamedQuery('M5Waffles/addM5COMP') reads "INSERT INTO TABLE2 (PART, LOT) SELECT PART, LOT FROM TABLE1 WHERE CHANGE = 1"

    lotpart = system.db.runNamedQuery('M5Waffles/getM5LOTPART', {'KIT':KIT}) #returns 2 columns of numbers 'LOT' and 'PART'
    prevLot = None   #initializing variable
    prevPart = None  #initializing variable
    for row in range(lotpart.getRowCount()): #reading values
	    lot = lotpart.getValueAt(row, 'LOT')
	    part = lotpart.getValueAt(row, 'PART')
	    lot = str(lot)
	    part = str(part)
	
	    if not (prevLot == lot) and not (prevPart == part):
		    system.db.runNamedQuery('M5Waffles/updateM5CHANGE', {'BARCODE':Old}) #changes values in another column in table
		    system.db.runNamedQuery('M5Waffles/addM5COMP') #SQL statement is supposed to take values updated in the query above and place them in another table
		    system.perspective.openPopup('M5serialID', 'MRSI M5/Serial affected')
	
	    prevLot = lot
	    prevPart = part
2

How do you know that the whole thing isn't getting called twice? There are places in Perspective where that happens.

Share more about the component/view/session environment that calls this script.

3

This script only runs when a button is clicked

4

Have you put logging at the beginning of the script? If so, does it log twice?

Share more.

5

I do log some numbers in the beginning but they aren't logged twice

6

My initial gut says that it probably has something to do with if not (prevLot == lot) and not (prevPart == part):

That's a weird way to write it, I would suggest if prevLot is not lot and prePart is not part: since you're comparing strings.

Although, better might be if all((prevLot is not lot),(prevPart is not part):

This has to do with strings being immutable and all that, but without getting to technical, Not only is it better I also think it more readable.

EDIT: Apparently I remembered this wrong. I need more coffee today.

What happens if you put a system.perspective.print('New Lot or Part') after the conditional?

if all([prevLot != lot,prevPart != part]):
    system.perspective.print('New Lot or Part')

Does it print out more times than you expect?

1 Like
7

One other thing I would note is that the parameters are {'BARCODE':Old} but Old is not defined in the loop, so I assume it's defined before the loop. So even if those conditions are fixed, it seems like it will always run the same query.

1 Like
8

Share your query. You aren't passing in any values in the loop so you're grabbing the values via the query. Hard to troubleshoot inserting too many values when we can't see what is actually selecting and inserting them...

9

The "if prevLot..." statement works but still shows up twice in the table.The "if all..." statement doesn't work at all.

10

Query says "INSERT INTO TABLE2 (PART, LOT) SELECT PART, LOT FROM TABLE1 WHERE CHANGE = 1"
CHANGE is the column being updated in the query system.db.runNamedQuery('M5Waffles/updateM5CHANGE', {'BARCODE':Old})

11

oops, that's what I get for not trying it in the console. :face_exhaling:

You need to wrap the conditions in a list.

all([prevLot != lot,prevPart != part])

Did you add the system.perspective.print() to the script? Did it print out more than you expected?

12

I agree, seems like for any lot and part pair that is different from the pair right before it in the dataset, you run the exact same code. Unless the M5Waffles/updateM5CHANGE query somehow changes with each iteration of the loop, its just going to run the exact same queries multiple times.

What does the M5Waffles/updateM5CHANGE query look like?

13

Can you provide the entirety of the script or show how or where the value for Old is coming from?

14

This works but it still shows up twice in table

15

it says "UPDATE TABLE1 SET CHANGE = 1 WHERE BARCODE = :BARCODE"

16

Old is a barcode value that is manually inserted in a textbox

17

Are you sure TABLE1 only has one row with CHANGE = 1 ?

2 Likes
18

So it is constant throughout the whole loop.

Since the query:

UPDATE TABLE1 SET CHANGE=1 WHERE BARCODE = :BARCODE

Is not specific to either the part number or the lot number, there really is no reason it should be in the loop.

I see you're opening a popup. Is the Idea that you run through the loop until the first lot or part is found to be different? I ask because you are initializing prevPart and prevLot to None, so the first time through the loop you will run your queries. Then the next time they are different you will run them again, but since Old hasn't changed the queries will do exactly the same thing.

What happens if you initialize prevLot and prevPart to the values in the first row?

prevLot = str(lotpart.getValueAt(0,'LOT')
prevPart = str(lotpart.getValueAt(0,'PART')

for row in range(1,lotpart.rowCount):

Also, not sure why you're converting the "numbers" to a string, if they are numbers you should be comparing them as numbers not strings.

19

Good question.

  1. Where is CHANGE being set back to 0 (or anything other than 1)?
    Is TABLE1.CHANGE used by anything other than the update? If not, could you use one query instead? Something like
INSERT INTO table2 (part, lot) SELECT part, lot FROM table1 WHERE barcode = :barcode

  1. How many rows are being returned by MSWaffles/getMSLOTPART'? The code as shown will insert as many rows into table2 as there are rows returned from getMSLOTPART. The rows in table2 will all be the same (unless the popup is changing old. If table1.changed isn't being changed back to 0 somewhere that you aren't showing that is executing inside the loop then first INSERT will happen n times (where n is the count of lot/parts in the kit?), the second INSERT will happen n-1 times, etc.

  2. Is there a unique constraint on table1.barcode? Are you sure there is only one matching record for it?

20

The pop up can't change old as old is not modified anywhere in the loop. Old is (in the scope of the loop) constant.