record.INSERT not failing as expected

kallja

Hi folks.

I just ran into an other bit of weird Nav behavior.

Originally i noticed the issue when testing custom functionality to import sales prices for items.

What happens is, that when a record of type Sales Price has been populated and INSERT(TRUE) is subsequently called, the INSERT does not fail even though a duplicate Sales Price row already exists in the database. The object (XMLPort in this case) only fails with an error about the duplicate once it's finishing execution, or COMMIT is explicitly called. This caused problems as i was relying on the object terminating with a runtime exception.

A peculiar addition to this mess is the fact that if the return value of the INSERT-call is checked, it actually returns FALSE to indicate that the call failed due to an existing duplicate.

I've managed to reproduce the issue with a number of tables types.

A simple way to reproduce the issue is to create a new table, one with a single field. I tried with a field of type code(10).

I then wrote the following codeunit to try and insert duplicate records into the table.

testTable.INIT;
testTable.Id := '99999';

// Ensures that the record actually exists in the database.
IF testTable.INSERT(TRUE) THEN
  COMMIT;

testTable.INSERT(TRUE);
testTable.INSERT;

MESSAGE('Duplicate records successfully inserted.');

Contrary to what may seem the obvious behavior to most, the message dialog is actually shown.

I've tested the above with Nav 5.0 SP 1 and Nav 2009 R2 with the exact same behavior. The issue is also present in the RTC.

Has anyone else run into this behavior? Is there a hotfix out there?


- Kallja

David_Singleton

How odd. This bug was in an early version of 4.00, (SP1 I think) but I thought it was long since fixed.

David_Singleton

Hmm actually no, just looking again, your issue looks different, your's maybe because of bulk insert. Try running it with the debugger, as the debugger should disable bulk insert.

FDickschat

I agree that this is caused by bulk inserts but then I tested it also in a 3.70 and it behaves exactly the same :shock:

Messages are collected during code execution and are not displayed immediately. They are also not invalidated when an error appears. So NAV first shows the message and right after that the Error that the record already exists. I have to admit I am not happy with the order 1st Message, 2nd Error.

ufuk

Bulk insert feature cause NAV to send INSERT command at the end of the execution so this behaviour seems normal to me. And yes, in some cases it can cause problems if you wait runtime errors. If I understood your problem correctly, I think you have to modify your code to check if a record already exists and continue the flow regarding this result.

rdebath

There are couple of things you can do if you need to push the pending 'bulk' updates to the database.

One possibility, as you've found, is to always look at the return value but this slows things down and you have to manufacture your own errors.

The best possibility IMO would be a 'flush' method or command, but Microsoft NEVER think that far ahead.
You might consider putting a request for that change.

IMO the best you do is to call something that naturally flushes the queue to the database; I find countapprox is a nice one; like this:

IF TestTable.COUNTAPPROX = 0 THEN; // Flush TestTable inserts.

It's a simple command which is specifically designed to be fast and has the nice property that it sets no extra locks in itself.
It's probably the best you'll get without a true 'Flush'.

PS: It's also useful for debugging which BTW does NOT disable bulk updates.

kallja

Hello guys and thanks for your replies!

Sorry for the inactivity. I'm currently working part time (one day a week) and couldn't be bothered to think about Nav while i'm not at my workstation.

I was unfamiliar with the bulk insert functionality, but imagined that something like it could be the cause of the issue here. I've since done some reading on the matter and agree with you guys that it is most likely the cause of this behavior.

While this functionality obviously adds to application performance it still unfortunately goes in my pool of things that Nav does wrong. The reason for my saying this is that bulk inserts cause Nav to behave differently in seemingly identical contexts. It either fails at the initial INSERT call or doesn't, depending on the types of fields that the target table has. In my opinion the existence of a duplicate should always be checked at the time of the INSERT call, even if it would result in some loss of performance. A simple select is always a considerably less expensive operation than an insertion.


- Kallja