Insert data to database using Information Links

Introduction

Information links are predefined database queries. They require Java Drivers (JDBC) on the server to create data connections. Writing back using information links can be a handy tool to execute queries before or after the data is loaded or execute complex database queries using stored procedures. One great advantage of using Information Links is the ability to pass parameters from markings or document properties.   To create information links you need the Information Designer tool for setting up data sources. Information links are created from columns, filters, joins, stored procedures, etc.  

Information Links that insert data

To create an information link that inserts data, we need to edit the underlying SQL from the Information Link. The insert statement can happen before or after the main query is executed. To insert data before the query runs, use the Pre-Updates option. Use the Post-Updates section to execute after the main query is run. The main query is expecting a result back from the database. The insert or update SQL script is triggered every time the information link is refreshed. In this case, we are going to return one record with just the timestamp. Information links that insert data can also return all rows from a table. It depends on the use case. 

Steps for creating an information link that inserts data

1. From Data > Information Designer create an Information Link.
2. Add a date column as a placeholder to it. The date column can come from any table, but it must be of the same data type the edited SQL will return the data. There must be the same number of columns the query returns.
3. Edit the SQL

   3.1 Edit the Query SQL to return the current timestamp

   3.2 Edit the Pre-Updates SQL statement to insert records to the database before the Query statements run. Use the Post-Updates to perform a database operation after the Query statement executes. Note the? before the name and val parameters

   3.3 Click OK

4. Expand the parameters section and click on the refresh button. The parameters from step 3.3 should display.
5. Click the Save button. If there are no errors, the Information Link is saved successfully
6. Click Close

Add the Information link in the analysis

Add the new Information Link to the analysis and configure the Data on demand parameters

Uncheck the Load automatically and allow caching. This will ensure the execution of the queries every time the information link is refreshed.  In this case, the val and name are the parameters going to the pre-updates section of the query. These can be mapped to document properties or hardcoded. The date parameter is embedded in the insert SQL statement with the CURRENT_TIMESTAMP keyword.

Execute the information link manually

The information link can be executed manually by refreshing the data table driven by that Information link from the canvas. If the information link has on-demand parameters, it will detect when the parameters change. Any visualization linked to this on-demand information link will prompt to refresh if it is not set to load automatically. 

If the On-demand settings are set to Load Automatically, the information link will execute every time the value changes. We do not want this in this case because it will result in executing the script every time the value changes. Imagine if we are using a slider property control! It will update the database as we slide through the values!  In this case, we want the Load automatically option unchecked. 

Execute the information link programmatically

The submit button from the previous image refreshes the Information links using the following IronPython script:

 Document.Data.Tables["rating_write_back"].Refresh() 

Using Stored Procedures in Information Links

This approach shares very similar steps as when "Editing Information Link SQL to insert data" from above.  Although stored procedures can be called directly by editing the information link SQL and passing parameters the same way, sometimes tweaking the SQL is not allowed for some users depending on the permissions and licensing settings.

As a side note, using stored procedures to receive data is safer and can increase performance because if the table structure or column changes, it will not impact the information link. One day the stored procedure can return one column and the next day can return many columns and the information link will work the same.

Stored procedure declaration

In this example, the stored procedure adds a row to the rating table and returns the data inserted. The stored procedure SQL declaration is:

CREATE OR REPLACE FUNCTION sp_writeback(a varchar, b int) RETURNS setof rating AS $$
  insert into rating values (CURRENT_TIMESTAMP,a,b);
  SELECT dt,name,value FROM public.rating ORDER BY dt DESC LIMIT 1;
  $$ LANGUAGE sql;

Create a stored procedure element in Information Designer

1. From the Information Designer window, add a procedure and select the procedure from the Data Sources tab
2. Click select to set the path of the stored procedure schema
3. Since the stored procedure returns data, use Query as the procedure type
4. Add the default value parameters. They can differ in name but must be preceded by a question mark
5. Click the [save] button. If the query is valid, it will allow the information link to be saved.
6. Close the dialog and you are done.

Create an Information Link with the Stored Procedure element

1. Create an Information Link
2. Select the Spotfire stored procedure element and click [Add >]
3. Expand Parameters and click the [Refresh] button to refresh the stored procedure parameters
4. Click [save]. If the Information link is valid, it will save successfully
5. Click [Close] and you are done

Add the Information link to the analysis

In this case, we are going to take advantage of the stored procedure by returning the inserted record to our main data table so that when the Stored Procedure Information Link runs, the record is added to the main data table. This step is optional as the information link can be set as a new data table.

This approach prevents running two different scripts, one to refresh the stored procedure information link and the other to refresh the main data table. By refreshing the main data table when the stored procedure information link is set up as adding rows, the stored procedure is executed. In other words, there is no need to refresh the stored procedure but the table that is linked to it. 

The final step is to Map the On-Demand Settings parameters to the corresponding document property and make sure the load automatically and allow caching are turned off

Add a script to refresh the Main data table. This will force the information link to trigger first when the stored procedure information link is linked to the "ratings" main data table 

 # Reload main data table
 Document.DataTables["ratings"].Refresh() 

See also

• Writing to files and databases from Spotfire