AnsweredAssumed Answered

how do you work with prepared statements with OSIPI JDBC and timestamp ranges

Question asked by sunilbarboza on Nov 24, 2015
Latest reply on Nov 25, 2015 by Marcos Vainer Loeff

I modified the getSnap.java program to query a set of tag records from the picomp table within a timerange. ( see the code snippet below). I need to parameterize the time range but I am not having much luck.

If I hard code the time range as shown in the first example  I get an accurate set of records.

If I use a parameter for the 'time' column, the PI JDBC driver and/or the data access server ignores the time column and  returns the complete set of rows.

-----  start ------------------------------------------------------------------------------------

Class.forName(driver).newInstance();

  con = DriverManager.getConnection(url, plist);

  stmt = con.prepareStatement("SELECT tag, value, status, time FROM piarchive.picomp WHERE tag like ? and time > '*-1d' ");

  

         DatabaseMetaData md = con.getMetaData();

  System.out.println(md.getDriverName() + " " + md.getDriverVersion());

  System.out.println(md.getDatabaseProductName());

  System.out.println(md.getDatabaseProductVersion() + "\n"); 

  

         // bind Parameter representing the Tag name

  stmt.setString(1, tagp);

            //stmt.setString(2, "*-1d");

  rs = stmt.executeQuery();

 

  while (rs.next()) {

  String value, tag, status, ts_string;

  tag = rs.getString(1);

  value = rs.getString(2);

                status = rs.getString(3);

                ts_string = rs.getString(4);

 

 

  System.out.println(tag+" "+value+" ["+status+"] ["+ ts_string+"] ");

  }

  rs.close();

  stmt.close();

-----  end  ------------------------------------------------------------------------------------

 

The following ignores the set of the time parameter  ( only including the lines that change )  and returns all rows.

 

 

-------  start -----------------------------------------------------------------
stmt = con.prepareStatement("SELECT tag, value, status, time FROM piarchive.picomp WHERE tag like ? and time > '?' );
        
stmt.setString(1, tagp);

    stmt.setString(2, "*-1d");

        -------  end -----------------------------------------------------------------

 

If I use a prepared statement without quotes for the time parameter , then I get a DB_E_PARAMNOTOPTIONAL error ( only including the lines that change )

------   start --------------------------------------

stmt = con.prepareStatement("SELECT tag, value, status, time FROM piarchive.picomp WHERE tag like ? and time > ?  );

stmt.setString(1, tagp );

stmt.setString(2, "*-1d");

--------- end--------------------------------------

Outcomes