Microsoft Dynamics AX gives the leverage to developers in terms of writing and using SQL SELECT statements with almost all of its functions. A developer can write simple or complex SELECT statements in AX for the DB objects like Tables, Views etc if they are in Microsoft Dynamics Application Object Tree (AOT) with almost same syntax as in traditional SQL.

But what if one has to run SQL statements for the objects (Tables, Views) not in AOT or want to execute database stored procedures for example. Dynamics AX framework provides a set of classes for this purpose.

This blog is intended to cover the scenario to execute pure SQL queries and stored procedures in Dynamics AX.

Following is the list of classes which we are going to use in our sample code.

OdbcConnection: Establishes the connection with the database using ODBC (Open database Connectivity)

LoginProperty: Used to set logon properties to be passed to an instance of OdbcConnection class.        

SysSQLSystemInfo: Used to get the information about the database.

SqlStatementExecutePermission: This extends CodeAccessPermission class and is used to control the execution of SQL statement by checking for the appropriate permission.

Statement: It executes a static SQL statement and obtains the results it produces.

ResultSet: It provides access to a table of data generated by executing a Statement.

 

Below is the code:

 

OdbcConnection     odbcConnection;

LoginProperty       loginProperty;

SysSQLSystemInfo   sysSQLSystemInfo;

Statement           statement;

str                 sqlStatement;

Resultset           resultSet;

 

loginProperty          = new LoginProperty();

sysSQLSystemInfo       = SysSQLSystemInfo::construct();

loginProperty.setServer(sysSQLSystemInfo.getLoginServer());

loginProperty.setDatabase(sysSQLSystemInfo.getloginDatabase());

odbcConnection = new OdbcConnection(loginProperty);

// Create Statement object

statement = odbcConnection.CreateStatement();

 

Above is the basic code to declare and initialize classes. Now, use the following code to execute SELECT statement.

 

// Set the SQL statement

sqlStatement = “select * from TableName“;

// Check for permission to use SQL statement

new SqlStatementExecutePermission(sqlStatement).assert();

// Get the query result in resultSet

resultSet = statement.executeQuery(sqlStatement);

while(resultSet.next())

{

print resultSet.getString(1);

}

// End the scope of the assert call

CodeAccessPermission::revertAssert();

 

If you want to run stored procedure, use below code:

 

sqlStatement = “exec StoredProcedureName“;

// Check for permission to use SQL statement

new SqlStatementExecutePermission(sqlStatement).assert();

// Execute the statement

statement.executeQuery(sqlStatement);

// End the scope of the assert call

CodeAccessPermission::revertAssert();

Leave a Reply

Recent Comments

    Archives

    Categories