dbGetQuery no longer works with stored procedures

[gitkatdev]

Issue Description and Expected Result

I've just install R version 4.0.3 (until last week I was using R 3.6) with DBI 1.1.1 and I'm facing a new error that I've never seen before. I have a MySQL database (version 8.0.18) and I use DBI to retrieve data. Specifically, the database includes some stored procedures that prepare a sql statement based on input parameters and execute it. Until last week I was able to retrieve the output of these stored procedures using dbGetQuery (as indicated in the DBI documentation) while after the upgrade to R 4.0.3 I get the following error:

Error in result_fetch(res@ptr, n) : nanodbc/nanodbc.cpp:2695: 24000: [MySQL][ODBC 8.0(w) Driver][mysqld-8.0.23]Fetch without a SELECT

Database

[MySQL][ODBC 8.0(w) Driver][mysqld-8.0.23]

Reproducible Example

Assume there is a simple table called "test_1" like this: intId | strName 1 | Rome 2 | New York 3 | Prague and a stored procedure # called "my_stored_proc" with two input parameters (table_name and id) and the following structure:

BEGIN: SET @t1 =CONCAT('SELECT strName FROM ', table_name, ' WHERE intId =', id, ';' ); PREPARE stmt3 FROM @t1; EXECUTE stmt3; DEALLOCATE PREPARE stmt3; END

In R I then run the following code:

library(odbc)
library(DBI)
strCnn <- paste0("Driver={MySQL ODBC 8.0 UNICODE Driver};
                              Server=***;
                              Database=***;
                              Port=***;
                              User=***;
                              Password=***;
                              trusted_connection=true;
                              ENABLE_LOCAL_INFILE=1;")
cnn <- dbConnect(odbc::odbc(), .connection_string = strCnn, bigint="numeric"

ref_tbl <- "test_1"
ref_id <- 1
out <- paste0("CALL my_stored_proc('", ref_tbl, "', ", ref_id, ");") %>% dbGetQuery(CNN_NOAH, .) %>% as.numeric()

In the paste the above code returns out == "Rome" while I now get the error mentioned above.

Is this an error on my side or a bug?

Thank you in advance Isacco

[r2evans]

I'm not familiar with the CALL format, but I can do DBI::dbGetQuery(con, "exec my_stored_proc arg1 arg2") without a problem. Is that different?

[Penna88]

Hi, I am facing the very same issue with CALL PROCEDURE format in MySQL DB when I updated odbc package from version 1.2.3 to 1.3.0 or higher. I have the very same connection_string as gitkatdev and I received the following error:

Code: dbGetQuery(cnn, "CALL prc_xxx(15195)") Error in result_fetch(res@ptr, n) : nanodbc/nanodbc.cpp:2695: 24000: [MySQL][ODBC 8.0(w) Driver][mysqld-8.0.18]Fetch without a SELECT

However, I also run the following without a warning: Code: res <- dbSendQuery(cnn, "CALL prc_xxx(15195)") <OdbcResult> SQL CALL prc_xxx(15195) ROWS Fetched: 0 [incomplete] Changed: 2

The res correctly identified that the resulting query has 2 rows. In addition, I also obtain the correct first row with Code: dbFetch(res, n = 1)

However, I have the very same error I had with dbGetQuery with with dbFetch(res, n = 2) or dbFetch(res, n = -1).

Any suggestion? It seems that something in dbFetch has changed in 1.3.0.

[Penna88]

Dear all,

Quick update on my comments above. It seems that dbFetch works also for Stored Procedure in case I call dbListTables before fetching.

WORKING CODE:

res <- dbSendQuery(cnn, "CALL prc_xxx(?)", params = 15195) dbListTables(cnn) dbFetch(res)

I also noticed that <OdbcResult> changes as follows after calling dbListTables()

Without dbListTables()

SQL CALL prc_xxx(15195) ROWS Fetched: 0 [incomplete] Changed: 2

After dbListTables()

SQL CALL prc_xxx(15195) ROWS Fetched: 0 [incomplete] Changed: 0

I am still cluesless on the real reason behind this behaviour. Hope this may shed lights on this issue and apologies for not being able to debug the underlying code.

[detule]

@Penna88 can you test your use case with: https://github.com/r-dbi/odbc/pull/546

Thanks for your report.

[Penna88]

Good morning Detule,

glad to confirm that your fix solved the problem.

[detule]

Thanks for taking the time to verify / appreciate it.