# MySQL SET statement causing error

**URL:** <https://forum.inductiveautomation.com/t/mysql-set-statement-causing-error/10927>\
**Category:** Ignition\
**Created:** [December 16, 2015, 8:37pm UTC](https://forum.inductiveautomation.com/t/mysql-set-statement-causing-error/10927 "2015-12-16T20:37:21Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Polynomial](https://avatars.discourse-cdn.com/v4/letter/p/848f3c/32.png) [@Polynomial](https://forum.inductiveautomation.com/u/Polynomial)\
**Post date:** [December 16, 2015, 8:37pm UTC](https://forum.inductiveautomation.com/t/mysql-set-statement-causing-error/10927/1 "2015-12-16T20:37:21Z")

</div>

The following code throws this error from MySQL:

`caused by GatewayException: ResultSet is from UPDATE. No Data.
	caused by SQLException: ResultSet is from UPDATE. No Data.
`

`SET @prev_value = -1;
SELECT ABS(SUM(a.curr_value - a.lag_value))
FROM
(
	SELECT time_stamp, @prev_value AS lag_value, @prev_value:=value AS curr_value
	FROM site_146_data
	WHERE tank_ndx = 1
	ORDER BY time_stamp
) AS a
WHERE a.curr_value - a.lag_value < 0;`

If I take out the SET statement it will run. Does anyone know of a workaround to this?

I have allowMultiQueries set to true and useAffectedRows set to false in the connection properties to the database.

Thanks!

---

<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 16, 2015, 10:01pm UTC](https://forum.inductiveautomation.com/t/mysql-set-statement-causing-error/10927/2 "2015-12-16T22:01:16Z")

</div>

I don’t think you can fix this in a regular binding – I’m pretty sure Ignition is using java.sql.Statement.executeQuery() under the hood. It only accepts one statement and returns one result set. Ignition uses connection pooling, so two separate queries back to back will almost certainly not use the same connection.  
The simplest solution is to use a database that directly supports [window aggregates](http://www.postgresql.org/docs/9.4/static/functions-window.html) and the lag() function. MySQL is pretty much the only major DB that doesn’t.

That’s the #1 reason I don’t use MySQL, fwiw. 🙂

---

<div class="post-metadata">

**Author:** ![jpark](https://avatars.discourse-cdn.com/v4/letter/j/a88e57/32.png) [@jpark](https://forum.inductiveautomation.com/u/jpark)\
**Post date:** [December 16, 2015, 11:15pm UTC](https://forum.inductiveautomation.com/t/mysql-set-statement-causing-error/10927/3 "2015-12-16T23:15:23Z")

</div>

Try this:

`SELECT ABS(SUM(a.curr_value - a.lag_value))
FROM
(
   SELECT time_stamp, @prev_value AS lag_value, @prev_value:=value AS curr_value
   FROM site_146_data
   WHERE tank_ndx = 1
   ORDER BY time_stamp
) AS a, (SELECT @prev_value = -1) AS b
WHERE a.curr_value - a.lag_value < 0;`

---

<div class="post-metadata">

**Author:** ![Polynomial](https://avatars.discourse-cdn.com/v4/letter/p/848f3c/32.png) [@Polynomial](https://forum.inductiveautomation.com/u/Polynomial)\
**Post date:** [December 17, 2015, 1:17pm UTC](https://forum.inductiveautomation.com/t/mysql-set-statement-causing-error/10927/4 "2015-12-17T13:17:03Z")

</div>

jpark: That version returns null.

```auto
Exception: Conversion error: query returned null, binding needs "double"
```

Unfortunately we’re locked into MySQL by the customer.

---

<div class="post-metadata">

**Author:** ![Polynomial](https://avatars.discourse-cdn.com/v4/letter/p/848f3c/32.png) [@Polynomial](https://forum.inductiveautomation.com/u/Polynomial)\
**Post date:** [December 17, 2015, 6:58pm UTC](https://forum.inductiveautomation.com/t/mysql-set-statement-causing-error/10927/5 "2015-12-17T18:58:35Z")

</div>

This seems to work so far:

`SELECT ABS(SUM(a.curr_value - a.lag_value)) AS Consumption
FROM (
	SELECT time_stamp, @prev_value AS lag_value, @prev_value:=value AS curr_value
	FROM site_146_data
	WHERE tank_ndx = 1
    ORDER BY time_stamp
	) AS a, (SELECT @prev_value := -1) AS b
WHERE a.curr_value - a.lag_value < 0`