sql query in ax

create a connection using connection object, don't need to assign any parameter here, this will help to run query. Create an statement object and assign a query in an execute method of it. Before that you need to create an SQlStatementExecutePermission that will allow to use SQL in a X++, then call assert that declares the calling code to invoke an API that is protected by a permission. after execute query a resultset is generated that you can access via .get(Datatype) method.

example is given below.



Connection con = new Connection();
Statement stmt = con.createStatement();
str sqlPassStr1;
ResultSet sqlres;
SqlStatementExecutePermission sqlStatementExecutePermission;
;

sqlPassStr1 = 'select ACCOUNTNUM, CUSTGROUP from CustTable';

sqlStatementExecutePermission = new SqlStatementExecutePermission(sqlPassStr1);
sqlStatementExecutePermission.assert();

sqlres = stmt.executeQuery( sqlPassStr1);
while(sqlres.next())
{
info(strfmt('%1 %2', sqlres.getString(1), sqlres.getString(2)));
}