Maximo

 View Only
Expand all | Collapse all

DATABASE ERROR MESSAGE WHEN RUNNING THE SCRIPT TO ADD NEW RECORDS

  • 1.  DATABASE ERROR MESSAGE WHEN RUNNING THE SCRIPT TO ADD NEW RECORDS

    Posted Tue July 18, 2023 05:24 AM

    Hello all,

    I developed a script to either update an existing timesheet or add a new period of timesheet. In the script I convert all dates attributes into a string before to use them in the Set Where statement. BTY, the script is working but at  some point generate the following error because of "Adding records" and I don't know why ? 

    Here is the complete script :

    from psdi.server import MXServer

    from psdi.mbo import Mbo

    from java.util import Calendar

    from java.util import Date

    from psdi.app.common import DateUtility

    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

    from java.time.format import DateTimeFormatter

    from java.util import Date, Locale

    mAmsActions = MXServer.getMXServer().getMboSet("ZZAMSACTIONS", mbo.getUserInfo())

    maximo = MXServer.getMXServer()

    mUser = maximo.getUserInfo(user)

    mAmsActions.moveFirst()

    mGap = mAmsActions.getString("ACTIONS")

    mFrom = mAmsActions.getString("PERIOD_FROM")

    mFromDate = Date(mFrom)

    mTo = mAmsActions.getString("PERIOD_TO")

    mToDate = Date(mTo)

    mDept = mAmsActions.getString("DEPARTMENT")

    mVendor = mAmsActions.getString("COMPANY")

    mPunchin = mAmsActions.getString("PUNCH_IN")

    mPunchout = mAmsActions.getString("PUNCH_OUT")

    mFingerno = mAmsActions.getString("FINGERNO")

    mCal = Calendar.getInstance()

    mCurrDate = mCal.getTime()

    mCreatedate = str(mCurrDate)

    mCreateby = "maxadmin"

    mxServer = MXServer.getMXServer()

    mTS = MXServer.getMXServer().getMboSet("ZZAMSTIMECARD", mbo.getUserInfo())

    mEMPTBL = MXServer.getMXServer().getMboSet("ZZAMSEMP", mbo.getUserInfo())

    strt = Calendar.getInstance()

    end=Calendar.getInstance()

    #

    startdt = mFromDate

    strt.setTime(startdt)

    enddt = mToDate

    end.setTime(enddt)

    enddt=end.getTime()

    #

    mFingerno='5393'

    if mFingerno == '':

        errorgroup = "AMSTSCHANGENO"

        errorkey = "AMSTSCHANGENO"

        # Do it for all employee

        mEmptable = mEMPTBL.moveFirst()

        while mEmptable:

            mFingerno = mEMPTBL.getString("FINGERNO")

            mAmsActions.setValue("FINGERNO",mFingerno)

            mAct = mAmsActions.save()

            #mAmsActions.setValue("DEPARTMENT",str(strt.getTime().date()))

            while strt.getTime().before(enddt):

                mTsday = str(strt.getTime())

                mTS.setWhere("FINGERNO='" + mFingerno + "' and str(TS_DAY)='" + mTsday +"'");   

                #mTSS = mTS.getMboSet("$NewWO","ZZAMSTIMECARD","FINGERNO='" + mFinger + "' and str(TS_DAY)='" + str(strt.getTime()) +"'");

                if mTS:

                    mTIMESHEET = mTS.add()

                    mTIMESHEET.setValue("TS_DAY",strt.getTime())

                    mTIMESHEET.setValue("FINGERNO",mFingerno)

                    mTIMESHEET.setValue("PUNCH_IN",mPunchin)

                    mTIMESHEET.setValue("PUNCH_OUT",mPunchout)

                    mTIMESHEET.setValue("GAP_ACTION",mGap)

                    mTIMESHEET.setValue("CREATE_DATE",mCreatedate)

                    mTIMESHEET.setValue("CREATED_BY",mCreateby)

                    mTIMESHEET.setValue("DESCRIPTION","*****************")

                else:

                    mTS.setValue("PUNCH_IN",mPunchin)

                    mTS.setValue("PUNCH_OUT",mPunchout)

                    mTS.setValue("GAP_ACTION",mGap)

                    mTS.setValue("CHANGE_DATE",mCreatedate)

                    mTS.setValue("CHANGE_BY",mCreateby)

                mTIMESHEET = mTS.save()   

                strt.add(Calendar.DATE,1)

            mEmptable = mEMPTBL.moveNext()

    else:

        # Do it for one Employee (the one who is entered by the user)

        while strt.getTime().before(enddt):

            #mTSS = mTS.getMboSet("$NewWO","ZZAMSTIMECARD","FINGERNO='" + mFinger + "' and str(TS_DAY)='" + strt.getTime() +"'");

            mTsday = str(strt.getTime())

            mTS.setWhere("FINGERNO='" + mFingerno + "' and str(TS_DAY)='" + mTsday +"'");   

            if mTS:

                mTIMESHEET = mTS.add()

                mTIMESHEET.setValue("TS_DAY",strt.getTime())

                mTIMESHEET.setValue("FINGERNO",mFingerno)

                mTIMESHEET.setValue("PUNCH_IN",mPunchin)

                mTIMESHEET.setValue("PUNCH_OUT",mPunchout)

                mTIMESHEET.setValue("GAP_ACTION",mGap)

                mTIMESHEET.setValue("CREATE_DATE",mCreatedate)

                mTIMESHEET.setValue("CREATED_BY",mCreateby)

                mTIMESHEET.setValue("DESCRIPTION","*****************")

            else:

                mTS.setValue("PUNCH_IN",mPunchin)

                mTS.setValue("PUNCH_OUT",mPunchout)

                mTS.setValue("GAP_ACTION",mGap)

                mTS.setValue("CHANGE_DATE",mCreatedate)

                mTS.setValue("CHANGE_BY",mCreateby)

            mTIMESHEET = mTS.save()   

            strt.add(Calendar.DATE,1)



    ------------------------------
    mohammad moula
    ------------------------------

    #MaximoIntegrationandScripting
    #Maximo


  • 2.  RE: DATABASE ERROR MESSAGE WHEN RUNNING THE SCRIPT TO ADD NEW RECORDS

    Posted Fri July 28, 2023 01:20 AM

    HI ,

    Looks like there is an unique index on the object which is causing the error. Please Check the Object and Its Indexes before writing any custom scripts.



    ------------------------------
    Subhransu Sekhar Sahoo
    ------------------------------

    Original Message