Knowing that two datasets are equal or not equal is just the first step.
If they are not equal and you want them to become equal then there needs to be a decision for each row that is different, either INSERT, UPDATE or DELETE.
Assuming that you can’t use SQL Minus or other comparisons before getting to Ignition then you’re stuck with row-by-row comparison.
I’m sure there are more efficient methods but here’s an example of INSERTs, DELETEs and UPDATEs I threw together for a UI dataset (e.g. Power Table) compared to a dataset from an SQL SELECT.
In this example the user edits (UI*) are the controlling/authoritative data and the database is updated based on the user edits.
Since I just threw this together please don’t be overly critical when you find typos and logic mistakes. You will need to tweak the INSERT, UPDATE and DELETE syntax for your data and RDBMS.
Since I’ve never done a runPrepUpdate for an UPDATE statement, I switched to just runUpdate at the end.
database = system.tag.read( 'Config/DatabaseConnection' ).value
pkColumn = 'yourPKcolumnName'
# make sure both datasets are sorted by Primary Key (or candidate key) Values
uiDataset = system.dataset.sort( uiDataset, pkColumn, True )
dbDataset = system.dataset.sort( uiDataset, pkColumn, True )
# the user edits are the controlling/authoritative data
rowsToInsert = []
rowsToDelete = []
# handle INSERTS and DELETES before looking for UPDATES
if uiDataset.rowCount != dbDataset.rowCount:
uiRow = 0
dbRow = 0
currentRow = 0
while currentRow < uiDataset.rowCount:
uiPk = uiDataset.getValueAt( uiRow, pkColumn )
dbPk = dbDataset.getValueAt( dbRow, pkColumn )
while uiPk != dbPk:
if uiPk > dbPk:
# uiPk record was INSERTED by the user
print 'row %n with Primay Key "%s" was INSERTED by the user' %(currentRow,uiPk)
# store the INSERTed row. Note that INSERTs will be done from the uiDataset
# advance to the next row in the uiDataset for the next compare
uiRow = uiRow + 1
# hold the dbDataset comparison back one row - until the primary keys are back in step
rowsToInsert.append( uiRow )
elif uiPk < dbPk:
# uiPk record was DELETED by the user
# advance to the next row in the dbDataset for the next compare
print 'row %n with Primay Key "%s" was DELETED by the user' %(currentRow,uiPk)
# store the DELETEd row. Note that Deletes will be done from the dbDataset
dbRow = dbRow + 1
# hold the dbDataset comparison back one row - until the primary keys are back in step
rowsToDelete.append( dbRow )
elif uiPk == dbPk:
# key values are the same, advance to the next rows in both datsets
uiRow = uiRow + 1
dbRow = dbRow + 1
uiPk = uiDataset.getValueAt( uiRow, pkColumn )
dbPk = dbDataset.getValueAt( dbRow, pkColumn )
# THIS LINE IS CRITICAL! Without it, you will have an endless loop
currentRow = currentRow + 1
# We've found the INSERTs and DELETEs, update the database with the user edits
for row in rowsToInsert:
# build the INSERT statment
sql = 'INSERT INTO <table> <columnNames> VALUES <?,?,?>'
rowData = []
for column in range( uiDataset.columnCount ):
rowData.append( uiDataset.getValueAt( row, column ) )
# INSERT the data - this should be in a try/except block for error handling
system.db.runPrepUpdate( sql, data, database )
for row in rowsToDelete:
# build the DELETE statment
pkValue = dbDataset.getValueAt( row, pkColumn )
sql = 'DELETE <table> <columnNames> WHERE %s = ?' %(pkColumn, pkValue)
# DELETE the data - this should be in a try/except block for error handling
system.db.runPrepUpdate( sql, [pkValue], database )
# now that the INSERTs and DELETEs have been handled, look for UPDATEs
# refresh the dbDataset using the same method it was first retrieved...
# sort the dbDataset by the primary key if it wasn't sorted via SQL
if uiDataset.rowCount == dbDataset.rowCount:
# use column names instead of ordinal numbers for updates
columnNames = system.dataset.getColumnHeaders( uiDataset )
# remove the primary key from the columns to be updated
columnNames.remove( pkColumn )
for row in range( uiDataset.rowCount ):
# UPDATE if the PKs are the same
pkValue = uiDataset.getValueAt( row, pkColumn )
updateCounter = 0
if dbDataset.getValueAt( row, pkColumn ) == pkValue:
updateCounter = 0
sql = 'UPDATE <tablename> SET '
for column in columnNames:
# UPDATE if the data is different
if uiDataset.getValueAt( row, column ) == dbDataset.getValueAt( row, column ):
sql = sql + column + '=' + uiDataset.getValueAt( row, column ) + ','
updateCounter = updateCounter + 1
# drop the trailing comma
if sql[-1] == ',':
sql = sql[:-1]
if updateCounter > 0:
# The WHERE caluse is CRITICAL!!!
sql = sql + " WHERE %s='%s'" %(pkColumn,pkValue)
print sql
system.db.runQuery( sql, database )
