AnsweredAssumed Answered

Write to PI from R via ODBC

Question asked by torsteins on Jul 18, 2017
Latest reply on Aug 4, 2017 by ernstamort

Hi guys. I am currently writing to PI from R using a ODBC connection with a OLEDB Provider type driver. I use the RODBC package and the function 'sqlQuery' to push multiple lines of data into PI, which works fine. I have a special function that parses a data.frame (or tibble) object containing the colums  'tag', 'time' and 'value' to a string like the one below:


## with package RODBC

picon <- odbcConnect(dsn = "MY_DATABASE", believeNRows = FALSE, rows_at_time = 1)

sql <- "INSERT INTO [piarchive].[picomp] (tag, time, value)

           SELECT 'test_5', '2017-07-18 08:00:00', cast(1 as float64)

           UNION SELECT 'test_5', '2017-07-18 08:00:01', 2

           UNION SELECT 'test_5', '2017-07-18 08:00:02', 3"

sqlQuery(picon, sql)


Now I want to augment the PI comp table with my R data.frame object without parsing it first, e.g. using the function


## data and query


# A tibble: 3 × 3

     tag value                time

   <chr> <chr>              <dttm>

1 test_5     1           2017-07-18 08:00:00

2 test_5     2           2017-07-18 08:00:01

3 test_5     3           2017-07-18 08:00:02


sqlUpdate(channel = picon, dat = data, tablename = "picomp", verbose = FALSE)


Error in sqlUpdate(channel = picon, dat = data, tablename = "picomp",  :

  cannot update ‘picomp’ without unique column





I actually want to use the DBI + odbc package (supposedly much faster). Using the same data.frame, I get this when I query:


## with DBI and odbc

picon2 <- dbConnect(odbc::odbc(), dsn = "MY_DATABASE")

dbWriteTable(conn = picon2, name = "picomp", value = data, append = TRUE)

Error in result_insert_dataframe(rs@ptr, values) :

  nanodbc/nanodbc.cpp:1128: HYC00: The 'SQL_AUTOCOMMIT_OFF' for the 'SQL_ATTR_AUTOCOMMIT' is not implemented.



Anyone got an idea of whats wrong, or if it is even possible to augment the PI table with a raw R data.frame without parsing a typical SQL-formatted query through a funciton like 'sqlUpdate'?




Any tip on how to parse strings to queries, with regards to date-time objects (lubridate) for times and integers/doubles for values? I use the dplyr::sql() function to get the straight text, which automatically parses everything as string before sending it to PI - which then re-formats it back to time/numerics.