AnsweredAssumed Answered

PI JDBC connection error

Question asked by kuehcj on Mar 6, 2019
Latest reply on Oct 9, 2019 by kuehcj

Hi Everyone,

 

We have been using PI JDBC to retrieve data from PI AF via DAS into R. Everything is working great until one of the function's table in an AF database becomes invalid (e.g. due to amendment/removal of an element template).

Below is the code to connect to the AF server.

 

library(glue)
library(RJDBC)
library(DBI)
library(rstudioapi)

 

user_ID <- rstudioapi::showPrompt(  title = "Username", message = "Username", default = "")
Password <- rstudioapi::askForPassword("Database password")
jdbc_drv <- RJDBC::JDBC(driverClass = "com.osisoft.jdbc.Driver")

 

jdbc_conn <- DBI::dbConnect(
  jdbc_drv,
  glue(
    'jdbc:pioledbent://<pisqldas_server>/Data Source=<af_server>;
    User ID={user_ID}; Password={Password};'
  ),
  user_ID,
  Password
  )

 

The error happens at dbConnect, where the error message is:

Error in .jcall(drv@jdrv, "Ljava/sql/Connection;", "connect", as.character(url)[1], :

   java.sql.SQLException: Connection failed. Server returned HTTP response code: 400 for URL: https://<pisqldas_server>:5461/DataAccessServer/Query2/. The definition of the table '[XXX].[DataT].[ft_TransposeSummarize_XXXX]' is not valid.

 

Please note our query are not using anything from this database, which has the invalid function/function's table/view. It seems as though while connecting through DAS, the connection is also trying to return some kinds of metadata from various AF databases.

May I know how can we make the connection woks given sometimes any unused AF database may have invalid objects?

 

Thanks,
Chia

Outcomes