NAV500SP1 bulk insert and code-testing on SQL
As we all know, SQL has the “negative” aspect that it is possible to read records that are not yet commited (with some transaction type you can avoid this, but this is an other discussion). With the native DB, this is not possible.
Why do I put double quotes around negative? Well, for testing it is a quite interesting feature. Why?
I give an example: let’s say you added some code that has to write other things when shipping a sales order. So you write your code. Then you create a sales order and ship it. You look at the results in the DB and you see that it doesn’t give the correct result.
You search where the problem is (just looking at the code or using the debugger or even closing your eyes and start changing. Actually the blog is not about finding the error). The point is: you change your code. YOU CREATE A NEW SALES ORDER AND SHIP IT. I just hate that! I want to use the same sales order, but I can’t because I just shipped it.
Actually it is possible on SQL to see the results of your code AND reuse the sales order for shipping it.
How?
Write your code and just after that code you put a CONFIRM-statement:
IF NOT CONFIRM(’Just finished my code.\Continue?’,false) THEN
ERROR(’Interupted’);
This CONFIRM will block the transaction and ask you if you want to continue. Now DON’T answer it yet. With another session, you can check the records your code has changed/inserted/deleted. If it is wrong, just answer NO in the confirmation box. And you will see that your sales order has NOT been shipped. Of course you can destroy this system if you misuse COMMIT-statements.
This way I can re-use the same sales order (or other data until I am happy with my code).
IMPORTANT 1 : DON’T do this on a production DB because you block everything while the CONFIRM-box is waiting for input! Remember the transaction remains ACTIVE until you respond to the CONFIRM-box!
IMPORTANT 2 : when you have finished, don’t forget to remove the CONFIRM-statement (or at least comment it).
But NOW with the bulk-insert in NAV500SP1 it is possible this trick doesn’t work.
When is that?
When you are using ONLY INSERT in a certain table (but not alwyas). MODIFY and DELETE have no problems.
Why is that?
Because the bulk insert buffers the inserts on a table until later. I am not going deeper into this. It is written in the manual, there are blogs about it, …
How to avoid it?
Just before the CONFIRM-statement, put this code:
recTheTable.RESET;
IF recTheTable.ISEMPTY THEN ;
This code will just check if the table is empty or not and thus flushing the bulk-insert-buffer. Remember also to remove that code when you are finished.
