SqlConnections Questions

[Christopher_Pai]

I am using the bult in SQL dialogs and CA's to validate the connection string information for either a SQL server or an Oracle instance. I don't actually apply any sql changes, but I use the properties to XPath an App.Config file to teach a .NET WindowsService how to talk to it's preexisting database.

This works fine for SQLServer, but now that I want to support Oracle I have several observations/questions

1) The dialog seems to present a list datasources without representing whether which provider class they belong to. I don't see anything in a log file to tell me either. In my App.Config I have to set a Provider element so that the service knows how to invoke a factoryprovider to connect to it's data source.

2) Connection testing for Oracle seems to be very, very slow

3) I've looked at the InstallScript SQLRT* functions and they don't seem to help my problem.

Any suggestions on how I should go about this? Should I just dump the entire pattern and roll my own .NET class for testing database connectivity?

[Christopher_Pai]

Any thoughts?

[Christopher_Pai]

Bump Bump?

[hidenori]

InstallShield currently retrieves server names instead of data source names from DSN using the provider name specified the DsnODBCName column in the ISSQLDEBMetaData table. However, this functionality is turned off for the Oracle support as you can see the column value is blank. Instead, we are collecting TNS names from the tnsnames.ora files available on the local machine.

Also, InstallShield connects to Oracle database servers through Microsoft ODBC for Oracle. You can force InstallShield to use another ODBC driver such as Oracle ODBC from Oracle by modifying the ISSQLDEBMetaData table, and see if there is any difference on connection performance. The following is the column values that need to be changed to use Oracle 9 ODBC driver.

AdoDriverName: {Oracle in OraHome92}
AdoCxnServer: DBQ=

Note that the driver name, {Oracle in OraHome92} may be different on your machine.

[Christopher_Pai]

The list of servers is properly populated as you say, and the connection succeeds for both SQL and ORA. But my question remains, after a user successfully connects, how do I know if the connection data is for a SQL datasource or an ORA datasource? I need to configure my app.config differently depending on the answer.

I hope my question makes sense.

[hidenori]

OK, what type project are you using?

[Christopher_Pai]

Basic MSI.

I've looked through a verbose logfile hoping to find a clue but nothing is sticking out at me.

I also wonder, what would happen if I had a TNS Alias that was the same name as a SQL servername. Would it try to connect to both and return success if either worked?

Thanks,
Chris

[hidenori]

By default, InstallShield verifies all of the connections specified in the SQL Scripts view, and lists available server names for all of the database technologies selected in the Advacned tab. Using the IS_SQLSERVER_CONNECTIONS_TO_VALIDATE Windows Installer property, you can only display available server names specific to a connection, and/or validate a specific connection. So you should be able to accomplish your requirements by having a separate connection for each database technology. The following steps illustrates how it can be done using one SQLLogin dialog, and a database server type selection dialog:

[LIST=1]

In the SQL Scripts view, add 2 connections and name them MSSQL and ORACLE.

Mark only the "Microsoft SQL Server" checkbox for the MSSQL connection, and the "Oracle" for the ORACLE connection in the Advanced tab.

In the Dialogs view, add a new dialog named "MyDbServerType".

Add a RadioButtonGroup control and associate with a custom Windows Installer property named MY_DB_SERVER_TYPE.

Add 2 RadioButton controls and set "Microsoft SQL Server" and "Oracle" as the text for each.

Set MSSQL to the Value property of the "Microsoft SQL Server" RadioButton, and ORACLE to the "Oralce" RadioButton.

Change the dialog sequence to display the MyDbServerType dialog between the CustomerInformation and SQLLogin dialog.

Add the following event to the Next button, the BtnSQLBrowse button, and the BtnDbBrowse button of the SQLLogin dialog and place it before the DoAction event:
[IS_SQLSERVER_CONNECTIONS_TO_VALIDATE] [MY_DB_SERVER_TYPE] 1

By checking the value of the MY_DB_SERVER_TYPE property, you should be able to know which database technology is picked for the selected server name.

Hope that helps.

[jfrasmussen]

I'm a bit of a beginner,

can someone please expand on the step:

Add the following event to the Next button, the BtnSQLBrowse button, and the BtnDbBrowse button of the SQLLogin dialog and place it before the DoAction event:
[IS_SQLSERVER_CONNECTIONS_TO_VALIDATE] [MY_DB_SERVER_TYPE] 1

I can't seem to find [IS_SQLSERVER_CONNECTIONS_TO_VALIDATE] in the actions list, and when i add [MY_DB_SERVER_TYPE] 1 to the condition list, it complains.

Is there something else i should be doing? I was expecting a place where i can set the value of a property. but i'm obviously going about it wrong...
Thanks

EDIT, oh, maybe it's because i'm using InstallShield 2010?

[hidenori]

You need to create a control event by typing [IS_SQLSERVER_CONNECTIONS_TO_VALIDATE] in the Event field, [MY_DB_SERVER_TYPE] for the Argument field, and 1 for the Condition field.

[jfrasmussen]

When applying as you suggest, I get an error

Error 2812. The event IS_SQLSERVER_CONNECTIONS_TO_VALIDATE is not recognized.

Is there a section of the help file I could refer to to brush up on this? I feel I may be wasting the time of whomever answers this question by not being as versed as I could be.

If I create the property in the property manager myself, i don't see how the installer knows that what i'm asking for is for it to choose between one of two connections.


Thanks.

[hidenori]

jfrasmussen, would it be possible for you to send me your project file (.ism) at hyamanishi@flexerasoftware.com so that I can take a look?