Named Queries VARCHAR Error

Jack_Jones · May 13, 2024, 1:41pm

I have my code below trying to write into the named query I made

# Retrieve the current values of each variable from their respective tags
paths = [
    "[default]CR Assembly/MDC15/PartSpecificTags/TestTag",
    "[default]CR Assembly/MDC15/PartSpecificTags/StartTime",
    "[default]CR Assembly/MDC15/PartSpecificTags/TempGoodPcs",
    "[default]CR Assembly/MDC15/PartSpecificTags/TempMissingPin",
    "[default]CR Assembly/MDC15/PartSpecificTags/TempPinLoad",
    "[default]CR Assembly/MDC15/PartSpecificTags/TempPlugGage",
    "[default]CR Assembly/MDC15/PartSpecificTags/TempRingGage"
]

data = system.tag.readBlocking(paths)
total_parts = sum(item.value for item in data[2:7])
total_rejects = sum(item.value for item in data[3:7])

params = {
    "PartNumber": data[0].value,
    "StartTime": data[1].value,
    "EndTime": system.date.now(),
    "GoodPcs": data[2].value,
    "MissingPin": data[3].value,
    "PinLoad": data[4].value,
    "PlugGage": data[5].value,
    "RingGage": data[6].value,
    "TotalPartsRan": total_parts,
    "TotalRejects": total_rejects
}

# Run the named query with the parameters
system.db.runNamedQuery("TestQueryForReport", params)`Preformatted text`

Here is my Named Query

And the traceback for the error

I have only been using ignition for a week so forgive me if this seems simple. But I am planning on putting this script in a tag so that I can log data everytime that data changes. However I have this table made and it says I have incorrect syntax near VARCHAR. I know that when doing the .value on the part number it returns a UNICODE so I have initialized the PartNumber column as a NVARCHAR.

Any help would be greatly appreciated.

sebastien_ng · May 13, 2024, 2:11pm

print data[0].value
Maybe you are not getting an varchar value on your script.

JordanCClark · May 13, 2024, 2:13pm

Have you tried inserting using the testing tab?

I'm suspecting something particularly SQLServer-ish, like putting brackets around the column names.

Jack_Jones · May 13, 2024, 2:13pm

This is the output from the read tags and their types

('Value:', u'BN-85139', 'Type:', <type 'unicode'>)
('Value:', Fri May 10 13:00:12 EDT 2024, 'Type:', <type 'java.util.Date'>)
('Value:', 10356, 'Type:', <type 'int'>)
('Value:', 75, 'Type:', <type 'int'>)
('Value:', 107, 'Type:', <type 'int'>)
('Value:', 9, 'Type:', <type 'int'>)
('Value:', 3, 'Type:', <type 'int'>)

Jack_Jones · May 13, 2024, 2:14pm

I have some data inserted from tests ran using the testing tab. However, when scripting I get an error.

Jack_Jones · May 13, 2024, 2:21pm

When inserting it inserts one row as it should and when executing the select query it reads what was inserted.

JordanCClark · May 13, 2024, 2:24pm

Look in the value names of the query and ensure there is are no leading or trailing spaces. That's hung me up once or thrice.

Jack_Jones · May 13, 2024, 2:29pm

There are no leading or trailing spaces. I will drop the table and recreate it since there is no data to be lost.

CREATE TABLE MDC15 (
    PartNumber NVARCHAR(255),
    StartTime DATE,
    EndTime DATE,
    GoodPcs INT,
    MissingPin INT,
    PinLoad INT,
    PlugGage INT,
    RingGage INT,
    TotalPartsRan INT,
    TotalRejects INT
);

Here I went to redo the table with NVARCHAR and when declaring the variable types with the query they usually turn blue. However, NVARCHAR has not. Is there some varaible declaration I could be missing here? When i realized that the tag was returning unicode last I just altered the table instead of drop and create.

JordanCClark · May 13, 2024, 2:30pm

If there are no non-Latin charachters in the part number, I'd probably keep it as varchar.

Jack_Jones · May 13, 2024, 2:32pm

That's it. Somehow the code runs now. I just needed to recreate the table. Thank you

JordanCClark · May 13, 2024, 2:36pm

As a note, although you didn't say what version of SQLServer you're using, as of v2019, unicode is supported natively by VARCHAR.

Transistor · May 13, 2024, 2:41pm

Tip: Correct grammar would require the column to be named "TotalPartsRun", not "...Ran".

The machine ran.
The parts were run.

Jack_Jones · May 13, 2024, 2:48pm

I wasn't informed myself. I'm just an intern with a problem. I do not know anything about the gateway. Just designer.

Jack_Jones · May 13, 2024, 2:49pm

This is true, however to keep consistent with variable names I am using the tags defined before my time.

pascal.fragnoud · May 13, 2024, 6:11pm

Gotta give you credit for clean code, posted properly using the formatting tool, with the error encountered and the stack trace, and marking the solution post.
Well played, I wished this was common enough that it didn't warrant an acknowledgement.
Welcome aboard.

Probably not the best idea.
Depending on what exactly you want to log, you could use the historian, transaction groups, or maybe a gateway event. Putting this kind of operation on tag change scripts (if that was indeed your intention) might give your gateway a serious headache.
Avoid putting any i/o operations there, or anything more than a very basic computation actually.
I try as hard as I can to avoid using tag change scripts actually.

Jack_Jones · May 13, 2024, 6:22pm

I have a Part Number that changes maybe twice every 8 hours on 8 Machines. If every machine changed at the same time, it would be simpler, however there is a schedule that changes daily for needs. I only need this data input into someplace that I can pull it out. My goal is to have a report that gives me the data of exactly what happened during the past 24 hours on all machines. Like I said I've only been using ignition for a week and am not sure the best process for this. Any tips would be greatly appreciated

bschroeder · May 13, 2024, 6:29pm

Rather than a Tag Value Changed Script, use a Gateway Tag Change Script: Gateway Event Scripts | Ignition User Manual.

Jack_Jones · May 13, 2024, 6:34pm

Is the declaration the same system.db.runNamedQuery("Jacks_Project","TestQueryForReport", params)
or
system.db.runNamedQuery("TestQueryForReport", params)

I know one is to specify the named query in the project. Does this also include the gateway event scripts? Thank you.

lrose · May 13, 2024, 6:39pm

You would need the first one since you will be calling the function from a Gateway Scope.

system.db.runNamedQuery("Jacks_Project", "TestQueryForReport", params)

pascal.fragnoud · May 13, 2024, 8:22pm

Don't code in gateway events. Create a function in the script library, and call it from the event.
Gateway events have weird scoping rules and behavior. Just don't take any risk and call library functions.