How to Execute a Stored Procedure call in Bonita Studio

1
0
-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!

No answers yet.
Notifications