Search code examples
mysqljoomlamariadb

How do I set the flag CLIENT_MULTI_STATEMENTS to allow for calling multiple stored procedures?

How do I set the flag CLIENT_MULTI_STATEMENTS for calling multiple stored procedures in a single stored procedure? I have 30 stored procedures I would like to execute with a button click to process user data. It seems that creating a single procedure to execute the 30 others would be easiest but this triggers a syntax error. Executing one stored procedure in a stored procedure works just fine, adding even one more creates an error. My research has indicated by setting the CLIENT_MULTI_STATEMENTS flag I can execute multiple procedures but there is little documentation on how to do this within a CMS like Joomla. I am working with Joomla 3 / mysql 5.7 / mariadb. Do I have to modify core code? Or is there a way to set the flag externally?

Solution

  • I was not able to figure out how to enable CLIENT_MULTI_STATEMENTS in Joomla. I was able to solve my issue by using this bit of code to split my queries into an array of queries that the server will allow to process.

    JDatabaseDriver::splitSql($query);

$query =  "CALL ...;CALL...;...;";

$queries = $db->splitSql($query);

foreach( $queries AS $sql ) 
{
$db->setQuery($sql) ->execute();
$db->query();
}

    Cheers