How to WriteBack to a Database using ODBC in Spotfire® Using IronPython Scripting

Introduction

This article provides a sample script on how to writeback to any data base by specifying the ODBC driver name installed on the machine in the connection string where Analyst or WebPlayer is installed. Below script is an example for writing back to MSSQLServer and so the driver name is given as "SQL Server" which is the name seen in the ODBC Administrator as below,

Similarly in case of an Oracle Datasource the driver name should be given as "Oracle in OraClient 12Home1"  etc.,

Code Sample

# Copyright © 2019. TIBCO Software Inc. Licensed under TIBCO BSD-style license.

import clr
import System
from Spotfire.Dxp.Data.Import import DatabaseDataSource
from Spotfire.Dxp.Data.Import import DatabaseDataSourceSettings
import System

#give the sql query
sql="mysqlcommand"

#Specify the driver name in the connection string
dbSettings=DatabaseDataSourceSettings("System.Data.Odbc","Driver=SQL Server;server=servername;Database=spotfire_demodata;Integrated Security=False;Uid=sa;Pwd=password",sql,True)

#create the datasource
ds = DatabaseDataSource(dbSettings)

#Add a new table or replace a datable
tblfound= Document.Data.Tables.Contains("temp10")
if tblfound == False:
	Document.Data.Tables.Add("temp10",ds)
if tblfound == True:
	Document.Data.Tables["temp10"].ReplaceData(ds)

See also

• Writing to files and databases from Spotfire

References

• API Reference: DatabaseDataSource

License:  TIBCO BSD-Style License