Axapta 3: ADO instead of ODBC or install the SP6

Axapta 3: ADO instead of ODBC or install the SP6

Rate This
  • Comments 1

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:

image 

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 http://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.

Leave a Comment
  • Please add 5 and 4 and type the answer here:
  • Post
  • 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.

Page 1 of 1 (1 items)