In general, Spotfire only reads data from underlying datasources and does not add, edit, delete or in any other way modify the original data. However, there are a variety of use cases where you would want to writeback information to the underlying data source. It could be calculated values, annotations, or anything else which was added to your data table during your analysis session.
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 tip will discuss using Property Controls and Script Controls, and will writeback an entire column back into the database.
First, we need to setup a Text Area. In this Text Area, we will setup 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.
Next, we need to create a Script Control. This Script Control will take in two parameters; specifically the values from the properties listed above.
The script will use code we discussed in an earlier tip to find records in the data table. It will search for two columns; the one specified to writeback to the database, and a primary key column used to match records in the database. In our solution, we hard code the key column, but this could be exposed as an additional property control.
The script will use the DatabaseDataSource class to communicate directly with the underlying database (in our example it is a SQL Server database).
The full script is shown below:
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=Northwind;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)
Once the solution is implemented, and the user clicks on the script button from within the Text Area, the correct information is written back to the database.
This solution works well for something very quick and for small datasets, but is not recommended for production on large scale analysis files with lots of data. This is because it requires a connection to to the database from each client directly, it exposes the user credentials in the script as clear text, and the code is written in a scripted language, so performance on large data tables would be less than optimal.
For a more efficient and production-ready solution, you should use the SDK to create a Custom Tool which utilizes compiled C# code. If you are interested in learning more about how to do this, or even having this solution built for you , please contact our Professional Services organization or consider attending our Developer Bootcamp training course.
