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"]=''
Recommended Comments
There are no comments to display.