Using InstallScript Text Substitution to Dynamically Replace Strings in SQL Scripts

InstallShield 2024

Project: This information applies to the InstallScript projects.

For InstallScript projects, you can specify a replacement for text in a SQL script at run time through the use of text substitution string variables. This enables you to let end users specify information that is then used in the SQL script that is launched on the target system. The InstallScript run-time code uses the TextSubSubstitute function to replace the string variable with the appropriate value in the SQL script.

Example

The following procedure demonstrates how to create a database at run time using a custom SQL script that contains information that end users enter on the SQLServerSelectLogin2 dialog. InstallScript text substitution is used for the database name and its target location.

To create a database using a SQL script that contains information that end users specify at run time:

In the View List under Server Configuration, click SQL Scripts.

Right-click the SQL Scripts explorer and click New SQL Connection. InstallShield adds a new SQL connection.

Click the SQL connection, and then click the General tab.

Clear the Create Catalog If Absent check box.

In the SQL Scripts explorer, right-click the new connection and click New Script. InstallShield adds a new SQL script to the SQL connection.

Click the SQL script, and then click the Script tab.

In the script pane, enter the following:

if not exists(select name from master.dbo.sysdatabases where name = '%DBNAME%')

begin

CREATE DATABASE %DBNAME%

( NAME = %DBNAME%_dat,

FILENAME = '%DBPATH%\%DBNAME%dat.mdf',

SIZE = 10,

MAXSIZE = 50,

FILEGROWTH = 5 )

LOG ON

( NAME = %DBNAME%_log,

FILENAME = '%DBPATH%\%DBNAME%log.ldf',

SIZE = 5MB,

MAXSIZE = 25MB,

FILEGROWTH = 5MB ) ;

end

Configure two text replacements for the SQL script:

Click the Text Replacement tab, and then click the Add button. The Find and Replace dialog box opens.

In the Find What box, type the following:

%DBNAME%

In the Replace What box, type the following:

Click OK. The Find and Replace dialog box closes.

Click the Add button. The Find and Replace dialog box opens.

In the Find What box, type the following:

%DBPATH%

In the Replace What box, type the following:

Click OK. The Find and Replace dialog box closes.

Click the Runtime tab.

10.

Clear the Run Script During Install check box and select the Run Script During Login check box.

11.

In the View List under Behavior and Logic, click InstallScript.

12.

Find the dialog code in the OnSQLServerInitialize event for the dialog that should contain the Database Name control, and add a call to the InstallScript function TextSubSetValue. For example, if you want the user name to be the name that the end user specifies on the SQLServerSelectLogin2 dialog, you would add a TextSubSetValue call as shown in the following lines of code:

// Display login dialog (without connection name)

// UNCOMMENT OUT TO SWAP DIALOGS

// nResult = SQLServerSelectLogin2( szConnection, szServer, szUser, szPassword, bWinLogin, szDB, FALSE, TRUE );

// Display login dialog (with connection name)

// COMMENT OUT TO SWAP DIALOGS

nResult = SQLServerSelectLogin2( szConnection, szServer, szUser, szPassword, bWinLogin, szDB, TRUE, TRUE );

TextSubSetValue ("<MYDATABASENAME>", szDB, FALSE);

13.

Build your release.