faiz

RDBMSPI, error on timestamp format

Discussion created by faiz on Aug 19, 2013
Latest reply on Aug 21, 2013 by 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

Outcomes