Write back to the database from Spotfire

from Spotfire.Dxp.Data.Import import DatabaseDataSource
from Spotfire.Dxp.Data.Import import DatabaseDataSourceSettings
from Spotfire.Dxp.Application.Visuals import TablePlot
from Spotfire.Dxp.Application.Visuals import VisualTypeIdentifiers
from Spotfire.Dxp.Data import IndexSet
from Spotfire.Dxp.Data import RowSelection
from Spotfire.Dxp.Data import DataValueCursor
from Spotfire.Dxp.Data import DataSelection
from Spotfire.Dxp.Data import DataPropertyClass

rowCount = Document.ActiveDataTableReference.RowCount
rowsToInclude = IndexSet(rowCount,True)

#Get a cursor to the two columns we want to use. cursor1 is for the key column and cursor2 is for the column selected by the user input
cursor1 = DataValueCursor.Create[int](Document.ActiveDataTableReference.Columns["ProductID"])
cursor2 = DataValueCursor.CreateFormatted(Document.ActiveDataTableReference.Columns[whichCol])

#The following section will add a column to the database table using the name specified by the user. This assumes a column with this name does not already exist.
sqlCommand = "ALTER TABLE Products ADD " + colName + " varchar(50);"
dbsettings = DatabaseDataSourceSettings( "System.Data.SqlClient",
"Server=localhost;Database=Northwind;UID=myuser;PWD=mypass",sqlCommand)
ds = DatabaseDataSource(dbsettings)
newDataTable = Document.Data.Tables.Add("temp",ds)
Document.Data.Tables.Remove(newDataTable)

#The following section will update the specified column in the database using the key column in the where clause

sqlStr=""
for  row in  Document.ActiveDataTableReference.GetRows(rowsToInclude,cursor1,cursor2):
   value1 = cursor1.CurrentValue
   value2 = cursor2.CurrentValue
   sqlStr = sqlStr +  "UPDATE Products SET " + colName + "='" + value2 + "' WHERE (ProductID=" + str(value1)  + ");"

sqlCommand = "UPDATE Products "  + sqlStr + ";"
dbsettings = DatabaseDataSourceSettings( "System.Data.SqlClient",
"Server=localhost;Database=Northwind;UID=myuser;PWD=mypass",
sqlCommand)
ds = DatabaseDataSource(dbsettings)
newDataTable = Document.Data.Tables.Add("temp",ds)
Document.Data.Tables.Remove(newDataTable)


Reference

13 comments:

Anonymous said...

is this iron python scripting?

Leviaguirre said...

It is, and it is using the Spotfire API to do all this stuff. Check the reference link for more info

Anonymous said...

Thanks for finding this hack, it's funny how you have to get the table API to do the job. By the way, you may need to correct the typo, looks like you meant sqlCommand in the DatabaseDataSourceSettings call

Leviaguirre said...

Thanks for pointing out the typo. This work was based from http://spotfirecommunity.tibco.com/community/blogs/tips/archive/2010/12/12/writing-calculations-annotations-and-other-values-back-to-the-database.aspx

The Alexu Boy said...

I am unable to write into the database, I keep getting the following error,

Spotfire.Dxp.Data.Exceptions.ImportException: Failed to execute data source query. ---> Spotfire.Dxp.Data.Exceptions.ImportException: Failed to execute data source query. ---> System.Exception: System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.

Do you know why this is happening?

Jose Leviaguirre said...

before you do scripting for that, have you tried parameterized information links and use the pre-updates option on your information link? Edit the IL and click on the SQL button. On the Pre-Updates type your insert statement and on the query radio option, do a regular select statement because information links expect an output.

Blake said...

I am unsure what the exact syntax is for DatabaseDataSourceSettings when connecting to a SQL Server. Any Ideas?
The errors messages I keep getting:
File "Spotfire.Dxp.Data", line unknown, in Add
File "Spotfire.Dxp.Data", line unknown, in Add
File "Spotfire.Dxp.Framework", line unknown, in InternalTransaction
File "Spotfire.Dxp.Data", line unknown, in b__36
File "Spotfire.Dxp.Framework", line unknown, in Insert
File "Spotfire.Dxp.Framework", line unknown, in InternalTransaction

Jose Leviaguirre said...

Blake, another way to write back to database is by using parameterized information links. I'll post something soon

Danger bros Porter said...

I'm interested in editing in spotfire and then posting back to the database - can this be done with free text, information link and a stored procedure?

Jose Leviaguirre said...

yes. Your pre-updates statement in IL works the same way as the SQL statement. The SQL will be executed before the data retrieval. This could be a stored procedure or statements such as update or insert. The post-updates executes after the data retrieval.

Swagata said...

Is there any way to achieve write back without using scripting in Spotfire?

Jose Leviaguirre said...

yes, by using the pre-update and post-update information links

Rayees said...

Hi All,

I was successful in doing a write back functionality to DB, but couldn't find how to close the connection safely.
Now if n no. of users are accessing this script, then probably that many no. of connections will be created and cause a memory concern.

So please help me in closing the connection safely.

Regards,
Rayees