hi,
I know this is very old but I tried the code you suggested above to see if it works for me.
I’m trying to insert or update a row in the table based on if or not I find a similar value in an existing row.
Could you take a look and see what I’m missing or doing worng?
Thanks!
Either you haven’t provided the entire script, or r is undefined. Though I’m pretty certain that if this is the entire script you will get an error. I believe that r should be row
Post the entire script or at least all pertinent parts and use the preformatted text option (</> button) to post your code, as opposed to a screen shot.
Also you need to provide a column number or the column name as a string literal.
for row in range(data.rowCount):
if data.getValueAt(row,'serialnumber'):
#run update query
else:
#run insert query
This is my entire script
I replaced r with row but still won’t work
if previousValue != currentValue and initialChange == False:
laserdata = system.tag.read("[.]Laser_Sink_Number").value
db = 'PostgreSQL'
if laserdata == 'BARCODE READ FAIL':
laser = 'Scan Failed'
system.db.runPrepUpdate("INSERT INTO packtrack (generictime, lasertime, lasersink) VALUES (CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?)", [laser], db)
else:
laser = 'Scan Successful'
table = event.source.parent.getComponent('packtrack')
data = table.data
for row in range(data.rowCount):
if data.getValueAt(row, serialnumber) == laserdata:
system.db.runPrepUpdate("UPDATE packtrack SET lasersink=?, lasertime=CURRENT_TIMESTAMP WHERE serialnumber=?", [laser, laserdata], db)
else:
system.db.runPrepUpdate("INSERT INTO packtrack (generictime, lasertime, lasersink) VALUES (CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?)", [laser], db)
as a note, the script works at:
if laserdata == ‘BARCODE READ FAIL’:
it deosn’t work from here down:
else:
laser = ‘Scan Successful’
I changed two things:
The column name must be supplied as a string, or you need to provide the column index.
if data.getValueAt(row, 'serialnumber') == laserdata:
If you are receiving an error, what is it?
I don’t get an error
I changed the column as a string but still no good
I tried a different approach but it still won’t work and I don’t get any error:
if currentValue.value == True:
laserdata = system.tag.read("[.]Laser_Sink_Number").value
db = 'PostgreSQL'
if system.tag.read("[.]Laser_Barcode_Error").value == True:
laser = 'Scan Failed'
system.db.runPrepUpdate("INSERT INTO packtrack (generictime, lasertime, lasersink) VALUES (CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?)", [laser], db)
else:
laser = 'Scan Successful'
system.db.runPrepUpdate("INSERT INTO packtrack (generictime, lasertime, serialnumber, lasersink) VALUES (CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?, ?)", [laserdata, laser], "ON CONFLICT (serialnumber) DO UPDATE SET lasersink=?, lasertime=CURRENT_TIMESTAMP WHERE serialnumber=?", [laser, laserdata], db)
system.db.runPrepUpdate(query, args, query2, args, db)
This isn’t valid syntax - you can only provide one query string and one set of arguments to runPrepUpdate. Barring anything else (once again: have you added print statements to verify the rest of your logic?) you should be sending a single SQL query. I’d highly recommend using multi-line strings to make your query more readable, as well:
if currentValue.value:
laserdata = system.tag.read("[.]Laser_Sink_Number").value
db = 'PostgreSQL'
if system.tag.read("[.]Laser_Barcode_Error").value:
laser = 'Scan Failed'
query = """
INSERT INTO packtrack (generictime, lasertime, lasersink)
VALUES (CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?)
"""
system.db.runPrepUpdate(query, [laser], db)
else:
laser = 'Scan Successful'
query = """
INSERT INTO packtrack (generictime, lasertime, serialnumber, lasersink)
VALUES (CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?, ?)
ON CONFLICT (serialnumber) DO UPDATE
SET lasersink=?, lasertime=CURRENT_TIMESTAMP
WHERE serialnumber=?
"""
system.db.runPrepUpdate(query, [laserdata, laser, laser, laserdata], db)
please excuse my ignorance 
, where would I see the print result?