Return uniqueidentifier primary key on INSERT, UPDATE

Ignition
1

Hello All,

I’m using uniqueidentifier/guid as my primary keys in my table – was expecting the PK to be returned using the system.db.runPrepUpdate() method – keep getting “0” returned… am I missing something here? Does the getKey flag only work for identity/integer columns?

db = "aux_data"
query = "INSERT INTO skip_delay_reports (start_range, end_range) VALUES (?,?)"
args = [event.source.parent.getComponent('Date Range').startDate,event.source.parent.getComponent('Date Range').endDate]
report_id = system.db.runPrepUpdate(query,args,db,getKey=1)
print("Report ID: %s" %report_id)

Every time, output is:

Report ID: 0

the id column is populated by default using the newsequentialid() function

Currently using MS SQL server 13.0.4001.0

I’ve looked into using OUTPUT statements per the link below – don’t seem to work with the system.db.runPrepUpdate method…

Any ideas? Perhaps I have to create a trigger on the SQL server side?

Kind Regards,

-Paul

2 Likes
2

not sure why it always takes posting in the forum before stumbling upon the solution minutes later…

I ended up using:

db = "aux_data"
query = "INSERT INTO skip_delay_reports (start_range, end_range) OUTPUT INSERTED.id VALUES(?,?)"
args = [event.source.parent.getComponent('Date Range').startDate,event.source.parent.getComponent('Date Range').endDate]
report_id = system.db.runScalarPrepQuery(query,args,db)
print("Report ID: %s" %report_id)

Proper ID returned:

Report ID: 8A9BA257-32F7-43A4-8108-9447567C875C