Write back to database from Spotfire using R Data Functions

Introduction

R and Python are great programming languages that can be leveraged with TIBCO Spotfire via Data Functions. R Data functions have been around for some time while Python data functions were introduced in version 10.7. These languages contain many packages. Some of these packages makes it easy to write back to databases, images, files or connect to data sources. In this example we are going to connect to a ODBC data source and perform SQL queries to insert data back to the database.

Setup a System DSN 

Whether you are in Linux, Windows or Mac, setup a System Data Source Name (DSN). You will need the proper driver for your databas. In this example we are usin PostgreSQL

Create R Data functions

Install required packages 

For the R data function to run, you need to install odbc and DBI packages using TERR tools. TERR tools are available from the Tools menu. If you do not see this option, ask your system administrator to enable it. 

Register a new data function from Tools > Register data functions... and make sure packages are properly installed by running an empty script:

Close the parameters window and make sure the empty script did not throw any errors. Look at the top right bell  for errors.  If you see a bell with a red dot circle, it means the packages are not properly declared or installed. If there are no errors, click [No] on the Register Data Function promp to embed the data function in the analysis. We can write to the library later. 

Edit the R data function

Go to Data > Data Function Properties... and edit the script and setup proper input parameters. This function has no outputs.

#prepare SQL statement
dt<-Sys.time()
sql = sprintf("INSERT INTO rating(dt, name, value) VALUES ('%s', '%s', %s)",dt,comments,rating)

#execute insert statement
con <- DBI::dbConnect(odbc::odbc(), dsn="PostgreSQL35W")
dbExecute(con, SQL)

To run the data function, add a button on a text area and link the data function to it

If you need to see the values right after inserting the values in the database, add the datetime variable (dt) as an output parameter to the R script to point to a DateTime document property (dateR)

Then make the datatable setup on demand and set the data table to load automatically by setting the date column as a Range. Leave Min blank and Max to point to the RDate document property. This will update the database with all the records up to date.

See also

• Writing to files and databases from Spotfire