Good afternoon,

 

Yesterday I hit a snag, took me a few hours to figure out (!). I had tinkered with SQL strings in ReleaseUpdate classes often before, tweaking them, rewriting them for optimization etc… but I don’t recall ever writing up a new class that needed to run SQL Statements as strings. Yesterday I needed to do that because a customer wanted a report that would display some data accross all companies in an instance. Using the ‘changecompany’ keyword proved to be heavily inefficient since all data was in the same table and I only had to group by fields accros companies on the one table.

  

After fighting for a few minutes trying to fool QueryBuildRanges to span multiple DataAreaIds, or trying to change the company on a QueryBuildDataSource at runtime, I decided to use an SQL string. But then I kept getting the error "Request for the permission of type ‘SqlStatementExecutePermission’ failed."…

 

The issue I had is that the calling context for SQL Statement execution has to be RUN ON SERVER. The solution was to create a new class, a main() and a menuitem running it. Here is a sample of a class executing an SQL statement (obviously not my customer’s!). It returns the customer Id for all customers accross all DataAreaIds (companies) on a database instance; whereas a standard select only returns the customers for the current company (curext()).

 

In short, any operation directly accessing an SQL Database must be called from a Static Server context. Static because you need to start another thread on another machine and Server for security reasons (this is only true for 3-tier installations of Ax 2.5, 3.0, and it is true for all of Dynamics Ax 4.0 installations).

 

public class MySQLTest
{
    //AOSRunMode::Server
}

 

public static server void main(Args args)
{
    Connection  connection = new Connection();
    Statement   stmt = connection.createStatement();
    SqlSystem   sqlSystem = new SqlSystem();
    str         sqlStmt;
    int         i;
    ResultSet   resultSet;
    SqlStatementExecutePermission   sqlStatementExecutePermission;
    ;

 

    sqlStmt =  ‘SELECT CUSTACCOUNT ‘;   

    sqlStmt += ‘FROM CUSTTABLE’;

    sqlStatementExecutePermission = new SqlStatementExecutePermission(sqlStmt);
    sqlStatementExecutePermission.assert();
 
    resultSet = stmt.executeQuery(sqlStmt);
 
    while (resultSet.next())
    {
        info(resultSet.getString(1));
    }
}

 

The //AOSRunMode::Server marker is only for the BP checker. You can either specify ‘server’ on the static method declaration or as a property on the calling menuitem.

 

Of course, in Ax2009, that would never have been an issue, since I’d have had the crosscompany keyword… hint hint.