Cannot prepare parameterized SQL query using "?"

[Viktor-PRI]

Starting from version odbc 1.4, unable to prepare sql queries with "?" parameters. It worked in versions 1.3.5 and older.
for example: Oracle use ":param", Postgres - "$1" , FirebirdSQL - "?"
I understand that this DBMS is rare, but how can I help to fix this error?

Example for FirebirdSQL

###### OK ######
> packageVersion("odbc")
[1] ‘1.3.5’
> prepare <- dbSendQuery(conn, "select * from sec$users where sec$user_name = ?")
> prepare
<OdbcResult>
  SQL  select * from sec$users where sec$user_name = ?
  ROWS Fetched: 0 [complete]
       Changed: 0

###### ERROR ######
> packageVersion("odbc")
[1] ‘1.4.1’
> prepare <- dbSendQuery(conn, "select * from sec$users where sec$user_name = ?")
Error: nanodbc/nanodbc.cpp:1710: 07002
[ODBC Firebird Driver]COUNT field incorrect 

[detule]

hi @Viktor-PRI - thanks for the report.

We changed some of the defaults with the 1.4.0 release - now when sending/executing a query, if the params argument is NULL ( default ), the execution is routed to an odbc API that does not allow for statement/preparing/parameter binding ( but is in theory, more performant ).

In your use case - I suspect the problem can be solved either by specifying immediate = FALSE, or by passing the parameters in the params argument at the same time you are sending the statement to be prepared.

> prepare <- dbSendQuery(conn, "select * from rdb$config where rdb$config_id = ?")
Error: nanodbc/nanodbc.cpp:1710: 00000
[ODBC Firebird Driver]COUNT field incorrect
> prepare <- dbSendQuery(conn, "select * from rdb$config where rdb$config_id = ?", immediate = FALSE)
> prepare
<OdbcResult>
   SQL  select * from rdb$config where rdb$config_id = ?
   ROWS Fetched: 0 [complete]
        Changed: 0
>

[hadley]

@detule I do think it was a mistake to set immediate = is.null(params) here, since dbSendQuery() is often followed by a dbBind(). I'm happy to fix this since I made the change.

[Viktor-PRI]

@hadley Yes, I use it...

    prepare <- dbSendQuery(conn, update_sql, immediate = FALSE)
    
    for (i in 1:nrow(DT)) {
      dbBind(prepare, DT[i], batch_rows = 1)
    }

    dbClearResult(prepare)

[detule]

Thanks @Viktor-PRI. A bit curious about the use case - binding the rows one-by-one is somewhat idiosyncratic, but maybe that's a function of the type of query your are executing.

For comparison, dbWriteTable uses a prepared statement - we prepare that INSERT statement with, at times, hundreds/thousands of rows at a time.

@hadley Copy / reverting the behavior of dbSendQuery sounds good.

[Viktor-PRI]

Thanks @detule for your interest.
Using binding one-by-one is bad idea, but FirebirdSQL 3 and older don't support multirow batch.