Friday, January 15, 2021

How to delete obsolete values in ENUMVALUETABLE table X++

Following up this old issue with extensions to enums, I propose to run this class, if you cannot delete added values (CDP* in my case) directly in SQL Studio. I am not sure if it is going to work in PROD. Please use it at your own risk.


class CDPENUMVALUETABLEDelete
{
   
    public static void main(Args _args)
    {
        if(!Box::confirm("Would you like to delete all CDP* values of SysPolicyRuleTypeEnum type in ENUMVALUETABLE? (If not, just print them"))
        {
            CDPENUMVALUETABLEDelete::printRecords();
            return;
        }
        CDPENUMVALUETABLEDelete::deleteRecords();
        info("all related records have been deleted. Synchonize DB now!");
    }

    private static void deleteRecords()
    {
        Connection                      conn;
        SqlStatementExecutePermission   permission;

        str sqlSelect = 
@"SELECT
t.RECID
 from ENUMVALUETABLE as t
 join ENUMIDTABLE
 on enumid = id
 and ENUMIDTABLE.NAME = 'SysPolicyRuleTypeEnum'
 and t.NAME like 'CDP%'";
        str sqlDelete = strFmt("%1 (%2)", "DELETE FROM ENUMVALUETABLE WHERE RECID IN ", sqlSelect);

        permission  = new SqlStatementExecutePermission(sqlDelete);
        conn        = new Connection();
        permission.assert();
        //conn.transactionScopeBegin();
        Statement statement = conn.createStatement();
        
        int result  = statement.executeUpdate(sqlDelete);
        str errText = statement.getLastErrorText();
        if(errText)
        {
            Info(errText);
        }

        // the permissions needs to be reverted back to original condition.
        CodeAccessPermission::revertAssert();
    }

    private static void printRecords()
    {
        Connection                      conn;
        SqlStatementExecutePermission   permission;

        str sqlSelect =
@"SELECT
t.RECID,
t.ENUMID,
t.ENUMVALUE,
t.NAME
 from ENUMVALUETABLE as t
 join ENUMIDTABLE
 on enumid = id
 and ENUMIDTABLE.NAME = 'SysPolicyRuleTypeEnum'
 and t.NAME like 'CDP%'";

        permission  = new SqlStatementExecutePermission(sqlSelect);
        conn        = new Connection();
        permission.assert();

        Statement statement = conn.createStatement();
        
        ResultSet results = statement.executeQuery(sqlSelect);
        str errText = statement.getLastErrorText();
        if(errText)
        {
            Info(errText);
        }

        int enumId;
        str name;
        int i = 1;
        while (results.next())
        {
            enumId  = results.getInt(3);
            name    = results.getString(4);
            Info(strFmt("Found %1) %2 : %3", i, enumId, name));
            i++;
        }
        // the permissions needs to be reverted back to original condition.
        CodeAccessPermission::revertAssert();
    }

}

Do not forget to build your models and full DB sync after!

No comments: