See the FAQ: How to do a minimal reproducible example `reprex`

for beginners.

The idea behind an `R`

database connection is to allow bringing variables and records into memory selectively, perform some operation and then doing something with the result. That *something* may include writing it back to the database but doing so can be unwise.

The database libraries

- Open a connection
- Allow the database to be queried
- Close the connection

The `{pool}`

library deals with 1 & 3. It is *primarily* intended for use within `shiny`

apps and can be replaced with more direct approaches. For example, see the example in `help(dbPool)`

.

Other libraries deal with all three aspects. A script would look like

```
library(DBI)
library(RMariaDB)
con <- dbConnect(RMariaDB::MariaDB(),
username="the_user",
password="the_password",
dbname ="r")
dbListTables(con)
#> [1] "jgr"
dbListFields(con, "jgr")
#> [1] "recid" "id" "v1" "v2" "v3" "v4" "v5" "v6" "v7"
#> [10] "v8" "v9" "v10" "v11" "v12" "v13" "v14" "v15" "v16"
#> [19] "v17" "v18" "v19" "v20" "v21" "v22" "v23" "v24" "v25"
#> [28] "v26" "v27" "v28" "v29" "v30"
x <- dbGetQuery(con, "SELECT v4 FROM jgr WHERE id = 'NS_000'")
y <- dbGetQuery(con, "SELECT v6 FROM jgr WHERE id = 'NS_022'")
dbDisconnect(con)
dat <- cbind(x,y)
fit <- lm(v6 ~ v4, data = dat)
summary(fit)
#>
#> Call:
#> lm(formula = v6 ~ v4, data = dat)
#>
#> Residuals:
#> Min 1Q Median 3Q Max
#> -618.21 -228.26 -6.68 13.13 1950.54
#>
#> Coefficients:
#> Estimate Std. Error t value Pr(>|t|)
#> (Intercept) -3.18906 13.50270 -0.236 0.813
#> v4 0.59126 0.01733 34.125 <2e-16 ***
#> ---
#> Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
#>
#> Residual standard error: 391.6 on 999 degrees of freedom
#> Multiple R-squared: 0.5383, Adjusted R-squared: 0.5378
#> F-statistic: 1165 on 1 and 999 DF, p-value: < 2.2e-16
```

This does use two SQL statements, like `SELECT v4 FROM jgr WHERE id = 'NS_000'`

. An alternative is provided by `{dbplyr}`

, which relieves even that slight degree of complexity by allowing queries using `{dplyr}`

syntax.