This blog entry provides an example of using inner joins to read from an MSI database using VBScript. It follows on from the previous blog post which provided a tutorial on handling tables that don’t exist in an MSI database using VBScript.
It forms part 11 of an 17-part series that explores how to use VBScript to manipulate MSI relational databases using the Windows Installer API. Throughout this series of tutorials, we identify the common issues that we encounter and the best practises that we use to overcome them.
Let’s say we want to know what directory a file gets installed too. In order to do that, we’d need to read the FileName column from the File table, and the Directory_
column from the Component table.
We can see that the File table includes a foreign key into the Component table (Component_). For this reason, we’ll join the two tables where:
File.Component_ equals Component.Component
'create 2 constants - one for when we want to just query the MSI (read) and one for when we want to make changes (write) Const msiOpenDatabaseModeReadOnly = 0 Const msiOpenDatabaseModeTransact = 1 'create WindowsInstaller.Installer object Dim oInstaller : Set oInstaller = CreateObject("WindowsInstaller.Installer") 'open the MSI (the first argument supplied to the vbscript) Dim oDatabase : Set oDatabase = oInstaller.OpenDatabase(WScript.Arguments(0),msiOpenDatabaseModeReadOnly) Dim sql : sql = "SELECT `File`.`FileName`, `Component`.`Directory_` FROM `File`, `Component` WHERE `File`.`Component_` = `Component`.`Component`" 'create a view of the registry we want to see Dim fileView : Set fileView = oDatabase.OpenView(sql) 'execute the query fileView.Execute 'fetch the first row of data (if there is one!) Dim fileRecord : Set fileRecord = fileView.Fetch 'whilst we've returned a row and therefore fileRecord is not Nothing While Not fileRecord Is Nothing 'print out the registry key wscript.echo "The file '" & fileRecord.StringData(1) & "' gets installed to the directory '" & fileRecord.StringData(2) & "'" 'go and fetch the next row of data Set fileRecord = fileView.Fetch Wend fileView.Close Set fileView = Nothing Set fileRecord = Nothing Set oDatabase = Nothing Set oInstaller = Nothing
Thanks for reading about using inner joins when reading from an MSI database. Next you can find out how to apply MST transforms to an MSI database using VBScript.