[geoken90]

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 ??

[amosbatto]

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:

Suggest fun.json

(1.9 KiB) Downloaded 281 times

Which has a suggest field with the ID and variable named "selectTask" and the SQL:
SELECT TAS_UID, TAS_TITLE FROM TASK WHERE PRO_UID=@@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.

[geoken90]

Thanks Amos!

I will try that.

Before I see your suggestion,
i wrote the sql query of the procedure inside the sql property of the suggestion control directly and
passed the @@filter param and it worked, so i understood at that time that only select statements work by default,
as you pointed out.

[amosbatto]

I played with this a bit more and you can't use Propel to execute a MySQL stored procedure. I recommend using mysqli in a trigger. See this example:
viewtopic.php?f=41&t=730730&p=815159&hi ... li#p815159