SQL Server and IDENTITY column

Wed Jan 16 16:25:49 EST 2019

Hey Greg,

Keep in mind that your identity column will usually be the primary key of your table and you should mark your primary key column in the schema class, there is a column for that in the schema class. 
First, don't forget to set $useprimarykeys to true in your table class or Omnis won't be smart enough to actually write efficient update and delete queries.
Then, you can write some logic that finds the column you've marked as the primary key in the schema class and call $excludefrominsert and $excludefromupdate for that column.

I have a table class that I use as a base class that has all this logic in its $construct. So when I have a new table in my database I create the schema class, make a table class for it, subclass my base table class and everything just works without writing a single line of extra code.

Kindest Regards,

Bastiaan Olij
Head of development - Instinct Systems: The JobBag People
Ground Floor, 48 Chandos Street
St Leonards NSW 2065
Australia

Phone +61 2 8115 8000
Mobile +61 4 321 44833
bastiaan.olij at instinctsystems.com.au
http://www.jobbag.com

 From:   Grzegorz (Greg) Pasternak <gpasternak at cogeco.ca> 
 To:   <r.ventura at kunvi.it>, OmnisDev List - English <omnisdev-en at lists.omnis-dev.com> 
 Sent:   1/17/2019 2:09 AM 
 Subject:   Re: SQL Server and IDENTITY column 

Rocco; 

Thanks for your suggestions.   

I was actually interested in using table class for $insert and/or $update. 
As other people have suggested, the solution is to put into table superclass a method that does something as follows: 
;   
Do $cinst.$cols.Seqno.$excludefrominsert.$assign(kTrue) 
Do $cinst.$cols.Seqno.$excludefromupdate.$assign(kTrue) 
;   

Then call this method from $construct() of the superclass table.  This way all table classes from inherited superclass will use this method. 
Of course, the above assumes Seqno column to be present on each schema class used by the corresponding table class. 
Where the Seqno does not exist the method should be skipped, alternatively one can add test for presence of the Seqno on the list of columns for the relevant schema class. 

Thanks, 

Greg 

On Jan 16, 2019, at 10:01 AM, Rocco Ventura [Kunvi] <r.ventura at kunvi.it> wrote: 

> Hi Greg, 
>  
> I use IDENTITY normally and  
> - in the SchemaClass the field-type is Sequence and set also as NOT-NULL and 
> Primary-KEY; in the CREATE command I use the following sintax on SQL-Server 
>  
> CREATE TABLE dbo.FLUSSI_LOG (  
> ID INT IDENTITY(1,1) NOT NULL,  
> STATUS TINYINT NOT NULL,  
> DIREZIONE TINYINT NOT NULL,  
> NOME_FLUSSO NVARCHAR(30),  
> (and so on)... 
> CONSTRAINT FLUSSI_LOG_PK PRIMARY KEY ( ID ) ) 
>  
> - in $insert() I just leave the field NULL, the database will assign a 
> unique not-null value (just after I usually re-read the record to read the 
> unique value) 
>  
> - in $update I do nothing, just get the record before updating 
>  
> Bye 
>  
> Rocco  
>  
> ______________________________________ 
> Distinti saluti. Best regards. 
>  
> Rocco Dr. Ventura 
> WMS Analist and Software Developer 
>  
> KUNVI srl - Experiences Holding - www.kunvi.it 
> Email: r.ventura at kunvi.it - Mobile: +39 348 5186051 
>  
> -----Messaggio originale----- 
> Da: omnisdev-en <omnisdev-en-bounces at lists.omnis-dev.com> Per conto di 
> Grzegorz (Greg) Pasternak 
> Inviato: lunedì 14 gennaio 2019 18:24 
> A: OmnisDev List - English <omnisdev-en at lists.omnis-dev.com> 
> Oggetto: Re: SQL Server and IDENTITY column 
>  
> Nick; 
>  
> Thanks for your help, the snipper seems to be just what I was looking for. 
>  
> Have a great day, 
> Greg 
>  
>  
> On Jan 14, 2019, at 12:19 PM, Nick Harris <nick at dataweaver.com> wrote: 
>  
>> Hi Greg 
>>  
>> In the $construct of your table class you have to turn on the  
>> following exclude properties (SEQ being the identity column name) 
>>  
>> Do $cinst.$cols.SEQ.$excludefrominsert.$assign(kTrue) 
>> Do $cinst.$cols.SEQ.$excludefromupdate.$assign(kTrue) 
>>  
>> Then you optionally can override the $insert method of the table class  
>> so that the identity column is populated in the row/list 
>>  
>> Do $cinst.$extraquerytext.$assign('SELECT SCOPE_IDENTITY()') Do  
>> ioSession.$newstatement() Returns loStatement Begin statement 
>> Sta: {INSERT INTO [$cinst.$servertablenames]} 
>> Sta: {[$cinst.$insertnames()]} 
>> Sta: {[$cinst.$extraquerytext()]} 
>> End statement 
>> Do loStatement.$prepare() Returns lvMethodFlag Do  
>> loStatement.$execute() Returns lvMethodFlag Do  
>> loStatement.$fetchinto(lfvIdent) Calculate $cinst.SEQ as lfvIdent 
>>  
>> There should be no need to override the $update method unless it has  
>> to do other work. 
>>  
>> You may have also cut $doinsert, $dowork, $doinserts depending if you  
>> using smartlists to populate the database. 
>>  
>> Regards 
>> Nick 
>>  
>>  
>> -----Original Message----- 
>> From: omnisdev-en [mailto:omnisdev-en-bounces at lists.omnis-dev.com] On  
>> Behalf Of Grzegorz (Greg) Pasternak 
>> Sent: Monday, January 14, 2019 4:22 PM 
>> To: OmnisDev List - English <omnisdev-en at lists.omnis-dev.com> 
>> Subject: SQL Server and IDENTITY column 
>>  
>> I would like to ask if anybody out there has used Omnis table classes  
>> with 
>> $insert() method against SQL Server table using IDENTITY column. 
>> The build in methods $insert() and $update() fail in this case. 
>>  
>> I need to find out what should be the best work around when overriding  
>> build in methods such as $insert and $update in this case. 
>> Can anybody help me with this? 
>>  
>> Thanks, 
>>  
>> Greg 
>> _____________________________________________________________ 
>> Manage your list subscriptions at http://lists.omnis-dev.com Start a  
>> new message -> mailto:omnisdev-en at lists.omnis-dev.com 
>>  
>> _____________________________________________________________ 
>> Manage your list subscriptions at http://lists.omnis-dev.com Start a  
>> new message -> mailto:omnisdev-en at lists.omnis-dev.com 
>  
> _____________________________________________________________ 
> Manage your list subscriptions at http://lists.omnis-dev.com Start a new 
> message -> mailto:omnisdev-en at lists.omnis-dev.com  
>  
>  
> --- 
> Questa e-mail è stata controllata per individuare virus con Avast antivirus. 
> https://www.avast.com/antivirus 
>  
> _____________________________________________________________ 
> Manage your list subscriptions at http://lists.omnis-dev.com 
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com  

_____________________________________________________________ 
Manage your list subscriptions at http://lists.omnis-dev.com 
Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com  