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
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
Thanks for sharing the info, keep up the good work going.... I really enjoyed exploring your site. good resource...
ReplyDeletepożyczka przez internet na konto
Will this work on Production ?
ReplyDeletedoes this work for insert into table?
ReplyDelete