How to Execute a Stored Procedure call in Bonita Studio

Q&A
1

Hello,

I’m trying to execute a stored procedure in Bonita Studio. The stored procedure is stored at a mysql database server, so currently I’m using a MYSQL Connector, but if it’s unsuitable, I don’t mind changing my approach. What I’ve done:

  1. Configure the database connector with the correct Driver, URL, Username & Password.

  2. Use a script editor to execute the call to the stored procedure (the stored procedure itself has 5 parameters, all of them are integers. Also, there should be no mistake in the sql syntax itself as I have tested this code using another program to connect directly to mysql):

    return “CALL testStoredProcedure(1, 2, 3, 4, 5);”;

  3. Use scripting mode.

  4. Finally, map the ResultSet to a process variable of type List<HashMap<String, Object>> as follows:

    // This is a re-mapping of the objects returned
    List previewItems = new ArrayList();
    while (resultset.next()) {
    Map<String, Object> itemMap = new HashMap<String, Object>();
    itemMap.put(“PersistenceID”, resultset.getLong(“persistenceId”));
    itemMap.put(“Name”, resultset.getString(“name”));
    itemMap.put(“ItemName”, resultset.getString(“itemName”));
    itemMap.put(“Date”, resultset.getDate(“date”));
    itemMap.put(“Amount”, resultset.getInt(“amount”));
    itemMap.put(“Total”, resultset.getInt(“total”));

     previewItems.add(itemMap);

    }
    return previewItems;

When I tried testing the connector, I kept on getting an error saying that the resultset cannot get the next value since it returns null. While I realised that I haven’t put a validation check of whether or not the returned value is null, the sql call itself shouldn’t have returned a null value at all (as I’ve mentioned above, I’ve tried the sql query using another program to connect directly to mysql, and it returns a few rows of results). Putting the validator aside, what did I do wrong that the code kept on returning null values?

Any kind of help is very much appreciated!

2

“CALL testStoredProcedure(1, 2, 3, 4, 5)”