# Using an MySQL script in a Gateway Event

**URL:** <https://forum.inductiveautomation.com/t/using-an-mysql-script-in-a-gateway-event/83444>\
**Category:** Ignition\
**Created:** [December 21, 2023, 7:49pm UTC](https://forum.inductiveautomation.com/t/using-an-mysql-script-in-a-gateway-event/83444 "2023-12-21T19:49:20Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![ahawes](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ahawes/32/36337_2.png) [@ahawes](https://forum.inductiveautomation.com/u/ahawes)\
**Post date:** [December 21, 2023, 7:49pm UTC](https://forum.inductiveautomation.com/t/using-an-mysql-script-in-a-gateway-event/83444/1 "2023-12-21T19:49:20Z")

</div>

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.

```auto
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';

```

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [December 21, 2023, 7:54pm UTC](https://forum.inductiveautomation.com/t/using-an-mysql-script-in-a-gateway-event/83444/2 "2023-12-21T19:54:51Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![ahawes](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ahawes/32/36337_2.png) [@ahawes](https://forum.inductiveautomation.com/u/ahawes)\
**Post date:** [December 21, 2023, 8:01pm UTC](https://forum.inductiveautomation.com/t/using-an-mysql-script-in-a-gateway-event/83444/3 "2023-12-21T20:01:01Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [December 21, 2023, 8:46pm UTC](https://forum.inductiveautomation.com/t/using-an-mysql-script-in-a-gateway-event/83444/4 "2023-12-21T20:46:28Z")

</div>

> [@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.

---

<div class="post-metadata">

**Author:** ![nicholas.robinson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nicholas.robinson/32/22188_2.png) [@nicholas.robinson](https://forum.inductiveautomation.com/u/nicholas.robinson)\
**Post date:** [December 21, 2023, 9:14pm UTC](https://forum.inductiveautomation.com/t/using-an-mysql-script-in-a-gateway-event/83444/5 "2023-12-21T21:14:04Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![ahawes](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ahawes/32/36337_2.png) [@ahawes](https://forum.inductiveautomation.com/u/ahawes)\
**Post date:** [December 22, 2023, 12:22pm UTC](https://forum.inductiveautomation.com/t/using-an-mysql-script-in-a-gateway-event/83444/6 "2023-12-22T12:22:07Z")

</div>

> [@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.

---

<div class="post-metadata">

**Author:** ![ahawes](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ahawes/32/36337_2.png) [@ahawes](https://forum.inductiveautomation.com/u/ahawes)\
**Post date:** [December 22, 2023, 1:19pm UTC](https://forum.inductiveautomation.com/t/using-an-mysql-script-in-a-gateway-event/83444/7 "2023-12-22T13:19:16Z")

</div>

Thanks for helping.

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

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/3/632b65cc84dbcdbe64f4826356f2af74a038470a.png)

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [December 22, 2023, 1:34pm UTC](https://forum.inductiveautomation.com/t/using-an-mysql-script-in-a-gateway-event/83444/8 "2023-12-22T13:34:09Z")

</div>

contain the actual query in triple quotes like this

```auto
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)](https://docs.inductiveautomation.com/display/DOC81/System+Functions)

---

<div class="post-metadata">

**Author:** ![ahawes](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ahawes/32/36337_2.png) [@ahawes](https://forum.inductiveautomation.com/u/ahawes)\
**Post date:** [December 22, 2023, 2:21pm UTC](https://forum.inductiveautomation.com/t/using-an-mysql-script-in-a-gateway-event/83444/9 "2023-12-22T14:21:35Z")

</div>

> [@dkhayes117](#):
>
> anual is your friend when using system functions.

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

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [December 22, 2023, 2:24pm UTC](https://forum.inductiveautomation.com/t/using-an-mysql-script-in-a-gateway-event/83444/10 "2023-12-22T14:24:23Z")

</div>

Feel free to keep asking questions 🙂

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [December 22, 2023, 2:31pm UTC](https://forum.inductiveautomation.com/t/using-an-mysql-script-in-a-gateway-event/83444/11 "2023-12-22T14:31:48Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![ahawes](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ahawes/32/36337_2.png) [@ahawes](https://forum.inductiveautomation.com/u/ahawes)\
**Post date:** [December 22, 2023, 2:51pm UTC](https://forum.inductiveautomation.com/t/using-an-mysql-script-in-a-gateway-event/83444/12 "2023-12-22T14:51:25Z")

</div>

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?

```auto
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';"""

```

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [December 22, 2023, 3:23pm UTC](https://forum.inductiveautomation.com/t/using-an-mysql-script-in-a-gateway-event/83444/13 "2023-12-22T15:23:36Z")

</div>

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

```auto
'AtronixDatabase'

```

1. 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](https://www.w3schools.com/python/python_intro.asp) to get the basics down, which will help you tremendously with scripting in Ignition.

Your script should be more like this

```auto
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

```

---

<div class="post-metadata">

**Author:** ![nicholas.robinson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nicholas.robinson/32/22188_2.png) [@nicholas.robinson](https://forum.inductiveautomation.com/u/nicholas.robinson)\
**Post date:** [December 22, 2023, 6:23pm UTC](https://forum.inductiveautomation.com/t/using-an-mysql-script-in-a-gateway-event/83444/14 "2023-12-22T18:23:24Z")

</div>

@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:

```auto
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:

```auto
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:

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/a/3a00dd07d35fc7432469b509f3a755a04e574417.png)

And the parameters on the UDT look like this:

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/5/8/58a1047a88fa20e9e615e7cb46e9e3a320306325.png)

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

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [December 22, 2023, 7:05pm UTC](https://forum.inductiveautomation.com/t/using-an-mysql-script-in-a-gateway-event/83444/15 "2023-12-22T19:05:05Z")

</div>

> [@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.

---

<div class="post-metadata">

**Author:** ![nicholas.robinson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nicholas.robinson/32/22188_2.png) [@nicholas.robinson](https://forum.inductiveautomation.com/u/nicholas.robinson)\
**Post date:** [December 22, 2023, 7:13pm UTC](https://forum.inductiveautomation.com/t/using-an-mysql-script-in-a-gateway-event/83444/16 "2023-12-22T19:13:27Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [December 22, 2023, 7:21pm UTC](https://forum.inductiveautomation.com/t/using-an-mysql-script-in-a-gateway-event/83444/17 "2023-12-22T19:21:15Z")

</div>

> [@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.