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
0
votes
0 answers

Working in R - dbReadTable and dbExistsTable claims a database table written using dbWriteTable exists, but table does not exist in database

Goal: be able to conduct SQL queries on a data frame in R. Approach used: using dbWriteTable to write the table to the database that I would then be able to query on using SQL and join to other tables existing in the DB. Issue: Seems to execute…
Emily Reed
  • 65
  • 10
0
votes
0 answers

R - anyway to speed up dbWriteTable?

I am working with a dataframe c. 700k rows and 400 columns which I'm uploading to Microsoft SQL Server. I'm currently using the code: dbWriteTable(conn = con, name = "table_test", value = x, …
user33484
  • 740
  • 2
  • 9
  • 36
0
votes
0 answers

SQL Server uses wrong data types with DBI::dbSendStatement() and DBI::dbBind()

We have a table [adj].[Assortment Snapshot] in our SQL Server database with the follwing data types: [Customer UUID] [uniqueidentifier] NOT NULL, [Branch Number] [int] NOT NULL, [Article Number] [int] NOT NULL, [Valid On] [date] NOT NULL, [Comment]…
scn
  • 11
  • 2
0
votes
1 answer

Is there a way to define data format within MS Access SQL CREATE TABLE Statement?

I am using the DBI library in R to establish a connection to an MS Access DB (in this example called "db") and then using the DBI dbGetQuery() function to pass MS Access SQL query seen below. If I run this example code segment without attempting to…
Lauren Hostert
  • 63
  • 3
  • 8
0
votes
0 answers

UPDATE using DBI in R

I have a data frame that I need to update using R DBI: c= conn() dummy <- reactiveValues(dummy_react = DBI::dbGetQuery(c, 'select * from dbname.dummy_df')) observe({ dummy_df <- reactive({dummy$dummy_react}) newSupplier = "New…
borderline_coder
  • 183
  • 1
  • 13
0
votes
1 answer

DBI::dbWriteTable error when writing to BigQuery -- trying to coerce string to int

In an AI notebook, I have the following: %%R tempdf %>% summary() %>% print() DBI::dbWriteTable( conn=clinvar_conn, name=table_name, value=tempdf, overwrite=T ) Giving CHROM POS ID …
abalter
  • 9,663
  • 17
  • 90
  • 145
0
votes
0 answers

Is a database name needed with an Amazon RDS url?

Sorry I can't give an MRE. This is from a programming task where I'm supposed to process data from a database. However, I'm having trouble accessing the database altogether, and I am allowed to use code fora in a limited way. I was given a URL of…
abalter
  • 9,663
  • 17
  • 90
  • 145
0
votes
1 answer

Can't get full result set in R DBI

That database is Oracle 12. I'm using R DBI with RODBC. I have a complex query with multiple sub queries and WITH statements. The result set is only about 3000 rows, but it only returns 800 in R, and exactly 800 rows every time. I've tried…
Odj fourth
  • 649
  • 1
  • 9
  • 16
0
votes
0 answers

odbc dbConnect Can't open lib - file not found, but file is there?

This worked perfectly for months. today I updated R and DBI library to 3.6.3 and now this.. library(DBI) con <- dbConnect(odbc::odbc(), driver = Sys.getenv("DRIVER_PATH"), server = "aus_net", database = "ice", …
stevec
  • 41,291
  • 27
  • 223
  • 311
0
votes
1 answer

Permission denied to write a table with dbWriteTable in RPostgreSQL

I've been trying to solve a problem with the RPostgreSQL package for quite some time. My code: path_ene <- "C:/Users/omen03/ENE" nene <- "ene_2010_02_efm.csv" drv <- dbDriver("PostgreSQL") con = dbConnect(drv, user="postgres",…
Héctor Garrido
  • 185
  • 1
  • 14
0
votes
0 answers

Pass .sql file into DBI::dbGetQuery with comments as the first line into R / RStudio

I am trying to pass a .sql file into DBI::dbGetQuery, but I run into problems whenever there is comment in the first line. For example: runs_fine.sql SELECT * FROM mytable does_not_work.sql -- Some comment SELECT * FROM mytable Then in…
JasonAizkalns
  • 20,243
  • 8
  • 57
  • 116
0
votes
1 answer

Add leading zeros with R to a SQL database

To add leading zeros to a string in R I'll use the formatC() function: x <- c(1, 10, 100, 10000, 100000, 1000000) formatC(x, width = 8, format = "d", flag = "0") #> [1] "00000001" "00000010" "00000100" "00010000" "00100000" "01000000" When I try…
Display name
  • 4,153
  • 5
  • 27
  • 75
0
votes
2 answers

R, SQL, and existing objects not existing

DBI::dbListFields(con, "ibin_acq") #> [1] "MANDT" "IN_RECNO" "VALFR" "VALTO" #> [5] "AENNR" "DELFLAG" "IN_STANCE" "MLANG" #> [9] "SORTF" "OBJNR" "IBASE" …
Display name
  • 4,153
  • 5
  • 27
  • 75
0
votes
1 answer

Connecting to Teradata with R through DBI and ODBC

I connect to Teradata using the Teradata SQL Assistant. The connection parameters consist of a server address and a driver (server info changed for privacy reasons), as shown below: Name: my_teradata_connection Teradata Server Info:…
Display name
  • 4,153
  • 5
  • 27
  • 75
0
votes
1 answer

R's dbConnect fails with "DSN error" when executed by script called from batch file

I have 64 bit R, version 3.6.0, installed on my windows 10 machine. I use the following code to establish a connection to the local MySQL database on my machine library(DBI) con <- dbConnect( odbc::odbc(),"my_dsn") where my_dsn is the 64-bit System…
Rookatu
  • 1,487
  • 3
  • 21
  • 50
1 2 3
…
18 19