results schema, write access?

[vojtechhuser]

The examples provide how to run sql code.
I was trying to run Achilles.
I assume there is no write access and that is why it fails.

Assuming we need results schema and test HivDescriptive study on Eunomia, we would write it into a real db, right?

When trying that, it works on some tables but fails in terms of timestamp on others..

> tbl='drug_era'
> library(glue)
> sql=glue::glue("SELECT * FROM {tbl};") %>% as.character()
> sql
[1] "SELECT * FROM drug_era;"
> t<-querySql(connection,sql )
> DatabaseConnector::dbWriteTable(con2,tbl,t)



> tbl='visit_occurrence'
> library(glue)
> sql=glue::glue("SELECT * FROM {tbl};") %>% as.character()
> sql
[1] "SELECT * FROM visit_occurrence;"
> t<-querySql(connection,sql )
> DatabaseConnector::dbWriteTable(con2,tbl,t)
Error in rJava::.jcall(connection@jConnection, "V", "setAutoCommit", TRUE) : 
  java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]
In addition: Warning messages:
1: In max(nchar(as.character(obj)), na.rm = TRUE) :
  no non-missing arguments to max; returning -Inf
2: In max(nchar(as.character(obj)), na.rm = TRUE) :
  no non-missing arguments to max; returning -Inf

[vojtechhuser]

the sqlite file has table coxib.

after I removed (and skipping note_nlp), transfer into postgres worked

library(glue);library(magrittr)
doTable<-function(tbl){
  sql=glue::glue("SELECT * FROM {tbl};") %>% as.character()
  #sql
  t<-querySql(connection,sql )
  #names(t)
  
  #executeSql(con2,glue('drop table {tbl}') %>% as.character())
  t %<>% select(-ends_with("DATETIME"))
 
  res<-DatabaseConnector::dbWriteTable(con2,tbl,t)
  res
}

[ablack3]

As far as I know SQLite only has two schemas: main and temp. Eunomia allows write access to both so either could be used as a results schema for testing studies.

library(Eunomia)
#> Warning: package 'Eunomia' was built under R version 4.0.5
#> Loading required package: DatabaseConnector
#> Warning: package 'DatabaseConnector' was built under R version 4.0.4


connectionDetails <- getEunomiaConnectionDetails()
con <- connect(connectionDetails)
#> Connecting using SQLite driver

dbWriteTable(con, "cars", cars)
result <- dbGetQuery(con, "select * from main.cars")
head(result, 5)
#>   speed dist
#> 1     4    2
#> 2     4   10
#> 3     7    4
#> 4     7   22
#> 5     8   16

dbWriteTable(con, DBI::SQL("temp.cars"), cars)
result <- dbGetQuery(con, "select * from temp.cars")
head(result, 5)
#>   speed dist
#> 1     4    2
#> 2     4   10
#> 3     7    4
#> 4     7   22
#> 5     8   16

disconnect(con)

^{Created on 2021-04-15 by the reprex package (v0.3.0)}