Reply To: Executing direct SQL on the current AX database

Network of MS Dynamics D365, AX, NAV, GP, SL, CRM, RMS, POS professionals Forums Reply To: Executing direct SQL on the current AX database

#12674

Anne Smith
Participant

< ![CDATA[Hi Andrew, When you execute a SQL statement, there are two options: - either you did a select and you expect a result to be returned - or you did insert/update/delete and you don’t expect a result. The first sample is for a SQL statement that returns a result: public static server void main(Args _args) { Connection connection; Statement statement; str query; Resultset resultSet; ; // create connection object connection = new Connection(); // create statement statement = connection.createStatement(); // Set the SQL statement query = 'select name from CustTable'; // assert SQL statement execute permission new SqlStatementExecutePermission(query).assert(); // when the query returns result, // loop all results for processing //BP Deviation documented resultSet = statement.executeQuery(query); while(resultSet.next()) { // do something with the result info(resultSet.getString(1)); } // limit the scope of the assert call CodeAccessPermission::revertAssert(); } Note: this is the main method, put it in a class. Also, note that it has to run on the server.
Now if you do an update/delete/insert, you will want to do something like this:

public static server void main(Args _args)
{
Connection connection;
Statement statement;
str query;
;
// create connection object
connection = new Connection();
// create statement
statement = connection.createStatement();
// Set the SQL statement
query = “insert into CustTable (AccountNum, Name, RecId) values (‘demo’, ‘demo’, 2)”;
// assert SQL statement execute permission
new SqlStatementExecutePermission(query).assert();
//BP Deviation documented
statement.executeUpdate(query);
// limit the scope of the assert call
CodeAccessPermission::revertAssert();
}]]>

Skip to toolbar