Axapta 3: ADO instead of ODBC or install the SP6

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)

    2:  {

    3:      LoginProperty           loginProperty;

    4:      OdbcConnection          connection;

    5:      Statement               statement;

    6:      ResultSet               resultSet;

    7:      ;

    8:      //initialzing the objects..

    9:      loginProperty = new LoginProperty();

   10:      loginProperty.setDSN("SAMPLE");

   11:      loginProperty.setUsername("usr");

   12:      loginProperty.setPassword("pwd");

   13:      connection = new OdbcConnection(loginProperty);

   14:      statement = connection.createStatement();

   15:      //query

   16:      resultSet = statement.executeQuery("Select count(*) from ADMINISTRATOR.EMPLOYEE");

   17:      //getting infos

   18:      if (resultSet.next())

   19:      {

   20:         info (strfmt("Number of records: %1", resultSet.getInt(1)));

   21:      }

   22:      //disposing object

   23:      resultSet.close();

   24:      resultSet.close();

   25:      connection = null;

   26:  }

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)

    2:  {

    3:  CCADOConnection connection = new CCADOConnection();

    4:  CCADORecordSet rSet = new CCADORecordSet();

    5:  CCADOCommand command;

    6:   

    7:  str sqlString;

    8:  ;

    9:      //see https://www.connectionstrings.com for further information about your specific provider

   10:      connection.connectionString("Provider=IBMDADB2;Database=SAMPLE;host;Protocol=TCPIP;Port=50000;Uid=usr;Pwd=pwd;");

   11:      //very simple example...

   12:      sqlString = "Select count(*) from ADMINISTRATOR.EMPLOYEE";

   13:   

   14:      connection.open();

   15:      rSet.open( sqlString, connection);

   16:      // Loop through the records

   17:      While (! RSet.EOF())

   18:      {

   19:          // Show the fields values

   20:          info(rSet.fields().itemIdx(0).value());

   21:          // Next record

   22:          rSet.moveNext();

   23:      }

   24:       rSet.close();

   25:       connection.close();

   26:  }

 

 

If you want to know the connection-string for your database, I recommend ConnectionStrings.

Comments

• Anonymous
  August 18, 2009
  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.