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
0
votes
2 answers

dbplyr tbl and DBI dbListTables - conflicting results on table presence

Here is my code library(DBI) library(dplyr) con <- dbConnect(odbc::odbc(), some_credentials) dbListTables(con, table_name = "Table_A") The above code returns Table_A indicating presence of table. Now I am trying to query Table_A df <-…
user1700890
  • 7,144
  • 18
  • 87
  • 183
0
votes
1 answer

R code optimization: For loop and writing to a database

I am trying to optimize a simple R code I wrote on two aspects: 1) For loops 2) Writing data into my PostgreSQL database For 1) I know for loops should be avoided at all cost and it's recommended to use lapply but I am not clear on how to translate…
ML_Enthousiast
  • 1,147
  • 1
  • 15
  • 39
0
votes
0 answers

Exclude row names when saving data to a database table

#install.packages("rfm") library(rfm) #library(data.table) ########Data Preparation invoice_no <- c('536365','536366','536365','536367') stock_code <- c('85123A','71053','84406B','84029G') description <- c('WHITE HANGING HEART T-LIGHT…
Suresh Gautam
  • 816
  • 8
  • 21
0
votes
1 answer

How to ignore delimiters inside quoted strings when importing a csv file with RSQLite?

I want to import a csv file that has a similar structure with the example below: var1;var2;var3 "a";1;"Some text" "b";0;"More text" "c;0;"Delimiter in ; middle of the text" Traditional parsers such as the one used by data.table::fread deal with…
Nícolas Pinto
  • 363
  • 2
  • 14
0
votes
1 answer

dbExistsTable() in R odbc says table exists when it shouldn't

Issue and Expected Result Link to Github Issue I am trying to drop a table in SQL Server 2017. When I inspect the database, it appears as if the table has disappeared. However, according to odbc, the table still exists. In my example, I am writing a…
drizzle123
  • 517
  • 5
  • 18
0
votes
1 answer

import for package variable definition

Is the following a correct way to use the roxygen @import directive when defining package variables? I suspect not, because trying to use the db function yields Error in connection_quote_identifier(conn@ptr, x) : Invalid connection. The connection…
Ian
  • 1,062
  • 1
  • 9
  • 21
0
votes
0 answers

Change table space from default with dbWriteTable in R

I'm trying to write to a table in DB2 using overwrite = TRUE with dbWriteTable() from the DBI package in R. I would like to specify the table space I am writing to rather than writing to the default space (USERSPACE1). I also would rather not create…
jon
  • 370
  • 1
  • 11
0
votes
0 answers

Appending records to a table in SQL Server from R

I am able to establish a connection to a Microsoft SQL Server and am also able to read tables. pool <- pool::dbPool(drv=odbc::odbc(), dsn="MYDSN", uid = "MYUID", pwd = "XXXXX") con <-…
Dhiraj
  • 1,650
  • 1
  • 18
  • 44
0
votes
1 answer

creating and inserting data from a R dataframe to Cloudera Impala with DBI package

I have created a couples of tables (data frames) in R that I need to upload to Cloudera Impala, I am using DBI package to connect with Impala. So I have for example: df<-data.frame(x) How do I insert df into Impala as a table? I have seen that this…
L.Gut
  • 1
  • 1
0
votes
1 answer

R : Difference in joins : Dplyr and sql

I would like to understand the difference between dplyr joins and sql joins. I have an open connection to an oracle database in R: con <- dbConnect(odbc::odbc(), …) The 1st request : dbGetQuery(con, "select * from result join test on…
Felipe
  • 719
  • 8
  • 20
0
votes
0 answers

Connecting to a database within a Redshift database via RStudio using `r-dbi` and `dbplyr`

Am relatively new to both databases and Redshift, and I'd like to use the Connections within R Studio to be able to query a set of databases to extract subsets of data. Specific Question: after connecting to the correct database in Redshift, how…
daRknight
  • 253
  • 3
  • 17
0
votes
0 answers

dbReadTable() says "invalid object name" but can select from with dbGetQuery

The following code fails at the second to last line but retrieves the correct result set on the last line: library(RJDBC) drv <- JDBC("com.microsoft.sqlserver.jdbc.SQLServerDriver","D:/sqljdbc4.jar") conn <- dbConnect(drv, …
A Hood
  • 51
  • 5
0
votes
1 answer

Adding Numbers to Postgres Database using RPostgres

I have enjoyed working with the RPostgres (R package) for a while now and it works very well on most things. One thing I recently ran into is it seems to automatically convert numeric types in R into REAL in my Postgres database. The REAL type seems…
Trevor Nederlof
  • 2,546
  • 5
  • 22
  • 40
0
votes
1 answer

Add a variable to a table in MonetDBLite

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 =…
Geet
  • 2,515
  • 2
  • 19
  • 42
0
votes
0 answers

type 5 error while using MonetDBLite or readr

I am trying to write 80GB csv file onto the MonetDB using MonetDBLite using the following code. mtcars[1:12,] %>% write_csv("mtcars1_12.csv") mtcars[13:32,] %>% write_csv("mtcars13_32.csv") setwd("path") # please use your folder path dbdir <-…
Geet
  • 2,515
  • 2
  • 19
  • 42
1 2 3
…
18 19