Jump to content
We've recently updated our Privacy Statement, available here. ×
  • How to write back to database in Spotfire® Using IronPython Scripting


    There are variety of ways to accomplish a database writeback from within Spotfire, all with varying levels of complexity and efficiency. You can use an Information Link to writeback to a stored procedure. You can use the SDK to create a Custom Tool which performs the necessary steps or you can utilize Property Controls and Script Controls to accomplish this. This article will discuss using Property Controls and Script Controls which will writeback an entire column back into the database.

    Introduction

    There are variety of ways to accomplish a database writeback from within Spotfire, all with varying levels of complexity and efficiency. You can use an Information Link to writeback to a stored procedure. You can use the SDK to create a Custom Tool which performs the necessary steps or you can utilize Property Controls and Script Controls to accomplish this. This article will discuss using Property Controls and Script Controls which will writeback an entire column back into the database.

    Steps

    • Add a Text Area and in this Text Area create two property controls. The first one will display a list of columns from your data table which you may want to writeback to the database. (This assumes it is an entire column or columns that you want to writeback and not just specific values). The second control is an input field which allows the user to specify the name of the Column to be added or replaced in the database
    • Need to create a Script Control. This Script Control will take in two parameters, specifically the values from the properties listed above.

       

    Code Sample

    # Copyright © 2017. TIBCO Software Inc. Licensed under TIBCO BSD-style license.
    
    from Spotfire.Dxp.Data.Import import DatabaseDataSource
    from Spotfire.Dxp.Data.Import import DatabaseDataSourceSettings
    from Spotfire.Dxp.Application.Visuals import TablePlot
    from Spotfire.Dxp.Application.Visuals import VisualTypeIdentifiers
    from Spotfire.Dxp.Data import IndexSet
    from Spotfire.Dxp.Data import RowSelection
    from Spotfire.Dxp.Data import DataValueCursor
    from Spotfire.Dxp.Data import DataSelection
    from Spotfire.Dxp.Data import DataPropertyClass
    
    rowCount = Document.ActiveDataTableReference.RowCount
    rowsToInclude = IndexSet(rowCount,True)
    
    #Get a cursor to the two columns we want to use. cursor1 is for the key column and cursor2 is for the column selected by the user input
    
    cursor1 = DataValueCursor.Create[int](Document.ActiveDataTableReference.Columns["ProductID"])
    cursor2 = DataValueCursor.CreateFormatted(Document.ActiveDataTableReference.Columns[whichCol])
    
    #The following section will add a column to the database table using the name specified by the user. This assumes a column with this name does not already exist.
    
    sqlCommand = "ALTER TABLE Products ADD " + colName + " varchar(50);"
    dbsettings = DatabaseDataSourceSettings( "System.Data.SqlClient","Server=localhost;Database=myDB;UID=myuser;PWD=mypass",sqlCommand)
    ds = DatabaseDataSource(dbsettings)
    newDataTable = Document.Data.Tables.Add("temp",ds)
    Document.Data.Tables.Remove(newDataTable)
    
    #The following section will update the specified column in the database using the key column in the where clause
    sqlStr=""
    for  row in  Document.ActiveDataTableReference.GetRows(rowsToInclude,cursor1,cursor2):
       value1 = cursor1.CurrentValue
       value2 = cursor2.CurrentValue
       sqlStr = sqlStr +  "UPDATE Products SET " + colName + "='" + value2 + "' WHERE (ProductID=" + str(value1)  + ");"
    
    sqlCommand = "UPDATE Products "  + sqlStr + ";"
    dbsettings = DatabaseDataSourceSettings( "System.Data.SqlClient",
    "Server=localhost;Database=Northwind;UID=myuser;PWD=mypass",sqlStr)
    ds = DatabaseDataSource(dbsettings)
    newDataTable = Document.Data.Tables.Add("temp",ds)
    Document.Data.Tables.Remove(newDataTable)
     

     

    See also

    References

    License:  TIBCO BSD-Style License


    User Feedback

    Recommended Comments

    There are no comments to display.


×
×
  • Create New...