Spotfire fails to load Time data through OData Connector

[John Griffith]

Regardless of how the time value is serialized (e.g. "09:50:00"), attempting to load data through the OData connector that is of type Edm.TimeOfDay (or evenEdm.Time) fails to load. The stack trace (seen below) seems to indicate that it is failing to convert the value to a TimeSpan. Why would it be trying to convert this to a TimeSpan, which would indicate it things of this as a Duration and not a time

This is being reported from Spotfire Analys 7.10.1. Is this something that is a known issue that might have been fixed in a later version Or is Spotfire expecting the data to be in some format that it can parse I know it can parse the values, because the data can be imported as "Edm.DateTime". But then that will require the user to change the type of the column from DateTime to Time.

Any information or help would be appreciated.

John

 

Exception Stacktrace from Spotfire.

ImportException at Spotfire.Dxp.Data:

An exception was thrown by the data connection.

Exception text:

System.FormatException: The string '09:50:00' is not a valid TimeSpan value.

at System.Xml.XmlConvert.ToTimeSpan(String s)

at Spotfire.Dxp.Data.Adapters.OData.ODataAdapterConnection.c__DisplayClass3b.b__2a(Object o)

at System.Action.Invoke()

at Spotfire.Dxp.Data.Adapters.OData.ODataQueryContext.AddRowsDataForJSon(ArrayList[] dataBlocks, SbdfTableWriter writer, QueryUrlInfo queryInfo)

at Spotfire.Dxp.Data.Adapters.OData.ODataAdapterConnection.ExecuteQueryForJSon(ODataQueryContext queryContext, QueryUrlInfo queryInfo, SbdfTableWriter writer)

at Spotfire.Dxp.Data.Adapters.OData.ODataAdapterConnection.ExecuteQueryCore(SqlQuery query, AdapterDataSchema schema, QueryResultWriter resultWriter, CancellationToken cancellationToken, IDictionary`2 logContext)

at Spotfire.Dxp.Data.Access.Adapters.DataAdapterConnection.ExecuteQuery(SqlQuery query, AdapterDataSchema schema, QueryResultWriter resultWriter, CancellationToken cancellationToken, IDictionary`2 logContext)

at Spotfire.Dxp.Data.Access.Adapters.DataAdapter.TryExecuteQuery(IDataAdapterExecutionContext executionContext, AdapterCredentialsValue credentials, SqlQuery query, AdapterDataSchema schema, QueryResultWriter resultWriter, CancellationToken cancellationToken, IDictionary`2 logContext)

at Spotfire.Dxp.Data.Access.Adapters.DataAccessDataSourceLink.c__DisplayClass79.c__DisplayClass7b.b__77(CancellationToken token)

at Spotfire.Dxp.Data.Access.Adapters.DataAccessDataSourceLink.ExecuteAdapterMethod(ImpersonationPolicy impersonationPolicy, AdapterMethodDelegate method, Nullable`1 cancellationToken)

[Harshith Reddy Musku]

Hello,

Spotfire triesto convert only the columns of type 'Edm.Duration' to TimeSpan. Can you attach full table information(XML or json file) You can get this from the browser.

[John Griffith]

That is what I would have expected as well, that only Duration mapped to TimeSpan.  But clearly the stacktrace shows that it is attempting to parse the JSON value (e.g. "09:50:00") as a TimeSpan.  whch fails.    

 

Interestingly enough, I also attempted in import data through ODBC, and my Time Columns mappsed to TimeSpan, which I found curious.   See attached image.

 

Here is the snippet from the $metdata for the OData Service URL.  (attached is full metadta.xml).

 

<Property Name="timeProperty" Type="Edm.TimeOfDay">

 

<Annotation Term="com.csi.propertyURI">

 

<String>

 

http://csi.com/ontologies/DateTimeTestOnt#timeProperty

 

</String>

 

</Annotation>

 

<Annotation Term="com.csi.dataTypeURI">

 

<String>http://www.w3.org/2001/XMLSchema#time</String>

 

</Annotation>

 

</Property>

 

Here is the JSON returned for querying the "timeProperty" column.

 

{"@odata.context":"http://localhost:8080/dataondemand/DateTime/DateTimeTest$metadata#DateTimeTestClass(timeProperty)","value":[

 

{"@odata.id":"DateTime/DateTimeTest/DateTimeTestClass('dXJuOi8vdGVzdC5jb20vc3BvdGZpcmVUZXN0L0RhdGVUaW1lQ2xhc3MvMQ')","timeProperty":"04:50:00"},

 

{"@odata.id":"DateTime/DateTimeTest/DateTimeTestClass('dXJuOi8vdGVzdC5jb20vc3BvdGZpcmVUZXN0L0RhdGVUaW1lQ2xhc3MvMg')","timeProperty":"04:50:00"},

 

{"@odata.id":"DateTime/DateTimeTest/DateTimeTestClass('dXJuOi8vdGVzdC5jb20vc3BvdGZpcmVUZXN0L0RhdGVUaW1lQ2xhc3MvMw')","timeProperty":"04:50:00"}]}

 

 

 

Here is the screenshot of the timeProperty column imported through ODBC showing as a TimeSpan.

 

[[{"fid":"152331","view_mode":"default","fields":{"format":"default","field_file_image_alt_text[und][0][value]":false,"field_file_image_title_text[und][0][value]":false},"type":"media","field_deltas":{"1":{"format":"default","field_file_image_alt_text[und][0][value]":false,"field_file_image_title_text[und][0][value]":false}},"attributes":{"class":"media-element file-default","data-delta":"1"}}]]

 

 

[John Griffith]

Here is full metadata xml for OData endpoint.

 

[[{"fid":"152336","view_mode":"default","fields":{"format":"default"},"link_text":"metadata.txt","type":"media","field_deltas":{"1":{"format":"default"}},"attributes":{"class":"media-element file-default","data-delta":"1"}}]]

 

As well as the data (JSON)

 

[[{"fid":"152341","view_mode":"default","fields":{"format":"default"},"link_text":"timedata.txt","type":"media","field_deltas":{"2":{"format":"default"}},"attributes":{"class":"media-element file-default","data-delta":"2"}}]]

[Harshith Reddy Musku]

Hi, I would suggest you to open a support case with TIBCO to investigate this issue further.