odbcListDataSources function

List Configured Data Source Names

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.

odbcListDataSources()

Returns

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()