MGS
Posts: 83
Joined: Tue Oct 04, 2022 9:52 am

Data Source - Test Connection, Persist in Registry

Mon Nov 21, 2022 1:13 pm

Hi there,

I created a Data Source under ODBC - Target Computers - Data Sources and successfully use it by referencing it in my SQL Databases - Database Server - MSSQL Connection and having the Connection Mode there set to 'ODBC Data Source'. Now I want to test exactly that connection using a button.
1. How is that possible? How do I need to adapt my SQL Operations - SQL Test Connection?
I tried to turn on the log to see if any Connection String was logged but that did not help unfortunately.
2. How can I persist my Data Source (ODBC - Target Computers - Data Sources) with all its values in the registry in order to load the data when doing an Upgrade using the installer? Does that functionality already exist?
3. How can I depend the Driver name of the Data Source on a property? I want to adapt the selected ODBC Driver during the installation depending on some registry values. -> if ODBC Driver 17 exists, use that one; if ODBC Driver 13 exists, use that one, and so on. I already read the values from the registry using a Search and retrieve the Driver name correctly. The last step left is to assign that Driver name to the Data Source. Is that possible?

All the best,
Johannes

Liviu
Posts: 1035
Joined: Tue Jul 13, 2021 11:29 am
Contact:  Website

Re: Data Source - Test Connection, Persist in Registry

Tue Nov 22, 2022 4:21 pm

Hello Johannes,
I created a Data Source under ODBC - Target Computers - Data Sources and successfully use it by referencing it in my SQL Databases - Database Server - MSSQL Connection and having the Connection Mode there set to 'ODBC Data Source'. Now I want to test exactly that connection using a button.
1. How is that possible? How do I need to adapt my SQL Operations - SQL Test Connection?
I tried to turn on the log to see if any Connection String was logged but that did not help unfortunately.
Please have a look on the Test ODBC Connection -> custom action missing forum thread.

In order to add a new "Test ODBC Connection" custom action, please go to "SQL Databases" page and press the "Test SQL Connection" button from the toolbar.

We also have predefined dialogs to test a connection. Just go to the Dialogs page and add the SqlServerConnectionDlg dialog:
sql dialog.png
sql dialog.png (109.16KiB)Viewed 11464 times

You can also check the following articles:

1. Using SQL Databases tutorials
2. SQL Server built-in configurations video
2. How can I persist my Data Source (ODBC - Target Computers - Data Sources) with all its values in the registry in order to load the data when doing an Upgrade using the installer? Does that functionality already exist?
Preserving resources is described in the How do I prevent a file or registry entry from being uninstalled or repaired? article.

For your Data Sources, you can click on the "Go to Component" toolbar button and check the "Permanent" attribute for the corresponding component.
3. How can I depend the Driver name of the Data Source on a property? I want to adapt the selected ODBC Driver during the installation depending on some registry values. -> if ODBC Driver 17 exists, use that one; if ODBC Driver 13 exists, use that one, and so on. I already read the values from the registry using a Search and retrieve the Driver name correctly. The last step left is to assign that Driver name to the Data Source. Is that possible?
For this, you can use two "Set installer property" custom actions, after Searches action group:

1. Property: DRIVER_PROP
Value: SEARCH_PROP_17
Condition: SEARCH_PROP_17

2. Property: DRIVER_PROP
Value: SEARCH_PROP_13
Condition: SEARCH_PROP_13

After that, in the ODBC page you can use the above property on the Driver name field:
driver prop.png
driver prop.png (34.24KiB)Viewed 11464 times

Hope this helps!

Best regards,
Liviu
________________________________________
Liviu Sandu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube

MGS
Posts: 83
Joined: Tue Oct 04, 2022 9:52 am

Re: Data Source - Test Connection, Persist in Registry

Wed Nov 23, 2022 10:16 am

Hello Liviu,
Liviu wrote:
Tue Nov 22, 2022 4:21 pm
Please have a look on the Test ODBC Connection -> custom action missing forum thread.

In order to add a new "Test ODBC Connection" custom action, please go to "SQL Databases" page and press the "Test SQL Connection" button from the toolbar.

We also have predefined dialogs to test a connection. Just go to the Dialogs page and add the SqlServerConnectionDlg dialog:

sql dialog.png


You can also check the following articles:

1. Using SQL Databases tutorials
2. SQL Server built-in configurations video
1. Thanks for the in-depth answer! That's very helpful to get more insights. But unfortunately, that does not answer my question. I already have the connection itself working (my SQL queries work fine) with the Connection Mode set to ODBC Data Source. My problem now is that the connection test actually reflects the same settings used for the connection (therefore using the same data source called XRDB). In the Sql Test Connection settings I can only change the Connection String. So is it possible to adapt the Connection String such that it reflects the same behavior as my actual connection (with the Connection Mode set to ODBC Data Source)?
data_source_persist_3.png
data_source_persist_3.png (43.48KiB)Viewed 10097 times
2. How can I persist my Data Source (ODBC - Target Computers - Data Sources) with all its values in the registry in order to load the data when doing an Upgrade using the installer? Does that functionality already exist?
Preserving resources is described in the How do I prevent a file or registry entry from being uninstalled or repaired? article.

For your Data Sources, you can click on the "Go to Component" toolbar button and check the "Permanent" attribute for the corresponding component.
2. Okay, so my actual problem here is that the ODBC Data Source is only persisted after pressing "Install" and it is not usable beforehand. But I need to test the connection and do some SQL queries using that Data Source before pressing "Install" So my setup is like this:

In AI, I set the data source up like this:
data_source_persist_2.png
data_source_persist_2.png (30.94KiB)Viewed 10097 times
And my connection like this:
data_source_persist_3.png
data_source_persist_3.png (43.48KiB)Viewed 10097 times
Now when not adding the Data Source myself in advance using the 'ODBC Data Sources' application like in the following, then I get an error. I post the error in the next post as there seems to be an attachment limit of 3 files.

Adding the Data Source in advance looks like this:
data_source_persist_1.png
data_source_persist_1.png (20.67KiB)Viewed 10097 times
When I delete this entry, I run into the error when clicking through the installer as soon as the first SQL query is executed (therefore the data source is used).

All the best,
Johannes
Last edited by MGS on Wed Nov 23, 2022 2:29 pm, edited 2 times in total.

MGS
Posts: 83
Joined: Tue Oct 04, 2022 9:52 am

Re: Data Source - Test Connection, Persist in Registry

Wed Nov 23, 2022 10:17 am

Add 2. find here the error message:
data_source_persist_4.png
data_source_persist_4.png (5.93KiB)Viewed 10094 times
After that, in the ODBC page you can use the above property on the Driver name field:
driver prop.png
3. Many thanks, I wasn't aware that I can use a property here too. As a suggestion, it would be great to have the possibility to use the property selector here too or at least highlight the [DRIVER_PROP] text in brown as is done usually.

So I switched to using the property [ODBC_DRIVER_PROP] here but this results in an error message after pressing the 'Install' button. I have my [ODBC_DRIVER_PROP] assigned to "ODBC Driver 17 for SQL Server" manually which should be exactly the same as when selecting a Driver Name via the provided dropdown. What can be the issue here? The error message I get:
odbc_error.png
odbc_error.png (6.81KiB)Viewed 9786 times
All the best,
Johannes

MGS
Posts: 83
Joined: Tue Oct 04, 2022 9:52 am

Re: Data Source - Test Connection, Persist in Registry

Wed Nov 23, 2022 2:38 pm

Add 3 find here the according settings.
I switch from Variant 1
odbc_driver_name_1.png
odbc_driver_name_1.png (10.15KiB)Viewed 9770 times
to Variant 2
odbc_driver_name_2.png
odbc_driver_name_2.png (9.72KiB)Viewed 9770 times
as per your suggestion.

Variant 1 does not result in the above error after pressing the 'Install' button. Variant 2 does that unfortunately.

Liviu
Posts: 1035
Joined: Tue Jul 13, 2021 11:29 am
Contact:  Website

Re: Data Source - Test Connection, Persist in Registry

Tue Nov 29, 2022 10:53 am

Hello Johannes,

1. Thanks for the in-depth answer! That's very helpful to get more insights. But unfortunately, that does not answer my question. I already have the connection itself working (my SQL queries work fine) with the Connection Mode set to ODBC Data Source. My problem now is that the connection test actually reflects the same settings used for the connection (therefore using the same data source called XRDB). In the Sql Test Connection settings I can only change the Connection String. So is it possible to adapt the Connection String such that it reflects the same behavior as my actual connection (with the Connection Mode set to ODBC Data Source)?
Unfortunately, we do not have support for this. You can, however, pass your ODBC Attributes to the Connection String:
connection string.png
connection string.png (49.53KiB)Viewed 8964 times

Just add all your properties from the ODBC Data source on this string.

2. Okay, so my actual problem here is that the ODBC Data Source is only persisted after pressing "Install" and it is not usable beforehand. But I need to test the connection and do some SQL queries using that Data Source before pressing "Install" So my setup is like this:
You can try to set the "Persistent" option for your properties used on the Attributes section of your Data Source. Just go to the Properties page and enable this option for the desired properties:
persistent property.png
persistent property.png (55.36KiB)Viewed 8964 times
3. Many thanks, I wasn't aware that I can use a property here too. As a suggestion, it would be great to have the possibility to use the property selector here too or at least highlight the [DRIVER_PROP] text in brown as is done usually.

So I switched to using the property [ODBC_DRIVER_PROP] here but this results in an error message after pressing the 'Install' button. I have my [ODBC_DRIVER_PROP] assigned to "ODBC Driver 17 for SQL Server" manually which should be exactly the same as when selecting a Driver Name via the provided dropdown. What can be the issue here? The error message I get:
I'm sorry for this. It seems that this field does not support properties. In order to achieve your scenario, you can try the below solution:

1. Add two data sources. Add the name of the desired driver, for one Data Source you can add the "ODBC Driver 17 for SQL Server" driver name, and for the other you can add "ODBC Driver 13 for SQL Server" in the driver name field.
data sources.png
data sources.png (43.53KiB)Viewed 8964 times

2. From the SQL Databases page add two Predefined Connections. In the ODBC Data Source field select one of the two Data Sources added at step (1). For the other predefined connection, add the second data source.

Now, you can condition these predefined connection with your search result on the Condition field.
predefined.png
predefined.png (50.38KiB)Viewed 8964 times

Attached you can find my sample project. Hope this helps!

Best regards,
Liviu
Attachments
odbc.aip
(21.51KiB)Downloaded 165 times
________________________________________
Liviu Sandu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube

MGS
Posts: 83
Joined: Tue Oct 04, 2022 9:52 am

Re: Data Source - Test Connection, Persist in Registry

Mon Dec 05, 2022 11:28 am

Hello Liviu,

1.
Liviu wrote:
Tue Nov 29, 2022 10:53 am
Unfortunately, we do not have support for this. You can, however, pass your ODBC Attributes to the Connection String:

connection string.png

Just add all your properties from the ODBC Data source on this string.
I already knew that. But I was not aware how to properly set up the connection string in order to achieve the same outcome as using the data source. But I figured out how I can add the database and the port to the ODBC connection string and that works fine now:

Code: Select all

DRIVER={[ODBC_DRIVER_PROP]};Address=tcp:[SERVER_PROP],[PORT_PROP];DATABASE=[DATABASE_PROP];UID=[USERNAME_PROP];PWD={[PASSWORD_PROP]};
2.
Liviu wrote:
Tue Nov 29, 2022 10:53 am
You can try to set the "Persistent" option for your properties used on the Attributes section of your Data Source. Just go to the Properties page and enable this option for the desired properties:

persistent property.png
That does not help as I need to have the data source settings persisted in the registry at "HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\ODBC\ODBC.INI" directly after changing the parameters in the UI, otherwise the my custom SQL queries fail. My custom SQL queries are executed before the user presses the Install button. I managed to overcome this issue by persisting the data source in the registry myself using Powershell commands.

3.
Liviu wrote:
Tue Nov 29, 2022 10:53 am
3. Many thanks, I wasn't aware that I can use a property here too. As a suggestion, it would be great to have the possibility to use the property selector here too or at least highlight the [DRIVER_PROP] text in brown as is done usually.

So I switched to using the property [ODBC_DRIVER_PROP] here but this results in an error message after pressing the 'Install' button. I have my [ODBC_DRIVER_PROP] assigned to "ODBC Driver 17 for SQL Server" manually which should be exactly the same as when selecting a Driver Name via the provided dropdown. What can be the issue here? The error message I get:
I'm sorry for this. It seems that this field does not support properties. In order to achieve your scenario, you can try the below solution:

1. Add two data sources. Add the name of the desired driver, for one Data Source you can add the "ODBC Driver 17 for SQL Server" driver name, and for the other you can add "ODBC Driver 13 for SQL Server" in the driver name field.

data sources.png


2. From the SQL Databases page add two Predefined Connections. In the ODBC Data Source field select one of the two Data Sources added at step (1). For the other predefined connection, add the second data source.

Now, you can condition these predefined connection with your search result on the Condition field.

predefined.png


Attached you can find my sample project. Hope this helps!
Thank you for trying to help me find a solution, I appreciate it! But the solution you suggested is not feasible as I have many custom SQL queries that I would need to duplicate for each connection. I have found that using a property (e.g. [ODBC_DRIVER_PROP]) for the data source driver name works well if the data source is not used prior to installation. However, I need to use the modified data source before installation. The reason this didn't work for me was that the driver name must be persistent in the registry, otherwise the change is not applied. So after changing the driver name, I now persist it myself in the correct place in the registry (HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\ODBC\ODBC.INI\ODBC Data Sources) using Powershell. That way it worked.

Best regards,
Johannes

Liviu
Posts: 1035
Joined: Tue Jul 13, 2021 11:29 am
Contact:  Website

Re: Data Source - Test Connection, Persist in Registry

Mon Dec 05, 2022 4:31 pm

Hello Johannes,

Thank you for your follow-up on this. I'm glad you found a solution to your problems.

I am sure this will be of help for other users facing a similar scenario.

Best regards,
Liviu
________________________________________
Liviu Sandu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube

MGS
Posts: 83
Joined: Tue Oct 04, 2022 9:52 am

Re: Data Source - Test Connection, Persist in Registry

Tue Dec 13, 2022 4:14 pm

Hello Liviu,

Unfortunately, currently it is not working anymore. So I was wrong when I said that using a property for the ODBC driver name works.

See the attached image for a diff I created comparing the log file when using the "[ODBC_DRIVER_PROP]" as ODBC - Data Source - Driver Name in comparison to using "ODBC Driver 17 for SQL Server". One can clearly see that the AI property is not replaced with its value.
My SQL queries that I execute during the UI stage execute fine at this place as I persist my ODBC connection settings myself prior to executing them. Unfortunately that doesn't help me here, as Advanced Installer uses the data source's settings and fails because it is not replacing the AI property to its value.

1. I would really appreciate a fix for this such that an AI property can be used as Driver Name for an ODBC Data Source.
2. Is it possible to prevent SQL queries from being executed during installation (after user pressed 'Install'? I already call them during UI Stage using a Custom Action, and I don't need them to be called during installation.

Best regards,
Johannes
Attachments
Property_as_ODBC_Driver_Name_fail.png
Property_as_ODBC_Driver_Name_fail.png (234.9KiB)Viewed 8597 times

Liviu
Posts: 1035
Joined: Tue Jul 13, 2021 11:29 am
Contact:  Website

Re: Data Source - Test Connection, Persist in Registry

Thu Dec 15, 2022 9:41 am

Hello Johannes,
1. I would really appreciate a fix for this such that an AI property can be used as Driver Name for an ODBC Data Source.
I have added this improvement on our TODO list and hopefully this will be implemented in a future version of Advanced Installer.

Please note, however, that this has quite a low priority at this moment, since you are the only one requesting such feature.

However, that can change if more people will request this over the time to come.
2. Is it possible to prevent SQL queries from being executed during installation (after user pressed 'Install'? I already call them during UI Stage using a Custom Action, and I don't need them to be called during installation.
The SQL Queries are executed only from an action (during Install Execution Stage or Wizard Dialogs Stage). By default, two actions are added, one in each stage.

You can add a false condition for the SqlQueryAction added on Install Execution Stage, or you can just delete it:
sql query.png
sql query.png (119.52KiB)Viewed 8441 times

Hope this helps!

Best regards,
Liviu
________________________________________
Liviu Sandu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube

MGS
Posts: 83
Joined: Tue Oct 04, 2022 9:52 am

Re: Data Source - Test Connection, Persist in Registry

Mon Dec 19, 2022 11:14 am

Hello Liviu,
Liviu wrote:
Thu Dec 15, 2022 9:41 am
I have added this improvement on our TODO list and hopefully this will be implemented in a future version of Advanced Installer.
Thanks a lot!
Liviu wrote:
Thu Dec 15, 2022 9:41 am
The SQL Queries are executed only from an action (during Install Execution Stage or Wizard Dialogs Stage). By default, two actions are added, one in each stage.
I am already using Custom Actions without Sequence as I only need to execute them during the Wizard Dialogs Stage.

If am looking at the installer-run-log, it tells me "OnSqlInstall: Evaluating SQL script: [CustomSQLUserQuery] for processing..."

Why are my SQL queries evaluated during the Installation Execution Stage when I have no way to run the SQL queries during the Installation Stage but instead I can only call it using "UI Triggering Events" (thus calling the custom action from on of my dialogs)? What am I missing here?

Best regards,
Johannes

Liviu
Posts: 1035
Joined: Tue Jul 13, 2021 11:29 am
Contact:  Website

Re: Data Source - Test Connection, Persist in Registry

Tue Dec 20, 2022 11:24 am

Hello Johannes,

When you add a new SQL Query, the SqlQueryAction is automatically added under the Install Execution Stage. Did you removed it?

Also, can you tell me what is the actual issue here? As far as I understand it, the custom action is only evaluating. This can happen because, as you can see in the screenshot above, the custom action has two conditions: "Dialogs Stage Condition" and "Execution Stage Condition", and during installation it is possible that both conditions evaluate.

Otherwise, please forward to me an installation log and the .AIP (setup project) file by email to support at advancedinstaller dot com.

Best regards,
Liviu
________________________________________
Liviu Sandu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube

Return to “Common Problems”