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

Creating an SQLite DB in R from an CSV file: why is the DB file 0KB and contains no tables?

I have a 9GB .csv file and would like to convert it to an sqlite data base. I have followed https://datacarpentry.org/R-ecology-lesson/05-r-and-databases.html#Connecting_to_databases and it works on my local machine but on a server it says the…
HCAI
  • 2,213
  • 8
  • 33
  • 65
3
votes
0 answers

Querying SQL database via DBI::dbGetQuery in RStudio takes 10times longer than in RGui

Queering a SQL database using the following: conn <- DBI::dbConnect(odbc::odbc(), "NAME_OF_SQL_SERVER", database = "NAME_OF_DB") system.time(x <- DBI::dbGetQuery(conn, "Exemplary_Data_ByDateRange @dtFrom = '2016/01/01' , @dtTo =…
Spinorbundle
  • 131
  • 2
3
votes
2 answers

How do I find the schema of a table in an ODBC connection by name?

I'm using the odbc package to connect to a MS SQL Server con <- dbConnect(odbc::odbc(), Driver = "ODBC Driver 13 for SQL Server", Server = "server", Database = "database", UID …
Fino
  • 1,774
  • 11
  • 21
3
votes
1 answer

Parameterized Queries Not Working with SQL Server 2017

Issue description and expected result I am trying out parameterized queries on my SQL Server following the documentation provided here: https://cran.r-project.org/web/packages/DBI/DBI.pdf. However, it is not working for me. Specifically when I…
drizzle123
  • 517
  • 5
  • 18
3
votes
4 answers

SQL function not working when trying to write table to non-default schema

I am trying to write a table to a non-default schema in SQL Server 2017. I am using RStudio's documentation as to what the best practice is for doing this: https://db.rstudio.com/best-practices/schema/#write-non-temporary-tables. However, when I…
drizzle123
  • 517
  • 5
  • 18
3
votes
0 answers

Issue Writing to Non-Default Schema when Table Does Not Have "_" In Name

Issue Description and Expected Result When I try to use dbWriteTable() to write to a non-default schema, it fails when using a table name without the "_" character. The error message is: Error in connection_sql_tables(conn@ptr, catalog_name = if…
drizzle123
  • 517
  • 5
  • 18
3
votes
0 answers

How to spread tbl_dbi and tbl_sql data without downloading to local memory

I am working with large datasets and tidyr's spread usually gives me error messages suggesting failure to obtain memoryto perform the operation. Therefore, I have been exploring dbplyr. However, as it says here, and also shown below,…
Krantz
  • 1,424
  • 1
  • 12
  • 31
3
votes
0 answers

Can RPostgreSQL run parameterized SQL statements with named parameters?

I have tried several times to run parameterized SQL statements using the RPostgreSQL package in R. I was able to run parameterized statements using positional arguments, however, I have had no luck for named arguments. Is this possible in…
ichbinallen
  • 1,019
  • 12
  • 18
3
votes
0 answers

DBI::dbWriteTable doesn't append beyond specific number of rows and throws exception on nvarchar(max)

Currently working with Professional Rstudio drivers I have set all varchar to max in Sqlserver table as well but it didnt help. diag_test <- diag[1:1025] system.time({ DBI::dbWriteTable(con, "urban_prodbi_diag", diag_test, append = TRUE) }) Error…
nsDataSci
  • 189
  • 2
  • 10
3
votes
3 answers

dbReadTable error in R: invalid object name

I've been scouring stackoverflow and google for an hour trying to figure out why my table won't show up. So far solutions that have worked for others, don't work for me. I'm connecting to a database like this: library(DBI) library(dplyr) con <-…
Nova
  • 5,423
  • 2
  • 42
  • 62
3
votes
1 answer

Create variable in database using ALTER TABLE and UPDATE

I have a 50GB SQLite database file and I want to calculate and add new variables. Can you leverage Moody_Mudskipper's function or something using ALTER TABLE and UPDATE to create the variable instead of an entire table? library(dbplyr) …
Geet
  • 2,515
  • 2
  • 19
  • 42
3
votes
3 answers

R pass parameter to SQL IN clause using dbBind (possible without glue package?)

Just wondering if it is possible to pass parameters to the SQL query IN clause using DBI? Have tried the following (and many variations, including unnamed parameters) con <- dbConnect(RSQLite::SQLite(), ":memory:") dbWriteTable(con, "iris",…
user1420372
  • 2,077
  • 3
  • 25
  • 42
3
votes
0 answers

Cannot connect to Sybase database, additional arguments necessary?

I'm trying to connect to a Sybase database (ASE) using the DBI and odbc packages. Using these packages the connection fails. However, when using the same dsn and the RODBC package, the connection does work. What are possible causes of the failure…
Willem
  • 976
  • 9
  • 24
3
votes
0 answers

dbWriteTable with different column names between data.frame and database

Version 0.4 of the dbWriteTable package allowed to copy a data.frame to a database table even if the column names of the two objects did not match. Current version (0.6) instead throws an error in such a situation. Is there a way to bypass the…
msaltieri
  • 162
  • 1
  • 9
3
votes
0 answers

In R how can I use multiple prepared statements with DBI + RSQLite simultaneously?

Suppose you're trying to do a typical insert-or-update loop with RSQLite. I would expect the following to work: library(DBI) testdb <- dbConnect(RSQLite::SQLite(), "test.sqlite") dbExecute(testdb, "CREATE TABLE spray_count (spray TEXT, count…
Tavin
  • 390
  • 2
  • 13
1 2 3
…
18 19