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 application, 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"]=''