SQL Connection Question

[timstspry]

Hello, I have several questions I was hoping to get some feedback on. First, I am using an evaluation version of InstallShield 2008 and have no experience using InstallShield. Now that we have cleared that up. I do have experience developing Windows Installers with a competitors product. I also have experience using a competitors scripting language. My questions are:

Can I establish a SQL Connection where the Server, Uid, and Pwd parameters are passed in? I apologize for not being able to phrase this correctly, but the gist of what I am doing in the competitors product is reading the servername and whether or not the connection is a secure connection from a config file located on the hard drive where the installer will run, and then setting various Windows Properties with different information depending upon what I read from the config file. This is done in the scripting language previously mentioned.

The problem is that there seems to be a bug where if I pass a Windows Property in the Server parameter, the connection fails. I have reported said bug, but don't know if or when it will be resolved. If I could do the same thing in installscript I am guessing and not have this problem, this would be a great thing for me! By the way, I would be connecting primarily to the MSDE2000 and SQL Express using a named instance if this makes any difference.

Secondarily, how easy would it be to transfer my skills from the competitor product to InstallShield and InstallScript? From what I can see in the limited exposure I have had to InstallShield, it looks fairly similar. I have not really played around with InstallScript yet.

Thanks in advance for any and all suggestions!

Tim

[hidenori]

InstallShield 2008 provides the built-in SQL feature which currently supports Microsoft SQL Server, MySQL, and Oralce. In the SQL Scripts view under the Server Configuration section, you simply need to add database connections and SQL scripts in order to use this feature. Once you do that, the SQLLogin dialog will be displayed after the Customer Information dialog and it will verify the connections when you click the Next button. The SQL scripts will be executed at the installation session. See http://helpnet.installshield.com/Robo/BIN/Robo.dll?tpc=/robo/projects/installshield14helplib/SQLServer.htm for more information.

However, the feature has some limitations such as SQL query results cannot be handled outside SQL scripts, the PRINT statements are ignored, and so on. If those limitations are problems for you, you need to write your InstallScript code to manage database connections and SQL scripts. There are several sample codes posted in InstallShield community forum:

Code for attaching a database:
http://community.installshield.com/showpost.php?p=288448&postcount=17

Code for running a SQL script:
http://community.installshield.com/showpost.php?p=266290&postcount=2


Note that you still need to write your code to read login information from a config file and set them to the Windows Installer properties so that they will be displayed in the built-in SQLLogin dialog even when you use the native SQL support. That can be done using an InstallScript custom action:

function ReadSQLLoginInfo(hMSI) 
STRING szServerName, szDatabaseName, szUserName, szUserPassword, szAuthenticationType;
begin

//TO DO: read login information from a config file

// Set to Windows Installer properties
MsiSetProperty(ISMSI_HANDLE, "IS_SQLSERVER_SERVER", szServerName);
MsiSetProperty(ISMSI_HANDLE, "IS_SQLSERVER_DATABASE", szDatabaseName);
// 1: SQL authentication, 0: Windows authentication
MsiSetProperty(ISMSI_HANDLE, "IS_SQLSERVER_AUTHENTICATION", szAuthenticationType);
MsiSetProperty(ISMSI_HANDLE, "IS_SQLSERVER_USERNAME", szUserName); 
MsiSetProperty(ISMSI_HANDLE, "IS_SQLSERVER_PASSWORD", szUserPassword);
endif;

Hope this helps.

[timstspry]

Thank you for your suggestions. I think that writing an InstallScript would be the only way I would go as the users of my application typically would not know the connection information and I would need to read it from a config file. It appears that InstallShield 2008, in particular InstallScript, has this capability which is what I was looking for.

Thanks again for taking the time to respond!

Tim