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

20

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:

  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

    • vidya Sagar Malla says:

      Can you share me the error logs?

      • Egor says:

        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()

  8. Steve Piper says:

    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

  9. Basha says:

    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

  10. Mahavir Gandhi says:

    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 !!!

  11. Hamed says:

    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

  12. Moldir says:

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

Leave a Reply

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

seven + eleven =