Unable to insert into table with IDate type column

[quantumdot]

I'm trying to run the ETL scripts in https://github.com/OHDSI/ETL-Synthea, but this is failing due to mismatch between the DB column type and the data that is being inserted. It looks like this is an issue with DatabaseConnector.

The table being loaded is the allergies.csv output by a synthea v3.30 run. Confirmed in a text editor as well as loading in R, the Start column is a date in the format YYYY-MM-DD:

> class(syntheaTable$START)
[1] "IDate" "Date" 

When attempting to insert the data into the table via insertTable(), I receive the error/stacktrace below. It seems the data is being converted to a integer at some point

Error in rJava::.jcall(batchedInsert, "Z", "executeBatch") : 
  java.sql.BatchUpdateException: Batch entry 0 INSERT INTO native.allergies (START,STOP,PATIENT,ENCOUNTER,CODE,SYSTEM,DESCRIPTION,TYPE,CATEGORY,REACTION1,DESCRIPTION1,SEVERITY1,REACTION2,DESCRIPTION2,SEVERITY2) VALUES(('20461'::int4),(NULL),('1d04d783-3508-1afe-a6f6-b0862d464eca'),('db1a35c9-6e5f-5a74-23d3-2bb07b694f5a'),('419199007'),('Unknown'),('Allergy to substance (finding)'),('allergy'),('environment'),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) was aborted: ERROR: column "start" is of type date but expression is of type integer
  Hint: You will need to rewrite or cast the expression.
  Position: 172  Call getNextException to see other errors in the batch.
8.
stop(structure(list(message = "java.sql.BatchUpdateException: Batch entry 0 INSERT INTO native.allergies (START,STOP,PATIENT,ENCOUNTER,CODE,SYSTEM,DESCRIPTION,TYPE,CATEGORY,REACTION1,DESCRIPTION1,SEVERITY1,REACTION2,DESCRIPTION2,SEVERITY2) VALUES(('20461'::int4),(NULL),('1d04d783-3508-1afe-a6f6-b0862d464eca'),('db1a35c9-6e5f-5a74-23d3-2bb07b694f5a'),('419199007'),('Unknown'),('Allergy to substance (finding)'),('allergy'),('environment'),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) was aborted: ERROR: column \"start\" is of type date but expression is of type integer\n Hint: You will need to rewrite or cast the expression.\n Position: 172 Call getNextException to see other errors in the batch.",
call = rJava::.jcall(batchedInsert, "Z", "executeBatch"),
jobj = new("jobjRef", jobj = <pointer: 0x0000022309343fa2>,
jclass = "java/sql/BatchUpdateException")), class = c("BatchUpdateException", ...
7.
.jcheck()
6.
rJava::.jcall(batchedInsert, "Z", "executeBatch")
5.
insertTable.DatabaseConnectorJdbcConnection(conn, tableName = paste0(syntheaSchema,
".", strsplit(csv, "[.]")[[1]][1]), data = as.data.frame(syntheaTable),
dropTableIfExists = FALSE, createTable = FALSE, bulkLoad = bulkLoad,
progressBar = TRUE)
4.
DatabaseConnector::insertTable(conn, tableName = paste0(syntheaSchema,
".", strsplit(csv, "[.]")[[1]][1]), data = as.data.frame(syntheaTable),
dropTableIfExists = FALSE, createTable = FALSE, bulkLoad = bulkLoad,
progressBar = TRUE)
3.
withCallingHandlers(expr, warning = function(w) if (inherits(w,
classes)) tryInvokeRestart("muffleWarning"))
2.
suppressWarnings({
DatabaseConnector::insertTable(conn, tableName = paste0(syntheaSchema,
".", strsplit(csv, "[.]")[[1]][1]), data = as.data.frame(syntheaTable),
dropTableIfExists = FALSE, createTable = FALSE, bulkLoad = bulkLoad, ...
1.
ETLSyntheaBuilder::LoadSyntheaTables(connectionDetails = cd,
syntheaSchema = syntheaSchema, syntheaFileLoc = syntheaFileLoc)

I'm using the following tools:
R (4.5.2)
DatabaseConnector (7.1.0)
ETLSyntheaBuilder (2.1/HEAD)

[schuemie]

How did you load the CSV into R? I have never seen the IDate class before.

[quantumdot]

The CSVs are loaded into R by the https://github.com/OHDSI/ETL-Synthea package, through the a call to ETLSyntheaBuilder::LoadSyntheaTables(). My script is what is shown in the step-by-step example on the readme there. Internally, it performs a data.table::fread to parse the CSV file, performs a cast via as.Date() on the START column, then calls DatabaseConnector::insertTable() passing data as.data.frame().

I've recreated those steps externally to validate the the START column is indeed of type Date. But again, DatabaseConnector::insertTable() attempts to insert the value as an integer.

[quantumdot]

It looks like IDate is a type used by data.frame() and stores the data as an integer value (but is also type Date as IDate inherits from the base class Date).

The return of is.integer(IDate) == TRUE, which is probably where things go awry. It looks like getSqlDataTypes() is testing for is.integer() first in the cascade of if statements there, before checking if the column is Date type. Likely hoisting a the check for Date types higher in the series of ifs would solve the issue.

[schuemie]

Unfortunately, just switching the order wasn't enough. I ended up just casting to base R Date. Maybe not the most efficient, but avoids complexity. Could you give the new version a try? To install:

remotes::install_github("ohdsi/DatabaseConnector", ref = "develop")