Expand my Community achievements bar.

Update GenCreateOrUpdateSchemaSequenceDDL function to manage negative Ids

Avatar

Community Advisor

6/23/22

The xtkBuilder.GenCreateOrUpdateSchemaSequenceDDL function is really cool to change the sequence of an existing schema.

It is used by the postUpgrade process, for example to go avoid the use of xtkNewId in old builtin schema.

But this function doesn't manage negative values : when it creates the new sequence, it use the 0 as min value. As some sequences can go to negative values (XtkNewId does), the sequence creation failed when it try to get the current value of the old sequence.

We had errors on a postupgrade last week in stage (the postupgrade worked, but several days after, we noticed that we cannot create new nms:group list beceause the sequence were missing). We fixed that by editing the SQL Code generated by GenCreateOrUpdateSchemaSequenceDDL, taking in account the old sequence min value.

 

Thank you

Cedric

2 Comments

Avatar

Community Advisor

6/23/22

Hello @CedricRey , 

 

Thanks for sharing it. 

Just a note or may be you know already, it will be updated to default code, if you will do the build upgrade next time. 

 

We also did some change on campaign SQL function directly to prevent the Update statistics  on large table every time on insert or update, because it was consuming the resource of all data base. When we did the build upgrade then it was changed to default function. 

 

Parvesh.

 

Avatar

Community Advisor

6/24/22

Hello @Parvesh_Parmar ,

Thank you for your help. I'll watch next upgrade, thant for the informations.

 

Today I realised that I was totally incomplete with the description : the problem seems to be specific in Oracle Database. So this must be very particular as I know few company use Oracle for Adobe Campaign.

 

I tested with a SQL Server, the sequences are not managed the same way and there is no impact for negative value. Don't know how others engines deal with it.

 

I also realise that I didn't mention the build number, it's in a 9349

 

Thank you.

Cedric