Using an MySQL script in a Gateway Event

[ahawes]

HI Team,

I am struggling on how to put a script in a gateway event.

The script in mysql pulls a count of all partial pallets from previous day. I only need it to run one time a day at a designated time.

Any help would be great.

SELECT

    COUNT(*)
FROM
    CFG.pallets
LEFT OUTER JOIN
    CFG.material_master
ON
    (CCFG.pallets.sku = CFG_master.sku)
LEFT OUTER JOIN
    CFG.skus
ON
    (CFG.pallets.human_readable = CFG.skus.sku)
WHERE
    CFG.pallets.timestamp  between DATE_ADD(CURRENT_DATE,INTERVAL -20 HOUR)AND DATE_ADD(CURRENT_DATE,INTERVAL -8 HOUR)
AND ((CFG.pallets.quantity) - (CFG.material_master.cases_per_layer * CFG.material_master.layers_per_pallet)) < '0';

[pturmel]

Normally, you'd use a scheduled event script to run such a thing, via system.db.runQuery or system.db.runPrepQuery. What do you want to do with the result dataset?

[ahawes]

The count will be displayed in a label, that is is.

I want to show counts for days, nights, and total. So would be setting up a few and all would go in labels.

And yes, and scheduled event script would be preferred.

[lrose]

@ahawes

The count will be displayed in a label, that is is.

Use the functions as Phil noted, then use system.tag.writeBlocking() to right the value to a memory tag, which you can then bind to your labels.

[nicholas.robinson]

Adding, its convenient to use a named query because the default return is as a dataset which can easily be stored to a memory dataset tag.

But most importantly make sure that any columns in your query where clause are indexed

I'd also do everything in my power to avoid joins.

[ahawes]

@nicholas.robinson

I'd also do everything in my power to avoid joins.

I hear you on the joins. The creators of teh db were very inconsistent and not very good, i agree.

[ahawes]

Thanks for helping.

I am not very good at scripting so I am not sure how to do this, even after watching insructions.

[dkhayes117]

contain the actual query in triple quotes like this

query = """
	select * from 
	join on ...
	join on ...
	where ...
"""
result = system.db.runQuery(query, database='dbNameHere')

The manual is your friend when using system functions.
System Functions - Ignition User Manual 8.1 - Ignition Documentation (inductiveautomation.com)

[ahawes]

@dkhayes117

anual is your friend when using system functions.

Thanks, i will try this. I was referencing the manual but still struggle.

[dkhayes117]

Feel free to keep asking questions

[lrose]

You can't type SQL directly into a script editor (or expression editor) like that. They are not the same language. That is why you are getting a syntax error.

@ahawes

I am not very good at scripting so I am not sure how to do this, even after watching insructions.

It's okay, you're not alone in that boat. As long as you put forth the effort, the users on the forum are more than happy to answer even the most basic of questions.

Don't limit your training to just ignition centric things (such as Inductive University). Don't get me wrong they are great resources, but they make certain assumptions about the end users skill set. Go watch Python videos, much of what you learn there transfers over directly to Ignition.

[ahawes]

Thank you for the advice. I have been taking some training for sql, python, and CS50 class.

How would i write this in a python format. Is that something you could help with?

def onScheduledEvent():
	system.db.runQuery(query,AtronixDatabase)
	query = """
	SELECT

    COUNT(*)
FROM
    Clemens.pallets
LEFT OUTER JOIN
    Clemens.material_master
ON
    (
        Clemens.pallets.sku = Clemens.material_master.sku)
LEFT OUTER JOIN
    Clemens.skus
ON
    (
        Clemens.pallets.human_readable = Clemens.skus.sku)
WHERE
    Clemens.pallets.timestamp  between DATE_ADD(CURRENT_DATE,INTERVAL -20 HOUR)AND DATE_ADD(CURRENT_DATE,INTERVAL -8 HOUR)
AND ((Clemens.pallets.quantity) - (Clemens.material_master.cases_per_layer * Clemens.material_master.layers_per_pallet)) < '0';"""

[dkhayes117]

Scripting is a Jython programming environment (Python with access to Java stuff). The query can be written straight up in a database environment because that is what it expects and has a specific set of key words like select, from, where. Those don't mean anything in Jython.

Jython isn't a query language, so it can't interpret it. That's why you put it in triple quotes, so the Jython environment can use it as a string type that can be on multiple typed lines. Otherwise, regular quotes work for defining strings.

Problems with what you have written:

1. Your database name is AtronixDatabase, that won't work because it's not in quotes, so Jython expects it to be a variable and not a string. Change it to

'AtronixDatabase'

2. You've defined your query string under the variable query but you've defined it after you tried to use it. That won't work.

You really need go through an introduction to python course to get the basics down, which will help you tremendously with scripting in Ignition.

Your script should be more like this

def onScheduledEvent():
	# Write your query in the Jython environment as a multi-line string 
	# and bind it to the variable name `query`
	query = """
		SELECT
	
	    COUNT(*)
		FROM
		    Clemens.pallets
		LEFT OUTER JOIN
		    Clemens.material_master
		ON
		    (
		        Clemens.pallets.sku = Clemens.material_master.sku)
		LEFT OUTER JOIN
		    Clemens.skus
		ON
		    (
		        Clemens.pallets.human_readable = Clemens.skus.sku)
		WHERE
		    Clemens.pallets.timestamp  between DATE_ADD(CURRENT_DATE,INTERVAL -20 HOUR)AND DATE_ADD(CURRENT_DATE,INTERVAL -8 HOUR)
		AND ((Clemens.pallets.quantity) - (Clemens.material_master.cases_per_layer * Clemens.material_master.layers_per_pallet)) < '0';
	"""
	# Bind the db name string to the variable `database`
	database = 'AtronixDatabase'
	
	# Run the query using Ignition's built-in system function and bind the result to the variable `result`
	# database is a key word argument so it is assigned and not just passed in
	result = system.db.runQuery(query,database=database)
	
	# TODO: take the result and do something with it

[nicholas.robinson]

@ahawes if you're still getting accustomed to scripting the method below might be a little bit of a stretch, but it would be my preferred method (just to provide alternatives since I typically do not use gateway even scripts unless there is no other choice).

• Query is defined in named queries
• Execution and data capture is done in a UDT

Using an example that someone wants to get updated data every morning at 0200 hours. In a UDT you make a tag called trigger that is an integer expression of the following:

getHour24(now())

Then you make a change script on that tag that will trigger whenever the hour changes. The paradigm here is that you can, at the head of the change script, choose whether or not to do anything else. Here it will execute when the hour changes from 1 to 2. The query will be executed and the return will be written to the data tag, which is a memory dataset:

def valueChanged(tag, tagPath, previousValue, currentValue, initialChange, missedEvents):
	udt = tag["parameters"]
	if currentValue.value == udt["executeHour"]:
		ds = system.db.runNamedQuery(udt["queryName"])
		system.tag.writeBlocking(["[.]data"], [ds])

The actual UDT definition looks like this:

And the parameters on the UDT look like this:

Then you make an instance of that UDT and name it whatever you like, for example: palletData, provide the actual parameters and it will run once per day.

This is a very simple example just to show the principle of how to leverage UDT's.

rgds,

Nick

[lrose]

@nicholas.robinson

since I typically do not use gateway even scripts unless there is no other choice).

Why?

I avoid tag value changed tags in this way, because they are limited by both scope and the thread pool.

Also, Named Queries are a project resource so when calling them from a pure gateway scope, you must include the Project Name. There might possibly be some resolution back to a default project or the Gateway Scripting Project that allows this to work, but the documentation calls out the project name as not optional.

[nicholas.robinson]

Because I tend to see people defining code in gateway events rather than just making calls. I've opened many a gateway to see a HUGE amount of script defined there and it drives me nuts. But, there is absolutely nothing wrong with defining a script package and making calls to it from a gateway event. As long as all members of a team agree on and utilize common, predictable patterns that is what matters most.

As personal preference, I do not like to write queries in the code itself where it is possible not to do so. Named queries are my preference because the UI provides a great way to test and validate the query (not that it cannot also be done in other ways).

Nick

[lrose]

@nicholas.robinson

Because I tend to see people defining code in gateway events rather than just making calls. I've opened many a gateway to see a HUGE amount of script defined there and it drives me nuts

Better there than in Tag Value Changed events where it can/will lead to missed events and performance issues. Don't get me wrong, they have their place and if done correctly are a wonderful tool, but they have certain pitfalls that you must be aware of or you can get into trouble very quickly.

In both cases Script Libraries should be used judiciously.

@nicholas.robinson

But, there is absolutely nothing wrong with defining a script package and making calls to it from a gateway event. As long as all members of a team agree on and utilize common, predictable patterns that is what matters most.

This I agree with whole heartedly.

@nicholas.robinson

I do not like to write queries in the code itself where it is possible not to do so

I agree with this. If a Named Query can be used, it certainly should, however, there are times when it isn't the correct tool for the Job. I was just pointing out that I don't believe the script you provided will work in all situations because you didn't provide a Project Name.