Spotfire Writing back to a database

Lot of time users need to write back to a database.

Spotfire provides different ways to do this, Without going thru the code I am going to talk thru the different options available. But if you need detailed instructions, please leave some comments and will try to provide one

1) Stored Procedures.


Advantages : Complex logic is easy to encapuslate in procedures. Also you are not storing username/passwords anywhere 

Spotfire can execute stored procedures and these procedures could be fetching data, or can be procedures that can be run Pre or Post running a query. You can then use procedures that basically update your database table or even inserts new rows in tables.

When using Spotfire information model it allows you to use these procedures which you can repurpose for updating your database.
Spotfire also understands inputs to a procedure and they can be mapped to your marked rows/filtered rows/ properties/constants etc from your analytics.

Steps to do that would be
1)Create a procedure that accepts inputs e.g. what is the value you want to update and some sort of key column(s) identifier for the record to update.
2) Create a Spotfire procedure element
3) Depending if you procedure is going to bring back some data or not, use the query or pre or post option correctly.
4) Create an Information Link that uses this Procedure element.
5) Ensure that your inputs to procedures are parameters and then use ?name syntax to map them to parameters.
6)Now in your analysis you can add this information link and chose to make it on demand.
7)Map you inputs to desired marking, filtering or property controls etc.
8) Generally I would recommend not using Automatic updates to avoid unnecessary write backs and also not select caching to ensure the procedure is run every time you need to.

9)Then the last step is writing a small ironpython script trigger the procedure whenever desired. Attach the script to a button or a link as desired in text area

if ondemandTable.IsRefreshable and ondemandTable.NeedsRefresh:
   ondemandTable.Refresh()
   dataTable.Refresh()

2) Information Links
Advantages : Quick and can be done right from within spotfire information designer, without having to define Procedures
Username and passwords are used from information model and hence not stored anywhere in clear text.

Create an information link, that will bring back some dummy data, or may be data that you want to bring back to spotfire after updating a table etc.

Once you have created an information link, hit the SQL button
In the Edit SQL Dialog, there is an option to provide preupdate query and also a post update query. In our case we will use the preupdate query option to write a query that will write back.
You can parameterize the query with the ?varname syntax, so your query could look like

Update tablename set salestotal = ?inputsalesvalues where record_id=?myrecordid

Then proceed to do steps 6 thru end as mentioned in method 1

3) Iron Python Script
Advantages : You get a lot of control over using ironpython to massage your data.

You can use the link below to see details on how to go about doing this http://spotfirecommunity.tibco.com/community/blogs/tips/archive/2010/12/12/writing-calculations-annotations-and-other-values-back-to-the-database.aspx?pcode=100044