Joomla 5: MySQL Error -- 2014 - Commands out of sync; you can't run this command now

[mbond87]

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:

2014 - Commands out of sync; you can't run this command now

I'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.

When 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.

I 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.

Steps to reproduce the issue

Here's an example of the code in my helper.php file.

public static function GetDropdown1($name, $details)
{
$db = Factory::getContainer()->get('DatabaseDriver');
$db->setQuery("call StoredProcedure_1");
$results1 = $db->loadObjectList();

	...foreach to create the $dropdown_1 object
	
	return $dropdown_1;

}

public static function GetDropdown2($name, $details)
{
$db = Factory::getContainer()->get('DatabaseDriver');
$db->setQuery("call StoredProcedure_2"); // <--- error occurs here
$results2 = $db->loadObjectList();

	...foreach to create the $dropdown_2 object
	
	return $dropdown_2;

}

public static function GetDropdown3($name, $details)
{
$db = Factory::getContainer()->get('DatabaseDriver');
$db->setQuery("call StoredProcedure_3");
$results3 = $db->loadObjectList();

	...foreach to create the dropdown_3 object
	
	return $dropdown_3;

}

public static function GetDropdown4($name, $details)
{
$db = Factory::getContainer()->get('DatabaseDriver');
$db->setQuery("call StoredProcedure_3");
$results4 = $db->loadObjectList();

	...foreach to create the dropdown_4 object
	
	return $dropdown_4;	

}

Expected result

In 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.

Actual result

Actual result is I receive this error:

2014 - Commands out of sync; you can't run this command now

System information (as much as possible)

Joomla version: 5.0.2
PHP version: 8.2.9
MySQL version: 8.0.28
DB Type: MySQLi
Server: AWS Linux 2023

Additional comments

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.

Researching 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.

Let me know what else you need to review this case. Thanks.

[alikon]

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

that's an interesting issue
btw

i dont't think this

that MySQL (PDO) does not use prepared statements

much more like something about "stored procedure"

[MarioML-CR]

I'm using R with RStudio and I'm having the same problem.

Error in dbSendQuery(mydb, query) :
could not run statement: Commands out of sync; you can't run this command now

I am trying to run some queries and I have noticed that when I use a stored procedure with parameters it generates the error, but only after running this procedure, the above queries do not have this problem.
In fact, if I execute this procedure with a parameter, first I have a response but the following queries do not.
I tried it on SQL Server and MySQL, both got the same result.

My code is this

library(RMySQL)
library(glue)
library(DBI)
library(odbc)

# query a un procedimiento sin parametro ---------------------------------------
query <- glue_sql("call sp_listar_cliente();", .con = mydb)
a <- dbSendQuery(mydb, query)
clientes <- fetch(a, n= -1)
dbClearResult(a)

# query a un procedimiento con parametro ---------------------------------------
p_num_documento <- '0116'
query <- glue_sql("call sp_obtener_cliente({ p_num_documento });", .con = mydb)
a <- dbSendQuery(mydb, query)
clientes2 <- DBI::fetch(a, n= -1)
DBI::dbClearResult(a)

In the previous case the first query works but the second no longer works, even if I make a simple query

If I execute them in the reverse order as follows, the storing procedure query with parameter works, but the next one no longer works.

If I execute them in the reverse order as follows, the storing procedure query with parameter works, but the next one no longer works.

Note that it is in reverse order.

# query a un procedimiento con parametro ---------------------------------------
p_num_documento <- '0116'
query <- glue_sql("call sp_obtener_cliente({ p_num_documento });", .con = mydb)
a <- dbSendQuery(mydb, query)
clientes2 <- DBI::fetch(a, n= -1)
DBI::dbClearResult(a)

# query a un procedimiento sin parametro ---------------------------------------
query <- glue_sql("call sp_listar_cliente();", .con = mydb)
a <- dbSendQuery(mydb, query)
clientes <- DBI::fetch(a, n= -1)
DBI::dbClearResult(a)

If anyone has a solution it would be a great help.

[MacJoom]

As a workaround you can run $db->disconnect(); after you have your results.