Configuring Databases with SQL Scripts

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.

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 a SQL Connection
• 3. Add SQL Scripts to connection
• 4. Run the project

1. Configure an ODBC Driver

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

Go to the ODBC page.

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

Configure it as follows:

2. Configure a SQL Connection

Go to the SQL Scripts page.

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

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:

4. Run the project

Build and run the package.

The End

This concludes our tutorial.