Examples of Database Queries Using SQL and Script
An example of using script-driven database queries is provided in the Windows Installer Software Development Kit (SDK) as the utility WiRunSQL.vbs. This utility handles database queries using the Windows Installer version of SQL described in the section SQL Syntax.
Delete a record from a table
The following command line deletes the record having the primary key RED from the Feature table of the Test.msi database.
Cscript WiRunSQL.vbs Test.msi "DELETE FROM `Feature` WHERE `Feature`.`Feature`='RED'"
Add a table to a database
The following command line adds the Directory table to the Test.msi database.
CScript WiRunSQL.vbs Test.msi "CREATE TABLE `Directory` (`Directory` CHAR(72) NOT NULL, `Directory_Parent` CHAR(72), `DefaultDir` CHAR(255) NOT NULL LOCALIZABLE PRIMARY KEY `Directory`)"
Remove a table from a database
The following command line removes the Feature table from the Test.msi database.
Cscript WiRunSQL.vbs Test.msi "DROP TABLE `Feature`"
Add a new column to a table
The following command line adds the Test column to the CustomAction table of the Test.msi database.
CScript WiRunSQL.vbs Test.msi "ALTER TABLE `CustomAction` ADD `Test` INTEGER"
Insert a new record into a table
The following command line inserts a new record into the Feature table of the Test.msi database.
Cscript WiRunSQL.vbs Test.msi "INSERT INTO `Feature` (`Feature`.`Feature`,`Feature`.`Feature_Parent`,`Feature`.`Title`,`Feature`.`Description`, `Feature`.`Display`,`Feature`.`Level`,`Feature`.`Directory_`,`Feature`.`Attributes`) VALUES ('Tennis','Sport','Tennis','Tournament',25,3,'SPORTDIR',2)"
This inserts the following record into the Feature table of Test.msi.
Feature Table
| Feature | Feature_Parent | Title | Description | Display | Level | Directory_ | Attributes |
|---|---|---|---|---|---|---|---|
| Tennis | Sport | Tennis | Tournament | 25 | 3 | SPORTDIR | 2 |
Note that binary data cannot be inserted into a table directly using the INSERT INTO or UPDATE SQL queries. For information see Adding Binary Data to a Table Using SQL.
Modify an existing record in a table
The following command line changes the existing value in the Title field to "Performances." The updated record has "Arts" as its primary key and is in the Feature table of the Test.msi database.
Cscript WiRunSQL.vbs Test.msi "UPDATE `Feature` SET `Feature`.`Title`='Performances' WHERE `Feature`.`Feature`='Arts'"
Select a group of records
The following command line selects the name and type of all controls that belong to the ErrorDialog in the Test.msi database.
CScript WiRunSQL.vbs Test.msi "SELECT `Control`, `Type` FROM `Control` WHERE `Dialog_`='ErrorDialog' "
Hold a table in memory
The following command line locks the Component table of the Test.msi database in memory.
CScript WiRunSQL.vbs Test.msi "ALTER TABLE `Component` HOLD"
Free a table in memory
The following command line frees the Component table of the Test.msi database from memory.
CScript WiRunSQL.vbs Test.msi "ALTER TABLE `Component` FREE"