Get external data by ODBC from Dynamics AX 4.0 and X++

A number of good blog entries have discussed how to pull information from other databases into Dynamics AX using X++.

Connecting to a Database via ODBC – Brandon George
http://dynamics-ax.blogspot.com/2005/11/connecting-to-database-via-odbc.html

Convert Axapta date to SQL datetime when querying SQL server directly – Fred Shen
http://fredshen.spaces.live.com/blog/cns!B32E9346DBBAE4E3!171.entry

Accessing an external database using X++ -- Arijit Basu
http://daxguy.blogspot.com/2006/11/updating-external-database-using-x.html

Access a SQL table that is not an Axapta table
http://dynamicsuser.net/forums/t/17136.aspx

But all of these were written for Axapta 3.0. I did not find a lot of documentation on how to do this in Dynamics 4.0. New security requirements in 4.0 mandate explicitly asserting permissions for calling outside data sources. MS has some source code samples, but they’re more for demonstrating the permissions classes than providing a functional example making calls to another database.
http://msdn2.microsoft.com/en-us/library/aa639808.aspx

For my project I needed to fetch records from a package shipping system to get the weight, tracking number, and delivery date of each box shipped. The shipping system uses a SQL database, but this code uses ODBC, so it may work for other data sources as well.

There was an additional requirement: I wanted this to run on the server, and use the AOS account for database authentication. This way, the code could be launched from any client without requiring every client to have the right SQL driver and ODBC configuration.

There was also a possible issue: The AOS is running on the 64-bit version of Windows Server 2003. The AOS is a 32-bit application, which can cause problems when trying to call a 64-bit ODBC driver.

Here is the finished code. It fetches the shipping records and copies them into a custom Dynamics table called MyShippingTrans. I've provided additional comments below.

============================

server static void GetShippingTrans(SalesTable salesTable)
{
LoginProperty loginProperty;
OdbcConnection connection;
Statement statement;
ResultSet results;
Str sqlStatement;
str dataSourceName = "MyODBCDataSource";
str databaseName = "MyDataBaseName";
MyShippingTrans trans;
SqlStatementExecutePermission sqlPermission;
;
loginProperty = new LoginProperty();
loginProperty.setDSN(dataSourceName);
LoginProperty.setDatabase(databaseName);

try
{

connection = new OdbcConnection(loginProperty);
statement = connection.createStatement();
sqlStatement = StrFmt(
"SELECT %1, %2, %3 "+
"FROM Shipment_detail WHERE " +
"Shipment_detail.packageid = '%4'"
, "tracking_number" //1
, "pkg_weight" //2
, "duedate" //3
, SalesTable.SalesId //4
);
sqlPermission = new SQLStatementExecutePermission(sqlStatement);
sqlPermission.assert();
results = statement.executeQuery(sQLStatement);
while (results.next())
{
setprefix(strfmt("Writing %1 to TLCShippingTrans"
,SalesTable.SalesId));
trans.clear();
trans.TrackingNumber = results.getString(1);
trans.Weight = results.getReal(2);
trans.DueDate = results.getDate(3);
trans.SalesId = salesTable.SalesId;
trans.insert();

}
CodeAccessPermission::revertAssert();
}
catch (Exception::Error)
{
error(strFmt("Error accessing shipping database "
+ "or writing to TLCShippingTrans"
+ " on sales order %1", salesTable.SalesId));
}

}

==========================

Here's the same code again, with some comments.

When declaring the method, make sure it runs on the server. Otherwise every client and batch server that runs it will need to have the SQL client and ODBC connection set up. Also, every user would need permission on the external database.

server static void GetShippingTrans(SalesTable salesTable)
{

When declaring the method, make sure it runs on the server. If it does not run on the server, then every client machine and batch server that runs it will need to have the SQL client and ODBC connection set up. Also, every user would need permission on the external database.

LoginProperty loginProperty;
OdbcConnection connection;
Statement statement;
ResultSet results;
Str sqlStatement;
str dataSourceName = "MyODBCDataSource";
str databaseName = "MyDataBaseName";

These are the variables that we'll use to create our connection to the SQL database via ODBC. For simplicity in this example, the data source and database names are hard-coded. In our production system, these are stored in a parameter table so that administrators can easily change them when necessary.

MyShippingTrans trans;

This is the Dynamics table where we'll store the retrieved data

SqlStatementExecutePermission sqlPermission;

Here's the permission object that was introduced in version 4.0 as part of the trustworthy computing initiative. It is now necessary to grant explicit permission to make a call to an external database.
;
loginProperty = new LoginProperty();
loginProperty.setDSN(dataSourceName);
LoginProperty.setDatabase(databaseName);

These lines initialize the login property with the data source name and database name established earlier. Some bloggers have reported that due to requirements of the trustworthy computing initiative, one can no longer make calls from Dynamics AX to MS SQL server using SQL authentication, even if the database server is configured too allow it. It must be done using windows authentication, which happens automatically if the .setUsername() and .setPassword() methods are not invoked. This may not hold true for access to other ODBC data sources, in which case these lines may be helpful:

// loginProperty.setUsername("UserID");
// loginProperty.setPassword("Password");


If there are problems accessing the ODBC data, I want to catch the error:

try
{

connection = new OdbcConnection(loginProperty);
statement = connection.createStatement();
sqlStatement = StrFmt(
"SELECT %1, %2, %3 "+
"FROM Shipment_detail WHERE " +
"Shipment_detail.packageid = '%4'"
, "tracking_number" //1
, "pkg_weight" //2
, "duedate" //3
, SalesTable.SalesId //4
);
This prepares our connection and defines the SQL statement that we'll send to the external data source. You can see that there are three fields we want to retrieve, and we'll select the records that match the SalesId that was passed to the method.

sqlPermission = new SQLStatementExecutePermission(sqlStatement);
sqlPermission.assert();
results = statement.executeQuery(sQLStatement);

Now we use the new permission classes. We create a permission specific to the SQL select statement that we want to use, and explicitly assert the permission. Then we fetch the ResultSet by passing the same SQL statement to the executeQuery() method.

while (results.next())
{
setprefix(strfmt("Writing %1 to TLCShippingTrans"
,SalesTable.SalesId));

trans.clear();
trans.TrackingNumber = results.getString(1);
trans.Weight = results.getReal(2);
trans.DueDate = results.getDate(3);
trans.SalesId = salesTable.SalesId;
trans.insert();
}

A while loop takes us through all the records, and assigns their values to the local table's fields. Note that the results.getType(x) must be in numerical order, or a database error results (more on this later).

CodeAccessPermission::revertAssert();

As you can guess from the name, this step removes the SQL permission granted earlier. This step is optional.


}
catch (Exception::Error)
{
error(strFmt("Error accessing shipping database or "
+ "writing to MyShippingTrans"
+ " on sales order %1", salesTable.SalesId));
}
}

Finally, we catch any errors that occurred and note the sales order on which it happened.

If you found this code helpful, take a minute and leave me a message about your current project.


======================


I mentioned above that the fields in the result set must be accessed in numerical order. If you try to access them out of order, or you reference them more than once, you'll get a database error. For example, you cannot do this:

// not in order
trans.DueDate = results.getDate(3);
trans.TrackingNumber = results.getString(1);
trans.Weight = results.getReal(2);

You also cannot do this:

if (results.getString(1))
{
// read index 1 a second time
trans.trackingNumber = results.getString(1);
{

Making these mistakes will cause one or more of these errors:

Cannot execute the required database operation. The operation failed.
[Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index
Cannot read a record in ().
The SQL database has issued an error.

======================

A note on using ODBC when running AOS on a 64-bit version of Windows:

The Dynamics AX 4.0 AOS is a 32-bit (x86) application. A 32-bit application is not permitted to call 64-bit (x64) code on Windows Server 2003. This means that you'll need to set up a 32-bit ODBC definition on your 64-bit server if you want Dynamics to use it.

The 64-bit ODBC connections are "invisible" to 32 bit apps, so if the Dynamics AOS tries to call a 64-bit ODBC connection, you'll get an error that the DSN is not found:

ODBC operation failed.
Unable to logon to the database.
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
Object 'OdbcConnection' could not be created

Fortunately, it is easy to set up the 32-bit ODBC connection. The default ODBC console on 64 bit Windows Server is, of course, 64-bit itself. MS did provide a 32-bit console, though. It can be found in
%SystemRoot%\SysWOW64\odbcad32.exe

Use this to set up and configure your ODBC connections, and the Dynamics AX AOS will see them.

I've only done this for the AOS on a Windows Server, but I would guess that the same considerations apply if you're trying to run the Dynamics AX client on an x64 desktop and your ODBC calls are initiated from the client.

Perhaps Dynamics AX 5.0 will have a 64-bit AOS as an option!

==============================

Some developers claim that using CCADO is a better way than ODBC to access other databases from Dynamics AX and X++. Here are some links in case you want to do more research:

Using CCADO to interact with other databases. (translated from Spanish)
http://translate.google.com/translate?hl=en&sl=es&u=http://www.trucosax.com/phpnuke/modules.php%3Fname%3DNews%26file%3Darticle%26sid%3D73&sa=X&oi=translate&resnum=4&ct=result&prev=/search%3Fq%3DCCADO%26hl%3Den%26safe%3Doff%26rls%3Dcom.microsoft:en-us%26pwst%3D1

Data Migration in Axapta Programming
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.axapta.programming&tid=962cb419-bdc4-45c4-8faa-5c4876c18c5f&p=1

Leave me a note if you think the CCADO classes are better than ODBC.