Why doesn't autoIncrement work?

[CampaignerForLife]

Title.

When you add one record to the table, it should be like

x (Autoincremented) = 0

then

x = 1

But it keeps being 0

Why? 

[LakshmiPravallika]

Hi @CampaignerForLife ,

It looks like there is an issue in generating the next Values of the sequence.

Could you Please try running the below SQL Code in SQL Code activity and let me know if incrementing is working fine?

alter table sqltablenameinpreviewtab alter sqlnameofautopkcolumn set default nextval('pkSequenceinPreviewTab') ;

After running this command, try adding any record and check if its incrementing fine.

Regards,

Pravallika.

[LakshmiPravallika]

Hi @CampaignerForLife ,

Please define autopk="true" inside the <element> tag in the schema instead of defining it at the attribute Tag.

Please find the documentation link for the same.

https://experienceleague.adobe.com/docs/campaign-classic/using/configuring-campaign-classic/schema-r...

In your example it would be as shown below:

<element autopk="true" label="testTableName" name="testTableName">

</element>

Also you don' t need to define the attribute and the key attribute XML separately for autopk, It automatically gets generated by adding autopk="true" and please check in preview Tab.

Also please update the DB Structure after doing the changes.

Regards,

Pravallika.

[Parvesh_Parmar]

Hello @CampaignerForLife, 

Can you please explain what is x there?

is the primary key or something else?

If x is not a primary key then autoincrement not going to work automatically. 

By default, autoincrement work only with primary key and where you define autoincrement flag true in the schema.

Thanks,

Parvesh

[CampaignerForLife]

Hello Parvesh, 

The schema is like this
<key internal="true" name="PrimaryKey">
<keyfield xpath="@PK"/>
</key>
<attribute advanced="true" autoIncrement="true" name="PK" type="long"/>

The first time I insert something in db, it works properly, but after that it gives me error (as the PK value keeps at 0)

[Parvesh_Parmar]

Hello @CampaignerForLife, 

Can you please check what is the pkSequence name for this schema?

For e.g. In the below example auto_custest01_seq is the sequence.

<element autopk="true" label="Test01" name="test01" pkSequence="auto_custest01_seq"
sqltable="CusTest01">

Now you need to check if this sequence is exit or not in system.

You can run the below SQL code via SQL code activity in a workflow.

Just change the sequence name with your sequence name in that code.

IF NOT EXISTS (SELECT * FROM XtkNewId WHERE sSequence='auto_custest01_seq')
INSERT INTO XtkNewId(sSequence, IdSeed) VALUES ('auto_custest01_seq', 1001);

Normally it should happen automatically when you define a schema and run the update the database command after that.

But it seems, it is not done therefore you are getting that issue.

Kr,

Parvesh

[AkshayAnand · Accepted solution]

Hi @CampaignerForLife 

You can try the below approach to make an autoincremental Key for a custom schema :-

Create a Schema with "AutoPK="true" " 

Do Update Database structure by following the path -- Tools >> Advanced >> Update database structure.

Once the DB has been committed logout and login.

Now create a workflow to alter the sequence that has been automatically create at the time of DB update. (You can edit the same at the time of DB update also in the SQL window.)

In the SQL activity, insert the following code to create your custom auto incremental key by 1

drop sequence auto_cussequenceTest_seq; --(dropping the Sequence of the custom schema created at the time of DB update, you can find it in the preview tab of the schema);

create sequence auto_cussequenceTest_seq; --(Creating the Sequence for the custome schema)
alter table CusSequenceTest alter isequenceTest set default nextval('auto_cussequenceTest_seq' ); --(setting up of nextVal for incremental value)
INSERT INTO CusSequenceTest (isequenceTest ) VALUES (0); --(defining the first 0 value for the primary key, hence the records now will have sequence as 1,2,3... )

insert into CusSequenceTest( sEmail ) values ('testingAkkitest3.com'); --(Testing by inserting a record using SQL sript)

You can also test the same by inserting the record using JS.

Hope this might help.

Regards

Akshay