DatabaseConnector

https://ohdsi.github.io/DatabaseConnector/
Admin
DatabaseConnector is an R package that provides a uniform interface for connecting to various...
Tokens:52,595
Snippets:485
Trust Score:9.7
Update:5 months ago
Show doc for...
Tokens:
Raw
### Create PostgreSQL Connection Details with Keyring

Source: https://ohdsi.github.io/DatabaseConnector/reference/createConnectionDetails.html

Demonstrates how to set up and use Windows authentication for SQL Server by installing the JDBC driver and configuring the system path or environment variables. This example shows how to create connection details for PostgreSQL using the 'keyring' package for secure credential management. Ensure credentials are set using 'key_set_with_value' before calling 'createConnectionDetails'.

```R
if (FALSE) { # \dontrun{
# Needs to be done only once on a machine. Credentials will then be stored in
# the operating system's secure credential manager:
keyring::key_set_with_value("server", password = "localhost/postgres")
keyring::key_set_with_value("user", password = "root")
keyring::key_set_with_value("password", password = "secret")

# Create connection details using keyring. Note: the connection details will
# not store the credentials themselves, but the reference to get the credentials.
connectionDetails <- createConnectionDetails(
  dbms = "postgresql",
  server = keyring::key_get("server"),
  user = keyring::key_get("user"),
  password = keyring::key_get("password"),
)
conn <- connect(connectionDetails)
dbGetQuery(conn, "SELECT COUNT(*) FROM person")
disconnect(conn)
} # }
```

--------------------------------

### Example: Download Redshift JDBC Driver

Source: https://ohdsi.github.io/DatabaseConnector/reference/downloadJdbcDrivers.html

This example demonstrates how to download the JDBC driver for Amazon Redshift. Ensure the `DATABASECONNECTOR_JAR_FOLDER` environment variable is set or specify the `pathToDriver` argument.

```R
if (FALSE) { # \dontrun{
downloadJdbcDrivers("redshift")
} # }
```

--------------------------------

### Example Usage of renderTranslateQuerySql

Source: https://ohdsi.github.io/DatabaseConnector/reference/renderTranslateQuerySql.html

Demonstrates how to use renderTranslateQuerySql to connect to a database, execute a SQL query, and retrieve results. Ensure you have a valid connectionDetails object and the necessary database drivers installed.

```r
if (FALSE) { # \dontrun{
connectionDetails <- createConnectionDetails(
  dbms = "postgresql",
  server = "localhost",
  user = "root",
  password = "blah",
  schema = "cdm_v4"
)
conn <- connect(connectionDetails)
persons <- renderTranslatequerySql(conn,
  sql = "SELECT TOP 10 * FROM @schema.person",
  schema = "cdm_synpuf"
)
disconnect(conn)
} # }
```

--------------------------------

### Example of Disconnecting from a Database

Source: https://ohdsi.github.io/DatabaseConnector/reference/disconnect.html

This example demonstrates the typical workflow of creating connection details, establishing a connection, performing a query, and then disconnecting. The code is wrapped in `if (FALSE)` to prevent accidental execution.

```R
if (FALSE) { # \dontrun{
connectionDetails <- createConnectionDetails(
  dbms = "postgresql",
  server = "localhost",
  user = "root",
  password = "blah"
)
conn <- connect(connectionDetails)
count <- querySql(conn, "SELECT COUNT(*) FROM person")
disconnect(conn)
} # }
```

--------------------------------

### Example: Querying Person Table

Source: https://ohdsi.github.io/DatabaseConnector/reference/querySqlToAndromeda.html

Demonstrates how to use querySqlToAndromeda to fetch all records from the 'person' table and store them in an Andromeda object under the table name 'foo'. This example requires setting up connection details and establishing a database connection.

```r
if (FALSE) { # \dontrun{
andromeda <- Andromeda::andromeda()
connectionDetails <- createConnectionDetails(
  dbms = "postgresql",
  server = "localhost",
  user = "root",
  password = "blah",
  schema = "cdm_v4"
)
conn <- connect(connectionDetails)
querySqlToAndromeda(
  connection = conn,
  sql = "SELECT * FROM person;",
  andromeda = andromeda,
  andromedaTableName = "foo"
)
disconnect(conn)

andromeda$foo
} # }
```

--------------------------------

### Connect to a PostgreSQL Database

Source: https://ohdsi.github.io/DatabaseConnector/reference/dbConnect-DatabaseConnectorDriver-method.html

Example of establishing a connection to a PostgreSQL database. Ensure you have the necessary connection details, including server address, username, and password.

```R
conn <- dbConnect(DatabaseConnectorDriver(),
  dbms = "postgresql",
  server = "localhost/ohdsi",
  user = "joe",
  password = "secret"
)
querySql(conn, "SELECT * FROM cdm_synpuf.person;")
dbDisconnect(conn)
```

--------------------------------

### Configure krb5.conf for Kerberos

Source: https://ohdsi.github.io/DatabaseConnector/articles/Connecting.html

Example configuration for the krb5.conf file to set up Kerberos authentication, pointing to your Active Directory server.

```ini
[libdefaults]
default_realm = DOMAIN.COMPANY.COM

[realms]
DOMAIN.COMPANY.COM = {
  kdc = dc-33.domain.company.com
}
```

--------------------------------

### Install RSQLite and duckdb Packages

Source: https://ohdsi.github.io/DatabaseConnector/articles/Connecting.html

Install RSQLite and duckdb packages for connecting to SQLite and DuckDB databases, as these do not use JDBC drivers.

```r
install.packages("RSQLite")
install.packages("duckdb")
```

--------------------------------

### Example of Using executeSql

Source: https://ohdsi.github.io/DatabaseConnector/reference/executeSql.html

Demonstrates how to establish a database connection and execute multiple SQL statements using executeSql. Ensure you have created connection details and connected to the database before execution.

```r
connectionDetails <- createConnectionDetails(
  dbms = "postgresql",
  server = "localhost",
  user = "root",
  password = "blah",
  schema = "cdm_v4"
)
conn <- connect(connectionDetails)
executeSql(conn, "CREATE TABLE x (k INT); CREATE TABLE y (k INT);")
disconnect(conn)
```

--------------------------------

### Example Usage of renderTranslateExecuteSql

Source: https://ohdsi.github.io/DatabaseConnector/reference/renderTranslateExecuteSql.html

Demonstrates how to establish a database connection and execute a SQL query using renderTranslateExecuteSql. Ensure connection details are correctly configured for your environment.

```r
connectionDetails <- createConnectionDetails(
  dbms = "postgresql",
  server = "localhost",
  user = "root",
  password = "blah",
  schema = "cdm_v4"
)
conn <- connect(connectionDetails)
renderTranslateExecuteSql(connection, 
  sql = "SELECT * INTO #temp FROM @schema.person;",
  schema = "cdm_synpuf"
)
disconnect(conn)
```

--------------------------------

### Install DatabaseConnector Package

Source: https://ohdsi.github.io/DatabaseConnector/index.html

Installs the latest stable version of the DatabaseConnector package from CRAN. Ensure R environment and Java are properly configured before installation.

```r
install.packages("DatabaseConnector")
```

--------------------------------

### dbms()

Source: https://ohdsi.github.io/DatabaseConnector/reference/index.html

Get the database platform from a connection.

```APIDOC
## dbms()

### Description
Identifies and returns the type of database platform (e.g., PostgreSQL, SQL Server) associated with an active connection.

### Method
N/A (Utility function)

### Parameters
- **connection**: An active database connection object.

### Response
- A string representing the database platform.
```

--------------------------------

### Basic Table Insertion Example

Source: https://ohdsi.github.io/DatabaseConnector/reference/insertTable.html

Demonstrates how to connect to a database and insert a data frame into a new table using the insertTable function. Ensure you have created the connection details and disconnected after use.

```R
connectionDetails <- createConnectionDetails(
  dbms = "mysql",
  server = "localhost",
  user = "root",
  password = "blah"
)
conn <- connect(connectionDetails)
data <- data.frame(x = c(1, 2, 3), y = c("a", "b", "c"))
insertTable(conn, "my_schema", "my_table", data)
disconnect(conn)
```

--------------------------------

### Example Usage of renderTranslateQuerySqlToAndromeda

Source: https://ohdsi.github.io/DatabaseConnector/reference/renderTranslateQuerySqlToAndromeda.html

Demonstrates how to establish a database connection, execute a SQL query using `renderTranslateQuerySqlToAndromeda`, and access the results stored in an Andromeda object. Ensure the `andromeda` object is open before calling the function.

```r
if (FALSE) { # \dontrun{
connectionDetails <- createConnectionDetails(
  dbms = "postgresql",
  server = "localhost",
  user = "root",
  password = "blah",
  schema = "cdm_v4"
)
conn <- connect(connectionDetails)
renderTranslatequerySqlToAndromeda(conn,
  sql = "SELECT * FROM @schema.person",
  schema = "cdm_synpuf",
  andromeda = andromeda,
  andromedaTableName = "foo"
)
disconnect(conn)

andromeda$foo
} # }
```

--------------------------------

### createConnectionDetails

Source: https://ohdsi.github.io/DatabaseConnector/reference/createConnectionDetails.html

This function creates a list containing all details needed to connect to a database. The list can then be used in the `connect()` function. It is highly recommended to use a secure approach to storing credentials, so not to have your credentials in plain text in your R scripts. The examples demonstrate how to use the `keyring` package.

```APIDOC
## createConnectionDetails

### Description
Creates a list with all the details needed to connect to a database. This list can then be used in the `connect()` function.

### Arguments

* **dbms** (string) - The type of DBMS running on the server. Valid values include "oracle", "postgresql", "redshift", "sql server", "pdw", "netezza", "bigquery", "sqlite", "sqlite extended", "spark", "snowflake", "iris".
* **user** (string) - The user name used to access the server.
* **password** (string) - The password for that user.
* **server** (string) - The name of the server.
* **port** (integer, optional) - The port on the server to connect to.
* **extraSettings** (list, optional) - Additional configuration settings specific to the database provider. For JDBC connections, these are appended to the connection string. For DBI connections, these are used to call `DBI::dbConnect()`.
* **oracleDriver** (string, optional) - Specify which Oracle driver to use. Choose between "thin" or "oci".
* **connectionString** (string, optional) - The JDBC connection string. If specified, `server`, `port`, `extraSettings`, and `oracleDriver` are ignored. If `user` and `password` are not specified, they are assumed to be included in the connection string.
* **pathToDriver** (string, optional) - Path to a folder containing the JDBC driver JAR files.

### Value
A list with all the details needed to connect to a database.

### Examples

```R
if (FALSE) { # \dontrun{
# Needs to be done only once on a machine. Credentials will then be stored in
# the operating system's secure credential manager:
keyring::key_set_with_value("server", password = "localhost/postgres")
keyring::key_set_with_value("user", password = "root")
keyring::key_set_with_value("password", password = "secret")

# Create connection details using keyring. Note: the connection details will
# not store the credentials themselves, but the reference to get the credentials.
connectionDetails <- createConnectionDetails(
  dbms = "postgresql",
  server = keyring::key_get("server"),
  user = keyring::key_get("user"),
  password = keyring::key_get("password")
)
conn <- connect(connectionDetails)
dbGetQuery(conn, "SELECT COUNT(*) FROM person")
disconnect(conn)
} # }
```
```

--------------------------------

### List Tables using DatabaseConnectorConnection

Source: https://ohdsi.github.io/DatabaseConnector/reference/dbListTables-DatabaseConnectorConnection-method.html

Use this method to get a list of all tables and views accessible via the connection. Ensure the `conn` argument is a valid DBIConnection object.

```R
dbListTables(conn, databaseSchema = NULL, ...)
```

--------------------------------

### Connect to SQL Server with Java Kerberos Authentication

Source: https://ohdsi.github.io/DatabaseConnector/articles/Connecting.html

Establishes a connection to a SQL Server database using Java Kerberos authentication. Requires the MSSql JDBC driver and proper Kerberos setup.

```r
library(devtools)
library(DatabaseConnector)
connectionDetails <- createConnectionDetails(
  dbms="sql server", 
  ...
  extraSettings="authenticationScheme=JavaKerberos")
c <- connect(connectionDetails = connectionDetails)
```

--------------------------------

### Example: Writing Batched Data to Andromeda

Source: https://ohdsi.github.io/DatabaseConnector/reference/renderTranslateQueryApplyBatched.html

Illustrates using renderTranslateQueryApplyBatched to process query results and store them in an Andromeda object. This is useful when local computations are needed before storing the data.

```r
# Second example: write data to Andromeda
# (Alternative to querySqlToAndromeda if some local computation needs to be applied)
bigResults <- Andromeda::andromeda()
writeBatchesToAndromeda <- function(data, position, ...) {
  data$p <- EmpiricalCalibration::computeTraditionalP(data$logRr, data$logSeRr)
  if (position == 1) {
    bigResults$rrs <- data
  } else {
    Andromeda::appendToTable(bigResults$rrs, data)
  }
  return(NULL)
}
sql <- "SELECT target_id, comparator_id, log_rr, log_se_rr FROM @schema.my_results;"
renderTranslateQueryApplyBatched(connection,
  sql,
  fun = writeBatchesToAndromeda,
  schema = "my_results",
  snakeCaseToCamelCase = TRUE
)
```

--------------------------------

### Example: Writing Batched Data to a CSV File

Source: https://ohdsi.github.io/DatabaseConnector/reference/renderTranslateQueryApplyBatched.html

Demonstrates how to use renderTranslateQueryApplyBatched to write query results to a large CSV file. The callback function `writeBatchesToCsv` handles appending data to the file.

```r
connectionDetails <- createConnectionDetails(
  dbms = "postgresql",
  server = "localhost",
  user = "root",
  password = "blah",
  schema = "cdm_v4"
)
connection <- connect(connectionDetails)

# First example: write data to a large CSV file:
filepath <- "myBigFile.csv"
writeBatchesToCsv <- function(data, position, ...) {
  write.csv(data, filepath, append = position != 1)
  return(NULL)
}
renderTranslateQueryApplyBatched(connection,
  "SELECT * FROM @schema.person;",
  schema = "cdm_synpuf",
  fun = writeBatchesToCsv
)
```

--------------------------------

### getAvailableJavaHeapSpace()

Source: https://ohdsi.github.io/DatabaseConnector/reference/index.html

Get available Java heap space.

```APIDOC
## getAvailableJavaHeapSpace()

### Description
Retrieves the amount of available Java heap memory. This can be useful for monitoring memory usage.

### Method
N/A (Utility function)

### Parameters
None

### Response
- The amount of available Java heap space (e.g., in MB or bytes).
```

--------------------------------

### Connect to PostgreSQL and Query Data

Source: https://ohdsi.github.io/DatabaseConnector/index.html

Demonstrates creating connection details for a PostgreSQL database, establishing a connection, executing a SQL query, and disconnecting. Ensure JDBC drivers are correctly configured.

```r
connectionDetails <- createConnectionDetails(dbms="postgresql", 
                                             server="localhost",
                                             user="root",
                                             password="blah")
conn <- connect(connectionDetails)
querySql(conn,"SELECT COUNT(*) FROM person")
disconnect(conn)
```

--------------------------------

### dbGetInfo(_<DatabaseConnectorDriver>_)

Source: https://ohdsi.github.io/DatabaseConnector/reference/index.html

Get metadata information about the DBMS driver.

```APIDOC
## dbGetInfo(_<DatabaseConnectorDriver>_)

### Description
Retrieves metadata information about the database driver itself.

### Method
N/A (Database operation)

### Parameters
- **driver**: A DatabaseConnectorDriver object.

### Response
- Returns a list or structure containing driver metadata.
```

--------------------------------

### dbGetInfo(_<DatabaseConnectorConnection>_)

Source: https://ohdsi.github.io/DatabaseConnector/reference/index.html

Get metadata information about the DBMS connection.

```APIDOC
## dbGetInfo(_<DatabaseConnectorConnection>_)

### Description
Retrieves metadata information about the connected Database Management System (DBMS).

### Method
N/A (Database operation)

### Parameters
- **connection**: A DatabaseConnectorConnection object.

### Response
- Returns a list or structure containing DBMS metadata (e.g., DBMS name, version).
```

--------------------------------

### Connect to PostgreSQL

Source: https://ohdsi.github.io/DatabaseConnector/articles/Querying.html

Establishes a connection to a PostgreSQL database using provided credentials.

```r
conn <- connect(dbms = "postgresql",
                server = "localhost/postgres",
                user = "joe",
                password = "secret")
```

--------------------------------

### dbGetStatement(_<DatabaseConnectorJdbcResult>_)

Source: https://ohdsi.github.io/DatabaseConnector/reference/index.html

Get the statement associated with a JDBC result set.

```APIDOC
## dbGetStatement(_<DatabaseConnectorJdbcResult>_)

### Description
Retrieves the SQL statement that generated the given JDBC result set.

### Method
N/A (Database operation)

### Parameters
- **result**: A DatabaseConnectorJdbcResult object.

### Response
- A string containing the SQL statement.
```

--------------------------------

### dbGetStatement(_<DatabaseConnectorDbiResult>_)

Source: https://ohdsi.github.io/DatabaseConnector/reference/index.html

Get the statement associated with a DBI result set.

```APIDOC
## dbGetStatement(_<DatabaseConnectorDbiResult>_)

### Description
Retrieves the SQL statement that generated the given DBI result set.

### Method
N/A (Database operation)

### Parameters
- **result**: A DatabaseConnectorDbiResult object.

### Response
- A string containing the SQL statement.
```

--------------------------------

### dbGetRowsAffected(_<DatabaseConnectorJdbcResult>_)

Source: https://ohdsi.github.io/DatabaseConnector/reference/index.html

Get the number of rows affected by a JDBC operation.

```APIDOC
## dbGetRowsAffected(_<DatabaseConnectorJdbcResult>_)

### Description
Returns the number of rows affected by the last executed JDBC statement (e.g., INSERT, UPDATE, DELETE).

### Method
N/A (Database operation)

### Parameters
- **result**: A DatabaseConnectorJdbcResult object.

### Response
- An integer representing the number of rows affected.
```

--------------------------------

### dbGetRowsAffected(_<DatabaseConnectorDbiResult>_)

Source: https://ohdsi.github.io/DatabaseConnector/reference/index.html

Get the number of rows affected by a DBI operation.

```APIDOC
## dbGetRowsAffected(_<DatabaseConnectorDbiResult>_)

### Description
Returns the number of rows affected by the last executed DBI statement (e.g., INSERT, UPDATE, DELETE).

### Method
N/A (Database operation)

### Parameters
- **result**: A DatabaseConnectorDbiResult object.

### Response
- An integer representing the number of rows affected.
```

--------------------------------

### connect

Source: https://ohdsi.github.io/DatabaseConnector/reference/index.html

Establish a connection.

```APIDOC
## connect()

### Description
Establishes a connection. This is a general function, specific connection details are likely handled by other functions.

### Method
N/A (General function)

### Parameters
None explicitly listed, likely takes connection details implicitly or through configuration.

### Response
- Returns a connection object or status.
```

--------------------------------

### Connect to PostgreSQL Database

Source: https://ohdsi.github.io/DatabaseConnector/reference/connect.html

Establishes a connection to a PostgreSQL database using provided server, user, and password. Requires subsequent calls to `dbGetQuery` and `disconnect`.

```r
connectionDetails <- createConnectionDetails(
  dbms = "postgresql",
  server = "localhost/postgres",
  user = "root",
  password = "xxx"
)
conn <- connect(connectionDetails)
dbGetQuery(conn, "SELECT COUNT(*) FROM person")
disconnect(conn)
```

--------------------------------

### dbGetRowCount(_<DatabaseConnectorJdbcResult>_)

Source: https://ohdsi.github.io/DatabaseConnector/reference/index.html

Get the number of rows fetched so far from a JDBC result.

```APIDOC
## dbGetRowCount(_<DatabaseConnectorJdbcResult>_)

### Description
Returns the number of rows that have been fetched from a JDBC result set.

### Method
N/A (Database operation)

### Parameters
- **result**: A DatabaseConnectorJdbcResult object.

### Response
- An integer representing the number of rows fetched.
```

--------------------------------

### Connect to SQLite Database

Source: https://ohdsi.github.io/DatabaseConnector/articles/Connecting.html

Use this to connect to a SQLite database. If the file does not exist, it will be created. Primarily for testing and demonstration.

```r
conn <- connect(dbms = "sqlite", server = tempfile())
```

--------------------------------

### dbGetRowCount(_<DatabaseConnectorDbiResult>_)

Source: https://ohdsi.github.io/DatabaseConnector/reference/index.html

Get the number of rows fetched so far from a DBI result.

```APIDOC
## dbGetRowCount(_<DatabaseConnectorDbiResult>_)

### Description
Returns the number of rows that have been fetched from a DBI result set.

### Method
N/A (Database operation)

### Parameters
- **result**: A DatabaseConnectorDbiResult object.

### Response
- An integer representing the number of rows fetched.
```

--------------------------------

### Bulk Data Insert with Redshift or PDW

Source: https://ohdsi.github.io/DatabaseConnector/reference/insertTable.html

Shows how to insert data using the bulk load option, which is more performant for Redshift or PDW. This requires setting the `bulkLoad` argument to TRUE or the `DATABASE_CONNECTOR_BULK_UPLOAD` environment variable.

```R
connectionDetails <- createConnectionDetails(
  dbms = "redshift",
  server = "localhost",
  user = "root",
  password = "blah",
  schema = "cdm_v5"
)
conn <- connect(connectionDetails)
data <- data.frame(x = c(1, 2, 3), y = c("a", "b", "c"))
insertTable(
  connection = connection,
  databaseSchema = "scratch",
  tableName = "somedata",
  data = data,
  dropTableIfExists = TRUE,
  createTable = TRUE,
  tempTable = FALSE,
  bulkLoad = TRUE
) # or, Sys.setenv("DATABASE_CONNECTOR_BULK_UPLOAD" = TRUE)
```

--------------------------------

### Connect to PostgreSQL using Connection String

Source: https://ohdsi.github.io/DatabaseConnector/reference/connect.html

Establishes a PostgreSQL connection using a JDBC connection string, bypassing individual server, port, and extra settings parameters. Requires subsequent calls to `dbGetQuery` and `disconnect`.

```r
conn <- connect(
  dbms = "postgresql",
  connectionString = "jdbc:postgresql://127.0.0.1:5432/cmd_database"
)
dbGetQuery(conn, "SELECT COUNT(*) FROM person")
disconnect(conn)
```

--------------------------------

### Get DBMS from Connection

Source: https://ohdsi.github.io/DatabaseConnector/reference/dbms.html

Use `dbms(connection)` to retrieve the database platform name. This is required by SqlRender for SQL dialect translation.

```R
library(DatabaseConnector)
con <- connect(dbms = "sqlite", server = ":memory:")
#> Connecting using SQLite driver
dbms(con)
#> [1] "sqlite"
#> [1] "sqlite"
SqlRender::translate("DATEADD(d, 365, dateColumn)", targetDialect = dbms(con))
#> [1] "CAST(STRFTIME('%s', DATETIME(dateColumn, 'unixepoch', (365)||' days')) AS REAL)"
#> attr(,"sqlDialect")
#> [1] "sqlite"
#> "CAST(STRFTIME('%s', DATETIME(dateColumn, 'unixepoch', (365)||' days')) AS REAL)"
disconnect(con)
```

--------------------------------

### Get Available Java Heap Space

Source: https://ohdsi.github.io/DatabaseConnector/reference/getAvailableJavaHeapSpace.html

This function retrieves the available Java heap space in bytes. It is useful for debugging memory-related issues.

```APIDOC
## getAvailableJavaHeapSpace

### Description
For debugging purposes: get the available Java heap space.

### Method
`getAvailableJavaHeapSpace()`

### Value
The Java heap space (in bytes).
```

--------------------------------

### createDbiConnectionDetails()

Source: https://ohdsi.github.io/DatabaseConnector/reference/index.html

Create DBI connection details.

```APIDOC
## createDbiConnectionDetails()

### Description
Creates connection details specifically for DBI (Database Interface) connections.

### Method
N/A (Utility function)

### Parameters
None explicitly listed, likely requires parameters specific to DBI connections.

### Response
- Returns a structure containing DBI connection details.
```

--------------------------------

### Create Connection Details Object

Source: https://ohdsi.github.io/DatabaseConnector/articles/Connecting.html

Create a connection details object using createConnectionDetails to specify connection parameters. This object can then be passed to the connect function, allowing for deferred connection establishment.

```r
details <- createConnectionDetails(dbms = "postgresql",
                                   server = "localhost/postgres",
                                   user = "joe",
                                   password = "secret")
conn <- connect(details)
```

--------------------------------

### Connect to a Database using DatabaseConnectorDriver

Source: https://ohdsi.github.io/DatabaseConnector/articles/DbiAndDbplyr.html

Use dbConnect() to establish a connection to a database, specifying the driver, DBMS, server, user, and password.

```R
connection <- dbConnect(
  DatabaseConnectorDriver(), 
  dbms = "postgresql",
  server = "localhost/postgres",
  user = "joe",
  password = "secret"
)
```

--------------------------------

### List field names of a remote table

Source: https://ohdsi.github.io/DatabaseConnector/reference/dbListFields-DatabaseConnectorConnection-character-method.html

Use this method to get the field names of a remote table. The `databaseSchema` argument is interpreted differently across platforms.

```R
dbListFields(conn, name, databaseSchema = NULL, ...)
```

--------------------------------

### Get Statement from DBI Result

Source: https://ohdsi.github.io/DatabaseConnector/reference/dbGetStatement-DatabaseConnectorDbiResult-method.html

Use `dbGetStatement` to retrieve the SQL query string from a DBI result object. This method is applicable to objects inheriting from `DBIResult`.

```R
dbGetStatement(res, ...)
```

--------------------------------

### Get Available Java Heap Space

Source: https://ohdsi.github.io/DatabaseConnector/reference/getAvailableJavaHeapSpace.html

Call this function to retrieve the current available Java heap space in bytes. This is useful for monitoring and debugging memory usage.

```R
getAvailableJavaHeapSpace()
```

--------------------------------

### Connect to a Database Directly

Source: https://ohdsi.github.io/DatabaseConnector/articles/Connecting.html

Establish a direct connection to a database by providing connection details like dbms, server, user, and password to the connect function. The disconnect function should be used to close the connection.

```r
conn <- connect(dbms = "postgresql",
                server = "localhost/postgres",
                user = "joe",
                password = "secret")
```

```r
disconnect(conn)
```

--------------------------------

### Connect to Oracle Database with Driver Path

Source: https://ohdsi.github.io/DatabaseConnector/reference/connect.html

Connects to an Oracle database, specifying the server, user, password, and the path to the JDBC driver. Ensure the driver is accessible at the provided path. Requires subsequent calls to `dbGetQuery` and `disconnect`.

```r
conn <- connect(
  dbms = "oracle",
  server = "127.0.0.1/xe",
  user = "system",
  password = "xxx",
  pathToDriver = "c:/temp"
)
dbGetQuery(conn, "SELECT COUNT(*) FROM test_table")
disconnect(conn)
```

--------------------------------

### Get DBMS metadata

Source: https://ohdsi.github.io/DatabaseConnector/reference/dbGetInfo-DatabaseConnectorConnection-method.html

Retrieves information on objects of class DBIDriver, DBIConnection or DBIResult. For connections, it returns details such as database version, name, username, host, and port.

```R
dbGetInfo(dbObj, ...)
```

--------------------------------

### Connect to a Database

Source: https://ohdsi.github.io/DatabaseConnector/reference/connect.html

The `connect` function establishes a connection to a specified database. It supports various DBMS types and allows for different authentication methods and connection configurations.

```APIDOC
## connect()

### Description
Establishes a connection to a database.

### Arguments

- **connectionDetails** (object): An object of class `connectionDetails` as created by the `createConnectionDetails()` function.
- **dbms** (string): The type of DBMS running on the server. Valid values include "oracle", "postgresql", "redshift", "sql server", "pdw", "netezza", "bigquery", "sqlite", "sqlite extended", "spark", "snowflake", "iris".
- **user** (string): The user name used to access the server.
- **password** (string): The password for that user.
- **server** (string): The name of the server.
- **port** (integer, optional): The port on the server to connect to.
- **extraSettings** (object, optional): Additional configuration settings specific to the database provider.
- **oracleDriver** (string, optional): Specify which Oracle driver to use. Choose between "thin" or "oci".
- **connectionString** (string, optional): The JDBC connection string. If specified, `server`, `port`, `extraSettings`, and `oracleDriver` are ignored.
- **pathToDriver** (string, optional): Path to a folder containing the JDBC driver JAR files.

### Value
An object that extends `DBIConnection` in a database-specific manner.

### Examples
```R
if (FALSE) { # \dontrun{
connectionDetails <- createConnectionDetails(
  dbms = "postgresql",
  server = "localhost/postgres",
  user = "root",
  password = "xxx"
)
conn <- connect(connectionDetails)
dbGetQuery(conn, "SELECT COUNT(*) FROM person")
disconnect(conn)

conn <- connect(dbms = "sql server", server = "RNDUSRDHIT06.jnj.com")
dbGetQuery(conn, "SELECT COUNT(*) FROM concept")
disconnect(conn)

conn <- connect(
  dbms = "oracle",
  server = "127.0.0.1/xe",
  user = "system",
  password = "xxx",
  pathToDriver = "c:/temp"
)
dbGetQuery(conn, "SELECT COUNT(*) FROM test_table")
disconnect(conn)

conn <- connect(
  dbms = "postgresql",
  connectionString = "jdbc:postgresql://127.0.0.1:5432/cmd_database"
)
dbGetQuery(conn, "SELECT COUNT(*) FROM person")
disconnect(conn)
} # }
```
```

--------------------------------

### createConnectionDetails

Source: https://ohdsi.github.io/DatabaseConnector/reference/index.html

Create details for a database connection.

```APIDOC
## createConnectionDetails()

### Description
Creates the necessary details for establishing a database connection. This function likely abstracts the creation of connection parameters.

### Method
N/A (Utility function)

### Parameters
None explicitly listed, likely requires parameters to specify connection type and credentials.

### Response
- Returns a structure containing connection details.
```

--------------------------------

### createConnectionDetails Function Signature

Source: https://ohdsi.github.io/DatabaseConnector/reference/createConnectionDetails.html

This function creates a list containing all details needed to connect to a database. There are three ways to call this function, depending on the connection details provided.

```APIDOC
## createConnectionDetails

### Description
Creates a list containing all details needed to connect to a database.

### Usage
There are three ways to call this function:

1. `createConnectionDetails(dbms, user, password, server, port, extraSettings, oracleDriver, pathToDriver)`
2. `createConnectionDetails(dbms, connectionString, pathToDriver)`
3. `createConnectionDetails(dbms, connectionString, user, password, pathToDriver)`
```

--------------------------------

### Log all database queries

Source: https://ohdsi.github.io/DatabaseConnector/articles/Querying.html

Enables logging of all queries sent to the server, including execution time, by setting an option and adding a file logger. This is useful for performance analysis.

```r
options(LOG_DATABASECONNECTOR_SQL = TRUE)
ParallelLogger::addDefaultFileLogger("sqlLog.txt", name = "TEST_LOGGER")

persons <- renderTranslatequerySql(conn, 
                                   sql = "SELECT TOP 10 * FROM @schema.person",
                                   schema = "cdm_synpuf")

readLines("sqlLog.txt")
```

--------------------------------

### Get Row Count from DBIResult

Source: https://ohdsi.github.io/DatabaseConnector/reference/dbGetRowCount-DatabaseConnectorDbiResult-method.html

This S4 method is used to retrieve the total number of rows fetched by `dbFetch()` from a `DatabaseConnectorDbiResult` object. Initially, the count is zero. It increments with each `dbFetch()` call, reflecting the rows actually retrieved.

```R
dbGetRowCount(res, ...)
```

--------------------------------

### Get Database Platform

Source: https://ohdsi.github.io/DatabaseConnector/reference/dbms.html

The `dbms` function retrieves the database management system (DBMS) name from a DBI connection object. This name is crucial for SQL translation functions, such as those in the SqlRender package, to adapt SQL to the specific dialect of the target database.

```APIDOC
## dbms(connection)

### Description
Retrieves the database platform name from a DBI connection.

### Arguments
- **connection** (DBIConnection) - The connection to the database server created using `connect()` or `DBI::dbConnect()`.

### Value
The name of the database (dbms) used by SqlRender.

### Examples
```R
library(DatabaseConnector)
con <- connect(dbms = "sqlite", server = ":memory:")
# Connecting using SQLite driver
dbms(con)
# [1] "sqlite"
SqlRender::translate("DATEADD(d, 365, dateColumn)", targetDialect = dbms(con))
# [1] "CAST(STRFTIME('%s', DATETIME(dateColumn, 'unixepoch', (365)||' days')) AS REAL)"
# attr(,"sqlDialect")
# [1] "sqlite"
disconnect(con)
```
```

--------------------------------

### dbConnect Method for DatabaseConnectorDriver

Source: https://ohdsi.github.io/DatabaseConnector/reference/dbConnect-DatabaseConnectorDriver-method.html

Establishes a connection to a database. This method is similar to the standard connect() function but requires a dummy driver to be specified.

```APIDOC
## dbConnect(drv, ...)

### Description
Connect to a database. This function is synonymous with the `connect()` function except a dummy driver needs to be specified.

### Method Signature
```R
# S4 method for class 'DatabaseConnectorDriver'
dbConnect(drv, ...)
```

### Arguments

*   **drv** (object of class `DatabaseConnectorDriver`)
    The result of the `DatabaseConnectorDriver()` function.

*   **...** (any)
    Other parameters. These are the same as expected by the `connect()` function.

### Value
Returns a `DatabaseConnectorConnection` object that can be used with most of the other functions in this package.

### Examples
```R
if (FALSE) { # \dontrun{
conn <- dbConnect(DatabaseConnectorDriver(),
  dbms = "postgresql",
  server = "localhost/ohdsi",
  user = "joe",
  password = "secret"
)
querySql(conn, "SELECT * FROM cdm_synpuf.person;")
dbDisconnect(conn)
} # }

```

--------------------------------

### insertTable()

Source: https://ohdsi.github.io/DatabaseConnector/reference/index.html

Insert a table on the server.

```APIDOC
## insertTable()

### Description
Inserts data into a table on the database server. This might be a wrapper for `dbWriteTable` or `dbAppendTable`.

### Method
N/A (Database operation)

### Parameters
- **table_name**: The name of the table to insert data into.
- **data**: The data to be inserted (e.g., a data frame).
- **connection**: The database connection object.

### Response
- Returns a status indicating success or failure.
```

--------------------------------

### Get column information for a DBI result

Source: https://ohdsi.github.io/DatabaseConnector/reference/dbColumnInfo-DatabaseConnectorDbiResult-method.html

Use `dbColumnInfo` to retrieve a data frame describing the output columns of a query. The returned data frame contains 'name' and 'type' columns for each output field. Do not rely on the 'type' column for data type information; use `dbFetch(res, n = 0)` for accurate type initialization.

```r
dbColumnInfo(res, ...)
```

--------------------------------

### Connect to SQL Server Database

Source: https://ohdsi.github.io/DatabaseConnector/reference/connect.html

Connects to a SQL Server instance using the 'sql server' dbms type. Assumes default authentication or Windows authentication if configured. Requires subsequent calls to `dbGetQuery` and `disconnect`.

```r
conn <- connect(dbms = "sql server", server = "RNDUSRDHIT06.jnj.com")
dbGetQuery(conn, "SELECT COUNT(*) FROM concept")
disconnect(conn)
```

--------------------------------

### Create DBI Connection Details

Source: https://ohdsi.github.io/DatabaseConnector/reference/createDbiConnectionDetails.html

Use this function to wrap any DBI driver. It requires the DBMS type and the driver object. Authentication arguments may also be needed.

```R
createDbiConnectionDetails(dbms, drv, ...)
```

--------------------------------

### connect function

Source: https://ohdsi.github.io/DatabaseConnector/reference/connect.html

The `connect` function creates a connection to a database server. It can be called in several ways, providing flexibility in how connection details are supplied.

```APIDOC
## connect

### Description
Creates a connection to a database server.

### Usage
There are four ways to call this function:

1. `connect(dbms, user, password, server, port, extraSettings, oracleDriver, pathToDriver)`
2. `connect(connectionDetails)`
3. `connect(dbms, connectionString, pathToDriver)`
4. `connect(dbms, connectionString, user, password, pathToDriver)`

### Parameters

* `dbms` (string) - The type of database management system.
* `user` (string) - The username for authentication.
* `password` (string) - The password for authentication.
* `server` (string) - The server address.
* `port` (integer) - The port number for the database connection.
* `extraSettings` (list) - Additional settings for the connection.
* `oracleDriver` (string) - The Oracle driver to use.
* `pathToDriver` (string) - The path to the database driver.
* `connectionDetails` (object) - An object containing all connection details.
* `connectionString` (string) - A connection string for the database.
```

--------------------------------

### DatabaseConnectorDriver()

Source: https://ohdsi.github.io/DatabaseConnector/reference/index.html

Create a DatabaseConnectorDriver object.

```APIDOC
## DatabaseConnectorDriver()

### Description
Creates a DatabaseConnectorDriver object.

### Method
N/A (Constructor)

### Parameters
None

### Response
- Returns a DatabaseConnectorDriver object.
```

--------------------------------

### Set Temporary Table Emulation Schema

Source: https://ohdsi.github.io/DatabaseConnector/articles/DbiAndDbplyr.html

Configure the temporary table emulation schema using options(sqlRenderTempEmulationSchema = "a_schema"). This is required for databases that do not support real temporary tables.

```R
option(sqlRenderTempEmulationSchema = "a_schema")
```

--------------------------------

### renderTranslateQuerySqlToAndromeda()

Source: https://ohdsi.github.io/DatabaseConnector/reference/index.html

Render, translate, and query to local Andromeda.

```APIDOC
## renderTranslateQuerySqlToAndromeda()

### Description
Renders and translates a SQL query, executes it, and retrieves the results into a local Andromeda object.

### Method
N/A (Database operation)

### Parameters
- **sql_template**: The SQL code template.
- **parameters**: Parameters to substitute into the template.
- **connection**: The database connection object.

### Response
- Returns an Andromeda object containing the query results.
```

--------------------------------

### dbCreateTable(_<DatabaseConnectorConnection>_)

Source: https://ohdsi.github.io/DatabaseConnector/reference/index.html

Create a new table in the database.

```APIDOC
## dbCreateTable(_<DatabaseConnectorConnection>_)

### Description
Creates a new table in the database based on provided schema information.

### Method
N/A (Database operation)

### Parameters
- **connection**: A DatabaseConnectorConnection object.
- **table_name**: The name of the table to create.
- **fields**: Information defining the columns of the table (e.g., name, type, constraints).

### Response
- Returns a status indicating success or failure.
```

--------------------------------

### Query Data using dbSendQuery and dbFetch

Source: https://ohdsi.github.io/DatabaseConnector/articles/DbiAndDbplyr.html

Execute a SQL query using dbSendQuery() and fetch results incrementally with dbFetch(). This is useful for large result sets.

```R
res <- dbSendQuery(connection, "SELECT TOP 3 * FROM cdmv5.person")
dbFetch(res)
```

--------------------------------

### executeSql()

Source: https://ohdsi.github.io/DatabaseConnector/reference/index.html

Execute SQL code.

```APIDOC
## executeSql()

### Description
Executes a given SQL command or script against the connected database.

### Method
N/A (Database operation)

### Parameters
- **sql_code**: The SQL code to execute.
- **connection**: The database connection object.

### Response
- Returns a status or result of the execution.
```
Tokens:
Raw