Rank: Administration
Groups: Administration
Joined: 6/20/2008
Posts: 409
Location: Cheshire, United Kingdom.
|
In this topic we will create a reusable connection to PI via the PI-ODBC driver. The connection method is standardised to be used as and when needed to ensure the connection is still alive.
PI ODBC Client Overview
The PI ODBC Driver is used to bring process data into any ODBC-compliant client application.
Open Database Connectivity (ODBC) is Microsoft's implementation of the SQL Access Group's call level interface standard, with some enhancements. Relational database vendors following this standard can be assured that any standard ODBC client applications can access their data.
The PI ODBC Driver is especially useful for applications that combine PI System data with data from other sources, such as cost accounting or laboratory systems. PI ODBC supports Level 1 compliance to the ODBC API, which allows client applications to browse such catalog information as table and column names. The ODBC standard also specifies levels of compliance for SQL processing: OSI Software implements the Core SQL Grammar.
This product is not related to the features of PI ProcessBook (Version 1.2 or later), which uses other vendors' ODBC drivers to access and display data from foreign databases.
The PI ODBC Driver is part of the Data Access (DA) add-on license, which also includes the PI OPC HDA Server, and the PI OLEDB Provider. It is not available separately.
The Connection
Firstly, once you have installed the PI-ODBC driver, for ease you need to create a DSN. For this example the DSN was created with the name of "PI".
What needs to be achieved:
- A module/class level variable to hold the connection (ADODB.Connection)
- A method to evaluate the connection variable and connect via PI-ODBC if required.
How this is done in a VBA project, for example in MS Excel:
Code:Private Conn As ADODB.Connection
Private Function PIODBC_Connection() As Boolean
On Error GoTo PIODBC_Connection_Error
If TypeName(Conn) = "Nothing" Then Set Conn = New ADODB.Connection
If Not Conn.State = adStateOpen Then Conn.Open "PI", "piuser", "pipass"
PIODBC_Connection = (Conn.State = adStateOpen)
Exit Function
PIODBC_Connection_Error:
PIODBC_Connection = False
End Function
To test the connection is established, create a method to call the connection method:
Code:Sub TestingConnection()
If PIODBC_Connection Then
' Connection established!
Else
End If
End Sub
Now this routine can be used whenever required and is used throughout any examples posted onto this forum. OSIsoft PI System SpecialistsPI consultancy on PI Systems, PISDK, AFSDK, OLEDB etc and PI custom developments. Well pretty much anything to do with PI!
|
Rank: Administration
Groups: Administration
Joined: 6/20/2008
Posts: 409
Location: Cheshire, United Kingdom.
|
Alternatively, you can create a connection without the use of a DSN:
Code:Private Function PIODBC_Connection() As Boolean
On Error GoTo PIODBC_Connection_Error
If TypeName(Conn) = "Nothing" Then Set Conn = New ADODB.Connection
If Not Conn.State = adStateOpen Then Conn.Open "Driver={PI-ODBC}; Server=piserver; UID=piuser; PWD=pipass"
PIODBC_Connection = (Conn.State = adStateOpen)
Exit Function
PIODBC_Connection_Error:
PIODBC_Connection = False
End Function OSIsoft PI System SpecialistsPI consultancy on PI Systems, PISDK, AFSDK, OLEDB etc and PI custom developments. Well pretty much anything to do with PI!
|
