access to database Bonita directly

I try it on UBUNTU 14.04, also the directory path was /bonita-studio/all-in-one/target/BonitaBPMCommunity-7.3.0/workspace/tomcat/lib/bonita/h2-1.3.170.jar.
Your answer was excellent.Moreover, How can I connect to other databases like Microsoft SQL or MySql?
on the other hand, is it possible I connect to this database and export some data to a file by a script. Any help would be acceptable.

You can't from this tool. You will have to get the appropriate Client software for your database. Use Google to search for {{mydatabase}} client. Or use something like DBVisualizer that can work on (nearly) all databases.

The same applies to any process you develop, you need to specify the appropriate Connection and then the appropriate Select statements. While SQL is the same everywhere, not all SQL statements are the same...I know, but that's how its implemented.

As I previously said create a simple batch job to:

1. Connect to the Database
2. Select the data
3. Write the data to a file

regards

thanks for your answer, in order to get the data from h2 embedded database, I have written a python script by the aid of "Jaydebeapi" library. following is my code:

import jaydebeapi
from jaydebeapi import _DEFAULT_CONVERTERS, _java_to_py
_DEFAULT_CONVERTERS.update({'BIGINT': _java_to_py('longValue')})
conn = jaydebeapi.connect('org.h2.Driver',['jdbc:h2:tcp://localhost:9091/business_data.db', 'sa', ''],'path to h2-1.3.170.jar',)
curs = conn.cursor()
curs.execute("select * from request") #this is SQL query on database
data=curs.fetchall()
print(data)

this code worked well but I want to get the Task id from the database. when a new task is assigned to a user, for example in "file-upload-download-example", a new task id that is assigned to the new task is created in the bonita portal. my question is that how can I get the Taskid from h2 database? or all in all in any application when a new task is assigned to a user what things was changed in the database?

In another word, can you outline the table under which the taskid is stored in?

Use this command

SELECT * FROM PROCESS_INSTANCE

for table PROCESS_INSTANCE

However, rather than using an external program you could also use Trigger, see here:
http://stackoverflow.com/questions/29993308/h2-how-to-create-a-database-...

regards
Seán

I have run your proposed query but there is no PROCESS_INSTANCE table.I want to get task id from the database then notify a user which you have a new task.how is it possible? do you have any idea?

There has to be a table Process_instance.

Bonitasoft will not work without it...what tables do you have?

I used SELECT * FROM INFORMATION_SCHEMA.TABLES and following isTABLE_NAME column.
VACATIONREQUEST,SUPPLIER,REQUEST,QUOTATION,VACATIONAVAILABLE,HELP,VIEWS,CONSTRAINTS,RIGHTS,FUNCTION_COLUMNS,SETTINGS,TABLE_TYPES,SCHEMATA,INDEXES,IN_DOUBT,CROSS_REFERENCES,USERS,SESSIONS,TABLE_PRIVILEGES,CONSTANTS,DOMAINS,TABLES,COLUMNS,COLLATIONS,ROLES,SESSION_STATE,SEQUENCES,FUNCTION_ALIASES,TYPE_INFO,TRIGGERS,LOCKS,COLUMN_PRIVILEGES,CATALOGS.

You are not connected to the database. There are about 60 tables and you've not got one of them...

in order to connect the H2 Embedded database, According to your answer, First of all, I click on h2-1.3.170.jar file then the browser opens up a 127.0.1.1:8082 link like your answer. after that, I log in with sa username to the web console.
is it ok or not?

I didn't answer - it was Originally Submitted by DrakunDry on Tue, 10/04/2016 - 02:57...

I only pointed out other ways to do what you want to do and that other clients would be necessary for other databases.

DrakenDry's answer while correct wasn't necessarily as complete as it could have been...

Anyway here is the correct way to do what you are doing.

On my system - I use Windows you have to find the database first in the file system, there are two:

bonita_journal.db.h2.db - the main Bonitasoft Process Database
business_data.db.h2.db - the main Bonitasoft Business Data Database

On my system these are in folder: C:\Bonita\7xx\73x\732\00 Scratch\workspace\default\h2_database

Open the h2-1.3.170.jar to open the client
Change Saved Settings to Generic H2 (Server)
Change JDBC URL to jdbc:h2:file:C:\Bonita\7xx\73x\732\01 Devl\workspace\default\h2_database\bonita_journal.db

Use your file directory structure.

NOTE: You must use a DB name of bonita_journal.db and NOT bonita_journal.db.h2.db

This is an issue with how h2 works. You do not use the .h2.db at the end. If you do you will end up with unexpected databases that h2 will create automatically. As you will find when you look in your own folder.

You will then see all the Bonitasoft database tables.

ALSO NOTE: Using these settings (h2 1.3) you will find you cannot have both the client and studio/Bonitasoft server open at the same time. To allow concurrent access you have to add the following to the URL string ;MVCC=TRUE to give you

jdbc:h2:file:C:\Bonita\7xx\73x\732\01 Devl\workspace\default\h2_database\bonita_journal.db;MVCC=TRUE

Hope this helps,

regards
Seán

It worked , Thank you Sean

Only problem I have now is, unlike business_data.db. the bonita_journal.db can only be accessed when the bonita tomcat server is stopped.

Is there any workaround for this?

Thanks in advance

Abhi-