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.

Color-code your Dynamics environments

Since implementing Axapta / Dynamics AX in 2003, we've found it invaluable to provide an environment for testing and training as well as our live system (or production environment) to end users. This lets people try different processes or train new employees without fear of screwing up the "real" data.

Occasionally, though, we've run into a problem where someone thought they were using the live system, but were actually in the test environment. It's an easy mistake to make. There is no obvious visual cue to alert a user that they are working on a test system.

There is a way change the color of the Dynamics forms to help indicate what environment is in use. It involved overriding the SysSetupFormRun.run() method, which will be called every time a form is opened. On the class SysSetupFormRun, create a new method with this code:


public void run()
{
SysSQLSystemInfo systemInfo = SysSQLSystemInfo::construct();
;
super();


// Set the color scheme of this instance of the SysFormRun to RGB
this.design().colorScheme(FormColorScheme::RGB);


// If the database name is not the live version, change the color of the form

if (systemInfo.getloginDatabase() != 'MyDBName')
this.design().backgroundColor(0x112255);
}



That's all there is to it. If your live and test systems use the same database name, but the AOS is running on different servers you can modify this code to to match on systemInfo.getLoginServer() != 'MyServerName'. You can change the color by setting the hex value. It uses the RGB values in reverse order: 0xBBGGRR.


Variations of this code could switch form colors to indicate the current user account, application layer, or company. Less practical uses could match color to the time of day or season of the year. If you find this code useful, leave me a message noting what you did with it.


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


Special thanks to Brandon George and his Dynamics AX blog for help with form colors.

For more information on changing form colors and sample code for version 3.0, see:

http://dynamics-ax.blogspot.com/2006/04/changing-form-color-based-on-current.html



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




Sample screens showing how the code above changes the test environment forms compared to the same form in the live production environment.

Axapta 3.0 to Dynamics 4.0: upgrade errors and issues

The documentation that Microsoft provides for upgrading from Dynamics AX version 3 to version 4 (the 676-page implementation guide) is surprisingly complete. During our conversion process, though, we ran into a couple of unexpected error messages. Here are some of the issues we ran into, and how we worked through them.

1. =======================================
Extra tables in the database. The Dynamics AX development environment takes care of managing the SQL tables and data structure. Any tables that you want added should be done through the AOT. But this is a best practice that is sometimes ignored. In our case we had a few tables in the database for our Axapta 3.0 installation that were created through SQL server itself. (They aided communication with another application.) When running the DB Upgrade Preparation Tool (which transfers each table in the 3.0 database to a new, empty version 4.0 database) tables that were created outside the Axapta development environment cause an error. You'll want to remove these tables before the beginning the conversion process. If you get the error in the middle of the DB upgrade prep process you can delete the offending table using SQL Server management tools, and re-start the process.

2. =======================================
Errors caused by compiling before entering license keys. When you start the Dynamics 4.0 client for the first time after converting your data from version 3.0, you are presented with an upgrade checklist. The first 2 steps are compiling the application and entering your license keys. Here is where we get to a catch-22: you have to compile the application before you enter your license keys, but the compiler needs your license keys to know if you have rights to the application objects.

If you are doing a fresh installation (not migrating data from version 3.0), this is not a problem, because there will be no license keys in the database, and Dynamics allows full access in demo mode. But if you have data from Axapta version 3.0, your license keys are stored in the database. Dynamics 4.0 uses different license keys than version 3.0 and it interprets the existing keys as invalid. This will cause a number of errors while compiling, and will cause errors in later steps, like the pre- and post-synchronize data upgrade cockpit. You'll get errors like these:

• Insufficient rights on object
• Error executing code: JMGParameters (table) has no valid runable code in method numberSeqModule

The fix is to compile the application again after entering your license keys for version 4.0, and before starting the pre-synchronize data upgrade cockpit. If you already started the pre-sync data upgrade process and got an error, don't worry, you won't lose anything. Close the data upgrade form, re-compile the application, and re-start the data upgrade process. (If asked, you don't need to reset the dependent steps in the checklist.)

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

Hope you find these tips useful. If you are in the middle of a conversion, post a comment here letting me know how it's going!

TextBuffer.Encrypt() changes from version 3 to 4

In version 3.0 there is a basic method for encryption: TextBuffer.Encrypt(key). It is easy to use, but not very secure; so in version 4.0 it has been phased out. To make sure it is not used any more Microsoft eliminated it, but left in a decryption method to help customers migrate encrypted data. All this sounds good and reasonable, but I ran into a problem.

We had been using the TextBuffer.encrypt method to encrypt credit card numbers in v 3.0. When I tried to use the TextBuffer.decryptOld(key) in v 4, though, I found the DecryptOld method was not returning our data intact.

I've created a job to demonstrate the problem. If you run it in version 3, it works. If you run it in verion 4, the data gets corrupted.

Version 3 job =============

static void DecodeTest1(Args _args)
{
TextBuffer txt = new TextBuffer();
;
txt.setText("4444555566667777");
info(strfmt("Clear text: %1",txt.getText()));
txt.encrypt(123456);
info(strfmt("Encrypted: %1",txt.getText()));

txt.setText("ÔÑ2G¡.Ÿ¤®‹LAûH9þ");
info(strfmt("Encryped text: %1",txt.getText()));
txt.decrypt(123456);
info(strfmt("Decrypted: %1",txt.getText()));
}

===========
The output shows the text is encrypted to "ÔÑ2G¡.Ÿ¤®‹LAûH9þ" and then decrypted to plain text.

Now run just the decryption in version 4:
===========

static void DecodeTest1(Args _args)
{

TextBuffer txt = new TextBuffer();
;
txt.setText("ÔÑ2G¡.Ÿ¤®‹LAûH9þ");
info(strfmt("Encryped text: %1",txt.getText()));
txt.decryptold(123456);
info(strfmt("Decrypted: %1",txt.getText()));
}

The result is
Decrypted: 444455僮56鷄667777
instead of
Decrypted: 4444555566667777
(In case the unicode characters don't display, in the version 4 result the 7th and 10th digit are replaced by Asian characters.)

I did not see any fixes from Redmond and could not find anyone else reporting problems with the DecryptOld method.

We had to work around this issue by decrypting the data in version 3 to a temporary field. After the data conversion, the data was re-encrypted and the temporary field deleted.

Even though our conversion is done, I'd love to hear from anyone who ran into this same problem or knows about this issue.