Using SQL inside an MSI database custom action

This blog entry provides an example of using SQL inside an MSI database custom action with VBScript. It follows on from the previous blog post which provided a tutorial on generating transforms for an MSI database using VBScript.

It forms part 14 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.

As well as creating administrative scripts like the previous examples, we can also use SQL queries during the installation of an MSI. When an MSI is installing, we
can refer to this as a ‘session’. Take the following excerpt – this is from a tutorial I wrote regarding using VBScript Custom Actions to automate the installation and removal of Excel add-ins:

...
...
Dim sql : sql = "SELECT File.FileName,File.Component_,Component.Directory_ FROM File, Component WHERE File.Component_ = Component.Component"
'start searching through file table for add-ins (.XLA or .XLL files)
Set fileView= Session.Database.OpenView(sql)
fileView.Execute
Set fileRecord = fileView.Fetch
While Not fileRecord Is Nothing
componentState = Session.ComponentRequestState(fileRecord.StringData(2))
'if component is being installed locally
If componentState = 3 Then
'get filename
tempFileName = LCase(fileRecord.StringData(1))
If InStr(tempFileName,"|") Then
'if filename is currently in sfn form, try and retrieve the full file name
tempFileName = Split(tempFileName,"|")(1)
End If 
'retrieve 3-length extension (that's all
tempExtension = Right(tempFileName,Len(tempFileName)-InStrRev(tempFileName ,"."))
If (tempExtension = "xla" OR tempExtension = "xll") Then       
'return full path to Excel add-in file.
filePath = Session.TargetPath(fileRecord.StringData(3)) & tempFileName          
'install/add add-in using automation....(logic not included in this example)
End If 
End If 
Set fileRecord = fileView.Fetch
Wend
fileView.Close
Set fileView = Nothing
Set fileRecord = Nothing
...
...

You can see that we don’t explicitly create an Installer object now, because we’re already in the session of one! Instead, we use this syntax:

Session.Database.OpenView(sql)

another handy tip in here is the use of:

Session.ComponentRequestState(fileRecord.StringData(2))

to detect if our session is installing/uninstalling or repairing. In this example, I’m detecting if we’re installing and if so I search for all Excel add-ins, finding their install locations by using:

Session.TargetPath

Thanks for reading about using SQL inside an MSI database custom action with VBScript. Next you can find out how to write to the windows installer log file from a custom action using VBScript.