ERROR: current transaction is aborted, commands ignored until end of transaction block

[ablack3]

Whenever I have a sql error using DatabaseConnector's jdbc drivers I need to manually rollback the transaction or disconnect and reconnect to the database.

Can we automatically end the transaction in event of an error so the user does not need to manually end the transaction?

! Error executing SQL:
org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block

[schuemie]

Just curious: do you know what the other DBI drivers do in these cases?

[ablack3]

Current behavior:

library(DatabaseConnector)
connectionDetails <- createConnectionDetails(dbms = "postgresql",
                                             server = Sys.getenv("CDM5_POSTGRESQL_SERVER"),
                                             user = Sys.getenv("CDM5_POSTGRESQL_USER"),
                                             password = Sys.getenv("CDM5_POSTGRESQL_PASSWORD"))

write_schema <- Sys.getenv("CDM5_POSTGRESQL_SCRATCH_SCHEMA")
cdm_schema <- Sys.getenv("CDM5_POSTGRESQL_CDM_SCHEMA")

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

# error
querySql(con, "select from a;")
#> Error in `.createErrorReport()`:
#> ! Error executing SQL:
#> org.postgresql.util.PSQLException: ERROR: relation "a" does not exist
#>   Position: 13
#> An error report has been created at  /private/var/folders/2j/8z0yfn1j69q8sxjc7vj9yhz40000gp/T/Rtmpnmqf8m/reprex-e37b1d909278-fresh-hare/errorReportSql.txt

# can no longer queries 
querySql(con, "select count(*) as n from cdmv5.person")
#> Error in `.createErrorReport()`:
#> ! Error executing SQL:
#> org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block
#> An error report has been created at  /private/var/folders/2j/8z0yfn1j69q8sxjc7vj9yhz40000gp/T/Rtmpnmqf8m/reprex-e37b1d909278-fresh-hare/errorReportSql.txt


disconnect(con)

packageVersion("DatabaseConnector")
#> [1] '6.3.2'

^{Created on 2024-08-25 with reprex v2.1.1}

Using the branch I'm working on

remotes::install_github("darwin-eu-dev/DatabaseConnector", "dbplyr2")

library(DatabaseConnector)
#> Warning in fun(libname, pkgname): Java library version does not match R package version! Please try reinstalling the DatabaseConnector package.
connectionDetails <- createConnectionDetails(dbms = "postgresql",
                                             server = Sys.getenv("CDM5_POSTGRESQL_SERVER"),
                                             user = Sys.getenv("CDM5_POSTGRESQL_USER"),
                                             password = Sys.getenv("CDM5_POSTGRESQL_PASSWORD"))

write_schema <- Sys.getenv("CDM5_POSTGRESQL_SCRATCH_SCHEMA")
cdm_schema <- Sys.getenv("CDM5_POSTGRESQL_CDM_SCHEMA")

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

# error
querySql(con, "select from a;")
#> Error in `.createErrorReport()`:
#> ! Error executing SQL:
#> org.postgresql.util.PSQLException: ERROR: relation "a" does not exist
#>   Position: 13
#> An error report has been created at  /private/var/folders/2j/8z0yfn1j69q8sxjc7vj9yhz40000gp/T/Rtmpnmqf8m/reprex-e37b57ede73f-stout-scaup/errorReportSql.txt

# can still run queries 
querySql(con, "select count(*) as n from cdmv5.person")
#>      N
#> 1 1000


disconnect(con)

packageVersion("DatabaseConnector")
#> [1] '6.3.3.9000'

^{Created on 2024-08-25 with reprex v2.1.1}

[schuemie]

We can probably borrow this code