How to run SQL query in Maximo without DB access using API Automation Script

From Maximo version 7.6.0.9, we can now build custom API using automation script. This is a powerful new feature yet it looks to be underutilized by the community.

The first obviously use case is it gives us the freedom to build any API we wanted without being restricted to the limitation of the Maximo Integration Framework. For example, we can build an API that returns data in CSV or binary format. Or we can use it to upload data and bypassing the business layer.

Since it allows us to use the browser to interact with Automation script, and the script framework itself has access to all Java functions of the MBO layer, We can exploit it to execute all sort of weird operations.

In an article I posted a few days ago, I use API script to call a Java function to refresh Maximo sequence and avoid a restart. Using the same approach, we can do a database configuration and deployment without downtime. I will provide more details about that in a future post.

In this tech note today, I'll demonstrate how we use API script to run SELECT, UPDATE, DELETE SQL statements to Maximo database without direct DB access. This can come in handy when DB access is restricted. Of course, we can use MXLoader to achieve the same result. However, with this new method, it is a lot more convenient.

Creating an API script is very simple, we just need to create a script without launch point. Then we can call it by accessing this URL on the browser:

https://[MAXIMO_ROOT]/maximo/oslc/script/[SCRIPT_NAME]

If you're already logged in and has a session. That is all it takes. Otherwise, to authenticate the request, you can pass in username and password parameters like you would normally do when calling REST API.

https://[MAXIMO_ROOT]/maximo/oslc/script/[SCRIPT_NAME]?_lid=[USERNAME]&_lpwd=[PASSWORD]

To run a SELECT query on the database, I created a script named RUNDANGEROUSSQL with the piece of python code I included at the end of this post.

To use the script to run a query, I typed the SQL query directly in the URL in the sql parameter as below. In this case, the data is returned to the browser in CSV format. If it's a big table, we can also copy/paste the text from the browser into a .CSV file then open it in Excel for better readability.

We can use this same autoscript to run DELETE or UPDATE command. Maximo will give an error in the response because there is no resultset return when executing the statement.  But statement will still run and update the data. If you don't like to see the error, you can modify the script a bit to handle the case stmt.executeQuery(sql)  doesn't return a value.

Have fun.

RUNDANGEROUSSQL Source Code: