Inserting a registry value into an MSI database

This blog entry provides an example of inserting a registry value into an MSI database using VBScript.  It follows on from the previous blog post which provided a tutorial on reading registry values from an MSI database using VBScript.

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

Did you notice in the first script we had the following two constants predefined?:

Const msiOpenDatabaseModeReadOnly = 0
Const msiOpenDatabaseModeTransact = 1

We use these when we are opening the Windows Installer database. If we want to just read information we open the database in read-only mode. If we want to manipulate the data inside the database (insert/delete/modify) then we must open it in transation mode.
Here’s how we open the database in transaction mode:

Dim oDatabase : Set oDatabase = oInstaller.OpenDatabase(WScript.Arguments(0),msiOpenDatabaseModeTransact)

Then we change our ‘SELECT’ query to be an ‘INSERT’ query…notice where the single quotes and backticks are used! We enclose table and column names
using backticks, and string values which we pass in using single quotes. In this example, we’ll write the following registry key:

HKLM\Software\AlkaneTest\testName testValue

Because we’re only inserting (and not reading records like we did previously) there is no requirement to create a record object. We can just execute the query:

'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),msiOpenDatabaseModeTransact)

'check if Registry table exists (0 is a temporary table, 1 is a permanent table)
If oDatabase.TablePersistent("Registry") = 1 Then

    Dim sql : sql = "INSERT INTO `Registry` (`Registry`,`Root`,`Key`,`Name`,`Value`,`Component_`) VALUES ('SampleReg',2,'Software\AlkaneTest','testName','testValue','alkaneComponent')"

    'create a view of the query we want to execute
    Dim regView : Set regView = oDatabase.OpenView(sql)

    'execute the query
    regView.Execute 

End If

oDatabase.Commit 'save changes

regView.Close
Set regView = Nothing
Set oDatabase = Nothing
Set oInstaller = Nothing

Tip: Remember that if you insert a duplicate primary key, you will receive an error.

If your SQL is slightly incorrect, you’ll be prompted with a screen similar to the following:

This tells us the line number that the error has occurred on (21). Note that in our case, the line number will be the line where we execute the query. Just remember that the
error will probably be the SQL statement which you’ve executed, which will probably be a few lines before and defined in your SQL variable! If you do receive an
error similar to this, be sure to check all your quotes, backticks, table names (and their case) and column names (and their case)!

An important line above is this one:

oDatabase.Commit 'save changes

We can insert and modify as many things as we want, but if we don’t commit the transaction to the database the changes will not be made!

Note that we can also use question marks (?) to create parameterised queries. We do this by creating a record object which specifies the values we would like to substitute, and then we pass the record in as an argument to the execute statement like so:

'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),msiOpenDatabaseModeTransact)    

'check if Registry table exists (0 is a temporary table, 1 is a permanent table) 

If oDatabase.TablePersistent("Registry") = 1 Then

    'substitute ? in for each parameter

	Dim sql : sql = "INSERT INTO `Registry` (`Registry`,`Root`,`Key`,`Name`,`Value`,`Component_`) VALUES (?,?,?,?,?,?)" 

	Set regRecord = oInstaller.CreateRecord(6)       
	'in this example, we've created a record ensuring that all the non-nullable field are populated     
	regRecord.StringData(1) = "SampleReg"    
	regRecord.IntegerData(2) = 2    
	regRecord.StringData(3) = "Software\AlkaneTest"   
	regRecord.StringData(4) = "testName" 
	regRecord.StringData(5) = "testValue" 
	regRecord.StringData(6) = "alkaneComponent" 

    'create a view of the query we want to execute     
	Dim regView : Set regView = oDatabase.OpenView(sql)    

	'execute the query, and pass in the record object as an argument
	regView.Execute(regRecord)  

End If  

oDatabase.Commit 'save changes  

regView.Close
Set regView = Nothing
Set regRecord = Nothing
Set oDatabase = Nothing
Set oInstaller = Nothing

Tip: The Windows Installer SQL syntax does not support the escaping of single-quotes (ASCII value 39) in a string literal. For example, we can’t replace the singles quote with two single-quotes. It just won’t work. However if we use parameterised queries like the above example, we can overcome this issue.  This applies to all query types (INSERT, DELETE, UPDATE etc).

Thanks for reading about inserting a registry value into an MSI database using VBScript.  Next you can find out how to delete a registry value from an MSI database using VBScript.