# SQL Statements in Custom Module

**URL:** <https://forum.inductiveautomation.com/t/sql-statements-in-custom-module/24188>\
**Category:** Module Development\
**Tags:** database\
**Created:** [April 16, 2019, 10:21pm UTC](https://forum.inductiveautomation.com/t/sql-statements-in-custom-module/24188 "2019-04-16T22:21:10Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![jramirez3](https://avatars.discourse-cdn.com/v4/letter/j/a183cd/32.png) [@jramirez3](https://forum.inductiveautomation.com/u/jramirez3)\
**Post date:** [April 16, 2019, 10:21pm UTC](https://forum.inductiveautomation.com/t/sql-statements-in-custom-module/24188/1 "2019-04-16T22:21:10Z")

</div>

We have a module we had created and its loaded into our gateway and works correctly. All it does is listen to a UDP port and parse the data into tags (had some special cases so we couldn’t use the UDP Driver). I need to know within the Java code of the module am I able to make a connection to a database and run insert statements? The module constantly is running and is constantly listening. Depending on the data coming in on that port depends if I put that data into tags in Ignition or store that data directly to the database. So within the Java code of the module, can I accomplish this?

---

<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:** [April 17, 2019, 11:45am UTC](https://forum.inductiveautomation.com/t/sql-statements-in-custom-module/24188/2 "2019-04-17T11:45:31Z")

</div>

Yes. From the context passed to your module, use `.getDatasourceManager().getDatasource(someName).getConnection()` to obtain a wrapped JDBC connection to the configured database connection `someName`. Then proceed to run your query(ies) and then close the connection. (That really returns it to the connection pool.)

---

<div class="post-metadata">

**Author:** ![jramirez3](https://avatars.discourse-cdn.com/v4/letter/j/a183cd/32.png) [@jramirez3](https://forum.inductiveautomation.com/u/jramirez3)\
**Post date:** [April 17, 2019, 3:20pm UTC](https://forum.inductiveautomation.com/t/sql-statements-in-custom-module/24188/3 "2019-04-17T15:20:45Z")

</div>

Awesome, thank you for the feedback. So to be clear here, would something like this work?

```auto
Connection con = GatewayContext.getDatasourceManager().getDatasource("SQLServer_SES2_SCADA").getConnection();
int res = con.runUpdateQuery(sql);

```

---

<div class="post-metadata">

**Author:** ![jramirez3](https://avatars.discourse-cdn.com/v4/letter/j/a183cd/32.png) [@jramirez3](https://forum.inductiveautomation.com/u/jramirez3)\
**Post date:** [April 17, 2019, 4:26pm UTC](https://forum.inductiveautomation.com/t/sql-statements-in-custom-module/24188/4 "2019-04-17T16:26:35Z")

</div>

Awesome, thank you for the feedback. So to be clear here, would something like this work?

```auto
Connection con = GatewayContext.getDatasourceManager().getDatasource("SQLServer_SES2_SCADA").getConnection();
int res = con.runUpdateQuery(sql);

```

---

<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:** [April 17, 2019, 4:53pm UTC](https://forum.inductiveautomation.com/t/sql-statements-in-custom-module/24188/5 "2019-04-17T16:53:18Z")

</div>

Yes, though I’d strongly recommend using the [.runPrepUpdate()](http://files.inductiveautomation.com/sdk/javadoc/ignition79/795/com/inductiveautomation/ignition/gateway/datasource/SRConnection.html#runPrepUpdate-java.lang.String-java.lang.Object...-) method instead. If you are going to do many inserts to the same table back-to-back, use [.prepareStatement()](https://docs.oracle.com/javase/8/docs/api/java/sql/Connection.html#prepareStatement-java.lang.String-) and then execute that statement repeatedly.

---

<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:** [April 17, 2019, 4:57pm UTC](https://forum.inductiveautomation.com/t/sql-statements-in-custom-module/24188/6 "2019-04-17T16:57:38Z")

</div>

Note, your connection variable needs to be an `SRConnection` to have IA’s enhancements available.

---

<div class="post-metadata">

**Author:** ![jramirez3](https://avatars.discourse-cdn.com/v4/letter/j/a183cd/32.png) [@jramirez3](https://forum.inductiveautomation.com/u/jramirez3)\
**Post date:** [April 17, 2019, 5:25pm UTC](https://forum.inductiveautomation.com/t/sql-statements-in-custom-module/24188/7 "2019-04-17T17:25:36Z")

</div>

Thank you. Now the only error i’m getting when compiling it is it can’t find classes SRConnection and GatewayContext. I have the following import statements:

```auto
import com.inductiveautomation.opcua.types.DataType;
import com.inductiveautomation.opcua.types.Variant;
import com.inductiveautomation.ignition.*;
import com.inductiveautomation.ignition.client.*;

```

Is this right?

---

<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:** [April 17, 2019, 5:29pm UTC](https://forum.inductiveautomation.com/t/sql-statements-in-custom-module/24188/8 "2019-04-17T17:29:11Z")

</div>

Look at the package for the SRConnection interface I linked. (Personally, I avoid wildcard imports.) Also, you shouldn’t be calling out the `GatewayContext` class as if `getDatasourceManager()` is static, but using the actual context supplied to your gateway hook.

---

<div class="post-metadata">

**Author:** ![jramirez3](https://avatars.discourse-cdn.com/v4/letter/j/a183cd/32.png) [@jramirez3](https://forum.inductiveautomation.com/u/jramirez3)\
**Post date:** [April 17, 2019, 7:47pm UTC](https://forum.inductiveautomation.com/t/sql-statements-in-custom-module/24188/9 "2019-04-17T19:47:08Z")

</div>

Thank you. I finally got it to compile. I’m now getting a `java.lang.NullPointerException` on this line:

```auto
GatewayContext gateContext = gateContextHook.getContext();
logger.info("Just created the GatewayContext Object");
SRConnection con = gateContext.getDatasourceManager().getDatasource("SQLServer_SES2_SCADA").getConnection();

```

The gateContext object is from a class created in the module to get the gateway context. the `logger.info("Just created the GatewayContext Object")` does run, then the other logger messages after this do not run. When i run `logger.info(e.getMessage());` in the catch block, i prints nothing because it is null. So i ran this: `e.getClass().getName();` and the logs said it was a null pointer exception. I can’t figure out how this is producing that exception.

---

<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:** [April 17, 2019, 9:25pm UTC](https://forum.inductiveautomation.com/t/sql-statements-in-custom-module/24188/10 "2019-04-17T21:25:07Z")

</div>

I don’t understand enough about your code structure to give more precise advice, but you should be saving the context supplied to the hook’s setup() method in a field in your hook class. Then during hook startup(), presumably when you are creating the classes that do your listening, you should be passing that context object to those listener classes’ constructors. The listener hangs onto its reference to use when needed.  
In a pinch, you could also have your hook save the context to a static field, and supply a static method to retrieve it.

---

<div class="post-metadata">

**Author:** ![jramirez3](https://avatars.discourse-cdn.com/v4/letter/j/a183cd/32.png) [@jramirez3](https://forum.inductiveautomation.com/u/jramirez3)\
**Post date:** [April 18, 2019, 5:08pm UTC](https://forum.inductiveautomation.com/t/sql-statements-in-custom-module/24188/11 "2019-04-18T17:08:19Z")

</div>

Ok, I see what your talking about now. I do have a driverHook class that is using the setup function with a GatewayContext. So that’s the gateway context i should use in the connection variable?

---

<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:** [April 18, 2019, 5:09pm UTC](https://forum.inductiveautomation.com/t/sql-statements-in-custom-module/24188/12 "2019-04-18T17:09:52Z")

</div>

> [@jramirez3](#):
>
> So that’s the gateway context i should use in the connection variable?

Yes.

{ Mumbo-jumbo to make 20 characters }