6 Replies Latest reply on Jan 20, 2016 9:22 AM by pthivierge Branched from an earlier discussion.

    PowerShell: Issue passing TimeStamp into PI OLEDB Enterprise query

    Rashmi

      I'm facing the a time stamp issue for the following query.

       

      $queryTimeStamp = "{0:dd/MM/yyyy HH:mm:ss}" -f $timeStamp
      
      SELECT eh.Name towercode,tc1.height,tc."100speed",tc."90speed",tc."80speed1",tc."80speed2",
      
      

       

      tc."60speed",tc."30speed",tc."10speed",tc."100direction",tc."90direction",tc."60direction" ,

      tc."30direction",tc."10direction" ,tc."80temperature",tc."10temperature",tc."80humidity",

      tc."10humidity",tc."80pressure",tc."10pressure"

      FROM(SELECT Date('" + $queryTimeStamp + "')Time) t, [HYREF].[Asset].[ElementHierarchy] eh

          CROSS APPLY [HYREF].[DataT].[TransposeArchive_WindTowerTemplate](eh.ElementID, t.Time,t.Time) tc

          CROSS APPLY [HYREF].[DataT].[TransposeSnapshot_WindTowerTemplate](eh.ElementID)tc1

          WHERE eh.Path like '%\%' and eh.Name ='F4401W0001H0060'

          OPTION (FORCE ORDER, IGNORE ERRORS, EMBED ERRORS)

       

      When I run the above SQL in powershell script, the  timestamp prints incorrect way:

       

      ForEach-Object : Exception calling "Open" with "4" argument(s): "[PI SDK] The time is invalid." + 10/01/201

      ""

      At D:\Windpower\SSE\PI_Windpower_Script.ps1:35 char:11

      + 0..143 | % <<<<  {

          + CategoryInfo          : NotSpecified: (:) [ForEach-Object], MethodInvocationException

          + FullyQualifiedErrorId : ComMethodTargetInvocation,Microsoft.PowerShell.Commands.ForEachObjectCommand

       

      The below lines are printing in From clause (look at the date format),

       

      FROM(SELECT Date('" + 10/01/2016 00:00:00 + "') Time) t, [HYREF].[Asset].[ElementHierarchy] eh

       

          CROSS APPLY [HYREF].[DataT].[TransposeInterpolateDiscrete_WindTowerTemplate](eh.ElementID, t.Time) tc

          WHERE eh.Path like '%\%' and eh.Name like 'F4401%'

          OPTION (FORCE ORDER, IGNORE ERRORS, EMBED ERRORS)

      But when I hardcode the value as 10/01/2016 or any other day, the script is running OK. Any suggestion pls? The same variable ($queryTimestamp) executes OK for other SQLs although.

       

      Regards,Rashmi

        • Re: PowerShell: Issue passing TimeStamp into PI OLEDB Enterprise query
          Roger Palmen

          Rashmi,

          It's a better idea to create a new topic op PI Square for a new issue. That way more people wil notice and will be able to assist.

          • Re: PowerShell: Issue passing TimeStamp into PI OLEDB Enterprise query
            gregor

            Hello Rashmi,

             

            As Roger said, posting an additional question to a question that has an answer marked correct doesn't generate much attention. Please note that I have branched your new question out of this other one.

            Without knowing of what type $timestamp is, it's a bit difficult to understand what's going wrong for you. I have assumed $timestamp is of type System.DateTime and simplified the query but below is a complete example on how one could execute queries against PI OLEDB Enterprise through PowerShell.

             

            # Please change the value of $PIServerName to refer your PI Data Archive host
            $PIServerName = "PIServer"
            
            # Create the connection string
            $PIOLEDB_Connection_String = "Provider=PIOLEDB;Data Source=$PIServerName;Integrated Security=SSPI;"
            
            # Create TimeStamp of type Syste3m.DateTime and convert it to string
            $TimeStamp = [System.DateTime]::Now
            $queryTimeStamp = "{0:dd/MM/yyyy HH:mm:ss}" -f $timestamp
            
            # Build the query string
            $PIOLEDB_Query_String = "SELECT DATE('" + $queryTimeStamp + "') as Time"
            
            # Create OLEDBConnection using the predefined connection string
            $PIOLEDB_Connection = New-Object System.Data.OLEDB.OleDbConnection($PIOLEDB_Connection_String)
            
            # Open the connection
            $PIOLEDB_Connection.Open()
            
            # Create the OLEDB Command object, refer the query string and the connection
            $PIOLEDB_Command = New-Object system.Data.OleDb.OleDbCommand($PIOLEDB_Query_String,$PIOLEDB_Connection)
            
            # Create reader object and execute the command
            $Reader = $PIOLEDB_Command.ExecuteReader()
            
            # Get the amount of columns 
            $ColumnCount = $Reader.FieldCount
            
            # Create an object array to hold the results
            $Results = @()
            
            # Repeat reading while there is content
            while ($Reader.Read()) 
            {
                # Create result object as PSObject
                # There are other options but this helps tremendously with 
                # formatting when returning results to the console
                $Result = New-Object psobject        
                
                # Read row content column by column
                for ($iColumn = 0; $iColumn -lt $ColumnCount; $iColumn++)
                {
                    # Title
                    $ColumnHeader = $Reader.GetName($iColumn)
                    # Value
                    $ColumnValue = $Reader.GetValue($iColumn).ToString()
                    # Assign result to $Result object
                    $Result | Add-Member -type NoteProperty -name ($ColumnHeader) -value ($ColumnValue)
                }
                # Add $Result to the $Results array
                $Results += $Result
            }
            
            # Output $Results to console
            $Results
            
            # Close the reader and the connection
            $Reader.Close()
            $PIOLEDB_Connection.Close()
            
            2 of 2 people found this helpful
            • Re: PowerShell: Issue passing TimeStamp into PI OLEDB Enterprise query
              Roger Palmen

              Looking at the SQL, you try to pass the date string " + 10/01/2016 00:00:00 + " into the Date function and you pass in a datetime string, starting with "+ ".

               

              I suspect that dropping the "+ " will help, as you mentioned yourselves by stating that the string "10/01/2016" does work ok.

              1 of 1 people found this helpful