8 Replies Latest reply on Jul 14, 2016 2:25 PM by Marcos Vainer Loeff

    Passing timestep value as a parameter for OLEDB query using c#

    Suganya_Ganesan

      Hi,


      I have a WCF service running to fetch daily and monthly readings from PI point.


      I am using the below OLEDB query to fetch interpolated value

      SELECT * FROM PIARCHIVE..PIINTERP2 WHERE TAG = ? AND TIME >= ? AND TIME <= ? AND TIMESTEP = ?

      When i am trying to pass the values as parameters to the query i am getting below exceptions:

       

      Case 1: When the value of the Timestep parameter is "1d".

                      cmd.Parameters.AddWithValue("@interval", "'1d'");

       

      Exception message:

      'PIOLEDB' failed with no error message available, result code: DB_E_PARAMNOTOPTIONAL(0x80040E10).


      Case 2: When the value of the timestep parameter is passed as a TimeSpan object. 

       

                     TimeSpan ts = new TimeSpan(1,0,0,0);
                     cmd.Parameters.AddWithValue("@interval", ts);


      Exception message:

      Query into 'piarchive..piinterp2 piinterp2' table is invalid. Column 'timestep' cannot be set to NULL.

       

      Apparently the problem is with Timestep parameter only, rest others are OK,

       

      Also, the query works fine when value of timestep is passed in the query.

       

       

      Please help!

       

      Thanks in advance.

        • Re: Passing timestep value as a parameter for OLEDB query using c#
          Marcos Vainer Loeff

          Hello Suganya,

           

          This code work for me:

           

              {
                  static void Main(string[] args)
                  {
                      string query = "SELECT * FROM PIARCHIVE..PIINTERP2 WHERE TAG = ? AND TIME >= ? AND TIME <= ? AND TIMESTEP = ?";
                      OleDbConnection cnn = new OleDbConnection("Provider=PIOLEDB; Data Source=MARC-PI2016; Integrated Security=SSPI;");
                      OleDbCommand cmd = new OleDbCommand(query, cnn);
                      cmd.Parameters.AddWithValue("@value1", "sinusoid");
                      cmd.Parameters.AddWithValue("@value2", DateTime.Now.AddDays(-1));
                      cmd.Parameters.AddWithValue("@value3", DateTime.Now);
                      cmd.Parameters.AddWithValue("@value4", new TimeSpan(1,0,0));
                      //cmd.Parameters.AddWithValue("@value4", "1h");
          
          
                      try
                      {
                          cnn.Open();
                          if (query.Substring(0, 6).ToUpper() == "SELECT")
                          {
                              OleDbDataAdapter dta = new OleDbDataAdapter(cmd);
                              DataSet ds = new DataSet();
                              dta.Fill(ds, "Values");
                              Console.WriteLine("OK Query");
                          }
                          else
                          {
                              cmd.ExecuteNonQuery();
                              Console.WriteLine("OK Non Query");
                          }
          
          
                      }
                      catch (OleDbException ex)
                      {
                          Console.WriteLine(ex.Message);
                      }
                      catch (Exception ex)
                      {
                          Console.WriteLine(ex.Message);
                      }
                      finally
                      {
                          cnn.Close();
                      }
                      Console.ReadKey();
                  }
              }
          

           

          Please let me know if this works for you!

            • Re: Passing timestep value as a parameter for OLEDB query using c#
              Suganya_Ganesan

              Thanks Marcos.

               

              It works with the above code for "1h" interval, but when I changed it to "1d", it doesn't work.

              cmd.Parameters.AddWithValue("@value4", new TimeSpan(1,0,0,0))

               

              I require this for "1d" and "1mo", as I need to fetch daily and monthly readings.

                • Re: Passing timestep value as a parameter for OLEDB query using c#
                  Marcos Vainer Loeff

                  Hi,

                   

                  I found a solution using the SQL Time function:

                   

                     class Program
                      {
                          static void Main(string[] args)
                          {
                              string query = "SELECT * FROM PIARCHIVE..PIINTERP2 WHERE TAG = ? AND TIME >= ? AND TIME <= ? AND TIMESTEP = TIME(?)";
                              OleDbConnection cnn = new OleDbConnection("Provider=PIOLEDB; Data Source=MARC-PI2016; Integrated Security=SSPI;");
                              OleDbCommand cmd = new OleDbCommand(query, cnn);
                              cmd.Parameters.AddWithValue("@value1", "sinusoid");
                              cmd.Parameters.AddWithValue("@value2", DateTime.Now.AddDays(-5));
                              cmd.Parameters.AddWithValue("@value3", DateTime.Now);
                              cmd.Parameters.AddWithValue("@value4", "1d");
                              //cmd.Parameters.AddWithValue("@value4", "1h");
                  
                  
                              try
                              {
                                  cnn.Open();
                                  if (query.Substring(0, 6).ToUpper() == "SELECT")
                                  {
                                      OleDbDataAdapter dta = new OleDbDataAdapter(cmd);
                                      DataSet ds = new DataSet();
                                      dta.Fill(ds, "Values");
                                      for (int i = 0; i<ds.Tables[0].Rows.Count; i++)
                                      {
                                          object[] itemArray = ds.Tables[0].Rows[i].ItemArray;
                                          Console.WriteLine(itemArray[1].ToString());
                                      }
                                      Console.WriteLine("OK Query");
                                  }
                                  else
                                  {
                                      cmd.ExecuteNonQuery();
                                      Console.WriteLine("OK Non Query");
                                  }
                  
                  
                              }
                              catch (OleDbException ex)
                              {
                                  Console.WriteLine(ex.Message);
                              }
                              catch (Exception ex)
                              {
                                  Console.WriteLine(ex.Message);
                              }
                              finally
                              {
                                  cnn.Close();
                              }
                              Console.ReadKey();
                          }
                      }
                  

                   

                  Please let me know if this works for you!