page contents

Interacting with IL-generated TempDB data


Together with Microsoft Dynamics AX 2012, there was a new execution mode introduced; called as "IL" execution mode, which is in some references also referred as "CIL" execution mode.
The "IL" stands for ".Net Intermediate Language” and referring to the Intermediate Language available in the .Net Technology, see also X++ Compiled to .NET CIL and X++ Scenarios that are Not Supported in CIL for more information on this topic.

In general, the purpose of this new execution mode is to use as much as possible advantages of the performance improvements that .Net IL-execution can bring to the Microsoft Dynamics AX 2012.

Scenario
While we can write for several pages about the IL-execution mode, this post will only focus about one practical question, which in some given scenarios can be useful to know, specially when you are busy with the performance optimization of your X++ code.
Here is the question:

“Suppose you use IL-execution mode to populate some data in a tempDB-based temporary table,
How would you access this data back from the X++ context? (i.e. to visualize in a form)”

Demo
Well, you might get to know the answer in this post step by step.
First of all, there are a couple of important facts to know:

  • The tempDB temporary tables in Microsoft Dynamics AX 2012 are being created under the normal tables node in the tempDB;
  • You may retrieve the name of the physical table which keeps the data of your temp table instance in the tempDB, using the following method getPhysicalTableName();
  • You may reuse an existing tempDB temporary table instance by calling useExistingTempDBTable(…);

This all together will form the ingredients of our sample code to demo this case.

The following shows how you would link to an existing tempDB temporary table:

// Sample code which shows how to link to an existing tempDB temporary table
public static server container runStatic(container c)
{
container ret;
TmpTempDBTable tmpTable1;
int i;
str tempTableName = conPeek(c, 1);

// Link to an exiting table
tmpTable1.useExistingTempDBTable(tempTableName);

// Generate some data in the temp table
for(i=2;i<=conLen(c);i++)
{
tmpTable1.ID = strFmt("Value %1", conPeek(c, i));
tmpTable1.insert();
}

// send the physical table name back to be used by the caller method
ret = [tmpTable1.getPhysicalTableName()];

return ret;
}

And finally the following shows how to pass the reference name of an existing tempDB temporary table to the IL session or i.e. fetch data from an existing tempDB temporary table which is populated in an IL session:

// NOTE: It is important to know that Xpp IL Execution is only allowed on the Server side
public static server void runInIL()
{
TmpTempDBTable tmpTable1, tmpTable1_filled;
str tempTableName;
container data;
container res;

XppILExecutePermission xppILExecutePermission = new XppILExecutePermission();
xppILExecutePermission.assert();

// Initialize the physical table
tmpTable1 = null;
select firstOnly tmpTable1;

// prepare the parameters to pass to the IL-session
data = [tmpTable1.getPhysicalTableName(), 1,2,3];

// go IL
res = SysDictClass::invokeStaticMethodIL(classStr(Tst_TempDB_IL_Interaction),
staticMethodStr(Tst_TempDB_IL_Interaction, runStatic),
data, true);

[tempTableName] = res;

// process results
// Link to the existing tempDB table

tmpTable1_filled.useExistingTempDBTable(tempTableName);
while select tmpTable1_filled
{
info(strFmt("%1", tmpTable1_filled.ID));
}

CodeAccessPermission::revertAssert();
}

Downloads
Sample code - Interacting with IL-generated TempDB data

Related topics
Temporary TempDB Tables: https://msdn.microsoft.com/en-us/library/gg845661.aspx


/*

DISCLAIMER:

Microsoft provides programming examples for illustration only,
without warranty either expressed or implied, including, but not limited to,
the implied warranties of merchantability or fitness for a particular purpose.

This post assumes that you are familiar with the programming language that is being demonstrated
and the tools that are used to create and debug procedures.

*/

Skip to main content