Connecting a mySQL database

Q&A
1

Hello,

I’m extremely new when it comes to Bonita BPM and am having troubles connecting to my MySQL database. I can’t seem to find any guides out there that are for the most recent version of Bonita BPM. Could someone give me a short tutorial and help walk me through what I need to do?

Thanks for your time,
Aaron

Setup Information:

Driver: com.mysql.jdbc.Driver
URL: jdbc:mysql://localhost:3306/firstdatabase
username:root
password:*******

Query: select * from testing where test_id = ‘testingquery’;

testing is a table in firstdatabase with one entry and one column. The column is test_id and the one entry is testingquery.

Error:

java.lang.reflect.InvocationTargetException
org.bonitasoft.engine.bpm.connector.ConnectorExecutionException: USERNAME=install | org.bonitasoft.engine.core.operation.exception.SOperationExecutionException: org.bonitasoft.engine.expression.exception.SExpressionEvaluationException: No value found for mandatory expression ‘singleResult’ of type Input Expression

2

OK

  1. Close Studio
  2. copy the mysql jar into {bonitahome}\tomcat\lib
  3. Open Studio
  4. Create a new diagram
  5. delete the task, there’s no need for it in this case
  6. click the pool area
  7. click EXECUTION Tab
  8. click Connectors In
  9. Add
  10. under categories expand database, then select your database
  11. MySQL is under Others
  12. under connector definitions click on the connector definition you want to use, next
  13. give it a name, next
  14. click next
  15. enter the correct information for Driver, URL, Username and Password, next
  16. in query add your query, next
  17. select Scripting Mode, next
  18. click dropdown arrow in Select Target
  19. give the variable a name and datatype of BOOLEAN, Finish
  20. click the Pencil next to resultset
  21. select expression type SCRIPT
  22. add the following code:
import java.util.logging.Logger;

Logger logger = Logger.getLogger(“org.bonitasoft”);
logger.severe(“Start of Logging”);

while(resultset.next()){
logger.severe(resultset.getInt(1).toString()); //choose what suitable for you
logger.severe(resultset.getString(2)); //choose what suitable for you
logger.severe(resultset.getBoolean(3).toString()); //choose what suitable for you
}

logger.severe(“End of Logging”);
return true;

  1. click OK
  2. click Finish
  3. In the task bar click RUN
  4. When the browser window opens click Start
  5. Close the browser
  6. In Studio->HELP->Bonita Engine Log → go to the bottom

You should see the results of the connector retrieving the records and the records being printed.

Anything else you’ve done something wrong.

regards
Seán

PS: As this reply answers your question, please mark as resolved by ticking the tick mark on the left of this reply.

Also Note:

A Tip on displaying CODE/LOGS correctly in Posts:

Do not use the Supplied Buttons above, for some reason they refuse to work correctly, and despite bringing it to Bonitasofts attention, it’s low priority.

To Show Code/Logs correctly use

< code >
your code/log
< /code >

removing the spaces to allow the showing of code/logs correctly as here:

your code/log

Thanks and regards
Seán

3

Watch a video perhaps. link

4

are you trying to use the database as part of a process?

5

Sean McP, I am simply trying to run a test on it to make sure that it is connected correctly. Once it is up and running correctly, I am going to pull values from the database and assign it to variables which I will add into forms.

6

FINALLY got it! Your directions were perfect, I was just messing up. If it isn’t too much trouble, could you explain how I would put the value I get from querying the database in a variable, and then how to use that variable? I want to put it into a form. Such as… "There was a helicopter named helicopterNameFromQuery from countryNameFromQuery that got shot down over placeOfConflictFromQuery. Thanks again Sean! -Aaron

7

Yes, but what have YOU done to do this?

I haven’t time or the inclination to write all your code for you I don’t get paid for that.

A simple Google search of mysql java resultset to json has given me loads of answers. This one having the nicest code:

http://stackoverflow.com/questions/12364036/create-json-object-in-servlet

You will want to return a type string instead of Boolean with the JSON data. As for using the variable. Have you read the “Getting Started Tutorial”? It shows how to do all this.

Use this data in a drop down list and then return the selected one via contract…

regards
Seán

8

Which never seemed to work :slight_smile:

The author should have shown us how to look at the Logs and I’m sure we would have seen the error he was getting - I suspect it was the password as it’s the one thing he never changed in three attempts… :wink:

regards