In r-gris/dplyrodbc:

knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>",
  fig.path = "README-"
)

dplyrodbc

devtools::install_github("mdsumner/RODBCDBI",   ref = "mike")

Example

ODBC has an installed File DSN called "CountriesAccess". This is a .mdb file created using 32-bit Windows (effectively) and configured with the Data Sources application from "C:\Windows\SysWOW64\odbcad32.exe".

Minimal test for RODBC.

library(RODBC)  ## remember this is 32-bit R
con <- odbcConnect("CountriesAccess1")
## some columns explode the app, presumably "Geom (I)"
sqlQuery(con, "SELECT TOP 10 * FROM [Countries Table]")
close(con)

Methods for us.

fs <- list.files("R", pattern = ".r$", full.names = TRUE)
for (i in fs) source(i)

Try for real with our Access source.

library(DBI)
library(RODBCDBI)
library(dplyr)
x <- src_access("CountriesAccess1")

accesstable <- tbl(x, "Countries Table")
## filter
accesstable  %>% filter(ID == 1)

## group_by
accesstable %>% group_by(BranchesI)

## mutate
accesstable  %>% mutate(a = 1)

## arrange, but prints too many rows?
accesstable  %>% arrange(BranchesI) %>% filter(coordinatesI > 1434)

## summarize doesn't work 
# accesstable %>% group_by(BranchesI) %>% summarize(x = n())
# Source: Access  [@ACCESS:/ACCESS]
# From: <derived table> [?? x 2]
# 
# Error in .valueClassTest(ans, "data.frame", "fetch") : 
#   invalid value from generic function ‘fetch’, class “character”, expected “data.frame”
# In addition: Warning message:
# closing unused RODBC handle 4 
# 

And Manifold.

## not clear how to do this yet
## we need cases for odbcConnectWhatever
setMethod(
  "dbConnect", 
  "ODBCDriver", 
  function(drv, dsn, user = NULL, password = NULL, ..., manifold = FALSE){
    uid <- if(is.null(user)) "" else user
    pwd <- if(is.null(password)) "" else password
    if (manifold) {
     connection <- odbcConnectManifold(dsn) 
    } else {
      connection <- odbcConnect(dsn, uid, pwd, ...)
    }
    new("ODBCConnection", odbc=connection)
  }
)


src_manifold <- function(dbname = NULL, host = NULL, port = NULL, user = NULL,
                         password = NULL, ...) {

  con <-    dbConnect(RODBCDBI::ODBC(), dbname, manifold = TRUE)

  src_sql("manifold", con)
}


mapfile <- "C:\\data\\Countries.map"
manifold <- src_manifold(mapfile)
## woah, not cool
# ct <- tbl(manifold, "Countries Table")
#  Show Traceback
#  
#  Rerun with Debug
#  Error in odbcQuery(channel, query, rows_at_time) : 
#   'Calloc' could not allocate memory (2147483648 of 1 bytes) 

## Try with innocuous table
 ct <- tbl(manifold, "Table")

 ## collect, summarize, etc. don't work yet
# a <- collect(ct %>% group_by(`Branches (I)`) %>% select(`Longitude (I)`, `Latitude (I)` ))
# Error in .valueClassTest(ans, "data.frame", "fetch") : 
#   invalid value from generic function ‘fetch’, class “character”, expected “data.frame”
# 

r-gris/dplyrodbc documentation built on May 26, 2019, 1:32 p.m.