Issue with % char in Ax BC.NET query

February 2, 2010

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!


Debug .NET Framework Assemblies

January 17, 2008

A little while ago Scott Guthrie has announced that .NET Framework Library Source Code is now available! This is great news, as I’m sure many (including me :)) will benefit from viewing and debugging the source code. Shawn Burke has also written a comprehensive walk through on how to configure Visual Studio to enable debugging.

Too bad it’s with Visual Studio 2008 only though, it would be nice to have this available at my workplace 🙂


Assign WSS Document Parser to specific Extension

December 30, 2007

The other day I was working with some excel documents within SharePoint 3.0 and I’ve noticed the excel template files do OLE properties do not get promoted into SPListItem properties. After some digging around I’ve found that the excel template extension *.XLT is not assigned to the default SharePoint office document parser.

The solution was somewhat harder to find, but finally I found this on the net:

SPFarm farm = SPFarm.Local;

SPWebService service = farm.Services.GetValue<SPWebService>();

SPDocumentParser customParser = new SPDocumentParser(“SharePoint.SPDocumentParser.OfficeParser”, “xlt”);

service.PluggableParsers.Add(“xlt”, customParser);

service.Update();

The *.XLT extension assignmentto the OfficParser is done at farm level. After running this code, all excel template files uploaded to SharePoint get their OLE properties promoted into SPListItem properties bag, as described in the WSS SDK custom document parsers section.

This solved the *.XLT problem, but shortly another one was revealed: the OfficeParser seems to promote only string type OLE properties, and OLE fields with float and DateTime types are ignored. I hope to get back to this problem when I’ll know more.