08/02/2014

[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+

4 comments:

  1. Does this work with version 4.5? I tried using this, but it does not seem to work, although I do not get an error with the python script.

    ReplyDelete
    Replies
    1. Cheers,

      I didn't test it on versions lower than 5.5, sorry. However, before saying it would definitely not work on 4.5, I would need some more debug information for example:
      - can you confirm that the script gets invoked correctly and runs until the end?
      - can you confirm that the SQL scripts to see if they are correctly invoked, executed and committed (maybe add some debug inserts or write to log)

      I haven't used version 4.5 myself, but if the example structure can be replicated in 4.5 (pre-query, query, mapping and script invocation) I don't see why it shouldn't work since it's a pretty basic idea after all.

      Having said that, I must also remind you that the official TIBCO support (https://support.tibco.com/login.htm) is always available to answer your questions and that you should always trust and apply their more experienced instructions when developing for a production environment.

      Have a nice day

      Delete
    2. My procedure works just fine when I run it in PL/SQL, and the script does get executed till the end (I used a print function at the end to check this). Is there any other way I can see where the error is, since the python script is not showing me any error, and so is the SQL proc..

      Delete
    3. Hi,

      I'm sorry I can't help you with this. If you have access to the official TIBCO support you may try asking them or you may try asking on the TIBCOmmunity website (free registration - https://www.tibcommunity.com/) under the Spotfire section.

      Regards

      Delete

With great power comes great responsibility