SQL Database Access

April 4, 2022

Introduction

In the majority of cases you can and should access Maximo data through the Maximo Business Objects (MBOs). Using the MBOs ensures that Maximo's business rules are respected and that data is not corrupted. However, there are rare cases where accessing Maximo data through a database connection is appropriate or even necessary. In this post we examine how to obtain a database connection, use it responsibly and finally make sure that the connection does not leak resources.

This post assumes a working understanding of JDBC. Oracle provides an excellent JDBC tutorial here: https://docs.oracle.com/javase/tutorial/jdbc/basics/index.html

Maximo Database Manager

The Maximo Database Manager class psdi.server.DBManager manages the Maximo connection to the database and allocates connections from an internally managed connection pool. Using the psdi.server.DBManager allows you to get a Maximo managed connection to the database in the same way that the MBOs do, without having to directly establish a java.sql.Connection to the database.

Obtaining and Freeing a Connection

Obtain a Connection

A JDBC java.sql.Connection object is obtained from the psdi.server.DBManager class using a psdi.security.ConnectionKey object that is associated with a user session. The psdi.security.ConnectionKey object is accessible from the psdi.security.UserInfo object. The psdi.security.UserInfo object is turn available as the implicit automation script variable userInfo or can be obtained from the implicit mbo variable with the getUserInfo() method.

Free a Connection

As important as obtaining a connection is making sure you put it back. When manually using a JDBC connection it is critical to free resources to prevent resource leaks. Since Maximo manages a pool of connections, you should not close the connection. Instead you tell the DBManager that the connection is no longer being used and it is free to be assigned to another. You do this by calling the freeConnection(ConnectionKey) method on the DBManager. Note that in our example we put this in a try finally block to ensure that the connection is always freed at the end of execution.

Javascript
MXServer = Java.type("psdi.server.MXServer");


main();


function main() {
    var dbManager = MXServer.getMXServer().getDBManager();
    var connectionKey = userInfo.getConnectionKey()


    try {
        // Get a reference to the database manager
        var dbManager = MXServer.getMXServer().getDBManager();
        // Get a reference to user's ConnectionKey
        var connectionKey = userInfo.getConnectionKey();
        // Use the ConnectionKey to get a java.sql.Connection
        var connection = dbManager.getConnection(connectionKey);


        // Perform actions with the connection...
    } finally {
        dbManager.freeConnection(connectionKey);
    }
}
Python
from psdi.server import MXServer


def main():
    dbManager = MXServer.getMXServer().getDBManager()
    connectionKey = userInfo.getConnectionKey()


    try:
        # Get a reference to the database manager
        dbManager = MXServer.getMXServer().getDBManager()
        # Get a reference to user's ConnectionKey
        connectionKey = userInfo.getConnectionKey()
        # Use the ConnectionKey to get a java.sql.Connection
        connection = dbManager.getConnection(connectionKey)
        
        # Perform actions with the connection...
    finally:
        dbManager.freeConnection(connectionKey)


main()

Using Connection

Once you have a java.sql.Connection reference, you can use it as you would any other JDBC connection. This post is not intended to cover JDBC, but in the interest of providing something useful we are going to look at using a java.sql.Statement and a java.sql.PreparedStatement.

java.sql.Statement

A java.sql.Statement is used to execute static SQL and return the results it produces. A java.sql.Statement is obtained from the java.sql.Connection with the createStatement() method. Once you have a java.sql.Statement you can use the execute(String sql) method to execute a static SQL statement, this will return a boolean value of true if there is a result or false if not. The executeQuery(String sql) method takes a static query SQL statement and will return a java.sql.ResultSet that can be used to access to the query results, note that this never returns null even if there are no results. Finally, the executeUpdate(String sql) method takes a static update SQL statement and returns an int that represents the number of rows updated.

After using the java.sql.Statement the close() method should be called to release JDBC resources immediately. This is important since the java.sql.Statement comes from a pool of java.sql.Connection objects and may not be closed automatically in a timely manner since the connection may remain open for an extended time. Likewise, if a java.sql.ResultSet is obtained, it should be closed after use. In both these cases, a try / finally block is appropriate to ensure the resources are closed.

java.sql.ResultSet objects have a next() method that returns a true value if there is a row in the set and moves the cursor to that row. When the java.sql.ResultSet is first returned it is in a zero position and calling next() moves the cursor to the first row.

Also note that column data in a java.sql.ResultSet is one (1) indexed instead of zero (0), so the first column is at index one (1) not zero (0).

Javascript
MXServer = Java.type("psdi.server.MXServer");


main();


function main() {
    // Get a reference to the database manager
    var dbManager = MXServer.getMXServer().getDBManager();
    // Get a reference to user's ConnectionKey
    var connectionKey = userInfo.getConnectionKey();
    // Use the ConnectionKey to get a java.sql.Connection
    var connection = dbManager.getConnection(connectionKey);


    var statement;
    var resultSet;


    try {
        statement = connection.createStatement();


        // delete everything in the dummy_table
        statement.execute("delete from dummy_table");
        
        // get labor records where the labor code starts with a capital A.
        resultSet = statement.executeQuery("select laborcode from labor where laborcode like 'A%'");


        while (resultSet.next()) {
            // do something with the data.
            var laborCode = resultSet.getString(1);
        }


        // update the user status to active for blocked users and capture how many users were unblocked.
        var blockedUserCount = statement.executeUpdate("update maxuser set status = 'ACTIVE' where status = 'BLOCKED'");


    } finally {
        if (statement && !statement.isClosed()) {
            statement.close();
        }
        if (resultSet && !resultSet.isClosed()) {
            resultSet.close();
        }


        dbManager.freeConnection(connectionKey);
    }
}
Python
from psdi.server import MXServer


def main():
    # Get a reference to the database manager
    dbManager = MXServer.getMXServer().getDBManager()
    # Get a reference to user's ConnectionKey
    connectionKey = userInfo.getConnectionKey()
    # Use the ConnectionKey to get a java.sql.Connection
    connection = dbManager.getConnection(connectionKey)
     
    statement
    resultSet
    try:
        statement = connection.createStatement()


        # delete everything in the dummy_table
        statement.execute("delete from dummy_table")
        
        # get labor records where the labor code starts with a capital A.
        resultSet = statement.executeQuery("select laborcode from labor where laborcode like 'A%'")


        while resultSet.next():
            # do something with the data.
            laborCode = resultSet.getString(1)      


        # update the user status to active for blocked users and capture how many users were unblocked.
        blockedUserCount = statement.executeUpdate("update maxuser set status = 'ACTIVE' where status = 'BLOCKED'")


    finally:
        if statement is not None and not statement.isClosed():
            statement.close()
        
        if resultSet is not None and not resultSet.isClosed():
            resultSet.close()


        dbManager.freeConnection(connectionKey)
            
main()

java.sql.PreparedStatement

A java.sql.PreparedStatement represents a precompiled SQL statement and allows for efficient execution of a statement multiple times. Parameters can be provided as part of the statement using question mark (?), with parameters one (1) indexed in the same way as accessing results from a java.sql.ResultSet object. A java.sql.PreparedStatement is obtained from the java.sql.Connection with the prepareStatement(String sql) method.

The java.sql.PreparedStatement supports the same execute(), executeQuery() and executeUpdate() as the java.sql.Statement, but without the java.lang.String parameter, since a 'java.sql.PreparedStatement` is created with the SQL statement that will be executed.

The same principles of closing both java.sql.ResultSet and java.sql.PreparedStatement objects applies when using the java.sql.PreparedStatement.

Javascript
MXServer = Java.type("psdi.server.MXServer");


main();


function main() {
    // Get a reference to the database manager
    var dbManager = MXServer.getMXServer().getDBManager();
    // Get a reference to user's ConnectionKey
    var connectionKey = userInfo.getConnectionKey();
    // Use the ConnectionKey to get a java.sql.Connection
    var connection = dbManager.getConnection(connectionKey);


    var preparedStatement;
    var resultSet;


    try {
        preparedStatement = connection.prepareStatement("delete from dummy_table");


        // delete everything in the dummy_table
        preparedStatement.execute();
        preparedStatement.close();


        // get labor records where the status equals ACTIVE.
        preparedStatement = connection.prepareStatement("select laborcode from labor where status = ?");
        preparedStatement.setString(1, "ACTIVE");


        resultSet = preparedStatement.executeQuery();


        while (resultSet.next()) {
            // do something with the data.
            var laborCode = resultSet.getString(1);
        }


        resultSet.close();


        // use the same prepared statement, but search for INACTIVE this time.
        preparedStatement.setString(1, "INACTIVE");
        resultSet = preparedStatement.executeQuery();
        
        while(resultSet.next()){
            // do something with the data.
            var laborCode = resultSet.getString(1);
        }


        preparedStatement.close();


        // prepare a statement to update the users statuses
        preparedStatement = connection.prepareStatement("update maxuser set status = ? where status = ?");


        preparedStatement.setString(1, "ACTIVE");
        preparedStatement.setString(2, "BLOCKED");


        // execute the statement.
        var blockedUserCount = preparedStatement.executeUpdate();


    } finally {
        if (preparedStatement && !preparedStatement.isClosed()) {
            preparedStatement.close();
        }
        if (resultSet && !resultSet.isClosed()) {
            resultSet.close();
        }


        dbManager.freeConnection(connectionKey);
    }
}
Python
from psdi.server import MXServer


def main():
    # Get a reference to the database manager
    dbManager = MXServer.getMXServer().getDBManager()
    # Get a reference to user's ConnectionKey
    connectionKey = userInfo.getConnectionKey()
    # Use the ConnectionKey to get a java.sql.Connection
    connection = dbManager.getConnection(connectionKey)
    
    preparedStatement
    resultSet


    try:
        preparedStatement = connection.prepareStatement("delete from dummy_table")


        # delete everything in the dummy_table
        preparedStatement.execute()
        preparedStatement.close()


        # get labor records where the status equals ACTIVE.
        preparedStatement = connection.prepareStatement("select laborcode from labor where status = ?")
        preparedStatement.setString(1, "ACTIVE")


        resultSet = preparedStatement.executeQuery()


        while resultSet.next() :
            # do something with the data.
            laborCode = resultSet.getString(1)
        
        resultSet.close()


        # use the same prepared statement, but search for INACTIVE this time.
        preparedStatement.setString(1, "INACTIVE")
        resultSet = preparedStatement.executeQuery()
        
        while resultSet.next():
            # do something with the data.
            var laborCode = resultSet.getString(1)
        
        preparedStatement.close()


        # prepare a statement to update the users statuses
        preparedStatement = connection.prepareStatement("update maxuser set status = ? where status = ?")


        preparedStatement.setString(1, "ACTIVE")
        preparedStatement.setString(2, "BLOCKED")


        # execute the statement.
        blockedUserCount = preparedStatement.executeUpdate()


    finally:
        if preparedStatement is not None and not preparedStatement.isClosed():
            preparedStatement.close()
        
        if resultSet is not None and not resultSet.isClosed():
            resultSet.close()   


        dbManager.freeConnection(connectionKey) 
            
main()

psdi.mbo.DBShortcut

If all that was a bit much, there is a shortcut, the psdi.mbo.DBShortcut. This provides access to connect, execute statements and queries and then free the connection without having to worry about the underlying psdi.server.DBManager, java.sql.Statement and java.sql.PreparedStatement objects. It is somewhat limited in its capabilities, but if you only need to execute a statements one at a time or query the database it may provide a more simple solution than the full JDBC framework provides.

The connect(ConnectionKey) method is similar the psdi.server.DBManager, but allows the psdi.mbo.DBShortcut to manage the connection lifecycle for you. The execute(SqlFormat) allows arbitrary SQL statements to be executed, with the notable exception that the method takes a psdi.mbo.SqlFormat object instead of a direct string, which encourages you to use the psdi.mbo.SqlFormat features that we covered here. The executeQuery(String) method executes the SQL query provided and returns the java.sql.ResultSet object. Finally, the close() method releases all the underlying JDBC connection resources and frees the connection back to the psdi.server.DBManager connection pool.

Putting all that together, our previous example looks like the following:

Javascript
DBShortcut = Java.type("psdi.mbo.DBShortcut");
SqlFormat = Java.type("psdi.mbo.SqlFormat");


main();


function main() {
    var dbShortcut = DBShortcut();
    var connectionKey = userInfo.getConnectionKey();


    try {
        dbShortcut.connect(connectionKey);


        // create a new SqlFormat object to unblock blocked users.
        var sqlf = new SqlFormat("update maxuser set status = :1 where status = :2");
        sqlf.setObject(1, "MAXUSER", "STATUS", "ACTIVE");
        sqlf.setObject(2, "MAXUSER", "STATUS", "BLOCKED");
        
        dbShortcut.execute(sqlf);
        
        // get labor records where the labor code starts with a capital A.
        resultSet = dbShortcut.executeQuery("select laborcode from labor where laborcode like 'A%'");


        while (resultSet.next()) {
            // do something with the data.
            var laborCode = resultSet.getString(1);
        }


    } finally {
        dbShortcut.close();
    }
}
Python
from psdi.mbo import DBShortcut, SqlFormat


def main():
    dbShortcut = DBShortcut()
    connectionKey = userInfo.getConnectionKey()


    dbManager = MXServer.getMXServer().getDBManager()
    connectionKey = userInfo.getConnectionKey()


    try:
        dbShortcut.connect(connectionKey)


        # create a new SqlFormat object to unblock blocked users.
        sqlf = new SqlFormat("update maxuser set status = :1 where status = :2")
        sqlf.setObject(1, "MAXUSER", "STATUS", "ACTIVE")
        sqlf.setObject(2, "MAXUSER", "STATUS", "BLOCKED")
        
        dbShortcut.execute(sqlf)
        
        # get labor records where the labor code starts with a capital A.
        resultSet = dbShortcut.executeQuery("select laborcode from labor where laborcode like 'A%'")


        while resultSet.next():
            # do something with the data.
            var laborCode = resultSet.getString(1)
        }


    finally:
        dbShortcut.close()      


main()

Conclusion

In this post we showed how to access the database directly in a safe and responsible way. We demonstrated how to execute statements and queries both as static java.lang.String statements and as a precompiled java.sql.PreparedStatemt that can accept parameters for more efficient execution. We reviewed how resources should be closed or freed to avoid resource leaks and system performance issues.

Finally, this is the first post where we have provided examples in both JavaScript and Python, which has been often requested.

If you have any questions or comments please reach out to us at [email protected]

In the time it took you to read this blog post...

You could have deployed Opqo, our game-changing mobile solution for Maximo.

Opqo is simple to acquire, simple to deploy and simple to use, with clear transparent monthly pricing that is flexible to your usage.

About

Learn

Help

Legal

© 2024 Sharptree LLC, All Rights Reserved.