dbConnect-MariaDBDriver-method.Rd

% Generated by roxygen2: do not edit by hand
% Please edit documentation in R/connect.R, R/dbConnect_MariaDBDriver.R
\name{MariaDB}
\alias{MariaDB}
\alias{dbConnect_MariaDBDriver}
\alias{dbConnect,MariaDBDriver-method}
\title{Connect/disconnect to a MariaDB DBMS}
\usage{
MariaDB()

\S4method{dbConnect}{MariaDBDriver}(
  drv,
  dbname = NULL,
  username = NULL,
  password = NULL,
  host = NULL,
  unix.socket = NULL,
  port = 0,
  client.flag = 0,
  group = "rs-dbi",
  default.file = NULL,
  ssl.key = NULL,
  ssl.cert = NULL,
  ssl.ca = NULL,
  ssl.capath = NULL,
  ssl.cipher = NULL,
  ...,
  groups = NULL,
  load_data_local_infile = FALSE,
  bigint = c("integer64", "integer", "numeric", "character"),
  timeout = 10,
  timezone = "+00:00",
  timezone_out = NULL,
  reconnect = FALSE,
  mysql = NULL
)
}
\arguments{
\item{drv}{an object of class \link[=MariaDBDriver-class]{MariaDBDriver} or
\link[=MariaDBConnection-class]{MariaDBConnection}.}

\item{dbname}{string with the database name or NULL. If not NULL, the
connection sets the default database to this value.}

\item{username, password}{Username and password. If username omitted,
defaults to the current user. If password is omitted, only users
without a password can log in.}

\item{host}{string identifying the host machine running the MariaDB server or
NULL. If NULL or the string \code{"localhost"}, a connection to the local
host is assumed.}

\item{unix.socket}{(optional) string of the unix socket or named pipe.}

\item{port}{(optional) integer of the TCP/IP default port.}

\item{client.flag}{(optional) integer setting various MariaDB client flags,
see \link{Client-flags} for details.}

\item{group}{string identifying a section in the \code{default.file} to use
for setting authentication parameters (see \code{\link[=MariaDB]{MariaDB()}}).}

\item{default.file}{string of the filename with MariaDB client options,
only relevant if \code{groups} is given. The default value depends on the
operating system (see references), on Linux and OS X the files
\verb{~/.my.cnf} and \verb{~/.mylogin.cnf} are used. Expanded with \code{\link[=normalizePath]{normalizePath()}}.}

\item{ssl.key}{(optional) string of the filename of the SSL key file to use.
Expanded with \code{\link[=normalizePath]{normalizePath()}}.}

\item{ssl.cert}{(optional) string of the filename of the SSL certificate to
use. Expanded with \code{\link[=normalizePath]{normalizePath()}}.}

\item{ssl.ca}{(optional) string of the filename of an SSL certificate
authority file to use. Expanded with \code{\link[=normalizePath]{normalizePath()}}.}

\item{ssl.capath}{(optional) string of the path to a directory containing
the trusted SSL CA certificates in PEM format. Expanded with
\code{\link[=normalizePath]{normalizePath()}}.}

\item{ssl.cipher}{(optional) string list of permitted ciphers to use for SSL
encryption.}

\item{...}{Unused, needed for compatibility with generic.}

\item{groups}{deprecated, use \code{group} instead.}

\item{load_data_local_infile}{Set to \code{TRUE} to use \verb{LOAD DATA LOCAL INFILE}
in \code{\link[DBI:dbWriteTable]{dbWriteTable()}} and \code{\link[DBI:dbAppendTable]{dbAppendTable()}} by default.
This capability is disabled by default on the server side
for recent versions of MySQL Server.}

\item{bigint}{The R type that 64-bit integer types should be mapped to,
default is \link[bit64:integer64]{bit64::integer64}, which allows the full range of 64 bit
integers.}

\item{timeout}{Connection timeout, in seconds. Use \code{Inf} or a negative value
for no timeout.}

\item{timezone}{(optional) time zone for the connection,
the default corresponds to UTC.
Set this argument if your server or database is configured with a different
time zone than UTC.
Set to \code{NULL} to automatically determine the server time zone.}

\item{timezone_out}{The time zone returned to R.
The default is to use the value of the \code{timezone} argument,
\code{"+00:00"} is converted to \code{"UTC"}
If you want to display datetime values in the local timezone,
set to \code{\link[=Sys.timezone]{Sys.timezone()}} or \code{""}.
This setting does not change the time values returned, only their display.}

\item{reconnect}{(experimental) Set to \code{TRUE} to use \code{MYSQL_OPT_RECONNECT} to enable
automatic reconnection. This is experimental and could be dangerous if the connection
is lost in the middle of a transaction.}

\item{mysql}{Set to \code{TRUE}/\code{FALSE} to connect to a MySQL server or to a MariaDB server,
respectively.
The \pkg{RMariaDB} package supports both MariaDB and MySQL servers, but the SQL dialect
and other details vary.
The default is to assume MariaDB if the version is >= 10.0.0, and MySQL otherwise.}
}
\description{
These methods are straight-forward implementations of the corresponding
generic functions.
}
\section{Time zones}{

MySQL and MariaDB support named time zones,
they must be installed on the server.
See \url{https://dev.mysql.com/doc/mysql-g11n-excerpt/8.0/en/time-zone-support.html}
for more details.
Without installation, time zone support is restricted to UTC offset,
which cannot take into account DST offsets.
}

\section{Secure passwords}{

Avoid storing passwords hard-coded in the code, use e.g. the \pkg{keyring}
package to store and retrieve passwords in a secure way.

The MySQL client library (but not MariaDB) supports a \code{.mylogin.cnf} file
that can be passed in the \code{default.file} argument.
This file can contain an obfuscated password, which is not a secure way
to store passwords but may be acceptable if the user is aware of the
restrictions.
The availability of this feature depends on the client library used
for compiling the \pkg{RMariaDB} package.
Windows and macOS binaries on CRAN are compiled against the MariaDB Connector/C
client library which do not support this feature.
}

\examples{
if (mariadbHasDefault()) {
  # connect to a database and load some data
  con <- dbConnect(RMariaDB::MariaDB(), dbname = "test")
  dbWriteTable(con, "USArrests", datasets::USArrests, temporary = TRUE)

  # query
  rs <- dbSendQuery(con, "SELECT * FROM USArrests")
  d1 <- dbFetch(rs, n = 10)      # extract data in chunks of 10 rows
  dbHasCompleted(rs)
  d2 <- dbFetch(rs, n = -1)      # extract all remaining data
  dbHasCompleted(rs)
  dbClearResult(rs)
  dbListTables(con)

  # clean up
  dbDisconnect(con)
}
\dontrun{
# Connect to a MariaDB database running locally
con <- dbConnect(RMariaDB::MariaDB(), dbname = "mydb")
# Connect to a remote database with username and password
con <- dbConnect(RMariaDB::MariaDB(),
  host = "mydb.mycompany.com",
  user = "abc", password = "def"
)
# But instead of supplying the username and password in code, it's usually
# better to set up a group in your .my.cnf (usually located in your home
# directory). Then it's less likely you'll inadvertently share them.
con <- dbConnect(RMariaDB::MariaDB(), group = "test")

# To connect to a remote database and require the use of SSL
(and an example of using environment variables for your sensitive info)
con <- dbConnect(RMariaDB::MariaDB(),
  dbname = Sys.getenv('DB_NAME'),
  host = Sys.getenv('DB_HOST'),
  user = Sys.getenv('DB_USER'),
  password = Sys.getenv('DB_PASSWORD'),
  client.flag = CLIENT_SSL
)

# Always cleanup by disconnecting the database
dbDisconnect(con)
}

# All examples use the rs-dbi group by default.
if (mariadbHasDefault()) {
  con <- dbConnect(RMariaDB::MariaDB(), dbname = "test")
  con
  dbDisconnect(con)
}

}
\references{
Configuration files: https://mariadb.com/kb/en/library/configuring-mariadb-with-mycnf/
}