[R] RODBC query

[Prof Brian Ripley]

On Thu, 31 May 2007, Lucke, Joseph F wrote:

> As a newbie to RODBC (Windows XP), I find that the commands aren't
> working quite as expected.
> After
> Library(RODBC)
>
> I had planned to use the two-step process
>
> myConn = odbcConnectExcel("Dates.xls")
> sqlQuery(myConn,"SELECT ID, ADM_DATE, ADM_TIME FROM A")	#A is the Excel
> spreadsheet name
> X = sqlGetResults(myConn, as.is=c(T,T,F))
> #2 char variables and 1 integer
> odbcClose(myConn)
>
> This doesn't work.  Instead the following works:
>
> myConn = odbcConnectExcel("Dates.xls")
> X=sqlQuery(myConn,"SELECT ID, ADM_DATE, ADM_TIME FROM A",
> as.is=c(T,T,F))
> odbcClose(myConn)
>
>
>> class(X)
> [1] "data.frame"
>> class(X$ID)
> [1] "character"
>> class(X[,2])
> [1] "character"
>> class(X[,3])
> [1] "integer"
>
>
> I thought sqlQuery stored a query that was to be processed by
> sqlGetResults.  What's happening here?

Listing the function shows

> sqlQuery
function (channel, query, errors = TRUE, ..., rows_at_time = 1)
{
     if (!odbcValidChannel(channel))
         stop("first argument is not an open RODBC channel")
     if (missing(query))
         stop("missing parameter")
     stat <- odbcQuery(channel, query, rows_at_time)
     if (stat == -1) {
         if (errors)
             return(odbcGetErrMsg(channel))
         else return(stat)
     }
     else return(sqlGetResults(channel, errors = errors, ...))
}

It is odbcQuery that runs the query alone.

-- 
Brian D. Ripley,                  ripley at stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595