Maximo

Of course, you could do the standard application approach where you get the connection string, username and password and then open a connection yourself. However, the Maximo Server object can give us the connect object. This eliminates the need to have the username and password stored somewhere (security audits like that). As such I will not be covering that approach here. In both examples, we will be using the Maximo Server Object (MXServer) to get the database connection.

JDBC Connection

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

from psdi.server import MXServer
from psdi.security import ConnectionKey
from java.sql import Connection
from java.sql import Statement

mxServer = MXServer.getMXServer()
conKey = mxServer.getSystemUserInfo().getConnectionKey()
con = mxServer.getDBManager().getConnection(conKey)

c = 0
try:
    stmt = con.createStatement()
    rs = stmt.executeQuery("select count(1) from asset")
    while rs.next():
        c = rs.getInt(1)
    rs.close()
    stmt.close()
    con.commit()
except:
    #error do something meaningful about the error
    c = -1
finally:
    mxServer.getDBManager().freeConnection(conKey)

If you have done any java JDBC programming at all you will see that this is a pretty standard approach. We first get the credentials for connecting to the database (conKey). Then we use it to get a connection object (con) from the Maximo database connection pool. Then we use a Statement object (stmt) to get a ResultSet object (rs). We then process that ResultSet. Take special note of the last line:

              mxServer.getDBManager().freeConnection(conKey)

This is how you tell Maximo that you are done with the JDBC connection so it can free it to the connect pool. Eventually the garbage collection would see that your script is complete and free the connection, but issuing the command makes it happen immediately, lessoning the change of running out of available connections. (e.g. when you are processing thousands of records, so the script is running thousands of times.)

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.

from psdi.server import MXServer
from psdi.security import ConnectionKey
from psdi.mbo import DBShortcut
from java.sql import Connection

mxServer = MXServer.getMXServer()
conKey = mxServer.getSystemUserInfo().getConnectionKey()
dbs = DBShortcut()
dbs.connect(conKey)

c = 0
try:
    rs = dbs.executeQuery("select count(1) from asset")
    while rs.next():
        c = rs.getInt(1)
    rs.close()
    dbs.commit()
except:
    #error do something meaningful about the error
    c = -1
finally:
    dbs.close()

The big difference here is that by using the DBShortcut class, Maximo controls all of the required connection objects (e.g. Connection, Statement) so you do not have to worry about them. It just gives you a ResultSet. Where this really is shown is in the cleanup. We don't have a Statement object to close, instead of committing the connection, we tell DBShortcut to commit(), and by simply calling the close() method on the DBShortcut variable, it will automatically free up the connection. The DBShortcut is doing everything we did in the JDBC Connection example, it just does it for you so you can use few lines of code.