Call of stored procedures gives `Commands out of sync` error

[vanhry]

Hello everyone!

I get error Commands out of sync; you can't run this command now [2014] after first call of Stored Procedure.

You can find example of my code here

Actually, my problem is duplication of this issue, but there is no solution for it yet,

There is solution only for RMySQL (which deprecated and I don't want to use it) here

[krlmlr]

Thanks. Can you please post a reproducible example here?

[vanhry]

@krlmlr
Hope it helps, actually it should imitate the logic of my issue

CREATE TABLE `users_all` (
  `username` varchar(36) DEFAULT NULL,
  `age` int DEFAULT NULL
)

INSERT INTO users_all (username, age) VALUES ("user1", 21)
INSERT INTO users_all (username, age) VALUES ("user2", 25)
INSERT INTO users_all (username, age) VALUES ("user3", 31)

CREATE PROCEDURE `GetUser` (in in_username VARCHAR(36))
BEGIN
	SELECT * FROM users_all WHERE username = in_username;
END

pool <- pool::dbPool(
    user = Sys.getenv("DB_USER"),
    password = Sys.getenv("DB_PASS"),
    host = Sys.getenv("DB_HOST"),
    dbname = "your_schema_here",
    drv = RMariaDB::MariaDB()
  )
# gives right data frame
dbGetQuery(pool, "CALL GetUser('user1')")  
# the I call this and error occurs
dbGetQuery(pool, "CALL GetUser('user1')") 

#Error: Commands out of sync; you can't run this command now [2014]

Here is the solution for RMySQL link
And the reason of the problem described there, but I don't know how to implement it with RMariaDB

[vanhry]

@krlmlr is my example above suitable for you as a reproducible example of my issue?

[krlmlr]

Thanks, I can't run the CREATE PROCEDURE command in the mysql command-line tool:

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 'END' at line 2

Could you please share the setup code either as a self-contained .sql script that I could send to mysql, or as a sequence of dbExecute() calls?

[vanhry]

Sorry about this. It should work

DELIMITER //
CREATE PROCEDURE `GetUser` (in in_username VARCHAR(36))
BEGIN
	SELECT * FROM users_all WHERE username = in_username;
END

[vanhry]

@krlmlr is my example above proper for you?

[jcheng5]

Really good description of the underlying problem here: https://stackoverflow.com/questions/614671/commands-out-of-sync-you-cant-run-this-command-now

So the immediate problem is that the RMySQL solution uses functions that aren't available in RMariaDB?

while(dbMoreResults(db) == TRUE) {
  dbNextResult(db)
}

[vanhry]

So the immediate problem is that the RMySQL solution uses functions that aren't available in RMariaDB?

Yes, that's a problem.
I don't want to use the deprecated RMySQL, but I want to be able to return the values from stored procedure's call

[mbarneytu]

I get this error when I call a simple stored procedure from my shiny app more than once -- but only on shinyapps.io, not when running on my local machine. The stored procedure definition:
DELIMITER $$ CREATE PROCEDURE sp_test() NO SQL select * from site$$ DELIMITER ;

And my shiny code:

library(pool)
library(shiny)

pool <- dbPool(
  RMariaDB::MariaDB(), 
  dbname = Sys.getenv("HYDROP_DBNAME"),
  host = Sys.getenv("HYDROP_HOST"),
  user = Sys.getenv("HYDROP_USER"),
  password = Sys.getenv("HYDROP_PWD")
)

onStop(function() {
  poolClose(pool)
})

ui <- fluidPage(
  actionButton("goBtn", "GO"),
  tableOutput("table")
)

server <- function(input, output, session) {
  
  observeEvent(input$goBtn, {
    query <- "CALL sp_test();"
    res <- dbGetQuery(pool, query)
    output$table <- renderTable(res)
  })  
}

[krlmlr]

Thanks, sorry this fell off the radar.

I need to take a closer look. Multiple result sets aren't really defined in the DBI specs at this time (which is I think the scope of dbMoreResults() and dbNextResult()). But the stored procedures in this example are returning only one result set. I do wonder why -- is there some meta-information returned in a second result set when running a stored procedure?

We could default to returning the first result set, which might solve the issue at hand. Not sure what other problems arise in other contexts.

[mbarneytu]

This may not be helpful, but I found that the same error occurs on shinyapps.io whether I use the RMySQL driver or RMariaDB. If I run the code locally, RMySQL reports the error, but RMariaDB does not.

[krlmlr]

Reprex:

con <- RMariaDB::mariadbDefault()

DBI::dbWriteTable(con, "users_all", data.frame(username = c("user1", "user2", "user3"), age = c(21, 25, 31)), overwrite = TRUE)

DBI::dbExecute(con, "DROP PROCEDURE IF EXISTS GetUser")
#> [1] 0
DBI::dbExecute(con, "
CREATE PROCEDURE `GetUser` (in in_username VARCHAR(36))
BEGIN
    SELECT * FROM users_all WHERE username = in_username;
END"
)
#> [1] 0

# gives right data frame
DBI::dbGetQuery(con, "CALL GetUser('user1')")
#>   username age
#> 1    user1  21
# the I call this and error occurs
DBI::dbGetQuery(con, "CALL GetUser('user1')")
#> Error: Commands out of sync; you can't run this command now [2014]

^{Created on 2024-04-01 with reprex v2.1.0}