Temporary Tables

Table types:

  • Regular – a standard physical table
  • InMemory – the type of temporary table which existed in the previous versions of Dynamics Ax. Such tables are held in memory and written to a local disk file once they grow beyond a certain point
  • TempDB – a new option in Ax 2012. They are “physical” temporary tables held in the SQL Server database.

To create a new instance link from one table instance variable to the other with Temporary type tables:

  • For InMemory tables, by using either the setTmpData() method
  • For TempDB tables, the linkPhysicalTableInstance() method replaces the setTmpData() call.

Heaving following code:

// job or any mehotd on client
static void testTmpTables(Args _args)
{
    TmpRecIdFilter  recIdFilter;
    
    recIdFilter = TestTmpTable::fillInFilter();
    //recIdFilter.linkPhysicalTableInstance(TestTmpTable::fillInFilter());
    
    while select recIdFilter
    {
        abs(1);
    }
    
}
// method on server
public static server TmpRecIdFilter fillInFilter()
{
    TmpRecIdFilter  recIdFilter;
    
    recIdFilter.RefRecId = 1;
    recIdFilter.insert();
    
    return recIdFilter;
}

The code above will not work, you wont be able to select any record. recIdFilter variable goes out of scope in server static method and as a result all temporary data is deleted.

Uncommenting the line with linkPhysicalTableInstance will give following error:

Cannot execute the required database operation.
The method is only applicable to TempDB table variables that are not linked to existing physical table instance

However example above will work if you do not change tier. client - client or server - server.

Here is an modified example of how you can transfer temporary table link between tiers:

static void testTmpTables(Args _args)
{
    TmpRecIdFilter  recIdFilter;
    
    TestTmpTable::fillInFilter(recIdFilter);
    
    while select recIdFilter
    {
        abs(1);
    }
    
}
public static server void fillInFilter(TmpRecIdFilter  _recIdFilter)
{
    _recIdFilter.RefRecId = 1;
    _recIdFilter.insert();
}
Error: The TempDB table variable can only be linked to another TempDB table variable of the same table type.
If you are in context of SSRS reports:
InventOnhandTmp             inventOnhandTmpUpd;
this.takeOwnershipOfTempTable(inventOnhandTmpUpd);

inventOnhandTmpUpd.linkPhysicalTableInstance(inventOnhandTmp);
Where inventOnhandTmpUpd is new cursor which should be linked with table inventOnhandTmp, which has data in it.
SrsReportDataProviderPreProcessTempDB.takeOwnershipOfTempTable:

        /// <summary>
    /// Takes ownership of the given temp table.
    /// </summary>
    /// <param name="_tempDbTable">
    /// The table to take ownership of.
    /// </param>
    public void takeOwnershipOfTempTable(Common _tempDbTable)
    {
        DictTable dictTable;
        TableId tableId;
        #SRSFramework

        if (!this.parmUseDefaultTransactionOnly())
        {
           _tempDbTable.setConnection(this.parmUserConnection());
        }

        tableId = _tempDbTable.TableId;
        dictTable = new DictTable(tableId);
        if(!dictTable)
        {
            throw error(strFmt("@SYS4007193", tableId2name(tableId)));
        }

        new ExecutePermission().assert();
        dictTable.callObject(#TempDBMethodName_TakeOwnership, _tempDbTable, true);
        CodeAccessPermission::revertAssert();
    }




 

Search

About

DaxOnline.org is free platform that allows you to quickly store and reuse snippets, notes, articles related to Dynamics AX.

Authors are allowed to set their own AdSense units.
Join us.

Blog Tags