Error in MakingNewAnnotationPackages vignette

[amanda-hi]

In section 9.2 of the Creating select Interfaces for custom Annotation resources vignette, there are warnings that (presumably) prevent the example code from being executable:

## Warning in result_fetch(res@ptr, n = n): SQL statements must be issued
with dbExecute() or dbSendStatement() instead of dbGetQuery() or dbSendQuery().

See below for a reprex created with code copy + pasted from the vignette:

reprex::reprex({
  suppressPackageStartupMessages({
    library(AnnotationForge)
    library(DBI)
  })

  drv <- dbDriver("SQLite")
  dbname <- file.path(tempdir(), "myNewDb.sqlite")
  con <- dbConnect(drv, dbname=dbname)
  data = data.frame(id=c(1,2,9),
                    string=c("Blue",
                             "Red",
                             "Green"),
                    stringsAsFactors=FALSE)

  # dbGetQuery is problematic
  dbGetQuery(con, "CREATE Table genePheno (id INTEGER, string TEXT)")
  names(data) <- c("id","string")
  sql <- "INSERT INTO genePheno VALUES ($id, $string)"
  dbBegin(con)

  # dbSendQuery is problematic
  res <- dbSendQuery(con, sql)
  dbBind(res, data)
  dbFetch(res)
  dbClearResult(res)
  dbCommit(con)
})

Could this vignette be updated to include the appropriate SQL commands? I'm assuming the same sort of thing (importing new information and/or adding tables to a SQLite database created with AnnotationForge tools) can be accomplished using the simpler procedure in the RSQLite vignette? I'm relatively new to SQL and am following this vignette to add a new table to my own ChipDb package, and while the RSQLite vignette contains similar information, I appreciate the more specific instructions in the AnnotationForge vignette (as it pertains to AnnotationDb packages).

Thanks so much!

[hpages]

Hi @amanda-hi,

Thanks for reporting this. Note that these are not errors, only warnings, and AFAIK they don't "prevent the example code from being executable". It's easy to check that the code you share above actually did import the data in the genePheno table:

> dbReadTable(con, "genePheno")
  id string
1  1   Blue
2  2    Red
3  9  Green

Anyways, warnings are not pleasant and the code in this vignette was old and needed some actualization. This is now done in AnnotationForge 1.40.2 (release version, part of BioC 3.16) and 1.41.2 (devel version, part of BioC 3.17).

Please allow between 2 or 3 days for these new version to propagate to the Bioconductor public package repositories and become available via BiocManager::install().

Cheers, H.