Configuring a Database with SQL Scripts

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

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.

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

Configure an ODBC Driver

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.

Click on the [ Add Driver ] toolbar button and create a driver called "MyODBC".

Configure it as follows:

Configure an SQL Connection

Go to the SQL Scripts page.

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 a 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:

Adding SQL Scripts to the 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 and condition of each SQL script. To change the execution order, move them by drag and drop in the tree.

Run the project

Build and run the package.

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.