queryDb: Run a query against a database

In tomjemmett/sqlhelpers: Helper functions for querying Sql Databases

Description

This function allows you to run a query against a database, returning the results in a tibble.

Usage

queryDb(server, database, query, ...)

Arguments

server	The name of the DSN for the server you want to connect to, or the name of a environment variable (with the prefix R_DB_)
database	The name of the database that you want to run the query against
query	The query as a string that you want to run against the database
...	Named parameters to interpolate into the query

Details

The query is passed as an argument. See queryDbFromFile if you want to read the query from a file.

The connection to the server is made by odbc and uses the name of a DSN to connect to. This removes any requirement to hard code in usernames, passwords and connection strings into files.

Altenatively, you can create an environment variable called R_DB_[SERVER] as a json string that contains all of the parameters to pass to dbConnect.

Parameters can be used in queries by either specifying each parameter and it's value after the query parameter, or by specifying all of the parameters in a named list.

Value

The results of the query as a tibble

Examples

## Not run: 
queryDb("MyServerName", "MyDatabase", "SELECT * FROM Table")

queryDb("MyServerName",
        "MyDatabase",
        "SELECT * FROM Table WHERE Id = ?id AND Date = ?date",
        id = 1,
        date = Sys.Date())

params <- list(id = 1, date = Sys.Date())
queryDb("MyServerName",
        "MyDatabase",
        "SELECT * FROM Table WHERE Id = ?id AND Date = ?date",
        params)

## End(Not run)

tomjemmett/sqlhelpers documentation built on July 23, 2019, 6:41 p.m.