Please rate how useful you found this document: 
Average: 2 (4 votes)

Other Types of Databases

It is possible to connect to a database that ProcessMaker does not support by 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. To query the database like a normal database when using the SQL Connection and SQL properties in Dynaform fields, the results of the database query will first have to be stored as an associative array of associative arrays in the $_SESSION superglobal variable so they can be accessed by Dynaform fields.

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 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 the trigger BEFORE the Dynaform that queries the database using ODBC and then set the value of the case variable of 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 Grid

To populate a Dynaform grid, create an associative array of associative arrays and assign it to the case variable of 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 = new G();
    $g->SendMessageText("Unable to connect to Access database!", "ERROR");
}