DuckDB sqlInsert doesn't work

[edmundmiller]

Example:

include { sqlInsert } from 'plugin/nf-sqldb'
// -- create a table
// CREATE TABLE ducks AS SELECT 3 AS age, 'mandarin' AS breed;
channel
    .of(tuple('3', "mandarin"))
    .sqlInsert( into: 'ducks',
               columns: 'age, breed',
               db: 'playground',
               setup: "CREATE TABLE ducks(age INTEGER, breed VARCHAR);")

Main error via @bentsherman

java.sql.SQLFeatureNotSupportedException: null
    at org.duckdb.DuckDBPreparedStatement.addBatch(DuckDBPreparedStatement.java:606)
    at nextflow.sql.InsertHandler.executeStm(InsertHandler.groovy:189)
    at nextflow.sql.InsertHandler.performAsTuple(InsertHandler.groovy:168)
    at nextflow.sql.InsertHandler.perform(InsertHandler.groovy:144)
    at nextflow.sql.ChannelSqlExtension$_sqlInsert_closure3.doCall(ChannelSqlExtension.groovy:123)

nextflow.log

Then I talked to @abhi18av who hunted down that this line is the issue. https://github.com/nextflow-io/nf-sqldb/blob/e9f3da63888046df57270a2a5b35e71d2a7815ac/plugins/nf-sqldb/src/main/nextflow/sql/InsertHandler.groovy#L239

He then dove into the DuckDB JDBC and found the feature isn't supported 🙃

We thought maybe if the old method without batch was used that might be a quick fix. However we also decided running a ton of queries on the headnode was a anti-pattern.

Regardless, good things come to those who wait, there's a PR to add batch functionality!