Named query insert to table, column always null

[Douglas_Rudd]

I have a named query that updates a table with the results of a QC test. when I run the test in the named query screen all columns update correctly. but when I run the query from a value-changed script. using system.db.runNamedQuery. all the parameters are updated except for one ("FRAME_PIN"). I have confirmed that FRAME_PIN in params object is populated correctly. but the database shows a null for FRAME_PIN.
this is a section of the script.

        framePin = str(system.tag.read("[.]FramePin").value)
        #logger.info("framePin:"+framePin)
        # Prepare the parameters for the named query
        params = {
            "PRODUCT": "", 
            "COST_CENTER": costCenter,
            "MACHINESEQ": machineSeq,
            "SUBSEQ": subSeq,
            "WORK_CENTER": "294",
            "WORK_STATION": "",  # Fill as needed
            "OPERATOR": operator,
            "TOOL_NO": "",  # Fill as needed
            "TOOL_SEQ": "",  # Fill as needed
            "JOB_ID": "",  # Fill as needed
            "TARGET_VALUE": 1.5,  # Ensure numeric values are not quoted
            "MAX_TARGET_VALUE": 2.0,
            "MIN_TARGET_VALUE": 0.0,
            "RESULT_VALUE": gap_measurement_value,  # Use value from resultValue
            "UOM": "mm",
            "MEASUREMENT_TYPE": "Laser",
            "TEST_DESCRIPTION": "Disc Gap Measurement",
            "STATUS": status,
            "FRAME_PIN":framePin
        }
		# Log parameters and confirm query execution
        logger.info("paramas: "+str(params))
        try:
            system.db.runNamedQuery("RU_Gap_Measurment","QCTOOLDATAINSERT", params)
            logger.info("Named query executed successfully.")
        except Exception as e:
            logger.error("Error executing named query: " + str(e))

I have confirmed the correct data type in Oracle (varchar2), as well as the correct spelling of the parameter in the named query.

this is the named query

`insert into QC_TOOL_DATA 
(
entrydate,
PRODUCT,
COST_CENTER,
MACHINESEQ,
SUBSEQ,
WORK_CENTER,
WORK_STATION,
OPERATOR,
TOOL_NO,
TOOL_SEQ,
JOB_ID,
TARGET_VALUE,
MAX_TARGET_VALUE,
MIN_TARGET_VALUE,
RESULT_VALUE,
UOM,
MEASURMENT_TYPE,
TEST_DESCRIPTION,
STATUS,
FRAMEPIN
)
values
(
sysdate,
:PRODUCT,
:COST_CENTER,
:MACHINESEQ,
:SUBSEQ,
:WORK_CENTER,
:WORK_STATION,
:OPERATOR,
:TOOL_NO,
:TOOL_SEQ,
:JOB_ID,
:TARGET_VALUE,
:MAX_TARGET_VALUE,
:MIN_TARGET_VALUE,
:RESULT_VALUE,
:UOM,
:MEASURMENT_TYPE,
:TEST_DESCRIPTION,
:STATUS,
:FRAME_PIN
)`

I am hoping a fresh set of eyes may be able to point out the issue.
thanks

[MMaynard]

Can you show the results of the logger.info()

[pturmel]

@Douglas_Rudd

logger.info("paramas: "+str(params))

Change the end of that to repr(params) so you can see strings as quoted entities in the log. This can show things that aren't otherwise visible.

[Douglas_Rudd]

here is the output in the logs.

paramas: {'RESULT_VALUE': 1.17, 'MEASUREMENT_TYPE': 'Laser', 'TOOL_SEQ': '', 'JOB_ID': '', 'MIN_TARGET_VALUE': 0.0, 'COST_CENTER': 195763, 'WORK_STATION': '', 'FRAME_PIN': 'CBJY2150VSS113806', 'SUBSEQ': '126730.16', 'UOM': 'mm', 'STATUS': 'P', 'TARGET_VALUE': 1.5, 'TEST_DESCRIPTION': 'Disc Gap Measurement', 'OPERATOR': 0, 'PRODUCT': '', 'MACHINESEQ': 126730, 'MAX_TARGET_VALUE': 2.0, 'WORK_CENTER': '294', 'TOOL_NO': ''}

[Douglas_Rudd]

I tried the change and nothings showed up in the logs and the script didnt finish executing? no errors in logs.

logger.info("params: "+ repr(params))

[pturmel]

@Douglas_Rudd

didnt finish executing

That's not good--suggests a circular reference in there somehow.

[Douglas_Rudd]

@pturmel

@Douglas_Rudd

didnt finish executing

That's not good--suggests a circular reference in there somehow.

you lost me .

[pturmel]

I assumed the script still showed as running in the gateway status page for running scripts. If so, that's an infinite loop. If not, then your script has some problem you can't see, possibly because your try-catch clause cannot catch java exceptions.

Issue passing selected row data to a pop-up

Python's Exception class doesn't include the panoply of java exceptions that can occur, particularly any SQLException that JDBC can throw. You should include another except Throwable, t: clause for those, where you include a from java.lang import Throwable above your try:. (Preferably in a project library script where you delegate your event and action scripts, so it can be imported as a persistent global.) Also, note that stringifying an exception loses the traceback, which is a real probl…

[Douglas_Rudd]

here is the log output with the repr(params)

{'RESULT_VALUE': 0.75, 'MEASUREMENT_TYPE': 'Laser', 'TOOL_SEQ': '', 'JOB_ID': '', 'MIN_TARGET_VALUE': 0.0, 'COST_CENTER': 195763, 'WORK_STATION': '', 'FRAME_PIN': 'CBJY2150VSS113806', 'SUBSEQ': '126730.20', 'UOM': 'mm', 'STATUS': 'P', 'TARGET_VALUE': 1.5, 'TEST_DESCRIPTION': 'Disc Gap Measurement', 'OPERATOR': 0, 'PRODUCT': '', 'MACHINESEQ': 126730, 'MAX_TARGET_VALUE': 2.0, 'WORK_CENTER': '294', 'TOOL_NO': ''}

[pturmel]

I'm stumped. I don't have any recent Oracle experience to rely on. Perhaps its time to open a support ticket.

[Douglas_Rudd]

OK I think I may have found the issue. but at the same time I also messed it up.
I have a parent project called planttemplates that I am using for all the perspective templates that I use in other projects.
this project is also where I have all the named queries. what I didn't notice was that I had set this named query to be overridden. and in another project I had made some changes. so the changes I was making in the master query in the planttemplates project was not being used in.

I am now stuck since I cleared the override in the other project. as to why I am getting this error,
Missing IN or OUT parameter at index:: 20

the change value script is tied to a memory tag in a UDT.

this is the new command I am using
system.db.runNamedQuery("PLANTTEMPLATES","QCTOOLDATAINSERT", params)

is it because I am editing the UDT's in the parent project or something?