Executing SQL directly from X++

The X++ language supports a number of ways to execute native SQL against any SQL data source. Below are two samples for retrieving data as well as manipulating data. Please be aware, that SQL can be really powerful, and it should be used as such.

Example #1: Retrieve data:

Since this example uses a Connecion class, data is retrieved from the database where Axapta is currently connected.

void Sample_1(void)

{
Connection Con = new Connection();
Statement Stmt = Con.createStatement();
ResultSet R =Stmt.executeQuery(‘SELECT VALUE FROM SQLSYSTEMVARIABLES’);

while ( R.next() )
{
print R.getString(1);
}
}

Example #2: Manipulating data (deletion):

void Sample_2(void)
{
str sql;
Connection conn;
SqlStatementExecutePermission permission;
;

sql = ‘delete from custTable’;
permission = new SqlStatementExecutePermission(sql);
conn = new Connection();
permission = new SqlStatementExecutePermission(sql);
permission.assert();
conn.createStatement().executeUpdate(sql);
// the permissions needs to be reverted back to original condition.
CodeAccessPermission::revertAssert();
}

Happing Daxing J Enjoy the power of SQL using x++