Dynamics Ax SQL statements (SQL Strings in DAx)
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
}
{
//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;
;
{
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));
}
}
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.