IP21 TAGs - Character to Double conversion error

[Former Member]

Hi All,

I have an issue with some IP21 TAGs.

I configured two Data servers to IP21 ( AspenTech Version 2004.2.1).

1. Connection Type : TAG, Connector: IP21 and JDBC Driver: sun.jdbc.odbc.JdbcOdbcDriver (Working fine, but not for all the TAGs)

2. Connection Type : SQL, Connector: IDBC and JDBCDriver: sun.jdbc.odbc.JdbcOdbcDriver ( I created this for testing if TAG Query Doesn’t work)

We are able query most of the TAGs using Tag type connector, but for some TAGs it is generating the following error.

**********ERROR************

INFO TimeEngine - TimeParams[_Shift=null,_TimePeriod=null,_Times=null,_EndDate=null,_StartDate=null,_Duration=null,_Units=S,_Format=MM/dd/yyyy HH:mm:ss,bAllowFuture=false]

INFO Service - StartDate=07/24/2007 16:23:48&EndDate=07/24/2007 17:23:48

DEBUG Service - Using service: IlluminatorService

DEBUG Service - Request for service com.lighthammer.Illuminator.services.handlers.IlluminatorService

DEBUG Service - Validating query...

DEBUG AuthorizationEngine - validateQuery() - check permissions for service 'IlluminatorService' and server 'IP21_MEMPHIS'

DEBUG Service - Trying to process request

INFO Illuminator - Executing Query: SELECT name,name->description,definition,name->graphminimum,name->graphmaximum FROM "gPV307003"

ERROR ErrorHandler - A SQL Error has occurred on query, [AspenTech][SQLplus] SQLGetData: Character to Double conversion error.

java.sql.SQLException: [AspenTech][SQLplus] SQLGetData: Character to Double conversion error

at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6958)

at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)

at sun.jdbc.odbc.JdbcOdbc.SQLGetDataDouble(JdbcOdbc.java:3658)

at sun.jdbc.odbc.JdbcOdbcResultSet.getDataDouble(JdbcOdbcResultSet.java:5579)

at sun.jdbc.odbc.JdbcOdbcResultSet.getDouble(JdbcOdbcResultSet.java:740)

at com.lighthammer.Illuminator.connectors.AspenTech.IP21.lookupTagInformation(Unknown Source)

at com.lighthammer.Illuminator.connectors.AspenTech.IP21.processDataQuery(Unknown Source)

at com.lighthammer.Illuminator.connectors.AspenTech.IP21.processRequest(Unknown Source)

at com.lighthammer.Illuminator.services.handlers.IlluminatorService.processRequest(Unknown Source)

********************

But, when I use server type SQL with the following statement I am able to pull the TAG data.

SELECT LAST_RECORDING_TIME,NAME,LAST_RECORDING_QUAL,LAST_RECORDING FROM "gPV307003"

What could be the reason?

[Former Member · Accepted solution]

Some more information.

We are in xMII 11.5 SP03. and JSDK 1.4.2_07

[Former Member · Accepted solution]

What values do you get back for graphmaximum/minimum(I am assuming these are tags in the database) when you execute the select stmt.

It seems the Tag connector is retrieving some junk for these particular tags and is eventually failing in conversion.

[ranga_manthena · Accepted solution]

Hi Uday,

The TAG name is "gPV307003". It is giving the above error when i try with TAG type Query.

When i use SQL Type query with the following statement. I am able to pull the TAG Data.

SELECT LAST_RECORDING_TIME,NAME,LAST_RECORDING_QUAL,LAST_RECORDING FROM "gPV307003"

[Former Member · Accepted solution]

Ok.

With the select what value do you get back for LAST_RECORDING ? Is this a valid float/double type?

[ranga_manthena · Accepted solution]

Hi Uday,

This the XML i got when i make SQL Type Query.

<?xml version="1.0" encoding="UTF-8" ?>

- <Rowsets DateCreated="2007-07-25T09:36:21" EndDate="2007-07-25T09:36:20" StartDate="2007-07-25T08:36:20" Version="11.5.3">

- <Rowset>

- <Columns>

<Column Description="LAST_RECORDING_TIME" MaxRange="1" MinRange="0" Name="LAST_RECORDING_TIME" SQLDataType="93" SourceColumn="LAST_RECORDING_TIME" />

<Column Description="NAME" MaxRange="1" MinRange="0" Name="NAME" SQLDataType="12" SourceColumn="NAME" />

<Column Description="LAST_RECORDING_QUAL" MaxRange="1" MinRange="0" Name="LAST_RECORDING_QUAL" SQLDataType="4" SourceColumn="LAST_RECORDING_QUAL" />

<Column Description="LAST_RECORDING" MaxRange="1" MinRange="0" Name="LAST_RECORDING" SQLDataType="7" SourceColumn="LAST_RECORDING" />

</Columns>

- <Row>

<LAST_RECORDING_TIME>2007-07-25T09:34:51</LAST_RECORDING_TIME>

<NAME>gPV307003</NAME>

<LAST_RECORDING_QUAL>-1</LAST_RECORDING_QUAL>

<LAST_RECORDING>3610</LAST_RECORDING>

</Row>

</Rowset>

</Rowsets>

[Former Member · Accepted solution]

Just a speculation-

Could the recording quality be a deciding factor?

It says -1 for this particular tag.(Could it be BAD quality?)

What do you get back for quality for the tags that you are able to retrieve flawlessly using both the TAG as well as SQL connector.

[ranga_manthena · Accepted solution]

"LAST_RECORDING_QUAL" is same for all the TAGs (even for the TAGs that i am able to pull from TAG type Query.

[ranga_manthena · Accepted solution]

We found the Bug, This is an IP21 TAG Configuration issue. We resolved the Problem.

Thanks,

[jcgood25 · Accepted solution]

Ranga,

When using the TagQuery connectors they always run a meta query to get the tagname's description, and engineering scale values. Then the data query (current, history, etc.) is run. Basically the stuff you see in the Columns section of the xml results is from the meta query.

What happens when you run the SELECT name,name->description,definition,name->graphminimum,name->graphmaximum FROM "gPV307003" query in your SQL based IP21 connection?

Is it possible that the graphminimum or graphmaximum are not double friendly?

Regards,

Jeremy Good

[ranga_manthena · Accepted solution]

What you mentioned is exactly right. They didn't maintained graphmaximum for some of the TAGs. So it is resulting some junk value like "<<<<<<" this.

Thank you very much for reply,

Ranga

[Former Member · Accepted solution]

Ranga,

Could you please tell me how you connected to IP21 using the IP21 and JDBC connector. What are the params i would need to configure in the IP21 connector.

Thanks and Regards,

Ram

[Former Member · Accepted solution]

Hi Ram,

Install AspenTech(IP21) ODBC driver in xMII Server and Configure System DSN targeting IP21 Legacy system.

Find the below parameters that i have updated in data server.

Compressed, History and Tag parameters will be changed based IP21 system TAG configuration. You have to get this information from the IP21 system technical team.

*********Parameters***************

CompressedHistoryEventNameColumn = NAME

CompressedHistoryEventQualityColumn = COMPRESSEDQUALITY

CompressedHistoryEventTable = <NULL>

CompressedHistoryEventTimeColumn = COMPRESSEDTIME

CompressedHistoryEventValueColumn = COMPRESSEDVALUE

Connector = IP21

ConnectorType = TAG

CurrentNameColumn = NAME

CurrentQualityColumn = LAST_RECORDING_QUAL

CurrentTable = <NULL>

CurrentTimeColumn = LAST_RECORDING_TIME

CurrentValueColumn = LAST_RECORDING

DatePrefix = '

DateSuffix = '

Description = IP21

Enabled = true

HistoryEventNameColumn = NAME

HistoryEventQualityColumn = TRENDQUALITY

HistoryEventTable = <NULL>

HistoryEventTimeColumn = TRENDTIME

HistoryEventValueColumn = TRENDVALUE

HistoryNameColumn = NAME

HistoryPeriodColumn = PERIOD

HistoryQualityColumn = QUALITY

HistoryTable = HISTORY

HistoryTimeColumn = TS

HistoryValueColumn = VALUE

InternalDateFormat = DD-MMM-YY HH:mm:ss

JDBCDriver = sun.jdbc.odbc.JdbcOdbcDriver

Name = IP21_Test

PoolMax = 100

PoolSize = 1

ServerPackage = com.lighthammer.Illuminator.connectors.AspenTech

ServerURL = jdbc:odbc:<Your IP21 System DSN Name>

TagInfoDescriptionColumn = name->description

TagInfoMaxRangeColumn = name->graphmaximum

TagInfoMinRangeColumn = name->graphminimum

TagInfoNameColumn = name

TagInfoTable = <NULL>

TagInfoTypeColumn = definition

TagListDescriptionColumn = description

TagListNameColumn record_= name

TagListTable = alltags

Timeout = 15

UseCount = 256

UserName = <NULL>

ValidationQuery = <NULL>

WaitTime = 30

[Former Member · Accepted solution]

There is no way to get IP21 through JDBC, you must use the OLEDB UDS.

[Former Member · Accepted solution]

Thanks Ranga.