OHDSI / OHDSI/DatabaseConnector

Unable to insert into table with IDate type column

Open
#326 4 comments 0 reactions 0 assignees View on GitHub

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:

Image
> 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

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.