[R-sig-DB] my Problem with the RODBC sql script below

[Philippi, Tom]

My first test would be dealing with the quotes around BOS in
where CallSign = 'BOS'

qString <- paste("SELECT MD.CallSign, ",
                           " HD.WBAN, ",
                           "TimeStamp_Local, ",
                           "ref_density, ",
                           "ref_dewpoint, ",
                           "ref_dir, ",
                           "ref_precip, ",
                           "ref_press, ",
                            "ref_rh, ",
                           "ref_temperature_avg, ",
                            "ref_ws_avg, ",
                            "ref_wetbulb ",
                            "FROM ASOS.dbo.HourlyData HD ",
                           "left outer join ASOS.dbo.ASOS_MetaData MD on ",
                            "MD.WBAN = HD.WBAN ",
                           "where CallSign = 'BOS' ",
                           "order by TimeStamp_Local ASC;",sep='')
p3 <- sqlQuery(ch1,qString)


That has 2 changes: quoting around the character literal value BOS, and a
semicolon at the end of the query string.

My second idea would be the syntax of FROM ASOS.dbo.HourlyData HD instead
of FROM ASOS.dbo.HourlyData AS HD.  That may differ depending on the db
backend you're hitting.  If this doesn't work, try reposting with
sessionInfo() and information on the db you are using.


I hope that this helps...
Tom 2


On Fri, Mar 6, 2015 at 4:54 PM, Alemu Tadesse <alemu.tadesse using gmail.com>
wrote:

> Dear All,
>
> The following script works in sql server or toad  for sql server.
>
> SELECT MD.CallSign
>   ,HD.WBAN
>       ,TimeStamp_Local
>       ,ref_density
>       ,ref_dewpoint
>       ,ref_dir
>       ,ref_precip
>       ,ref_press
>       ,ref_rh
>       ,ref_temperature_avg
>       ,ref_ws_avg
>       ,ref_wetbulb
>   FROM ASOS.dbo.HourlyData HD
>     left outer join ASOS.dbo.ASOS_MetaData MD on MD.WBAN = HD.WBAN
> where CallSign = 'BOS' order by TimeStamp_Local ASC
>
> and the result is a table of the data corresponding to those variables
> listed above
>
> But, in R it doesn't work with the following commands
>
> ch1 <- odbcConnect(dsn="COLOMSQ", uid="DataPull", pwd="Puldata")
>
>
> p3<-sqlQuery(ch1,'SELECT MD.CallSign
>   ,HD.WBAN
>       ,TimeStamp_Local
>       ,ref_density
>       ,ref_dewpoint
>       ,ref_dir
>       ,ref_precip
>       ,ref_press
>       ,ref_rh
>       ,ref_temperature_avg
>       ,ref_ws_avg
>       ,ref_wetbulb
>   FROM ASOS.dbo.HourlyData HD
>     left outer join ASOS.dbo.ASOS_MetaData MD on MD.WBAN = HD.WBAN
> where CallSign = BOS order by TimeStamp_Local ASC')
>
> close(ch1)
>
> and p3 results in
>
> [1] "08S01 0 [Microsoft][ODBC SQL Server Driver]Communication link failure"
>
>
>
>
>
> [2] "[RODBC] ERROR: Could not SQLExecDirect 'SELECT MD.CallSign\n
>  ,HD.WBAN\n      ,TimeStamp_Local\n      ,ref_density\n
>  ,ref_dewpoint\n      ,ref_dir\n      ,ref_precip\n      ,ref_press\n
>  ,ref_rh\n      ,ref_temperature_avg\n      ,ref_ws_avg\n
>  ,ref_wetbulb\n  FROM ASOS.dbo.HourlyData HD\n    left outer join
> ASOS.dbo.ASOS_MetaData MD on MD.WBAN = HD.WBAN\nwhere CallSign = BOS order
> by TimeStamp_Local ASC'"
>
> with no data
>
> Any idea ?
>  Thank you in Advance
>
>         [[alternative HTML version deleted]]
>
> _______________________________________________
> R-sig-DB mailing list -- R Special Interest Group
> R-sig-DB using r-project.org
> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>



--

	[[alternative HTML version deleted]]