Ignition - Report module - Script Data Source: Cannot coerce value 'String' into type: class org.python.core.PyString

Hi,

I have an issue with a string data type in a script.

I have done many changes but I cannot find the solution (with str, Java String, Unicode,…)

In the report module, I have:

  • Primary query, that it is working and it returns 4 rows for an example value.
  • Then, there is a Script Data Source: Here is the script:
def updateData(data, sample):
    # Import system functions
    from java.util import Date
    from com.inductiveautomation.ignition.common.util import DatasetBuilder
    import system
    # --- Initiate logger
    logger = system.util.getLogger("ReportScript")
    # Get the dataset from the primary query
    queryDataset = data["Query_CoolingBatch"].getCoreResults()  # Convert to dataset
    # Get column names from the dataset
    columnNames = queryDataset.getColumnNames()
    logger.info("Primary Query Column Names: " + str(columnNames))
    # Log the first 5 rows properly
    for i in range(min(5, queryDataset.getRowCount())):
        rowData = [queryDataset.getValueAt(i, col) for col in columnNames]  # Extract row as a list
        logger.info("Row {} Data: {}".format(i, rowData))
    #endfor
    # Initialize list to hold tag history data
    historyResults = []
    # Check if primary query returned data
    tagPaths = []
    startTimes = []
    endTimes = []
    if "TagPath" in columnNames and "Start_DT_Local" in columnNames and "End_DT_Local" in columnNames:
        tagPaths = [queryDataset.getValueAt(i, "TagPath") for i in range(queryDataset.getRowCount())]
        startTimes = [queryDataset.getValueAt(i, "Start_DT_Local") for i in range(queryDataset.getRowCount())]
        endTimes = [queryDataset.getValueAt(i, "End_DT_Local") for i in range(queryDataset.getRowCount())]
        # Iterate over the query results to fetch historical data for each batch
        for i in range(len(tagPaths)):
            tagPath = tagPaths[i]
            startDate = startTimes[i] if i < len(startTimes) else None
            endDate = endTimes[i]  if i < len(endTimes) else None
    
            if tagPath and startDate and endDate:  # Ensure valid data
                try:
                    tagHistory = system.tag.queryTagHistory(
                        paths=[tagPath], 
                        startDate=startDate, 
                        endDate=endDate, 
                        returnSize=100,  # Adjust sample size as needed
                        aggregationMode="Average"
                    )
                    # Debugging: Print available columns
                    logger.info("Tag History Columns: " + str(tagHistory.getColumnNames()))
                    # Extract correct tag column name dynamically
                    # --- The TagPaths don't match, so we'll get the proper column name by the number: second after 't_stamp'
                    tagColumnName = tagHistory.getColumnNames()[1]  # Second column after 't_stamp'
                    
                    # Process and store history results
                    for row in range(tagHistory.rowCount):
                        timestamp = tagHistory.getValueAt(row, "t_stamp")   # Timestamp column
                        value = tagHistory.getValueAt(row, tagColumnName)  # Correct tag column name
                        historyResults.append({
                            "t_stamp": timestamp, 
                            "PV_Temperature": value, 
                            "tagPath": tagPath, 
                            "TrolleyStartTime": startDate, 
                            "TrolleyEndTime": endDate
                        })
                    #endfor
                except Exception as e:
                	logger.info("Error fetching history for tag: " + tagPath + "Error:" + str(e)
                #endtry
            #endif
        #endfor
    #endif
    # Convert the history data into a dataset
    from java.util import Date
    from java.lang import Double  # Import Java's Double type (To fix issue with Float)
    from org.python.core import PyString  # Import Jython's PyString - One of the many tries to fix the issue with the tagPath
    builder = DatasetBuilder()
    builder.colNames("t_stamp", "PV_Temperature", "tagPath", "TrolleyStartTime", "TrolleyEndTime")
    builder.colTypes(Date, Double, str, Date, Date)   
    # As many problems are happening with Float: instead of Float: Double
    # As many problems are happening with str: instead of str: unicode; back to str
    
    for row in historyResults:
        logger.info("Row Data Types: t_stamp={}, PV_Temperature={}, tagPath={}, TrolleyStartTime={}, TrolleyEndTime={}".format(
            type(row["t_stamp"]),
            type(row["PV_Temperature"]),
            type(row["tagPath"]),
            type(row["TrolleyStartTime"]),
            type(row["TrolleyEndTime"])
        ))
        if row["PV_Temperature"] is None:
            row["PV_Temperature"] = 0.0  # Assign default value
        #endif
        pvTemperature = Double.valueOf(float(row["PV_Temperature"]))  # Convert to Java Double; Ensure PV_Temperature is a Java Double
        # --- Ensure tagPath is a Unicode string
        # database queries return values with hidden special characters (like non-breaking spaces) - To clean: \u200b = zero-width space, \u00A0 = non-breaking space
        #cleanTagPath = unicode(row["tagPath"]).strip().replace("\u200b", "").replace("\u00A0", "")  
        # Convert tagPath to Java String
        #javaTagPath = String(cleanTagPath)
        # Ensure tagPath is a Unicode object
        #pythonTagPath = unicode(row["tagPath"]).strip().encode("ascii", "ignore")
        #pythonTagPath = unicode(row["tagPath"]).strip().replace("\x00", "")
        # Ensure tagPath is a properly formatted UTF-8 string
        # Ensure tagPath is a properly formatted Java String
        pythonTagPath = PyString(row["tagPath"].strip())  # Convert to PyString
        #javaTagPath = String(pythonTagPath)  # Convert to Java String
        logger.info("Final Data Types - t_stamp: {}, PV_Temperature: {}, tagPath: {}, TrolleyStartTime: {}, TrolleyEndTime: {}".format(
            type(Date(row["t_stamp"].getTime())),
            type(pvTemperature),  # Print actual type of PV_Temperature
            type(pythonTagPath),
            type(Date(row["TrolleyStartTime"].getTime())),
            type(Date(row["TrolleyEndTime"].getTime()))
        ))
        logger.info("Final tagPath: '{}' (Type: {})".format(pythonTagPath, type(pythonTagPath)))
        builder.addRow(
            Date(row["t_stamp"].getTime()),  # Convert java.sql.Timestamp to java.util.Date
            pvTemperature,  # Use correctly converted Java Double
            str(row["tagPath"]).strip(),  # Ensure it's a plain Python string  
            Date(row["TrolleyStartTime"].getTime()),
            Date(row["TrolleyEndTime"].getTime())
        )
    #endfor
    # Store the dataset into the "data" dictionary
    data["TagHistory"] = builder.build()
    # --- troubleshooting:
    # --- verify Query_CoolingBatch output:
    logger.info("Primary Query Columns: " + str(data["Query_CoolingBatch"]))
    logger.info("First 5 rows: " + str(list(data["Query_CoolingBatch"])[:5]))
    # --- check available datasets 
    logger.info("Available datasets: " + str(data.keys()))
    # Check if PV_Temperature_Path exists
    if "TagPath" in data:
        tagPaths = list(data["TagPath"])
        logger.info("First 5 Tag Paths: " + str(tagPaths[:5]))  # Print first 5 tag paths
    else:
        logger.warn("TagPath column is missing from the Primary Query!")
    # --- logger.info("First 5 rows in TagHistory: " + str(data["TagHistory"][:5]))
    # --- Verify that the startDate and endDate are valid
    # logger.info("Start Date: " + str(startDate) + ", End Date: " + str(endDate))
    # --- Check if TagHistory exists and has data
    if "TagHistory" in data:
        dataset = data["TagHistory"]
        rowCount = dataset.rowCount
        logger.info("TagHistory row count: " + str(rowCount))
        logger.info("Tag Paths: " + str(data.get("TagPath", [])))
    
        # Print first 5 rows (if available)
        for i in range(min(5, rowCount)):
            logger.info("Row {}: {}".format(i, dataset.getRowAsList(i)))
        #endfor
    else:
        logger.warn("TagHistory dataset is missing!")
    #endif

This script is generating an error in line 109:

Caused by: org.python.core.PyException: java.lang.ClassCastException: java.lang.ClassCastException: Cannot coerce value '[Plant_Model]xxx/Trolleys/Trolley1/01_Inputs/PV' into type: class org.python.core.PyString

... 71 common frames omitted

Caused by: java.lang.ClassCastException: Cannot coerce value '[Plant_Model]xxx/Trolleys/Trolley1/01_Inputs/PV' into type: class org.python.core.PyString

I have tried to manually entering a tagPath copied from the Tag Browser but it gave me a similar error.
Do you know how could I fix the issue?

What is the type of the object at row["tagPath"]?

print type(row["tagPath"])

Row Data Types: t_stamp=<type 'java.sql.Timestamp'>, PV_Temperature=<type 'float'>, tagPath=<type 'unicode'>, TrolleyStartTime=<type 'java.sql.Timestamp'>, TrolleyEndTime=<type 'java.sql.Timestamp'>

tagPath=<type 'unicode'>

I tried:

builder.colTypes(Date, Double, unicode, Date, Date)

But I have similar errors.

You cannot use python data types with the builder. They must be java types. You need java.lang.String in place of unicode. (Java's strings are unicode.)

2 Likes

Yeah, get rid of the PyString import, you don't need that.

Use java.lang.String for the column type.

The PyString import is from one of the many tries. I have removed it now.

    from java.lang import Double, String  # Import Java's Double type (To fix issue with Float)
    builder = DatasetBuilder()
    builder.colNames("t_stamp", "PV_Temperature", "tagPath", "TrolleyStartTime", "TrolleyEndTime")
    builder.colTypes(Date, Double, String, Date, Date)
	javaTagPath = String(row["tagPath"])  # Force Java String

    builder.addRow(
        Date(row["t_stamp"].getTime()),  # Convert java.sql.Timestamp to java.util.Date
        pvTemperature,  # Use correctly converted Java Double
        javaTagPath,  
        Date(row["TrolleyStartTime"].getTime()),
        Date(row["TrolleyEndTime"].getTime())
    )

But now I have an unrelated issue with indenting (it is breaking my heart). I have tried converting TAB to space (it usually works but not this time), manually fix it (it is not working neither).
Any suggestion about how to fix it?
No major changes in the script... but now it is not happy
And I cannot test the code

pturmel and Kevin.Herron, thank you very much for your help.
The java.lang.String as the column type worked

2 Likes