Stack Overflow
Stack Overflow

Questions tagged [r-dbi]

DBI is an R package providing a common interface to several databases (currently MySQL, PostgreSQL, SQLite and Oracle are supported, as well as JDBC connections).

DBI is an package providing a common interface to several databases (currently , , and are supported, as well as connections).

Repositories

Other resources

Packages that extend DBI

Users don't often work directly with DBI; you probably want one of these

Related tags

272 questions
21
votes
11 answers

R DBI ODBC error: nanodbc/nanodbc.cpp:3110: 07009: [Microsoft][ODBC Driver 13 for SQL Server]Invalid Descriptor Index

I continue to read the DBI/ODBC is faster than RODBC, so I tried as follows: require(DBI);require(odbc) con <- DBI::dbConnect(odbc::odbc(), dsn = 'SQLSERVER1', database = 'AcumaticaDB') I can make a successful connection to the DSN, but the…
user2948714
  • 671
  • 2
  • 9
  • 13
20
votes
2 answers

Set dbGetQuery to return integer64 as integer

By default when I use dbGetQuery() from the DBI package it returns columns of type integer64 as the integer64 class of the bit64. I then use dplyr to try and filter and manipulate my results but come into issues as dplyr does not support objects of…
user1165199
  • 6,351
  • 13
  • 44
  • 60
16
votes
3 answers

How to pass data.frame for UPDATE with R DBI

With RODBC, there were functions like sqlUpdate(channel, dat, ...) that allowed you pass dat = data.frame(...) instead of having to construct your own SQL string. However, with R's DBI, all I see are functions like dbSendQuery(conn, statement, ...)…
mchen
  • 9,808
  • 17
  • 72
  • 125
14
votes
2 answers

How to connect R to MySQL? Failed to connect to database: Error: Plugin caching_sha2_password could not be loaded

I recently installed MySQL on my computer and am trying to connect RStudio to MySQL. I followed instructions in a book as well as instructions here. However, whenever I use dbConnect() or src_mysql in RStudio, I get this error message: Error in…
Phil
  • 578
  • 3
  • 7
  • 17
14
votes
4 answers

collect only if query returns less than n_max rows

Occasionally when connecting to my Oracle database through ROracle and dbplyr I will run a dplyr::collect operation that fetches more data than expected and than R can handle. This can make R crash and is often a sign I should have filtered or…
moodymudskipper
  • 46,417
  • 11
  • 121
  • 167
12
votes
1 answer

Can I run an SQL update statement using only dplyr syntax in R

I need to update column values conditionnaly on other columns in some PostgreSQL database table. I managed to do it writing an SQL statement in R and executing it with dbExecute from DBI package. library(dplyr) library(DBI) # Establish connection…
Romain
  • 1,931
  • 1
  • 13
  • 24
11
votes
1 answer

Mutate variables in database tables directly using dplyr

Here is mtcars data in the MonetDBLite database file. library(MonetDBLite) library(tidyverse) library(DBI) dbdir <- getwd() con <- dbConnect(MonetDBLite::MonetDBLite(), dbdir) dbWriteTable(conn = con, name = "mtcars_1", value = mtcars) data_mt <-…
Geet
  • 2,515
  • 2
  • 19
  • 42
10
votes
2 answers

How to pipe SQL into R's dplyr?

I can use the following code in R to select distinct rows in any generic SQL database. I'd use dplyr::distinct() but it's not supported in SQL syntax. Anyways, this does indeed work: dbGetQuery(database_name, "SELECT t.* FROM…
Display name
  • 4,153
  • 5
  • 27
  • 75
10
votes
1 answer

Is there a way to timeout a MySql query when using DBI and dbGetQuery?

I realize that dbGetQuery comes with a default implementation that calls dbSendQuery, then dbFetch, ensuring that the result is always freed by dbClearResult. and dbClearResult frees all resources (local and remote) associated with a result set.…
d8aninja
  • 3,233
  • 4
  • 36
  • 60
10
votes
1 answer

Execute multiple SQL commands at once on R

I am using RMySQL and DBI for the connection between R and MySQL library(RMySQL) library(DBI, quietly = TRUE) Everything is working fine for one command, such as sql = "select * from clients" con <- dbConnect(MySQL(),user=user, password=password,…
Duy Bui
  • 1,348
  • 6
  • 17
  • 38
9
votes
1 answer

dbplyr, dplyr, and functions with no SQL equivalents [eg `slice()`]

library(tidyverse) con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:") copy_to(con, mtcars) mtcars2 <- tbl(con, "mtcars") I can create this mock SQL database above. And it's very cool that I can perform standard dplyr functions on this…
Display name
  • 4,153
  • 5
  • 27
  • 75
9
votes
0 answers

sqlAppendTable and dbExecute work, but not dbAppendTable. why?

I have a data.frame that I am trying to append to a table in a PostgreSQL database in R using the DBI package. library(DBI) library(RPostgreSQL) mydrv <- dbDriver(drvName = "PostgreSQL") mydbcon <- dbConnect(drv = mydrv , host = myhostname ,…
cmo
  • 3,762
  • 4
  • 36
  • 64
9
votes
2 answers

Reconnect to PostgreSQL database with R's pool package

I have an API built with R plumber that connects to a PostgreSQL database using RPostgreSQL and pool (although this would also apply if I was using a Shiny app): # create the connection pool pool <- dbPool( drv = PostgreSQL(), host =…
Devin
  • 851
  • 12
  • 32
9
votes
1 answer

Update a table using subquery in SQLite

I want to add a column to my table using ALTER TABLE and UPDATE statements not to recreate the full table. When using a subquery in my UPDATE statement I don't get the output I expect. build reproducible…
moodymudskipper
  • 46,417
  • 11
  • 121
  • 167
9
votes
3 answers

RMySQL system error: 10060

I have a project with a connection that was working properly on the same device. I suddenly got the error below. And I could connect from the same device through MySQL workbench. What could be the reason? library(RMySQL) con <-…
OmaymaS
  • 1,671
  • 1
  • 14
  • 18
1
2 3
…
18 19