DDL in Rmarkdown

Package installing

remove.packages('cli')
install.packages('cli')

install.packages(c("rJava", "DBI", "SqlRender"))
install.packages("DatabaseConnector")
Sys.setenv("DATABASECONNECTOR_JAR_FOLDER" = "c:/temp/jdbcDrivers")
DATABASECONNECTOR_JAR_FOLDER = 'c:/temp/jdbcDrivers'
install.packages("usethis")
usethis::edit_r_environ()
library(DatabaseConnector)
downloadJdbcDrivers("postgresql", pathToDriver = "c:/temp/jdbcDrivers")
install.packages("SqlRender")
library(SqlRender)

install.packages("devtools")

#Installing packages from Github

library(devtools)
devtools::install_github("OHDSI/CommonDataModel")
CommonDataModel::buildRelease(cdmVersions = "5.4",
                              targetDialects = "postgresql",
                              outputfolder = "C:/Users/loren/OneDrive/Escritorio/output_for_ddl")
devtools::install_github("ohdsi/DatabaseConnector")

Assignment of the JAR_FOLDER for the drivers

Sys.setenv("DATABASECONNECTOR_JAR_FOLDER" = "C:/Users/loren/OneDrive/Escritorio/output_for_ddl")
library(DatabaseConnector)
downloadJdbcDrivers("postgresql")

Connection with the database

cd <- DatabaseConnector::createConnectionDetails(dbms = "postgresql",
                                                 server = "localhost/ohdsi",
                                                 user = "postgres",
                                                 password = "ryzen7123",
                                                 pathToDriver = "C:/Users/loren/OneDrive/Escritorio/output_for_ddl")
conn <- DatabaseConnector::connect(connectionDetails = cd)
DatabaseConnector::executeSql(
  connection = conn,
  sql = "CREATE SCHEMA IF NOT EXISTS ohdsi_demo;")
CommonDataModel::executeDdl(connectionDetails = cd,
                            cdmVersion = "5.4",
                            cdmDatabaseSchema = "ohdsi_demo"
                            )

Here is an example image of the table ‘concept_ancestor’ in the database: