Actually, what we did was, Execute java code from Eclipse IDE to get fields from the ERP and deployed it on Eclipse tomcat server and expose the REST API url.
Then we consumed this URL in the Maximo automation scripting to get the data and insert it in the respective maximo fields.
What I noticed was, the script we gave to the client worked fine but when the Issue raised from the client and checked the script it was different from what we gave them before.
This is the Script we gave to the client.
from com.ibm.json.java import JSONObject,JSONArray
from java.io import BufferedReader, IOException, InputStreamReader
from java.lang import System, Class, String, StringBuffer
from java.nio.charset import Charset
from java.util import Date, Properties, List, ArrayList
from org.apache.commons.codec.binary import Base64
from org.apache.http import HttpEntity, HttpResponse, HttpVersion
from org.apache.http.client import ClientProtocolException, HttpClient
from org.apache.http.client.entity import UrlEncodedFormEntity
#from org.apache.http.client.methods import HttpPost
from org.apache.http.client.methods import HttpGet
from org.apache.http.entity import StringEntity
from org.apache.http.impl.client import DefaultHttpClient
from org.apache.http.message import BasicNameValuePair
from org.apache.http.params import BasicHttpParams, HttpParams, HttpProtocolParamBean
from psdi.mbo import Mbo, MboRemote, MboSet, MboSetRemote
from psdi.security import UserInfo
from psdi.server import MXServer
from psdi.mbo import MboConstants
from psdi.mbo import Mbo
from sys import *
uri ='http://IPADDRESS:PORT/GetEMPDetails/getPerson'
# get http parameters
params = BasicHttpParams()
paramsBean = HttpProtocolParamBean(params)
paramsBean.setVersion(HttpVersion.HTTP_1_1)
paramsBean.setContentCharset("UTF-8")
paramsBean.setUseExpectContinue(True)
# get client, http headers and request
client = DefaultHttpClient()
request = HttpGet(uri)
request.setParams(params)
#request.addHeader(HttpHeaders.CONTENT_TYPE, "application/x-www-form-urlencoded")
# get client response
response = client.execute(request)
status = response.getStatusLine().getStatusCode()
obj = JSONArray.parse(response.getEntity().getContent())
print obj
def updateperson(userid, supervisor,location,pos,depart,site):
mxServer = MXServer.getMXServer()
userInfo = mxServer.getSystemUserInfo()
personSet = mxServer.getMboSet("PERSON",userInfo)
personSet.setWhere("TRIM('0' from employeeid) = '"+userid+"' fetch first row only")
personSet.reset()
if personSet.count()>0:
personSet.getMbo(0).setValue("SUPERVISOR", supervisor)
if len(site)>0:
personSet.getMbo(0).setValue("LOCATIONSITE", site)
personSet.getMbo(0).setValue("LOCATION",location)
personSet.getMbo(0).setValue("TITLE",pos)
personSet.getMbo(0).setValue("DEPARTMENT",depart)
try:
personSet.save()
print "In save eidiko"+userid
except:
print "error occured "
if status == 200:
errorkey = 'successs'
errorgroup = 'successs'
else:
errorkey = status
errorgroup = status
for x in obj :
sup = x.get("supervisorEmpId")
empidPer = x.get("emp_ID")
pos = x.get("position")
depart = x.get("location")
ld = x.get("locationId")
p_number = x.get("pr_Number")
#Get Supervisor Personid
sup_pid =''
if len(sup)>0:
mxServer = MXServer.getMXServer()
userInfo = mxServer.getSystemUserInfo()
personSet = mxServer.getMboSet("PERSON",userInfo)
personSet.setWhere("TRIM('0' from employeeid) = '"+sup+"' fetch first row only")
personSet.reset()
if personSet.count()>0:
sup_pid = personSet.getMbo (0).getString('personid')
print "In personseteidiko"+sup_pid
siteid=''
locationId=''
#Get Site Id From Location ID
if len(ld)>0:
locationSet = mxServer.getMboSet("LOCATIONS",userInfo)
locationSet.setWhere("BRANCH_CODE = '"+ld+"'")
locationSet.reset()
if locationSet.count()>0:
siteid = locationSet.getMbo (0).getString("SITEID")
locationid = locationSet.getMbo (0).getString("LOCATION")
print "In locationseteidiko"+locationid
if len(empidPer)>0:
updateperson(empidPer,sup_pid,locationid,pos,depart,siteid)
This is modified code by the Client:
from com.ibm.json.java import JSONObject,JSONArray
from java.io import BufferedReader, IOException, InputStreamReader
from java.lang import System, Class, String, StringBuffer
from java.nio.charset import Charset
from java.util import Date, Properties, List, ArrayList
from org.apache.commons.codec.binary import Base64
from org.apache.http import HttpEntity, HttpResponse, HttpVersion
from org.apache.http.client import ClientProtocolException, HttpClient
from org.apache.http.client.entity import UrlEncodedFormEntity
#from org.apache.http.client.methods import HttpPost
from org.apache.http.client.methods import HttpGet
from org.apache.http.entity import StringEntity
from org.apache.http.impl.client import DefaultHttpClient
from org.apache.http.message import BasicNameValuePair
from org.apache.http.params import BasicHttpParams, HttpParams, HttpProtocolParamBean
from psdi.mbo import Mbo, MboRemote, MboSet, MboSetRemote
from psdi.security import UserInfo
from psdi.server import MXServer
from sys import *
uri ='http://IPADDRESS:PORT/GetEMPDetails/getPerson'
# get http parameters
params = BasicHttpParams()
paramsBean = HttpProtocolParamBean(params)
paramsBean.setVersion(HttpVersion.HTTP_1_1)
paramsBean.setContentCharset("UTF-8")
paramsBean.setUseExpectContinue(True)
# get client, http headers and request
client = DefaultHttpClient()
request = HttpGet(uri)
request.setParams(params)
#request.addHeader(HttpHeaders.CONTENT_TYPE, "application/x-www-form-urlencoded")
# get client response
response = client.execute(request)
status = response.getStatusLine().getStatusCode()
obj = JSONArray.parse(response.getEntity().getContent())
print obj
if status == 200:
errorkey = 'successs'
errorgroup = 'successs'
else:
errorkey = status
errorgroup = status
mxserver = MXServer.getMXServer()
user_info = mxserver.getSystemUserInfo()
conn_key = user_info.getConnectionKey()
conn = mbo.getMboServer().getDBConnection(conn_key)
stmt = conn.createStatement()
for x in obj :
sup = x.get("supervisorEmpId")
empidPer = x.get("emp_ID")
pos = x.get("position")
depart = x.get("location")
ld = x.get("locationId")
p_number = x.get("pr_Number")
query = "update PERSON set SUPERVISOR=(select personid from person where TRIM('0' from employeeid) ='"+sup+"' fetch first row only),TITLE ='"+pos+"',DEPARTMENT='"+depart+"' where TRIM('0' from employeeid) ='"+empidPer+"'"
results = stmt.executeUpdate(query)
conn.commit()
#get Location
#locationSet = mxserver.getMboSet('LOCATIONS',user_info)
#locationSet.setWhere("Location = '"+ld+"'")
#location = locationSet.getMbo(0)
#locationdata = location.getString('Location')
#print 'loc',locationdata
#get Person
personSet = mxserver.getMboSet('PERSON',user_info)
personSet.setWhere("employeeid = '"+empidPer+"'")
person = personSet.getMbo(0)
person.setValue('LOCATION',"CONSUMEROF")
stmt.close()
mxserver.getDBManager().freeConnection(conn_key)
I think, the SQL implementation in the modified script is causing the issue.
What are you thoughts Steve?
------------------------------
Harshavardhan Kamepalli
Eidiko System Integrators
------------------------------
Original Message:
Sent: 04-02-2021 08:52
From: Steven Shull
Subject: Error in Syncing with data from ERP system
Did you look for additional entries in the logs around that time? If the Maximo system hasn't been configured to log to an alternate location, it would be in the SystemOut or SystemErr logs of WebSphere. Typically you get more helpful messages around the query, such as the exact query trying to be executed and the more platform friendly error message that Oracle returns (starts with ORA).
I'm not personally familiar with database error -10 and a quick Google search didn't help for Oracle nor DB2. You mentioned the ERP is Oracle, so I assumed Oracle for Maximo but matching platforms isn't required.
------------------------------
Steven Shull
Projetech Inc.
Original Message:
Sent: 04-01-2021 06:42
From: Harshavardhan Kamepalli
Subject: Error in Syncing with data from ERP system
Hi All,
We have a functionality requirement to Sync data between Maximo and ERP(Oracle) system for Person details.
For this we have written Automation script that gets details from the REST service exposed from the ERP system.
It was working fine until today. The problem is, the data does not get synced and when we try to insert data manually, it returns error.
Error Code: 'BMXAA4210E - Database error number -10 has occurred. Invalid SQL queries are logged in the log file. Check the SQL query to identify the error.'
Please check the log File attached.
Any help here is appreciated.
P.S: No changes were made in the script and ERP recently, they are as is before when working fine.
#Customizations
------------------------------
Harshavardhan Kamepalli
Eidiko System Integrators
------------------------------