Errors when trying to run

[nhall6]

Hi,

I was testing your package and came up with some errors/issues that I wanted to point out, along with some general comments. I tested its use in 2 different ways: One by installing the package first, and the second by simply running the RunCohortDiagnosticsAndViewResult.R script and downloading files as needed. Below are my comments:

1. Installing and Running the SvvEHDEN Package • Need to first instruct user how to install remotes package o Then, need to instruct them how to use remotes::install_github() to download SvvEHDEN package • Fatal error upon installation, I cannot call library(SvvEHDEN). Should I be able to? I also get the following warning: Rd warning: C:/Users/Admin_NHall6/AppData/Local/Temp/2/RtmpEvG4t8/R.INSTALL3ba41a5c483c/CohortDiagnostics/man/preMergeDiagnosticsFiles.Rd:11: missing link 'runCohortDiagnostics'

2. Running the RunCohortDiagnosticsAndViewResult.R script manually • Package should have an input parameter for the working directory if the user needs to change it • Lines 61-69 only work if CohortDiagnostics is within C:/R/CohortDiagnostics • Error on Line 72: detach("package:CohortDiagnostics", unload = TRUE) produces invalid name argument • Before line 75: Need to clearly specify the user needs to download CohortDiagnostics_2.2.1.zip AND ALSO move it to the current wd, otherwise will fail • Fatal error at line 91: o Error in get_tar_options(cohortToCreateFile = cohortToCreateFile) : could not find function "get_tar_options” o This is because the package does not properly install (see method 1 above)

[OskarGauffin]

Dear Nathan,

1) the package name is CohortDiagnostics, not Svehden. If you try to library with that instead I hope you'll be more successful.

2) the intended setup is through an Rstudio project environment, select version control and initiate the project through the GitHub study package page.

I hope these two clarifications will solve the issues you bring up (except the detach, I think we can put some if statement on the package being loaded there). We'll update/ make our user instructions more explicit.

Thanks for your time and input.

[nhall6]

Hi Oskar,

Thank you for your reply. I will re-attempt following the instructions using the RStudio project environment, and will update you with any other findings or suggestions.

[nhall6]

Hi again,

I was able to get further in the project using the Rproject setup you described. The detach() issue is still present, and also I ran into an issue on Line 78 when trying to install the package. But, when I adjusted the code to the following, I was able install without errors and move on:

tryCatch({ install.packages(file.path(path_to_project_root, "CohortDiagnostics_2.2.1.zip"), repos = NULL, type = "win.binary", INSTALL_opts="--no-multiarch", dependencies=TRUE ) })

However, I ran into an error when generating the cohorts. I was using the 'mediumtestset' as the example, and was able to generate the first 45/199 cohorts. But on Cohort 46: "All drugs (matched to Phenobarbital) (TAR=30)", I received the following error message:

"An error occurred while generating cohortName = All drugs (matched to Phenobarbital) (TAR=30). Error: Error in .createErrorReport(): ! Error executing SQL: java.sql.SQLException: Amazon Invalid operation: syntax error at or near "from" Position: 424;"

I am not sure why the cohort generation would fail on this cohort in particular since it had been generating fine for the prior 45 cohorts. But this is currently the furthest I have gotten using your script.

[OskarGauffin]

Dear Nathan,

Don't worry about the detach, we'll take care of that in a later push.

Ah, "win.binary", was needed. That means that the default type option, "getOption("pkgType")" does not work for everyone, that's helpful to know, we'll add that in a future push, thanks.

To give a bit of detail, cohort 46 is the first in a set of cohorts that are defined in a particular SQL script, which explains why we see that particular cohort here. Ed from Oxford reported a similar, but not identical issue for that same cohort 46 a few days ago in another test run, using PostGreSQL.

Anyhow, our first guess would be that something in that script has not been correctly translated to the appropriate sql dialect. To debug this, it's helpful for us to know what sql dialect you used when the error occurred. Your error message mentions Amazon, which I'm not very familiar with, but as far as I understand Amazon can use several DBMS. Do you know which one was used, is it sql server, postgreSQL, etc?

If possible, it might also be useful for us to check additional information logged about the error in the error-report that's generated. It's a txt-file in the project folder, or possibly in the folder "R", in the project folder. The txt-file would be named "errorReportSql.txt", here's an example on how they typically look (not related to this issue though): "DBMS: postgresql

Error: org.postgresql.util.PSQLException: ERROR: syntax error at or near "COLUMN" Position: 43

SQL: ALTER TABLE test_schema.analysis_refALTER COLUMN ANALYSIS_ID TYPE NUMERIC

R version: R version 4.1.3 (2022-03-10)

Platform: x86_64-w64-mingw32"

Thanks again,

[nhall6]

Hi Oskar,

Thank you for all the information. I found the errorReportSql.txt file, and attached it here:

errorReportSql.txt

We are using Amazon Redshift as the dialect.

Hopefully this helps, -Nate

[OskarGauffin]

Hello again Nathan,

With some help from Patrick, we've solved a bug in one of the SQL scripts that was not rendered properly. Could you please have another try at it?

[nhall6]

Hi Oskar,

Sure, I will give it another try and report back with how things go.

[nhall6]

Hi Oskar,

I just ran it again, this time on the "smalltestset" (in the interest of reducing the size of the test). I ran into a very similar error, this time on the "All drugs (matched to Perindopril) (TAR=30)" cohort.

I have attached the error log below for you:

errorReportSql.txt

[OskarGauffin]

Dear Nathan,

That's unfortunate. But the error log suggests that something did not work out when updating the scripts with the bug fix, there's still a "datepart"-function in it.

I wonder if you may have forgotten to pull the latest version of the branch before running, or if the line with pkgbuild executed with errors for you. We misplaced the type=win.binary-argument you suggested there, instead on the install.packages-line on the next line, and Sicco from IPCI reported that this type-argument caused an error until he removed it. We've moved it into the right place now, if you pull the latest it will be as intended.

[OskarGauffin]

That is, could you please pull the latest version, keep a close eye on any errors appearing while running lines 56-57, and rerun it once again.

And great that you changed to smalltestset, that should speed things up.

Many thanks. :)

[nhall6]

Hi Oskar,

I did indeed restart my R session and re-ran all code from the beginning of the script, but still maybe it did not pull the latest version when I did that?

I will completely close my R sessions and try it again, no problem. I will keep a close eye out for lines 56-57, too.

I will do all of this and report back! :)

[OskarGauffin]

I'm sorry, the Rstudio-project setup will not update the code by restarting.

I think the path of least resistance here is to delete the folder with the project, and create a new project as described above. This will update the code. Sorry for the confusion.

[OskarGauffin]

And perhaps you want to save the database parameters somewhere before deleting, to make entering them again a bit easier.

Thanks again!

[nhall6]

Hi Oskar,

I closed out of my R session completely, deleted the project folder, and started a new project just as I did before, but I am still running into the same exact issue I mentioned previously. I have attached the error log again below. How does it look? If there is anything else I can try, please feel free to let me know.

errorReportSql.txt

[OskarGauffin]

Hi Nathan,

As far as I can see that's a new bug, that is progress, and you have successfully updated the scripts, great! We'll have to examine what the issue is this time.

We appreciate your patience!

[OskarGauffin]

Dear Nathan,

A new week, and a new version of the package has been uploaded with the most recent bug being addressed. Would you mind trying to run it again?

[nhall6]

Hi Oskar,

Yes, I will try it again and let you know how it goes.

[nhall6]

Hi Oskar,

I got much further this time than the other attempts, but still reached a failure. The cohorts all generated, but I believe this time it failed while running CohortDiagnostics. I have attached the error message that popped up in my R session along with my error log below.

errorReportSql.txt

[dandedman]

Hi Oskar

I am running into issues with SQL server. This is the step after the cohort tables have been created successfully:

Here is the error trace output

errorreport also attached errorReportSql.txt