Database access with Ant!

15

You can access a database with Ant. This can be useful when you want to create a database and fill it with some default data at the start/deployment of a project, but also when you want to reset the contents of your database just before invoking your tests!

Example of an Ant target to create a table:




CREATE TABLE br_vote
(
voteid INTEGER NOT NULL,
reviewid INTEGER NOT NULL,
votevalue INTEGER DEFAULT 5 NOT NULL
);


The accompanying build.properties for MySQL access in this case reads:

mysql.password = MyDatabaseUserPassword
mysql.user = MyDatabaseUserName
mysql.driver = com.mysql.jdbc.Driver
mysql.url = jdbc:mysql://localhost/myDatabase
mysql.classpath = /classpath/to/mysql-jdbc.jar

Mutatis mutandum for the build.properties, I was also able to successfully access an Oracle DB (LUDO).

Of course, you can also populate a DB this way, by just formulating a different target and putting the appropriate SQL statements in.

Share.

About Author

15 Comments

  1. Pingback: Bad Credit Credit Cards

  2. Pingback: Puma Shoes

  3. Pingback: Ties

  4. Pingback: Bad Credit Credit Cards

  5. Pingback: Magazine

  6. Pingback: Bowling Shoes

  7. Pingback: Wireless Camera Shop

  8. No, the syntax of the function definition is correct like this: function function_name (parameters) return <type> is …

  9. Sorry musn’t :
    Function rdc_date_get_jou(
    p_datin IN DATE
    )
    Return VARCHAR2;

    to be
    Function rdc_date_get_jou(
    p_datin IN DATE
    Return VARCHAR2;

    )

    ???? I mean the Return VARCHAR2;
    shouldn’t be inserted into the function?

  10. Could you give an example ? Because even with a very simple procedure, and without any comment,
    I still dont’manage to get any compilation error. For example :

    Create OR Replace Package pkg_utils_date
    AS
    Function rdc_date_get_jou(
    p_datin IN DATE
    )
    Return VARCHAR2;
    nd pkg_utils_date;

    As you can see the “End” keyword is missing one character and ant says it completed successfully…

  11. It seems all PL/SQL code is seen as one very long line. That means that any — style (normally
    used as end of line) comments prevent the remainder of the line to be compiled. This basically means that any PL/SQL program unit with end-of-line comments will create incorrectly. I have not yet found a workaround other than leaving out end of line comments (and only use /* */ style comments).

  12. Hi,

    I tried what Lucas suggested (delimiter=”/” and delimiterType=”row”). It indeed seems to be the only way for Ant
    to be able to read the content of the file. But it does not see any compilation error ! If I have a look to Oracle
    the pl/sql code has been loaded but is in invalid state (and does not compile) but ant says it has completed succesfully.

    Setting the onerrror attribute has no effect as the default value is abort.

    What Am I doing wrong ?

    Thanks,
    Jay

  13. Lucas Jellema on

    The task not only may contain complete SQL statements but also references to files that contain SQL statements. Thye following task was used for invoking a number of DDL scripts to set up a database. These same scripts can be used from SQL*Plus.

    <target name="_buildTables" depends="_init" >
      <sql driver="oracle.jdbc.driver.OracleDriver"
           url="${database.url}"
           userid="${database.username}"
           password="${database.password}">
         <fileset dir="${database.ddl.dir}">
            <include name="als_tables.sql"/>
         </include></fileset>
      </sql>
    </target>
    

    Files containing PL/SQL code it is important to specify how the ANT task can identify the end of the code units. For example:

    <target name="_buildPackageSpecs" depends="" >
      <sql driver="oracle.jdbc.driver.OracleDriver"
           url="${database.url}"
           userid="${database.username}"
           password="${database.password}"
           delimiter="/"
           delimitertype="row" >
         <fileset dir="${database.ddl.dir}">
            <include name="CG$ERRORS.PKS"/>
            <include name="CG$ALS_AUTHORS.PKS"/>
         </include></include></fileset>
      </sql>
    </target>
    

    see also http://www.bris.ac.uk/is/projects/portal/team/lilian/AntSql.

  14. Zeger Hendrikse on

    Of course, DB set-up for testing can be done with Ant but:

    • DbUnit adds in-container testing if DB is accessed by container, e.g. J2EE container
    • DbUnit adds verification of contents of DB after tests

    By the way, in DbUnit the DB set-up is defined in flat XML file.