How to create a custom actor filter that calsl to a Oracle database procedure?

Q&A
1

Hi experts,

I’m using Bonita Comunity 7.2.3.

I’d need to implement a custom actor filter that calls to an Oracle database procedure. That procedure will return the user_name of the person to assign the task.

Can anybody provide me a example?

Thanks a lot,
Jose.

2

Unlikely as your requirement is rather specific.

You could create a Actor Filter and add your own code to the filter to read the DB and return the name,

regards
Seán

PS: If this reply answers your question, please mark as resolved.

3

Hi Sean,

I’ve solved my issue and my Custom Actor Filter calling to database is working.

Was missing to go to Configure->Process Dependencies->Others-> Add ojdbc6.jar

Here is the code for everybody who need it:

In Oracle:

CREATE OR REPLACE PROCEDURE get_apex_supervisor(x_retcode OUT INTEGER,
x_errbuf OUT VARCHAR2,
p_user_name IN VARCHAR2,
x_supervisor_name OUT VARCHAR2) IS
BEGIN
x_retcode := 0;
x_errbuf := ‘Procedure succesfully completed’;
x_supervisor_name := ‘mauro.zetticci’;
END;

/** * */ package org.mycompany.connector;

import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;

import org.bonitasoft.engine.connector.ConnectorValidationException;
import org.bonitasoft.engine.exception.BonitaException;
import org.bonitasoft.engine.filter.UserFilterException;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;

import oracle.jdbc.OracleDriver;

/**

  • The actor filter execution will follow the steps 1 - setInputParameters() →
  • the actor filter receives input parameters values 2 -
  • validateInputParameters() → the actor filter can validate input parameters
  • values 3 - filter(final String actorName) → execute the user filter 4 -
  • shouldAutoAssignTaskIfSingleResult() → auto-assign the task if filter
  • returns a single result
    */

public class ApexSupervisorImpl extends AbstractApexSupervisorImpl {
static CallableStatement cs = null;
static Connection conn = null;

public void validateInputParameters() throws ConnectorValidationException {
}

public List<Long> filter(String actorName) throws UserFilterException {
	try {
		long processInstanceId = getExecutionContext()
				.getProcessInstanceId();
		long processInitiator = getAPIAccessor().getProcessAPI()
				.getProcessInstance(processInstanceId).getStartedBy();
		long supervisorId = 0;

		// Call database to get supervisor userName

		try {
			String supervisor = getApexSupervisor(getAPIAccessor()
					.getIdentityAPI().getUser(processInitiator)
					.getUserName());
			supervisorId = getAPIAccessor().getIdentityAPI()
					.getUserByUserName(supervisor).getId();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return Arrays.asList(new Long[] { Long.valueOf(supervisorId) });

	} catch (BonitaException e) {
		throw new UserFilterException(e);
	}
}

public boolean shouldAutoAssignTaskIfSingleResult() {
	Boolean autoAssignO = (Boolean) getInputParameter("autoAssign");
	return autoAssignO == null ? true : autoAssignO.booleanValue();
}

public static Connection getConnection() throws SQLException {
	String username = "hr";
	String password = "hr";
	String thinConn = "jdbc:oracle:thin:@localhost:1521:XE";
	DriverManager.registerDriver(new OracleDriver());
	Connection conn = DriverManager.getConnection(thinConn, username,
			password);
	conn.setAutoCommit(false);
	return conn;
}

public static void closeConnection() {
	try {
		conn.close();
	} catch (SQLException e) {
		e.printStackTrace();
		try {
			conn.close();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
	}
}

public static String getApexSupervisor(String pUserName)
		throws SQLException {
	conn = getConnection();
	String sql = "{call get_apex_supervisor(?,?,?,?)}";

	cs = conn.prepareCall(sql);

	cs.registerOutParameter(1, Types.INTEGER);
	cs.registerOutParameter(2, java.sql.Types.VARCHAR);
	cs.setString(3, pUserName);
	cs.registerOutParameter(4, java.sql.Types.VARCHAR);

	cs.execute();
	Integer retcode = cs.getInt(1);
	String errbuf = cs.getString(2);
	String supervisor = cs.getString(4);

	closeConnection();
	return supervisor;
}

}

4

Could I create a database connector and use it from the Actor Filter?

5

Hi Sean,

I have the working Java code that connects to Oracle and executes the procedure.
I’ve added it into the custom actor filter, but I get this exception:

*Caused by: org.bonitasoft.engine.core.filter.exception.SUserFilterExecutionException: java.util.concurrent.ExecutionException: java.lang.NoClassDefFoundError: oracle/jdbc/OracleDriver
*

The filter compiles in Bonita Studio fine. How can I resolve this dependency problem?
Thanks!