June 26, 2012

Retrieve JDBC connection to Maximo database

In some cases you need a JDBC connection to directly access Maximo database in order to perform some job. This is not common since all Maximo data can be accessed through standard MboSets. However, you may need to access non-Maximo tables, perform specific SQL statements or bypass Mbos for performance reasons. The two main approaches to use issue standard SQL code through Java JDBC are described here.

JDBC Connection
Here is a sample that shows how to get a JDBC connection from Maximo connection pool (and correctly release it).

public int countAssets() throws RemoteException, MXException
{
  MXServer mxServer = MXServer.getMXServer();
  ConnectionKey conKey = mxServer.getSystemUserInfo().getConnectionKey();
  Connection con = mxServer.getDBManager().getConnection(conKey);

  int c = 0;
  try
  {
    Statement stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery("select count(*) from asset");
    while (rs.next())
      c = rs.getInt(1);
    rs.close();
    stmt.close();
    con.commit();
  }
  catch (SQLException e )
  {
    throw new MXApplicationException("SQLException caught", e.getMessage());
  }
  finally
  {
    mxServer.getDBManager().freeConnection(conKey);
  }
  return c;
}

DBShortcut class
A quick easy way to get a java.sql.ResultSet object is to use DBShortcut class. This object comes in the psdi.mbo package and is designed to quickly retrieve result-sets or run update statements against the database.
public int countAssets() throws RemoteException, MXException
{
  MXServer mxServer = MXServer.getMXServer();
  ConnectionKey conKey = mxServer.getSystemUserInfo().getConnectionKey();
  DBShortcut dbs = new DBShortcut();
  dbs.connect(conKey);

  int c = 0;
  try
  {
    ResultSet rs = dbs.executeQuery("select count(*) from asset");
    while (rs.next())
      c = rs.getInt(1);
    rs.close();
    dbs.commit();
  }
  catch (SQLException e )
  {
    throw new MXApplicationException("SQLException caught", e.getMessage());
  }
  finally
  {
    dbs.close();
  }
  return c;
}

Alternative methods
In both the described methods the connection is retrieved through a static reference to MXServer object and a system psdi.security.ConnectionKey object.
A better approach could be to use some methods defined in the psdi.mbo.Mbo class as follows.
ConnectionKey conKey = getUserInfo().getConnectionKey();
Connection con = getMboServer().getDBConnection(conKey);

6 comments:

  1. greate example - do you have any examples of creating connection to a non-maximo database from within a maximo java program? would like to query and update non maximo database with a maximo cron job and with an interface exit.

    ReplyDelete
    Replies
    1. To get a connection to an external database you can use plain JDBC. I suggest you to practice with some sample code in a main() method. Once you get it working, you can copy and paste ito the cron code.

      Delete
  2. Hi Bruno can you suggest some ideas on how to copy all the data from a non maximo result set to a mboset of maximo.Do we have any method to achieve that? plz help

    ReplyDelete
  3. Can we use JDBC connection to insert a new record in MBO

    ReplyDelete
  4. Can we use JDBC connection to insert a new record in MBO using automation scripting

    ReplyDelete
    Replies
    1. I think it's possible but I strongly discourage it.
      Access to data should only be trough MBOs especially if updating/inserting.
      Exception to this rule if you are writing into non standard tables on non-Maximo tables.

      Delete