Connect to a DB Programatically via ODBC (PIOLEDB)

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

Category=Document.Properties["Category"]
State=Document.Properties["State"]

dbSettings=DatabaseDataSourceSettings("System.Data.Odbc","DSN=DSN Name","Dynamic SQL Query")

ds=DatabaseDataSource(dbSettings)

Document.Data.Tables["Test1"].ReplaceData(ds)


##Example using PIOLEDB
CSTR = (
   "Provider=PIOLEDBENT.1;"
   "Initial Catalog=DPDSDashboard;"
   "Data Source=piserver.americas.ent.yourcompany.net;"
   "Integrated Security=SSPI;Persist Security Info=False"
)
PROVIDER ="System.Data.OleDb"

SQL = (
 " SELECT * "
 " FROM [XXX].[A].[E] T1"
 " CROSS APPLY [XXX].[D].[T](T1.E, DATE('t+6h+59m'), DATE('t+6h+59m'), '1d')"
 " WHERE T1.Path LIKE N'\PROD\EF\HV\HV\%' AND T1.level=6"
)

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

dbSettings=DatabaseDataSourceSettings(PROVIDER,CSTR,SQL)

ds=DatabaseDataSource(dbSettings)
Document.Data.Tables["pi test"].ReplaceData(ds)