Login/Register
RODBC queries returning zero rows
Asked Answered
Solved sqlrodbcrodbc
O

5

21

Issue: RODBC (falsely) returning zero rows

Situation:

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?

Proof that there should be many rows:

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.

Additional Info

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
Orthopedics answered 15/9, 2011 at 2:1 Comment(6)
Are any data returned if you omit the WHERE clause entirely? – Berey
I'd give you 10 up votes for a concise, descriptive and well thought out problem. I'm sure you'd rather have an answer but I can't help you there. Safe to assume there is no difference in running the table info commands for piinterp? Can you verify that a RODBC select statement does return rows (remove your where clause) to help isolate whether it's an issue with the filter in your query vs a generic issue getting data out? – Bukhara
@Joshua, HY000 -268 [OSI][PI ODBC][PI]Syntax error at offset 8 token -> [2] "[RODBC] ERROR: Could not SQLExecDirect 'SELECT tag, time, status, value FROM piinterp' – Krefetz
@billinkc. I don't know if it's possible to return rows w/o a WHERE clause for this RDMBS. But either way, I'm positive that there should be rows returned for this query, as there are from the cmd prompt as well as in Excel. FYI - is my question that unclear? I thought it was alright, but I'll edit to see what I can do. – Krefetz
I would try the following. Can you run the SQL query directly on the database and not through ODBC? Can you run a trivial query through ODBC (like SELECT 0 as n;)? Can you connect to the database using ODBC from some other environment like VBA or MS Access? – Honourable
As noted by Btibert3 on the following question #11955234 ODBC connections can get confused by date/timestamp formats and falsely report 0 rows. I've just seen this with RODBC and an Oracle database. Not retrieving the date/timestamp column corrected the problem for me. Hope this helps someone. – Abysm
O
13

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)
Orthopedics answered 11/6, 2012 at 23:50 Comment(3)
Thank you very much for your answer. I ran into the same (baffling) issue and you just saved me a lot of time. – Marismarisa
Glad to help. I'm curious, were you working with the PI Historian ODBC, or some other finicky ODBC? – Krefetz
The believeNRows = FALSE also seems to be needed by the SQLite driver. – Dochandorrach
P
3

Try adding

believeNRows = FALSE

to the query. This is an issue that has come up with a few of the drivers which report a wrong size on the result set.

Pannier answered 15/9, 2011 at 2:23 Comment(2)
Hey Dirk, unfortunately sqlQuery(piconn, sqlStr, believeNRows = FALSE) still returns zero rows. As does sqlQuery(piconn, sqlStr, believeNRows = FALSE, max = 0) that I've seen others have success with (though, for Oracle). – Krefetz
It turns out that I needed to add believeNRows = FALSE and also rows_at_time = 1. Thanks Dirk. – Krefetz
A
3
  1. Test if the ODBC driver works correctly. Each ODBC driver should provide simple means to test the connection. Also try to connect to the ODBC source using MS Office (Access, Excel...) or Open Office.
  2. If the above works, then go to R and try the simplest query possible, like select 1 (your query isn't the simplest, as darcken noted! You have to try really the simplest query to be sure).
  3. If it doesn't work, try to call odbcGetErrMsg() function after each RODBC function call (after connect, after query, ...).
Amalekite answered 23/9, 2011 at 9:5 Comment(0)
C
1

I had the same problem and fixed it by adding "rows_at_time=1" to the odbcConnect call. From the odbcConnect help:

*Several errors which have been reported as bugs in RODBC 1.3-0 which were in fact ODBC driver errors that can be circumvented by setting rows_at_time = 1 (and the warning under that argument has always been there). The drivers involved have been third-party Oracle drivers and old SQL Server drivers.*

In my case I was running 64bit R 2.15.0, RODBC 1.3-5 and the Actual ODBC Oracle driver on OS X Lion.

Charitycharivari answered 24/5, 2012 at 0:53 Comment(1)
Thanks Klaas! It turns out that you and Dirk were BOTH right. The combination of rows_at_time = 1 and believeNRows = FALSE is what did the trick here. – Krefetz
W
0

I think you need to rule out that your actually connecting to the database/table first by getting SELECT * FROM MYTABLE to work within R. If you can't get this working then something is wrong with your setup/drivers.

Once you are sure that you can actually query the database/table within R then progressively make your query more complex and try to isolate where the issue is. One thing to try might be double == on your equality conditions.

I've tried using a bunch of databases(sql server,mysql,sqlite) within R and the performance has been poor with all of them. Imo your better off querying the database natively, dumping to text, then reading the file into R.

Willed answered 17/9, 2011 at 8:11 Comment(3)
The simplest query possible looks like "SELECT * FROM piinterp WHERE time > DATE('-1h')". And this returns zero rows. You simply can't query this DB without time > DATE(). I've tried many other equally valid query variations which worked in Excel through the ODBC but return zero rows in R. I'm 100% sure all valid queries are returning zero rows in R. – Krefetz
As I said in my comment above, try a simple query like SELECT 0 as n;. It doesn't get any simpler. If that doesn't work then you know it is something with the driver. If it does, then there is something wrong with your query. – Honourable
As I said in my comment above, a query like SELECT 0 as n; is not possible in this database. You simply have to specify a table name and date range in every query for this time-series database. – Krefetz

© 2022 - 2024 — McMap. All rights reserved.