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);

Retrieve JDBC connection to Maximo database

9 thoughts on “Retrieve JDBC connection to Maximo database

  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.

  2. 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.

  3. 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

  4. 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.

  5. @Bruno : How to bypass Asset Creation Code while creating new asset for custom application as they want data only in Asset and Assetspec not in other asset associated table in which by default entry will create when new asset is added.

  6. @Bruno. Its possible to get the database name or the full URL just using the browser login? I need to fetch data into Power BI

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to top