Groovy and JDBC connection

Q&A
1

Hi all,

I was wondering if i could get some assistance.

I have a script step where i want to connect to my local mySQL and perform and update.

Here is my script

import groovy.sql.Sql

java.util.logging.Logger logger = java.util.logging.Logger.getLogger ""

logger.info("Script Update Step")

 

if (merchantid != null)

{

logger.info(merchantid.toString())

}

if (storename != "")

{

logger.info(storename)

}

File file = new File("/Users/seanfrogner/talechtools/Dockers/filesystem/api-talech/m2/repository/mysql/mysql-connector-java/6.0.6/mysql-connector-java-6.0.6.jar");

logger.info(file.length().toString());

 

if (file != null)

{

this.getClass().classLoader.rootLoader.addURL(new File('/Users/seanfrogner/talechtools/Dockers/filesystem/api-talech/m2/repository/mysql/mysql-connector-java/6.0.6/mysql-connector-java-6.0.6.jar').toURL())

}

try{

 

//def sql = Sql.newInstance("jdbc:mysql://127.0.0.1:3306/pos_local","posuser", "pospassword", "com.mysql.jdbc.Driver")

//sql.execute("UPDATE pos_local.merchant_store SET EQUIPMENT_SHIPPED = 0 WHERE ID = 831");

//def newsql = "UPDATE pos_local.merchant_store SET EQUIPMENT_SHIPPED = 0 WHERE ID = " + merchantid + ""

//sql.execute(newsql);

}

catch(e)

{

logger.info(e.toString())

}

I keep getting this error in the engine.

org.bonitasoft.engine.commons.exceptions.SBonitaRuntimeException : "java.lang.NullPointerException: Cannot invoke method addURL() on null object"

If i run it from the evaluator it will work since i can pick the jars.  Also, i can get it to work through IntelliJ..

Anyone have any ideas

thx

2

Where is this code executed ? If you are in a process, you should not use a groovy script to perform a database operation but use the database connector.

The database driver can be added to the process classpath like this:

  1. Import the driver jar file in the project using the Project > Manage dependencies…
  2. Select your process and go to the Configuration dialog
  3. Select Java Dependencies > Other > Add…
  4. Click on import and add the driver jar file
  5. Select the driver and add it.

You don’t have to manipulate the classloader in the script.

HTH
Romain

3

Thanks much, i resolved by managing the dependencies.  I agree on the JDBC item.. I was not able to pass a process variable in the update using the database connector.  It only allowed me to pass a static value in the update request.

4

Thanks much, i resolved by managing the dependencies.  I agree on the JDBC item.. I was not able to pass a process variable in the update using the database connector.  It only allowed me to pass a static value in the update request.

5

You can use variables in a database connector script input.
Either use the plain text editor like this:

UPDATE pos_local.merchant_store SET EQUIPMENT_SHIPPED = 0 WHERE ID = ${myIdVariable} or use a script: """ UPDATE pos_local.merchant_store SET EQUIPMENT_SHIPPED = 0 WHERE ID = ${myIdVariable} """.toString()

HTH
Romain

6

Awesome Romain thank you

7

Hi where can I find "Project > Manage dependencies "? Is it not available in bonita community 2021.2 build 7.13.0?

8

With 2021.2, the dependency management has been revamped. You can now manage them in the Overview > Extension view.