TS_DAY is a date column in the database.
Original Message:
Sent: Thu July 13, 2023 03:55 AM
From: Mark Robbins
Subject: I cannot read records from result set of SQL in Automation Script
hi.
What error do you see when you use?
mTSDAY = str(rs.getDate("TS_DAY"))
please put in the stack trace as well.
currently you are trying to use
mTSDAY = str(rs.getDate("TS_DAY"))
Is the TS_DAY column stored as a date in the database?
Is there a value in the TS_DAY column for the record that you are trying to process?
If there is no value then the str( ) is likely to throw an exception because it is trying to convert a null into a string.
If you really want the date as a string then the best bet would be to retrieve the date as a Date object e.g. mydate = rs.getDate("TS_DAY")
then ,check if the object is not none. If the object has a value then use one of the date related methods to convert it to a string.. that way you can also apply the date format that is used to produce the string value
some general thoughts on the rest of the script
mBIO = MXServer.getMXServer().getMboSet("ZZICLOCK", mbo.getUserInfo())
mxServer = MXServer.getMXServer()
^^ you don't need this because you have already retrieved an instance of the Maximo server and stored in it in the mxServer variable earlier
conn.commit()
you don't need to have the commit statement in there because you haven't executed any SQL to perform any changes.
while (rs.next()):
this is correct.
rs is a variable containing a pointer to the recordset.
rs will always have a value if the SQL is executed successfully.... so using while (rs is not None) will cause it to enter an endless loop.
using rs.next() causes the script to loop through each of the records in the resultset.
you need to modify your script so it uses a finally block so you can always free up the database connection that you are using.
mbo.getThisMboSet().getMboServer().freeDBConnection(ConnectionKey conKey)
if you don't free up the connection then the DB connection will be leaked and eventually you will experience problems with large numbers of inactive DB connections and potentially memory leaks.
I discuss how to spot memory leaks in this article:
https://www.linkedin.com/pulse/db-connections-mbosets-understanding-which-piece-code-mark-robbins/
I see you have used isEmpty....
you are adding mbos to the mboset if it is empty so that is ok.
be aware though that this method can cause memory leaks.... I explain why it can cause a leak and how to avoid it here:
https://www.linkedin.com/pulse/how-mbosetisempty-can-cause-memory-leak-use-instead-mark-robbins/
------------------------------
Mark Robbins
Support Lead/Technical Design Authority / IBM Champion 2017 & 2018 & 2019 & 2020 & 2021
Vetasi Limited
https://www.linkedin.com/pulse/maximo-support-advice-from-non-ibm-engineer-article-mark-robbins/
Original Message:
Sent: Wed July 12, 2023 01:06 AM
From: mohammad moula
Subject: I cannot read records from result set of SQL in Automation Script
Hello all,
Following is a script that I developed to fetch the a certain records using SQL and then I want to read record by record to the end of result set "RS". I have a problem that I do not get any error but there is no impact of the process as the script does not do any modification.
NOTE: When I use " while rs is not None:" instead of ""while (rs.next())" then
it seems it is get into the loop but then I've an error at "mTSDAY = str(rs.getDate(TS_DAY))"
Any hep much appreciated.
from java.util.concurrent import TimeUnit
from java.lang import Math
from java.text import SimpleDateFormat
from psdi.security import ConnectionKey
from java.sql import Connection
from java.sql import Statement
mxServer = MXServer.getMXServer()
mTS = MXServer.getMXServer().getMboSet("ZZAMSTIMECARD", mbo.getUserInfo())
mBIO = MXServer.getMXServer().getMboSet("ZZICLOCK", mbo.getUserInfo())
mxServer = MXServer.getMXServer()
connKey = mbo.getThisMboSet().getUserInfo().getConnectionKey()
conn = mbo.getThisMboSet().getMboServer().getDBConnection(connKey)
sql = "SELECT DISTINCT FINGERNO, TS_DAY, TS_YEAR, TS_MONTH, PUNCH_IN, PUNCH_OUT, TSDAY_CHAR FROM (SELECT A.EMP_CODE FINGERNO, TRUNC(A.PUNCH_TIME) TS_DAY, EXTRACT(YEAR FROM PUNCH_TIME) TS_YEAR, EXTRACT(MONTH FROM PUNCH_TIME) TS_MONTH, SUBSTR(TO_CHAR ((SELECT MIN(PUNCH_TIME) FROM ZZICLOCK B WHERE A.EMP_CODE=B.EMP_CODE AND TRUNC(A.PUNCH_TIME)=TRUNC(B.PUNCH_TIME)),'DD/MM/YYYY HH24:MI:SS'),12,5) PUNCH_IN, SUBSTR(TO_CHAR ((SELECT MAX(PUNCH_TIME) FROM ZZICLOCK B WHERE A.EMP_CODE=B.EMP_CODE AND TRUNC(A.PUNCH_TIME)=TRUNC(B.PUNCH_TIME)),'DD/MM/YYYY HH24:MI:SS'),12,5) PUNCH_OUT,SUBSTR(TO_CHAR ((SELECT MAX(PUNCH_TIME) FROM ZZICLOCK B WHERE A.EMP_CODE=B.EMP_CODE AND TRUNC(A.PUNCH_TIME)=TRUNC(B.PUNCH_TIME)),'DD/MM/YYYY HH24:MI:SS'),1,10) TSDAY_CHAR FROM ZZICLOCK A where trunc(a.punch_time)>=(select max(TRUNC(ts_day)) from zzamstimecard)) order by FINGERNO, ts_day"
s = conn.createStatement()
rs = s.executeQuery(sql)
conn.commit()
while (rs.next()):
mTSDAY = str(rs.getDate("TS_DAY"))
#mTS.setWhere("TS_DAY=:rs.ts_day and FINGERNO=:rs.fingerno")
mTSS = mTS.getMboSet("$NewWO","ZZAMSTIMECARD","FINGERNO='" + rs.getString("FINGERNO") + "'");
if mTS.isEmpty():
mTIMESHEET = mTS.add()
mTIMESHEET.setValue("TS_DAY",rs.getDate("TS_DAY"))
mTIMESHEET.setValue("FINGERNO",rs.getString("FINGERNO"))
mTIMESHEET.setValue("PUNCH_IN",rs.getString("PUNCH_IN"))
mTIMESHEET.setValue("PUNCH_OUT",rs.getString("PUNCH_OUT"))
else:
mTS.setValue("PUNCH_IN",rs.getString("PUNCH_IN"))
mTS.setValue("PUNCH_OUT",rs.getString("PUNCH_OUT"))
mTIMESHEET = mTS.save()
------------------------------
mohammad moula
------------------------------
#Maximo
#MaximoIntegrationandScripting