ODBC

Sometimes, data that you need to access from Microsoft Dynamics AX may be
stored in an alternative database. This is common when fetching data from an
external application, either on a regular basis, or during data conversion.
An Open DataBase Connection (ODBC) is a simple way for Microsoft Dynamics
AX to access an external database, and perform queries on it to fetch the required
data.
For this to work, an ODBC DataSource Name (DSN) needs to be created in
Windows. A DSN acts as a thin database client, usually including all
authentication information like username and password. The DSN needs to be
created on the tier where the X++ code will call it from (in other words, either the
client or the AOS). Best practice is to keep the DSN on the AOS, to help
scalability.
Once a DSN is available in Windows, code can be written to leverage its access
to the external database:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

static void TestODBC() { LoginProperty loginProperty; OdbcConnection odbcConnection; Statement statement; ResultSet resultSet; str sql, criteria; SqlStatementExecutePermission perm; ; //Set information on the ODBC loginProperty = new LoginProperty(); loginProperty.setDSN("dsn"); loginProperty.setDatabase("databaseName"); //Create connection to external DB odbcConnection = new OdbcConnection(loginProperty); if (odbcConnection) { sql = "SELECT * FROM MYTABLE WHERE FIELD =" + criteria + " ORDER BY FIELD1,FIELD2 ASC" //assert permission for sql string perm = new SqlStatementExecutePermission(sql); perm.assert(); //Prepare statement statement = odbcConnection.createStatement(); resultSet = statement.executeQuery(sql); //Running statement while (resultSet.next()) { //It is not possible to get field 3 and then 1. Always get fields in numerical order: 1,2,3,4,5,6 print resultSet.getString(1); print resultSet.getString(3); } //Shutting down the connection resultSet.close(); statement.close(); } else error("Failed to log on to the database"); }

The previous code uses a variable called "criteria", and includes it in the SQL
string that is executed against the example external database. It then prints the
string values from columns 1 and 3 in the result set. Columns in the result set can
only be called in numerical order. If you need to print column 3 value first, first
fetch the column 1 value and store it in a local variable, then fetch and print the
column 3 value, then print the local variable.
More methods are available on the ResultSet object, to fetch other types from the
record. These include, getReal(), getDate(), getInt().
The string that is passed to the statement.executeQuery() method must be an
SQL query in the format that the external database can understand. For example,
if you are querying an Oracle database, the string must contain a valid Oracle
SQL query.

Best Regards,
Hossein Karimi