External Database or ODBC Connection in Ax 2012

First Create DSN for MS Access Database

Second Add Your AccessDatabase to that DSN

Third write this below code

static void JKDBTest(Args _args)
{
LoginProperty loginProperty;
OdbcConnection odbcConnection;
Statement statement;
ResultSet resultSet;
str sql, criteria,ConnectionString;
SqlStatementExecutePermission perm;
;
// Set the information on the ODBC.
loginProperty = new LoginProperty();

// ConnectionString=strfmt(“DSN=%1;UID=%2;PWD=%3″,”MS Access Database”,””,””);

// loginProperty = new LoginProperty(); Loginproperty.setOther(ConnectionString);

loginProperty.setDSN(“Johnkrish”);
loginProperty.setDatabase(“JKDatabase”);
//Create a connection to external database.
odbcConnection = new OdbcConnection(loginProperty);
if (odbcConnection)
{
sql = “SELECT * FROM ABill_Table”;

/* WHERE FIELD = ”
+ criteria
+ ” ORDER BY FIELD1, FIELD2 ASC ;”;
*/
//Assert permission for executing the sql string.
perm = new SqlStatementExecutePermission(sql);
perm.assert();
//Prepare the sql statement.
statement = odbcConnection.createStatement();
resultSet = statement.executeQuery(sql);
//Cause the sql statement to run,
//then loop through each row in the result.
while (resultSet.next())
{
//It is not possible to get field 3 and then 1.
//Always get fields in numerical order, such as 1 then 2 the 3 etc.
print resultSet.getString(1);
print resultSet.getString(2);
}
//Close the connection.
resultSet.close();
statement.close();
pause;
}
else
{
error(“Failed to log on to the database through ODBC.”);
}

}

 

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!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s