René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"Joomla 5:    MySQL Error -- 2014 - Commands out of sync; you can't run this command now","articleBody":"I am working to upgrade a module from Joomla 3 to Joomla 5. I'm using Joomla 5.0.2, PHP 8.2 and the module I'm upgrading has multiple dropdowns. I'm running into an error that I have not seen before and I'm stuck. This is the error:\r\n\r\n2014 - Commands out of sync; you can't run this command now\r\n\r\nI've built modules like this in Joomla 3 many times. The process is I'm calling a Stored Procedure to get the dataset I need to create the dropdown object, I return the created object and I move to the next dropdown. The functions are called sequentially from the main php file in the module to the helper.php file.\r\n\r\nWhen I run the code this the first dropdown object is returned correctly. However, when the code reaches the setQuery line in the dropdown_2 function in the helper.php file I get the \"out of sync\" error listed above.\r\n\r\nI thought the issue was with the dropdwon_2 function, but when I comment out the call to dropdown_1 function I receive back the dropdown_2 object and the error occurs in the dropdown_3 function, same place in the setQuery line. Any combination of running the different functions seems to produce the same result.\r\n\r\n### Steps to reproduce the issue\r\n\r\nHere's an example of the code in my helper.php file.\r\n\r\npublic static function GetDropdown1($name, $details) \r\n{\r\n\t\t$db = Factory::getContainer()-\u003eget('DatabaseDriver');\t\r\n\t\t$db-\u003esetQuery(\"call StoredProcedure_1\");\r\n\t\t$results1 = $db-\u003eloadObjectList();\r\n\t\t\r\n\t\t...foreach to create the $dropdown_1 object\r\n\t\t\r\n\t\treturn $dropdown_1;\r\n}\r\n\r\npublic static function GetDropdown2($name, $details) \r\n{\r\n\t\t$db = Factory::getContainer()-\u003eget('DatabaseDriver');\t\r\n\t\t$db-\u003esetQuery(\"call StoredProcedure_2\");    //   \u003c--- error occurs here\r\n\t\t$results2 = $db-\u003eloadObjectList();\r\n\t\t\r\n\t\t...foreach to create the $dropdown_2 object\r\n\t\t\r\n\t\treturn $dropdown_2;\r\n}\r\n\r\npublic static function GetDropdown3($name, $details) \r\n{\r\n\t\t$db = Factory::getContainer()-\u003eget('DatabaseDriver');\t\t\r\n\t\t$db-\u003esetQuery(\"call StoredProcedure_3\");\r\n\t\t$results3 = $db-\u003eloadObjectList();\r\n\t\t\t\t\r\n\t\t...foreach to create the dropdown_3 object\r\n\t\t\r\n\t\treturn $dropdown_3;\r\n\t\r\n}\r\n\r\npublic static function GetDropdown4($name, $details) \r\n{\r\n\t\t$db = Factory::getContainer()-\u003eget('DatabaseDriver');\t\t\r\n\t\t$db-\u003esetQuery(\"call StoredProcedure_3\");\r\n\t\t$results4 = $db-\u003eloadObjectList();\r\n\t\t\t\t\r\n\t\t...foreach to create the dropdown_4 object\r\n\t\t\r\n\t\treturn $dropdown_4;\t\r\n}\r\n\r\n\r\n### Expected result\r\n\r\nIn Joomla 3 this code would output the 4 dropdown objects I need for the module.  In Joomla 5 (and I assume Joomla 4) it is not possible to run back-to-back-to-back functions like this calling a stored procedure.\r\n\r\n### Actual result\r\n\r\nActual result is I receive this error:\r\n\r\n2014 - Commands out of sync; you can't run this command now\r\n\r\n### System information (as much as possible)\r\n\r\nJoomla version:   5.0.2\r\nPHP version:        8.2.9\r\nMySQL version:   8.0.28\r\nDB Type:              MySQLi\r\nServer:                 AWS Linux 2023\r\n\r\n### Additional comments\r\n\r\n**Important:**  Changing the DB type in Joomla to MySQL (PDO) solves the problem.  When I run the page containing the module after changing the DB type to MySQL (PDO) it runs as expected with no errors.  However, I would prefer to use MySQLi if possible.\r\n\r\nResearching the issue, it appears to be related to prepared statements in PHP.  My understanding is that MySQL (PDO) does not use prepared statements and probably that is why it works correctly.  \r\n\r\nLet me know what else you need to review this case.  Thanks.","author":{"url":"https://github.com/mbond87","@type":"Person","name":"mbond87"},"datePublished":"2024-01-24T19:49:36.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":3},"url":"https://github.com/296/database/issues/296"}