An Afternoon Stroll with Temp Tables in AX 2009
How to initialize and transfer temp tables in AX 2009
In Dynamics AX 2009 there are two ways to create and initialize a temporary table:
1) Create new table and set Temporary property to Yes:
2) Call the setTmp() method in X++ code for any existing table in AOT:
Temporary tables store data the same way as regular tables, except that they keep data only when the object exists during code execution. In other words, if the object is finalized, then all data from the temp tables will be dropped.
There are two common situations where temp tables can be applied:
- As a Datasource for a form or report, where the original data is too complex to be easily queried.
- As temporary storage during complicated processing, to hold the results midway through the process.
Temporary tables can be defined by the following ways:
- Set the table’s Temporary property to Yes.
- Call the setTmp method in X++ code.
A temporary table is held in memory until its size reaches 128 KB. The dataset is then written to a disk file. The disk file for a temporary table has the naming convention $tmp<nnnnnnnn>.$$$.
Best practices requires to infix temporary table names with Tmp. This improves readability in code.
Here is an example of how to declare, initialize and use temp tables on the fly based on already existing CustTable:
static void testInitTmp(Args _args)
{
CustTable custTable;
CustTable custTableTmp;
Counter totalRec;
;
//The setTmp method is used to create a temporary table based on CustTable
custTableTmp.setTmp();
custTableTmp.recordLevelSecurity(true);
//Show all records in CustTable filtered by CustGroup
while select custTable where custTable.CustGroup == “Test”
{
info(custTable.AccountNum);
}
//Show number of all records in CustTable
info (strfmt(“Total records %1”, (select firstonly count(RecId) from CustTable).RecId));
// Insert a subset of custTable records based on the filter on CustGroup
while select custTable where custTable.CustGroup == “Test”
{
custTableTmp.data(custTable.data());
custTableTmp.doInsert();
}
//Show all records in temporary table.
while select custTableTmp
{
info (custTableTmp.AccountNum);
}
//Find all records in temporary table
select firstonly count(RecId) from custTableTmp;
totalRec = any2int(custTableTmp.RecId);
info (strfmt(“Total records %1”, totalRec));
}
Please note that calling insert() method on temporary table executes logic on standard CustTable.insert(), so in that case I would recommend to use CustTableTmp.doInsert();
Result:
We have two records of CustGroup: Test in CustTable (Total records 39). All these two records from custGroup: Test were successfully copied in CustTableTmp, so now it has only 2 records.
AOT temporary table:
static void TableTmpInsertRecord(Args _args)
{
TestTableTmp testTableTmp
;
testTableTmp.AccountNum = “1000”;
testTableTmp.Name = “Name”;
testTableTmp.Group = “10”;
testTableTmp.insert();
testTableTmp.AccountNum = “2000”;
testTableTmp.Name = “Name2”;
testTableTmp.Group = “20”;
testTableTmp.insert();
while select testTableTmp
{
info(strfmt(“%1, %2, %3”, testTableTmp.AccountNum, testTableTmp.Name, testTableTmp.Group));
}
}
Result:
Using Temporary tables in different classes
Let’s talk about another common issue, when we need to initialize temporary table and pass it between different classes. In this particular case we will create a new class: ImportDataTmp to initialize data in the ImportTmp table and then pass and display it in our job: ImportTmpClass
Class:
public class ImportDataTmp
{
TestTableTmp testTableTmp;
}
public void importData()
{
;
testTableTmp.AccountNum = “1000”;
testTableTmp.Name = “Name”;
testTableTmp.Group = “10”;
testTableTmp.insert();
testTableTmp.AccountNum = “2000”;
testTableTmp.Name = “Name2”;
testTableTmp.Group = “20”;
testTableTmp.insert();
}
public testTableTmp parmTestTableTmp (TestTableTmp _testTableTmp = testTableTmp)
{
;
testTableTmp = _ testTableTmp;
return _ testTableTmp;
}
void showAllRecords()
{
while select testTableTmp
{
info(strfmt(“%1, %2, %3”, testTableTmp.AccountNum, testTableTmp.Name, testTableTmp.Group));
}
}
Job:
static void importTmpClass(Args _args)
{
ImportDataTmp importDataTmp;
TestTableTmp testTableTmp;
;
importDataTmp = new ImportDataTmp();
importDataTmp.importData();
testTableTmp = importDataTmp. parmTestTableTmp ();
importDataTmp.showAllRecords();
}
Result:
In this example we created two records in our temp table during our class execution and returned reference of this temporary table directly to the job. Please note that when you pass a temporary table into a method call, it is passed by reference. In other words, we don’t make a new copy of the importTmp table.
Let’s consider an example to demonstrate such behavior:
static void importTmpClass(Args _args)
{
ImportDataTmp importDataTmp;
ImportTmp importTmp;
;
importDataTmp = new ImportDataTmp();
importDataTmp.importData(); //Initialize temporary table with data
importTmp = importDataTmp.parmImportTmp(); //Pass reference
//Create new record
importTmp.AccountNum = “3000”;
importTmp.Name = “Name3”;
importTmp.Group = “30”;
importTmp.insert();
//Show all records in the temp table
importDataTmp.showAllRecords();
}
New record has been created in the job, and you can see it from the class:
Let’s summarize: when a variable is passed by a reference, only a pointer to the object is passed to the method. When a variable is passed by value, a new copy of the variable is passed to the method (Ex: Integer, String, Container, etc)
How to create a copy of a temporary table.
If we do something like this:
TalbeTmp2 = TableTmp1
Then all it does is creating a reference to the TableTmp1 table.
Let’s consider an example:
static void copyTmpTable(Args _args)
{
ImportTmp originalTmp;
ImportTmp duplicateTmp;
;
originalTmp.AccountNum = “1000”;
originalTmp.Name = “Name”;
originalTmp.Group = “10”;
originalTmp.insert();
originalTmp.AccountNum = “2000”;
originalTmp.Name = “Name2”;
originalTmp.Group = “20”;
originalTmp.insert();
duplicateTmp = originalTmp;
while select originalTmp
{
info(strfmt(“Original: %1, %2, %3”, originalTmp.AccountNum, originalTmp.Name, originalTmp.Group));
}
while select duplicateTmp
{
info(strfmt(“Duplicates: %1, %2, %3”, duplicateTmp.AccountNum, duplicateTmp.Name, duplicateTmp.Group));
}
}}
Result:
It looks like both tables have the same data, however, as mentioned above, duplicateTmp table is just a reference to originalTmp table. Therefore, if you drop data from the original temp table, the second one won’t show any data. This is demonstrated in the next example:
static void copyTmpTable(Args _args)
{
ImportTmp originalTmp;
ImportTmp duplicateTmp;
;
originalTmp.AccountNum = “1000”;
originalTmp.Name = “Name”;
originalTmp.Group = “10”;
originalTmp.insert();
originalTmp.AccountNum = “2000”;
originalTmp.Name = “Name2”;
originalTmp.Group = “20”;
originalTmp.insert();
duplicateTmp = originalTmp;
while select originalTmp
{
info(strfmt(“Original: %1, %2, %3”, originalTmp.AccountNum, originalTmp.Name, originalTmp.Group));
}
//drop data
originalTmp = null;
while select duplicateTmp
{
info(strfmt(“Duplicates: %1, %2, %3”, duplicateTmp.AccountNum, duplicateTmp.Name, duplicateTmp.Group));
}
}
Result:
This is what happened.
- We declared two temp tables: OriginalTmp and DuplicateTmp.
- Inserted two records in the OriginalTmp, so the system allocated memory for those two records and set pointer to OriginalTmp.
- Then copied a reference for the allocated records in memory to the duplicateTmp. So now the two variables represent the reference to the same allocated memory.
- Looping through records of the originalTmp we see records in that temp table.
- Now we delete these records from that originalTmp table, i.e. we delete those records from memory.
- Finally, looping through the duplicateTmp table shows no records.
Note: if we want to create a new copy of originalTmp, then we need to make sure that each record is inserted into duplicateTmp table separately:
static void copyTmpTable(Args _args)
{
ImportTmp originalTmp;
ImportTmp duplicateTmp;
;
originalTmp.AccountNum = “1000”;
originalTmp.Name = “Name”;
originalTmp.Group = “10”;
originalTmp.insert();
buf2buf(originalTmp, duplicateTmp);
duplicateTmp.insert();
originalTmp.AccountNum = “2000”;
originalTmp.Name = “Name2”;
originalTmp.Group = “20”;
originalTmp.insert();
buf2buf(originalTmp, duplicateTmp);
duplicateTmp.insert();
while select originalTmp
{
info(strfmt(“Original: %1, %2, %3”, originalTmp.AccountNum, originalTmp.Name, originalTmp.Group));
}
delete_from originalTmp;
while select duplicateTmp
{
info(strfmt(“Duplicates: %1, %2, %3”, duplicateTmp.AccountNum, duplicateTmp.Name, duplicateTmp.Group));
}
}
Using temporary tables in X++ is a powerful and useful tool, but since data in such tables is saved in memory only (or disk), you need to be very careful while using it to avoid unforeseen results.
Prior to Dynamics AX 2012, only one type of temporary table was available. In AX 2012, however, the Temporary property on tables was replaced with a new property: TableType, which has three possible values:
- Regular – a standard physical table
- InMemory – the type of temporary table which existed in the previous versions of Dynamics AX.
- TempDB – a new option in AX 2012. They are “physical” temporary tables held in the SQL Server database, but this is a topic for another discussion.