Database Connections

Each ProcessMaker workspace 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 the following DBMS MySql, Microsoft SQL Server, PostgreSQL, and Oracle.

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.

Creating a New Database Connection

To create a new database connection, open a process by going to DESIGN tab and then go to DATABASE CONNECTION, the Databases Source List will display as the image below.

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, see this list to check which database extensions will be supported. If you install one of the extensions mentioned, remember to restart PHP to see changes.

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.

Warning: The password for the Database Connection is stored as normal text in the wf_<WORKSPACE>.DB_SOURCE.DBS_PASSWORD field in the MySQL database, so make sure to restrict access to your 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 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. If the PosgreSQL database does not use the UTF-8 character set and it contains non-ASCII characters, then see this bug.

If the PostgreSQL database is located on a different server than the ProcessMaker server, remember to configure PostgreSQL to allow for connections from 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: <INSTALL-DIRECTORY>\php\php.ini

Look for the "Windows Extensions" section and uncomment the PostgreSQL modules by removing the semicolon (;) from the beginning of the following lines:

extension=php_pdo_pgsql.dll
extension=php_pgsql.dll

Then restart the Apache server to use the new PHP configuration, by either rebooting or 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 to verify that the pdo_pgsql and pgsql extensions are enabled in PHP.

Then, open ProcessMaker and go to Processes. Open a process and go to DATABASE CONNECTIONS and click on the New link. PostgreSQL should now be an available option under the Engine dropdown box.

GNU/Linux or UNIX

It is best to install the PostgreSQL client and PostgreSQL's PHP module using the repositories of your distribution.

Red Hat/Cent OS/Fedora:
Login as root and issue the commands:

yum install postgresql php-pgsql
/etc/init.d/httpd restart

Debian/Ubuntu:
Login as root and issue the commands:

su   or   sudo -s
apt-get install postgresql-client php5-pgsql
/etc/init.d/apache2 restart

SUSE/openSUSE:
Login as root and issue the commands:

su   or   sudo -s
zypper install postgresql php5-pgsql
rcapache2 restart

After installing, verify that the pgsql and pdo_pgsql modules are enabled in PHP with the command:

php -m

Then, open ProcessMaker in a web browser. Go to PROCESSES and open a process. Then, go to DATABASE CONNECTIONS and click on the New link. PostgreSQL should now be an available option in the Engine dropdown box.

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. Some issues might happen specially if If the SQL Server database does not use the UTF-8 character set and it contains non-ASCII characters, because ProcessMaker is designed to execute all in utf-8, it means:

The source code of PM is written in utf8, and produce utf8 html pages.

Charset for apache server needs to be in utf8

The json encode for all ajax request is using json encode.

The mysql collation database by default for ProcessMaker is utf8.

In other words, these four components needs to be in utf8 to everything works: source code, apache server, interactive data and database fields should be in utf8

Solving issues with UTF-8 characters

Now, to display Unicode data from SQL server it is recommended to:

setup freetds to get back utf8 data to ProcessMaker.

remove some lines in the file MSSQLResultSet.php

Setup FREETDS

To avoid some permissions problems with the freetds.conf file it is strongly recommended to create an own freetds.conf file in:

 /home folder

Give all privileges, and make the environment variable FREETDSCONF point to the own freetds.conf file, to do this add the following line:

export FREETDSCONF=/home/fernando/freetds.conf

at the beginning of:

/etc/init.d/httpd

Finally change the following line in the global section of the freetds.conf file:

       tds version = 8.0
       client charset = UTF-8

Change `MSSQLResultSet.php`

Since, now freetds is getting back all the data in UTF8, we don't need the code to convert all fields to utf8, please remove lines 123-133 from MSSQLResultSet.php file located at:

/gulliver/thirdparty/creole/drivers/mssql/.

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 generally located at C:\Program Files\ProcessMaker\php\php.ini, to enable the mssql extension:

 extension=mssql.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 mssql extension, 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 to verify that the pdo_mssql and mssql extensions are enabled in PHP.

Then, open ProcessMaker and go to Processes. Open a process and go to DATABASE CONNECTIONS and click on the New link. PostgreSQL should now be an available option under the Engine dropdown box.

Solving Issues with UTF-8 Characters

The following steps must be followed to solve problems with characters different from English:

The first option, is install freetds in your windows box, and use the same setup of linux and freetds.

The second option is install last version of native microsoft php driver for sql server.

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 FreeTDS and a PHP extension package for SQL Server or Sybase. If your distribution doesn't provide a package for FreeTDS, see these instructions to compile FreeTDS from the source code.

If planning on executing stored procedures on the SQL Server database, edit the FreeTDS configuration file, which is generally 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 TDS version number:

host = xxx.xxx.xxx.xxx 
port = 1433 
tds version = 8.0

Red Hat/CentOS/Fedora

Install FreeTDS and the php-mssql package using yum:

yum install freetds php-mssql

Then, edit the /etc/php.ini file to enable the mssql extension:

extension=mssql.so

Finally, restart Apache:

/etc/init.d/httpd restart

For older versions of Red Hat/CentOS which don't contain a php-mssql package, see these instructions to manually compile FreeTDS and PHP's mssql extension.

Debian/Ubuntu

If using Debian 6 (Squeeze) or Ubuntu 9.10 (Karmic Koala) or later, login as root (or use sudo) to install FreeTDS and the php5-sybase package (which will work for MS SQL Server as well). Then, restart apache:

apt-get install freetds-bin php5-sybase
/etc/init.d/apache2 restart

If using Debian 5 (Lenny), install the freetds-common package instead:

apt-get install freetds-common php5-sybase
/etc/init.d/apache2 restart

For older versions of Debian and Ubuntu, compile FreeTDS from source.

SUSE/openSUSE

SUSE/openSUSE does not provide a PHP package to access MS SQL Servers, so freeTDS and the mssql PHP extension will have to be compiled from source.

First, install the unixOBDC package and the tools for compiling:

zypper install unixOBDC gcc make

Then, download the source code for the latest stable version of freeTDS. Then, decompress the code and configure and compile it:

tar xzf freetds-stable.tgz
cd freetds-0.82/
./configure -with-unixODBC=/usr/lib
make
make install

Then, install the php5-devel package (which contains the phpize command):

zypper install php5-devel

Next, check which version of PHP you are using:

php -i

Then, download the source code for your version of PHP to a local directory. Decompress the code. Then, switch to the ext/mssql directory and compile the mssql extension. Note that the following commands will only compile the mssql extension and insert it into your existing installation of PHP without altering anything else:

tar xzf php-5.3.3.tar.gz
cd php-5.3.3/ext/mssql
phpize
./configure
make
make install

Then, configure PHP to use the new extension by editing the PHP configuration files:

vi /etc/php5/apache2/php.ini
vi /etc/php5/cli/php.ini

In both files, add the line:

extension=mssql.so

Finally, restart Apache to be able to use the new mssql.so extension:

rcapache2 restart

Oracle

In order to connect to an Oracle database, Oracle's client software must be installed on the same server running ProcessMaker. If the Oracle database does not use the UTF-8 character set and it contains non-ASCII characters, then see this bug.

Windows

Install Oracle InstantClient, Client or Server on the same server running ProcessMaker. Oracle InstantClient can be downloaded for free at:

http://www.oracle.com/technology/software/tech/oci/instantclient/index.html

Install the version of Oracle InstantClient or Client which corresponds to your version of Oracle Server.

Oracle 8-10
After installing Oracle's software on the same server running ProcessMaker, enable PHP's OCI8 extension to connect to Oracle. The DLL file to use Oracle version 8 through 10, should already be included by default in PHP. Verify that your installation includes the file <INSTALL-DIRECTORY>\php\ext\php_oci8.dll. If not, see Installing OCI8 on Windows.

Next, edit <INSTALL-DIRECTORY>\php\php.ini with a plain text editor (like Notepad or Notepad++) and add the following line:

extension=php_oci8.dll

If this line already exists with a ";" (semicolon) in front, remove the semicolon to uncomment the line.

Oracle 11g
To use Oracle 11g, PHP will have to be upgraded to PHP 5.3. After upgrading PHP and installing Oracle's software on the same server running ProcessMaker, enable PHP's OCI8 extension for Oracle 11g, which generally isn't include by default in PHP. See Installing OCI8 on Windows. The file <INSTALL-DIRECTORY>\php\ext\php_oci8_11g.dll needs to be added.

Next, edit <INSTALL-DIRECTORY>\php\php.ini with a plain text editor (like Notepad or Notepad++) and add the following line:

extension=php_oci8_11g.dll

If this line already exists with a ";" (semicolon) in front, remove the semicolon to uncomment the line.

Note: Either the php_oci8.dll or php_oci8_11g.dll can be enabled, but not both at the same time.

Restarting to Enable
After installing the Oracle software and modifying php.ini, Windows must be restarted to enable the Oracle software and enable the Oracle extension in PHP. Then, "Oracle" should appear as one of the available options when creating a Database Connection inside ProcessMaker.

GNU/Linux

Red Hat/Cent OS/Fedora/SUSE
Oracle provides official rpm packages to install its InstantClient in 32-bit Red Hat/CentOS, which can be downloaded for free at:

http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html

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:

http://www.oracle.com/technetwork/articles/dsl/technote-php-instant-084410.html

In case the above link doesn't work (page under maintenance or link moved to another site) all steps to install and configure InstantClient in GNU/Linux are listed below:

Enabling the PHP OCI8 Extension on Linux

On Linux, PHP is generally manually compiled because the bundled version never seems to be up to date. However, if you don't wish to recompile PHP, more recent, unsupported RPM packages for Oracle Linux are available from oss oracle, or via Unbreakable Linux Network updates. If a supported PHP environment is desired use Zend Server. These all have the OCI8 extension pre-built.

Step 1: Installing libraries

It's required to have the followin libraries installed:

For CentOS

yum install php5-common php5 php5-dev libapache2-mod-php5 php5-cli
yum install build-essential php-pear
yum install libaio1

Debian

apt-get install php5-common php5 php5-dev libapache2-mod-php5 php5-cli
apt-get install build-essential php-pear
apt-get install libaio1

Then, follow the steps below to build PHP and OCI8 from source code:

Step 1: Install Oracle Instant Client download it from the Oracle official page. Download the rpm files:

oracle-instantclient11.2-basic_11.2.0.2.0-2_i386.rpm
oracle-instantclient11.2-devel_11.2.0.2.0-2_i386.rpm
oracle-instantclient11.2-sqlplus_11.2.0.2.0-2_i386.rpm

If you are using Debian you must install alien package to convert it.

apt-get install alien

alien oracle-instantclient11.2-basic_11.2.0.2.0-2_i386.rpm
alien oracle-instantclient11.2-devel_11.2.0.2.0-2_i386.rpm
alien oracle-instantclient11.2-sqlplus_11.2.0.2.0-2_i386.rpm

That will convert to .deb to have them installed:

dpkg -i oracle-instantclient11.2-basic_11.2.0.2.0-2_i386.deb
dpkg -i oracle-instantclient11.2-devel_11.2.0.2.0-2_i386.deb 
dpkg -i oracle-instantclient11.2-sqlplus_11.2.0.2.0-2_i386.deb

If you are using CentOS run the following commands:

rpm -ivh oracle-instantclient11.2-devel-11.2.0.3.0-1.x86_64.rpm
rpm -ivh oracle-instantclient11.2-basic-11.2.0.3.0-1.x86_64.rpm
rpm -ivh oracle-instantclient11.2-sqlplus_11.2.0.2.0-2_i386.rpm

Step 2: Create the environment variable

Use putty to run the following command:

For 32 bits:

echo export ORACLE_HOME=/usr/lib/oracle/11.2/client/

For 64 bits:

 echo export ORACLE_HOME=/usr/lib/oracle64/11.2/client/

Step 3: Download the OCI Library

Download it from this page

At this point there are two ways to install oci

- Installing directly using pecl.

- Descompressing the OCI Library and run the installation manually

Step 4. Installing directly using pecl

The latest OCI8 extension from PECL is always the current version. Although it is generally in sync with the latest PHP 5.4 source code, it can sometimes be more recent. The latest production extension can be automatically downloaded and added to PHP using:

pecl install oci8

This gives:

downloading oci8-1.4.7.tgz ...
Starting to download oci8-1.4.7.tgz (Unknown size)
.....done: 168,584 bytes
10 source files, building
running: phpize
Configuring for:
PHP Api Version:         20100412
Zend Module Api No:      20100525
Zend Extension Api No:   220100525
Please provide the path to the ORACLE_HOME directory.
Use 'instantclient,/path/to/instant/client/lib' if you're compiling
with Oracle Instant Client [autodetect] :

Complete the installation by going to Step 6.

Step 4: Installing by descompressing the OCI Library

Descompress the OCI Library and run the installation manually:

Create a directory to descompress those libraries:

mkdir -p /opt/oracle/ $ cd /opt/oracle/

Descompress the file in the directory

tar -xzvf /opt/oracle/oci8-1.4.9.tgz

And then Enter into oci8 directory

cd /opt/oracle/oci8-1.4.5

Execute the following command

phpize

Run the following command:

./configure --with-oci8=shared,instantclient,/usr/lib64/oracle/10.2.0.3/client/lib/

Step 5: Configure oci8

Configure oci8 to be installed with the correct parameters.

/configure –with-oci8=share,instantclient,/usr/lib/oracle/11.2/client/lib/

Use the command make to compile.

Make

Install oci8 running the following command

make install

Step 6:Adding the `oci8` extension

to load the libraries when it starts:

On Debian

Create the oci8.ini file on the following path:

/etc/php5/apache2/conf.d/

And add the line:

extension=oci8.so

On CentOS

Open php.ini file and add the following extension:

extension=oci8.so

Step 7: Checking the libraries installed

echo /usr/lib/oracle/11.2/client/lib/ >> /etc/ld.so.conf
ldconfig

Step 8: Restart apache services

On Debian

/etc/init./apache2 stop $ /etc/init./apache2 start

On CentOS

service httpd restart

If you want to make sure that the oci8 is configured correctly create a php page with the following code:

<?php
 
if (function_exists('oci_connect')) {
echo "Oci8 is configured successfully";
}else{
echo "Error";
}
 
?>

And copy it on the public_html folder, on the browser make it run:

server_name/test.php

Once the installation finished, log in to ProcessMaker and go to Designer choose a process and click on Database Connections tab, when a new connection is created, the Oracle engine will display:

Using PHP OCI8 and Oracle

Try out a simple script, testoci.php Modify the connection credentials to suit your database and load it in a browser. This example lists all tables owned by the user HR:

<?php
 
$conn = oci_connect('hr', 'hr_password', 'mymachine.mydomain/orcl');
 
$stid = oci_parse($conn, 'select table_name from user_tables');
oci_execute($stid);
 
echo "<table>\n";
while (($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) != false) {
    echo "<tr>\n";
    foreach ($row as $item) {
        echo "  <td>".($item !== null ? htmlentities($item, ENT_QUOTES) : "&nbsp;")."</td>\n";
    }
    echo "</tr>\n";
}
echo "</table>\n";
 
?>

Troubleshooting

Check the Apache error log file for startup errors.

Temporarily set display_error=On in php.ini so script errors are displayed. Switch it back off when finished for security reasons.

Chapter 9 of The Underground PHP and Oracle Manual contains information about common connection errors and discusses alternative ways to set environment variables.

Oracle's SQL*Plus command line tool can be downloaded from the [www.oracle.com/technetwork/database/features/instant-client/index-100365.html Instant Client] page to help resolve environment and connection problems. Check SQL*Plus can connect and then ensure the Environment section (not the Apache Environment section) of phpinfo.php shows the equivalent environment settings.

Linux Specific Help

If using Instant Client ZIP files, make sure the two packages are unzipped to the same location. Make sure a symbolic link libclntsh.so points to libclntsh.so.11.1.

Set all required Oracle environment variables in the shell that starts Apache.

Debian/Ubuntu
Oracle 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.

Other Types of Databases

If using another type of database that ProcessMaker does not support, then it is possible to connect to that database using PHP's database extensions inside a trigger. Many types of databases have their own specialized functions such as oci_connect() or mysql_connect(), but almost all databases and spreadsheets also support the Open Database Connectivity (ODBC) protocol.

Unfortunately, database connections created in triggers can not be used like normal Database Connections in ProcessMaker. In order to be queried like a normal database, using the SQL Connection and SQL properties in DynaForm fields, first the results of the database query will have to be stored as an associative array of associative arrays in the $_SESSION superglobal variable, so they can be accessed by DynaForm fields. See the example for populating a dropdown box.

ODBC

The specialized PHP functions for each type of database are generally easier to use, but PHP's ODBC functions offer a standardized way to connect to any data source.

Installing ODBC

Before trying to use ODBC in PHP, make sure that the odbc module is installed in PHP, by going to the command line and issuing the command:

php -m

If "odbc" doesn't appear in the list of modules, then it will need to be installed:

Debian/Ubuntu:

apt-get install unixodbc php5-odbc

Red Hat/CentOS/Fedora:

yum install unixODBC unixODBC-devel php-odbc

SUSE/OpenSUSE:

yast2 -i php5-odbc

Windows:
The ODBC module should be installed by default in PHP.

Using ODBC in Triggers

First, establish a connection to the database with the obdc_connect() (or obdc_pconnect() for a persistent connection):

resource odbc_connect( string $dsn, string $user, string $password  [, int $cursor_type])

The first parameter is the data source name, which contains the custom parameters needed to connect to different types of databases. It might contain something like:

"Driver={DRIVER-NAME};Server=SERVER-URL;Database=DB-NAME;"

but it varies widely. See this list of connection strings.

Populating a Textbox

To set the value of a DynaForm textbox, fire trigger before the DynaForm which queries the database using ODBC and then sets the value of the case variable for the textbox.

For example, to query a Visual FoxPro database and set the value of a textbox named "ClientName":

$dns = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBC;SourceDB=c:\somepath\contacts.dbc;Exclusive=No"; 
$con = obdc_connect($dns, "", "");
if ($con !== false) {
   $res = odbc_exec($con, "SELECT FIRSTNAME, LASTNAME FROM CLIENTS WHERE CLIENT_ID='ARG29'");
   if ($row = odbc_fetch_array($res))
      @@ClientName = $row['FIRSTNAME'] . ' ' . $row['LASTNAME'];
}

Populating a Dropdown Box

To populate the list of options in a dropdown box, create an associative array of associative arrays, which can be queried like a normal table.

For example, this trigger uses OBDC to connect to an Excel spreadsheet and populate a dropdown box:

$aClients = array();
$cnt = 1;
$dns = "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=c:\somepath\Clients.xls;DefaultDir=c:\somepath";
$con = obdc_connect($dns, "", "");
if ($con !== false) {
   $res = odbc_exec($con, "SELECT CLIENT_ID, CLIENT_NAME from CLIENT_LIST");
   while ($row = (odbc_fetch_array($res)))
      $aClients[$cnt++] = $row;
}
else {
   G::SendMessageText("Unable to access Excel file 'c:\somepath\Clients.xls'.", "ERROR");
}
#Make $aClients available to be queried in the DynaForm like a table from a database:
global $_DBArray;
$_DBArray['CLIENTS_LIST'] = $aClients;
$_SESSION['_DBArray'] = $_DBArray;

Then in the DynaForm create a dropdown box with the sqlconnection="dbarray" property in the XML definition. To populate the list of options, use an SQL query to access the CLIENTS_LIST array:

SELECT CLIENT_ID, CLIENT_NAME FROM CLIENTS_LIST

Where CLIENT_ID will become the value for each option and CLIENT_NAME will become the label for each option in the dropdown box. The complete XML definition for the dropdown box would be:

<SelectClient type="dropdown" required="0" readonly="0" savelabel="0" 
  mode="edit" options="Array" sqlconnection="dbarray">
  SELECT CLIENT_ID, CLIENT_NAME FROM CLIENTS_LIST
  <en>Select Client</en>
</SelectClient>

Populating a Grid

To populate a DynaForm grid, create an associative array of associative arrays and assign it to the case variable for the grid. The keys in the associative arrays must be the same as the names of the grid fields, so use AS in the SQL query to rename the fields from the table if they don't match the field names in the grid.

For example, this trigger uses OBDC to connect to an Access database and populate a DynaForm grid named "ProductsGrid" which has the fields "serialNo", "productTitle", "description" and "listPrice":

$dns = "Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\somepath\Products.mbd";
$con = obdc_connect($dns, "someuser", "p4sSw0rd");
if ($con !== false) {
   $aProducts = array();
   $cnt = 1;
   $sql = "SELECT SERIAL AS serialNo, TITLE AS productTitle, DESC AS description, 
      PRICE AS listPrice FROM PRODUCTS WHERE CATEGORY='current'";
   $res = odbc_exec($con, $sql);
 
   while ($row = (odbc_fetch_array($res)))
      $aProducts[$cnt++] = $row;
 
   @@ProductsGrid = $aProducts;
}
else {
   G::SendMessageText("Unable to connect to Access database!", "ERROR");
}

Database Connections

Contents

Creating a New Database Connection

MySQL

PostgreSQL

Windows

GNU/Linux or UNIX

Microsoft SQL Server

Solving issues with UTF-8 characters

Setup FREETDS

Change MSSQLResultSet.php

Windows

Solving Issues with UTF-8 Characters

GNU/Linux or UNIX

Red Hat/CentOS/Fedora

Debian/Ubuntu

SUSE/openSUSE

Oracle

Windows

GNU/Linux

Enabling the PHP OCI8 Extension on Linux

Step 1: Installing libraries

Step 2: Create the environment variable

Step 3: Download the OCI Library

Step 4. Installing directly using pecl

Step 4: Installing by descompressing the OCI Library

Step 5: Configure oci8

Step 6:Adding the oci8 extension

Step 7: Checking the libraries installed

Step 8: Restart apache services

Using PHP OCI8 and Oracle

Troubleshooting

Linux Specific Help

Other Types of Databases

ODBC

Installing ODBC

Using ODBC in Triggers

Populating a Textbox

Populating a Dropdown Box

Populating a Grid

Personal tools

Namespaces

Variants

Views

Actions

Search

Navigation

Toolbox

In other languages

Share This

Change `MSSQLResultSet.php`

Step 6:Adding the `oci8` extension