This function will soon be deprecated; please transition to
retrieve_token_mssql(). These functions are not essential to calling the REDCap API, but instead are functions that help manage tokens securely.
The friendly/shortened name given to the REDCap project in the MSSQL table. Notice this isn't necessarily the same name used by REDCap. Required
A DSN on the local machine that points to the desired MSSQL database. Required.
An optional connection handle as returned by
channel is passed, one will be created at the beginning of the function, and destroyed at the end. However if a channel
is created, it's the caller's responsibility to destroy this resource. If you're making successive calls to the database, it might
be quicker to create a single
channel object and batch the calls together. Otherwise, the performance should be equivalent.
If you create the
channel object yourself, consider wrapping calls in a
base::tryCatch block, and closing the channel in
finally expression; this helps ensure the expensive database resource isn't held open unnecessarily. See the internals of
retrieve_token_mssql for an example of closing the
channel in a
If the database elements are created with the script provided in package's ‘Security Database’ vignette, the default values will work.
The token, which is a 32 character string.
We use Microsoft SQL Server, because that fits our University's infrastructure the easiest. But this approach theoretically can work with any LDAP-enabled database server. Please contact us if your institution is using something other than SQL Server, and would like help adapting this approach to your infrastructure.
There's a lot of error checking for SQL injection, but remember that the user is executing under their own credentials, so this doesn't obviate the need for disciplined credential management. There's nothing that can be done with this R function that isn't already exposed by any other interface intot he database (eg, SQL Server Managment Studio, or MySQL Workbench.)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
library(REDCapR) #Load the package into the current R session. ## Not run: # ---- SQL Server Example ---------------------------- # Rely on `retrieve_token()` to create & destory the channel. dsn <- "TokenSecurity" project <- "DiabetesSurveyProject" token <- retrieve_token(dsn=dsn, project_name=project) # Create & close the channel yourself, to optimize repeated calls. dsn <- "TokenSecurity" project1 <- "DiabetesSurveyProject1" project2 <- "DiabetesSurveyProject2" project3 <- "DiabetesSurveyProject3" channel <- RODBC::odbcConnect(dsn=dsn) token1 <- retrieve_token(dsn=dsn, project_name=project1) token2 <- retrieve_token(dsn=dsn, project_name=project2) token3 <- retrieve_token(dsn=dsn, project_name=project3) RODBC::odbcClose(channel) ## End(Not run)