Extended MS SQL Server Database Connector for Bonita BPM

The Bonita Platform has Microsoft SQL Server connector which will only execute DML commands from the Java layer. However, this connector does not support the procedure calls. The idea behind curating this blog is to help Bonita developers overcome the issues faced with procedure call execution. To begin with, we have created one custom connector for Microsoft SQL Server Database 2008. This connector will execute procedure calls as well as DML commands.

Environment

• Bonita BPM 7.6
• Java 1.7
• Microsoft SQL Server 2008 R2

Steps Involved in the Process

• Add the connector in Service/Human Tasks
• Add sqljdbc4.jar
• Database configuration and query section
• Resultset Manipulation
• Source code and GIT URL

Step 1: Add the connector in Service/Human Tasks

• Navigate to the Service/Human task
• Select the ‘Execution’ tab
• Now choose Connector In/Out depending on the requirement
• Click on ‘Add’ Button.

This will pop up a new window with all the predefined connectors as shown in the below screen.

• Select the Microsoft SQL 2008 custom connector in database category and click ‘Next’ to open the appropriate dialogue.

• Enter the connector name and click next to proceed.

Step 2: Add sqljdbc4.jar

• Download MS SQL connector jar (sqljdbc4.jar) and add it in definition as shown in the screen above.

Step 3: SQL Server Database Configuration & Query Section

• Enter the respective database driver, URL, username, and password and click Next to proceed.

• Enter Query here and click Next.

• Make sure, the stored procedure should only be in curly braces ({YOUR_QUERY}). We can write normal DML queries without braces. Set the inputs to query and click Next.

• The above filed for input to stored procedure and provide input like ‘List of Objects’. Below is the code snippet for input object.

InputList() script should be like this:
List<Object> inputList = new LinkedList<Object>();
inputList.add(new Integer(requestDetailID));
inputList.add(new String(requestStatusID));
return inputList;

• Check More Result: Sometimes stored procedure would return the output, but will not be able to draw result in the set first row. It will be available in any one of the rows in result set object.
• Select <Check More> result checkbox to search the results from all the rows in a result object.

Step 4: Resultset Manipulation

• The above is the screen for output data collection. Here, we need to perform resultSetList to List<Map<String,Object>> objects and resultSet to Map<String,Object>.

Step5: Source Code and GIT URL

/**
 *
 */
package org.evoketechnologies.connector;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import org.bonitasoft.engine.connector.ConnectorException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
 * The connector execution will follow the steps 1 - setInputParameters() -->
 * the connector receives input parameters values 2 - validateInputParameters()
 * --> the connector can validate input parameters values 3 - connect() --> the
 * connector can establish a connection to a remote server (if necessary) 4 -
 * executeBusinessLogic() --> execute the connector 5 - getOutputParameters()
 * --> output are retrieved from connector 6 - disconnect() --> the connector
 * can close connection to remote server (if any)
 */
public class MicrosoftSQL2008CustomConnectorImpl extends
		AbstractMicrosoftSQL2008CustomConnectorImpl {
	private final static Logger logger = LoggerFactory
			.getLogger(MSSQLConnectorImpl.class);
	@Override
	protected void executeBusinessLogic() throws ConnectorException {
		// Get access to the connector input parameters
		Connection con = null;
		CallableStatement csmt = null;
		ResultSet resultSet = null;
		LinkedList inputList = getInputList();
		List<Map<String, Object>> requestList = new ArrayList<Map<String, Object>>();
		logger.info("url "+getUrl());
		try {
			// converting Util date to SQL date
			Class.forName(getDriver());
			// connecting to MicroSoft SQL Server
			con = DriverManager.getConnection(getUrl(), getUsername(),getPassword());
			// invoke the stored procedure
			csmt = con.prepareCall(getQuery());
			// setting query input's
			if (inputList != null && inputList.size()>0) {
				for (int i = 0; i < inputList.size(); i++) {
					if (inputList.get(i) instanceof String || inputList.get(i) instanceof Date ) {
						csmt.setString(i+1, (String) inputList.get(i));
					} else if (inputList.get(i) instanceof Integer ) {
						csmt.setInt(i+1, (Integer) inputList.get(i));
					} else if (inputList.get(i) instanceof Long) {
						csmt.setLong(i+1, (Integer) inputList.get(i));
					} else if (inputList.get(i) instanceof Boolean) {
						csmt.setBoolean(i+1, (Boolean)inputList.get(i));
					} else if (inputList.get(i) instanceof Double) {
						csmt.setDouble(i+1, (Double)inputList.get(i));
					}else {
						csmt.setObject(i+1,inputList.get(i));
					}
			}
			}
			if (getCheckMoreResultset()) {
				logger.info("getCheckMoreResultset  ");
				boolean queryResult = csmt.execute();
				int rowsAffected = 0;
				while (queryResult || rowsAffected != -1) {
					if (queryResult) {
						ResultSet rs = csmt.getResultSet();
						ResultSetMetaData rsMetaData = rs.getMetaData();
						int columnCount = rsMetaData.getColumnCount();
						if (rs != null) {
							Map<String, Object> requestMap = new HashMap<String, Object>();
							while (rs.next()) {
								for (int i = 0; i < columnCount; i++) {
									requestMap.put(rsMetaData.getColumnName(i + 1),rs.getObject(i + 1));
								}
								setResultSet(requestMap);
								rs.close();
							}
						}
					} else {
						rowsAffected = csmt.getUpdateCount();
					}
					queryResult = csmt.getMoreResults();
				}
			} else {
				logger.info("getCheckMoreResultset  else  "+csmt);
				resultSet = csmt.executeQuery();
				ResultSetMetaData rsMetaData = resultSet.getMetaData();
				int columnCount = rsMetaData.getColumnCount();
				if (resultSet != null) {
					while (resultSet.next()) {
						Map<String, Object> requestMap = new HashMap<String, Object>();
						for (int i = 0; i < columnCount; i++) {
							requestMap.put(rsMetaData.getColumnName(i + 1),resultSet.getObject(i + 1));
						}
						requestList.add(requestMap);
					}
					setResultSetList(requestList);
				}
			}
		} catch (Exception e) {
			logger.error("Exception ::  "+e.getMessage());
			setResultSet(null);
			setResultSetList(requestList);
			throw new ConnectorException("Exception error while executing stored procedure ::"+ e.getMessage(), e);
		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
				}
				if (csmt != null) {
					csmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				logger.info("Exception ::  "+e);
			}
		}
	}
	@Override
	public void connect() throws ConnectorException {
		// [Optional] Open a connection to remote server
	}
	@Override
	public void disconnect() throws ConnectorException {
		// [Optional] Close connection to remote server
	}
}

Above source code will fetch the input data from input connector definition. And based on the input parameter, it will connect to the database, so that we are able to run the stored procedure. It will then set result to List<Objects> or a single object.

Conclusion

The existing Bonita SQL Server Database connector can be used only for performing CRUD (Create, Read, Update and Delete) operations. This blog is primarily helpful to perform operations like CRUD and to execute stored procedures. By leveraging the information shared here, any stored procedure can be run, and the results could be fetched to the Bonita Engine for further processing.

Evoke’s BPM Solutions

At Evoke Technologies, we bring over 15 years of experience as one of the industry’s best IT services companies. Our dedicated teams of highly trained BPM experts will come to know your business almost as well as you do, trusting your expertise in what you do to inspire us as we design and deploy customized BPM solutions to help you do it even better.

Further, with core technical competencies in the latest business process design standards (BPMN 2.0) and programming languages (like Java), premier partner status with Bonitasoft (the world’s fastest-growing provider of BPM services), and our customer-centric approach to planning and implementing business process strategy, Evoke can support you to develop and deploy right BPM solutions for your business needs. To learn more about our BPM consulting services, contact us online or call us at +1 (937) 660-4923.

Author

Gopal Rao Pamu has over 5 years of hands-on experience with Java/J2EE and also proficient at implementing BPM process with BonitaSoft. He has successfully deployed multiple applications with Spring and Hibernate framework as well.