Jump to content
  • Spotfire Write Back using Stored procedure


    Introduction

    One of the unique features of Spotfire is its writeback functionality. Writeback means we can insert data into database from Spotfire. Here I am explaining how we can implement write-back functionality using Stored Procedure.

    Example

     I have table User which contains user details (Id, UserName, User First Name and User Last Name). I created an info link for this table for loading data into the dashboard. 

    In my dashboard, one page contains three input box document property a save button and table visualization.  The input boxes are for inserting Username, First name, and second name. If we click the save button the corresponding user inputs are inserting into the table and show in below table visualization(Table visualisation is refreshing after saving data).

    Implementation

    1.Create a Stored Procedure for Insertion.

    CREATE PROCEDURE Sanandh.SpInsertUsers  
     @UserName VARCHAR(50),   ----These are the input from Spotfire
     @FirstName VARCHAR(50),  
     @LastName VARCHAR(50)  
    AS  
    --EXEC Sanandh.SpInsertUsers 'sahajhh','ajdgjghf','dkjfdshf'  
    DECLARE @id INT  
    DECLARE @IsExisting INT =0  
    SELECT   
     @Id = MAX(UserId)  
    FROM sanandh.Users  
      
    SELECT   
     @IsExisting = ISNULL(MAX(UserId),0)  
    FROM sanandh.Users  
    WHERE UserName=@UserName  
      
    --SELECT @IsExisting  
      
    If @IsExisting=0  
    BEGIN  
     Insert into sanandh.Users  
     (UserName,FirstName,LastName)  
     SELECT @UserName,@FirstName,@LastName  
    END  
    ELSE  
    BEGIN  
     UPDATE sanandh.Users  
     SET FirstName = @FirstName,  
      LastName = @LastName   
     WHERE UserName= @UserName  
    END  
    SELECT  1 AS Test   --- As we using Stored procedure as query type in Spotfire we should retrun at least one column from Stored procedure.
    
     

    2. Create this stored procedure as an element in Spotfire with query type.

    3. Create an information link for this stored procedure.

    4. Create an information link for loading user details.

    5. Load the user info link to the dashboard.

    6. Create a page with 3 document property, save button and table visualization.

    7. Write an Iron Python script for inserting data and link with Save button.

    Sample Iron Python script

    from System import Array,Guid,String,Object
    from Spotfire.Dxp.Data.Import import InformationLinkDataSource, InformationLinkParameter
    from Spotfire.Dxp.Data import *
    
    ###  Get value from three document property###
    Parameter=Document.Properties["UserName"]
    print Parameter
    TableParameter=Document.Properties["UserFirstName"]
    print TableParameter
    TableParameter1=Document.Properties["UserSecondName"]
    print TableParameter
    ############################
    ### Create an array for each parameter###
    a=Array.CreateInstance(str,1)
    a[0]=Parameter
    
    b=Array.CreateInstance(str,1)
    b[0]=TableParameter
    c=Array.CreateInstance(str,1)
    c[0]=TableParameter1
    #########################################
    
    ###Create info link paramter (The GUI id is Store procedure element Id)
    ilParam1 = InformationLinkParameter.CreateReferencedParameter(Guid("1faf5389-ca7c-4125-af6e-79d7ba94e2e2"),"@UserName",a)
    ilParam2 = InformationLinkParameter.CreateReferencedParameter(Guid("1faf5389-ca7c-4125-af6e-79d7ba94e2e2"),"@FirstName",b)
    ilParam3 = InformationLinkParameter.CreateReferencedParameter(Guid("1faf5389-ca7c-4125-af6e-79d7ba94e2e2"),"@LastName",c)
    
    #############################################
    #############executing info link
    ilDataSource = InformationLinkDataSource("ae073b74-aa8e-4652-8a4b-556154dd6d82", [ilParam1,ilParam2,ilParam3])
    ds=Document.Data.Tables.Add("temp",ilDataSource)
    Document.Data.Tables.Remove("temp")
    
    
    Table.Refresh() ########Refreshing user table , We need to pass data table name for this paramter
          
    Document.Properties["UserName"] =''
    Document.Properties["UserFirstName"]=''
    Document.Properties["UserSecondName"]=''
     

     

     


    User Feedback

    Recommended Comments

    There are no comments to display.


×
×
  • Create New...