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
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
ConversionConversion EmoticonEmoticon