odbc: Connect to ODBC Compatible Databases (using the DBI Interface)

Description

A DBI-compatible interface to ODBC databases.

Author(s)

Maintainer: Hadley Wickham simon.couch@posit.co

Authors:

• Jim Hester

• Oliver Gjoneski

Other contributors:

• lexicalunit (nanodbc library) [copyright holder]

• Google Inc. (cctz library) [copyright holder]

• Posit Software, PBC [copyright holder, funder]

See Also

Useful links:

• https://odbc.r-dbi.org

• https://github.com/r-dbi/odbc

• https://solutions.posit.co/connections/db/

• Report bugs at https://github.com/r-dbi/odbc/issues

DBI classes

Description

DBI classes

Convenience functions for reading/writing DBMS tables

Description

Convenience functions for reading/writing DBMS tables

Usage

## S4 method for signature 'OdbcConnection,character,data.frame'
dbWriteTable(
  conn,
  name,
  value,
  overwrite = FALSE,
  append = FALSE,
  temporary = FALSE,
  row.names = NULL,
  field.types = NULL,
  batch_rows = getOption("odbc.batch_rows", NA),
  ...
)

## S4 method for signature 'OdbcConnection,Id,data.frame'
dbWriteTable(
  conn,
  name,
  value,
  overwrite = FALSE,
  append = FALSE,
  temporary = FALSE,
  row.names = NULL,
  field.types = NULL,
  batch_rows = getOption("odbc.batch_rows", NA),
  ...
)

## S4 method for signature 'OdbcConnection,SQL,data.frame'
dbWriteTable(
  conn,
  name,
  value,
  overwrite = FALSE,
  append = FALSE,
  temporary = FALSE,
  row.names = NULL,
  field.types = NULL,
  batch_rows = getOption("odbc.batch_rows", NA),
  ...
)

## S4 method for signature 'OdbcConnection'
dbAppendTable(
  conn,
  name,
  value,
  batch_rows = getOption("odbc.batch_rows", NA),
  ...,
  row.names = NULL
)

## S4 method for signature 'OdbcConnection'
sqlCreateTable(
  con,
  table,
  fields,
  row.names = NA,
  temporary = FALSE,
  ...,
  field.types = NULL
)

Arguments

Examples

## Not run: 
library(DBI)
con <- dbConnect(odbc::odbc())
dbListTables(con)
dbWriteTable(con, "mtcars", mtcars, temporary = TRUE)
dbReadTable(con, "mtcars")

dbListTables(con)
dbExistsTable(con, "mtcars")

# A zero row data frame just creates a table definition.
dbWriteTable(con, "mtcars2", mtcars[0, ], temporary = TRUE)
dbReadTable(con, "mtcars2")

dbDisconnect(con)

## End(Not run)

SQL Server

Description

Details of SQL Server methods for odbc and DBI generics.

dbUnquoteIdentifier()

conn@quote returns the quotation mark, but quotation marks and square brackets can be used interchangeably for delimited identifiers. (https://learn.microsoft.com/en-us/sql/relational-databases/databases/database-identifiers). This function strips the brackets first and then calls the DBI method to strip the quotation marks.

isTempTable()

Local temp tables are stored as ⁠[tempdb].[dbo].[#name]_____[numeric identifier]⁠, so isTempTable() returns TRUE if catalog_name is "tempdb" or "%", or the name starts with "#".

dbExistsTable()

The default implementation reports temporary tables as non-existent since they live in a different catalog. This method provides a special case for temporary tables, as identified by isTempTable().

dbListTables()

The default implementation reports temporary tables as non-existent when a catalog_name isn't supplied since they live in a different catalog. This method provides a special case for temporary tables.

odbcConnectionSchemas

Method for an internal function. Calls catalog-specific sp_tables to make sure we list the schemas in the appropriate database/catalog.

sqlCreateTable()

Warns if temporary = TRUE but the name does not conform to temp table naming conventions (i.e. it doesn't start with ⁠#⁠).

odbcConnectionColumns_()

If temp table, query the database for the actual table name.

odbcConnectionColumns_()

Copied over from odbc-connection to avoid S4 dispatch NOTEs.

Odbc Connection Methods

Description

Implementations of pure virtual functions defined in the DBI package for OdbcConnection objects.

Usage

## S4 method for signature 'OdbcConnection'
show(object)

## S4 method for signature 'OdbcConnection'
dbIsValid(dbObj, ...)

## S4 method for signature 'OdbcConnection'
dbDisconnect(conn, ...)

## S4 method for signature 'OdbcConnection,character'
dbSendQuery(conn, statement, params = NULL, ..., immediate = FALSE)

## S4 method for signature 'OdbcConnection,character'
dbExecute(conn, statement, params = NULL, ..., immediate = is.null(params))

## S4 method for signature 'OdbcConnection,character'
dbSendStatement(conn, statement, params = NULL, ..., immediate = FALSE)

## S4 method for signature 'OdbcConnection,ANY'
dbDataType(dbObj, obj, ...)

## S4 method for signature 'OdbcConnection,data.frame'
dbDataType(dbObj, obj, ...)

## S4 method for signature 'OdbcConnection,character'
dbQuoteIdentifier(conn, x, ...)

## S4 method for signature 'OdbcConnection,SQL'
dbQuoteIdentifier(conn, x, ...)

## S4 method for signature 'OdbcConnection'
dbGetInfo(dbObj, ...)

## S4 method for signature 'OdbcConnection,character'
dbGetQuery(
  conn,
  statement,
  n = -1,
  params = NULL,
  immediate = is.null(params),
  ...
)

## S4 method for signature 'OdbcConnection'
dbBegin(conn, ...)

## S4 method for signature 'OdbcConnection'
dbCommit(conn, ...)

## S4 method for signature 'OdbcConnection'
dbRollback(conn, ...)

## S4 method for signature 'OdbcConnection,Id'
dbListFields(conn, name, ...)

## S4 method for signature 'OdbcConnection,SQL'
dbListFields(conn, name, ...)

## S4 method for signature 'OdbcConnection,character'
dbListFields(
  conn,
  name,
  catalog_name = NULL,
  schema_name = NULL,
  column_name = NULL,
  ...
)

## S4 method for signature 'OdbcConnection,Id'
dbExistsTable(conn, name, ...)

## S4 method for signature 'OdbcConnection,SQL'
dbExistsTable(conn, name, ...)

## S4 method for signature 'OdbcConnection,character'
dbExistsTable(conn, name, ...)

## S4 method for signature 'OdbcConnection,character'
dbRemoveTable(conn, name, ...)

Arguments

Odbc Driver Methods

Description

Implementations of pure virtual functions defined in the DBI package for OdbcDriver objects.

Usage

## S4 method for signature 'OdbcDriver'
show(object)

## S4 method for signature 'OdbcDriver,ANY'
dbDataType(dbObj, obj, ...)

## S4 method for signature 'OdbcDriver,list'
dbDataType(dbObj, obj, ...)

## S4 method for signature 'OdbcDriver,data.frame'
dbDataType(dbObj, obj, ...)

## S4 method for signature 'OdbcDriver'
dbIsValid(dbObj, ...)

## S4 method for signature 'OdbcDriver'
dbGetInfo(dbObj, ...)

Arguments

Odbc Result Methods

Description

Implementations of pure virtual functions defined in the DBI package for OdbcResult objects.

Usage

## S4 method for signature 'OdbcResult'
dbClearResult(res, ...)

## S4 method for signature 'OdbcResult'
dbFetch(res, n = -1, ...)

## S4 method for signature 'OdbcResult'
dbHasCompleted(res, ...)

## S4 method for signature 'OdbcResult'
dbIsValid(dbObj, ...)

## S4 method for signature 'OdbcResult'
dbGetStatement(res, ...)

## S4 method for signature 'OdbcResult'
dbColumnInfo(res, ...)

## S4 method for signature 'OdbcResult'
dbGetRowCount(res, ...)

## S4 method for signature 'OdbcResult'
dbGetRowsAffected(res, ...)

## S4 method for signature 'OdbcResult'
dbBind(res, params, ..., batch_rows = getOption("odbc.batch_rows", NA))

Arguments

Oracle

Description

Details of Oracle methods for odbc and DBI generics.

odbcConnectionTables()

Method for an internal function that otherwise relies on the SQLTables ODBC API. While this method is much faster than the OEM implementation, it does not look through synonyms.

odbcConnectionColumns()

Query, rather than use SQLColumns ODBC API, since we bind a BIGINT to one of the column results and Oracle's OEM driver can't handle it.

Usage

## S4 method for signature 'Oracle'
sqlCreateTable(
  con,
  table,
  fields,
  row.names = NA,
  temporary = FALSE,
  ...,
  field.types = NULL
)

## S4 method for signature 'Oracle,character'
odbcConnectionTables(
  conn,
  name,
  catalog_name = NULL,
  schema_name = NULL,
  table_type = NULL,
  exact = FALSE
)

Supported Connection Attributes

Description

These (pre) connection attributes are supported and can be passed as part of the dbConnect call in the named list attributes parameter:

Details

• azure_token: This should be a string scalar; in particular Azure Active Directory authentication token. Only for use with Microsoft SQL Server and with limited support away from the OEM Microsoft driver.

Examples

## Not run: 
conn <- dbConnect(
  odbc::odbc(),
  dsn = "my_azure_mssql_db",
  Encrypt = "yes",
  attributes = list("azure_token" = .token)
)

## End(Not run)

Helper for Connecting to Databricks via ODBC

Description

Connect to Databricks clusters and SQL warehouses via the Databricks ODBC driver.

In particular, the custom dbConnect() method for the Databricks ODBC driver implements a subset of the Databricks client unified authentication model, with support for personal access tokens, OAuth machine-to-machine credentials, and OAuth user-to-machine credentials supplied via Posit Workbench or the Databricks CLI on desktop. It can also detect viewer-based credentials on Posit Connect if the connectcreds package is installed. All of these credentials are detected automatically if present using standard environment variables.

In addition, on macOS platforms, the dbConnect() method will check for irregularities with how the driver is configured, and attempt to fix in-situ, unless the odbc.no_config_override environment variable is set.

Usage

databricks()

## S4 method for signature 'DatabricksOdbcDriver'
dbConnect(
  drv,
  httpPath,
  workspace = Sys.getenv("DATABRICKS_HOST"),
  useNativeQuery = TRUE,
  driver = NULL,
  HTTPPath,
  uid = NULL,
  pwd = NULL,
  ...
)

Arguments

Value

An OdbcConnection object with an active connection to a Databricks cluster or SQL warehouse.

Examples

## Not run: 
DBI::dbConnect(
  odbc::databricks(),
  httpPath = "sql/protocolv1/o/4425955464597947/1026-023828-vn51jugj"
)

# Use credentials from the viewer (when possible) in a Shiny app
# deployed to Posit Connect.
library(connectcreds)
server <- function(input, output, session) {
  conn <- DBI::dbConnect(
    odbc::databricks(),
    httpPath = "sql/protocolv1/o/4425955464597947/1026-023828-vn51jugj"
  )
}

## End(Not run)

List remote tables and fields for an ODBC connection

Description

dbListTables() provides names of remote tables accessible through this connection; dbListFields() provides names of columns within a table.

Usage

## S4 method for signature 'OdbcConnection'
dbListTables(
  conn,
  catalog_name = NULL,
  schema_name = NULL,
  table_name = NULL,
  table_type = NULL,
  ...
)

Arguments

Value

A character vector of table or field names respectively.

Helper method used to determine if a table identifier is that of a temporary table.

Description

Currently implemented only for select back-ends where we have a use for it (SQL Server, for example). Generic, in case we develop a broader use case.

Usage

isTempTable(conn, name, ...)

## S4 method for signature 'OdbcConnection,Id'
isTempTable(conn, name, ...)

## S4 method for signature 'OdbcConnection,SQL'
isTempTable(conn, name, ...)

Arguments

Connect to a database via an ODBC driver

Description

The dbConnect() method documented here is invoked when DBI::dbConnect() is called with the first argument odbc(). Connecting to a database via an ODBC driver is likely the first step in analyzing data using the odbc package; for an overview of package concepts, see the Overview section below.

Usage

odbc()

## S4 method for signature 'OdbcDriver'
dbConnect(
  drv,
  dsn = NULL,
  ...,
  timezone = "UTC",
  timezone_out = "UTC",
  encoding = "",
  name_encoding = "",
  bigint = c("integer64", "integer", "numeric", "character"),
  timeout = 10,
  driver = NULL,
  server = NULL,
  database = NULL,
  uid = NULL,
  pwd = NULL,
  dbms.name = NULL,
  attributes = NULL,
  interruptible = getOption("odbc.interruptible", interactive()),
  .connection_string = NULL
)

Arguments

Connection strings

Internally, dbConnect() creates a connection string using the supplied arguments. Connection string keywords are driver-dependent; the arguments documented here are common, but some drivers may not accept them.

Alternatively to configuring DSNs and driver names with the driver manager, you can pass a complete connection string directly as the .connection_string argument. The Connection Strings Reference is a useful resource that has example connection strings for a large variety of databases.

Overview

The odbc package is one piece of the R interface to databases with support for ODBC:

The package supports any Database Management System (DBMS) with ODBC support. Support for a given DBMS is provided by an ODBC driver, which defines how to interact with that DBMS using the standardized syntax of ODBC and SQL. Drivers can be downloaded from the DBMS vendor or, if you're a Posit customer, using the professional drivers. To manage information about each driver and the data sources they provide access to, our computers use a driver manager. Windows is bundled with a driver manager, while MacOS and Linux require installation of one; this package supports the unixODBC driver manager.

In the R interface, the DBI package provides a front-end while odbc implements a back-end to communicate with the driver manager. The odbc package is built on top of the nanodbc C++ library.

Interfacing with DBMSs using R and odbc involves three high-level steps:

1. Configure drivers and data sources: the functions odbcListDrivers(), odbcListDataSources(), and odbcListConfig() help to interface with the driver manager.

2. Connect to a database: The dbConnect() function, called with the first argument odbc(), connects to a database using the specified ODBC driver to create a connection object.

3. Interface with connections: The resulting connection object can be passed to various functions to retrieve information on database structure ([DBI::dbListTables()][]), iteratively develop queries (DBI::dbSendQuery(), DBI::dbColumnInfo()), and query data objects (DBI::dbFetch()).

Learn more

To learn more about databases:

• "Best Practices in Working with Databases" documents how to use the odbc package with various popular databases.

• The pyodbc "Drivers and Driver Managers" Wiki provides further context on drivers and driver managers.

• Microsoft's "Introduction to ODBC" is a thorough resource on the ODBC interface.

List the actions supported for the connection

Description

Return a list of actions that can be performed on the connection.

Usage

odbcConnectionActions(connection)

Arguments

Details

The list returned is a named list of actions, where each action has the following properties:

callback

A function to be invoked to perform the action

icon

An optional path to an icon representing the action

Value

A named list of actions that can be performed on the connection.

odbcConnectionColumns

Description

This function has been deprecated in favor of DBI::dbListFields().

For a given table this function returns detailed information on all fields / columns. The expectation is that this is a relatively thin wrapper around the ODBC SQLColumns function call, with some of the field names renamed / re-ordered according to the return specifications below.

Usage

odbcConnectionColumns(conn, name, ..., exact = FALSE)

Arguments

Details

In DBI::dbWriteTable() we make a call to this method to get details on the fields of the table we are writing to. In particular the columns data_type, column_size, and decimal_digits are used. An implementation is not necessary for DBI::dbWriteTable() to work.

odbcConnectionColumns is routed through the SQLColumns ODBC method. This function, together with remaining catalog functions (SQLTables, etc), by default ( SQL_ATTR_METADATA_ID == false ) expect either ordinary arguments (OA) in the case of the catalog, or pattern value arguments (PV) in the case of schema/table name. For these, quoted identifiers do not make sense, so we unquote identifiers prior to the call.

Value

data.frame with columns

• name

• field.type - equivalent to type_name in SQLColumns output

• table_name

• schema_name

• catalog_name

• data_type

• column_size

• buffer_length

• decimal_digits

• numeric_precision_radix

• remarks

• column_default

• sql_data_type

• sql_datetime_subtype

• char_octet_length

• ordinal_position

• nullable

See Also

The ODBC documentation on SQLColumns for further details.

Connecting to Snowflake via ODBC

Description

odbcConnectionColumns()

If the catalog, or the schema arguments are NULL, attempt to infer by querying for CURRENT_DATABASE() and CURRENT_SCHEMA(). We do this to aid with performance, as the SQLColumns method is more performant when restricted to a particular DB/schema.

Usage

## S4 method for signature 'Snowflake,character'
dbExistsTableForWrite(conn, name, ..., catalog_name = NULL, schema_name = NULL)

Arguments

Get an icon representing a connection.

Description

Return the path on disk to an icon representing a connection.

Usage

odbcConnectionIcon(connection)

Arguments

Details

The icon returned should be a 32x32 square image file.

Value

The path to an icon file on disk.

Return the corresponding ODBC data type for an R object

Description

This is used when creating a new table with dbWriteTable(). Databases with default methods defined are:

• MySQL

• PostgreSQL

• SQL Server

• Oracle

• SQLite

• Spark

• Hive

• Impala

• Redshift

• Vertica

• BigQuery

• Teradata

• Access

• Snowflake

Usage

odbcDataType(con, obj, ...)

Arguments

Details

If you are using a different database and dbWriteTable() fails with a SQL parsing error the default method is not appropriate, you will need to write a new method. The object type for your method will be the database name retrieved by dbGetInfo(con)$dbms.name. Use the documentation provided with your database to determine appropriate values for each R data type.

Value

Corresponding SQL type for the obj.

List columns in an object.

Description

Lists the names and types of each column (field) of a specified object.

Usage

odbcListColumns(connection, ...)

Arguments

Details

The object to inspect must be specified as one of the arguments (e.g. table = "employees"); depending on the driver and underlying data store, additional specification arguments may be required.

Value

A data frame with name and type columns, listing the object's fields.

List locations of ODBC configuration files

Description

On MacOS and Linux, odbc uses the unixODBC driver manager to manage information about driver and data sources. This helper returns the filepaths where the driver manager will look for that information.

odbcListConfig() is a wrapper around the command line call odbcinst -j. The odbcEditDrivers(), odbcEditSystemDSN(), and odbcEditUserDSN() helpers provide a shorthand for file.edit(odbcListConfig()[[i]]).

Windows does not use .ini configuration files; on Windows, odbcListConfig() will return a 0-length vector and ⁠odbcEdit*()⁠ will raise an error.

Usage

odbcListConfig()

odbcEditDrivers()

odbcEditSystemDSN()

odbcEditUserDSN()

See Also

The odbcListDrivers() and odbcListDataSources() helpers return information on the contents of odbcinst.ini and odbc.ini files, respectively. odbcListDataSources() collates entries from both the System and User odbc.ini files.

Learn more about unixODBC and the odbcinst utility here.

Examples

configs <- odbcListConfig()

configs

# shorthand for file.edit(configs[[1]])
odbcEditDrivers()

List Configured Data Source Names

Description

Collect information about the available data source names (DSNs). A DSN must be both installed and configured with the driver manager to be included in this list. Configuring a DSN just sets up a lookup table (e.g. in odbc.ini) to allow users to pass only the DSN to dbConnect().

DSNs that are not configured with the driver manager can still be connected to with dbConnect() by providing DSN metadata directly.

Usage

odbcListDataSources()

Value

A data frame with two columns:

name

Name of the data source. The entries in this column can be passed to the dsn argument of dbConnect().

description

Data source description.

Configuration

This function interfaces with the driver manager to collect information about the available data source names.

For MacOS and Linux, the odbc package supports the unixODBC driver manager. unixODBC looks to the odbc.ini configuration file for information on DSNs. Find the location(s) of your odbc.ini file(s) with odbcinst -j.

In this example odbc.ini file:

[MySQL]
Driver = MySQL Driver
Database = test
Server = 127.0.0.1
User = root
password = root
Port = 3306

...the data source name is MySQL, which will appear in the name column of this function's output. To pass the DSN as the dsn argument to dbConnect(), pass it as a string, like "MySQL". ⁠Driver = MySQL Driver⁠ references the driver name in odbcListDrivers() output.

Windows is bundled with an ODBC driver manager.

When a DSN is configured with a driver manager, information on the DSN will be automatically passed on to dbConnect() when its dsn argument is set.

For example, with the MySQL data source name configured, and the driver name ⁠MySQL Driver⁠ appearing in odbcListDrivers() output, the code:

con <-
  dbConnect(
    odbc::odbc(),
    Driver = "MySQL Driver",
    Database = "test",
    Server = "127.0.0.1",
    User = "root",
    password = "root",
    Port = 3306
  )

...can be written:

con <- dbConnect(odbc::odbc(), dsn = "MySQL")

In this case, dbConnect() will look up the information defined for MySQL in the driver manager (in our example, odbc.ini) and automatically pass the needed arguments.

See Also

odbcListDrivers()

List Configured ODBC Drivers

Description

Collect information about the configured driver names. A driver must be both installed and configured with the driver manager to be included in this list. Configuring a driver name just sets up a lookup table (e.g. in odbcinst.ini) to allow users to pass only the driver name to dbConnect().

Driver names that are not configured with the driver manager (and thus do not appear in this function's output) can still be used in dbConnect() by providing a path to a driver directly.

Usage

odbcListDrivers(
  keep = getOption("odbc.drivers_keep"),
  filter = getOption("odbc.drivers_filter")
)

Arguments

Value

A data frame with three columns.

name

Name of the driver. The entries in this column can be passed to the driver argument of dbConnect() (as long as the driver accepts the argument).

attribute

Driver attribute name.

value

Driver attribute value.

If a driver has multiple attributes, there will be one row per attribute, each with the same driver name. If a given driver name does not have any attributes, the function will return one row with the driver name, but the last two columns will be NA.

Configuration

This function interfaces with the driver manager to collect information about the available driver names.

For MacOS and Linux, the odbc package supports the unixODBC driver manager. unixODBC looks to the odbcinst.ini configuration file for information on driver names. Find the location(s) of your odbcinst.ini file(s) with odbcinst -j.

In this example odbcinst.ini file:

[MySQL Driver]
Driver=/opt/homebrew/Cellar/mysql/8.2.0_1/lib/libmysqlclient.dylib

Then the driver name is ⁠MySQL Driver⁠, which will appear in the name column of this function's output. To pass the driver name as the driver argument to dbConnect(), pass it as a string, like "MySQL Driver".

Windows is bundled with an ODBC driver manager.

In this example, function output would include 1 row: the name column would read "MySQL Driver", attribute would be "Driver", and value would give the file path to the driver. Additional key-value pairs under the driver name would add additional rows with the same name entry.

When a driver is configured with a driver manager, information on the driver will be automatically passed on to dbConnect() when its driver argument is set. For an example, see the same section in the odbcListDataSources() help-file. Instead of configuring driver information with a driver manager, it is also possible to provide a path to a driver directly to dbConnect().

See Also

odbcListDataSources()

Examples

odbcListDrivers()

Return the object hierarchy supported by a connection.

Description

Lists the object types and metadata known by the connection, and how those object types relate to each other.

Usage

odbcListObjectTypes(connection)

Arguments

Details

The returned hierarchy takes the form of a nested list, in which each object type supported by the connection is a named list with the following attributes:

contains

A list of other object types contained by the object, or "data" if the object contains data

icon

An optional path to an icon representing the type

For instance, a connection in which the top-level object is a schema that contains tables and views, the function will return a list like the following:

list(schema = list(contains = list(
                   list(name = "table", contains = "data")
                   list(name = "view", contains = "data"))))

Value

The hierarchy of object types supported by the connection.

List objects in a connection.

Description

Lists all of the objects in the connection, or all the objects which have specific attributes.

Usage

odbcListObjects(connection, ...)

Arguments

Details

When used without parameters, this function returns all of the objects known by the connection. Any parameters passed will filter the list to only objects which have the given attributes; for instance, passing schema = "foo" will return only objects matching the schema foo.

Value

A data frame with name and type columns, listing the objects.

Preview the data in an object.

Description

Return the data inside an object as a data frame.

Usage

odbcPreviewObject(connection, rowLimit, ...)

Arguments

Details

The object to previewed must be specified as one of the arguments (e.g. table = "employees"); depending on the driver and underlying data store, additional specification arguments may be required.

Value

A data frame containing the data in the object.

Create a preview query.

Description

Optimize against the rowLimit argument. S3 since some back-ends do not parse the LIMIT syntax. Internal, not expected that users would interact with this method.

Usage

odbcPreviewQuery(connection, rowLimit, name)

## S3 method for class 'OdbcConnection'
odbcPreviewQuery(connection, rowLimit, name)

## S3 method for class ''Microsoft SQL Server''
odbcPreviewQuery(connection, rowLimit, name)

## S3 method for class 'Teradata'
odbcPreviewQuery(connection, rowLimit, name)

## S3 method for class 'Oracle'
odbcPreviewQuery(connection, rowLimit, name)

Arguments

Set the Transaction Isolation Level for a Connection

Description

Set the Transaction Isolation Level for a Connection

Usage

odbcSetTransactionIsolationLevel(conn, levels)

Arguments

See Also

https://learn.microsoft.com/en-us/sql/odbc/reference/develop-app/setting-the-transaction-isolation-level

Examples

## Not run: 
# Can use spaces or underscores in between words.
odbcSetTransactionIsolationLevel(con, "read uncommitted")

# Can also use the full constant name.
odbcSetTransactionIsolationLevel(con, "SQL_TXN_READ_UNCOMMITTED")

## End(Not run)

Quote special character when connecting

Description

When connecting to a database using odbc, all the arguments are concatenated into a single connection string that looks like ⁠name1=value1;name2=value2⁠. That means if your value contains = or ⁠;⁠ then it needs to be quoted. Other rules mean that you need to quote any values that starts or ends with white space, or contains ⁠{⁠ or ⁠}⁠.

This function quotes a string in a way that should work for most drivers, but unfortunately there doesn't seem to be an approach that works everywhere. If this function doesn't work for you, you'll need to carefully read the docs for your driver.

Usage

quote_value(x)

Arguments

Value

A quoted string, wrapped in I().

Examples

quote_value("abc")
quote_value("ab'c")

# Real usage is more likely to look like:
## Not run: 
library(DBI)

con <- dbConnect(
  odbc::odbc(),
  dsn = "reallycooldatabase"
  password = odbc::quote_value(Sys.getenv("MY_PASSWORD"))
)

## End(Not run)

Helper for Connecting to Redshift via ODBC

Description

Connect to Redshift clusters via ODBC.

In particular, the custom dbConnect() method for Redshift ODBC drivers automatically determines whether IAM-based credentials are available, much like other AWS SDKs and tools. This requires the paws.common package.

Usage

redshift()

## S4 method for signature 'RedshiftOdbcDriver'
dbConnect(
  drv,
  clusterId,
  server,
  database,
  region = NULL,
  driver = NULL,
  uid = NULL,
  pwd = NULL,
  dbUser = uid,
  ...
)

Arguments

Value

An OdbcConnection object with an active connection to a Redshift cluster or SQL warehouse.

Examples

## Not run: 
# Connect to Redshift using IAM credentials.
DBI::dbConnect(
  odbc::redshift(),
  clusterId = "my-testing-cluster",
  database = "dev",
  dbUser = "me"
)

## End(Not run)

Helper for connecting to Snowflake via ODBC

Description

Connect to a Snowflake account via the Snowflake ODBC driver.

In particular, the custom dbConnect() method for the Snowflake ODBC driver detects ambient OAuth credentials on platforms like Snowpark Container Services or Posit Workbench. It can also detect viewer-based credentials on Posit Connect if the connectcreds package is installed.

In addition, on macOS platforms, the dbConnect method will check and warn if it detects irregularities with how the driver is configured, unless the odbc.no_config_override environment variable is set.

Usage

snowflake()

## S4 method for signature 'SnowflakeOdbcDriver'
dbConnect(
  drv,
  account = Sys.getenv("SNOWFLAKE_ACCOUNT"),
  driver = NULL,
  warehouse = NULL,
  database = NULL,
  schema = NULL,
  uid = NULL,
  pwd = NULL,
  ...
)

Arguments

Value

An OdbcConnection object with an active connection to a Snowflake account.

Examples

## Not run: 
# Use ambient credentials.
DBI::dbConnect(odbc::snowflake())

# Use browser-based SSO (if configured). Only works on desktop.
DBI::dbConnect(
  odbc::snowflake(),
  account = "testorg-test_account",
  authenticator = "externalbrowser"
)

# Use a traditional username & password.
DBI::dbConnect(
  odbc::snowflake(),
  account = "testorg-test_account",
  uid = "me",
  pwd = rstudioapi::askForPassword()
)

# Use credentials from the viewer (when possible) in a Shiny app
# deployed to Posit Connect.
library(connectcreds)
server <- function(input, output, session) {
  conn <- DBI::dbConnect(odbc::snowflake())
}

## End(Not run)

Unimportant DBI methods

Description

Unimportant DBI methods

Usage

## S4 method for signature 'OdbcConnection'
sqlData(con, value, row.names = NA, ...)

## S4 method for signature 'HDB'
sqlCreateTable(
  con,
  table,
  fields,
  row.names = NA,
  temporary = FALSE,
  ...,
  field.types = NULL
)

## S4 method for signature 'Hive,character'
dbQuoteString(conn, x, ...)

## S4 method for signature 'Teradata'
sqlCreateTable(
  con,
  table,
  fields,
  row.names = NA,
  temporary = FALSE,
  ...,
  field.types = NULL
)