SmartList and sequence or serial primary keys

[IT]

Thanks for all replies. Now I have a better view point to solve this in a proper way.

If you need to use OmnisSQL, the better is generate your own primary keys.
So, before executing the $dowork method I run a method that fills the primary keys of the table or row if needed.
In this way I can enjoy the easy of $dowork method


Do $clib.$tables.tSC.$sqlclassname.$assign('sFTEXTOS')
Do ivList.$definefromsqlclass('tSC')
Do ivList.$smartlist.$assign(kTrue)
Enter data 
If flag true
	Do ivList.$set_pk()
	Do ivList.$dowork()
Else
	Do ivList.$clear()
End If
Do $cwind.$redraw()




$set_pk
=======
Calculate ivPK as 'TX_PK' ;; Now is assigned manually but I will assign automatically using the column of the schema that has the $primaryKey = kTrue

Begin statement
	Sta: select CT_SEQ, CT_PK_COUNTER
	Sta: from FCONTADOR for update
End statement
Do $cinst.$statementobject().$prepare()
Do $cinst.$statementobject().$execute()
Do $cinst.$statementobject().$fetchinto(ivContadorSEQ;ivContador)
If $cinst.$type=kRow
			If $cinst.[ivPK]=0
				Calculate ivContador as ivContador+1
				Calculate $cinst.[ivPK] as ivContador
			End If
		Else
	For $cinst.$line from 1 to $cinst.$linecount step 1
		If $cinst.[ivPK]=0
			Calculate ivContador as ivContador+1
			Calculate $cinst.[ivPK] as ivContador
		End If
	End For
End If
Begin statement
Sta: update FCONTADOR set CT_PK_COUNTER = [ivContador] where ct_seq = [ivContadorSEQ]
End statement
Do $cinst.$statementobject().$prepare()
Do $cinst.$statementobject().$execute()

regards

x


El 14/03/2015, a las 16:35, Jean Marc Azerad <azerad.jm at wanadoo.fr> escribió:

> Nice one Doug!
> 
> JM
> 
> 
>> Le 13 mars 2015 à 12:21, Doug Easterbrook <doug at artsman.com> a écrit :
>> 
>> just to offer a twist on how Bas does it in postgres -- we didn't know about the $excludefrominsert
>> 
>> so we do it a different way in the table class.     we have a method (or methods) that return fields we don't want to actually insert -- like the unique key field.
>> 
>> 
>> Calculate Field as $cinst.$getUniqueKeyField
>> 
>> 
>> then we replace the   @[$cinst. field ]    in the insert values with  'DEFAULT'    eg
>> 
>> Do tStringFields.$replace(pStatement,con('@[$cinst.',Field,']'),'DEFAULT',pStatement)
>> 
>> 
>> 
>> this tells postgres to take the default value that the database suggests when inserting, rather than the value in the smart list.
>> 
>> 
>> 
>> This can be extended to a lot of things.  For example, we have timestamps on each of our tables for date entered and date updated.     So we make the default in the database for those kinds of fields to be now().
>> 
>> and then we replace a   @$cinst.XX_DATE_ENTERED  in the SQL statement with   'DEFAULT'
>> 
>> advantage of this approach is that the date entered field gets replaced server time (not local omnis workstation time -- which is generally off set in some fashion from the server).
>> 
>> With this same approach you can set the default value of fields to be functions if you wish...
>> 
>> 
>> so, a statement that omnis generates in the $insert of a table class might go from
>> 
>> insert into   table (key_field,date_field,funny_field,...) values (@[$cinst.key_field], @[$cinst.date_field], @[$cinst.funny_field], ......)
>> 
>> 
>> to
>> 
>> insert into   table (key_field,date_field,funny_field,...) values ('DEFAULT', 'DEFAULT', someFunction(), ......) returning key_field,date_field,funny_field...
>> 
>> 
>> I find this approach incredibly helpful for synchronizing the date/time on log records across multiple machines --  its all about when the sql gets to the database and the order in which it is processed -- rather than what the time is on a machine.
>> 
>> 
>> 
>> but like Bas suggests ...  skip omnissql -- and go to postgres and you won't regret al the workarounds you have to build for omnissql
>> 
>> 
>> 
>> 
>> 
>> Doug Easterbrook
>> Arts Management Systems Ltd.
>> mailto:doug at artsman.com
>> http://www.artsman.com
>> Phone (403) 536-1205    Fax (403) 536-1210
> 
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com