SmartList and sequence or serial primary keys

Flurin Sievi flurin1 at
Fri Mar 13 11:13:12 EDT 2015

Hi there
> A  subsecuent edit of the ivList fails because the primary keys are NULLs and the smart list is unable to update the rows unless you reread the rows after inserting to recover the TX_SEQ.
Seems like I'm missing the same..

The only way I found to get the DB-generated/inserted sequence value 
back into my original smart list without reloading the whole list is to 
overwrite $dowork, $doinserts and $doinsert in my table (super) class:

1. $dowork
; you want the PK to be handled by your DB
Do $cinst.$cols.[ivPrimaryKeyName].$excludefrominsert.$assign(kTrue)
; you need your own history list
Do $cinst.$history.$makelist($ref.$rownumber,$ref.$status) Returns 
; handling: first deletes, then updates, then inserts

[deletes and updates skipped here b/c it's not what you asked]

Do historyListInserts.$assign(historyList)
Do historyListInserts.$remove(kListDeleteSelected)
If historyListInserts.$linecount()
  Do $cinst.$doinserts(historyListInserts)
End If
[ Do some error handling, and when everything's ok: ]
; this feels very unelegant, but we need the status of all the rows to be
; kRowUnchanged now, and I didn't find something else that worked
Do $cinst.$smartlist.$assign(kFalse)
Do $cinst.$smartlist.$assign(kTrue)

2. $doinserts
For pList.$line from 1 to pList.$linecount step 1
  ; itemRef is a reference to the "original" row in the list
  Set reference itemRef to $cinst.[pList.c1]
  ; now we do the actual insert
  Do $cinst.$doinsert(itemRef) Returns id
  ; and assign the returned sequence value back to the list row
  Do itemRef.[ivPrimaryKeyName].$assign(id)
End For

3. $doinsert
; build the sql text using returning clause
Do sqlString.$assign(con('INSERT INTO ',$cinst.$servertablenames,' 
',$cinst.$insertnames('pRow'),' RETURNING ',ivPrimaryKeyName))
Do $ctask.tSessionObject.$newstatement('doinsert') Returns statementObject
Do statementObject.$prepare(sqlString)
Do statementObject.$execute() Returns ok
If not(ok)
Do $cinst.$getNativeError()
End If
Do statementObject.$fetchinto(resultString)
Quit method resultString

Hm, that became a bit lengthy. Maybe you can use some of it.


More information about the omnisdev-en mailing list