Open vanhry opened 2 years ago
Thanks. Can you please post a reproducible example here?
@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
@krlmlr is my example above suitable for you as a reproducible example of my issue?
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?
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
@krlmlr is my example above proper for you?
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)
}
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
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)
})
}
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.
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.
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
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