I recently did face a quite annoying problem with the OdbcConnection-class on Axapta 3 (client build number: 1951.8). I could access my DB2 (9.5)database without problems, but unfortunately I wasn’t able to close the connection. The only way to close this connection was shutting down the instance that initialized the connection (the Ax-Client or the AOS). The Application-tool from DB2 offers a great tool to monitor the connection hold by each instance. As you can see it was there that the Ax-Client (ax32.exe) did not release the connection:
Here is the reproduction code:
1: static server void main(Args _args)
3: LoginProperty loginProperty;
4: OdbcConnection connection;
5: Statement statement;
6: ResultSet resultSet;
8: //initialzing the objects..
9: loginProperty = new LoginProperty();
13: connection = new OdbcConnection(loginProperty);
14: statement = connection.createStatement();
16: resultSet = statement.executeQuery("Select count(*) from ADMINISTRATOR.EMPLOYEE");
17: //getting infos
18: if (resultSet.next())
20: info (strfmt("Number of records: %1", resultSet.getInt(1)));
22: //disposing object
25: connection = null;
After installing the SP6 on Axapta 3 (client build number: 1951.7609), the connection was released with a delay of 60 seconds, but at least I was not forced to shut down the instance.
Another possibility for most common databases is ADO. Since Axapta 3 does not support .Net (Ax4 and Ax2009 are doing this and so you can use ADO.Net instead), ODBC and ADO are the only possibilities. By using ADO I did not face the problems I described above, so if SP6 is not an option for you just use ADO. Here’s the reproduction code for ADO.
1: public static void main(Args _args)
3: CCADOConnection connection = new CCADOConnection();
4: CCADORecordSet rSet = new CCADORecordSet();
5: CCADOCommand command;
7: str sqlString;
9: //see http://www.connectionstrings.com for further information about your specific provider
11: //very simple example...
12: sqlString = "Select count(*) from ADMINISTRATOR.EMPLOYEE";
15: rSet.open( sqlString, connection);
16: // Loop through the records
17: While (! RSet.EOF())
19: // Show the fields values
21: // Next record
If you want to know the connection-string for your database, I recommend ConnectionStrings.
Thank you! Your solution was very helpful. I was searching for a way to connect to AS400 from AX and finally stumbled upon your solution.