sql - microsoft - odbc rstudio

Consultas RODBC que devuelven cero filas

Question

Problema: RODBC (falsamente) devolviendo cero filas

Situación:

Estoy usando RODBC para conectarme a un DSN que creé utilizando un controlador ODBC de DB comercial (el PI Historian Time Series DB de OSI Soft, si tiene curiosidad).

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

Ahora si pregunto, obtengo cero filas.

> sqlQuery(piconn, sqlStr)
[1] TAG    TIME   STATUS VALUE 
<0 rows> (or 0-length row.names)

Con BelieveNRows = FALSE todos estos todavía muestran cero resultados, aunque debería devolver 120 filas.

> sqlQuery(piconn, sqlStr, believeNRows = FALSE)
> sqlQuery(piconn, sqlStr, believeNRows = FALSE, max = 0)
> sqlQuery(piconn, sqlStr, believeNRows = FALSE, max = 0, buffsize = 120)

¿Qué más puedo probar?

Prueba de que debe haber muchas filas:

En Excel o Símbolo del sistema

SELECT tag, time, status, value FROM piinterp WHERE tag = ''PW1.PLANT1.PRODUCTION_RATE'' and time > DATE(''-4h'') and timestep = ''+2m''

Con resultados ...

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

Tanto en R como en Excel, si tag = ''aeeEEEEE11!!!'' una etiqueta que no existe, diga tag = ''aeeEEEEE11!!!'' , devuelve correctamente cero filas.

Información adicional

Tablas 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

Información de 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" 

Información de mi sesión:

 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

Answer 1

1. Probar si el controlador ODBC funciona correctamente . Cada controlador ODBC debe proporcionar medios simples para probar la conexión. También intente conectarse a la fuente ODBC utilizando MS Office (Access, Excel ...) u Open Office.
2. Si lo anterior funciona, entonces vaya a R e intente la consulta más simple posible , como select 1 (su consulta no es la más simple, como anotó darcken. Debe estar seguro de que es la consulta más simple).
3. Si no funciona, intente llamar a la función odbcGetErrMsg() después de cada llamada a la función RODBC (después de conectarse, después de la consulta, ...).

Answer 2

Creo que debe descartar que su conexión a la base de datos / tabla primero haga que SELECT * FROM MYTABLE funcione dentro de R. Si no puede hacer que esto funcione, entonces hay algún problema con su configuración / controladores.

Una vez que esté seguro de que realmente puede consultar la base de datos / tabla dentro de R, entonces haga su consulta cada vez más compleja e intente identificar dónde está el problema. Una cosa a tratar podría ser el doble == en tus condiciones de igualdad.

He intentado usar un montón de bases de datos (servidor sql, mysql, sqlite) dentro de R y el rendimiento ha sido deficiente en todos ellos. Imo es mejor consultar la base de datos de forma nativa, verter el texto y luego leer el archivo en R.

Answer 3

Intenta agregar

believeNRows = FALSE

a la consulta. Este es un problema que ha surgido con algunos de los controladores que informan un tamaño incorrecto en el conjunto de resultados.

Answer 4

Resulta que todo lo que tenía que hacer era configurar rows_at_time = 1 además de believeNRows = FALSE al configurar mi conexión ODBC.

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)

Answer 5

Tuve el mismo problema y lo arreglé agregando "rows_at_time = 1" a la llamada odbcConnect. Desde la ayuda de odbcConnect:

* Varios errores que se informaron como errores en RODBC 1.3-0 que en realidad eran errores del controlador ODBC que se pueden eludir configurando rows_at_time = 1 (y la advertencia bajo ese argumento siempre ha estado ahí). Los controladores involucrados han sido controladores de Oracle de terceros y controladores antiguos de SQL Server. *

En mi caso, estaba ejecutando 64bit R 2.15.0, RODBC 1.3-5 y el controlador ODBC Oracle real en OS X Lion.