SQL scripts questions

[mstarnaud]

Hi. I'm still learning how to use the Advanced Installer. I found a few limitations with SQL scripts and was wondering if there was a way around them.

- Importing multiple SQL scripts at once in the SQL Scripts tab. I have 100+ scripts and at the moment I have to import them 1 by 1. I'm not aticipating my next 100+ scripts projects.

- Adding an inline SQL script. Oddly enough, I can't find how to add a "blank" SQL script : I have to select a file and then I can change the option to "inline script", but in the order it's still named after the file (at least I can change this part).

- Adding events between SQL scripts, getting the script's pass/fail error status. For example, between each script, I would like to output the result in a text file (pass/fail). This could also let us run a script depending on the result of another script (even though now I could do it programatically inside the script with SELECTs, but it's inconvenient).

- Adding folders in the SQL scripts tab, or at least separators with multi-select. This would help visibility and would not necessitate me to drag&drop the files one by one to change their order.

- (This is in the Install Parameters) Is there a way to have an empty default value for a Property? When I clear the default value box, it prevents me from saving (button gets disabled). For example, if I ask for the user's server, I don't want to have a default value. I can use a space, but then the user won't see it and will end up having a space at the start of the server's name.

- This confused me for a while until I finally found the cause. The name of the script is limited to 42 characters. Luckily, you can rename them in this interface. If you go over 42, you won't be able to build in Visual Studio :
Error 1 The command ""C:\Program Files (x86)\Caphyon\Advanced Installer 10.0\bin\x86\AdvancedInstaller.com" /build "myProject1.aip"" exited with code 536805277.
EDIT : I also get the same error if I remove the AISetupMAdeWith registry key. Not sure if this is because I'm still in the Trial version, it would be inconvenient for it to stay mandatory once we go through with buying (most likely to Entreprise).

- Some characters seem forbidden in the search-and-replace. For example, if I use curly bracket {} the search-and-replace is simple not done. I did not find a list of the forbidden characters, can I please at the very least have the list?

[Daniel]

Hello,

- Importing multiple SQL scripts at once in the SQL Scripts tab. I have 100+ scripts and at the moment I have to import them 1 by 1. I'm not aticipating my next 100+ scripts projects.

We have this improvement on our TODO list and will be available in version 10.2 of Advanced Installer which should be released in 1-2 months from now. I'll let you know when this will be available.

- Adding an inline SQL script. Oddly enough, I can't find how to add a "blank" SQL script : I have to select a file and then I can change the option to "inline script", but in the order it's still named after the file (at least I can change this part).

Indeed, this is an Advanced Installer limitation. We'll consider this improvement in a future version. Thank you for your suggestion.

- Adding events between SQL scripts, getting the script's pass/fail error status. For example, between each script, I would like to output the result in a text file (pass/fail). This could also let us run a script depending on the result of another script (even though now I could do it programatically inside the script with SELECTs, but it's inconvenient).

Starting with our next version (10.1 - which should be released until the end of this week) you will have support to set installer properties within a new type of SQL Scripts (i.e. SQL Queries). In this way you will be able to execute an SQL statement based on a property value. So, starting with our next version you will be able to achieve this using our SQL Querry support.

- Adding folders in the SQL scripts tab, or at least separators with multi-select. This would help visibility and would not necessitate me to drag&drop the files one by one to change their order.

We'll consider your suggestion for adding this feature, but since yours is the only request, implementing it has very low priority. Perhaps in the future we will increase its priority, but for the moment there are not immediate plans. Than you for your understanding.

- (This is in the Install Parameters) Is there a way to have an empty default value for a Property? When I clear the default value box, it prevents me from saving (button gets disabled). For example, if I ask for the user's server, I don't want to have a default value. I can use a space, but then the user won't see it and will end up having a space at the start of the server's name.

In order to achieve what you can add your property (MY_PROP) in "Install Parameters" page, and then you can add a "Set installer property" custom action with sequence, after "Wizard Dialogs Stage -> Searches" action group, like this:

• Property: MY_PROP
  Formatted: {}

- This confused me for a while until I finally found the cause. The name of the script is limited to 42 characters.

This is the SQL Scripts support default implementation.

- Some characters seem forbidden in the search-and-replace. For example, if I use curly bracket {} the search-and-replace is simple not done. I did not find a list of the forbidden characters, can I please at the very least have the list?

I'm afraid I don't fully understand what you mean. Can you please give us more details about this (maybe exemplify)?

All the best,
Daniel

[mstarnaud]

Hi.

Thanks for the reply! I'll be looking forward to the next version and all its improvements! And yes, I understand some of my queries are very low priority especially when they have an easy-to-implement workaround. I was also making sure I didn't miss something obvious since I'm still rather new at Advanced Installer (even though I used the old visual studio version a lot).

Honestly I'm a bit baffled by the workaround for empty properties values, but I guess I can work with that if it doesn't cause any other problem (I didn't try custom action ordering yet).

- Some characters seem forbidden in the search-and-replace. For example, if I use curly bracket {} the search-and-replace is simple not done. I did not find a list of the forbidden characters, can I please at the very least have the list?

In the SQL scripts window, there is a "Script replacement" tab. In it, I can set search-and-replace rules. If the rule is looking for a text containing some forbidden characters, the rule will not be executed when I run the MSI. Let's say I ask for the username (PROP_USERNAME) in a dialog and my script is INSERT INTO mytable(username) VALUES (/*USERNAME-GOES-HERE*/). I can set the rule to be : /*USERNAME-GOES-HERE*/ becomes PROP_USERNAME. This will work. However, if I used {USERNAME-GOES-HERE} in my script and my rule (so I expected it would still work), it will not work because the curly brackets {} are forbidden characters : the script will be executed as-is : INSERT INTO mytable(username) VALUES ({USERNAME-GOES-HERE}) and this will give me an SQL error. Was that more understandable? So I know the curly brackets won't work, I'm asking if you knew what else did not work and if there was a way to make them work.

[Daniel]

Hello,

I've talked with our development team and it seems this is the default behavior. This happens because when an SQL script is parsed by Advanced Installer all of special formatted characters (i.e. {,},[,]) are escaped like this:

Code: Select all

[\special_char]

Also, SQL script replacements are done after SQL scripts are parsed.

So, in order to search for "{USERNAME-GOES-HERE}" string and replace it you should escape the "{}" characters like this:

• Find what: [\{]USERNAME-GOES-HERE[\}]

Let us know if this helped.

All the best,
Daniel

[mstarnaud]

Are those brackets (square and curly) the only special characters? And yes, that's clarified (I did expect it was by design), and thanks for giving me the workaround!

I think the only question from my original post that remains unanswered is this :

Error 1 The command ""C:\Program Files (x86)\Caphyon\Advanced Installer 10.0\bin\x86\AdvancedInstaller.com" /build "myProject1.aip"" exited with code 536805277.
EDIT : I also get the same error if I remove the AISetupMAdeWith registry key. Not sure if this is because I'm still in the Trial version, it would be inconvenient for it to stay mandatory once we go through with buying (most likely to Entreprise).

But I also have a new one : how can we get IIS' wwwroot folder? They are not included in your pre-defined paths (http://www.advancedinstaller.com/user-g ... paths.html) and the Search method as suggested here http://www.advancedinstaller.com/forums ... f=1&t=7548 will not work in many cases. In my test example, the destination machine also had another folder called "wwwroot" somewhere else on the computer, and that other folder was the one returned by the Search. Also, another problem with the search was it's execution order : I want to search for wwwroot and then set it into APPDIR and then use it in the FolderDlg, but the Dialog and Search actions are in the wrong order for this to work. I was going to go investigate on this issue soon (there is probably a way to get it using a custom action that reads IIS' settings) but maybe there's an easier way.

[Daniel]

Hello,

Are those brackets (square and curly) the only special characters?

Yes, only square and curly brackets need to be escaped using the related format. However, I've talked with our development team and they decided to implement an improvement for this scenario in our next version of Advanced Installer which should be released until the end of this week or next week at the latest. So, starting with our next version of Advanced Installer these special characters will be automatically escaped by Advanced Installer when the SQL script replacement is done. Therefore, you will no longer have to escape these characters when adding a SQL script replacement.

Error 1 The command ""C:\Program Files (x86)\Caphyon\Advanced Installer 10.0\bin\x86\AdvancedInstaller.com" /build "myProject1.aip"" exited with code 536805277.

Does this behavior still persist even when your SQL script name is under 42 characters? If so, can you give us a step by step test case scenario so we can investigate this?

EDIT : I also get the same error if I remove the AISetupMAdeWith registry key. Not sure if this is because I'm still in the Trial version, it would be inconvenient for it to stay mandatory once we go through with buying (most likely to Entreprise).

I'm afraid I don't fully understand your scenario. Do you get this error when building from Visual Studio? Also, can you send us your .AIP(project file) to support at advancedinstaller dot com so we can investigate?

Also, you can take a look on our "exited with code 536805277" thread which debates a similar issue.

But I also have a new one : how can we get IIS' wwwroot folder? They are not included in your pre-defined paths (http://www.advancedinstaller.com/user-g ... paths.html) and the Search method as suggested here viewtopic.php?f=1&t=7548 will not work in many cases. In my test example, the destination machine also had another folder called "wwwroot" somewhere else on the computer, and that other folder was the one returned by the Search. Also, another problem with the search was it's execution order : I want to search for wwwroot and then set it into APPDIR and then use it in the FolderDlg, but the Dialog and Search actions are in the wrong order for this to work. I was going to go investigate on this issue soon (there is probably a way to get it using a custom action that reads IIS' settings) but maybe there's an easier way.

In order to achieve what you want you can proceed like this:
- Go to "Search" page and using the “Add/Remove Predefined Search...” context menu option add the "IIS root folder path" predefined search.
- Go to "Install Parameters" page and set the "Application Folder" field like this:

Code: Select all

[IIS_ROOT_PATH]

- Build and run your project.

Let us know if this helps, otherwise please give us more details about your scenario.

All the best,
Daniel

[mstarnaud]

Thanks for your answer. I wasn't expecting the special characters to be "repaired", I mostly wanted to know the list so I wouldn't get any surprise and know how to deal with them. But that's even better!

About the error code : it's when I build in Visual Studio (2012). For the SQL script name, it's very simple. I had many scripts including one with a long script name (50+ characters) and was wondering why I was getting this error. I re-created the project from scratch and built after every step, and it failed after the SQL scripts were added. And then if I removed them it worked again. It worked as long as I didn't add the script that had a long name. So I renamed it with 1 less letter between each build attempt until it finally succeeded. At 43 characters it failed, at 42 it succeeded. Note that I haven't tested if there are other factors (connection name's length).

I used similar steps to determine the error also came from the registry entry : I had a project which was getting the error. So I re-created it from scratch and built after every step, the step that caused the build failure was removing the registry entry. I'll send you an AIP soon.

And thanks for the IIS search, I noticed a while ago there were predefined searches but I did not think about it when looking for wwwroot. However, if I used that, I'd still be stuck with part of the problem : the search is done after the dialogs, and I want to use the search's result in a dialog (the default folderDlg). For now I'm just hardcoding C:\inetpub\wwwroot\[PROP_VirtualDirectoryName] but it's not ideal (such as my test computer having the wwwroot folder in the D drive instead).

[Daniel]

Hello,

I used similar steps to determine the error also came from the registry entry : I had a project which was getting the error. So I re-created it from scratch and built after every step, the step that caused the build failure was removing the registry entry. I'll send you an AIP soon.

Also, it would be very useful if you will be able to isolate this behavior in a sample or if you can send us a step by step test case scenario too, so we can try to reproduce and investigate this issue on our test machines.

And thanks for the IIS search, I noticed a while ago there were predefined searches but I did not think about it when looking for wwwroot. However, if I used that, I'd still be stuck with part of the problem : the search is done after the dialogs, and I want to use the search's result in a dialog (the default folderDlg). For now I'm just hardcoding C:\inetpub\wwwroot\[PROP_VirtualDirectoryName] but it's not ideal (such as my test computer having the wwwroot folder in the D drive instead).

I'm not sure why this happens. Can you please send us your .AIP (project file) to support at advancedinstaller dot com so we can investigate? Also, please keep in mind that when your installation package is running with Full UI, searches from "Search" page are performed during "Wizard Dialogs Stage" -> "Searches" -> "AppSearch" standard action. If your installation package is running without Full UI, then searches from "Search" page are performed during "Install Execution Stage" -> "Searches" -> "AppSearch" standard action.

All the best,
Daniel

[mstarnaud]

Hi

Thanks for the reply. I took a look at your answer and the custom actions' orders and it contradicts the behavior I was getting. So maybe I was doing something wrong? I'll probably try the search again soon, this time with the pre-defined search and not with "folder : wwwroot" like I did last time.

About the weird error code, I just took the time to make brand new projects to send to you. I immediately re-created the registry bug. But for some reason, the SQL script name bug isn't reproducing. I have no idea why. And like I said, I really did isolate it the first time (built every time I removed a character, worked at 42) so I'm not dreaming, there's probably other factors involved. I found the script names get truncated to 50 characters when you first import them, but that shouldn't be a problem (well, the 43+ character bug isn't much of a problem either, as long as we know it and can just rename the script).

EDIT : sent the registry bug to support, the answer was that the project needs at least 1 component to build, and the only component it starts with is this registry key. So that's why in my case deleting it caused this error. I just tried removing the registry in a "complete" project and it did not crash.

[Daniel]

Hello,

Thanks for the reply. I took a look at your answer and the custom actions' orders and it contradicts the behavior I was getting. So maybe I was doing something wrong? I'll probably try the search again soon, this time with the pre-defined search and not with "folder : wwwroot" like I did last time.

Here's attached a sample, created using version 10.0 of Advanced Installer, which implements a similar scenario.

sample.zip

(2.37 KiB) Downloaded 871 times

If you have any questions let us know.

All the best,
Daniel

[mstarnaud]

Just a quick update, I did try the pre-defined search and set the default installation folder to [IIS_ROOT_PATH]\[PROP_VIRTUALDIRECTORY]. And yet, it gets the C drive even though the real one is actually in the D drive. The folders both exist, but the "good" one is the one on D. Which I think is strange because it looks like the Search is querying the registry. I haven't had the time to look at your sample yet though. That said, at least I'm happy to get the "correct" folder without hardcode, that was the main issue. Thanks for the help.

[Daniel]

You're welcome. Glad to help.

All the best,
Daniel

[Daniel]

Hello Brian,

- Adding folders in the SQL scripts tab, or at least separators with multi-select. This would help visibility and would not necessitate me to drag&drop the files one by one to change their order.

We'll consider your suggestion for adding this feature, but since yours is the only request, implementing it has very low priority. Perhaps in the future we will increase its priority, but for the moment there are not immediate plans. Than you for your understanding.

This improvement was added in version 10.7 of Advanced Installer released on November 11, 2013.

All the best,
Daniel