Questions and discussion about using ProcessMaker: user interface, running cases & functionality

Moderator: amosbatto

#815146
Hello
I have a connection to an external database in SQL Server and I need to run Stored Procedure (SP) in the database through processMaker via triggers.

I've been consulting the forum, but the information is very ambiguous, some topics say that you have to change the code of some files so that the processmaker can run SP and in others you only have questions about errors.
I have version 3.2.2 Community

What is the best way to run SPs in ProcessMaker?
Do I have to change any code in the configuration files? If so, which ones?
Can anyone help me?

Thank you!
#815159
I can't get stored procedures to work with the version of Propel used by MySQL, but you can use mysqli in a trigger.

For example, if you have the following stored procedure in MySQL:
Code: Select all
delimiter $$

create procedure getUsers(in search_string varchar(64)) 
begin 
  declare user_query varchar(66);
  set user_query = concat("'%",search_string,"%'"); 
  select * from USERS where USR_USERNAME = user_query; 
end$$

delimiter ;
And you have the following DynaForm:
formFindUsersInDesigner.png
formFindUsersInDesigner.png (40.66 KiB) Viewed 868 times
The "Select User" dropdown has its datasource set to "array variable" and its data variable set to "@@userOptions". The "Users List" grid is associated with the "userGrid" variable and the IDs of its fields are: userId, userName, firstName and lastName

The DynaForm has the following JavaScript code:
Code: Select all
$("#action").setValue(""); //reset action when form loads

//when the "Search" button is clicked, set the action to "search" 
//so subsequent trigger knows to search for a user
$("#searchBtn").find("button").click(function() {
  console.log("search");
  $("#action").setValue("search"); 
})
And the following trigger is set to fire after the Dynaform:
Code: Select all
//set to the ID of the Dynaform:
$dynaformId = '5740412495b4810d1358583096516821'; 
//set to the ID of the DB connection or 'workflow' if a PM Table:
$dbConnectionId = 'workflow';
   
if (@@action == 'search') {
      
   //use addslashes() instead of mysql_real_escape_string() if not a MySQL database: 
   $srch = mysql_real_escape_string(@@userSearch);

   //change to your SQL statement: 
   $sql = "CALL getUsers('$srch')";
   
   //mysqli_connect("domain-or-ip","user", "password", "database", "port-number")
   $conn = mysqli_connect("localhost", "root", "vato1234", "wf_workflow321", "3306") or
	   die("MySQL connection error: " . mysqli_error());

   //run the store proc
   $result = mysqli_query($conn, $sql) or
     die("Query error: " . mysqli_error());

   @=userOptions = array();
   @=userGrid = array();	
   $i = 1;
	
   //loop the result set
   while ($aRow = mysqli_fetch_assoc($result)) {   
     
      //set the list is options for dropdown/suggest/radio/checkgroup:
      @=userOptions[] = array(
	     $aRow['USR_UID'],
	     $aRow['USR_FIRSTNAME'].' '.$aRow['USR_LASTNAME'].' ('.$aRow['USR_USERNAME'].')' 
      );
	  
      //set the rows in a grid:
      @=userGrid[$i++] = array(
	     'userId'    => $aRow['USR_UID'],
	     'userName'  => $aRow['USR_USERNAME'],
	    'firstName' => $aRow['USR_FIRSTNAME'],
	    'lastName'  => $aRow['USR_LASTNAME']
      ); 
   }
	
   PMFRedirectToStep(@@APPLICATION, @%INDEX, 'DYNAFORM', $dynaformId);	
}   
When a case is run, the Dynaform will display the searched users in the list of options in the dropdown and in the rows in the grid.
searchUsersInCase.png
searchUsersInCase.png (21.73 KiB) Viewed 868 times
Here is the process if you want to test it:
(31.13 KiB) Downloaded 11 times
Here is the process if you want to test it:
#815167
Thank You amosbatto
This example is in MySQL database.
Is there any example for SQLServer?
I already have a SQL datasource configured in the processmaker, in which I can execute querys directly to the database, however I needed instead of executing the querys directly in the database, I wanted to use the SPs.
#815183
I don't have MSSQL installed to test this, but I imagine that this trigger code would work:
Code: Select all
//set to the ID of the Dynaform:
$dynaformId = '5740412495b4810d1358583096516821'; 
   
if (@@action == 'search') {
      
   //use addslashes() instead of mysql_real_escape_string() if not a MySQL database: 
   $srch = addslashes(@@userSearch);

   //change to your SQL statement: 
   $sql = "CALL getUsers('$srch')";

   $serverName = "123.45.67.89";
   $aConnection = array( "Database"=>"my_database", "UID"=>"my_username", "PWD"=>"my_password");
   $conn = sqlsrv_connect( $serverName, $aConnection ) or 
       die( print_r( sqlsrv_errors(), true));

   $result = sqlsrv_query( $conn, $sql ) or    
      die( print_r( sqlsrv_errors(), true) );

   @=userOptions = array();
   @=userGrid = array();	
   $i = 1;

   while( $aRow = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC) ) {   
      //set the list is options for dropdown/suggest/radio/checkgroup:
      @=userOptions[] = array(
	     $aRow['USR_UID'],
	     $aRow['USR_FIRSTNAME'].' '.$aRow['USR_LASTNAME'].' ('.$aRow['USR_USERNAME'].')' 
      );
	  
      //set the rows in a grid:
      @=userGrid[$i++] = array(
	     'userId'    => $aRow['USR_UID'],
	     'userName'  => $aRow['USR_USERNAME'],
	    'firstName' => $aRow['USR_FIRSTNAME'],
	    'lastName'  => $aRow['USR_LASTNAME']
      ); 
   }
	
   PMFRedirectToStep(@@APPLICATION, @%INDEX, 'DYNAFORM', $dynaformId);	
}
If using Linux, you need to install the php-sqlsrv package (called php5-sqlsrv in Debian/Ubuntu).
Can I connect to firebird database?

If you want to connect the database remotely using[…]

Task with multiple users

Well i have worked for days on this project and th[…]

Multilple Flow Possibility

Hi Is it possible to have multiple flow in a sing[…]

Hi, Please tell to processmaker team for tran[…]