Direct SQL Statements in X++

Executing direct SQL statements

Hi everyone,Today I want to talk about executing SQL statements in X++ on both the AX database and external databases. This is something probably every AX developer will have to do at some point.

You’ll want to do this for many reasons; to execute stored procedures, to improve performance, to get data from an external database, and so on.

I will provide samples for two classes:
- Connection (Execute SQL statement on current AX database)
- ODBCConnection (Execute SQL statement on external database)

I will not cover the ADO connection (CCADOConnection class), because it doesn’t work when you run it on server (or in batch), and I don’t like that. If you do, try to convince me ;-).

Executing direct SQL on the current AX database

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 a main method, put it in a class. Also note that it has to run on 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();
}

You can find more info about the executeQuery() and executeUpdate() methods on msdn:
Statement Class

Executing direct SQL on an external database using ODBC

Again, we have to differentiate between queries that return a result and those that don’t.

The following code sample retrieves records from an external database and processes the result:

public static server void main(Args _args)
{
    Statement       statement;
    str             query;
    Resultset       resultSet;
    LoginProperty   loginProperty;
    OdbcConnection  odbcConnection;
    ;

    loginProperty = new LoginProperty();
    loginProperty.setDSN('YOURDSN');

    odbcConnection = new OdbcConnection(loginProperty);

    // Create new Statement instance
    statement =odbcConnection.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 by handler
    //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();
}

As you can see, the code is pretty similar. The main difference is that we are using ODBC classes, including the LoginProperty class.

In this example, I use a DSN (Data Source Name) that I configured on the AOS server. The DSN contains a reference to the server and database you want to connect to, and also what user credentials should be used to connect to the database. This is a lot safer than storing them in AX.

If you don’t know how to create a DSN, there are plenty of tutorials on the web.

To update/delete/update, the code is more or less the same:

public static server void main(Args _args)
{
    Statement       statement;
    str             query;
    LoginProperty   loginProperty;
    OdbcConnection  odbcConnection;
    ;

    loginProperty = new LoginProperty();
    loginProperty.setDSN('YOURDSN');

    odbcConnection = new OdbcConnection(loginProperty);

    // Create new Statement instance
    statement =odbcConnection.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();

    // when the query returns result,
    // loop all results for processing by handler
    //BP Deviation documented
    statement.executeUpdate(query);

    // limit the scope of the assert call
    CodeAccessPermission::revertAssert();
}

*** Use the above code in Server Class, it may not throw an error while executing using Jobs.

How to Use Number Sequence Engine Efficiently with Dynamics AX Forms

If you wanted to write a an X++ code to generate a number sequence and assign it to a field, you might use the following X ++ statement.

yourTableBuffer.Field = NumberSeq::newGetNum(NumberSequenceReference::find (TypeID2ExtendedTypeId(TypeId(YourExtendedDataType)))).num();

And for the continuous number sequence (notice the “true” parameter):

yourTableBuffer.Field = NumberSeq::newGetNum(NumberSequenceReference::find (TypeID2ExtendedTypeId(TypeId(YourExtendedDataType))), true).num();

But, what you could do if you have that field in form and you want to generate a number sequence while the user creates a new record is the class NumberSeqFormHandler.

NumberSeqFormHandler is a great class that doesn’t only take care of generating a new number sequence for your field… but also it takes care of removing or inserting that number in the Number Sequence List table when the number is not used (in case that the record hasn’t been inserted although the number has been already generated).

To use the NumberSeqFormHandler class, you have to declare a NumberSeqFormHandler object in the class declaration. Also you have to create a method at the Form level like the following:

//Form level method NumberSeqFormHandler numberSeqFormHandler() {;     //you should have been declared numberSeqFormHandler variable in  //the ClassDeclaration of your form     if (!numberSeqFormHandler)     {         numberSeqFormHandler = NumberSeqFormHandler::newForm(NumberSequenceReference::find (TypeID2ExtendedTypeId(TypeId(YourExtendedDataType))).NumberSequence,                                                              element,                                                              YourDataSourceName,                                                              fieldnum(YourTableName, Field));     }       return numberSeqFormHandler; }

Then you need to actually call the NumberSeqFormHandler class methods like:

//Form methods public void close() {     if (numberSeqFormHandler)     {         numberSeqFormHandler.formMethodClose();     }     super(); }

//DataSource method public void write() {     element.numberSeqFormHandler().formMethodDataSourceWrite();     super(); }

//DataSource method public boolean validateWrite() {     boolean ret;       ret = super();     ret = element.numberSeqFormHandler().formMethodDataSourceValidateWrite(ret)  && ret;       return ret; }

//DataSource method public void linkActive() {     element.numberSeqFormHandler().formMethodDataSourceLinkActive();     super(); }

//DataSource method public void delete() {     element.numberSeqFormHandler().formMethodDataSourceDelete();     super(); }

//DataSource method public void create(boolean _append = false) {     element.numberSeqFormHandler().formMethodDataSourceCreatePre();       super(_append);       element.numberSeqFormHandler().formMethodDataSourceCreate(); }

By this you will have your form works efficiently with the Number Sequence engine of Dynamics AX  and you don’t have to write any code at the table level… so remove all that code that you might have written at the initValue method of your table.

One last important thing, NumberSeqFormHandler works with Continuous and non-Continuous number sequences. But if you want to “regenerate” the unused numbers that have been previously generated, you have to set your number sequence as Continuous of course.