Skip to main content

Cannot create a procedure element using TIBCO Spotfire Information Services for a Snowflake stored procedures.

Rich Talbot
  • Created
Date Posted:
Product: TIBCO Spotfire®

 

Problem:

Cannot create a procedure element using TIBCO Spotfire Information Services for a Snowflake stored procedures

 

Solution:
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.

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.

So Snowflake Stored Procedures and User defined functions are not supported in TIBCO Spotfire.

Doc : Overview of Procedures 

Doc: Procedure element tab 

External: Snowflake procedures

Comments

0 comments

Article is closed for comments.