Run SQL directly from X++

Below is the way to run SQL query directly from AX X++.

To 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);
      }
}

Manipulating data (deletion/updation/ selection):

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

         sql =         "SELECT custinvoicejour.invoiceid,custinvoicejour.SALESID,custinvoicejour.DATAAREAID,custinvoicejour.INVOICEAMOUNT, CUSTTABLE.ACCOUNTNUM, " +
            "custinvoicejour.InvoiceDate,CUSTTABLE.SUNTAFFORECASTCHANNELID "+
            "FROM custinvoicejour inner join CUSTTABLE "+
    "ON custinvoicejour.INVOICEACCOUNT = CUSTTABLE.ACCOUNTNUM ";

         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();
}

@Rahul