Issue with % char in Ax BC.NET query

Issue: If you’ve used Microsoft Dynamics Ax Business Connector .NET long enough, you might have stumbled upon an issue when trying to execute direct X++ statements that contained % char. For example, the following code :   

try
{
    using (Axapta ax = new Axapta())
    {
        ax.Logon(null, null, null, null);
        using (AxaptaRecord record = ax.CreateAxaptaRecord("TaxTable"))
        {
            record.ExecuteStmt("SELECT * FROM %1 WHERE %1.TaxCode=='25%'");
            //The following also throws exception
            //record.ExecuteStmt("/* Comment % */ SELECT * FROM %1 WHERE %1.TaxCode=='Normal'");

            Console.WriteLine("TaxCode: {0}", record.get_Field("TaxCode"));
            Console.WriteLine("TaxName: {0}", record.get_Field("TaxName"));
        }
        ax.Logoff();
    }
}
catch (Exception e)
{
    Console.WriteLine(e.ToString());
}

throws the following exception in Ax2009:   

System.ArgumentException: The supplied method arguments are not valid.
   at Microsoft.Dynamics.BusinessConnectorNet.Axapta.ExecuteStmt(String statement, AxaptaRecord[] paramList)
   at Microsoft.Dynamics.BusinessConnectorNet.AxaptaRecord.ExecuteStmt(String statement) 
  

It seems BC.NET does not allow ANY % chars in direct X++ statements, even if they are within string literals or comments. At first this seems as just a matter of avoiding using % chars in any primary keys that you might have to query, but what if you have an existing data set and do not have the luxury of choosing? Besides, there must be some way to workaround this, right? Indeed – there are actually several workarounds.   

Workaround 1: Use X++ Query classes. To explain what we need to do first we need to figure out the X++ code that will do the job. For a detailed explanation of the classes used see this MSDN reference. Here is the code:   

 
static void QueryJob()
{
    Query q;
    QueryRun qr;
    QueryBuildDataSource qbd;
    QueryBuildRange qbr;
    TaxTable tt;  

    q = new Query();  

    qbd = q.addDataSource(TableNum(TaxTable));  

    qbr = qbd.addRange(FieldNum(TaxTable, TaxCode));
    qbr.value('25%');  

    qr = new QueryRun(q);  

    while (qr.next())
    {
        tt = qr.Get(tableNum(TaxTable));
        info(tt.TaxName);
    }
}  

Once we have this it’s easy to get the equivalent C# code:   

using (var ax = new Axapta())
{
    ax.Logon(null, null, null, null);
    int tableId = ax.GetTableId("TaxTable");
    var query = ax.CreateAxaptaObject("Query");
    var qbd = (AxaptaObject)query.Call("addDataSource", tableId);
    var qbr = (AxaptaObject)qbd.Call("addRange", ax.GetFieldId(tableId, "TaxCode"));
    qbr.Call("value", "25%"); 

    var qr = ax.CreateAxaptaObject("QueryRun", query); 

    while ((bool)qr.Call("next"))
    {
        var record = (AxaptaRecord)qr.Call("Get", tableId); 

        Console.WriteLine("TaxCode: {0}", record.get_Field("TaxCode"));
        Console.WriteLine("TaxName: {0}", record.get_Field("TaxName"));
    }
    ax.Logoff();
} 

You will also need to following Axapta extension methods:

public static class AxaptaExtensions
{
    public static int GetFieldId(this Axapta ax, int tableId, string fieldName)
    {
        using (var dict = ax.CreateAxaptaObject("DictTable", tableId))
        {
            return (int)dict.Call("fieldName2Id", fieldName);
        }
    } 

    public static int GetTableId(this Axapta ax, string table)
    {
        return (int)ax.CallStaticClassMethod("Global", "tableName2Id", table);
    }
}

Workaround 2: Use table methods to return the records we are interested in (find methods)   

You will usually see ‘find’ methods on quite a few tables, but there is a caveat – there is no guarantee the table find method provides an option to select a record with forupdate option – and this is precisely the case with TaxTable. The result is that we will not be able to update any records returned by TaxTable.find. It is a convenient practice to provide the forupdate parameter (for example see CustTable.find method), but this practice is not enforced.   

 
using (Axapta ax = new Axapta())
{
    ax.Logon(null, null, null, null);
    using (AxaptaRecord record = (AxaptaRecord)ax.CallStaticRecordMethod("TaxTable", "find", "25%"))
    {
        Console.WriteLine("TaxCode: {0}", record.get_Field("TaxCode"));
        Console.WriteLine("TaxName: {0}", record.get_Field("TaxName"));
    }
    ax.Logoff();
} 

Hope this saves you a couple of hours and/or a headache!

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: