Named Query Params Error

Ignition
,
1

Hello!

I have a named query that looks for parameters and passes the value to the named query. This is my query below.

UPDATE Fake
SET  WorkOrder_ID = :WOID, Good_Part_Count = :GPC, Total_Part_Count = :TPC, WO_Status = 'S', Finish_Time = getdate()
WHERE WorkOrder_ID = :WOID

My parameter types are value, and the WOID data type is a string while the GDC and TPC is Float4.

The error code I get is this,

File "function:runAction", line 2, in runAction
java.lang.Exception: java.lang.Exception: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ':'.

I assume I am missing something that is rather simple, but cannot get it figured out as to what it is.

Thanks!

2

You haven’t provided the script that is running this query. That may provide more insight into what is wrong.

1 Like
3

My apologies, this is the script.

	system.db.runNamedQuery('Fake_Name', 'Pasting Submit', {"WOID": str(self.getSibling("Label_0").props.text), "TPC": (self.getSibling("Label_20").props.text), "GPC": (self.getSibling("NumericEntryField").props.value)})
4

What is the value of self.getSibling('Label_0').props.text) and self.getSibling('Label_20').props.text?

For instance if you add this line before you call the named query what do you see in the console?

system.perspective.print(self.getSibling('Label_0').props.text)
1 Like
5

I did add that before the named query, It does bring in the Label_0 value, as well as the Label_20 value. It does give a lot more information related to the error code.

6

What are those values?

As you have correctly set the parameters dictionary in the runNamedQuery function, it suggests that the ‘:’ mentioned in the error is being sent as a value of one of the parameters.

There isn’t really anything else it can be, assuming that everything is as you have shown in the query and script.

1 Like
7

That is what confuses me, this query is basically the same as another one I have just without a column. The values are Label_0 = MFG0208870 and the Label_20 Is 100. I may have to get in touch with Ignition and see if they have any solutions as this has been bugging me all day lol.

8

My guess with that error would be that the parameter is misspelled somewhere in your named query as lrose mentioned, here’s an example.

queryerror
queryerror1370×488 22.7 KB

3 Likes
9

Gosh, I am almost ashamed to admit that was the error. Thank you for troubleshooting and finding the solution! Might need to take a break, been staring at the screen for too long lol.

2 Likes
10

Haha no worries, the query editor is where most of my headaches come from as it isn’t the best at telling you what the exact issue is like in this case.

1 Like
11

I completely agree! Have a great rest of your day and thanks again!

1 Like
12

I found that if you have an incorrect data type, say Int4 when it wants an Int8.

MSSQL data type integer, using numbers with six or less digits, I thought Int4 was correct, however I got the same syntax error, but upon changing it to Int8, no error and the query functioned as expected.

1 Like