PHP calling MySQL functions and procedures

Following on from a recent post on creating MySQL functions and procedures from PHP, the next logical step is to make use of the functions and procedures.

To call a MySQL procedure using the Joomla database classes you make use of a CALL statement.

  $query = 'CALL #__dbprocedure (' . $userId . ',"' . $date . '","' . $ndate . '","' . $datap . '",' . $npwds . ')';
  $db->setQuery($query);
  $retn= $db->query();   // Returns false if query fails to execute

To call a MySQL function it is a simple select statement that is used.

      $query = 'SELECT #__dbfunction ( ' . $userId . ',"' . $chk . '")';
      $db->setQuery($query);
      $ret = $db->loadResult();

Where #__dbfunction is a defined (or bespoke) MySQL function.

One issue that was encountered with the use of the function was that the MySQL function was generating an error about the binlog_format which in the database being used was set to ROW.  The binlog_format is important if replication of the database is in place, but it is noted that in later versions (6) of MySQL the default format is MIXED.   One way to resolve the issue is to change the binlog_format.  This was achieved with the following code.

          $db->setQuery('SET SESSION binlog_format = "MIXED"');
          $db->query();

For sites where database replication is being used any statements that change the binlog_format needs to be closely checked to ensure that the underlying replication is not impacted.  It is suspected that the majority of Joomla sites are probably not using the underlying database replication, although this may be incorrect.  Certainly in the database being used replication was not a consideration.