SmartList and sequence or serial primary keys

[Paul Mulroney]

Hi X,

You need to write some code to assign the sequence value.

The way I've seen it done, is that you create a table class, and then override the $insert() method to set the new sequence field.  The table class is then linked to the schema class, so the code will execute when you $dowork()

You need these methods in the table class:

$insert method:
Do method getPrimaryKey Returns $cinst.[ivPrimaryKeyName]
Do default Returns vbOK     ;; Do the default $insert()
Quit method vbOK

getPrimaryKey method:
Begin statement
Sta: SELECT max([ivPrimaryKeyName])+1
Sta: FROM [$cinst.$servertablenames]
End statement
Do $cinst.$statementobject().$prepare()
Do $cinst.$statementobject().$execute()
Do $cinst.$statementobject.$fetchinto(vnMaxID)
;  Note: if there are no records, then this returns Null.  Therefore, need to trap null and return 1 instead.
Calculate vnMaxID as pick(isnull(vnMaxID),max(1,vnMaxID),1)
Quit method vnMaxID

Obviously you need to set ivPrimaryKeyName to be the column name for the sequence field, etc.  This can be done in the $construct of the table class.

This is fairly new for me too, so please accept my apologies for any mistakes in this.

Regards,
Paul.


On 13/03/2015, at 1:00 AM, omnisdev-en-request at lists.omnis-dev.com wrote:

> Do $clib.$tables.tSC.$sqlclassname.$assign('sFTEXTOS')
> Do ivList.$definefromsqlclass('tSC')
> Do ivList.$smartlist.$assign(kTrue)
> Enter data 
> If flag true
> 	Do ivList.$dowork()
> Else
> 	Do ivList.$clear()
> End If
> Do $cwind.$redraw()
> 
> I get all rows inserted in the database, but ivList has TX_SEQ as NULLs.
> 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.
> 
> Something similar happens with a row if you do an ivRow.$insert(). You don't get back the TX_SEQ value.
> 
> 
> Is $dowork() not so useful as I thought and I will need to loop the list and insert rows one by one, recovering the primary key manually and updating it's value in the ivList ? 
> Am I missing something ?


A good example of quantum superposition is not only the classical 1930's Schrodinger's Cat experiments but on every BandAid has the phrase, "Sterile unless opened".
-- 
Paul W. Mulroney                                                   We Don't Do Simple Pty Ltd 
pmulroney at logicaldevelopments.com.au        Trading as Logical Developments
www.logicaldevelopments.com.au			   ACN 161 009 374 
Ph: +61 8 9458 3889						   86 Coolgardie Street
Fax: +61 8 9458 2169                       			   BENTLEY  WA  6102