Product:TIBCO Spotfire Analyst
Versions:All
Summary:
This article explains why you get errors when creating Snowflake stored procedures elements in TIBCO Spotfire. In short, Snowflake stored procedures and user defined functions are not supported in TIBCO Spotfire.
Details:
When creating a data source for Snowflake in TIBCO Spotfire Information Designer, even though the stored procedure is visible under the tree view it fails to create the procedure element with following error
"The previously selected source procedure was taken from a data source that can no longer be found on the server. Please select a new source"
System.Net.WebException: The remote server returned an error: (500) Internal Server Error <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><soap:Body><soap:Fault><faultcode>soap:Server</faultcode><faultstring>Error retrieving metadata: Invalid column index: 7</faultstring><detail><ns3:InformationModelWebServiceException xmlns:ns3="http://spotfire.tibco.com/ws/2008/11/faults.xsd" xmlns:ns2="http://spotfire.tibco.com/ws/2008/11/im.xsd"><errorCode>Server.InformationModelService.DataSourceError</errorCode><message>Error retrieving metadata: Invalid column index: 7</message><stackTrace xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/></ns3:InformationModelWebServiceException></detail></soap:Fault></soap:Body></soap:Envelope>. at Spotfire.Dxp.Framework.HttpClient.NativeWebResponse..ctor(NativeHttpClient httpClient, NativeWebRequest request) at Spotfire.Dxp.Framework.HttpClient.WinINet.WinINetWebRequest.GetResponseCore() at Spotfire.Dxp.Framework.HttpClient.NativeWebRequest.GetResponse() at Spotfire.Dxp.Services.SessionEnabledSoapClient.GetWebResponse(WebRequest request) 2019-09-25T13:26:12,195-06:00 2019-09-25 19:26:12,195 sf_admin [Main Thread] WARN Spotfire.Dxp.Services.WebServiceBase`1[[Spotfire.Dxp.WebServices.ElementManagerService, Spotfire.Dxp.Services, Version=28.0.10709.3627, Culture=neutral, PublicKeyToken=789861576bd64dc5]] [(null)] - WebServiceCall failed, giving up.
Resolution:
In short, Snowflake Stored Procedures and User defined functions are not supported in TIBCO Spotfire.
Additional details:
In Snowflake, stored procedures returns a scalar value and not a record set/table but the procedure element in the Information designer expects a procedure that returns a table
Example Stored Procedure (which fails in Spotfire):
Snowflake supports user defined functions that can return a table but that uses a special keyword/function called 'table' to achieve that and Information Services in Spotfire is not designed to handle such functions.
Versions:All
Summary:
This article explains why you get errors when creating Snowflake stored procedures elements in TIBCO Spotfire. In short, Snowflake stored procedures and user defined functions are not supported in TIBCO Spotfire.
Details:
When creating a data source for Snowflake in TIBCO Spotfire Information Designer, even though the stored procedure is visible under the tree view it fails to create the procedure element with following error
"The previously selected source procedure was taken from a data source that can no longer be found on the server. Please select a new source"
System.Net.WebException: The remote server returned an error: (500) Internal Server Error <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><soap:Body><soap:Fault><faultcode>soap:Server</faultcode><faultstring>Error retrieving metadata: Invalid column index: 7</faultstring><detail><ns3:InformationModelWebServiceException xmlns:ns3="http://spotfire.tibco.com/ws/2008/11/faults.xsd" xmlns:ns2="http://spotfire.tibco.com/ws/2008/11/im.xsd"><errorCode>Server.InformationModelService.DataSourceError</errorCode><message>Error retrieving metadata: Invalid column index: 7</message><stackTrace xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/></ns3:InformationModelWebServiceException></detail></soap:Fault></soap:Body></soap:Envelope>. at Spotfire.Dxp.Framework.HttpClient.NativeWebResponse..ctor(NativeHttpClient httpClient, NativeWebRequest request) at Spotfire.Dxp.Framework.HttpClient.WinINet.WinINetWebRequest.GetResponseCore() at Spotfire.Dxp.Framework.HttpClient.NativeWebRequest.GetResponse() at Spotfire.Dxp.Services.SessionEnabledSoapClient.GetWebResponse(WebRequest request) 2019-09-25T13:26:12,195-06:00 2019-09-25 19:26:12,195 sf_admin [Main Thread] WARN Spotfire.Dxp.Services.WebServiceBase`1[[Spotfire.Dxp.WebServices.ElementManagerService, Spotfire.Dxp.Services, Version=28.0.10709.3627, Culture=neutral, PublicKeyToken=789861576bd64dc5]] [(null)] - WebServiceCall failed, giving up.
Resolution:
In short, Snowflake Stored Procedures and User defined functions are not supported in TIBCO Spotfire.
Additional details:
In Snowflake, stored procedures returns a scalar value and not a record set/table but the procedure element in the Information designer expects a procedure that returns a table
Example Stored Procedure (which fails in Spotfire):
CREATE or replace PROCEDURE proc3()
RETURNS VARCHAR
LANGUAGE javascript
AS
$$
rs = snowflake.execute( { sqlText:
`INSERT INTO table1 ("column 1")
SELECT 'value 1' AS "column 1" ;`
} );
return 'Done.';
$$;
Snowflake supports user defined functions that can return a table but that uses a special keyword/function called 'table' to achieve that and Information Services in Spotfire is not designed to handle such functions.