4 Replies Latest reply on Aug 21, 2013 5:19 AM by faiz

    RDBMSPI, error on timestamp format

    faiz

      Hi,

       

      Any one can help me on the issue state below.

       

      Currently i'm connecting PI Server to retrieve data from dBase IV database (.dbf) file. The dBase table contain column name, DATE, TIME, TAGNAME, VAR_STATUS,VAR_EVENT.

       

      We have create PI Point with the name same as the TAGNAME.

       

      If i'm using at the exDesc /SQL="SELECT VAR_STATE as PI_VALUE,EVENT_TYPE as PI_STATUS FROM JOURNAL WHERE TAGNAME='SOE_TAG01' ORDER BY TIME DESC;"

       

      The PI server have no problem to retrive the data and pass the timestamp with time it execute the SQL (i'm using the scan class of 5 second)

       

      But when we select with include the timestamp we facing this error. FYI the DATE,TIME is separate column so in my SQL statement use the join column to present the PI_TIMESTAMP.

       

      /SQL="SELECT (DATE + ' ' + TIME)  as PI_TIMESTAMP,VAR_STATE as PI_VALUE,EVENT_TYPE as PI_STATUS FROM JOURNAL WHERE TAGNAME='SOE_TAG01' ORDER BY TIME DESC;"

       

      Error message (column PI_TIMESTAMP is not of timestamp data type!), with result at PI value show 'Bad Input'

       

      Mon Aug 19 17:09:23 2013 [IFC-E]: bindColAli> Point - SOE_TAG01 : column PI_TIMESTAMP is not of timestamp data type!
      Mon Aug 19 17:09:23 2013 [IFC-E]: executeTag> Point - SOE_TAG01 : SELECT (DATE + ' ' + TIME)  as PI_TIMESTAMP,VAR_STATE as PI_VALUE,EVENT_TYPE as PI_STATUS FROM JOURNAL WHERE TAGNAME='SOE_TAG01' ORDER BY TIME DESC

       

      Appreciate any advice on how to troubleshoot this.

       

       

       

      Regards

        • Re: RDBMSPI, error on timestamp format
          xwang

          Hi,

           

          what is the data type of DATE and TIME please?  I believe this error is caused by data tpye cannot be identified.  I think you could try to cast the DATE and TIME to string type, and link them together.  And then, put this string type into PI_TIMESTAMP column.

           

          Xi Wang

           

          v-Campus team

            • Re: RDBMSPI, error on timestamp format
              faiz

              Tq Xi Wang,

               

              Tech support has reply the above matter

               

              -----------------------------------

               

              From the user manual,

               

              dBase III, dBase IV

               

              Date and Time Data Type

               

              dBase does not have any native timestamp data type. If sending PI timestamps to dBase, the interface and the ODBC driver will automatically convert the timestamp placeholder from the SQL_TIMESTAMP into SQL_VARCHAR (the dBase target column therefore has to be TEXT(20)).

               

              The other direction RDB->PI is not that simple. Actually, it is not possible to read a timestamp from a TEXT field because the required ODBC function CONVERT does not support the SQL_VARCHAR into SQL_TIMESTAMP conversion either. However, a workaround is possible:

               

              Use the dBase database as a linked table from within MS Access. Now the MS Access ODBC driver is available, which implements a function called CDATE(). The following query works for string columns e.g. TEXT(20) in dBase with the format  "DD-MMM-YY hh:mm:ss":

               

              SELECT CDATE(Timestamp), Value, Status FROM Table  WHERE CDATE(Timestamp) > ?; P1=TS

               

              ODBC drivers used:

               

              Microsoft dBase Driver    4.00.4403.02

               

              Microsoft Access Driver  4.00.4403.02

               

              This may be the issue you are facing. You may want to try the work around.

               

              Regards

               

              Willy Seah

               

              --------------------------------

               

              By using the CDATE function at MS Access then it work.. Only the date format is quite confusing, on D/M/Y or M/D/Y.

               

              If in my database column store date day less than 12 (cause we only have 12month),

               

              i:e 01/02/2013 - PI assume it 02-Jan-2013 (it wrong) however if the day at the date is over 12 i.e: 13/02/2013 - PI assume it as 13-Feb-2013 which is right.

               

              I have try to use format(DATE,'YYYY-MMM-DD') function at the SQL, but it still goes to the same result for 01/02/2013.

                • Re: RDBMSPI, error on timestamp format
                  xwang

                  Hi,

                   

                  Yes, PI time is dd-mmm-yyyy.  That is the reason why I suggested convert your source data type from dBase to string.  If the type is string, you could seperate the day, month and year into different columns with the string data type, and could conbine these columns into one column with the different format order.  This is the SQL language supported, will spend you some time to do.

                   

                  Xi Wang

                   

                  v-Campus team

                    • Re: RDBMSPI, error on timestamp format
                      faiz

                      Tq Xi Wang, it now fixed :)

                       

                      i'm using this SQL statement to handle storing data in D/M/Y format

                       

                      SELECT CDATE(MID(DATE,4,2) + '/' + MID(DATE,1,2) + '/' + MID(DATE,7,4) + ' ' + TIME) as PI_TIMESTAMP,VAR_STATE as PI_VALUE FROM JOURNAL

                       

                      However if it already in M/D/Y format

                       

                      SELECT CDATE(DATE + ' ' + TIME) as PI_TIMESTAMP,VAR_STATE as PI_VALUE FROM JOURNAL