SQL error on template dataset using custom property in the where clause

Ignition
1

I get the following error in the dataset when I open the template in designer mode or the form using the template is opened in user mode. The dataset uses a custom property in the where clause for ScaleNo. The custom property isn’t populated before the dataset query is run and that causes the SQL syntax error.

Error running query:
SQLQuery(query=SELECT RunNo
, VarietyName
, LEFT(BlockID, 4) AS LotNo
, BlockName AS LotName
, GradeName
, SizeName
, ProductType
FROM TrnQcifyScaleConfig
WHERE PacklineName = ''
AND ScaleNo = AND
ActiveConfigYN = 'Y', database=)@30000ms
On: tQcifyData.tQcifyData.dsCurrLot

Here is the dataset SQL query:

SELECT RunNo
, VarietyName
, LEFT(BlockID, 4) AS LotNo
, BlockName AS LotName
, GradeName
, SizeName
, ProductType
FROM TrnQcifyScaleConfig
WHERE PacklineName = '{tQcifyData.PacklineName1}'
AND ScaleNo = {tQcifyData.ScaleNo1} AND
ActiveConfigYN = 'Y'

Is there any way to delay updating this query until the integer custom property ScaleNo1 is populated? Or is there some syntax in the SQL that would convert the blank value to zero (ISNULL still throws an error due to invalid syntax)? Or anything else to prevent this error when the window is opened? It causes the screen to turn pink when the window is opened, it quickly clears, but makes the user feel like there is something wrong.

2

Tip: rather than format each line of code individually, select the whole block and use the </> button. It gives syntax highlighting too. Please see Wiki - how to post code on this forum.

3

Try,
AND ScaleNo = COALESCE({tQcifyData.ScaleNo1}, 0)

On a separate note, you're passing parameters as QueryStrings. These are vulnerable to SQL injection. Use Value instead. Your query then becomes

SELECT	RunNo
, VarietyName
, LEFT(BlockID, 4) AS LotNo
, BlockName AS LotName
, GradeName
, SizeName
, ProductType
FROM	TrnQcifyScaleConfig
WHERE	PacklineName = :tQcifyData.PacklineName1
AND ScaleNo = COALESCE(:tQcifyData.ScaleNo1, 0)
AND ActiveConfigYN = 'Y'

I'd say the ActiveConfigYN column should be a boolean, not a string.

4

Pretty sure you want to remove the apostrophes around :tQcifyData.PacklineName1

It should automatically handle that as long as the parameter has the string data type.

SELECT	RunNo
, VarietyName
, LEFT(BlockID, 4) AS LotNo
, BlockName AS LotName
, GradeName
, SizeName
, ProductType
FROM	TrnQcifyScaleConfig
WHERE	PacklineName = :tQcifyData.PacklineName1
AND ScaleNo = COALESCE(:tQcifyData.ScaleNo1, 0)
AND ActiveConfigYN = 'Y'
1 Like
5

Oops! I noticed that and forgot to remove them. Fixed! Thanks.

6

The dataset custom property is not using a named query (maybe that is the solution). It is just using the SQL statements in the dataset property

image
image776×711 51.5 KB

The problem is not with the alpha field, it is with the ScaleNo1 custom property field. It evaluates to blank and even with COALESCE (like with ISNULL) it gives a syntax error.

SQLQuery(query=SELECT RunNo
, VarietyName
, LEFT(BlockID, 4) AS LotNo
, BlockName AS LotName
, GradeName
, SizeName
, ProductType
FROM TrnQcifyScaleConfig
WHERE PacklineName = ''
**AND ScaleNo = COALESCE(, 0) AND**
ActiveConfigYN = 'Y', database=)@30000ms
On: tQcifyData.tQcifyData.dsCurrLot

7

Is your ScaleNo1 property a string not an integer?

8

ScaleNo1 is integer.

It works without the error using the named query and passing the properties to the named query. Thanks for steering me in the right direction, this issue has been resolved.

9

Please see Wiki - how to post code on this forum.

10 
SELECT	RunNo
		, VarietyName
		, LEFT(BlockID, 4) AS LotNo
		, BlockName AS LotName
		, GradeName
		, SizeName
		, ProductType
FROM	TrnQcifyScaleConfig
WHERE	PacklineName = :PacklineName
		AND ScaleNo = :ScaleNo AND
		ActiveConfigYN = 'Y'

Thank you, I appreciate the help.