Monday, 6 October 2008

Careful with those Captions!

On occasions I have needed a table that is similar to an existing table but with maybe some changes that I didn't want to have to make to the standard system (such as adding a field to the primary key).

One easy way to do this is to design the table you want to copy and use the File, Save As... menu option to save the table as a new table name and ID.

If you do this, make sure you change the Caption for the table to match the table name. Today I spent far too long investigating a problem with an error message telling me a record could not be inserted into table X since it already existed.

For some bizarre reason, the error was only being triggered when a commit was being called so there was some guesswork as to where the error was actually taking place. No matter what I tried it appeared that something really weird was going on. Eventually in a fit of frustration, I deleted the table in question and yet the error message still appeared.

The problem? Well the table had been created by copying another table and the caption had not been changed. As a result the error message was giving the wrong table name - causing great confusion.

Now here's a question for seasoned veterans. Does anyone know why the error was only appearing at the point of a commit or when all commits were removed after all other code in the transaction had executed? When you put a lock on a table, does NAV use SQL BEGIN TRANSACTION and then use COMMIT or ABORT if an error is thrown, or does it cache all transactions to the locked table and only send them to the database when the COMMIT is executed? I think I might do a little experiment and log the SQL commands to see what is going on. If this is the case then removing the LOCKTABLE command would have helped the debugger pinpoint my error.

3 comments:

kriki said...

Since 5.0SP1 NAV waits to send the inserts to SQL to avoid too much round-trips and thus slowing down everything. This is called bulk-insert in NAV (it is NOT the bulk-insert of SQL!).
NAV sends the records only when a the server must return something from the DB (like in IF rec.INSERT THEN).
Also the native server has this. If I remember correctly, you can test it like this:
rec."Primary Field" := 'A';
rec.DELETE(FALSE);
ERROR('Some Error');
=> you will get the 'Some Error' and not the error that the record does not exist. If on the other hand you used the debugger, you get the error that the record doesn't exist.

watson said...

great blog
weldone

Jimy said...

good work
and kriki i have solution for ur porblem