Togaware DATA MINING
Desktop Survival Guide
by Graham Williams
Google

Teradata Setup

The Teradata ODBC driver on AMD64 GNU/Linux appears to have some issues with returning error conditions. The behaviour is see with both the RODBC package and when using the isql application separately. The applications segfault when calling GetInfo or accessing a table that does not exist.

For RODBC a simple solution is to modify odbcDriverConnect (which is called from odbcConnect), which in turn .Call's C_RODBCDriverConnect. This latter function actually succeeds, but is followed by a .Call to C_RODBCGetInfo which results in the segfault.

In the setup of the Teradata data warehouse I am connecting to the default odbc GetInfo seems to try to access the table DBC.Table, but our setup does not have that available, with DBC.TableX being used instead!

Commenting out the GetInfo call from odbcDriverConnect and all is well, until we query a table that does not exist, and once again the Teradata driver crashes (also crashes in isql when typing "help" or querying a table that does not exist).

The following works for me:



> library(RODBC)
> odbcDriverConnect <- function (connection = "", case = "nochange",
                                 believeNRows = TRUE,
                                 colQuote, tabQuote = colQuote,
                                 DBMSencoding = "", rows_at_time = 1000,
                                 bulk_add = NULL)
  {
    id <- as.integer(1 + runif(1, 0, 1e+05))
    stat <- .Call(RODBC:::C_RODBCDriverConnect, as.character(connection),
                  id, as.integer(believeNRows))
    if (stat < 0)
    {
      warning("ODBC connection failed")
      return(stat)
    }
    if (missing(colQuote)) colQuote <- "\""
    if (missing(case)) case <- "nochange"
    switch(case,
           toupper = case <- 1,
           oracle = case <- 1,
           tolower = case <- 2,
           postgresql = case <- 2,
           nochange = case <- 0,
           msaccess = case <- 0,
           mysql = case <- ifelse(.Platform$OS.type == "windows", 2, 0),
           stop("Invalid case parameter: nochange | toupper | tolower | common db names"))
    case <- switch(case + 1, "nochange", "toupper", "tolower")
    structure(stat, class = "RODBC", case = case, id = id,
              believeNRows = believeNRows,
              bulk_add = bulk_add, colQuote = colQuote, tabQuote = tabQuote,
              encoding = DBMSencoding, rows_at_time = rows_at_time)
  }
> channel <- odbcDriverConnect("DSN=dwh;UID=USER;PWD=PASSWORD")
> sqlQuery(channel, "select count(*) from mytable;")

Copyright © Togaware Pty Ltd
Support further development through the purchase of the PDF version of the book.
The PDF version is a formatted comprehensive draft book (with over 800 pages).
Brought to you by Togaware. This page generated: Sunday, 22 August 2010