Questions and discussion about developing processes and programming in PHP, JavaScript, web services & REST API.

Moderator: amosbatto

Forum rules: Please search to see if a question has already asked before creating a new topic. Please don't post the same question in multiple forums.
#815003
Hi everyone,

I have a stored procedure in mysql that returns lastnames based on a given parameter.
I am trying to call this procedure for a suggest control.
So, inside sql editor i have the following :
CALL fetch_lastnames_from_cost_centers(@@filter)

The suggest control seems to work but no results are returned.

I have also tried this :
SET @param = @@filter
CALL fetch_lastnames_from_cost_centers(@param)

But no results are returned either.

How can i call the mysql stored procedure inside sql editor ??
#815020
ProcessMaker only supports "SELECT" statements. You can get around this restriction by editing the source code.

Let's say that you have the following Dynaform:
(1.9 KiB) Downloaded 13 times
Which has a suggest field with the ID and variable named "selectTask" and the SQL:
SELECT TAS_UID, TAS_TITLE FROM TASK WHERE [email protected]@selectProcess

Then, edit the file workflow/engine/src/ProcessMaker/BusinessModel/Variable.php and change the executeSqlSuggest() function defined on line 673 from:
Code: Select all
    public function executeSqlSuggest($processUid, $variableName, array $arrayVariable = array())
    { 
        try {
            return $this->executeSqlControl($processUid, $arrayVariable);
        } catch (\Exception $e) {
            throw $e;
        }
    }
To:
Code: Select all
    public function executeSqlSuggest($processUid, $variableName, array $arrayVariable = array())
    {
      /*
       * In $arrayVariable: 
       * [      
       *    app_uid:   null,
       *    del_index: 0,
       *    dyn_uid:   "3475266175b3d69c951f2a3034351254",
       *    field_id:  "selectTask",
       *    filter:    "sur",                                 //what entered by the user
       *    limit:     20,
       *    order_by:  "ASC",
       *    selectProcess: "26792814759f981ea9c1fc5090238278" //variable in SQL query
       * ]  
      */
      if ($variableName == 'selectTask') {
         //set to the ID of the DB connection or 'workflow' if a PM Table:
         $dbConnectionId = 'workflow';  //'1234567890abcdef1234567890abcedf';
         
         //change to match the name of the variable in the SQL statement:
         //use addslashes() instead of mysql_real_escape_string() if not a MySQL database: 
         $id = mysql_real_escape_string($arrayVariable['selectProcess']);
         //change to your SQL statement: 
         $sql = "SELECT TAS_UID, TAS_TITLE FROM TASK WHERE PRO_UID='$id'";
         
         $oDB = new \Propel();
         $con = $oDB->getConnection($dbConnectionId);
         $con->begin();
         $rs = $con->executeQuery($sql);
         $con->commit();
         
         while ($rs->next()) {
            $aRow = $rs->getRow();
            //change to the name of the second field returned by the SQL:
            if (stripos($aRow['TAS_TITLE'], $arrayVariable['filter']) !== false) {
               $o = new \StdClass();
               //change to the name of the fields in your SQL statement:
               $o->value = $aRow['TAS_UID']; 
               $o->text  = $aRow['TAS_TITLE'];
               $aResult[] = $o;
            }
         }
         
         return $aResult; 
      }
       
        try {
            return $this->executeSqlControl($processUid, $arrayVariable);
        } catch (\Exception $e) {
            throw $e;
        }
    }
This code should work for the suggest box in the DynaForm I gave you. Change the code to match the variable name, database connection ID, and SQL query for your suggest box.

【Q/微信729926040】如以删除请点网页快照,专业办理德国/法国/英国/加拿大各大学高端精仿文[…]

【Q/微信729926040】如以删除请点网页快照,专业办理德国/法国/英国/加拿大各大学高端精仿文[…]

【Q/微信729926040】如以删除请点网页快照,专业办理德国/法国/英国/加拿大各大学高端精仿文[…]

【Q/微信729926040】如以删除请点网页快照,专业办理德国/法国/英国/加拿大各大学高端精仿文[…]