Using inner joins to read from an MSI database

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.

Tip: All foreign key column names end with an underscore (_).

 

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.