Executemany does not work

[jakobnissen]

julia> DBInterface.executemany(stmt,
           (
               lnums,
               owner,
               segments,
               seqs,
               datetimes,
               datetimes,
               len
           )
       )
ERROR: MethodError: no method matching getconnection(::ODBC.Statement)
Stacktrace:
 [1] executemany(stmt::ODBC.Statement, params::Tuple{Vector{String}, Vector{String}, Vector{String}, Vector{String}, Vector{String}, Vect
or{String}, Vector{Int64}})
   @ DBInterface ~/.julia/packages/DBInterface/1Gmxx/src/DBInterface.jl:193
 [2] top-level scope
   @ REPL[106]:1

I'm not sure why, but it looks like just a missing implementation of a method?

[jfb-h]

Same case for SQLite, here's an MWE:

using DBInterface, SQLite

db = SQLite.DB()
DBInterface.execute(db, "CREATE TABLE t (x INTEGER, y TEXT)")
stmt = DBInterface.prepare(db, """INSERT INTO t VALUES (:x, :y)""")
DBInterface.executemany(stmt, (x=[1,2,3,4], y=["a", "b", "c", "d"]))

This returns:

ERROR: MethodError: no method matching getconnection(::SQLite.Stmt)
Stacktrace:
 [1] executemany(stmt::SQLite.Stmt, params::NamedTuple{(:x, :y), Tuple{Vector{Int64}, Vector{String}}})
   @ DBInterface C:\Users\Jakob\.julia\packages\DBInterface\1Gmxx\src\DBInterface.jl:193
 [2] top-level scope
   @ REPL[40]:1

[lawless-m]

It's not executemany at fault but the lack of getconnection

julia> DBInterface.getconnection(stmt)
ERROR: MethodError: no method matching getconnection(::ODBC.Statement)
Stacktrace:
[1] top-level scope
@ REPL[22]:1

and according to the source

https://github.com/JuliaDatabases/DBInterface.jl/blob/master/src/DBInterface.jl#LL39-L44

This is a function which should be overrided by the library

So it is an ODBC / SQLite problem not DBInterface

[lawless-m]

as a workaround, you can define this yourself

DBInterface.getconnection(stmt::ODBC.Statement) = stmt.dsn
DBInterface.getconnection(stmt::SQLite.Stmt) = stmt.handle # untested

although in my case it turned into another problem

julia> DBInterface.executemany(stmt, [["a", 1], ["b", 2]])
ERROR: 22018: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Conversion failed when converting the varchar value 'b' to data type int.
Stacktrace:

and if I changed it to

julia> DBInterface.executemany(stmt, [["a", 1], [1, 2]])

The insert completed with 1 inserted as a string into the first column

but that's something else to find

[lawless-m]

I created a PR to fix this missing function
JuliaDatabases/ODBC.jl#336

[lawless-m]

this has been fixed in SQLite

julia>  using DBInterface, SQLite

julia> db = SQLite.DB()
SQLite.DB(":memory:")

julia> DBInterface.execute(db, "CREATE TABLE t (x INTEGER, y TEXT)")
SQLite.Query(SQLite.Stmt(SQLite.DB(":memory:"), 1), Base.RefValue{Int32}(101), Symbol[], Type[], Dict{Symbol, Int64}(), Base.RefValue{Int64}(0))

julia> stmt = DBInterface.prepare(db, """INSERT INTO t VALUES (:x, :y)""")
SQLite.Stmt(SQLite.DB(":memory:"), 2)

julia>  DBInterface.executemany(stmt, (x=[1,2,3,4], y=["a", "b", "c", "d"]))

julia> DBInterface.execute(db, "SELECT * from t") |> DataFrame
4×2 DataFrame
 Row │ x      y      
     │ Int64  String 
─────┼───────────────
   1 │     1  a
   2 │     2  b
   3 │     3  c
   4 │     4  d

[quinnj]

Looks like both cases have been fixed now; thanks all.