Simple Database Population Tool

(Recovered from my old Blog).

My source control contains many tables, many of which have dependencies on one another via foreign keys. When the table structures are being changed fairly regularly, it can be a pain to have to keep updating the database manually.

Ant has a built in SQL task which will pick up a file, then execute the contained statements on the database of your choice.

  1. Firstly, create the required scripts to be executed. For table creation scripts, I always add ‘DROP TABLE IF EXISTS’ (or a variant depending on SQL dialect) to make it easier to run a script on its own if it has no external dependencies.
  2. Create extra scripts for dropping the tables (with the commands in the correct order to respect foreign keys).
  3. Create an Ant build file (build.xml). Each script to be executed is placed in the file in order of execution (to again respect foreign keys).
  4. Execute Ant from the directory containing build.xml

Example for step one:

Example for step two (filename – drop_party.sql):

Example for step three (build.xml):

It is of course important that the JDBC driver referenced (in the example com.mysql.jdbc.Driver) is available to Ant. A simple way of doing this is to add the relevant jar to Ant’s lib directory.

MySQL Stored Procedures
The above technique works for MySQL stored procedures, with one minor change. When writing a MySQL stored procedure, the delimiter to use must be explicitly set (as ; is used within the stored procedures statements).

To set this delimiter, the ‘delimiter’ attribute within the sql tag must be provided.

For example:

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: