I see many Tibco Spotfire Developers are excited and confused about writeback database functionality using Spotfire Analyst / Spotfire WebPlayer. So this post is for all of them.

Scenario: I would like to create a user registration form in Spotfire and insert the provided values into the Database tables. Also, I would like to delete the records from the database table from Spotfire.

Please follow the below steps the achieve this scenario.

Step1: Create a user registration form table in your database. I am using MS SQL Server.

CREATE TABLE [dbo].[UserRegistration](
[ID] [int] IDENTITY(1,1) NOT NULL,
[First Name] [nchar](30) NOT NULL,
[Last Name] [nchar](30) NOT NULL,
[Email ID] [nvarchar](50) NOT NULL,
[Password] [nvarchar](50) NOT NULL,
[Gender] [char](2) NOT NULL,
CONSTRAINT [PK_UserRegistration] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Step2: Create Insert Stored Procedure 

CREATE PROC [dbo].[SPUSERREGISTRATIONFORM]
@FN NCHAR(30),
@LN NCHAR(30),
@EMAIL NVARCHAR(50),
@PASS NVARCHAR(50),
@G CHAR(2)

AS
BEGIN

INSERT INTO [dbo].[UserRegistration] VALUES (@FN,@LN,@EMAIL,@PASS,@G)

SELECT * FROM [dbo].[UserRegistration]

END;
GO

Step3: Create Delete Stored Procedure

create PROC [dbo].[SPDeleteUsers]
@userID int

AS
BEGIN

Delete from [dbo].[UserRegistration] where ID in (@UserID)

SELECT * FROM [dbo].[UserRegistration]

END;
GO

Step4: Create create information link for User Registration table.

To create an information link refer to Spotfire information link documentation.

Step5: Open Spotfire Client and Create a procedure in information designer for Insert Stored Procedure

Find the below image for your reference.

Step6: Now create an Information link from the above procedure

Find the below image for your reference.

Step7: Now repeat step5 and Step6  for Delete Stored Procedure. 

Find the below image for your reference

Step8: Now load the load the “UserRegistration” information link as a data table.

Step9: Open the text area and create a User Registration form.

Find the form HTML code below and make sure you replace all Spotfire Control ID with your INPUT FIELD document properties

<DIV align=center style=”margin-top:30px;”>
<TABLE align=center style=”HEIGHT: 300px; WIDTH: 497px; FONT-WEIGHT: bold;COLOR: #1E90FF” border=1 cellPadding=2>
<TBODY>
<TR style=”HEIGHT: 21px”>
<TD align=center style=”FONT-SIZE: 20px; HEIGHT: 21px; WIDTH: 248px; ” colSpan=2>User Registration</TD></TR>
<TR style=”HEIGHT: 21px”>
<TD style=”HEIGHT: 21px; WIDTH: 248px; PADDING-LEFT: 20px;”>First Name</TD>
<TD style=”HEIGHT: 21px; WIDTH: 248px”><SpotfireControl id=”db53aa92f09c405fa45aa995822ce4f0″ /></TD></TR>
<TR style=”HEIGHT: 21px”>
<TD style=”HEIGHT: 21px; WIDTH: 248px; PADDING-LEFT: 20px”>Last Name</TD>
<TD style=”HEIGHT: 21px; WIDTH: 248px”><SpotfireControl id=”ff45c27612c943f69e68343b2b1d6f7b” /></TD></TR>
<TR style=”HEIGHT: 21px”>
<TD style=”HEIGHT: 21px; WIDTH: 248px; PADDING-LEFT: 20px”>Email</TD>
<TD style=”HEIGHT: 21px; WIDTH: 248px”><SpotfireControl id=”a58b6f88245641f288af3907b0aec7a0″ /></TD></TR>
<TR style=”HEIGHT: 21px”>
<TD style=”HEIGHT: 21px; WIDTH: 248px; PADDING-LEFT: 20px”>Password</TD>
<TD style=”HEIGHT: 21px; WIDTH: 248px”><SpotfireControl id=”b972159ce4444cfd84c73a9ae090cfa7″ /></TD></TR>
<TR style=”HEIGHT: 21px”>
<TD style=”HEIGHT: 21px; WIDTH: 248px; PADDING-LEFT: 20px”>Gender</TD>
<TD style=”HEIGHT: 21px; WIDTH: 248px”><SpotfireControl id=”a6bb8d760ec845a2a01ed42fe4277219″ /></TD></TR>
<TR style=”HEIGHT: 21px”>
<TD align=center style=”FONT-SIZE: 20px; HEIGHT: 21px; WIDTH: 248px; COLOR: gray” ><SpotfireControl id=”1158280db4524fef885fff332c0f71b9″ />
</TD>
<TD align=center style=”FONT-SIZE: 20px; HEIGHT: 21px; WIDTH: 248px; COLOR: gray” ><SpotfireControl id=”8dccbd66a54746848d1915ada3667347″ />
</TD>
</TR>
</TBODY></TABLE></DIV>

Step10: Now create two data tables and from the procedure information links (which is Step6 and step7 2nd image).

Step11: Make sure each Stored Procedure information links is on data on demand.

For Insert Stored Procedure

For Delete Store Procedure

Step12: Finally  we need to create 4 buttons using Iron python Scripting

Insert button:

Delete button:

Reset button:

Refresh button:

However, I am still adding the downloadable DXP link just in case if you lost in the process. Clik Here to download the file.

Thanks,

-Vidya