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:
another handy tip in here is the use of:
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:
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.