This is not a recommended approach to run query against AX, but sometimes there's a need for it (Eg. stored procedure), below is the sample code to run SQL statement directly to the AX database (you'll need to do manual security check due to this approach will skip AX security validation).
I usually has a class with 2 simple method:
- executeUpdate
Used to execute INSERT, UPDATE, DELETE statement - executeQuery
Used to execute SELECT statement
//Class :: SQLUtility ==========================================
class SQLUtility
{
}
//Method :: executeUpdate ======================================
server static int executeUpdate(str sql, Connection connection = new Connection())
{
SqlStatementExecutePermission sqlPerm;
Statement statement;
int rowsAffected;
;
if(sql)
{
sqlPerm = new SqlStatementExecutePermission(sql);
sqlPerm.assert();
statement = connection.createStatement();
rowsAffected = statement.executeUpdate(sql);
CodeAccessPermission::revertAssert();
}
return rowsAffected;
}
//Method :: executeQuery =======================================
server static ResultSet executeQuery(str sql, Connection connection = new Connection())
{
SqlStatementExecutePermission sqlPerm;
Statement statement;
ResultSet resultSet;
;
sqlPerm = new SqlStatementExecutePermission(sql);
sqlPerm.assert();
statement = connection.createStatement();
resultSet = statement.executeQuery(sql);
CodeAccessPermission::revertAssert();
return resultSet;
}
//Job :: Job_DirectSQLTest =====================================
static void Job_DirectSQLTest(Args _args)
{
ResultSet testRS;
;
testRS = SQLUtility::executeQuery("select top 10 ItemId from InventTable");
while(testRS.next())
{
info(testRS.getString(1));
}
}
 |
| Result of running the job " Job_DirectSQLTest " |
