1

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?

The procedures are called in order as seen below, the first procedure always executes without issue. I have checked the mariadb documentation and mysql and tried every iteration of the proper syntax I could think of, which is why I think it is a flag issue.
By default, mysql will not allow execution of multiple statements in this version, as I understand it. Which is why I would like to figure out how to set the flags as referenced in the documentation.

CALL joomla3.Answers_to_Translated_RTN1();
CALL joomla3.Answers_to_Translated_RTN2();
CALL joomla3.UC_to_Results_RTN3();

...

MySQL said: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CALL joomla3.Answers_to_Translated_RTN2(); CALL joomla3.UC_to_Results_RTN3(); ' at line 2

Improve this question
7
  • Please include the syntax error that you are experiencing. This might not only serve the volunteers who can help you with this issue, it may also improve the searchability of this page for future researchers who are experiencing a similar issue.
    – mickmackusa
    Sep 13, 2018 at 4:27
  • May we see more of the error message? stackoverflow.com/q/23515347/2943403 Is the error just pointing to the start of the second stored procedure? We may stand a better chance of helping you if we can understand more about your process/code.
    – mickmackusa
    Sep 13, 2018 at 20:23
  • Perhaps more specifically on that linked page: stackoverflow.com/a/43493692/2943403
    – mickmackusa
    Sep 13, 2018 at 20:30
  • Might you be content with a single "master" stored procedure which calls a set batch of stored procedures? Or do you require the versatility/flexibilty of being able to pick specific/individual stored procedures throughout your codebase?
    – mickmackusa
    Sep 15, 2018 at 5:18
  • I am trying to use a "master" stored procedure (SP) to execute all others but only the first SP executes each time I try. I was hoping to set the client_multi_statements flag to enable execution of multiple SPs but I cannot figure out how to do that.
    – BabyCode123
    Sep 18, 2018 at 17:59

1 Answer 1

Reset to default
2

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.