The script below (and attached) will let you try out sql from a browser or using a tool like Postman (
https://www.postman.com/). You can deploy the script using the VisualCode extension here
https://marketplace.visualstudio.com/items?itemName=sharptree.maximo-script-deploy , which is a great help when creating scripts and worth getting to know anyway. Otherwise, you can create a script manually, just name is SQL and select Javascript as the script language.
For the following examples I am going to assume your server is at
https://maximo-server/maximo. The results are returned in JSON and for formatting you may want to use Firefox as the browser since it has a nice JSON formatter built in and the results will look a lot nicer than in Chrome.
To invoke the script put the following in your browser address bar, not that only the first 1000 records will be returned.
https://maximo-server/maximo/oslc/script/sql/[OBJECT_NAME]?sql=[WHERE_CLAUSE]For example if I wanted to query for work order number 1001:
https://maximo-server/maximo/oslc/script/sql/workorder?sql=wonum='1001'You can also query for related records with the following:
https://maximo-server/maximo/oslc/script/sql/[OBJECT_NAME]?sql=[WHERE_CLAUSE]&parentMbo=[PARENT_OBJECT_NAME]&parentId=[PARENT_UNIQUE_ID]For example if I wanted to get the asset information for work order 1022 in the Maximo demo database, the unique Id as found in the WORKORDERID attribute is 202 so the request would look like the following:
https://maximo-server/maximo/oslc/script/sql/asset?sql=assetnum=:assetnum&parentMbo=workorder&parentId=202If you are need to use special characters like & or % if your query then make sure you encode them as described here:
https://www.w3schools.com/tags/ref_urlencode.aspHere is the script. Standard disclaimer, I am providing it as-is and for development and educational purposes only. It should not be applied to production environments.
MXServer = Java.type("psdi.server.MXServer");
MXException = Java.type("psdi.util.MXException");
main();
function main() {
var isHttpRequest = typeof request != 'undefined';
// this is only supported when invoked from a web request.
if (!isHttpRequest) {
return;
}
var objectName = __getRequestObject();
if (!objectName) {
var httpRequest = request.getHttpServletRequest();
var port = httpRequest.getServerPort();
var url = (httpRequest.isSecure() ? "https" : "http") + "://" + httpRequest.getServerName() + ((port != 80 && port != 443) ? ":" + port : "") + httpRequest.getServletContext().getContextPath() + "/oslc/script/sql/[OBJECT_NAME]";
var error = { "status": "error", "reason": "The object name could not be determined. Call this script with " + url + ". Where [OBJECT_NAME] is the name of the Maximo object." };
responseBody = JSON.stringify(error);
return;
}
var sql = request.getQueryParam("sql");
if (!sql || sql.trim() == '') {
var httpRequest = request.getHttpServletRequest();
var port = httpRequest.getServerPort();
var url = (httpRequest.isSecure() ? "https" : "http") + "://" + httpRequest.getServerName() + ((port != 80 && port != 443) ? ":" + port : "") + httpRequest.getServletContext().getContextPath() + "/oslc/script/sql/" + objectName + "?sql=[YOUR_SQL_HERE]";
var error = { "status": "error", "reason": "The object name could not be determined. Call this script with " + url + ". Where [YOUR_SQL_HERE] is the SQL you want to test." };
responseBody = JSON.stringify(error);
return;
}
var result = {};
var records = [];
var max = 1000;
if (request.getQueryParam("maxCount")) {
max = request.getQueryParam("maxCount");
}
var parentMbo;
var parentId;
if (request.getQueryParam("parentMbo")) {
parentMbo = request.getQueryParam("parentMbo");
}
if (request.getQueryParam("parentId")) {
parentId = request.getQueryParam("parentId");
}
var mboSet;
var parentSet;
try {
if (parentMbo && parentId) {
parentSet = MXServer.getMXServer().getMboSet(parentMbo, userInfo);
var mbo = parentSet.getMboForUniqueId(parentId);
if (mbo) {
mboSet = mbo.getMboSet("$" + objectName.toLowerCase(), objectName, sql);
} else {
error = { "status": "error", "reason": "A record was not found for " + parentMbo + " with a unique Id " + parentId };
responseBody = JSON.stringify(error);
return;
}
} else {
mboSet = MXServer.getMXServer().getMboSet(objectName, userInfo);
mboSet.setWhere(sql);
}
var mbo = mboSet.getMbo(0);
var i = 0;
while (mbo && i++ < max) {
records.push(___serializeMbo(mbo));
mboSet.remove(0);
mbo = mboSet.getMbo(0);
}
result.count = mboSet.count();
result.records = records;
responseBody = JSON.stringify(result);
return;
} catch (error) {
if (error instanceof MXException) {
var error = { "status": "error", "reason": error.getDisplayMessage() };
} else {
var error = { "status": "error", "reason": error.getMessage() };
}
} finally {
___close(mboSet);
___close(parentSet);
}
}
function ___serializeMbo(mbo) {
var mboSetInfo = mbo.getThisMboSet().getMboSetInfo();
var attributes = mboSetInfo.getAttributes();
var object = {};
while (attributes.hasNext()) {
var attribute = attributes.next();
object[attribute.getAttributeName()] = mbo.getString(attribute.getAttributeName());
}
return object;
}
function __getRequestObject() {
var httpRequest = request.getHttpServletRequest();
var requestURI = httpRequest.getRequestURI();
var contextPath = httpRequest.getContextPath();
var resourceReq = requestURI;
if (contextPath && contextPath !== '') {
resourceReq = requestURI.substring(contextPath.length());
}
if (!resourceReq.startsWith("/")) {
resourceReq = "/" + resourceReq;
}
var isOSLC = true;
if (!resourceReq.toLowerCase().startsWith('/oslc/script/' + service.scriptName.toLowerCase())) {
if (!resourceReq.toLowerCase().startsWith('/api/script/' + service.scriptName.toLowerCase())) {
return null;
} else {
osOSLC = false;
}
}
var baseReqPath = isOSLC ? '/oslc/script/' + service.scriptName : '/api/script/' + service.scriptName;
var action = resourceReq.substring(baseReqPath.length);
if (action.startsWith("/")) {
action = action.substring(1);
}
if (!action || action.trim() === '') {
return null;
}
return action.toLowerCase();
}
function ___close(mboSet) {
if (mboSet) {
try {
mboSet.close();
mboSet.cleanup();
} catch (ignored) { }
}
}
var scriptConfig = {
"autoscript": "SQL",
"description": "SQL Testing Script",
"version": "1.0.0",
"active": true,
"logLevel": "INFO"
};
------------------------------
Jason VenHuizen
https://sharptree.iohttps://opqo.io------------------------------
Original Message:
Sent: Fri December 30, 2022 06:20 AM
From: Craig Kokay
Subject: Is there a suggested site to build or learn about Maximo Where Clauses
Hi all,
My journey with SQL started with attending a SQL course run by Oracle and then writing many reports using the Oracle PL/SQL book as my bible combined with a printout of the MAXATTRIBUTES table which you may hear referred to as the data dictionary. Be careful as there are two data dictionaries, I am referring to Maximo's, not the database. Your other friends will be ALT+F1 or ALT+I to get the field help and information, then the WhereClauses build up through searching.
Again, be careful as the SQL that is generated from the Query By Example (QBE) search is not always optimised. You may have to use an external SQL query tool like DBeaver, SQL Analyzer, or the like to run an Explain Plan which tells you how efficient your SQL is.
I do agree that you should not write your code with ORGID=:ORGID and SITEID=:SITEID. Why? Normally when you are involving the SITEID, the indexes are optimised to use that and when you use ORGID it will force a full table scan, which can be bad.
I've run a number of beginner SQL courses that PSDI designed that aligned with Maximo, but the whole thing is that you don't need Maximo to learn SQL. It just really helps as you are designing the queries for reporting anyway.
As always, I'm happy to help.
------------------------------
===============================
Craig Kokay,
Lead Senior Maximo/IoT Consultant
ISW
Sydney, NSW, Australia
Ph: 0411-682-040
=================================
#IBMChampion2022
Original Message:
Sent: Thu December 22, 2022 03:17 PM
From: Chelsea McCabe
Subject: Is there a suggested site to build or learn about Maximo Where Clauses
Looking for recommended sites for where clause testing and labs. We use where clauses a lot in our references to our internal clients and I want good resources to test SQL statements and where clauses for the Maximo applications.
Thank you for any support you can provide! Good with the application, not as advanced with languages, yet.
------------------------------
Chelsea McCabe
------------------------------
#Maximo
#AssetandFacilitiesManagement