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

Spotfire

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

20 Comments so far:

Gary D says:

June 18, 2018 at 6:27 pm

Great Instructions. Thanks!

Reply
- vidya Sagar Malla says:
  
  July 6, 2018 at 2:24 pm
  
  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 🙂
  
  Reply
Venkata gopi says:

July 6, 2018 at 2:17 pm

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.

Reply
- vidya Sagar Malla says:
  
  July 6, 2018 at 2:27 pm
  
  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.
  
  Reply
Mary Anne says:

July 18, 2018 at 7:55 pm

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.

Reply
- vidya Sagar Malla says:
  
  July 19, 2018 at 4:56 pm
  
  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
  
  Reply
satyanarayana Thottempudi says:

July 19, 2018 at 7:23 pm

Hi Vidya,

Can you please share the dxp?

Looks like I don’t have access to it.

Thanks,
Satya

Reply
- vidya Sagar Malla says:
  
  July 20, 2018 at 2:43 am
  
  https://1drv.ms/f/s!AvtPuLJM7iDU3zkDASOKWChFKCR1
  
  Reply
satyanarayana thottempudi says:

July 20, 2018 at 4:42 am

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

Reply
- vidya Sagar Malla says:
  
  July 21, 2018 at 5:39 pm
  
  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
  
  Reply
Hitesh says:

August 28, 2018 at 5:23 am

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

Reply
- vidya Sagar Malla says:
  
  February 4, 2019 at 10:02 pm
  
  I know its really late response. Just out of curiosity have you figured it out? I haven’t tried this with Hive yet.
  
  Reply
Srikanth says:

October 5, 2018 at 6:38 am

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

Reply
- vidya Sagar Malla says:
  
  February 4, 2019 at 10:03 pm
  
  Can you share me the error logs?
  
  Reply
  - Egor says:
    
    March 31, 2020 at 6:07 am
    
    Hello Vidya Sagar,
    Thank you for very detailed instruction.
    But implementing it for oracle database, when adding information link as procedure I have error with opening Info Link: Error message: Could not open information link.(I think on STEP 5)
    
    Error from SF below
    ____________________________________________
    Error message: Could not open information link.
    
    ImportException at Spotfire.Dxp.Data:
    Failed to execute data source query for data source “test_sf2db”. (HRESULT: 80131500)
    
    Stack Trace:
    at Spotfire.Dxp.Data.DataSourceConnection.ExecuteQuery2()
    at Spotfire.Dxp.Data.DataFlow.Execute()
    at Spotfire.Dxp.Data.DataFlow.DataFlowConnection.ExecuteQueryCore2()
    at Spotfire.Dxp.Data.DataSourceConnection.ExecuteQuery2()
    at Spotfire.Dxp.Data.Producers.SourceColumnProducer.c__DisplayClass73_0.b__0()
    at Spotfire.Dxp.Framework.ApplicationModel.Progress.ExecuteSubtask(String title, ProgressOperation operation)
    at Spotfire.Dxp.Data.Producers.SourceColumnProducer.CreateView(CxxSession session, GlobalMethodRegistry globalMethodRegistry, DataSourceConnection connection, DataRowReader initialReader, IDataPropertyContainer defaultColumnProperties, UInt64 maxRowsToConsume, Boolean disableReset, PartialDataLoadReport& partialLoadReport)
    at Spotfire.Dxp.Data.Producers.SourceColumnProducer.GetColumnsAndProperties(DataSourceConnection connection)
    at Spotfire.Dxp.Data.Persistence.DataItem.PerformUpdate(SourceColumnProducer producer, DataSourceConnection connection)
    at Spotfire.Dxp.Data.Persistence.DataItem.Update(SourceColumnProducer producer, DataSourceConnection connection)
    at Spotfire.Dxp.Data.Persistence.DataPool.d__15.MoveNext()
    at Spotfire.Dxp.Data.Producers.SourceColumnProducer.OnConfigure()
    at Spotfire.Dxp.Framework.DocumentModel.Node.ConfigureSubTree()
    at Spotfire.Dxp.Framework.DocumentModel.Node.c.b__47_0(Node node)
    at Spotfire.Dxp.Framework.DocumentModel.UndoableListAvlLeaf`1.ForEachChild(Action`1 action, Boolean includeFrozen)
    at Spotfire.Dxp.Framework.DocumentModel.Node.ConfigureSubTree()
    at Spotfire.Dxp.Framework.DocumentModel.Node.c.b__47_0(Node node)
    at Spotfire.Dxp.Framework.DocumentModel.UndoableList`1.ForEachChild(Action`1 action, Boolean includeFrozen)
    at Spotfire.Dxp.Framework.DocumentModel.Node.ConfigureSubTree()
    at Spotfire.Dxp.Framework.DocumentModel.Node.c.b__47_0(Node node)
    at Spotfire.Dxp.Framework.DocumentModel.State.NodeState.c__DisplayClass92_0.b__0(IDocumentNodeChild documentNodeChild)
    at Spotfire.Dxp.Framework.DocumentModel.State.NodeState.ForEachChild(IDocumentNodeChild[] children, Action`1 action)
    at Spotfire.Dxp.Framework.DocumentModel.Node.ConfigureSubTree()
    at Spotfire.Dxp.Framework.ApplicationModel.Progress.ExecuteSubtask(String title, IndeterminateProgressFormatter progressFormatter, ProgressOperation operation)
    at Spotfire.Dxp.Framework.DocumentModel.DocumentNode.ConfigureAndAttachFromNew()
    at Spotfire.Dxp.Framework.DocumentModel.DocumentNode.AttachSubTreeWhileExecuting(UndoableNodeBase newOwner)
    at Spotfire.Dxp.Framework.DocumentModel.UndoableNode.Spotfire.Dxp.Framework.DocumentModel.IUndoableNode.AttachItemToUndoableNode(Object item)
    at Spotfire.Dxp.Framework.DocumentModel.UndoableKeyedCollection`2.c__DisplayClass43_0.b__0()
    at Spotfire.Dxp.Framework.DocumentModel.Node.InternalTransaction(Executor executor, Boolean rollbackNestedInternalTransactionAtException, Boolean isStreamingProperty)
    at Spotfire.Dxp.Framework.DocumentModel.UndoableKeyedCollection`2.Insert(Int32 index, TNode item)
    at Spotfire.Dxp.Data.DataTableCollection.c__DisplayClass88_0.b__0()
    at Spotfire.Dxp.Framework.DocumentModel.Node.InternalTransaction(Executor executor, Boolean rollbackNestedInternalTransactionAtException, Boolean isStreamingProperty)
    at Spotfire.Dxp.Data.DataTableCollection.Add(DataTable dataTable)
    at Spotfire.Dxp.Application.PartiallyOpenedDataSource.d__9.MoveNext()
    at Spotfire.Dxp.Application.AnalysisApplication.d__90.MoveNext()
    at Spotfire.Dxp.Application.AnalysisApplication.d__83.MoveNext()
    at Spotfire.Dxp.Application.AnalysisApplication.ConsumeDataLoadPromptRequests(IEnumerable`1 prompts)
    at Spotfire.Dxp.Application.AnalysisApplication.Open(DataSource source, DocumentOpenSettings settings)
    at Spotfire.Dxp.Forms.Data.Import.DataSourceFactoryService.OpenDataSourceWithoutPrompting(DataSource dataSource, DocumentOpenSettings documentOpenSettings, IServiceProvider serviceProvider)
    at Spotfire.Dxp.Forms.Data.Import.DataSourceFactoryService.OpenDataSource(InformationLinkDataSource dataSource, DocumentOpenSettings documentOpenSettings, IServiceProvider serviceProvider)
    at Spotfire.Dxp.Forms.Application.FormsProgressService.ProgressThread.DoOperationLoop()
    
    InformationModelException at Spotfire.Dxp.Data:
    Failed to execute query: The query returned no result set (HRESULT: 80131500)
    
    Stack Trace:
    at Spotfire.Dxp.Data.InformationModel.InternalInformationModelManager.DataStream.GetNextBlock()
    at Spotfire.Dxp.Data.InformationModel.InternalInformationModelManager.DataStream.Read(Byte[] buffer, Int32 offset, Int32 count)
    at Spotfire.Dxp.Internal.Utilities.SeekableStream.Read(Byte[] buffer, Int32 offset, Int32 count)
    at System.IO.BinaryReader.FillBuffer(Int32 numBytes)
    at System.IO.BinaryReader.ReadUInt16()
    at Spotfire.Dxp.Data.Formats.Sbdf.SbdfSectionHeader.ReadMagicNumber(BinaryReader reader)
    at Spotfire.Dxp.Data.Formats.Sbdf.SbdfSectionHeader.ReadSectionType(BinaryReader reader, SbdfSectionTypeId expectedType)
    at Spotfire.Dxp.Data.Import.SbdfDataRowReader.DelayedInit()
    at Spotfire.Dxp.Data.Import.InformationLinkDataSourceConnection.ExecuteInformationLink()
    at Spotfire.Dxp.Data.Import.InformationLinkDataSourceConnection.ExecuteQueryCore2()
    at Spotfire.Dxp.Data.DataSourceConnection.ExecuteQuery2()
    
    InformationModelServiceException at Spotfire.Dxp.Services:
    Failed to execute query: The query returned no result set (HRESULT: 80131509)
    
    Stack Trace:
    at Spotfire.Dxp.Services.WebServiceBase`1.InvokeService[T](ServiceMethod`1 serviceMethod, ExceptionFactoryMethod exceptionFactoryMethod, String customMethodNameForLogging)
    at Spotfire.Dxp.Services.Data.InformationModel.QueryManagerService.InvokeService[T](ServiceMethod`1 serviceMethod)
    at Spotfire.Dxp.Data.InformationModel.InternalInformationModelManager.DataStream.GetNextBlock()
    
    Reply
Steve Piper says:

June 18, 2019 at 6:05 am

Hi Vidya,
This is very helpful, but I’m looking to tale it a stage further. I need to write a whole Spotfire datatable to SQL server.
Can I pass a whole datatable to the stored procedure and then have it insert all the rows into the SQL table?
If so how do I set this up ?
Many thanks for any help you can give me on this

Reply
Basha says:

December 19, 2019 at 5:47 am

Hi Vudya Sagar,

This is very good session, i am able to execute it from my end. But when i am adding the date field and using that procedure in spotifre it was not working. Could you please provide some help with below code.

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

AS
BEGIN

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

SELECT * FROM [dbo].[UserRegistration]

END;
GO

Thanks,
Basha

Reply
Mahavir Gandhi says:

April 2, 2020 at 10:06 am

Hi Vidya,

I have developed spotfire writeback functionality using stored procedure.
Can you let me know how can we pass multiple values to stored procedure for write functionality.

Thank you for help !!!

Reply
Hamed says:

June 11, 2020 at 1:03 pm

Hi
Vidya Sagar
Really appreciate great work it will much value to my work
Could you please help how I can get this work with excel database as Ian using excel as database
Thanks Ahmed

Reply
Moldir says:

August 3, 2020 at 4:44 pm

Hello,
How to do the update of the fields of row/line rather than deleting whole line/row?
Thank you

Reply