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
5
votes
1 answer

dbWriteTable function returning TRUE status even if it fails to insert record as it violates the schema constraints

I am using DBI, RMySql packages for interacting with MySql database. Here are more details about configuration: R version: 3.3.2 DBI version: 0.7 RMySql version: 0.10.13 Below is the schema for table…
Abhinandan Satpute
  • 2,558
  • 6
  • 25
  • 43
5
votes
2 answers

How to correctly use sqlAppendTable?

Short summary I'm trying to insert data from an R data.frame into a table on an SQLServer database using the DBI package. After reading the information about sqlAppendTable I was hoping this function could help me generate the necessary SQL…
Willem
  • 976
  • 9
  • 24
5
votes
1 answer

Use dplyr with database without creating an explicit DBI object

Most code examples showing how to use dplyr with a database involve creating a database connection object: connStr <- "driver=driver;server=hostname;database=mydatabase;..." db <- DBI::dbConnect(odbc::odbc(), .connection_string=connStr) tbl <-…
Hong Ooi
  • 56,353
  • 13
  • 134
  • 187
4
votes
1 answer

Why use a pool in shiny application?

In several places (1, 2) I find the following statement as to why I should use the pool package (https://github.com/rstudio/pool) to manage my database connections in a Shiny app: Opening only one connection per app … cannot handle simultaneous…
David K
  • 78
  • 6
4
votes
0 answers

dplyr/dbplyr: sql table vs df speed

I am currently writing a function with dplyr to do calculations. The function turns out to be really slow when I input a tbl_sql object, but reasonably fast when I input a data.frame. An example, df = data.frame( a = rnorm(1000000), b =…
user9672798
  • 113
  • 1
  • 4
4
votes
1 answer

R Updating database with dbi

I have worked little bit with DBI in R and first question is more of best practice, as currently appending new data to DB is taking more time than I hoped. Second is error that I'm receiving when trying to update old information in database. Here is…
Hakki
  • 1,440
  • 12
  • 26
4
votes
3 answers

ODBC connect, get a table in R

Wrestling the whole day with this issue: I Want to access data on Hadoop (through Hive). And installed the ODBC package. I'm able to make connection with the server: con <- dbConnect(odbc:: odbc(), "hadoop") And I am able to see the table that…
R overflow
  • 1,292
  • 2
  • 17
  • 37
4
votes
2 answers

Connect MS Access database to R

I want to connect MS Access database to R with DBI package. I try this: library(DBI) con <- dbConnect(odbc::odbc(), "BASE_MEPSA") and I have this error Error: nanodbc/nanodbc.cpp:950: HY024: [Microsoft][Pilote ODBC Microsoft Access] « (Inconnu)…
Roland Samati
  • 77
  • 1
  • 5
4
votes
1 answer

How to use parameterized SQL with dplyr?

I'm trying to execute a SQL query with dplyr on SQL Server: tbl(con, sql(sqlQuery)) The query is generated dynamically using sprintf("SELECT ... WHERE a = '%s'). This is a bad practice because it can be abused for SQL injection, but I can't find…
ckarras
  • 4,946
  • 2
  • 33
  • 37
4
votes
1 answer

How do I remove embedded quotes from sqlInterpolate function of R?

I am trying to generate a bunch of SQL scripts using DBI::sqlInterpolate function but constantly get SQL error as the script embeds quotes that are returned with the R variable. Here is the code: > x<-'state_transtions' > y<-'transition_time' >…
Lazarus Thurston
  • 1,197
  • 15
  • 33
4
votes
0 answers

"set" statement RMySQL and DBI package

I am using 'RMySQL' and 'DBI' for MySQL database connection. Does anyone know if it is possible to use the set @variable := something when sending queries with DBI::dbGetQuery function? When trying to pass the query, I am getting an error that…
Alfred Beit
  • 77
  • 7
4
votes
1 answer

How to get the schema of a database with the DBI package

Is there a function in the DBI package to get information about the schema of a database? I am working with a connection to an SQL Server with the DBI package and the odbc driver. The dbListTables functions allows to show the tables in a particular…
Patrick Werkmeister
  • 43
  • 1
  • 3
4
votes
3 answers

R: sqlAppendTable only works with numbers?

I am unable to get the R language DBI::sqlAppendTable function to work with anything other than numbers. Below is a bit of code illustrating the problem. I suspect the problem is that sqlAppendTable does not quote data. Any fix or workaround would…
Argent
  • 885
  • 2
  • 9
  • 18
4
votes
1 answer

R SQL: Pull data from MySQL for list of ids already in a dataframe

I have a dataframe in R which contains the output of previous queries. Unfortunately, I cannot do this directly in SQL since it is too slow so I am using the data.table package. The output from the data.table package is a data frame of 50,000 ids. I…
quant actuary
  • 75
  • 6
3
votes
1 answer

Issue when several users are saving data in postgresql database with Rshiny (many duplicates of an unique row are created)

I need some clarification on how to properly send queries to my database within RShiny... I have build-up an application in which anyone can create an account and then write some informations in a dataframe before saving those rows to my…
wanderzen
  • 119
  • 2
  • 12
1 2
3
…
18 19