lcofre
Posts: 5
Joined: Wed Aug 01, 2018 8:41 pm

Run Powershell to upgrade database

Wed Aug 01, 2018 10:07 pm

Hi,

I have a powershell script that installs a database by executing a set of .sql files.
What is the best way to configure this on AI?

What I did until now is:
Put the folder with all the .sql files in Files and Folders > Target Computer > Windows Volume > Temporary

On the script get the database server instance from the installer properties by doing

Code: Select all

$Server = AI_GetMsiProperty SQL_SERVER_INSTANCE
But I still have to answer:
How can I get the Temporary folder path inside the powershell script?
How to ensure the scripts executes after the temporary folder has the .sql files?
Is this the best way to configure the script?

Thanks very much in advance,

Leandro.

Daniel
Posts: 8237
Joined: Mon Apr 02, 2012 1:11 pm
Contact:  Website

Re: Run Powershell to upgrade database

Sun Aug 05, 2018 3:50 pm

Hello Leandro and welcome to our forums,

To access the temporary folder path you should just get within your PowerShell script code the "TempFolder" property value. Also, each temp file added in our "Files and Folders" view has an associated property whose value is set at install time to the full path of the temp file. Just go to "Files and Folders" page, double click on a temp file item and from its "Properties" dialog check its property name.

The temporary files are copied on target machine at install time during the "Paths Resolution" action group, so your PowerShell custom action should be scheduled anytime after "Paths Resolution" in the actions sequence.

Also, starting with the "Enterprise" edition of Advanced Installer you can take advantage of our dedicated "SQL Databases" feature which is the recommended way to maintain and run sql scripts within your setup project.

All the best,
Daniel
Daniel Radu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube

lcofre
Posts: 5
Joined: Wed Aug 01, 2018 8:41 pm

Re: Run Powershell to upgrade database

Tue Aug 07, 2018 10:33 am

Thanks very much for your answer.

My script needs two values:
  • $TempFolder The temporary folder that contains my own folder with scripts, "SQL". This SQL folder is synchronized as its content can change between builds as more sql scripts are added. The powershell script will execute all .sql files
  • $ServerInstance The SQL Server the installation is pointing to, to install the database.
I add a Powershell script file with immediate execution but after getting the Temp folder

Code: Select all

$TempFolder = AI_GetMsiProperty TempFolder
the powershell script fails saying the SQL folder does not exist in the $TempFolder path. (I already checked the local policy as explained here: viewtopic.php?f=5&t=34856#p96469)

I change the execution time of the script to deferred but now the $ServerInstance variable,

Code: Select all

$Server = AI_GetMsiProperty SQL_SERVER_INSTANCE
a variable completed with a value provided by the user in one of the dialogs, is empty.

How can I obtain both values inside my powershell script?

Regarding your advice to use the SQL Databases feature, it looks that I have to add all scripts one by one and in my case the number of script files will increase on every build, so I would need a synchronized folder, but it seems not possible on this feature.

Thanks very much for your help.

Leandro.

Daniel
Posts: 8237
Joined: Mon Apr 02, 2012 1:11 pm
Contact:  Website

Re: Run Powershell to upgrade database

Wed Aug 08, 2018 10:44 am

Hello Leandro,

When getting the "TempFolder" folder property value within your custom action scheduled as immediate, you should just make sure your custom action is scheduled for execution after "Paths Resolution" action groups. During this action group the folder paths are resolved.

Also, when scheduling your custom action to run as deferred please make sure you use the syntax explained in our "Getting a property value into a script (for deferred custom actions)" article section.
Regarding your advice to use the SQL Databases feature, it looks that I have to add all scripts one by one and in my case the number of script files will increase on every build, so I would need a synchronized folder, but it seems not possible on this feature.
Yes, this is true.

All the best,
Daniel
Daniel Radu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube

lcofre
Posts: 5
Joined: Wed Aug 01, 2018 8:41 pm

Re: Run Powershell to upgrade database

Wed Aug 08, 2018 12:00 pm

Thanks very much for your answer Daniel,

I tried your advise (the immediate execution one) on a new project that I attach here.
It worked the first time, but when I made the temp folder synchronized (folder properties, tab synchronize) the folder is not available even when the script is after Paths Resolution.
Also the temp folder is not deleted after the installation finishes.
Then I unchecked the synchronize on the temp folder, but the behavior kept the same.

What am I missing?

By the way, I am using AI 14.3

Thanks very much,

Leandro
Attachments
Your Application.aip
(20.03KiB)Downloaded 213 times

Daniel
Posts: 8237
Joined: Mon Apr 02, 2012 1:11 pm
Contact:  Website

Re: Run Powershell to upgrade database

Thu Aug 09, 2018 2:55 pm

Hello Leandro,

I am not so sure why this doesn't work. I have tested the scenario and everything went fine. Can you please use a small ps1 script with the following content just for testing purposes:

Code: Select all

[System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")
$TempFolder = AI_GetMsiProperty TempFolder
[System.Windows.Forms.Messagebox]::Show($TempFolder)
If this still doesn't work it will be useful if you could share with us a small sample (AIP + PS1 script) and a test case we can follow to replicate the behavior.

In what regards the files you added under "Temporary" folder from "Files and Folders" page, they are not deleted from target machine after installation process exits because they are not added as temporary files, but as regular files. Can you please delete them from your project and re-add them by using our [Add Temporary Files] toolbar button? This is the right way to add temporary files to your setup project.

All the best,
Daniel
Daniel Radu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube

lcofre
Posts: 5
Joined: Wed Aug 01, 2018 8:41 pm

Re: Run Powershell to upgrade database

Thu Aug 09, 2018 10:42 pm

Thanks Daniel for your detailed answer.

I was always able to see the contents of TempFolder, but the folder I am adding is the one that is not present when executing the custom action.
I attach an example project that should show an empty messagebox when executing

Code: Select all

[System.Windows.Forms.Messagebox]::Show($(Get-ChildItem "$($TempFolder)Desktop"))
Your Application.aip
(20.05KiB)Downloaded 205 times
test.ps1.txt
(200Bytes)Downloaded 211 times
(Remove the .txt extension on this file)

Hopefully you can reproduce the behavior with the following steps:
  • On the attached project, go to the custom action and search for the location of the script I also attached
  • In Files and Folders remove the Desktop folder and add it again with the Add Temporary Folder button. You can use the folder C:\Users\Public\Desktop, ensuring it has at least one file on it
  • Build the project and install the app
The messagebox will show the files that are on the Desktop folder. Also the Desktop folder is seen on the temp folder during installation and is removed after it finishes. This is I think the result you are getting.
Now:
  • Uninstall the app so you can install a new build
  • In Files and Folders of the project, delete the content of the Desktop folder, and in its properties change the Desktop folder to synchronized to the same source, C:\Users\Public\Desktop
  • The Desktop folder will be filled with the same files but the text will be blue this time, and an icon on the folder will indicate it's synchronized
  • Build the project and install the app again
Now the messagebox will be empty. Before pressing ok check the temp folder and you will see the Desktop folder is not there, but after pressing ok the folder will appear, and stay there even after the installation finishes.

Also I don't know if related but the project I attach logs all to a file. The MSI command line in Builds says: /L*V "[DesktopFolder]\[|ProductName]-Install.log"

Thanks again for your work on my behalf.

Leandro.

Daniel
Posts: 8237
Joined: Mon Apr 02, 2012 1:11 pm
Contact:  Website

Re: Run Powershell to upgrade database

Tue Aug 14, 2018 2:38 pm

Hello Leandro,

I apologize for my delayed reply.

Unfortunately your project settings are not supported by your scenario requirements. Please note that when synchronizing your "Desktop" folder with a folder from disk all its files content is added as regular file and not as temporary files. Thus the files will be deployed on target machine on "Add Resources" action group on deferred execution time (also the synced files won't be removed automatically from disk after installation). I am afraid we do not have support to synchronize a folder from disk as a temporary content.

Thus, the only solution for your scenario (when using our synced folder support) will be to add your PowerShell script custom action after "Add Resources" action group on deferred execution time. Also, you should update your script functionality by using the syntax explained in our "Getting a property value into a script (for deferred custom actions)" article section.

Also, in this case you should try to remove from disk all the installed files within your PowerShell script functionality too.

Let us know if this helped.

All the best,
Daniel
Daniel Radu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube

lcofre
Posts: 5
Joined: Wed Aug 01, 2018 8:41 pm

Re: Run Powershell to upgrade database

Mon Aug 20, 2018 8:40 pm

Thanks Daniel,

By following "Getting a property value into a script (for deferred custom actions)" to get both my [SQL_SERVER_INSTANCE] custom property and the [TempFolder] path I am forced to make my powershell script as inline, embedded in the AI project file, right?
I am keeping the powershell script outside the project file on purpose, so its changes can be traced with git, as the Base64 version inside the project file is untraceable.

It looks like you are describing the incompatibility between the synchronized folder property and both the Temporary Folders functionality and the "Run PowerShell script file" functionality. Is that a design decision or it could be considered a bug?

Thanks very much for your help,

Leandro.

Daniel
Posts: 8237
Joined: Mon Apr 02, 2012 1:11 pm
Contact:  Website

Re: Run Powershell to upgrade database

Tue Aug 28, 2018 12:28 pm

Hello Leandro,

I apologize for the delayed reply.
By following "Getting a property value into a script (for deferred custom actions)" to get both my [SQL_SERVER_INSTANCE] custom property and the [TempFolder] path I am forced to make my powershell script as inline, embedded in the AI project file, right?
Yes, unfortunately this is true.
It looks like you are describing the incompatibility between the synchronized folder property and both the Temporary Folders functionality and the "Run PowerShell script file" functionality. Is that a design decision or it could be considered a bug?
Yes, currently this is our custom action design. Maybe such improvements will be considered in the future.

All the best,
Daniel
Daniel Radu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube

Return to “Common Problems”