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 ScriptThe 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 The ResultsI 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. The VideoHere is the video:
