I am trying to communicate with a database in my module. From what I’ve heard, the easiest way to connect is to add the sqljdbc4.jar to the projects build path and then connect in the following way:
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = DriverManager.getConnection(host, userName, password);
Statement stmt = con.createStatement();
However when I load my module into ignition I receive the following error:
Searching up the error I discovered it’s an issue with the CLASSPATH environment variable, so I set the classpath to the appropriate value (.;[path to the sqljdbc4.jar]) but I still get the same error.
if I remove the “Class.forName” statement the error becomes:
java.lang.Exception: java.sql.SQLException: No suitable driver found for [database connect string]
I know my “host” “userName” and “password” variables are correct since I was able to successfully add and connect the database to ignition database connections using the same info. The only reason I can think of for this is that somehow the sqljdbc4.jar is not being discovered properly? Or is there some other setting I need to change in ignition for this to work?
[quote=“caitlinf”]… From what I’ve heard …[/quote]Well, maybe you heard wrong
Consider just using Ignition’s built-in data sources. In your module, retain a reference to the gateway context, which then allows you to use getDatasourceManager(), which then allows you to use getDatasource() with an Ignition data source name. Which then gives you access to the corresponding JDBC connection pool to use for queries. Don’t hold on to connections long – just let them go back to the pool right after you’re done with them.
I see, perhaps I heard from the wrong sources. This makes much more sense in the context of developing ignition modules. However using this method I have run into another problem.
When executing the insert statement (by following the example in the manual):
con = lrscontext.getDatasourceManager().getConnection("test2");
con.runPrepQuery("INSERT INTO OpenConnections(Connection_Name, IP_Address, Port) VALUES", antenna.getName(), antenna.getIP(), antenna.getPort());
I get the following error:
java.lang.Exception: com.microsoft.sqlserver.jdbc.SQLServerException: The index 1 is out of range.
You neglected to include placeholders for the arguments you provided. Try this:con = lrscontext.getDatasourceManager().getConnection("test2");
con.runPrepQuery("INSERT INTO OpenConnections(Connection_Name, IP_Address, Port) VALUES (?, ?, ?)", antenna.getName(), antenna.getIP(), antenna.getPort());
