How to write back to database in Spotfire® Using IronPython Scripting

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

• Writing to files and databases from Spotfire

References

• API Reference: DataValueCursor
• API Reference: DatabaseDataSourceSettings
• API Reference: DatabaseDataSource

License:  TIBCO BSD-Style License