Create an ODBC connection in Ax

In Ax, you are never on an island. You have lots of options to bring in data from other systems, whether it's through AIF, reading an XML file or.. directly connecting to another database. In this post, I'll show how to create an ODBC connection to a external database.
For this, we'll use the standard available ODBCConnection class, as opposed to CLR Interop and rely on the .NET functions.
Here goes, in 3 simple steps.

1. Set the login property
2. Create the connection
3. Read from a sample table

static void myODBCConnection(Args _args)
{ ODBCConnection myODBC;
Statement myStatement;
LoginProperty myLoginProperty;
Resultset myResultset;

str mySQLStatement;
str myConnectionString;

str myDSN="yourdatasourcenamehere";
str myUserName="yourusername";
str myPassword="yourpassword";
;
myConnectionString=strfmt("DSN=%1;UID=%2;PWD=%3",myDSN,myUserName,myPassword);

myLoginProperty = new LoginProperty();
myLoginProperty.setOther(myConnectionString);

try
{
myODBC = new OdbcConnection(myLoginProperty);
myStatement=myODBC.createStatement();

mySQLStatement="SELECT Field1,Field2 FROM myTable";
myResultSet=myStatement.executeQuery(mySQLStatement);
while (myResultSet.next())
{
info(myResultSet.getString(1));
info(int2str(myResultSet.getInt(2)));
}
} catch
{
error('Unexpected error');
}
}

In earlier versions from Ax, the LoginProperty came with the methods to set the loginname and password. But Microsoft has cut these features (security reasons?). But as you can see in the example, we work around that by using the 'SetOther' method.

There's one thing you need to consider. The Ax client requires a 32 bit datasource. On a 64 bit system like Windows 2008, this may result in an error 'Data source name not found'. That's something for my next post.