Database Connections
From ProcessMaker
ProcessMaker maintains 3 MySQL databases to store internal information about processes, user permissions and reports. Nonetheless, ProcessMaker can also be configured to connect to external databases, allowing an organization to integrate ProcessMaker with other DBMS and business applications which utilize databases.
ProcessMaker uses Propel library to map between ProcessMaker's PHP classes and databases. Propel allows access to different DBMS, including MySql, Microsoft SQL Server, PostgreSQL, and Oracle.
The ProcessMaker automatically detects which database modules for PHP are installed on the server. For example if the php-pgsql module is installed on the ProcessMaker server, ProcessMaker will offer the option to connect to PostgreSQL databases.
Contents |
Creating a New Database Connection
To create a new database connection, open a process and then go to ADMIN (formerly SETUP) > DATABASE CONNECTIONS. At the top of the list of existing database connections, click the "New" link to define a new database connection.
Fill in the following information:
- Engine: Select the type of database, which can be MySql, PostgreSQL, Microsoft SQL Server, or Oracle. ProcessMaker will only offer databases in the dropdown list which have PHP modules installed on your server.
If MySQL or PosgreSQL is selected in ProcessMaker version 1.2-2425 and later, the option Encode will appear below Engine to allow the character encoding of the database to be selected. Look up the encoding of your external database.
- Server: IP address or domain name where the database source is installed.
- Database Name: The name of the database to use.
- Username: The username to log into the external database.
- Password: The password to log into the external database.
- Port: The port used by the external database. If this field is left empty, the routine will use the default port, which is 3306 for MySQL, 5432 for PostgreSQL, 1521 for Oracle, and 1433 for SQL Server.
- Description: Add any additional information about the external database.
Once you have filled out this information, you need to test the connection by clicking on the Test Connection button. The test routine verifies hostname, port, service, the user access, and the existence of the database, as shown in this illustration:
If the test is successful, then click Create to set up the connection. After that the database connection will be available for the current process. It can be used in SQL SELECT statements in DynaForms fields and with the executeQuery() function in triggers.
MySQL
All the necessary modules should already be installed in order to use an external MySQL database, since those same modules are needed to use the internal databases used by ProcessMaker.
By default MySQL servers are set up to only receive local connections from the localhost. If ProcessMaker is trying to connect to a a MySQL server on another machine, then that server will have to be configured to allow external connections. Edit the my.cnf file of the MySQL server and comment out the line:
# bind-address = 127.0.0.1
Then reload or restart the MySQL server. To check whether MySQL is listening for external connections, in Windows issue the command:
netstat -an
You should see a line such as:
TCP 127.0.0.1:3306 0.0.0.0:0 LISTENING
In Linux/UNIX, use the command:
netstat -tanp
You should see a line such as:
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 2270/mysqld
When setting up a Database Connection to an external MySQL database, it is necessary to select the character encoding, such as Latin-1 and UTF-8. To find out the character encoding used by a MySQL database, log into MySQL and change to the database and issue the status command:
mysql -u root -p mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | example_db | +--------------------+ 3 rows in set (0.00 sec) mysql> use example_db; Database changed mysql> status; -------------- mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2 Connection id: 6054 Current database: example_db Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: Using delimiter: ; Server version: 5.0.51a-24 (Debian) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: utf8 Client characterset: latin1 Conn. characterset: latin1 UNIX socket: /var/run/mysqld/mysqld.sock Uptime: 12 days 3 hours 51 min 33 sec
Look for the "client characterset" which is the character set in which ProcessMaker will send queries to the MySQL database and the "Conn. characterset" which is the character set which MySQL will send information back to ProcessMaker.
In the configuration of ProcessMaker's Database Connection, set Encode to the character set used by "Client characterset and "Conn. characterset". ProcessMaker expects the Client and Connection character set to be the same. If they aren't the same, change them to be the same. You can change them temporarily with:
SET NAMES <character-set>;
To change them permanently, add the following lines to my.cnf:
character_set_client = <character-set> character_set_results = <character-set> character_set_connection = <character-set>
Then reload or restart the MySQL server.
Enabling MySQL log
If detailed records need to be kept about each action in the MySQL databases used by ProcessMaker, turn on the debug logging.
Edit the file <INSTALL-DIRECTORY>/workflow/public_html/sysGeneric.php with a plain text editor. In Linux/Unix, it can generally be found at:
/opt/processmaker/workflow/public_html/sysGeneric.php
In Windows, it can generally be found at:
c:\Program Files\ProcessMaker\apps\processmaker\workflow\public_html\sysGeneric.php
Change the value of the debug setting to 1 to turn on detailed SQL logging:
'debug' => 1, //change this value to 1, to have a detailed sql log in PATH_DATA . 'log' . PATH_SEP . 'workflow.log'
This line will enable logging to three log files in the path: <INSTALL-DIRECTORY>shared/log/
rbac.log report.log workflow.log
PostgreSQL
In order for ProcessMaker to connect to a PostgreSQL database, the PostgreSQL client software and the PHP module "pgsql" has to be installed on the same machine as the ProcessMaker server.
Windows
Download the pgInstaller for Windows and install the PostgreSQL client software (and the server as well if you plan on running PostgreSQL server from the same machine as the ProcessMaker server).
Then go to the directory where you installed PHP and verify that you have the dynamic link libraries php_pgsql.dll and php_pdo_pgsql.dll located in the ext directory. If you used the ProcessMaker Windows Installer, these files can be found at: C:\Program Files\ProcessMaker\php\ext\
Then enable PHP's PostgreSQL modules, by opening the PHP configuration file php.ini with a plain text editor. If you used the ProcessMaker Windows Installer, it will be located at: C:\Program Files\ProcessMaker\php\php.ini
Look for the "Windows Extensions" section and uncomment the PostgreSQL modules by removing the semicolon (;) from the beginning of following lines:
extension=php_pdo_pgsql.dll extension=php_pgsql.dll
Then restart the Apache server to use the new PHP configuration, by going to the command line (located at Start > All Programs > Accessories > Command Prompt) and issuing the command:
httpd -k restart
To verify that PHP is now using the PostgreSQL modules, create a file named "info.php" with a bare text editor containing:
<?php phpinfo(); ?>
and save it to your workflow\public_html\ directory, which generally will be found at: C:\Program Files\ProcessMaker\apps\processmaker\htdocs\workflow\public_html\
Then open your web browser and direct it to http://localhost/info.php and you should see the configuration information about PHP. Verify that the pdo_pgsql and pgsql modules are enabled.
The next time you open ProcessMaker and go to the ADMIN > DATABASE CONNECTIONS and click "New", PostgreSQL should be an available option under Engine.
GNU/Linux or UNIX
It is best to install the PostgreSQL client and PostgreSQL's PHP module using the repositories of your distribution. In Red Hat/Cent OS/Fedora, login as root and issue the command:
yum install postgresql php-pgsql
Then reload Apache:
service httpd reload
In Debian and its derivatives, login as root and issue the commands:
apt-get install postgresql-client php5-pgsql /etc/init.d/apache2 reload
In Ubuntu:
sudo apt-get install postgresql-client php5-pgsql sudo /etc/init.d/apache2 reload
Then verify that the pgsql and pdo_pgsql modules are enabled with the command:
php -m
The next time you open ProcessMaker and go to the ADMIN > DATABASE CONNECTIONS and click on New, PostgreSQL should be an available option under Engine.
Microsoft SQL Server
To connect ProcessMaker to SQL Server databases, either install MS SQL Client Tools on Windows machines or install FreeTDS on GNU/Linux or UNIX machines.
Windows
If Microsoft SQL Server is installed on the same machine as ProcessMaker, then ProcessMaker should automatically detect it and it should be listed as an available option under Database Connections. Otherwise, install the SQL Client Tools which can be found on the Microsoft SQL Server CD. Configuration of the client will require installation of all the tools. If the SQL Server CD isn't available, make a copy of SQL Server's dynamic link library found at \winnt\system32\ntwdblib.dll on a machine which has SQL Server or SQL Client Tools installed. Then copy the ntwdblib.dll file into the \winnt\system32 directory of your ProcessMaker server.
Make sure that the version of ntwdblib.dll matches the version of SQL Server. For instance, the dynamic link library for SQL Server 2003 will not work to connect to SQL Server 2005.
Then edit the php.ini file that is in the directory C:\Program Files\ProcessMaker\php, to enable the mssql extension:
extension=msql.dll
After restarting Apache (or rebooting the server), the option "SQL Server" should appear as an available option under the Engine dropdown box for Database Connections.
GNU/Linux or UNIX
ProcessMaker servers running on GNU/Linux or UNIX can access Microsoft SQL Server or Sybase databases by installing FreeTDS. Most distributions provide a PHP extension package for SQL Server or Sybase, which contains FreeTDS. If your distribution doesn't provide a package for FreeTDS, see these instructions to compile FreeTDS from the source code.
Red Hat/CentOS/Fedora
Install the php-mssql package using yum:
yum install php-mssql
Then edit the php.ini file to enable the mssql extension:
extension=mssql.so
Finally, restart Apache:
/etc/init.d/httpd restart
Debian/Ubuntu
In Debian login as root and install the php5-sybase package (which will work for MS SQL Server as well):
apt-get install php5-sybase
In Ubuntu, use the sudo command instead of logging in as root:
sudo apt-get install php5-sybase
If planning on executing stored procedures on the SQL Server database, edit the FreeTDS configuration file, which in Debian/Ubuntu is found at /etc/freetds/freetds.conf. Edit the following lines to specify the IP address and port of server where SQL Server is installed and the FreeTDS version number:
host = xxx.xxx.xxx.xxx port = 1433 tds version = 8.0
Oracle
In order to connect to an Oracle database, Oracle's client software must be installed on the same server running ProcessMaker.
Windows
Install Oracle’s InstantClient on the same server running ProcessMaker. It can be downloaded for free at:
Install the version of InstantClient which corresponds to your version of Oracle Server.
From version 1.1-2151 on, the ProcessMaker Windows Installer has the PHP extensions already enabled to connect to Oracle servers. In previous versions of ProcessMaker, the PHP extensions for Oracle need to be enabled by editing the php.ini file. These extensions are the following:
;extension=php_oci8.dll ;extension=php_pdo_oci.dll ;extension=php_pdo_oci8.dll
To enable these extensions, just delete the semicolon (;) which is found at the beginning of each line.
After installing the InstantClient and modifying php.ini, Windows must be restarted to enable the InstantClient. Then, "Oracle" should appear as one of the available options when creating a Database Connection.
GNU/Linux
Oracle provides official rpm packages to install its InstantClient in 32-bit Red Hat/CentOS, which can be downloaded for free at:
For ProcessMaker servers using 64bits or a different server, download the ZIP file of InstantClient.
To install and configure InstantClient in GNU/Linux, see Oracle's instructions at:
If installing InstantClient in Red Hat, Fedora or SuSE, the links in this page may be helpful.
Oracle also provides a repository for Debian (and Ubuntu), which can be accessed by adding the following line to the file /etc/apt/sources.list:
deb http://oss.oracle.com/debian unstable main non-free
See these instructions or this blog article to enable the repository and install InstantClient.
