Executing Information Link based on Google BigQuery fails with error "Marshalling Error: INVALID_CHARACTER_ERR: An invalid or illegal XML character is specified."

Executing Information Link based on Google BigQuery fails with error "Marshalling Error: INVALID_CHARACTER_ERR: An invalid or illegal XML character is specified."

book

Article ID: KB0078068

calendar_today

Updated On:

Products Versions
Spotfire Analyst All Versions

Description

When executing an Information Link based on Google BigQuery using the default template, it may fail to load with the following error:

Error message: Could not open information link.
ImportException at Spotfire.Dxp.Data:
Failed to execute data source query for data source "Information link". (HRESULT: 80131500)

Stack Trace:
   at Spotfire.Dxp.Data.DataSourceConnection.ExecuteQuery2()
   at Spotfire.Dxp.Data.DataFlow.Execute()
   at Spotfire.Dxp.Data.DataFlow.DataFlowConnection.ExecuteQueryCore2()
   at Spotfire.Dxp.Data.DataSourceConnection.ExecuteQuery2()
   at Spotfire.Dxp.Data.Producers.SourceColumnProducer.<>c__DisplayClass72_1.<CreateView>b__0()
   at Spotfire.Dxp.Framework.ApplicationModel.Progress.ExecuteSubtask(String title, ProgressOperation operation)
   at Spotfire.Dxp.Data.Producers.SourceColumnProducer.CreateView(CxxSession session, DataPropertyRegistry propertyRegistry, GlobalMethodRegistry globalMethodRegistry, DataSourceConnection connection, PartialDataLoadReport& partialLoadReport)
   at Spotfire.Dxp.Data.Producers.SourceColumnProducer.GetColumnsAndProperties(DataSourceConnection connection)
   at Spotfire.Dxp.Data.Persistence.DataItem.PerformUpdate(SourceColumnProducer producer, DataSourceConnection connection)
   at Spotfire.Dxp.Data.Persistence.DataItem.Update(SourceColumnProducer producer, DataSourceConnection connection)
   at Spotfire.Dxp.Data.Persistence.DataPool.<LoadData>d__15.MoveNext()
   at Spotfire.Dxp.Data.Producers.SourceColumnProducer.OnConfigure()
   at Spotfire.Dxp.Framework.DocumentModel.Node.ConfigureSubTree()
   at Spotfire.Dxp.Framework.DocumentModel.Node.<>c.<ConfigureSubTree>b__47_0(Node node)
   at Spotfire.Dxp.Framework.DocumentModel.UndoableListAvlLeaf`1.ForEachChild(Action`1 action, Boolean includeFrozen)
   at Spotfire.Dxp.Framework.DocumentModel.Node.ConfigureSubTree()
   at Spotfire.Dxp.Framework.DocumentModel.Node.<>c.<ConfigureSubTree>b__47_0(Node node)
   at Spotfire.Dxp.Framework.DocumentModel.UndoableList`1.ForEachChild(Action`1 action, Boolean includeFrozen)
   at Spotfire.Dxp.Framework.DocumentModel.Node.ConfigureSubTree()
   at Spotfire.Dxp.Framework.DocumentModel.Node.<>c.<ConfigureSubTree>b__47_0(Node node)
   at Spotfire.Dxp.Framework.DocumentModel.State.NodeState.<>c__DisplayClass91_0.<ForEachManagedChild>b__0(IDocumentNodeChild documentNodeChild)
   at Spotfire.Dxp.Framework.DocumentModel.State.NodeState.ForEachChild(IDocumentNodeChild[] children, Action`1 action)
   at Spotfire.Dxp.Framework.DocumentModel.Node.ConfigureSubTree()
   at Spotfire.Dxp.Framework.ApplicationModel.Progress.ExecuteSubtask(String title, IndeterminateProgressFormatter progressFormatter, ProgressOperation operation)
   at Spotfire.Dxp.Framework.DocumentModel.DocumentNode.ConfigureAndAttachFromNew()
   at Spotfire.Dxp.Framework.DocumentModel.DocumentNode.AttachSubTreeWhileExecuting(UndoableNodeBase newOwner)
   at Spotfire.Dxp.Framework.DocumentModel.UndoableNode.Spotfire.Dxp.Framework.DocumentModel.IUndoableNode.AttachItemToUndoableNode(Object item)
   at Spotfire.Dxp.Framework.DocumentModel.UndoableKeyedCollection`2.<>c__DisplayClass43_0.<Insert>b__0()
   at Spotfire.Dxp.Framework.DocumentModel.Node.InternalTransaction(Executor executor, Boolean rollbackNestedInternalTransactionAtException)
   at Spotfire.Dxp.Framework.DocumentModel.UndoableKeyedCollection`2.Insert(Int32 index, TNode item)
   at Spotfire.Dxp.Data.DataTableCollection.<>c__DisplayClass68_0.<Add>b__0()
   at Spotfire.Dxp.Framework.DocumentModel.Node.InternalTransaction(Executor executor, Boolean rollbackNestedInternalTransactionAtException)
   at Spotfire.Dxp.Data.DataTableCollection.Add(DataTable dataTable)
   at Spotfire.Dxp.Application.PartiallyOpenedDataSource.<LoadData>d__9.MoveNext()
   at Spotfire.Dxp.Application.AnalysisApplication.<OpenPartiallyOpenedDocument>d__110.MoveNext()
   at Spotfire.Dxp.Application.AnalysisApplication.<OpenDataSource>d__99.MoveNext()
   at Spotfire.Dxp.Application.AnalysisApplication.ConsumeDataLoadPromptRequests(IEnumerable`1 prompts)
   at Spotfire.Dxp.Application.AnalysisApplication.Open(DataSource source, DocumentOpenSettings settings)
   at Spotfire.Dxp.Forms.Data.Import.DataSourceFactoryService.OpenDataSourceWithoutPrompting(DataSource dataSource, DocumentOpenSettings documentOpenSettings, IServiceProvider serviceProvider)
   at Spotfire.Dxp.Forms.Data.Import.DataSourceFactoryService.OpenDataSource(InformationLinkDataSource dataSource, DocumentOpenSettings documentOpenSettings, IServiceProvider serviceProvider)
   at Spotfire.Dxp.Forms.Application.FormsProgressService.ProgressThread.DoOperationLoop()

InformationModelException at Spotfire.Dxp.Data:
Marshalling Error: INVALID_CHARACTER_ERR: An invalid or illegal XML character is specified.  (HRESULT: 80131500)

Stack Trace:
   at Spotfire.Dxp.Data.InformationModel.InternalInformationModelManager.DataStream.GetNextBlock()
   at Spotfire.Dxp.Data.InformationModel.InternalInformationModelManager.DataStream.Read(Byte[] buffer, Int32 offset, Int32 count)
   at Spotfire.Dxp.Internal.Utilities.SeekableStream.Read(Byte[] buffer, Int32 offset, Int32 count)
   at System.IO.BinaryReader.FillBuffer(Int32 numBytes)
   at System.IO.BinaryReader.ReadUInt16()
   at Spotfire.Dxp.Data.Formats.Sbdf.SbdfSectionHeader.ReadMagicNumber(BinaryReader reader)
   at Spotfire.Dxp.Data.Formats.Sbdf.SbdfSectionHeader.ReadSectionType(BinaryReader reader, SbdfSectionTypeId expectedType)
   at Spotfire.Dxp.Data.Import.SbdfDataRowReader.DelayedInit()
   at Spotfire.Dxp.Data.Import.InformationLinkDataSourceConnection.ExecuteQueryCore2()
   at Spotfire.Dxp.Data.DataSourceConnection.ExecuteQuery2()

InformationModelServiceException at Spotfire.Dxp.Services:
Marshalling Error: INVALID_CHARACTER_ERR: An invalid or illegal XML character is specified.  (HRESULT: 80131509)

Stack Trace:
   at Spotfire.Dxp.Services.WebServiceBase`1.InvokeService[T](ServiceMethod`1 serviceMethod, ExceptionFactoryMethod exceptionFactoryMethod)
   at Spotfire.Dxp.Data.InformationModel.InternalInformationModelManager.DataStream.GetNextBlock()

SoapHeaderException at System.Web.Services:
Marshalling Error: INVALID_CHARACTER_ERR: An invalid or illegal XML character is specified.  (HRESULT: 80131501)

Stack Trace:
   at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
   at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
   at Spotfire.Dxp.WebServices.QueryManagerService.getDataBlock(String jobID, Int32 timeout)
   at Spotfire.Dxp.Services.WebServiceBase`1.InvokeService[T](ServiceMethod`1 serviceMethod, ExceptionFactoryMethod exceptionFactoryMethod)

Resolution

Quotes (like " ") are appended to the schema, table and column names by default in the Information link query. In Google BigQuery, having quotes '' " in the schema, table and column names in the SQL query is not supported. To resolve, please follow either of the options:

Option 1) Edit the default SQL of the Information Link by removing the quotes to the schema, table and column names. See Editing the SQL of an Information Link for more details. For example, if the default Information Link SQL is:
SELECT
  B1."ASSISTS" AS "ASSISTS",
  B1."NAME" AS "NAME",
  B1."POSITION" AS "POSITION"
FROM
  "spotfire_demodata"."dbo"."BASEBALL" B1
WHERE
  <conditions>
It should be modified to:
SELECT
  B1.ASSISTS AS ASSISTS,
  B1.NAME AS NAME,
  B1.POSITION AS POSITION
FROM
   spotfire_demodata.dbo.BASEBALL B1
WHERE
  <conditions>


Option 2) Add the following lines before </jdbc-type-settings> in the default template available for Google BigQuery from the TIBCO Spotfire® JDBC Data Access Connectivity Details wiki page:
<column-name-pattern>$$name$$</column-name-pattern>
<table-name-pattern>$$name$$</table-name-pattern>
<schema-name-pattern>$$name$$</schema-name-pattern>
<catalog-name-pattern>$$name$$</catalog-name-pattern>
<procedure-name-pattern>$$name$$</procedure-name-pattern>
<column-alias-pattern>$$name$$</column-alias-pattern>

Issue/Introduction

In Google BigQuery, having quotes '' in the schema, table and column names in the SQL query is not supported and will fail to load data with the default Information Link query.

Additional Information

Wiki: TIBCO Spotfire® JDBC Data Access Connectivity Details > Google BigQuery template:  Doc: Editing the SQL of an Information Link