Write-back into database functionality using Tibco Spotfire Analyst

Home  >>  Spotfire  >>  Write-back into database functionality using Tibco Spotfire Analyst

Write-back into database functionality using Tibco Spotfire Analyst

12
Jun,2018

14

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

 

 

14 Comments so far:

  1. Gary D says:

    Great Instructions. Thanks!

    • vidya Sagar Malla says:

      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 🙂

  2. Venkata gopi says:

    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.

    • vidya Sagar Malla says:

      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.

  3. Mary Anne says:

    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.

    • vidya Sagar Malla says:

      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

  4. satyanarayana Thottempudi says:

    Hi Vidya,

    Can you please share the dxp?

    Looks like I don’t have access to it.

    Thanks,
    Satya

  5. satyanarayana thottempudi says:

    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

    • vidya Sagar Malla says:

      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

  6. Hitesh says:

    Very nice video Vidya Sagar… one small doubt can we do this in HIVE? means can we write Procedure in Hive? Please guide

    • vidya Sagar Malla says:

      I know its really late response. Just out of curiosity have you figured it out? I haven’t tried this with Hive yet.

  7. Srikanth says:

    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

Leave a Reply

Your email address will not be published. Required fields are marked *

one × five =