# MariaDB JBDC type casting question

**URL:** <https://forum.inductiveautomation.com/t/mariadb-jbdc-type-casting-question/77709>\
**Category:** Ignition\
**Tags:** ignition81\
**Created:** [August 10, 2023, 5:34pm UTC](https://forum.inductiveautomation.com/t/mariadb-jbdc-type-casting-question/77709 "2023-08-10T17:34:11Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![justin.brzozoski](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justin.brzozoski/32/13452_2.png) [@justin.brzozoski](https://forum.inductiveautomation.com/u/justin.brzozoski)\
**Post date:** [August 10, 2023, 5:34pm UTC](https://forum.inductiveautomation.com/t/mariadb-jbdc-type-casting-question/77709/1 "2023-08-10T17:34:11Z")

</div>

I've got a simple table in MariaDB kinda like this:

```auto
CREATE TABLE IF NOT EXISTS event_counts_per_group (
  group_id INT NOT NULL,
  day DATE NOT NULL,
  event_count INT NOT NULL DEFAULT 0,
  PRIMARY KEY (group_id, day)
);

```

And I have a query kinda like this:

```auto
SELECT SUM(event_count) FROM event_counts_per_group GROUP BY group_id;

```

But, I'm finding that the column datatype in the returned dataset is a `float` in Ignition.

I looked through the [MySQL JDBC docs on datatype conversions](https://dev.mysql.com/doc/connector-j/8.1/en/connector-j-reference-type-conversions.html) and it still doesn't make sense to me. I'm assuming that MariaDB must be upgrading the SUM() to a float to avoid overflow, but I can't find anyone else complaining about that online.

Since I know my numbers are smaller, I tried using `CAST(SUM(event_count) AS INTEGER)` instead, which changes my results to `long` types. That's better but still overkill. MariaDB doesn't seem to allow`CAST(x AS MEDIUMINT)` or other smaller integer types.

This isn't causing me any serious issues, but I'm curious if anyone could explain why the original SUM() result was being upgraded to float, and also if there is any way to get the datatype of the column in the Ignition result dataset to come back as int instead of long or float.

Yeah, I could re-cast the whole column after getting it back into Ignition, but I feel like there must be a more efficient way I just can't find online.

If it matters, my actual queries are all being done through `system.db.runPrepQuery` with proper `?` parameters, I just showed the plain SQL here for ease of reading.

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [August 10, 2023, 7:09pm UTC](https://forum.inductiveautomation.com/t/mariadb-jbdc-type-casting-question/77709/2 "2023-08-10T19:09:22Z")

</div>

Interesting. Never ran into this before but I am using the MariaDB driver for a MySQL 5.6 and using your table/example I get the same thing, it's coming through as a float, regardless of if I use the group by or not, runPrepQuery vs runQuery vs namedQuery (scalar or not) - all float.

Doing some testing in MySQL Workbench makes me think its not the driver itself though. Check this out if you look at the field types of a regular old `SELECT event_count FROM event_counts_per_group;` you get what you expect, an INT type

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/0/80edb146a9e9c8c2a6d5a99acf2f1ff1cbce8295.png)

But running `SELECT SUM(event_count) FROM event_counts_per_group;` even though the the result looks like an int, now shows it as a DECIMAL

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

So I think then once it comes back to Ignition and the db is saying hey this is a decimal, boom now we got a float. I think.

---

<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:** [August 10, 2023, 7:28pm UTC](https://forum.inductiveautomation.com/t/mariadb-jbdc-type-casting-question/77709/3 "2023-08-10T19:28:19Z")

</div>

Looks like you are correct.

> <https://stackoverflow.com/questions/17006049/mysqldb-return-decimal-for-a-sum-of-int>

Apparently you have to do:

```auto
SELECT kind, CAST(SUM(n) AS SIGNED) n 
  FROM table1 
 GROUP BY kind

```

crazy

---

<div class="post-metadata">

**Author:** ![justin.brzozoski](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justin.brzozoski/32/13452_2.png) [@justin.brzozoski](https://forum.inductiveautomation.com/u/justin.brzozoski)\
**Post date:** [August 10, 2023, 8:59pm UTC](https://forum.inductiveautomation.com/t/mariadb-jbdc-type-casting-question/77709/4 "2023-08-10T20:59:18Z")

</div>

So my `CAST(SUM(x) AS INTEGER)` is about as good as I'm going to get. Weird, but still better than passing a table of floats around...

I've never actually used the DECIMAL type before. It's unusual.