How to scrape redirected URL with R

Question

I have afunction

testFun <- function(myTeam){
  print(myTeam)
  teamResults <- sqlQuery(channel,paste(
    "
  SELECT  soccer.tblResultsallMore.TEAMNAME,
    sum(case when soccer.tblResultsallMore.RES='W' then 1 else 0 end) as W,
    sum(case when soccer.tblResultsallMore.RES='L' then 1 else 0 end) as L,
    sum(case when soccer.tblResultsallMore.RES='D' then 1 else 0 end) as D
    FROM soccer.tblResultsallMore
    WHERE soccer.tblResultsallMore.TEAMNAME=myTeam
    GROUP BY soccer.tblResultsallMore.TEAMNAME


    "))
  return(teamResults) # no error if this is not returned
}
testFun("Everton")

I f I hardcode 'Everton' in the code, I get the required output

[1] "Everton"
  TEAMNAME    W    L    D
1  Everton 1734 1463 1057

but with the parameter there is an error

[1] "Everton"
[1] "42S22 207 [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'myTeam'."                                                                                                                                                                                                                                                                                                                                                                                                
[2] "[RODBC] ERROR: Could not SQLExecDirect

Help much appreciated

score 2 · Accepted Answer · edited May 23 '17 at 10:34

2

In the code you provided the name myTeam is not replaced, it is seen as character string, and the sql statement looks for the team called myTeam.

variabel = "spam"
paste("from table select variabel")

Does not put "spam" in the sql statement inside paste. The correct syntax is:

paste("from table select ", variabel)

In your situation I would use sprintf. An example:

variabel = "spam"
sprintf("from table select %s", variable)

For more detail see the documentation of sprintf.

In regard to your remark, if there is no explicit return statement, the last evaluated expression is returned. For a discussion see:

Explicitly calling return in a function or not

edited May 23 '17 at 10:34

Community

1
1

answered Sep 01 '12 at 19:36

Paul Hiemstra

59,984
12
142
149

Actually i was having a bit of a brain cramp. I should have put ... WHERE soccer.tblResultsallMore.TEAMNAME='",myTeam,"' GROUP BY soccer.tblResultsallMore.TEAMNAME",sep="")... but thanks for the suggestion which i have not used before. I'll assume your answer is correct – pssguy Sep 01 '12 at 19:57
I extended my answer a bit to include the correct syntax of paste. – Paul Hiemstra Sep 01 '12 at 19:59

How to scrape redirected URL with R

1 Answers1