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 to access information in other DBMS. This option allows organizations to integrate ProcessMaker with their other data-intense operations which also utilize on databases.

ProcessMaker uses the object-relational mapping library Propel to map between ProcessMaker's PHP classes and databases. Propel opens 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 1 Creating a New Database Connection 2 MySQL 3 PostgreSQL 3.1 Windows 3.2 Linux/UNIX 4 SQL Server 5 ORACLE

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.

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 bare text editor such as Notepad++. 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.

Linux/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 "New", PostgreSQL should be an available option under Engine.

SQL Server

The default installation of ProcessMaker for Windows includes the PHP extension to connect to SQL Server, but many times this option does not appear in the dropdown when a new connection is created. This is because the Dynamic Link Library "ntwdblib.dll" is required.

This DLL is provided in the SQL Server or SQL Server client installer. Once either of them is installed, or the dll is copied manually to the windows system directory, and the ProcessMaker service is re-started, the option that allows you to create connections to “SQL Server” Servers should be enabled.

There are a number of "ntwdblib.dll" versions; you need to install the appropriate one depending on the version of the SQL Server you want to connect to .

ORACLE

From version 1.1-2151 on, ProcessMaker Windows installers have PHP extensions already enabled to connect to ORACLE servers. Previous versions need to enable them manually 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 them, just delete the semicolon(;) which is found at the beginning of each line.

Next, you must install ORACLE’s InstantClient, which is free to download at (http://www.oracle.com/technology/software/tech/oci/instantclient/index.html). After you install this application, even though the installer does not require it, you need to restart Windows. Once you’ve done that, the option which allows you to connect to ORACLE servers will be enabled.

Just as with the SQL Server, there are various versions of InstantClient, depending on which version of ORACLE is used by the Server you want to connect to.