Translate

Monday, August 8, 2011

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.


1 comment:

  1. I have a class using a direct SQL statement and I'm able to run it from a Menu Item. How can I use it in a form or report?

    ReplyDelete