Marcos Vainer Loeff

Creating a simple app using PI JDBC and Eclipse

Blog Post created by Marcos Vainer Loeff Employee on Oct 28, 2016

Hello,

 

I want to share with you how simple it is to create a simple Java app using PI JDBC  2016 and Eclipse IDE  Actually, I am using Eclipse IDE for Java Developers 4.6.0 (Neon release).

 

 

Before we start, let me explain the roles of each VM of my domain:

 

- MARC-PI2016 has the PI Data Archive 2016 and PI AF

- MARC-WEB-SQL has the PI SQL DAS 2016 installed

- MARC-WIN8 has the client tools including PI JDBC and Eclipse.

 

 

Here are the steps:

 

  1. Open Eclipse
  2. Click on File --> New --> Java Project. The window below will be opened:
  3. Type a project name, a location and click on Next.



  4. On the next window, you should define the Java Settings, click on the Libraries ribbon and then on "Add External JARs..." button.
  5. Find the PIJDBCDriver.jar file under the %PIHOME64%\JDBC folder.



  6. The new project is available on the package explorer.



  7. Expand the root folder of the project, right-click on the folder src and click on New --> Class.
  8. Type a Name and make sure to check the "public static void main(String[] args)" option.



  9. Replace the content of the new created file with the content below:

    import java.io.Console;
    import java.sql.Connection;
    import java.sql.DatabaseMetaData;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.Properties;
    
    
    
    
    public class SampleAppMain {
    
    
      public static void main(String[] args) {
    
      Console console = System.console();
      String dasName = "MARC-WEB-SQL";
      String dataSourceName = "MARC-PI2016";
      String isTrustedConnection = "Yes";
      String protocolOrder = "nettcp:5462";
      String logLevel = "0";
      String tagNamePattern = "sin%";
    
    
    
    
      System.out.println("\nArguments:");
      System.out.println("\tData Access Server name: " + dasName);
      System.out.println("\tPI Data Archive name: " + dataSourceName);
      System.out.println("\tUse trusted connection?: " + isTrustedConnection);
      System.out.println("\tProtocol order: " + protocolOrder);
      System.out.println("\tLog level: " + logLevel);
      System.out.println("\tPI tag name/name pattern: " + tagNamePattern + "\n");
    
    
    
      Connection connection = null;
      String url = "";
      String driverClassName = "com.osisoft.jdbc.Driver";
      PreparedStatement pStatement = null;
      ResultSet resultSet = null;
      Properties properties = new Properties();
    
      url = "jdbc:pioledb://" + dasName + "/Data Source=" + dataSourceName + "; Integrated Security=SSPI";
    
    
      properties.put("TrustedConnection", isTrustedConnection);
      properties.put("ProtocolOrder", protocolOrder);
    
      properties.put("LogConsole", "True");
      properties.put("LogLevel", logLevel);
    
      try {
      Class.forName(driverClassName).newInstance();
      connection = DriverManager.getConnection(url, properties);
    
      pStatement = connection.prepareStatement("SELECT tag, value FROM pisnapshot WHERE tag like ?");
    
    
      DatabaseMetaData metaData = connection.getMetaData();
      System.out.println(metaData.getDriverName() + " " + metaData.getDriverVersion()); 
      System.out.println(metaData.getDatabaseProductName());
      System.out.println(metaData.getDatabaseProductVersion() + "\n");  
       
             // bind Parameter representing the Tag name
      pStatement.setString(1, tagNamePattern);
      resultSet = pStatement.executeQuery();
    
    
      // read the data
      while (resultSet.next()) {
      String value, tag;
      tag = resultSet.getString(1);
      value = resultSet.getString(2);
      System.out.println( tag + " " + value);
      }
      resultSet.close();
      pStatement.close();
      }
      catch (Exception ex) {
      System.err.println(ex);
      }
      finally
      {
      try {
      if(resultSet != null) resultSet.close();
      if(connection != null) connection.close();
      } catch (SQLException ex) {
      System.err.println(ex);
      }
      }
      }
    }
    sfsf
    


  10. This example was taken from the sample app available on %PIHOME64%\JDBC\Samples\GetSnap\src\getSnap.java.
  11. Press F11 or click on Run --> Debug to execute your problem. You will see the results on the Console window.

 

 

Hope this is helpful for you! Please share your comments!

Outcomes