SQL Statements in Custom Module

Module Development
1

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?

2

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.)

2 Likes
3

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

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

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

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

Yes, though I’d strongly recommend using the .runPrepUpdate() method instead. If you are going to do many inserts to the same table back-to-back, use .prepareStatement() and then execute that statement repeatedly.

2 Likes
6

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

1 Like
7

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:

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

Is this right?

8

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.

9

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

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.

10

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.

1 Like
11

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?

12

Yes.

{ Mumbo-jumbo to make 20 characters }

2 Likes