I'm using RODBC to connect to a DSN I created using a commercial DB's ODBC driver (OSI Soft's PI Historian Time Series DB, if you're curious).
> library(RODBC)
> piconn <- odbcConnect("PIRV", uid = "pidemo")
> sqlStr <- "SELECT tag, time, status, value FROM piinterp WHERE tag = 'PW1.PLANT1.PRODUCTION_RATE' and time > DATE('-4h') and timestep = '+2m'"
Now if I query, I get zero rows.
> sqlQuery(piconn, sqlStr)
[1] TAG TIME STATUS VALUE
<0 rows> (or 0-length row.names)
With BelieveNRows = FALSE these all still show zero results, even though it should return 120 rows.
> sqlQuery(piconn, sqlStr, believeNRows = FALSE)
> sqlQuery(piconn, sqlStr, believeNRows = FALSE, max = 0)
> sqlQuery(piconn, sqlStr, believeNRows = FALSE, max = 0, buffsize = 120)
What else can I try?
In Excel or Command Prompt
SELECT tag, time, status, value FROM piinterp WHERE tag = 'PW1.PLANT1.PRODUCTION_RATE' and time > DATE('-4h') and timestep = '+2m'
With results...
TAG TIME STATUS VALUE
PW1.PLANT1.PRODUCTION_RATE 15/09/2011 9:33 448 0
PW1.PLANT1.PRODUCTION_RATE 15/09/2011 9:31 452 0
PW1.PLANT1.PRODUCTION_RATE 15/09/2011 9:29 390 0
PW1.PLANT1.PRODUCTION_RATE 15/09/2011 9:27 419 0
PW1.PLANT1.PRODUCTION_RATE 15/09/2011 9:25 413 0
PW1.PLANT1.PRODUCTION_RATE 15/09/2011 9:23 393 0
PW1.PLANT1.PRODUCTION_RATE 15/09/2011 9:21 427 0
etc
Both in R and in Excel, if I query for a tag that doesn't exist, say tag = 'aeeEEEEE11!!!', it correctly returns zero rows.
SQL Tables
> sqlTables(piconn)
TABLE_QUALIFIER TABLE_OWNER TABLE_NAME TABLE_TYPE REMARKS
1 <NA> <NA> pialias TABLE pialias
2 <NA> <NA> piavg TABLE piavg
3 <NA> <NA> pibatch TABLE pibatch
4 <NA> <NA> picomp TABLE picomp
5 <NA> <NA> piinterp TABLE piinterp
ODBC info
> odbcGetInfo(piconn)
DBMS_Name DBMS_Ver Driver_ODBC_Ver Data_Source_Name Driver_Name Driver_Ver ODBC_Ver Server_Name
"PI" "03.04.0370" "02.01" "PIRV" "PIODBC32.DLL" "01.03.0100" "03.52.0000" "Aurvyzpis1"
My session info :
sessionInfo()
R version 2.12.2 (2011-02-25)
Platform: i386-pc-mingw32/i386 (32-bit)
locale:
[1] LC_COLLATE=English_Australia.1252 LC_CTYPE=English_Australia.1252 LC_MONETARY=English_Australia.1252 LC_NUMERIC=C
[5] LC_TIME=English_Australia.1252
attached base packages:
[1] grid stats graphics grDevices utils datasets methods base
other attached packages:
[1] ggplot2_0.8.9 proto_0.3-9.2 reshape_0.8.4 plyr_1.6 RODBC_1.3-3
loaded via a namespace (and not attached):
[1] tools_2.12.2
It turns out that all I needed to do was to set rows_at_time = 1 in addition to believeNRows = FALSE while setting up my ODBC connection.
piconn <- odbcConnect(dsn = "PI", uid = "pwd", believeNRows = FALSE, rows_at_time = 1)
sqlStr <- "SELECT tag, time, status, value FROM piinterp WHERE tag = 'RV1.MADST101_WINDSPEED' and time > DATE('-12h') and timestep = '+2m'"
results <- sqlQuery(piconn, sqlStr)