UncategorizedNo Comments

default thumbnail

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:

Be the first to post a comment.

Add a comment