SQL Query (ISqlQuery)
Overview
An SQL Query represents a block of SQL statements that are executed against a database connection during install, uninstall, or maintenance operations. Queries can be defined inline as text or linked to an external SQL script file. They are processed as part of a connection (Custom, Predefined, or SQLite) and executed at runtime by Advanced Installer.
The query interface allows you to configure error handling, specify statement separators (such as ; for SQLite or GO for SQL Server), strip comments, and parameterize SQL using installer properties or token replacements.
Methods
- AddDataBindingProperty (string propertyName) Adds a Windows Installer property name that can be substituted at runtime (parameterization).
- RemoveDataBindingProperty (string propertyName) Removes a previously added data-binding property.
- NewScriptReplacement (string token, string value) → ISqlScriptReplacement Creates a script replacement entry that substitutes token with value before execution. Returns the created ISqlScriptReplacement.
- DeleteScriptReplacement (ISqlScriptReplacement replacement) Deletes the specified ISqlScriptReplacement from this query.
Properties
- Name (string) {get}{set} Gets or sets the display name of the query.
- Connection (Variant) {get}{set} Gets or sets the connection this query runs against (ISqlCustomConnection, ISqlPredefinedConnection, or ISqliteConnection).
- InlineScript (string) {get}{set} Gets or sets the inline SQL text to execute.
- ScriptFile (string) {get}{set} Gets or sets the path to an external SQL script file to execute (project-relative or absolute path).
- StatementSeparator (string) {get}{set} Gets or sets the separator used to split the script into individual statements (e.g., ; or GO).
- StripComments (bool) {get}{set} When true, removes SQL comments before execution.
- AbortInstallationOnError (bool) {get}{set} When true, aborts the installation if this query fails.
- DataBindingProperties (SAFEARRAY(string)) {get} Returns the collection of data-binding property names for this query.
- ScriptReplacements (SAFEARRAY(Variant)) {get} Returns the collection of ISqlScriptReplacement entries defined for this query.
Sample commands
# ISqliteConnection usage for ISqlQuery
$advinst = New-Object -ComObject "AdvancedInstaller"
$project = $advinst.CreateProject($advinst.ProjectTypes.Enterprise)
# Create a SQLite connection (required before adding queries)
$sqlite = $project.SqlDatabasesComponent.NewSQLiteConnection("SQLite Query Sample")
$sqlite.DatabaseFile = "[APPDIR]\data\app.db"
$sqlite.CreateDatabaseIfNotExists = $true
$sqlite.Use64bitOdbcResource = $true
$sqlite.LoginTimeout = 30
$sqlite.IsVerbose = $true
$sqlite.LogErrorsToFile = $true
# Add an inline query
$q = $sqlite.NewQuery("Create Demo Table")
$q.StatementSeparator = ";"
$q.InlineScript = @"
CREATE TABLE demo (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
"@
# Add a query from an external SQL file
$queryFromFile = $sqlite.NewQuery("Sample Query from file")
$queryFromFile.ScriptFile = "F:\cmdlet\res\demo-sql-script.sql"
$queryFromFile.StatementSeparator = ";"
$queryFromFile.AbortInstallationOnError = $true
# Add script replacements to substitute tokens in the SQL file
$queryFromFile.NewScriptReplacement("DB_NAME", "DemoDB")
$rep = $queryFromFile.NewScriptReplacement("TABLE_NAME", "DemoTable")
# Remove a script replacement (optional)
# $queryFromFile.DeleteScriptReplacement($rep)
# List queries
$sqlite.Queries
# Delete a query (optional cleanup)
# $sqlite.DeleteQuery($q)
# Remove the connection (optional cleanup)
# $project.SqlDatabasesComponent.DeleteDatabaseObject($sqlite)
$project.SaveAs("F:\cmdlet\output\SQLite Query Sample.aip")