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

Connect R and Vertica using RODBC

This is my first time connecting to Vertica. I have already connected to a MySQL database sucessfully by using RODBC library. I have the database setup in vertica and I installed the windows 64-bit ODBC driver from…
Jana
  • 1,523
  • 3
  • 14
  • 17
7
votes
3 answers

update an SQL table via R sqlSave

I have a data frame in R having 3 columns, using sqlSave I can easily create a table in an SQL database: channel <- odbcConnect("JWPMICOMP") sqlSave(channel, dbdata, tablename = "ManagerNav", rownames = FALSE, append = TRUE, varTypes = c(DateNav =…
Lorenzo Rigamonti
  • 1,705
  • 8
  • 25
  • 36
7
votes
2 answers

How to manage a database connection in an R Package

I'm building an R package, the main purpose of which is to abstract away the pain of dealing with a proprietary database that requires some fairly complex SQL queries in order get data out. As such, the connection to the Microsoft SQL Server…
Ina
  • 4,400
  • 6
  • 30
  • 44
7
votes
2 answers

sqlQuery: impossible to get the ID number as Character into R?

I'm using the RODBC package inside an ORACLE DATA BASE (DB). Everything is doing very well, but I need to obtain a table from this DB and some variables as character type, not numbers. So, I made my query like this: e …
José Bustos
  • 159
  • 1
  • 7
6
votes
0 answers

Open .ODC connection in R

I have an .odc (office data connection) that connects Excel to a Web Service (MSBI, Web PowerBI). It's working fine. I open the odc file, Excel opens up and it is connected to the data source. How can I open this connection directly from R? The odc…
Dan
  • 1,711
  • 2
  • 24
  • 39
6
votes
3 answers

SQL Server Query failing when executed from R

I have the following simple SQL server code: set nocount on; if OBJECT_ID('tempdb..#A') IS NOT NULL DROP TABLE #A; set nocount on; create table #A ( obj_id int, obj_name varchar(50), obj_dt datetime); insert into #A ( obj_id, …
Rookatu
  • 1,487
  • 3
  • 21
  • 50
6
votes
1 answer

Running a .sql file from R

I have a few .sql files which aggregate a number of tables/views in a SQL Database. I want to be able to direct R to a specific sql file and execute it and return the results in a dataframe. Googling around it seems that I can only grab actual…
user33484
  • 740
  • 2
  • 9
  • 36
6
votes
1 answer

How to use RODBC to save dataframe to table with primary key generated at database

I would like to enter a data frame into an existing table in a database using an R script, and I want the table in the database to have a sequential primary key. My problem is that RODBC doesn't seem to allow the primary key constraint. Here's the…
Matthew
  • 4,149
  • 2
  • 26
  • 53
6
votes
3 answers

RODBC string getting truncated

I am fetching data from MySql Server into R using RODBC. So in one column of the database is a character vector SELECT MAX(CHAR_LENGTH(column)) FROM reqtable; RETURNS 26566 Now I will show you an example how I am running into the problem…
sayan dasgupta
  • 1,084
  • 6
  • 15
6
votes
0 answers

R - Manual RODBC Windows Authentication

When using RODBC to connect to Microsoft SQL Server I know I can do the following: con <- odbcDriverConnect(connection="driver={SQL Server};server=servername;database=dbname;trusted_connection=yes;") However, that limits me to using the windows…
cdeterman
  • 19,630
  • 7
  • 76
  • 100
6
votes
1 answer

R RODBC Show all tables

I'm trying to catalog the structure of a MSSQL 2008 R2 database using R/RODBC. I have set up a DSN, connected via R and used the sqlTables() command but this is only getting the 'system databases' info. library(RODBC) conn1 <-…
screechOwl
  • 27,310
  • 61
  • 158
  • 267
6
votes
1 answer

RODBC with MAC not working. Using unixODBC and Rstudio

I followed this guide http://eriqande.github.io/2014/12/19/setting-up-rodbc.html to get RODBC working with MAC and I am able to connect to the DB. Marcos-MacBook-Pro:~ MarcoB$ isql -v ODBC_SQLSS_R **** ****** …
Marco Blume
  • 97
  • 5
6
votes
1 answer

sqlUpdate returns error "[RODBC] Failed exec in Update" when updating table read from DB

I'm creating a table and writing it to a database with RODBC. No problems. First I tried updating the table in R and writing the updates back line by line using sqlUpdate in the same R script and the same R session as initialising it. No…
SiKiHe
  • 439
  • 6
  • 16
6
votes
1 answer

'Could not allocate memory' when inserting to a memo field in an MS-Access database with RODBC

I'm trying to insert a new row to a table in a Microsoft Access database containing a memo field using the sqlSave function in RODBC, and I'm getting the following error: Calloc' could not allocate memory (1073741824 of 1 bytes) odbcUpdate channel…
JaredL
  • 1,372
  • 2
  • 11
  • 27
6
votes
2 answers

Running SQL query through RStudio via RODBC: How do I deal with Hash Tables?

I've got a very basic SQL query that I'd like to be able to view in R. The trouble is, I need to be able to reference a #table: select RAND(1) as random into #test select * from #test Is this possible, or will I need to create…
user1963962
1 2 3
…
53 54