Configuring Databases with SQL Scripts

The following article uses options that are available starting with the Enterprise edition and project type.

This tutorial will guide you in configuring SQL scripts that will be executed during installation if the application you are installing depends on certain database content and configuration.

1. Configure an ODBC Driver
2. Configure an SQL Connection
3. Add SQL Scripts to connection
4. Run the project

1. Configure an ODBC Driver

The SQL Script configuration provided by Advanced Installer uses ODBC connectivity, so you will need to install an ODBC driver for the targeted DBMS (Database Management System) or rely on a preexisting driver/datasource on the destination system.

Let's suppose you want to create a package that deploys a web application which relies on a MySQL database. The application requires that a new database be created containing all the tables needed for storing the application's data, as well as pre-loading some tables.

Before configuring the ODBC Driver you need to add its files in the Files and Folders view.

The ODBC driver files (DLLs) can be downloaded from the manufacturer's web site.

Go to the ODBC page.

Select the [ Add Driver ] context menu item to add a driver called “MyODBC”.

Select the [ New Data Source ] context menu item to create a new data source for the driver.

2. Configure an SQL Connection

Go to the SQL Databases Page, Database server.

Use the [ New SQL Connection ] toolbar button to create a new SQL Connection called "MySQLConnnection"

The configuration data for the connection can be collected with the Advanced Installer MSI dialog "SQLConnectionDlg". You can add this dialog from the Dialog Editor view and customize it or you can create your own. When using an MSI dialog to collect the data, in the formatted edit fields: ODBC Driver/Data Source, Server, Port, Username, Password, Database insert references to those respective Windows Installer properties associated with the MSI dialog edit controls.

Configure it as follows:

3. Add SQL Scripts to connection

Use the [ New SQL Script ] toolbar button to add a new SQL Script to the above connection.

You will have to provide an SQL script file (manually edited or generated by a database dump tool). For example let's use the following SQL Script:

--
-- Table structure for table `user`
--

CREATE TABLE `user` (
  `user_id` int(10) unsigned NOT NULL auto_increment,
  `group_id` int(10) unsigned NOT NULL default '0',
  `user_login` varchar(100) NOT NULL default '',
  `user_email` varchar(255) NOT NULL default '',
  `user_fname` varchar(255) NOT NULL default '',
  `user_lname` varchar(255) NOT NULL default '',
  `user_phone` varchar(15) NOT NULL default '',
  PRIMARY KEY  (`user_id`),
  UNIQUE KEY `user_login` (`user_login`),
  KEY `group_id` (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='User Table';

--
-- Table structure for table `user_groups`
--

CREATE TABLE `user_groups` (
  `group_id` int(10) unsigned NOT NULL auto_increment,
  `group_name` varchar(255) NOT NULL default '',
  `group_description` text NOT NULL,
  `group_attributes` smallint(5) unsigned NOT NULL default '0',
  PRIMARY KEY  (`group_id`),
  UNIQUE KEY `group_name` (`group_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='User Groups Table';


---------------------------------------------------------------

--
-- Dumping data for table `user`
--

INSERT INTO `user`
(`user_id`, `group_id`, `user_login`, `user_email`,
 `user_fname`, `user_lname`, `user_phone`)
VALUES
(1, 1, 'admin', 'admin@site.net', 'John', 'Doe', '+10123456789'),
(2, 2, 'bob', 'bob@site.net', 'Bob', 'Smith', '+10123456789'),
(3, 2, 'brian', 'brian@site.net', 'Brian', 'Doyle', '+10123456789'),
(4, 3, 'cip', 'chip@site.net', 'Chip', 'Dale', '+10123456789'),
(5, 3, 'chris', 'chris@site.net', 'Chris', 'Johnson', '+10123456789');

--
-- Dumping data for table `user_groups`
--

INSERT INTO `user_groups`
(`group_id`, `group_name`, `group_description`, `group_attributes`)
VALUES
(1, 'Administrators', 'Site Administrator''s Group', 555),
(2, 'Editors', 'Group with the article editors', 120),
(3, 'Users', 'Registered users', 40);

You can also download the SQL Script Example.

Configure it as follows:

Repeat the above steps to add as many SQL connections and scrips as you want. If more that one script is added to a connection, they will be executed in the order they appear in the tree, taking into consideration the run time (scheduling) and condition of each SQL script. To change the execution order, move them by drag and drop in the tree.

4. Run the project

Build and run the package.

Run

The changes made by an SQL Script on a database cannot be undone. Uninstall won't rollback the changes performed by the SQL statements. You will need to supply, if necessary, SQL scripts scheduled to run at uninstall or rollback that will undo changes made by the scripts ran during install.