Some tips and hints about temporary tables

MP900433071[1]Temporary tables in NAV are a great thing, and are frequently used, but there are some misconceptions about them. I see developers do the same mistakes time after time and again. In this post I’ll address some common misconceptions and give some tips and hints that you can use in practice.

There may be a lot of basics for you here, in which case just skip to the end: there I give you a nice tip about how to prevent accidental changes to physical tables when you expect that a table is temporary, and in fact it is not.

A temporary table is a record variable on which you have set the Temporary property to Yes. When you do this, the record variable has the same structure as the physical table, but it is only stored in memory. When you create a temporary record variable, the temporary table is empty at first, and then you can insert, modify, or delete data in it at will, and it does not affect the physical table. I’m quite sure you knew this much, but I repeated it here just to show you how smart I am Smile with tongue out

Temporary Tables in Function Parameters

One thing that is frequently done is passing around temporary tables to functions as parameters. What often goes wrong here is that people tend to think that inside the function the table is temporary because the variable passed to it is temporary. In fact – it’s not. Or at least not always.

First thing to know about record variables is that a record variable does not represent the whole table. It only represents as much as it says on the tin: the record – or to be as precise as it gets – a single record (row) in a table. The Temporary property is simply telling you where the runtime will store the changes you make if you call any of the data modification functions, or where it will look for more records if you iterate through data or run any of the FIND functions.

Another piece of theory is about function parameters. A parameter can be by value or by reference. By value means that the runtime establishes a new in-memory space for the parameter and when you call the function the value of the variable (or constant, or expression) passed to the function is copied into this freshly allocated memory space – literally you get a copy of the original value. By reference means that the runtime literally takes the variable into the function and the parameter is merely another name tag attached to the same variable that was passed to the function.

Last piece of theory here is about record type as a function parameter. When declaring a parameter of type record, you can set its Temporary property to Yes or No, as with any other record variable.

If you combine the last two theory bits, about parameters, and about record type, you may believe that you have these four combinations:

  • Record, by value
  • Temporary record, by value
  • Record, by reference
  • Temporary record, by reference

In fact, you don’t. There are only three combinations:

  • Record by value
  • Temporary record by value
  • By reference

The first two are simple: if the parameter is by value, then the Temporary property on the parameter determines whether within the function the table will be temporary, or physical.

Check this out:

image

Within the first two functions, the actual state of the Cust record is determined by the Temporary property set on the parameter. If you pass a physical record variable to DoSomething_Temporary, the field values of that physical record will get copied to the Cust record, but the Cust will continue to be temporary.

Just as if you did this: Cust := Customer_Physical;

The same applies if you call DoSomething_Physical and pass the Customer_Temporary to it. The field values are copied, but Cust refers to the physical table.

Furthermore, when you have Temporary set to Yes on a by-value parameter, that temporary table is always empty when the function is called. Just as if you declared a local temporary record variable. That’s because the runtime first establishes a new memory space for the by-value parameter.

So, this covers the first two cases. Now for the third case.

If the record parameter is by reference, then the Temporary property makes absolutely no difference. These two are essentially equal:

image

When you pass a variable by reference, you don’t allocate new memory space: you simply pass the whole variable. The Temporary property is then taken from the variable, regardless of what you set as the Temporary property on the parameter itself. If you pass a physical table to a temporary by-reference parameter, the parameter record within the function is physical. Also, if you pass a temporary table to a non-temporary by-reference parameter, the parameter record within the function is temporary.

Not understanding this may have devastating effects on your data. Imagine this:

image

This deletes all of your customers from the physical table (those in the filter, at least). The Temporary property had no effect – you have passed the whole variable including all of its properties, including Temporary.

Therefore, setting Temporary on a by-reference parameter makes absolutely no difference. Never forget this.

Temporary Records and Transactions

Another misconception about temporary records is that they are managed as transactions. They are not. When you call COMMIT, it does not affect your temporary tables. Also, if you call ERROR, it won’t rollback any inserts, changes, or deletions from your temporary tables.

There is not much rocket science around this, really.

SourceTableTemporary

The SourceTableTemporary property on pages (or forms in 5.0 and 2009) allows you to run pages that only work over temporary data. When you run them, they are empty, and then you need to populate them with data. You do that by simply filling in the data in the Rec variable.

One typical problem I’ve frequently seen with pages (or forms) over temporary data, is that the Rec variable is passed by reference, and then iterated over. That’s not the smartest thing in the world. In certain versions of clients, this may cause phantom inserts – if you are positioned on the new row, and then call a function which iterates over a range of rows to do something, and then leaves the row positioned on an actual row.

For example:

image

Let’s set the phantom inserts aside for a while, and just apply a simple trick here that works – and should always be done anyway – with physical tables as well: when entering a function where a record variable is passed by reference, store the position of the current record, and when exiting the function, restore the record to its original position:

image

By Reference – How to Know Temporary from Physical?

And finally, for the tip: when in a function that receives a record variable by reference, how do you know if the record is temporary or physical? You may “know” that your function will always be called with temporary record (or you may set its Temporary property to Yes to “ensure” it) but in fact for by-reference whether it’s temporary or not is determined by the actual variable. So, don’t do something crazy like this and delete your G/L:

image

You know by now that the GLEntryTemp record may – or may not!! – be temporary. Instead of populating the physical G/L Entries into a temporary table, you may end up deleting all of the entries from the actual, physical G/L Entry table. Probably not something you’d want to do on your regular Thursday.

To avoid headache, just do a simple sanity check:

image

You simply create a record reference over the parameter, and then check its ISTEMPORARY property before you do anything crazy with those physical tables.

So – I hope this was useful and helpful. If not, just feel free to boo me publicly here.

7 thoughts on “Some tips and hints about temporary tables”

  1. Some comments

    >”…but it is only stored in memory”
    It start in memory but can be written to a temporary file on disk. If the file gets big (2GB [FAT-inheritance…]), you get an error something like “the file-pointer cannot be moved after the end-of-file”).
    BTW: this is an error on the classic client. I don’t know about NAV2009-RTC or NAV2013.

    >ISTEMPORARY property
    Does NOT exist in NAV2009 without SP or before!
    Does exist starting from NAV2009SP1.

    1. Kriki – thanks for the comment! In fact – yes, it only starts in memory, but when it’s stored to a temporary file, I believe it is a part of the regular memory paging process, not something that NAV specifically does. This is true for everything “in-memory” under Windows. As far as the developer is concerned, there is no difference between physical memory or paging file, as the paging process itself is a part of Windows infrastructure and is fully transparent to the user. Regarding ISTEMPORARY – this was indeed introduced in NAV 2009 SP1. I didn’t explicitly mention that because I am trying to keep this as latest-version as possible. You won’t see me blogging about old-version features here 😉

  2. Thx for the blog post! One additional thought: I have always considered it best practice not to set the TEMPORARY property on a record in a parameter collection at all. If it was physical it is targeted to remain physical, and if it was temporary it is targeted to remain temporary. If you need to transfer from physical to temporary, do so explicitely by writing a function called TransferCustToTempCust (e.g.). In the light of the possible misconceptions you mention, I think that makes all the more sense for the sake of clarity.

    1. @Gary – thanks for the comment. You are right – the practice you mention is indeed a best practice that is applicable in this case, and should be applied for the sake of code clarity and avoiding bugs.

  3. Great post, but you should mention two more issues with temporary records in NAV:

    1) It is possible to have multiple records pointing to the same temporary dataset, by defining the temporary record as an array.

    2) Be very careful calling triggers (i.e. insert/modify/delete(true) and validate ) and functions on temporary tables, as they might update real tables.

Leave a Reply