Query SQL During Upgrade to Determine What Upgrade Scripts to Run

[Carlostwin]

We have an installer that need to determine the previously installed version so it knows what upgrade scripts to run and in what order. We have the version stored in the target database in a configuration table.

How can I retrieve this during the upgrade process?
If I can, can this be this be used against a condition against the scripts to I know which I should run?

I appreciate ideally I would used the OLDPRODUCTS approach, but I don't know what the products codes are.

[Catalin]

Hello Carlos,

So basically, if I understand correctly (please correct me if I'm wrong), you want to execute an SQL script/query based on which previous version is installed on the target machine? For instance, if version 1.0.1 is installed, execute ScriptA, if 1.0.2 is installed, execute ScriptB, etc.?

First of all, let's start this up with a definition:

OLDPRODUCTS - A list with the Product Codes of the older versions of your installation package. This property is set only in a package which upgrades another package.

Retrieving the version of an installed setup based on the ProductCode property can be done in two ways:

1. "Manually" through our predefined support for extended searches. The search that we are looking for here is "Product Version (identify by Product Code)" predefined search. However, this approach requires you to know the previous installed setup's product codes.

2. Programmatically through a custom action. The following example I will give you will be based on PowerShell, although you can use any language you wish.

In order to achieve this, we can make use of the "Get-WmiObject" cmdlet. The purpose of this cmdlet is to get instances of Windows Management Instrumentation (WMI) classes or information about the available classes.

A one liner that gets the version of an already installed application based on its Product Code is the following:

Code: Select all

get-wmiobject Win32_Product | where -property IdentifyingNumber -eq "{PRODUCT_CODE}" | select Version

In order to work with the returned data, we have to store it in a variable. With that being said, it would look something like this:

Code: Select all

$var = get-wmiobject Win32_Product | where -property IdentifyingNumber -eq "{PRODUCT_CODE}" | select Version

By typing "$var" in the PowerShell session, it will return the following:

Version
-------
X.X.X

I have said that the second method is more programatically, because we can make use of the OLDPRODUCTS property instead of needing to input the ProductCode property manually.

The value of a public property can be retrieved into a PowerShell variable using the following:

Code: Select all

$oldProd = AI_GetMsiProperty OLDPRODUCTS

Quick note: Please keep in mind that the "AI_GetMsiProperty PROPERTY" is a custom cmdlet and can not be used outside of Advanced Installer.

With that being said, we can transform our one-liner into the following:

Code: Select all

$var = get-wmiobject Win32_Product | where -property IdentifyingNumber -eq "$oldProd" | select Version

Now, all we need is to compare the value returned by the above one-liner to another values and, based on that, we will define some properties which we can use as a condition for our scripts. Long story short, we will do something as it follows:

Code: Select all

if (version = 1.0.0){
AI_SetMsiProperty SOME_PROPERTY 1 // Sets the value of SOME_PROPERTY to 1
}
elseif (version = 1.0.1){
AI_SetMsiProperty SOME_PROPERTY 2
}
etc.

After doing so, we can condition the scripts based on the property values. For instance, the condition for ScriptA could be: SOME_PROPERTY = "1". For our ScriptB, it could be: SOME_PROPERTY = "2" and so on.

Basically, we would be tempted here to form a condition as it follows:

Code: Select all

if($var -eq "1.0.0"){
# do something
}

However, this is not correct since "$var" is not actually a string. If we use the "GetType()" method, we will see that our variable is actually a PowerShell custom object. With that being said, the comparison could be done as it follows:

Code: Select all

if($var.Version -eq "1.0.0"){
# do something
}

Long story short, the script should look something as it follows:

Code: Select all

# Block for declaring the script parameters.
Param()

# Your code goes here.

Add-Type -AssemblyName PresentationFramework

$oldProd = AI_GetMsiProperty OLDPRODUCTS

$var = get-wmiobject Win32_Product | where -property IdentifyingNumber -eq "$oldProd" | select Version
if($var.Version -eq "1.2.3"){
# Here you should set a property's value.
}
elseif($var.Version -eq "1.2.4"){
# Set the above property to another value
}

# and so on

Now that we have created our script, it is time to create the custom action. To do so, please proceed as it follows:

1. Go to "Custom Actions" page.

2. From the list, select "Run PowerShell inline script" and press the "Add custom action with sequence" button which is placed to the right side of the custom action's name.

3. Copy and paste the above content into the custom action.

I think that the best place to schedule this custom action would be after the "Searches" action group -- "Wizard Dialogs Stage'. To do so, simply drag and drop the custom action after the "Searches" action group.

After doing so, use the properties defined within your script to condition the execution of your scripts.

P.S.: I think that this approach may encounter some problems if there is more than on setup installed on the same machine, for instance if the user has both version 1.0.0 and 1.0.1 installed (this may be the case if you allowed side by side installs in the past). Aside from that, I do not see a problem with this approach at this moment.

Hope my explanation was clear enough and this will be of help for you.

All the best,
Catalin