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

Missing letters of database objects being returned in DBI SQL Server ODBC connection

Unfortunately, I will not be able to create a good repro for this issue without sharing confidential creds to the database I am having issues with. Hopefully I have enough information below to flag any obvious problems that ODBC experts will…
joemienko
  • 2,220
  • 18
  • 27
2
votes
1 answer

How to use dbWriteTable on a connection with no default Database

I've seen many posts on SO and the DBI Github regarding trouble using DBI::dbWriteTable (i.e. [1], [2]). These mostly have to do with the use of non-default schemas or whatnot. That's not my case. I have a server running SQL Server 2014. This server…
Wasabi
  • 2,879
  • 3
  • 26
  • 48
2
votes
0 answers

How to write an R dataframe to an Access .mdb if supports.transactions=FALSE

I would like to save an R dataframe as a new table in an existing .mdb available here. I am getting an error message (below) and I suspect it has to do with .. ..$ supports.transactions: logi FALSE. I know I can save the dataframe as a .csv and…
BonnieM
  • 191
  • 1
  • 13
2
votes
2 answers

parameterized query with long string

I have a parametrized SQL query that I want to execute from (local) R on Exasol database as described here: https://db.rstudio.com/best-practices/run-queries-safely/#parameterized-queries. with tab as (select t.*, position(value in…
tomaz
  • 493
  • 4
  • 13
2
votes
1 answer

How to insert data into a table such that possible extra columns in data get added to the parent table?

I'm trying to insert daily imported data into a SQL Server (2017) table. While most of the time the imported data has a fixed amount of columns, sometimes the client wants to add a new column to the data-to-be-imported. I'm seeking for a solution…
topiaa
  • 21
  • 3
2
votes
0 answers

How to fix dbWriteTable "Error: Can't unquote dbo.test_cars" using Impala connection on backend?

I'm trying to write a table in one my databases using dbWriteTable but have been running into a variety of problems. It may be due to the schema or the backend connection to Impala I'm not entirely sure. I've tried several variations of the…
David Haycraft
  • 21
  • 2
2
votes
1 answer

Print sql statement craeted by DBI::dbBind

I want to print the sql syntax created with DBI::dbBind while creating safe parametrized query: conn <- #create connection stmt <- "select * from dbo.mytable where mycolumn = ?" params = list("myvalue") query <- DBI::dbSendQuery(conn,…
stakowerflol
  • 979
  • 1
  • 11
  • 20
2
votes
1 answer

Appending new data to a local Access data base file with r after a successful connection

So I am currently working with a connecting to an Access database. I am able to get connected to the Access DB which is located on my local system. This is actually connected to a SharePoint list. I would love to automate the process handling this…
CrayCrayTown
  • 61
  • 7
2
votes
1 answer

Package installation requires newer version of dependency

While installing mapview I get the error: namespace ‘DBI’ 0.6-1 is being loaded, but >= 0.8 is required > install.packages("mapview") > ... > Error: package or namespace load failed for ‘sf’ in loadNamespace(j <- i[[1L]], > c(lib.loc,…
Rich Pauloo
  • 7,734
  • 4
  • 37
  • 69
2
votes
1 answer

RPostgreSQL and DBI: "operator does not exist: uuid = text"

When using dbReadTable to read in database tables that uses UUID as the primary key, I get the following warning message. 1: In postgresqlExecStatement(conn, statement, ...) : RS-DBI driver warning: (unrecognized PostgreSQL field type uuid…
Christian
  • 932
  • 1
  • 7
  • 22
2
votes
1 answer

"Select * from " not working from Oracle 11g when tables are created from R using 'odbc', 'DBI' package

I have created some tables in 'Oracle Database 11g Express Edition' using two method: Method-1: From R using odbc and DBI package library(odbc) db <- odbc::dbConnect(odbc::odbc(), 'Oracle ODBC', uid = "raja", …
Raja Saha
  • 499
  • 5
  • 14
2
votes
2 answers

How to secure database username and password with R DBI?

When connecting to a database using the dbConnect function in the DBI package, what are the best practices for securing logon information such as database name, username and password? Entering logon information as a character text such…
ichbinallen
  • 1,019
  • 12
  • 18
2
votes
1 answer

RSQLite Warning: "Too many SQL variables"

I was having an issue getting the following error with RSQLite and was having trouble diagnosing the problem: Error in result_create(conn@ptr, statement) : too many SQL variables The database showed the correct, fixed number (24) of columns and…
chasemc
  • 849
  • 6
  • 12
2
votes
1 answer

SQL filter query in dplyr database case sensitive

I want to filter a SQLite database in R looking for pattern in string. The problem is the LIKE sql query appear to be case insensitive. Below a reproducible example: library(DBI) library(dplyr) tb <- dplyr::tibble(a=c(rep("aMSq",3), rep("amsq",3),…
dauby gilles
  • 57
  • 1
  • 5
2
votes
1 answer

Truncated updated string with R DBI package

I need to update a wide table on an SQL SERVER from R. So the package DBI seems to be very useful for that. The problem is that the R data.frame contains strings of more than 3000 characters and when I use the DBI dbSendQuery function, all strings…
Ben
  • 185
  • 1
  • 10
1 2 3
…
18 19