Placement of sql definition

[toniher]

Hello, I am try this plugin, and I would like to place sql definition in the main workflow file, doing something like this:

sql {
    db {
        'dbtest' {
              url = "jdbc:mysql://${dbhost}:${params.dbport}/${params.dbname}"
              user = params.dbuser
              password = params.dbpass
            }
    }
}

However, I get an error message like this:

No signature of method: Script_23533472.sql() is applicable for argument types: (Script_23533472$_runScript_closure1) values: [Script_23533472$_runScript_closure1@9a6fbc0]
Possible solutions: run(), run(), any(), tap(groovy.lang.Closure), split(groovy.lang.Closure), is(java.lang.Object)

Do I understand that this can only be placed in nextflow.config ? Is there any way to add parameters in a way I outline above yet?

[pditommaso]

Have you put the above snippet in the config file, right?

[toniher]

Well, I actually wanted to place it in the main.nf file, because dbhost var I'm retrieving from a Groovy processing there, but anyway, trying something like this in nextflow.config:

plugins {
  id '[email protected]'
}

sql {
    db {
        'dbtest' {
              url = "jdbc:mysql://0.0.0.0:3567/tal"
              user = "tal"
              password = "tal"
            }
    }
}

I get the following error:

Run NEXTFLOW
N E X T F L O W  ~  version 21.09.0-edge
Launching `main.nf` [amazing_plateau] - revision: de46f3898c
Unknown method invocation `call` on BigDecimal type -- Did you mean?
  scale

Code I am testing is here: https://github.com/biocorecrg/nextflow_detached_db_wrapper/ so, it might be another error...

[pditommaso]

No signature of method: java.math.BigDecimal.call() is applicable for argument types: (BigDecimal) values: [0.0]

Can't see anybody bad in the snippet above, I guess something bad is happening in the imported config

[pditommaso]

this is strange tho

"${params.config ?: 'params.config'}"

if params.config config is empty you are passing the string params.config

[toniher]

I managed to work a bit more on this, and I advanced... This non-orthodox bit: includeConfig "${params.config ?: 'params.config'}" ensures that in nextflow.config you can use params.dbhost and so:

sql {
    db {
        'dbtest' {
              url = "jdbc:mysql://${params.dbhost}:${params.dbport}/${params.dbname}"
              user = params.dbuser
              password = params.dbpass
            }
    }
}

However, now when running I'm facing another kind of problem:

workflow {

  done = importSchema()
  if ( done ) {

  query = "SELECT id from test limit 1"
  out = channel.sql.fromQuery(query, db: 'dbtest')

  }

}

It seems that the channel is triggered before the 'importSchema' previous step...
I tried to place it in a separate process, but with little luck. Reference here:
https://github.com/biocorecrg/nextflow_detached_db_wrapper/blob/b44eca8ba664b55f5cf96b70c14c28ba1910b36d/main.nf#L75

What would be the best way to handle this?
Thanks!

[pditommaso]

Don't do the that, user the setup: option to create the table on-fly, see here https://github.com/nextflow-io/nf-sqldb#sqlinsert

[toniher]

Perfect. I could make it work! It helped be aware of the 'Important' section of the README.