OHDSI / OHDSI/DatabaseConnector
Unable to insert into table with IDate type column
Nobody has claimed this yet.
- Dominant language
- R
- Stars
- 57
- Forks
- 93
- Avg merge
- 10d 12h
- Merged PRs (30d)
- 1
Description
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)
Contributor guide
No contributing guide indexed for this repository
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start by reproducing the failure through DatabaseConnector::insertTable(), specifically insertTable.DatabaseConnectorJdbcConnection(), using the allergies.csv data and the PostgreSQL date column described here. Inspect how the IDate value becomes an integer during batch insertion; done means the START value is inserted into the date column without a type mismatch.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- postgresql, r
- Domain
- database
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100