[R] R crashes on Repeated ODBC Queries

[James Chapman]

Hi,

I've been up all night trying to get the following to work. 

First here is the setup. I have a P4 1G mem running WinXP SP2, R 2.0.1
patched 2005-01-15, MySQL 4.1.7 and MyODBC 3.51.

I've been pulling data from MySQL using the code at the end of the post,
where start.date and end.date are chron objects and auction.type is a
string. I've been rushed so the code, especially the "CMPC.load" function,
is a little less then elegent.

The problem lies in the fact that R crashes, with an exit value of 5, after
looping through nine months or so of observations. I've tried different
values as the function arguments and it still happens at around nine
monthes, or about 270 loops through the "for (i in dates)" loop or around
500 calls to the ODBC connection. I say around since I can start the
function with the same inputs and it will crash at different months.

My question is this a problem in R or ODBC?

Thanks for your help,

James

"CMPC.load" <-
  function(start.date,end.date,auction.type){
    the.data <- NULL
    dates <- seq(from=start.date,to=end.date);
    for (i in dates){
      if (!is.weekend(i)){
        the.date <- day.list(i)
        tranches <- tranche.find(i,auction.type)
        if (tranches$num != 0){
          for (j in 1:dim(tranches$data)[1]){
            the.tranche <- tranches$data[j,]
            the.auc.date <- day.list(the.tranche$aucdate)
            the.mat.date <- day.list(the.tranche$matdate)
            the.is.date <- day.list(the.tranche$issdate)
            #get the data from a given auction/tranche
            cmnd <- paste("SELECT submitted_by_fi_id AS fiid , bid_yield AS
yld,bid_amt/100000000 AS amt,bid_allotted_amt/100000000 AS aamt,on_target as
target, on_rate as rate FROM official_bid,rates,auction WHERE
auction.auction_id = ",the.tranche$id," AND auction.auction_id =
official_bid.auction_id AND auction.auction_type_code like
'",auction.type,"%' AND rates.day =
'",sprintf('%i-%i-%i',the.auc.date[1],the.auc.date[2],the.auc.date[3]),"'
AND issue_date =
'",sprintf('%i-%i-%i',the.is.date[1],the.is.date[2],the.is.date[3]),"' AND
maturity_date =
'",sprintf('%i-%i-%i',the.mat.date[1],the.mat.date[2],the.mat.date[3]),"';",
sep="")
            tmp.data <- auction.connect(cmnd)
            if (!is.nan(tmp.data[1,1])){
              tmp.bidders <- unique(tmp.data$fiid)
              for (k in tmp.bidders){
                tmp.id.data <- tmp.data[tmp.data$fiid == k,]
                tmp.order <- order(tmp.id.data$yld,decreasing = T)
                tmp.id.data <- tmp.id.data[tmp.order,]
                tmp.id.data$bnum <- seq(from=1,along=tmp.id.data$yld)
                tmp.id.data$id <- the.tranche$id
                tmp.id.data$auc.date <- the.tranche$aucdate
                tmp.id.data$mat.date <- the.tranche$matdate
                tmp.id.data$the.is.date <- the.tranche$issdate
                the.data <- rbind(the.data,tmp.id.data)
              }
            }
          }
        }
      }
    }
    return(the.data)
  }


"tranche.find" <- function(the.date,auction.type){
  #This function returns a list containing the number of tranches in
  #an auciton as well as the issue date, the maturity dates, term codes and
IDs for
  #the auctions.
  the.dates <- day.list(the.date)
  cmnd <- paste("SELECT
auction.auction_id as id,
tranche.term_days as term,
tranche.issue_date as issdate,
tranche.term_type_code as termcode,
tranche.maturity_date as matdate,
auction.auction_date as aucdate
FROM
auction,tranche
WHERE
auction.auction_id = tranche.auction_id
AND
auction_type_code like '",auction.type,"%'
AND
auction.auction_date =
",sprintf("'%i-%i-%i'",the.dates[1],the.dates[2],the.dates[3]),";"
                ,sep="");
  the.output <- auction.connect(cmnd);
  #now see if we got anythin back
  if (length(the.output$id)){
    #if so count how many auctions there were
    num.auctions <- length(the.output$term)
    the.output$issdate <- chron(the.output$issdate,format='y-m-d')
    the.output$matdate <- chron(the.output$matdate,format='y-m-d')
    the.output$aucdate <- chron(the.output$aucdate,format='y-m-d')
  }
  else {
    num.auctions <- 0
  }
  the.return <- list(num=num.auctions,data=the.output)
  return(the.return)
}

#This is a wrapper to take care of the odbc connection
"auction.connect" <- function(query){
  auctions <- odbcConnect("auctions");
  the.output <- sqlQuery(auctions,query);
  odbcClose(auctions);
  return(the.output);
}