Stack Overflow
Stack Overflow

Questions tagged [rodbc]

RODBC provides an ODBC database interface for R. The CRAN package provides access to any Open DataBase Connectivity (ODBC) accessible database.

RODBC is an package providing an interface to database sources supporting an ODBC interface. This is very widely available, and allows the same R code to access different database systems. RODBC runs on /, and , and almost all database systems provide support for . The supported systems include , , , , and .

Repositories

Vignettes

Other resources

Related tags

802 questions
6
votes
3 answers

Why won't RODBC upload a dataframe to SQL Server?

library(RODBC) con <- odbcDriverConnect("driver=SQL Server; server=name") df <- data.frame(a=1:10, b=10:1, c=11:20) Trying to upload the dataframe: sqlSave(con, df, tablename='[MyDatabase].[MySchema].[MyTable]', rownames=F) >Error in…
jenswirf
  • 7,087
  • 11
  • 45
  • 65
5
votes
1 answer

annoying "feature" (or bugs?) for RODBC

RODBC is the main library in R to import data from a database into R. RODBC seems to have the ability of "guess" the datatype of the column which I find it particularly annoying. I have uploaded a file test.xls here, or you may create a xls file…
lokheart
  • 23,743
  • 39
  • 98
  • 169
5
votes
2 answers

Error when trying to connect R to PostgreSQL using RODBC

I am trying to connect to R using RODBC. However, when I run ch <- odbcConnect("rails_dev", uid="skline", pwd="d0gsleep") I get this error: In odbcDriverConnect("DSN=rails_dev;UID=skline;PWD=d0gsleep") : [RODBC] ERROR: state IM002, code 0,…
Spencer
  • 21,348
  • 34
  • 85
  • 121
5
votes
3 answers

First argument is not an open RODBC channel

I am connecting to an Oracle database via DNS (set up the connection on the tnsnames.ora with the name "database"). I am able to succesfully run the following code: con <- DBI::dbConnect(odbc::odbc(), "database", …
Victor Galuppo
  • 187
  • 2
  • 2
  • 8
5
votes
1 answer

How to append data to a SQL Server table with IDENTITY primary key using function sqlSave() in R?

I created a table in SQL Server as follows: CREATE TABLE testPK ( ID INT NOT NULL IDENTITY (1, 1) PRIMARY KEY, NumVal NUMERIC (18, 4) ) Now I want to append data to testPK from an R program using the RODBC function sqlSave() as follows: #…
Robert
  • 2,111
  • 4
  • 18
  • 32
5
votes
1 answer

Execute multiple statements separated by semicolons in RODBC

I have a fairly complex SQL query that I am trying to run through RODBC that involves defining variables. A simplified version looks like this: DECLARE @VARX CHAR = 'X'; SELECT * FROM TABLE WHERE TYPE = @VARX; Running this code works just fine.…
crf
  • 1,810
  • 3
  • 15
  • 23
5
votes
5 answers

R ODBC - Querying Column name with spaces

I am trying to query through R OBDC. But one Column name has space on it. For example, [Account No]. I am using this code to query: esiid_ac <- sqlQuery(myconn, paste(" SELECT * FROM CustomerUsage WHERE ((CustomerUsage.Account No ='", 12345, "'))…
Sripati
  • 71
  • 2
5
votes
1 answer

Efficient way to insert data frame from R to SQL

I have a data frame with 10 million rows and 5 columns that I want to insert to an existing sql table. Note that I do not have permission to create a table, I can only insert values into an existing table. I'm currently using RODBCext query_ch <-…
user124543131234523
  • 273
  • 2
  • 12
5
votes
4 answers

How to install & locate ODBC headers sql.h and sqlext.h on a macOS

I am trying to install package RODBC via CRAN on MacBook Air with OS 10.12. I am receiving the below error: Installing package into ‘/Users/aaron.samuels/Library/R/3.3/library’ (as ‘lib’ is unspecified) Package which is only available in source…
A.Samuels
  • 51
  • 1
  • 3
5
votes
1 answer

Connecting to Microsoft SQL Server on ODBC using R in Ubuntu

I was able to get it completely working on Windows 7, but I have to move it to a Linux server. I'm running Ubuntu 16.04.1 LTS 64-bit in a VM right now for testing before I replicate the process on the server. I'm pretty sure the issue is somewhere…
user2990276
  • 307
  • 1
  • 2
  • 7
5
votes
3 answers

Unable to append to SQL Server table using sqlSave in R

I am trying to update a SQL table using sqlSave function of RODBC package in R. Data is present in a data frame. When I try to run the command: sqlSave(DBConn, dat=df, verbose=T, tablename='table', append=T) I get the following error: Query: INSERT…
user1465557
  • 329
  • 2
  • 4
  • 11
5
votes
4 answers

Dynamic "string" in R

Simple question, but cannot find the answer. Instead of: Df <- sqlQuery(ch, "SELECT * FROM tblTest WHERE Id=25") I want a more dynamic piece of code. Something like: Id <- 25 Df <- sqlQuery(ch, c("SELECT * FROM tblTest WHERE Id=", Id)) But this is…
waanders
  • 8,907
  • 22
  • 70
  • 102
5
votes
1 answer

MySQL odbc timeout from R

I'm using R to read some data from a MySQL database using the RODBC package. The data is then processed and some results are sent back to the database. The problem is that the server closes the connection after about a minute due to inactivity,…
stotastic
  • 796
  • 1
  • 5
  • 18
5
votes
5 answers

How do I connect to an SQL server database in R

I'm trying to connect to the SQL Sever database using R but not sure on the details for the query string. I normally use SQL server management studio on SQL Server 2008 and connnect using single sign on. I found the below example myconn <-…
user1605665
  • 3,771
  • 10
  • 36
  • 54
5
votes
2 answers

RODBC sqlSave column types: how determined?

I'm trying to understand how RODBC determines the column types of a newly created (Access) table? The R documentation of sqlSave is very cryptic: "types are selected by consulting arguments varTypes and typeInfo". And no examples for this arguments.…
waanders
  • 8,907
  • 22
  • 70
  • 102
1 2 3
…
53 54