Friday, 08 November 2013 00:00

Running SQL from a Groovy Event Handler

Written by 

Have you ever run into a situation where you needed to run SQL from an event handler or PX but wasn't quite sure how to do it?  This blog will show you how to get a connection to the Agile PLM database and execute SQL without worrying about the connection details.

The Script

The script itself is pretty simple - just import the com.agile.util.sql package and leverage the ConnectionFactory class.  Once you get the connection, you can proceed as you normally would since it is a standard Connection interface.  If you are coding this externally make sure you add agileclasses.jar to your classpath.

import com.agile.agileDSL.ScriptObj.*;
import com.agile.api.*;
import com.agile.util.sql.*;
import java.sql.*;

void invokeScript(IBaseObjectScriptObj object) {

	Connection connection = null;
	PreparedStatement statement = null;
	ResultSet rows = null;
	
	try {
		connection = ConnectionFactory.getFactory().getConnection();
		statement = connection.prepareStatement("select ifs_url from vault");
		rows = statement.executeQuery();

		while(rows.next()) {

			// log the SQL response
			object.logMonitor( "SQL Response: " + rows.getString("ifs_url") );
		}
		
	} catch (Exception e) { 
		object.logMonitor( e.getMessage() );
	}
}

 

Setting Up Agile for the Test

Event

 

event handler

 

event subscriber


The Results

I set up my script to execute on the title block update event for items, so simply modifying an attribute on a part and saving the changes will trigger it.  If we inspect the event monitor the following entry is found.

monitor result

 

The Video

Here is the video:

Last modified on Monday, 27 October 2014 14:48
Rodney McCabe

Sr. Technical Consultant and Solution Architect at Mazamo Corp
http://www.linkedin.com/in/rodneymccabe