WinAPI::getTempFilename() in .NET Business Connector on a x64 machine

A call on the external method GetTempFileNameW on Kernel32 will raise an exception when called from the .NET Business Connector which is running on a x64 machine.

This problem applies at least in AX 2009 (may be on AX 4.0 and AX 3.0 too).

As a workaround you can use code such this:

// returns a filename in the temporary directory
fileName = System.IO.Path::GetTempFileName();

or

fileName = CLRInterop
    ::getAnytypeForObject(System.IO.Path::GetTempPath())
    + curuserid() + int642str(CLRInterop
        ::getAnyTypeForObject(netTime.get_Ticks());

However don't forget to delete the temporary file after, since it makes no sense to let garbage alive.

Update_recordset and arrays from joint tables

The database modification command update_recordset gives you nice possibility to modify data in a fast way.

Unfortunately this kernel command a some little bug if you are using an array element of a joint table for value assignment. The follow update_recordset will be partial ignored, specific from the assignement from the array element custTableRead.Dimension[2]. This assignment and any following field value assignment will be left out (so the assignment of NameAlias with the value "any Alias" will just fail too):

CustTable    custTableUpdate;
CustTable    custTableRead;
;

ttsbegin;

update_recordset custTableUpdate 
    setting Street = 
        custTableRead.Street, 
    Name = custTableRead
        .Dimension[2], 
    NameAlias = "any Alias"
        where custTableUpdate
            .AccountNum 
            == "00000001"
        join custTableRead
            where custTableRead
                .AccountNum 
                == "00000002";

info(strfmt("%1", custTableUpdate
    .RowCount()));

select firstonly custTableUpdate 
    where custTableUpdate.AccountNum
        == "00000001";

// works fine so far
info(custTableUpdate.Street); 


// you didn't get what you expect
info(custTableUpdate.Name);

// you didn't get what you expect
info(custTableUpdate.NameAlias); 
ttscommit;

But all works fine as long as you code it without any array field:

update_recordset custTableUpdate 
    setting Name = custTableRead.Name, 
        NameAlias = "any Alias"
        where custTableUpdate.AccountNum 
            == "00000001"
        join custTableRead
            where custTableRead.AccountNum 
                == "00000002";

Also works fine too, if the array field is hosted by the same table as the table which is selected for update.

update_recordset custTableUpdate 
    setting Name = custTableUpdate
        .Dimension[2], 
        Street = custTableRead
        .Street
        where 
            custTableUpdate
                .AccountNum
            == "00000001"
        join custTableRead
            where custTableRead
                .AccountNum
            == "00000002";

To make a assignment from custTableRead.Dimension[2] to custTableUpdate.Name to work, you have to do it the old fashion way: select first, then update. Well, the execution of this code is slower, but it works (and that's prior in my eyes :).

ttsbegin;

select firstonly Dimension 
    from custTableRead
    where custTableRead.AccountNum
    == "00000002"
    join forupdate custTableUpdate
        where custTableUpdate
        .AccountNum == "00000001";
        
custTableUpdate.Name = custTableRead
    .Dimension[2];
custTableUpdate.update();
        
ttscommit;

This behaviour relates to AX 2009 only and (if not fixed in the meantime) later versions, since the ability to use joins in update_recordset was invented in version AX 2009.

Select Group by and Join Order By

Take care if you mix Group By and Order By in select statements. Queries like that will bring you data from the Data Base, but you loose data; Data retrieved form tables in Order By mode will not be available.

The code example below retrieves the desired due dates from table CustTransOpen, but the vouchers from table CustTrans are missing:

CustTable custTable;
CustTrans custTrans;
CustTransOpen custTransOpen;
;

while select CustGroup from custtable 
    group by CustGroup // group by
    join Voucher 
    from custTrans // order by
         where custTrans.AccountNum 
             == custtable.AccountNum
         join DueDate from custTransOpen 
             group by DueDate // group by
             where custTransOpen.RefRecId 
                 == custTrans.RecId
{
    info(custTable.CustGroup); // works
    info(custTrans.Voucher); // works not
    info(Date2StrUsr(custTransOpen
        .DueDate)); // works
}

Add a 'group by' to CustTrans, and you get CustTrans data too:

CustTable custTable;
CustTrans custTrans;
CustTransOpen custTransOpen;
;

while select CustGroup from custtable 
    group by CustGroup // group by
    join Voucher, RecId from custTrans 
        group by Voucher, RecId // group by
        where custTrans.AccountNum 
            == custtable.AccountNum
        join DueDate from custTransOpen 
            group by DueDate // group by
            where custTransOpen.RefRecId 
                == custTrans.RecId
{
    info(custTable.CustGroup); // works
    info(custTrans.Voucher); // works now
    info(Date2StrUsr(custTransOpen
        .DueDate)); // works
}

This issue applies to queries proceeded with a QueryRun object too.

Still not clear, if the source of this behaviour is driven by AX or by the SQL engine. But fact is, the mix of Group By and Order By raises hard finding bugs.