Using JDBC to Access OSI PI from Ignition

Ignition
,
1

I am running version 8.1.44 of Ignition and I am trying to configure a JDBC Database Driver that will connect to and query my OSI PI Data.

I was trying to follow the guidance outlined in this article but I see that they were using a much older version of Ignition and JDBC driver.

The JDBC Driver that I am using is version 2019, Patch 1, the AF PI Server version that I am using is 2018 SP3 Patch 6, and the PI SQL Data Access Server that I am using is version 2023, Patch 1.

Right now, I am running into a couple of different errors on the Ignition side.

My JDBC Driver is configured as follows:
Name - OSI PI
Classname - com.osisoft.jdbc.Driver
Driver Type - Generic
URL Format - jdbc:pisql://PI Server IP Address/Data Source=PI Server Data Source;Integrated Security=SSPI;
Default Translator - MSSQL

When I try to create a Database Connection using the OSI PI JDBC Driver, the connection bounces between a reconnecting and faulted status, and I get the following error:
"Cannot create PoolableConnectionFactory (Connection failed. Please make sure the PI SQL DAS service is running, and you have supplied the correct port number. Connection refused: connect)"

I have confirmed that the PI SQL DAS service is running on the PI server, and that I can see the PI server from my Ignition Server.

I have tried updating the URL in my Database Connection to: jdbc:pisql://PI Server IP Address:PI SQL DAS Port/Data Source=PI Server Data Source;Integrated Security=SSPI;
But when I do this, my Database Connection immediately goes into a faulted status and I get a different error:
"Cannot create PoolableConnectionFactory (Connection failed. Error at index 4 in: "5469:5461")"

Does anybody know if this functionality is still supported on newer versions of Ignition? If so, are there any steps that I may be missing in my configuration that would help give better clarity on the errors that I am seeing?

2

Your URLs do not look like the correct format, did you just remove the das_node and PI Server name from the URLs before posting, or are they actually missing in your configuration?

3

I only removed before posting. They exist in my configuration.
Edit: I didn't realize that using <>'s in my post would hide the information. I have updated the OP.

4

I think you’re going to have to post the un-redacted connection string for this error to make sense.

If you can’t do that here give support a call.

5

"Cannot create PoolableConnectionFactory (Connection failed. Error at index 4 in: "5469:5461")"

Sounds like a syntax issue when trying to read the connection string. Try 5462 if you haven't changed the default ports on the PI DAS

6

Using a different IP address but the connection string is:
jdbc:pisql://192.168.21.128:5469/Data Source=HIST;Integrated Security=SSPI;

7

The PI DAS is installed on ports 5468/5469 on my PI server. The default ports are 5464 and 5465, but they were being occupied by some System Kernel PID.

8

Oh. I was getting 5462 from the docs here but I believe that's the wrong documentation for the product you're using.

It's weird that the error message contains 5461 if this product doesn't default to 5461/5462 and you're trying to use 5468/5469.

9

It appears that your document is for PI SQL DAS 2016 R2. I am using 2023 SP1, and its document specifies that the default ports are 5464 and 5465. Here is a link to the Administrator Guide

10

Yeah, agreed. I'm just not sure how 5461 ends up in your error message if that port isn't involved.

11

Hi there,

I’m facing the exact same problem using the same sw versions, only difference in my case is that SQL DAS ports are 5464 and 5465 (net.tcp) and the log points to 5461. Is there a ticket/case opened or any reason this is happening?

Thanks!

12

It seems like something is appending the :5461 to your connection string for some reason. If you don't have anything configured in Ignition with this port number, then it's likely the PI JDBC driver itself. That seems odd, I wouldn't think the driver should append a port number when a port already exists in the connection string.

I can't play with this, as I don't have a PI JDBC driver. You could try using this as your URL format jdbc:pisqlclient://<AF Server>[:<Port>]/<AF Database>[;property=value[;property=value]] from this documentation: AVEVA™ Documentation

Double check the values of any driver properties you have set, also. Connecting to PI from Ignition is becoming a common question, so please let us know how it's going. I'll try to help where I can.

EDIT: The URL format I suggested may not work either, making a follow up comment with more details..

13

OK, I believe a lot of this confusion is due to a misunderstanding of the OSIsoft/AVEVA SQL/OLEDB/ODBC/JDBC products. I've been doing a bit of research, but I do not have hands on experience with any of these products, so take this information with a grain of salt.

tl;dr- You may need the older PI SQL DAS (OLEDB) product and the older JDBC driver to query historical data from PI. I have not seen any examples or documentation that suggest the newer PI SQL Client and PI SQL DAS (RTQP) can query historical data, only snapshot (current data) via AF Attributes. There might be a way, but I haven't found it. :person_shrugging: I imagine the syntax issue that y'all have ran into is due to the different connection string URLs used by the different PI SQL DAS products described below.

  • PI SQL Data Access Server (RTQP Engine) 2023 SP1 (assuming this is what you're using)
    • This product allows connections to PI via an AF server from the PI JDBC driver provided by PI SQL Client. PI SQL Client was introduced somewhere around 2018.
    • You cannot directly access PI Points (and their data) from the PI Data Archive. Instead, queries are AF-centric, meaning any PI Point that you wish to query must be associated with an AF Attribute somewhere in your AF structure.
      • If you're not using AF for all of your PI Data, you'll have to create some sort of dummy AF structure that you can query. Your connection string will be the format described in the PI SQL Client docs: jdbc:pisqlclient://<AF Server>[:<Port>]/<AF Database>[;property=value[;property=value]]
      • I'm not sure if you can even query historical data stored in the Data Archive with this. I've only seen examples/documentation that suggest being able to retrieve snapshot (current) data.
  • PI SQL Data Access Server (OLEDB / PI Integrators)
    • This is the product I referenced the documentation for in this comment. This product is different than PI SQL Data Access Server (RTQP Engine) 2023 SP1.
    • This product allows connections to the PI Data Archive via the PI OLEDB Provider without the need for a PI AF server.
    • This is the product referenced in the Using JDBC to access OSI PI from Ignition article.

Sources of good info:

Unfortunately, "ability to query historical data from an archive" is absent from the comparison of capabilities for these products. You may consider opening a ticket with AVEVA support.

14

Hi Cody,

Thanks for the reply. In fact you are right the usual setup is to go against the AF server, although there is a way, as you suggested, to “query” the Data Archive and that is done installing the PI OLEDB Provider in the source, that opens a connection to the Data Archive and allows us to get snapshot and historical data. In that case the connection string should point to the pioledb type instead to pisql (as the documentation sugests) which seems to not be completely correct as we have tested the pisql type and it works even without providing any port number in the connection string.

This makes me thing that AVEVA/OSIsoft have hardcoded the port 5461 into the JDBC driver and for some reason it doesn’t work with newer versions of PI SQL DAS where by default the ports are 5464 and 5465.

I’ll continue testing and will update this post.

Thank you!