gatesasbait: Dynamics Ax SQL statements (SQL Strings in DAx)

[Blog bot]

Источник: http://gatesasbait.spaces.live.com/B...B9F5!206.entry
==============


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 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()).

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 * ';
    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.


Источник: http://gatesasbait.spaces.live.com/B...B9F5!206.entry