OutSystems trying to insert NULL into the auto increment ID attribute

OutSystems trying to insert NULL into the auto increment ID attribute

  

I added an attribute to an existing entity that already has a number of records in. I have a screen to create new records (entries) for this entity and we have inserted quite a few records using this screen without a problem. After I added the attribute I started having this problem, I get this error message when I call the “Create” action of this entity:

ORA-01400: cannot insert NULL into ("OSADMIN"."OSUSR_8LZ_CONTRAC9"."ID")

The “ID” is the only field in the table that ismandatory, the others can all be NULL, here is the table in Oracle to show this is in fact correct:

Notice the "Nullable" column, only the ID has "No", so it cannot be any of the others causing the problem.

Screenshot of the "Save" action of the screen where I call the “Create” action of the entity with which I create the record, this is causing the error (raising an exception). Note that this used to work before. Off course if I step through the code the "ID" attribute's value of the record that is passed to the "CreateContract" action shows up as 0 (zero) in the debugger, which is correct because the ID has not been assigned yet. the "CreateContract" action will off course return the new ID of the newly created record, but this never happens because it fails with above error.

It looks like you failed to set the autonumber (or identity or whatever it is called in Oracle) function for this column, though I cannot tell for sure based on the screen shot. In addition, you have the default value set to NULL which explains the error you are seeing.  Lastly, if you really intend to use this new field as an ID value you'll need to fix all the existing records otherwise you'll have non-unique ID values which will cause all kinds of problems.  I would do the following to fix this.

1) Delete the ID column

2) Export the existing records in any reasonable format like Excel

3) Delete the records in the entity

4) Add back the ID column with autonumber/identity set

5) Import the data records


Hope this helps,
Curt


Curt Raddatz wrote:

It looks like you failed to set the autonumber (or identity or whatever it is called in Oracle) function for this column, though I cannot tell for sure based on the screen shot. In addition, you have the default value set to NULL which explains the error you are seeing.  Lastly, if you really intend to use this new field as an ID value you'll need to fix all the existing records otherwise you'll have non-unique ID values which will cause all kinds of problems.  I would do the following to fix this.

1) Delete the ID column

2) Export the existing records in any reasonable format like Excel

3) Delete the records in the entity

4) Add back the ID column with autonumber/identity set

5) Import the data records


Hope this helps,
Curt


Hi Curt, thanks for the response. We have been using this table and inserting without a problem for 5 months already, I added an attribute only and this attribute is no a foreign key nor is it mandatory. So I am pretty sure the Id column is set to "Auto Increment=Yes" and this has not changed. But yes, I have been considering more-or-less what you suggest, but to create an entirely new table and import the data back because we already have other tables dependant on this so I do not know how I will do this yet. But this is just fixing this issue for now, seems to me OutSystems has an underlying bug that needs to be fixed as others will run into the same problem.




Hi Elize,

I'd advise you to check the definition of the table in OS - it might well be the developer adding the attribute has inadvertently removed the "Id" property from the table. If on OS-side everything is ok, I'd check the table definition in Oracle, to see if the autonumber is indeed set. Being pretty sure is nice, but being completely sure is better :).

AFAIK, there is no autonumber in Oracle.

You have to create a sequence, then use trigger to get the sequence.nextval and assign it to primary key.

Right. As you can see, I have no Oracle expertise :). Still valid to check the definition in OS though...

Solution

Kilian Hekhuis wrote:

Right. As you can see, I have no Oracle expertise :). Still valid to check the definition in OS though...

Hi guys, thanks for taking the time to look at the issue.

I found the issue. In case it may be of use to anyone else here it is: 

Oracle uses triggers to implement the auto number, this trigger has a status of "enabled" or "disabled". For some reason this was set to "disabled". I enabled this again in Oracle and all is fine again. OutSystems is looking at the problem I described to them what I did, I think it must have been a particular sequence of events that led to this occurrence, I am sure they will find the problem, for now I am sorted.


Solution

Hi Elize,

Great to hear it works again!

It started to happen to us as well, on existing databases which we use from months/years.  

We are also using Oracle databases behind. 

I did re-enable the triggers but after a few days it appeared again !

I suspect something happens during the deployment as so far the Development database was not affected, only the ones to which we deploy. 

Not sure if it matters, but one recent change is that we did upgrade the Service Studio to 9.1.605.0, and we didn't have this issue before that date. 

So good to hear I am not the only one. At least we know how to fix it by enabling the trigger again. But this is really bad, you never know when it will happen.

It happened to us in Development after we changed a column on a table. OutSystems closed my call after 10 minutes saying they could not reproduce and since I found a fix I am welcome to investigate further myself. I do think it is a problem in the OutSystems because I never knew about this trigger before so I did not change it.

We are on 9.1.306 still. We tried to install 10 but got errors during OutSystems installations complaining about incorrect Oracle managed DLLs that it needs, we tried all the recommended advice we could find on the Internet, nothing worked. So we are stuck with 9 forever.

It seems SQL Server is best used with OutSystems.

Elize, sorry to hear you got so much problems! If you have troubles upgrading to P10, I'd advise you to log an issue with OutSystems, in general they're eager to help.

Elize van der Riet wrote:

Kilian Hekhuis wrote:

Right. As you can see, I have no Oracle expertise :). Still valid to check the definition in OS though...

Hi guys, thanks for taking the time to look at the issue.

I found the issue. In case it may be of use to anyone else here it is: 

Oracle uses triggers to implement the auto number, this trigger has a status of "enabled" or "disabled". For some reason this was set to "disabled". I enabled this again in Oracle and all is fine again. OutSystems is looking at the problem I described to them what I did, I think it must have been a particular sequence of events that led to this occurrence, I am sure they will find the problem, for now I am sorted.


Thanks Elize this explanation helped