Friday, May 25, 2012

"Error when executing direct SQL – SqlStatementExecutePermission”


While accessing SQL statements within AX world I came across an error which was really time consuming for me to get rid of.

I created a method like this;

It got failed while executing statement and gave following error " Error when executing direct SQL – SqlStatementExecutePermission” on line resultSet = statement.executeQuery(sql);

SOLUTION:
You can solve this issue easily, if you use the method marked as server static and call this method from the display method. 

Dynamics AX 2012: "Precision lost" warning message

This post is copied from Peter Villadsen  blog and I thought to share it on my blog too. It really helped me a lot.


A question came up at today's webinar where a developer had a (presumably legitimate) reason to cast a real value into an integer value. The X++ language does not allow explicit casting (there's no support for it in the language), but the compiler will do its best to satisfy the user and do the conversion on its own. In this case, however, it issues a warning message, lest this is not what the user wanted.
One solution is to use the anytype type to hold the vaue for conversion and then using the any2int function, as shown below:
static void Job47(Args _args) 
{
real r = 3.13;
int i = r;      // Warning is issued here 
anytype a;

a = r;          // Assign to an anytype variable... 
i = any2int(a); // ... and back into an int 

print r;
print i;
pause;
}
This should be packaged into a function, maybe called int RealToInt(real arg).
Another way would be doing the conversion in managed code (through the System.Convert::ToInt32(object) method), but the performance will not be as good because of the marshalling that needs to take place.
I hope this helps.

Code for Traverse between Tables and all the fields

With reference of Bineet's blog here I found the code for Traverse between tables and all the fields.


//Travese between tables and for each table traverse between all the fields
static void TeeNode_traverse(Args _args)
{
    TreeNode            treeNode, treeNodeTable;
    TreeNodeIterator    iterator;
    void traverseFields(TreeNode _treeNode)
    {
        TreeNode parentNode;
        parentNode = _treeNode.AOTfindChild(‘Fields’);
        //treeNodeTable = _treeNode.AOTfirstChild();
        iterator = parentNode.AOTiterator();
        parentNode = iterator.next();
        while(parentNode)
        {
            //treeNodeTable = treeNodeTable.AOTfirstChild();
            parentNode = iterator.next();
        }
    }
    ;
    treeNode = TreeNode::rootNode();
    treeNode = treeNode.AOTfirstChild();
    treeNodeTable = treeNode.AOTfindChild(‘Tables’);
    treeNodeTable = treeNodeTable.AOTfirstChild();
    while(treeNodeTable)
    {
        traverseFields(treeNodeTable);
        treeNodeTable = treeNodeTable.AOTnextSibling();
    }
}

How to get table name and number of records in each table in AOT

Today, I came across a requirement to find out the name of each table in AOT along with the number of records for each table.
Here is the code to achieve this functionality;


void getTableNameRecordCount(Args _args)
{
    #AOT Name name;
    NumberOf recordCount;
    TreeNode treeNode;
    SysDictTable sysDictTable;
    ;
    treeNode = TreeNode::findNode(#TablesPath);
    treeNode = treeNode.AOTfirstChild();
    while (treeNode)
    {
        name = treeNode.AOTname();
        sysDictTable = SysDictTable::newTableId(treeNode.applObjectId());
        recordCount = sysDictTable.recordCount(false);
        if (recordCount)
        info (strfmt("@SYS26868", name, recordCount));
        treeNode = treeNode.AOTnextSibling();
    }
}

Wednesday, May 2, 2012

CRUD operation in Dynamics AX 2009 through .Net business connector

Friends, rather going into more details about "How to's" of accessing Dynamics AX through .Net, my main objective is to share my experience of performing CRUD operations in Dynamics AX 2009 through .Net BC.

Here is a sample code;


    // CREATE OR INSERT SAMPLE
    // Create the .NET Business Connector objects.
    Axapta ax;
    AxaptaRecord axRecord;
    string tableName = “AddressState”;
    try
    {
        // Login to Microsoft Dynamics AX.
        ax = new Axapta();
        ax.Logon(null, null, null, null);
        // Create a new AddressState table record.
        using (axRecord = ax.CreateAxaptaRecord(tableName));
        {
            // Provide values for each of the AddressState record fields.
            axRecord.set_Field(“NAME”, “MyState”);
            axRecord.set_Field(“STATEID”, “MyState”);
            axRecord.set_Field(“COUNTRYREGIONID”, “US”);
            axRecord.set_Field(“INTRASTATCODE”, “”);
            // Commit the record to the database.
            axRecord.Insert();
        }
    }
    catch (Exception e)
    {
        Console.WriteLine(“Error encountered: {0}”, e.Message);
        // Take other error action as needed.
    }



    // READ SAMPLE
    // Create the .NET Business Connector objects.
    Axapta ax;
    AxaptaRecord axRecord;
    string tableName = “AddressState”;
    // The AddressState field names for calls to
    // the AxRecord.get_field method.
    string strNameField = “NAME”;
    string strStateIdField = “STATEID”;
    // The output variables for calls to the
    // AxRecord.get_Field method.
    object fieldName, fieldStateId;
    try
    {
        // Login to Microsoft Dynamics AX.
        ax = new Axapta();
        ax.Logon(null, null, null, null);
        // Create a query using the AxaptaRecord class
        // for the StateAddress table.
        using (axRecord = ax.CreateAxaptaRecord(tableName));
        {
            // Execute the query on the table.
            axRecord.ExecuteStmt(“select * from %1″);
            // Create output with a title and column headings
            // for the returned records.
            Console.WriteLine(“List of selected records from {0}”, tableName);
            Console.WriteLine(“{0}\t{1}”, strNameField, strStateIdField);
            // Loop through the set of retrieved records.
            while (axRecord.Found)
            {
                // Retrieve the record data for the specified fields.
                fieldName = axRecord.get_Field(strNameField);
                fieldStateId = axRecord.get_Field(strStateIdField);
                // Display the retrieved data.
                Console.WriteLine(fieldName + “\t” + fieldStateId);
                // Advance to the next row.
                axRecord.Next();
            }
        }
    }
    catch (Exception e)
    {
        Console.WriteLine(“Error encountered: {0}”, e.Message);
        // Take other error action as needed.
    }


    // UPDATE SAMPLE
    // Create an Axapta record for the StateAddress table.
    axRecord = ax.CreateAxaptaRecord(tableName);
    // Execute a query to retrieve an editable record where the name is MyState.
    axRecord.ExecuteStmt(“select forupdate * from %1 where %1.Name == ‘MyState’”);
    // If the record is found then update the name.
    if (axRecord.Found)
    {
        // Start a transaction that can be committed.
        ax.TTSBegin();
        axRecord.set_Field(“Name”, “MyStateUpdated”);
        axRecord.Update();
        // Commit the transaction.
        ax.TTSCommit();
    }



    // DELETE SAMPLE
    // Execute a query to retrieve an editable record
    // where the name is MyStateUpdated.
    axRecord.ExecuteStmt(“select forupdate * from %1 where %1.Name == ‘MyStateUpdated’”);
    // If the record is found then delete the record.
    if (axRecord.Found)
    {
        // Start a transaction that can be committed.
        ax.TTSBegin();
        axRecord.Delete();
        // Commit the transaction.
        ax.TTSCommit();
    }




How to reuse table Id in Dynamics AX


Sometime we created tables just for testing purpose within AOT and later on we delete these tables. In such cases we don't care about the IDs which are created for each table. There is a way to use these system generated IDs for other newly created tables. 

You can do this by following steps;

1.     Export the table with its current id.
2.     Delete the table
3.     Edit the xpo file and change the id to what you want
4.     Import the xpo file with the id option checked.  As long as the id is not used by something else, then it should get imported with that id.

Dynamics AX 2009: How to retrieve table properties

Here is the sample code to retrieve table properties in Dynamics AX 2009.


static void TableProperties(Args _args)
{
    Dictionary                      dictionary;
    TableLabel                      tableLabel;
    SysdictTable                    dTable;
    TreeNode                        Tnode;
    ;
   
    //initilize the data dictionary
    dictionary = new Dictionary();
    // Replace _tableId parameter with the actual table id
    dTable = new SysDictTable(_tableId);
    // initilize the tree node to traverse each property of the table
    tNode = dTable.treeNode();
    // Replace "Label" with any property name of the table. In this case I am getting table label and it will return label id
    tNode.AOTgetProperty("Label");
    // Get actual label from label file
    print sysLabel::labelId2String(tNode.AOTgetProperty("Label"));
    pause;
}