IFS Manage Database Connections

Back to Tips and Tricks Back to Release Notes for IFS Workflow 1.4 Release Notes

1 Managing Database Connections
2 External API Table Configuration

Managing Database Connections

Use ProcessMaker’s API to obtain all database connections for a specific process.

Endpoint: {server}/api/1.0/{workspace}/project/{processUid}/database-connections

Using a ProcessMaker Function (PMFProcessList), a list of process uids can be obtained. Loop through each process uid, calling the REST service to obtain the database connection information, and store the relevant information in a grid.

@@processUids = PMFProcesslist();  
@@connectionUids = array();  
@@dbUpdateTypeArray = array(array('mssql', 'Microsoft SQL Server'), array('mysql', 'MySQL'), array('oracle', 'Oracle'));  
$index = 1;  
foreach(@@processUids as $process) {  
    $resource_url = 'ddevwf2/api/1.0/workflow/project/'.$process['guid'].  
    '/database-connections';  
    $curl = curl_init($resource_url);  
    curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);  
    curl_setopt($curl, CURLOPT_CUSTOMREQUEST, 'GET');  
    curl_setopt($curl, CURLOPT_HTTPHEADER, array('Content-
Type: application/json', 'Authorization: Bearer’.@@apiToken ));  
    $response = curl_exec($curl);  
    $httpResponse = curl_getinfo($curl, CURLINFO_HTTP_CODE);  
    curl_close($curl);  
    if ($httpResponse == 200 || $httpResponse == 201) {  
        $content = json_decode($response);  

        foreach($content as $db) {  
            $databaseName = $db -> dbs_database_name;  
            $databaseServer = $db -> dbs_server;  
            $databaseUid = $db -> dbs_uid;  
            $databaseTypeOriginal = $db -> dbs_type;  
            $databaseTypeClean = '';  
            switch ($databaseTypeOriginal) {  
                case 'oracle':  
                    $databaseTypeClean = 'Oracle';  
                    break;  
                case 'mssql':  
                    $databaseTypeClean = 'Microsoft SQL Server';  
                    break;  
                case 'mysql':  
                    $databaseTypeClean = 'MySQL';  
                    break;  
               default:  
                    break;  
            }  
            if ($databaseName != '') {  
                @@connectionUids[$index]['process'] = $process['name'];  
                @@connectionUids[$index]['dbs_database_name'] = $databaseName;  
                @@connectionUids[$index]['dbs_uid'] = $databaseUid;  
                @@connectionUids[$index]['dbs_type'] = $databaseTypeOriginal;  
                @@processGrid[$index]['process'] = $process['name'];  
                @@processGrid[$index]['databaseName'] = $databaseName;  
                @@processGrid[$index]['databaseServer'] = $databaseServer;  
                @@processGrid[$index]['databaseType'] = $databaseTypeClean;  
                $index++;  
            }  
        }  
    }

Now all database connections can be viewed within the grid and the user may select whichever database connection they wish to update. To select a database connection, select the checkbox on the row of that database connection.

The following information is required to update a database connection:

To call the Update Database Connection REST Service, the process Id and database connection Id are required as well. These can be obtained from the foreach loops within this trigger.

Endpoint: {server}/api/1.0/{workspace}/project/{processUid}/database-connection/{databaseUid}

$databaseId = '';  
foreach(@@processGrid as $process) {  
        if ($process['updateDatabase'] == 1) {  
            foreach(@@processUids as $processId) {  
                if ($processId['name'] == $process['process']) {  
                    $processUid = $processId['guid'];
                }  
            }  
            foreach(@@connectionUids as $connection) {  
                if ($connection['dbs_database_name'] == $process['databaseName'] and $connection['process'] == $process['process']) {  
                    $databaseId = $connection['dbs_uid'];
                }  
            }  
            $resource_url = 'ddevwf2/api/1.0/workflow/project/'.$processUid.  
            '/database-connection/'.$databaseId;  
            $curl = curl_init($resource_url);  
            $curl_post_data = array("dbs_type" => @@dbUpdateType, "dbs_server" => @@dbUpdateServer, "dbs_database_name" => @@dbUpdateName, "dbs_username" => @@dbUpdateUser, "dbs_password" => @@dbUpdatePassword, "dbs_port" => @@dbUpdatePort, "dbs_encode" => "UTF8");  
            curl_setopt($curl, CURLOPT_POSTFIELDS, json_encode($curl_post_data, true));  
            curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);  
            curl_setopt($curl, CURLOPT_CUSTOMREQUEST, 'PUT');  
            curl_setopt($curl, CURLOPT_HTTPHEADER, array('Content-Type: application/json', 'Authorization: Bearer '.@@apiToken));  
            $response = curl_exec($curl);  
            $httpResponse = curl_getinfo($curl, CURLINFO_HTTP_CODE);  
            curl_close($curl);

External API Table Configuration

Please refer to Environment Configuration .