Groovy and JDBC connection

1
0
-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

1 answer

1
0
-1
This one is the BEST answer!

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... 1. Select your process and go to the Configuration dialog 2. Select Java Dependencies > Other > Add... 3. Click on import and add the driver jar file 4. Select the driver and add it.

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

HTH
Romain

Comments

Submitted by sfrogner on Tue, 02/09/2021 - 19:05

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.

Submitted by sfrogner on Tue, 02/09/2021 - 19:06

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.

Submitted by romain.bioteau on Wed, 02/10/2021 - 09:40

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

Submitted by sfrogner on Wed, 02/10/2021 - 18:32

<p>Awesome Romain thank you</p>

Submitted by Hasitha on Thu, 10/21/2021 - 12:55

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

Submitted by romain.bioteau on Thu, 10/21/2021 - 15:14

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

Notifications