[R] RODBC crashes connecting to Teradata

[Graham Williams]

Received Fri 19 Dec 2008  7:38am +1100 from Prof Brian Ripley:
> On Thu, 18 Dec 2008, Francisco Javier Perez Caballero wrote:
>
>> Hi, I'm trying to connect to a Teradata database via RODBC on a Linux 64
>> machine (Red Hat Enterprise Linux 5). 

[...]

>>> library(RODBC)
>>> conn = odbcConnect("thedsn", uid="theuid", pwd="thepass")
>>
>> *** caught segfault ***
>> address (nil), cause 'memory not mapped'

[...]

> Most likely a broken driver.  It seems that several do not support 
> GetInfo correctly: isql does not use it by default.  Debugging will show 
> you where the error actually was.

I came across the same problem recently and could not find any
discussion on the r-sig-db list solving this..... Here's my solution.

I have a similar setup - Teradata ODBC driver on amd64 GNU/Linux
(Debian), using the RODBC package, and getting a segfault on
odbcConnect.

odbcConnect calls odbcDriverConnect which in turn .Call's
C_RODBCDriverConnect, which actually succeeds. Then there is a .Call
to C_RODBCGetInfo which results in the segfault. 

I suspect underneath the Teradata driver is crashing on querying a
table that does not exists or that you may not have access to (for me
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!)

I dropped the GetInfo call from odbcDriverConnect  and all is well,
until I 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).

Regards,
Graham


========================================================================
In case it helps, I use this to connect.....

channel <- odbcDriverConnect("DSN=mydsn;UID=user;PWD=password")

with the following function.....

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)
  }
###     res <- .Call(C_RODBCGetInfo, attr(stat, "handle_ptr"))
### Also removed references to res in the following.
  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")
###   if (is.null(bulk_add))
###     bulk_add <- .Call(RODBC:::C_RODBCCanAdd, attr(stat, "handle_ptr"))
  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)
}