My first post - I would appreciate any advice
Success requires reading integer arrays stored in postgresql
config.yml
shinyapps:
dataconnection:
driver: 'PostgreSQL'
server: ''
uid: ''
pwd: '*********'
port: 5432
database: 'postgres'
boolsaschar: 0
maxvarcharsize: 0
Reads fails when a table with integer arrays is accessed
split_db_array <- function(v, name = ""){
x <- unlist( strsplit(gsub(fixed=T, "{", "",gsub(fixed=T, "}", "",v)), ",", fixed=T))
print(paste(length(x), name ))
if(length(x) == 0)
return(NA)
x
}
query <- DBI::sqlInterpolate(pool, "SELECT ts, oat, watts, fit, model FROM v1.poi_meters WHERE mid = ?mid ORDER BY ts;", .dots = list(mid = as.numeric(mid)))
df <- DBI::dbGetQuery(pool, statement = query)
Locally this works
ts = as.Date(split_db_array(df$ts, "ts"))
oat = split_db_array(df$oat, "oat")
kW = split_db_array(df$watts, "kW")
fit = split_db_array(df$fit, "fit")
model = split_db_array(df$model, "model") == "t"
tibble::tibble( ts, oat, kW, fit, model)
On shinyapps.io it fails
[1] "24 ts"
[1] "46 oat"
[1] "45 kW"
[1] "43 fit"
[1] "127 model"
Warning: Error in : Tibble columns must have compatible sizes.
- Size 46: Column at position 2.
2020-05-07T13:18:32.899656+00:00 shinyapps[2259625]:
Only values of size one are recycled.
2020-05-07T13:18:32.905385+00:00 shinyapps[2259625]: 173:
I believe this must be due to the odbc driver truncating returned varchars - my app and db require
boolsaschar: 0
maxvarcharsize: 0