AnsweredAssumed Answered

sql call returning empty recordset

Question asked by SPR_Glasgow on Oct 17, 2017
Latest reply on Oct 20, 2017 by maristone

Hi there,

I'm making use of the scripts provided by the whitepaper 'using PI with Matlab'. However, I am making a call with sql to return interpolated values from 10min averages in PI

 

sql = select tag,time,value FROM piarchive..piinterp2

WHERE tag ='SITE1.Met1.WMET1.AvMetAlt2HorWdDir'

and time between '23/07/2017 00:00:00' and '25/07/2017 00:00:00'

and timestep='10m'

 

using the call below (and the associated scripts for invoking)

 

raw = PIinterface(sql);

 

function [data,varargout] = PIinterface(query)

        % Create ActiveX control

        cn = actxserver('ADODB.Connection');

        set(cn,'CursorLocation',3);

       

        % Open connection

        invoke(cn,'Open',strcat('Provider=PIOLEDB.1;Data Source=xxxxxxx;Persist Security Info=True;Time Zone=UTC;Timestamp Interval Start=True'));

       

        % Specify connection timeout

        set(cn,'CommandTimeout',60);

       

        % Open recordset and run query

        invoke(cn,'BeginTrans');

        try

            r = invoke(cn,'Execute',query);

            invoke(cn,'CommitTrans');

        catch

            invoke(cn,'RollbackTrans');

        end

 

However, it is returning an empty recordset (r.recordcount = 0) even when I know there is data over the time period from using PI OLEDBTester.exe directly with the sql call mentioned above.

 

it works fine when calculating averages, it seems to be the interpolating that is casuing a problem but I don'y know how to fix it.

 

hope someone can help

 

D

Outcomes