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

What is the purpose of dbClearResult() in DBI/RMySQL/RMariaDB?

SQL novice here. In the Hello World section of the RMySQL github page, there is the following - # You can fetch all results: res <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = 4") dbFetch(res) dbClearResult(res) I don't understand the…
Ben
  • 20,038
  • 30
  • 112
  • 189
2
votes
2 answers

Appending new data to sqlite db in R

I have created a table in a sqlite3 database from R using the following code:- con <- DBI::dbConnect(drv = RSQLite::SQLite(), dbname="data/compfleet.db") s<- sprintf("create table %s(%s, primary key(%s))", "PositionList", …
Dhiraj
  • 1,650
  • 1
  • 18
  • 44
2
votes
0 answers

Does DBI dbWriteTable have a row limit 1457?

Using R and troubleshooting an error, I discovered an interesting limit [that I seemingly have] with DBI::dbWriteTable. I am trying to write a large data my.df frame to a sql server. dim(my.df) = > 19391 18 dbWriteTable(conn = connection, name =…
Dave
  • 410
  • 6
  • 13
2
votes
0 answers

more info for R DBI::dbSendQuery?

Here is a line of code that used to work: res <- DBI::dbSendQuery(con, "SELECT 1") Now it says: Error in .verify.JDBC.result(r, "Unable to retrieve JDBC result set for ", : Unable to retrieve JDBC result set for SELECT 1 (Error executing…
dfrankow
  • 20,191
  • 41
  • 152
  • 214
2
votes
1 answer

checking class of R-DBI connection object

I'm rewriting a function that optionally accepts a connection object. The parameter should be verified that it's a valid connection/channel. How do I do this robustly with the DBI package? (Specifically, I'm using odbc package.) I'd like…
wibeasley
  • 5,000
  • 3
  • 34
  • 62
2
votes
2 answers

RSQLite Error in loadNamespace(i, c(lib.loc, .libPaths()), versionCheck = vI[[i]]) : namespace ‘DBI’ 0.4-1 is being loaded, but >= 0.8 is required

When I load sqldf package, I got error message below. How can I fix this? install.packages("sqldf", dep = TRUE) library(sqldf) RSQLite Error in loadNamespace(i, c(lib.loc, .libPaths()), versionCheck = vI[[i]]) : namespace ‘DBI’ 0.4-1 is being…
melik
  • 1,268
  • 3
  • 21
  • 42
2
votes
0 answers

dbGetQuery throttles when the limit is removed for a very large table in a hive database

All, I'm trying to use the packages RJDBC , rJava and DBI in R to extract the data from a big hive table sitting in a mapr hive/hadoop cluster on a remote linux machine. I don't have any issues in connecting to the hive cluster. The table1 I'm…
ML_Passion
  • 1,031
  • 3
  • 15
  • 33
2
votes
2 answers

DBI/Spark: how to store the result in a Spark Dataframe?

I am using sparklyr to run some analysis, but I am interested also in writing raw SQL queries using DBI. I am able to run the following query query <- "SELECT col1, FROM mydata WHERE some_condition" dataframe <- dbGetQuery(spark_connection,…
ℕʘʘḆḽḘ
  • 18,566
  • 34
  • 128
  • 235
2
votes
1 answer

SparklyR wrapper for spark SQL: sqlContext.sql

I am trying to write a wrapper for SQL function for SparklyR. I have created the following function: sqlfunction <- function(sc, block) { spark_context(sc) %>% invoke("sqlContext.sql", block) } Then I call it using the following: newsqlData…
Levi Brackman
  • 325
  • 2
  • 17
2
votes
1 answer

How to check if the connection to MySql through RMySql persists or not?

I have connected to a mysql database through the RMySQL package using this statement: con<-dbConnect(drv=RMySQL::MySQL(max.con=1,fetch.default.rec=500),host="host",dbname="dbname",password="psswd",user="user")) So far no issues. When I…
tushaR
  • 3,083
  • 1
  • 20
  • 33
2
votes
1 answer

can't write timestamps in PostgreSQL using RPostgres dbWriteTable

I'm trying to write an R tibble object (or data.frame) into a Postgres database using the RPostgres package. Following the documentation, I'm trying to use the dbWriteTable function. However, I realized that there's no specification of field.types…
spsaaibi
  • 452
  • 4
  • 13
2
votes
2 answers

ROracle: dbGetQuery works but dbListTables and other functions do not

I installed ROracle (following the directions in the package) and successfully connected to our Oracle database. I can run queries, using dbGetQuery, and get results just fine, e.g.: > dbGetQuery(con, "select count(*) from table_name") COUNT(*) 1 …
Victor Kostyuk
  • 621
  • 5
  • 16
2
votes
2 answers

character encoding, dplyr with database (postgresql)

I've read the threads and package updates for encoding issues with Shiny, but I have a (difficult-to-reproduce example) database-driven Shiny app which is fumbling some special characters. In my postgresql database I see correctly my Swedish river,…
Jeff
  • 78
  • 1
  • 8
2
votes
1 answer

How to get all mysql databases into r

I want to get a few hundred separate databases into r from mysql. I can get them in separately but I do not know how to make a loop/function/apply to get all of them in at once. Here is how I get them in separately. library(RMySQL) mydb =…
user6452857
  • 117
  • 2
  • 9
2
votes
0 answers

Understanding valgrind output and solving errors R

I am running an Rscipt which calculates economic parameters for around 1000 cities in a loop. The data is stored in MySQL which I am accessing using RMySQL and DBI packages. However I keep getting segmentation fault errors. I have no idea why this…
kum
  • 21
  • 1
1 2 3
…
18 19