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

How to use dynamic values while executing SQL scripts in R

My R workflow now involves dealing with a lot of queries (RPostgreSQL library). I really want to make code easy to maintain and manage in the future. I started loading large queries from separate .SQL files (this helped) and it worked great. Then I…
Pranasas
  • 597
  • 6
  • 22
3
votes
1 answer

How can I actually see the raw query generated by DBI::dbWriteTable?

I am wondering if there is a way to retrieve the SQL query which dbWriteTable sends to the DBMS. For example, for the following example. Is there a way to get the query? library(DBI) con <- dbConnect(RSQLite::SQLite(), ":memory:") dbWriteTable(con,…
Mark Heckmann
  • 10,943
  • 4
  • 56
  • 88
3
votes
1 answer

Datetime values chopped in RSQLite depending on null values

I am confronted with a weird phenomenom when extracting data out of a SQLite 3 database using the RSQLite 1.0.0 package. All except the year of the datetime value gets truncated! See an example: Following I am extracting a DATETIME…
Alexander Meyer
  • 31
  • 2
3
votes
1 answer

How to pass client_protocol to JDBC driver in R?

I am trying to connect with the HiveServer2 with the use of dplyr.spark.hive package but I am occuring an error that I can not pass a username to the dbConnect function and probably this is the reason I receive an error about NULL…
Marcin
  • 7,834
  • 8
  • 52
  • 99
3
votes
0 answers

?tcpKeepAlive=true and RPostgres

The suggest url for my redshift server has a ?tcpKeepAlive=true appended to it, so that it looks something like jdbc:postgresql://myserver:myport/dbname?tcpKeepAlive=true Now, the RPostgres::dbConnect function has signature dbConnect(dbname = NULL,…
StevieP
  • 1,569
  • 12
  • 23
3
votes
1 answer

Leave connection to database open for the session or connect to it when it's useful

I'm working in R with a MySQL database to which I connect with RMySQL (and DBI). I have a function which read SQL table (and import them as R objects) on the fly, when the user need them. He can read table often or don't send a query to the database…
Julien Navarre
  • 7,653
  • 3
  • 42
  • 69
3
votes
1 answer

Error on dbSendQuery with RPostgreSQL and RJDBC on CentOS 6.5 and not Windows 7

I've run into a strange problem that occurs on my CentOS 6.5 box hosted on AWS that does not occur on my Windows machine. When sending a large dbSendQuery (string with 15,000+ bytes/characters), I get error messages. With smaller queries, it runs…
Ken Yeoh
  • 876
  • 6
  • 11
3
votes
0 answers

Error in ls(envir = envir, all.names = private) : invalid 'envir' argument when using dbDisconnect() after dbCommit()

I am trying to update the oracle database through R by using this code: cxn <- makeCxn(config) x <- try(res <- dbSendUpdate(cxn, query)) dbCommit(cxn) dbDisconnect(cxn) Then I got the error: Error in ls(envir = envir, all.names =…
user3516947
  • 33
  • 4
3
votes
1 answer

How to write binary data into SQLite with R DBI's dbWriteTable()?

For instance, how to execute the equivalent following SQL (which inserts into a BINARY(16) field) INSERT INTO Table1 (MD5) VALUES (X'6717f2823d3202449201145073ab871A'),(X'6717f2823d3202449301145073ab371A') using dbWriteTable()?…
mchen
  • 9,808
  • 17
  • 72
  • 125
3
votes
1 answer

Sending a query with a single transaction

I'm using the DBI package to send queries to a MySQL server. I'd like to assure that these queries are sent as a single transaction in order to avoid table lock. I use the dbSendQuery function to send queries: df <- fetch(dbSendQuery(connection, …
Statwonk
  • 713
  • 1
  • 8
  • 21
2
votes
0 answers

R - dbGetQuery and preserve comments

It seems when I pass a comment to dbGetQuery() it doesn't send the comment to our database. Is there a way to preserve comments? I'm trying track which apps on Posit Connect are sending which queries and trying to do something like…
yake84
  • 3,004
  • 2
  • 19
  • 35
2
votes
0 answers

Defining CTE (Common Table Expressions) in R?

I have this table on a server - I am querying it with R: library(dplyr) library(DBI) con <- dbConnect(RSQLite::SQLite(), ":memory:") dbWriteTable(con, "iris", iris) I was able to run this query: DBI::dbGetQuery(con, " select *, row_number() …
stats_noob
  • 5,401
  • 4
  • 27
  • 83
2
votes
1 answer

Issue with `DBI::dbGetQuery` run from a Shiny app

I have a Shiny app with which the user selects a table in a SQL database and then selects some columns of this table. Then the app runs this function to get the table: selectAllGetQuery <- function(conn, columns, table){ columns <-…
Stéphane Laurent
  • 75,186
  • 15
  • 119
  • 225
2
votes
1 answer

R studio windows and unix connection to Microsoft SQL Server

I am able to make connection from my laptop (window's) to the database using the below details conn2 <- DBI::dbConnect(odbc::odbc(), Driver = "ODBC Driver 17 for SQL Server", Server = "XXXX" , …
Dexter1611
  • 492
  • 1
  • 4
  • 15
2
votes
0 answers

Using glue_sql and DBI::dbExecute can I update set by joining a sql table to a local df?

I would like to update a table in a database within r: library(tidyverse) library(dbplyr) library(DBI) library(glue) # create a table with empty field to be updated later # id field is now 'id' my_mtcars <- mtcars %>% rownames_to_column() %>% …
Doug Fir
  • 19,971
  • 47
  • 169
  • 299
1 2 3
…
18 19