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

Proper way to pass parameters to query in R DBI

In perl/python DBI APIs have a mechanism to safely interpolate in parameters to an sql query. For example in python I would do: cursor.execute("SELECT * FROM table WHERE value > ?", (5,)) Where the second parameter to the execute method is a…
cts
  • 1,790
  • 1
  • 13
  • 27
8
votes
0 answers

How to use glue_data_sql to write safe parameterized queries on an SQL server database?

The problem I want to write a wrapper around some DBI functions that allows safe execution of parameterized queries. I've found the this resource that explains how to use the glue package to insert parameters into an SQL query. However, there seem…
Willem
  • 976
  • 9
  • 24
8
votes
1 answer

dbplyr mutate character to date format in temp table

I have extracted data to a temporary table in SQL Server using DBI::dbGetQuery. Even though, in the real query (not the play query below), I select convert(date, date_value) as date_value, the dates are still stored as character. I then try to…
user1420372
  • 2,077
  • 3
  • 25
  • 42
8
votes
1 answer

DBI or odbc package for SQL server

We are moving away from RODBC which seems to have a limited error handling system (sometimes to query goes through even though there was an error, and there is no way to get the error message. we have tried all RODBC functions) However there seem to…
RockScience
  • 17,932
  • 26
  • 89
  • 125
8
votes
1 answer

Why does dbListTables give a warning message when called via a function? (R DBI)

I wrote a function using dbListTables from the DBI package, that throws a warning that I cannot understand. When I run the same code outside of a function, I don't get the warning message. For info, the database used is Microsoft SQL…
Willem
  • 976
  • 9
  • 24
7
votes
2 answers

R Oracle connect via DBI::dbDriver("Oracle") throws error

I try to do a simple connect to an Oracle database via DBI and ROracle package following instructions from R to Oracle Database Connectivity: Use ROracle for both Performance and Scalability. When I test the connection via Windows7 > ODBC Data…
Triamus
  • 2,415
  • 5
  • 27
  • 37
7
votes
1 answer

Adding column to sqlite database

I am trying to add a vector which I generated in R to a sqlite table as a new column. For this I wanted to use dplyr (I installed the most recent dev. version along with the dbplyr package according to this post here). What I tried:…
Alex
  • 4,925
  • 2
  • 32
  • 48
7
votes
3 answers

Connect to MSSQL using DBI

I can not connect to MSSQL using DBI package. I am trying the way shown in package itself m <- dbDriver("RODBC") # error Error: could not find function "RODBC" # open the connection using user, passsword, etc., as # specified in the…
indra_patil
  • 283
  • 1
  • 4
  • 11
7
votes
1 answer

R - how to react to database inserts/updates/deletes?

I'm reading in data from an SQLite database table into a data.frame with R's DBI. Often (as often as every 5 secs), new records get added into the database table externally, or existing ones updated/deleted, at which point I need to propagate these…
mchen
  • 9,808
  • 17
  • 72
  • 125
6
votes
2 answers

ODBC/DBI in R will not write to a table with a non-default schema in R

The Issue When trying to write to a table with a non-default schema, dbWriteTable in the package DBI, writes to default.non-default.tablename rather than writing to non-default.tablename. I know that non-default.tablename exists because it's showing…
user111417
  • 143
  • 1
  • 9
6
votes
1 answer

How to write to table with date column with DBI

I'm trying to append a dataframe to a sql server table using: DBI::dbWriteTable(con_poc, "DEP_EVENTS", data_up, overwrite=FALSE, append = TRUE, verbose = TRUE, rownames = FALSE) But I am getting an error on a column that is 'date' type in the…
tonyk
  • 348
  • 5
  • 22
6
votes
2 answers

How to insert reactive input values from a shiny app into a MySQL database?

I created an online experiment with the shiny package for R. Let's say I have 3 reactive values called "toss", "decision" and "rating". Additionally, I launched a MySQL database on Amazon web service RDS. The version is MySQL 5.6.22. I…
schindst
  • 71
  • 1
  • 6
5
votes
1 answer

Disconnect DBI / RSQLite within a function in R

I'm building a package for internal usage and attempting to abstract away all possible database interaction from the users. I need to connect to the database and disconnect from the database within the function (I think). However, the disconnect…
Scott Stoltzman
  • 363
  • 1
  • 15
5
votes
1 answer

Database calculations with dbplyr

I have very simple problem that produces error. Example will clear this one. library(odbc) library(DBI) library(dplyr) library(dbplyr) con <- dbConnect(odbc(), "myDSN") tbl_test <- tibble(ID = c("A", "A", "A", "B", "B", "B"), …
Hakki
  • 1,440
  • 12
  • 26
5
votes
1 answer

Retrieve output from stored procedure result in R with odbc/DBI

I'm trying to call a stored procedure and retrieve the output. I'm calling SQL Server using the DBI-compliant odbc package. How can I use this package? The following code returns the number of rows affected. sql <- "EXEC schema.prc_person…
wibeasley
  • 5,000
  • 3
  • 34
  • 62
1
2
3
…
18 19