Skip to main content

Executing direct SQL statements

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();
}

Popular posts from this blog

What does this mean: "The form datasource query object does not support changing its AllowCrossCompany property after the form has executed the query."?

I have made a form with datasources vendtable and vendtrans. Inside vendtable_ds.executequery() looks like this: QueryBuildDataSource queryBuildDatasource ,queryBDS_VendTrans_Invoice; ; queryBuildDatasource = this.query().dataSourceTable(tablenum(vendtable)); queryBDS_VendTrans_Invoice = this.query().dataSourceTable(tablenum(vendtrans)); if (curext() == "MASTERCOMP") { this.query().allowCrossCompany(true); } else { this.query().allowCrossCompany(false); } //FilterVendorName = stringedit control on form if (FilterVendorName.text()) { queryBuildDatasource.addRange(fieldNum(VendTable,Name)).value(strfmt("*%1*", FilterVendorName.text())); } else { queryBuildDatasource.clearRange(fieldNum(VendTable,Name)); } //FilterInvoiceNumber = stringedit control on form if (FilterInvoiceNumber.valueStr() == "") { queryBDS_VendTrans_Invoice.enabled(false); } else { queryBDS_VendTrans_Invoice.enabled(true); queryBDS_VendTrans_In...

Credit Note [Dynamics AX] using X++

This post will help to create credit note for a sales order based on the invent lot id. All the invoices raised for a particular sales line – Lot Id will be raised back as a credit note. Information on Credit Note: A credit note or credit memorandum (memo) is a commercial document issued by a seller to a buyer. The seller usually issues a Credit Memo for the same or lower amount than the invoice, and then repays the money to the buyer or sets it off against a balance due from other transactions Below Code will help to create credit note for all the invoices raised against the sales line -lot id. Please note: This code can be customized as per your requirements. This is just a template to help creating credit note using X++ code. Please test the code before use. static void SR_CreateCreditNote_Sales(Args _args) { // Coded by Sreenath Reddy CustInvoiceTrans custInvoiceTrans; Dialog dialog = new Dialog(“Create credit note – for sales.”); DialogField dfInv...