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

R- create temporary table in sql server from R data frame

I know I can create a temporary table in SQL from R with, for example: require(RODBC) X<- odbcDriverConnect('driver={SQL Server}; server=s001000;database=X1;trusted_connection=true') sqlQuery(X, "create table #temptable (test int)"…
GabyLP
  • 3,649
  • 7
  • 45
  • 66
9
votes
6 answers

Can I gracefully include formatted SQL strings in an R script?

I'm working in an R script that uses a long SQL string, and I would like to keep the query relatively free of other markup so as to allow copying and pasting between editors and applications. I'd also like the ability to split the query across lines…
MW Frost
  • 980
  • 1
  • 11
  • 23
9
votes
3 answers

RODBC: sqlUpdate() doesn't recognise index column

My database table looks approximately like this: +-----+-------+--------------------+-----------+----------+ | ID1 | ID2 | FilePath1 | FilePath2 | Status | +-----+-------+--------------------+-----------+----------+ | 1 | Test1 |…
AnjaM
  • 2,941
  • 8
  • 39
  • 62
8
votes
1 answer

R RODBC putting list of numbers into an IN() statement

I've looked at the 'Pass R variable to RODBC's sqlQuery with multiple entries? ' already but can't seem to get it to work. I'm trying to do an sqlQuery() from R on a SQL Server 2008 R2 db. I'm trying to get a sample from a large db based on row…
screechOwl
  • 27,310
  • 61
  • 158
  • 267
8
votes
1 answer

R and RStudio not detecting installed PostgreSQL (or any other) ODBC drivers

I am trying to connect to a PostgreSQL database using the R ODBC drivers in RStudio. For some reason, the drivers are not being detected by R: $ Rscript -e 'odbc::odbcListDrivers()' [1] name attribute value <0 rows> (or 0-length…
Floris
  • 637
  • 1
  • 8
  • 17
8
votes
2 answers

How to specify field data type when reading from a SQL database

I have a SQL table called "Customers". In it is a CustomerNumber field with values like "0001234567", i.e. they are made up of numbers only, but some include leading 0s. So, when I try to run something like sqlFetch(channel=myconn,…
Ben
  • 20,038
  • 30
  • 112
  • 189
8
votes
4 answers

sqlSave error: table not found

I'm trying to save a R dataframe back to a sql database with the following code: channel <- odbcConnect("db") sqlSave(db, new_data, '[mydb].[dbo].mytable', fast=T, rownames=F, append=TRUE) However, this returns the error "table not found on…
Geoffrey
  • 196
  • 1
  • 12
8
votes
2 answers

varType usage in RODBC

I am writing to from an ODBC to a SQL Server table via the RODBC package, specifically the function sqlSave. It seems that the default var types is charvar(255) for this function. I tried to use the argument of varTypes that is listed within the…
Edgar S Martinez
  • 91
  • 1
  • 5
8
votes
6 answers

MS-SQL Bulk Insert with RODBC

Is it possible to perform a bulk insert into an MS-SQL Server (2000, 2005, 2008) using the RODBC package? I know that I can do this using freebcp, but I'm curious if the RODBC package implements this portion of the Microsoft SQL API and if not, how…
ephpostfacto
  • 303
  • 1
  • 2
  • 7
7
votes
4 answers

rodbc character encoding error with PostgreSQL

I'm getting a new error which I've never gotten before when connecting from R to a GreenPlum PostgreSQL database using RODBC. I've gotten the error using both EMACS/ESS and RStudio, and the RODBC call has worked as is in the past. library(RODBC) gp…
wahalulu
  • 1,447
  • 2
  • 17
  • 23
7
votes
4 answers

Pass R Vector to Sql query

I'm using RODBC package to access my sql database in R. I haven't been able to find any useful information on how to pass a vector from R to sql as a vector. id <- ('00003', '00100') query <- sqlQuery(channel = channel, query = "select * …
Jeremy R. Johnson
  • 285
  • 4
  • 13
7
votes
1 answer

RODBC: chars and numerics converted aggressively (with/without as.is)

Related to https://stackoverflow.com/a/33284035/3358272, I'm finding inconsistent behavior with pulling data from SQL Server (2014). library(RODBC) sqlQuery(.conn, "CREATE TABLE r2test ( [mychar] [NVARCHAR](16), [mynum] [FLOAT])") #…
r2evans
  • 141,215
  • 6
  • 77
  • 149
7
votes
2 answers

odbcConnectExcel function from RODBC package for R not found on Ubuntu

Installing the RODBC package on Ubuntu is a bit of a kludge. First I learned to install the following: $ sudo apt-get install r-cran-rodbc That wasn't good enough as the package was still looking for header files. I solved this issue by: $ sudo…
Milktrader
  • 9,278
  • 12
  • 51
  • 69
7
votes
4 answers

Querying Oracle DB from Revolution R using RODBC

RODBC error in Revolution R 64bit on winxp64 bit connected to Oracle using a 64bit ODBC driver thru a DSN library(RODBC) db <- odbcConnect("oraclemiso",uid="epicedf",pwd="…") rslts = sqlQuery(db, "select count(*) from FTRAuction") Error in…
Arun
  • 447
  • 1
  • 5
  • 12
7
votes
4 answers

Writing Lengthy SQL queries in R

I am researching how to read in data from a server directly to a data frame in R. In the past I have written SQL queries that were over 50 lines long (with all the selects and joins). Any advice on how to write long queries in R? Is there some way…
JMonroe
  • 107
  • 1
  • 5
1 2
3
…
53 54