Rodbc Queries Returning Zero Rows

RODBC queries returning zero rows

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 returning 0 rows even though there are many rows

The first thing you can do is to change the working date incrementally until you discover a pattern that causes the query to fail. Perhaps (for future readers) the problem is that SQL Server is expecting a format other than the standard. If that's the case then a query for the first day of the first month would (by coincidence only) return the proper result. You can use set dateformat to get SQL Server to accept the format you need.

RODBC query not returning data

Here's my thinking, and something to try.

It's interesting to me that the field it fails on is the first instance of a TIMESTAMP in the table. Working on the theory that TIMESTAMP may have something to do with it, first let's create a view on MV_BRM_COMMUNICATION_DM that casts all the TIMESTAMP fields to DATE:

CREATE VIEW MV_BRM_COMM_DM_VIEW AS
SELECT COMM_ITEM_PK, COMM_ERROR_PK, COMM_ADDRESS_PRLO_PK,
COMM_ADDRESS_LOPR_PK, COMM_ADDRESS_PR_PK,
COMM_OUTBOUNDMESSAGE_ID, COMM_PIDM, COMM_CREATED_BY,
TO_DATE(COMM_CREATION_DATE) AS COMM_CREATION_DATE,
COMM_GENERIC_TEMPLATE_NAME, COMM_TEMPLATE_NAME,
COMM_TEMPLATE_VERSION,
TO_DATE(DATE_COMM_SENT) AS DATE_COMM_SENT,
TO_DATE(COMM_DATE_SENT) AS COMM_DATE_SENT,
COMM_COMMUNICATION_CHANNEL, COMM_SUBJECT, COMM_EMAIL_PK,
COMM_TO_ADDRESS, COMM_ISP_DOMAIN, COMM_CCLIST,
COMM_BCCLIST, COMM_REPLYTO, COMM_SENDER, COMM_REFERENCE_DESC,
COMM_OPTOUT_TOKEN, COMM_DELIVERED_FLAG, COMM_OPTOUT_FLAG,
COMM_OPTOUT_DATE, COMM_OPTOUT_CHANNEL, COMM_OPTIN_FLAG,
COMM_OPTIN_DATE, COMM_OPTIN_CHANNEL, COMM_OPTLOCK,
COMM_ERROR_FLAG, COMM_BOUNCED_FLAG, COMM_ONE_OFF,
COMM_OWNERID, COMM_OWNERNAME, COMM_OWNER_KEY, COMM_ORG_ID,
COMM_ORG_NAME, COMM_CONTENT_PURGED, COMM_ACTIVITY_DATE,
ACTIVITY_DATE
FROM MV_BRM_COMMUNICATION_DM

Now, change your SELECT to read from the view, and see if things work differently.

Not guaranteed as I can't test against your environment, but it's something to start from.

Share and enjoy.

RODBC gives proper row count but yields empty query

In case it helps others, the problem was that the database contained an Oracle spatial field (MDSYS.SDO_GEOMETRY). R did not know what to do with it. I assumed it would just convert it to a character but instead it just got confused. By omitting the spatial field, the query worked fine.

Why is sqlQuery from RODBC not always returning the same data when querying an Impala DB?

It seems my problem was that Impala didn't have enough memory to perform well.



Related Topics



Leave a reply



Submit