errorReportSql.txt file: intermediate errors are overwritten

[scossin]

Describe the bug The bug was also discussed here: https://github.com/OHDSI/DatabaseConnector/issues/132

If an error is generated by the DatabaseConnector::executeSql() function, the SQL error is written in a file ("errorReportSql.txt" by default) with an opening mode "w+".

When a tryCatch statement is used to handle the error; the DatabaseConnector::executeSql() function overwrites the previous error so intermediate errors are overwritten and lost.

        tryCatch({
          DatabaseConnector::executeSql(connection = connection, sql = mainSql$sql)  # for example here
          delta <- Sys.time() - start
          ParallelLogger::logInfo(sprintf("[Main Analysis] [COMPLETE] %d (%f %s)", 
                                          as.integer(mainSql$analysisId), 
                                          delta, 
                                          attr(delta, "units")))  
        }, error = function(e) {
          ParallelLogger::logError(sprintf("Analysis %d -- ERROR %s", mainSql$analysisId, e))
        })

[PRijnbeek]

true, solution could simply be to change the name of the errorfile and add the analysisId in the name using the errorReportFile parameter of executeSql.

or stop, but that is not my preference so you can at least see all the issues at once at the end of the run.

[PRijnbeek]

Code now stops after an error to avoid this. Will be pulled into next version update that will close this issue