[justinopsky]

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!

[amosbatto]

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 (40.66 KiB) Viewed 8565 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 (21.73 KiB) Viewed 8565 times

Here is the process if you want to test it:

Call_MySQL_procedure-1.pmx

(31.13 KiB) Downloaded 443 times

[justinopsky]

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.

[amosbatto]

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).

[MahmoudNazparvar]

hi
use executequery like this :
executequery("exec spname param1,param2" , $db)

[eostro]

Can we execute stored procedures in the Enterprise version?