[TIBCO Spotfire] Write data to database

TIBCO Spotfire primarily reads data from a DB to populate analysis and reports, but it is also possible to have it write data back on the database.

Doing so is fairly easy; the key point is to remember that the last operation in the Information Link MUST return data back. This means that your procedure that performs the desired operation (INSERT, UPDATE, DELETE) MUST return data back, even if usually you wouldn't do that in standard SQL.

For Microsoft SQLServer databases, this is easily accomplished by putting a SELECT statement at the end of the script, but with Oracle it gets a little trickier since we cannot simply put a SELECT at the end of our function/procedure.

The easiest way to have it work on Oracle too then, is to configure Spotfire's Information Link so that it performs the query itself AFTER it executes our procedure:

1. Create the INSERT/UPDATE/DELETE procedure on Oracle using only IN parameters (if needed)
2. Add the procedure as a Spotfire procedure from the Information Designer, and configure (if any) the IN parameters as ?NAME in the Default Value field
3. Create an Information Link and set the Spotfire procedure as a PRE-QUERY.
4. Add a QUERY step and write a bogus SELECT query that returns some data, and save the Information Link
5. Create a new Information Link and add only a QUERY step to SELECT all the data from the table you modified in your procedure
6. Add, if needed, Action Controls to your page(s) using IronPython scripts to call the Information Link

This way, you decoupled the "Run procedure" and "Refresh data" steps, and you can display input fields inside the page and add a button that will invoke the Information Link when clicked:

 from System import Array,String,Object  
 from Spotfire.Dxp.Data.Import import InformationLinkDataSource, InformationLinkParameter  
   
 #The next line will specify the parameter name. This name is shown in the Information Designer  
 paramName = "Parameter.MYPARAMETER"  
 #The next line will specify the values going into the parameter. It needs to be an array, so you pass in the data type (string in this case) and then the value(s)  
 paramValues = ("MYDATATYPE", "MYVALUE")  
   
 ilParams = InformationLinkParameter.CreateNamedParameter(paramName, paramValues)  
   
 #Below you pass in the GUID of the information link which has the parameter and then the array of InformationLinkParameters  
 ilDataSource = InformationLinkDataSource("MYUID", [ilParams])  
   
 #Add the Information Link data as a new data table  
 Document.Data.Tables.Add("MYDATATABLE",ilDataSource)  

This solution was tested on Spotfire 5.5+