RODBC查询返回零行 [英] RODBC queries returning zero rows

问题描述

我正在使用RODBC连接到使用商业数据库的ODBC驱动程序(如果您好奇的话，则为OSI Soft的PI Historian时间序列数据库)创建的DSN.

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)

使用BelieveNRows = FALSE，即使返回120行，这些结果仍然显示为零.

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)

我还能尝试什么?

在Excel或命令提示符下

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'

有结果...

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

在R和Excel中，如果我查询不存在的标记(例如tag = 'aeeEEEEE11!!!')，它都会正确返回零行.

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表

> 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信息

> 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" 

我的会话信息:

 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

推荐答案

事实证明，在设置ODBC连接时，除了believeNRows = FALSE之外，我还需要设置rows_at_time = 1.

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)

这篇关于RODBC查询返回零行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！