24 Replies Latest reply on May 8, 2012 12:25 PM by mhamel

    PitoMATLAB problem lauching ADOBD.connection

    SPR_Glasgow

      Hi there,

       

      I'm trying to use some .m scripts which link matlab through the ActiveX display objects (MDAC components of windows) and then another set of scripts that allow SQL access to PI server. However, I seem to have an issue invoking the ADODB.connection early in this process. Matlab gives the error

       

      ??? Invoke Error, Dispatch Exception:
      Source: ADODB.Connection
      Description: Provider cannot be found. It may not be properly installed.
      Help File: C:\WINDOWS\HELP\ADO270.CHM
      Help Context ID: 12ee4f

       

      Error in ==> adodbcn at 29
      invoke(cn,'Open', cnstr);

       

      Error in ==> PI_WL at 9
      cn = adodbcn(cnstr)

       

      I realise this is a matlab issue and not specifically PI (I am also pursing this on matlab central newsgroup). But thought I'd post here also in case someone has had th same problem and managed to solve.

       

      any help, much appreciated

       

       

       

      David 

        • Re: PitoMATLAB problem lauching ADOBD.connection
          Ahmad Fattahi

          Have you had a chance to look up the example we have in the white paper on the topic "Using PI Data with MATLAB"? You can access that in vCampus Library under "White Papers and Tutorials > Miscellaneous".

            • Re: PitoMATLAB problem lauching ADOBD.connection
              SPR_Glasgow

              I have tried to open the commands from the PI_demo.m, so nothing fancy. Just trying to establish a connnection and retrieve an archive value of a particular PI tag. It seems to be falling over when trying to establish the initial connection, so really have no idea how to solve the problem. I don't have the help file ADO270 in windows\help but have reinstalled MDAC in case this was the issue. Still no luck. Has anyone else had this problem?

               

              regards

               

              David

                • Re: PitoMATLAB problem lauching ADOBD.connection
                  Ahmad Fattahi

                  David,

                   

                  I'm sure you have already installed PI OLEDB provider on the same machine as MATLAB, right? Have you also been able to test the connection through the provider to the PI Server independent of MATLAB, say using PIOLEDB.msc?

                    • Re: PitoMATLAB problem lauching ADOBD.connection
                      SPR_Glasgow

                      Hi Ahmad,

                       

                      I had installed PIOLEDB provider but for some reason it only installed the SDK and not the OLEDB. I have reinstalled but I appear to get an error that it cannot gain a connection. The PI server I access (usually through PI process book) requires a username and passwork. How can I modify the conection string in below to get MATLAB to apply these when it attempts to connect?

                       

                      % Change your PI Server name here

                       

                      server = 'phdsrv'

                       

                      % Build connection string

                       

                      cnstr = strcat('Provider=PIOLEDB.1;Data Source=',server,';Persist Security Info=False')

                       

                      Also I tried using PIOLEDB.msc but I have restrictions on a work machine which does not allow me to use it.

                       

                      hope you can help

                       

                      david

                        • Re: PitoMATLAB problem lauching ADOBD.connection

                          You can add "User ID = user_name; Password = password;" to the connection string. You can consult the PI OLEDB Provider User Guide for complete explanations on Connectrion String options.

                          • Re: PitoMATLAB problem lauching ADOBD.connection

                            Note, it is generally recommended to user Windows Integrated Security ("Integrated Security" option in the Connection String). You can read more on the topic in the Authentication section of the PI OLEDB Provider User Guide as well as the Configuring PI Server Security manual - all available on the vCampus Library.

                              • Re: PitoMATLAB problem lauching ADOBD.connection
                                SPR_Glasgow

                                Thanks for the help on this, got is working now and just figuring out the conection strings.

                                 

                                I am trying to pull in data for a number of PItags into a large cell array, however the code seems to only allow pulling in one tag at a time, can this be altered simply

                                 

                                SampledData = PI_SampledData(server,'cdt158','*-1h','*','5m',cn)

                                 

                                I have tried adding an '*' with just the common part of the tag but with no success

                                 

                                hope someone can help

                                 

                                dave

                                  • Re: PitoMATLAB problem lauching ADOBD.connection
                                    mhamel

                                    @David: Using PI OLEDB provider, you can perfom queries that interpolates for multiple tags. You can use one such as below and replace the tag1, tag2, tagn, time1, time2 and interval tokens by the values received by the Matlab function.

                                     
                                    SELECT TAG, TIME, VALUE
                                    FROM PIARCHIVE..PIINTERP2
                                    WHERE TAG IN ('TAG1', 'TAG2', 'TAGn')
                                    AND TIME BETWEEN 'Time1' AND 'Time2'
                                    AND TIMESTEP = 'Interval'
                                    ORDER BY TAG, TIME
                                    

                                     

                                     

                                    I suggest you to take a look at the PI OLEDB provider user guide that you can find from the Library section of vCampus (Library > vCampus PI Products Kit > Data Access Technologies > PI OLEDB Provider Manual).

                                      • Re: PitoMATLAB problem lauching ADOBD.connection
                                        SPR_Glasgow

                                        Thank you for the reply,

                                         

                                        apologies for asking a basic question. I am using scripts in matlab. How woudl I wrap this sql script into a matlab code so it was executed and returned to matlab?

                                         

                                        regards

                                         

                                        dave

                                          • Re: PitoMATLAB problem lauching ADOBD.connection
                                            SPR_Glasgow

                                            Hi again,

                                             

                                            To be more specific I am using a MATLAB script that creates an SQL query and then I invoke the adodbquery.m function to retrieve the data foe 3 PI tags

                                             

                                            % Build connection string

                                             

                                            cnstr = strcat('Provider=PIOLEDB.1;Data Source=phdsrv;User ID=ldave;Password=ldave;Persist Security Info=False')

                                             

                                            % Open connection

                                             

                                            cn = adodbcn(cnstr)

                                             

                                            % retrieving ave windspeed and power for WL_T100

                                             

                                            %Sample query to execute

                                             

                                            sql='SELECT TAG, TIME, VALUE FROM PIARCHIVE..PIINTERP2 WHERE TAG IN (''WWL_T1_WindSpeed_10MIN_AVG'', ''WWL_T2_WindSpeed_10MIN_AVG'', ''WWL_T3_WindSpeed_10MIN_AVG'' AND TIME BETWEEN ''30/01/2012 00:00:00'' AND ''31/01/2012 23:50:00''AND TIMESTEP = ''10m'' ORDER BY TAG, TIME';

                                             

                                            x=adodbquery(cn,sql);

                                             

                                            invoke(cn,'close');

                                             

                                            however it throws back the error that a variable 'r' is undefined. r is explicitly defined in the function adodbquery.m so I don't knwo how to solve this problem. I include the script adodbquery.m below which is part of the PitoMATLAB.zip from OSI

                                             

                                            function x=adodbquery(cn,sql)

                                             

                                            % [x]=adodbquery(cn,sql)

                                             

                                            %

                                             

                                            % adoledbquery    Executes the sql statement against the connection cn

                                             

                                            %

                                             

                                            % Inputs:

                                             

                                            %   cn,     open connection to ADO OLEDB ActiveX Data Source Control

                                             

                                            %   sql,    SQL statement to be executed

                                             

                                            %

                                             

                                            % Output

                                             

                                            %   x,      cell array of query results

                                             

                                            %

                                             

                                            % Notes: Convert cells to strings using char. Convert cells to numeric

                                             

                                            % data using cell2mat() for ints or double(cell2mat()) for floats

                                             

                                            %

                                             

                                            % This code basis on Tim Myers code (oledb*.m) and use ADO OLE DB instead

                                             

                                            % of OWC - Office Web Component

                                             

                                            %

                                             

                                            % Martin Furlan

                                             

                                            % martin.furlan@iskra-ae.com

                                             

                                            % January 2007

                                             

                                            %

                                             

                                            % Changed "invoke(r,'release');" for "invoke(r,'close');

                                             

                                            % By Steve Pilon (spilon@osisoft.com) August 2009

                                             

                                            %open recordset and run query

                                             

                                            invoke(cn,'BeginTrans');

                                             

                                            try

                                             

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

                                             

                                             invoke(cn,'CommitTrans');

                                             

                                             sclSuccess = 1;

                                             

                                            catch

                                             

                                             invoke(cn,'RollbackTrans');

                                             

                                             sclSuccess = 0;

                                             

                                            end  

                                             

                                            %retrieve data from recordset

                                             

                                            if r.recordcount>0

                                             

                                               x=invoke(r,'getrows');

                                             

                                               x=x';

                                             

                                            else

                                             

                                               x=[];

                                             

                                            end

                                             

                                            %close recordset

                                             

                                            invoke(r,'close');

                                              • Re: PitoMATLAB problem lauching ADOBD.connection
                                                mhamel

                                                @David: I would suspect that your query has a syntax problem and this is why the r variable is not populated with records sent by the ADODB connection. Try your query with the PI OLEDB Tester tool or the PI SQL Commander to verify its validity. I am not 100% sure but I think the lack of space between your second time boundary and the timestep field might be the culprit.

                                                  • Re: PitoMATLAB problem lauching ADOBD.connection
                                                    SPR_Glasgow

                                                    Thank you, as I was inputting to OLEDB tester I knowtest a missed ) sorry for being an idiot! Thank you for your help, I will use OLEDB tester in future to test my SQL queries direct

                                                     

                                                    cheers

                                                     

                                                    dave

                                                      • Re: PitoMATLAB problem lauching ADOBD.connection
                                                        SPR_Glasgow

                                                        One last question,

                                                         

                                                        I'm trying to create an SQL query that will return data from 3 tags in columns, but I wish to timestep at 10m intervals. Can someone help modify the query below please

                                                         

                                                        SELECT A.TIME as "Times", A.VALUE as "Tag A", B.VALUE as "Tag B", C.VALUE as "Tag C"

                                                         

                                                        FROM                

                                                         

                                                        PICOMP2 A INNER JOIN PIINTERP2 B                

                                                         

                                                        ON B.TIME = A.TIME INNER JOIN PIINTERP2 C                

                                                         

                                                        ON C.TIME = A.TIME

                                                         

                                                        WHERE                

                                                         

                                                        A.TAG = 'WWL_T1_WindSpeed_10MIN_AVG'          

                                                         

                                                        AND B.TAG = 'WWL_T2_WindSpeed_10MIN_AVG'

                                                         

                                                        AND C.TAG = 'WWL_T3_WindSpeed_10MIN_AVG'

                                                         

                                                        AND A.TIME BETWEEN '30/01/2012 00:00:00' AND '31/01/2012 23:50:00'

                                                         

                                                        AND A.TIMESTEP = '10m'

                                                         

                                                        I'm assuming I've got the timestep line wrongly included,

                                                         

                                                        regards

                                                         

                                                        david

                                                          • Re: PitoMATLAB problem lauching ADOBD.connection
                                                            mhamel

                                                            @David: The operation you are looking for is pivoting the data. I have added an example that will let you pivot them efficiently. If you need to compare more than 3 values, you add more items in the adhoc table formed of UNION keywords and add another MAX aggregate line to report the value next to the others.

                                                             

                                                            As another hint, when you join PICOMP2 and PIINTERP2 (or PICOMP and PIINTERP) you will synchronize the data generated by the PIINTERP2 table with the timestamps retrieved from the PICOMP2. If you only want to sample your data, you use PIINTERP2 for all tags.

                                                             
                                                            SELECT
                                                                 T1.TIME,
                                                                 MAX(CASE WHEN T2.ID = 1 THEN CAST(T1.VALUE AS FLOAT32) ELSE NULL END) AS VALUE1,
                                                                 MAX(CASE WHEN T2.ID = 2 THEN CAST(T1.VALUE AS FLOAT32) ELSE NULL END) AS VALUE2,
                                                                 MAX(CASE WHEN T2.ID = 3 THEN CAST(T1.VALUE AS FLOAT32) ELSE NULL END) AS VALUE3
                                                            FROM     
                                                                 PIARCHIVE..PIINTERP2 T1
                                                                 INNER JOIN
                                                                 
                                                                 (SELECT 1 ID, 'WWL_T1_WindSpeed_10MIN_AVG' TAG
                                                                  UNION
                                                                  SELECT 2 ID, 'WWL_T2_WindSpeed_10MIN_AVG' TAG
                                                                  UNION
                                                                  SELECT 3 ID, 'WWL_T3_WindSpeed_10MIN_AVG' TAG) T2
                                                                  
                                                                      ON T2.TAG = T1.TAG
                                                            WHERE          
                                                                 T1.TIME BETWEEN '*-7d' AND '*'
                                                                 AND TIMESTEP = '10m'
                                                            GROUP BY T1.TIME
                                                            ORDER BY T1.TIME
                                                            

                                                             

                                                             

                                                            Let me know if that answered your question.

                                                              • Re: PitoMATLAB problem lauching ADOBD.connection
                                                                SPR_Glasgow

                                                                Mathieu,

                                                                 

                                                                Thank you for the code and help on this sql. Unfortunately I now have an unrelated problem that the connection will not access the PI server. I have no idea why I cannot establish a connection as I have done nothing over the weekend. I get the error from matlab

                                                                 

                                                                ?? Invoke Error, Dispatch Exception:

                                                                 

                                                                Source: PIOLEDB

                                                                 

                                                                Description: [PI SDK] ServerID returned from server does not match that

                                                                 

                                                                currently configured on this machine.  The server at the path for the opened

                                                                 

                                                                handle may have changed.

                                                                 

                                                                Error in ==> adodbcn at 29

                                                                 

                                                                invoke(cn,'Open', cnstr);

                                                                 

                                                                Error in ==> PI_WL_SQL at 9

                                                                 

                                                                cn = adodbcn(cnstr)

                                                                 

                                                                I also get the same error when trying to connect with OLEDB tester. Does anyone know why this is happening. I can still use PI datalink to connect in excel without any problems.

                                                                 

                                                                Hope someone can help

                                                                 

                                                                regards

                                                                 

                                                                David

                                                                  • Re: PitoMATLAB problem lauching ADOBD.connection
                                                                    mhamel

                                                                    @David: When a call is made to Server.Open, after establishing a connection, the PI SDK (used by PI OLEDB provider behind the scene) retrieves the Server ID from the server and compares it to a value stored in the Known Servers Table on the local machine. When the locally stored value does not match that returned by the server the open call returns pseSERVERIDMISMATCH (which is the message: "[PI SDK] ServerID returned from server does not match that currently configured on this machine. The server at the path for the opened handle may have changed."). This return is intended as a warning however COM calls only support error returns. Because the return is informational, the connection is left open and if ignored the application proceeds normally.

                                                                     

                                                                    The purpose of this behavior is to detect when an existing PI server has been removed and a new PI server installed in its place.

                                                                     

                                                                    One way to fix the problem would consist in opening your AboutPI-SDK.exe or PISDKUtility.exe and connect to your PI Server from the machine you are using with MATLAB. If the Server ID has changed, you will be prompted to store the new ID locally. This should fix your problem.

                                                                      • Re: PitoMATLAB problem lauching ADOBD.connection
                                                                        SPR_Glasgow

                                                                        Hi Mathieu,

                                                                         

                                                                        I've hit the same connection problem again unfortunately and cannot resolve the issue. I hope you can offer some assistance?

                                                                         

                                                                        We have an excel plugin that allows connection to PI server so we can pull data in. I have no problems using that Pi connection manager to initialise a session with excel. The problem arrises with PISDKutility which keeps throwing up an error that the the serverID returned does not match the one currently stored on the machine. It does not offer any option to refresh this or update. Does thris sound a familiar problem?

                                                                         

                                                                        kind regards

                                                                         

                                                                        David

                                                                          • Re: PitoMATLAB problem lauching ADOBD.connection
                                                                            mhamel

                                                                            @David: I have not seen much of this error before. Your problem seems more on the side of a technical issue. I would suggest to use our technical support service (you can reach it using this link) to solve your problem. A technical agent will be capable of entering remotely to your machine and dig the problem with you. I would suggest you give this thread URL in your call as reference.

                                                                             

                                                                            Please do not hesitate to initiate a new discussion thread if you have any other question related to PI System and MATLAB integration.

                                                                      • Re: PitoMATLAB problem lauching ADOBD.connection
                                                                        SPR_Glasgow

                                                                        Mathieu,

                                                                         

                                                                        Got the connection working again. However, the code above runs into a timeout after about 60s. I limited it to values from the last hour and it still hangs. I'm trying to understand the SQL syntax from the PIOLEDB manual, but with little success. I'm testing this in PIOLEDB tester so not a matlab problem at the moment.

                                                                         

                                                                        hope you can offer some ideas

                                                                         

                                                                        regards

                                                                         

                                                                        David

                                                                          • Re: PitoMATLAB problem lauching ADOBD.connection
                                                                            mhamel

                                                                            @David: Could you share the query you are trying with the PI OLEDB tester? A mistake in a query may lead to an extensive call to the PI Server to recover data.

                                                                              • Re: PitoMATLAB problem lauching ADOBD.connection
                                                                                SPR_Glasgow

                                                                                Hi Mathieu,

                                                                                 

                                                                                I am trying the code you posted and it is causing tha time out. I shortened the time frame from -7h to -1h but still not working. Hope you can help?

                                                                                 

                                                                                SELECT

                                                                                 

                                                                                     T1.TIME,

                                                                                 

                                                                                     MAX(CASE WHEN T2.ID = 1 THEN CAST(T1.VALUE AS FLOAT32) ELSE NULL END) AS VALUE1,

                                                                                 

                                                                                     MAX(CASE WHEN T2.ID = 2 THEN CAST(T1.VALUE AS FLOAT32) ELSE NULL END) AS VALUE2,

                                                                                 

                                                                                     MAX(CASE WHEN T2.ID = 3 THEN CAST(T1.VALUE AS FLOAT32) ELSE NULL END) AS VALUE3

                                                                                 

                                                                                FROM     

                                                                                 

                                                                                     PIARCHIVE..PIINTERP2 T1

                                                                                 

                                                                                     INNER JOIN

                                                                                 

                                                                                     (SELECT 1 ID, 'WWL_T1_WindSpeed_10MIN_AVG' TAG

                                                                                 

                                                                                      UNION

                                                                                 

                                                                                      SELECT 2 ID, 'WWL_T2_WindSpeed_10MIN_AVG' TAG

                                                                                 

                                                                                      UNION

                                                                                 

                                                                                      SELECT 3 ID, 'WWL_T3_WindSpeed_10MIN_AVG' TAG) T2

                                                                                 

                                                                                          ON T2.TAG = T1.TAG

                                                                                 

                                                                                WHERE          

                                                                                 

                                                                                     T1.TIME BETWEEN '*-7d' AND '*'

                                                                                 

                                                                                     AND TIMESTEP = '10m'

                                                                                 

                                                                                GROUP BY T1.TIME

                                                                                 

                                                                                ORDER BY T1.TIME

                                                                                 

                                                                                thank you for all the help on this

                                                                                 

                                                                                regards

                                                                                 

                                                                                david

                                                                                  • Re: PitoMATLAB problem lauching ADOBD.connection
                                                                                    mhamel

                                                                                    @David: Sorry, I forgot to restrict to a tag list on the PIINTERP2 table. The timeout you are experiencing comes with the fact that an interpolation is requested on all tags of your PI Server. I have added an example that will let you pivot efficiently (this time by not dragging all the resources of your PI Server). As another hint you can activate the logging feature of PI OLEDB provider by adding Log Level=1;Log File=c:\temp\yourfile.log; to your connection string. You will be able to see how your query was translated into one or many PI SDK calls. I suggest that you consult the PI OLEDB SQL Optimization White Paper that you can find in the vCampus Library (Library > White Papers and Tutorials > White Paper - PI OLEDB SQL Optimization).

                                                                                     
                                                                                    SELECT
                                                                                         T1.TIME,
                                                                                         MAX(CASE WHEN T2.ID = 1 THEN CAST(T1.VALUE AS FLOAT32) ELSE NULL END) AS VALUE1,
                                                                                         MAX(CASE WHEN T2.ID = 2 THEN CAST(T1.VALUE AS FLOAT32) ELSE NULL END) AS VALUE2,
                                                                                         MAX(CASE WHEN T2.ID = 3 THEN CAST(T1.VALUE AS FLOAT32) ELSE NULL END) AS VALUE3
                                                                                    FROM     
                                                                                         PIARCHIVE..PIINTERP2 T1
                                                                                         INNER JOIN
                                                                                         
                                                                                         (SELECT 1 ID, 'WWL_T1_WindSpeed_10MIN_AVG' TAG
                                                                                          UNION
                                                                                          SELECT 2 ID, 'WWL_T2_WindSpeed_10MIN_AVG' TAG
                                                                                          UNION
                                                                                          SELECT 3 ID, 'WWL_T3_WindSpeed_10MIN_AVG' TAG) T2
                                                                                          
                                                                                              ON T2.TAG = T1.TAG
                                                                                    WHERE
                                                                                         T1.TAG IN ('WWL_T1_WindSpeed_10MIN_AVG', 'WWL_T2_WindSpeed_10MIN_AVG', 'WWL_T3_WindSpeed_10MIN_AVG')          
                                                                                         AND T1.TIME BETWEEN '*-7d' AND '*'
                                                                                         AND TIMESTEP = '10m'
                                                                                    GROUP BY T1.TIME
                                                                                    ORDER BY T1.TIME
                                                                                    

                                                                                     

                                                                                     

                                                                                    Let me know if that answered your question.

                                                                                      • Re: PitoMATLAB problem lauching ADOBD.connection
                                                                                        SPR_Glasgow

                                                                                        Mathieu,

                                                                                         

                                                                                        Thank you for the query, it works a treat :-)

                                                                                         

                                                                                        I will download the SQL paper you mention as I understadn very basic SQL query for filtering peoples names for a database, but this is another level!

                                                                                         

                                                                                        thank you very much for all your help

                                                                                         

                                                                                        dave

                                                                                          • Re: PitoMATLAB problem lauching ADOBD.connection

                                                                                            Now that you've got things running with the classic PI OLEDB Provider, I would like to kindly invite you to look into the "new way of doing things" - that is, the PI Asset Framework (AF) and the PI OLEDB Enterprise provider instead.

                                                                                             

                                                                                            As you can see, the classic PI OLEDB Provider makes it pretty hard to 'pivot' data... we see this as a very powerful thing to do and this is something we addressed in PI OLEDB Enterprise. We could address it because in PI AF everything is better organized and there is this notion of templates (contrarily to the good old flat list of tags in the PI Data Archive).

                                                                                             

                                                                                            So, whenever you have a chance, try to have a look at PI OLEDB Enterprise and "Transpose Functions". There already are discussion threads and other materials on the topic, but please do not hesitate to initiate a new discussion thread if you have a hard time finding those or simply need further assistance!