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);
}