Write-back into database functionality using Tibco Spotfire Analyst
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
BEGININSERT 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 intAS
BEGINDelete 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
Great Instructions. Thanks!
Thanks, Gary. I worked for Asurion as well in Nashville TN. You can let me know if you have any question I am happy to help you 🙂
With oracle DB I cannot about to perform write back to DB from spotfire.
My store is working successfully in DB.
Unable to figure out the issue, glad if you could do the SHORT video with oracle DB.
Can you please post the issue/ Error Logs?. Making video might take quite some time. But I might help you fix your issue if you can share me the error log or screenshots.
Hi Vidya,
Do you know the instructions for doing the same using Oracle database?
I mean do you have instruction on how to insert data to Oracle using Spotfire?
Thank you.
As of now, I do not have one. But I recommend you to try first and share me the issues/logs you are facing. So that I can troubleshoot it from my end provide you the solution.
Note: Make sure your Data Source Template in your Information Designer is ‘jdbc:tibcosoftwareinc:oracle://:;SID=‘
Thanks,
-Vidya
Hi Vidya,
Can you please share the dxp?
Looks like I don’t have access to it.
Thanks,
Satya
https://1drv.ms/f/s!AvtPuLJM7iDU3zkDASOKWChFKCR1
Hi Vidya,
I was able implement based on your instruction but I am getting duplicate records when i hit submit button. Please help
Thanks,
Satya
Hi Satya,
Yes, it is expected. Since Spotfire is not web development tool it is hard to control the server validations. However, My recommendation is to create table level validation in your database by defining the constraint like PK on the columns for which you do not want to have the duplicated records.
Thanks,
-Vidya
Very nice video Vidya Sagar… one small doubt can we do this in HIVE? means can we write Procedure in Hive? Please guide
I know its really late response. Just out of curiosity have you figured it out? I haven’t tried this with Hive yet.
Hi Vidya Sagar,
Appreciate for the useful blog.
Could you please share the above mentioned Step#1 and Step#2 SQL compatible with Oracle Database, as I am unable to get this to working with Oracle database.
Thanks in advance,
Srikanth
Can you share me the error logs?