In my previous note about QuantumDB, I explained how to use an SQL database within Eclipse. But now, the question is how to automate the run of a SQL script using Ant...

Note: to illustrate this, we will use a MySQL datatbase but it's the same idea with another database.

[More:]

This is the process to run SQL commands using Ant:

1. If not yet done, please download MySQL Connector/J 3.1, the JDBC connector for MySQL database. Uncompress the zip under c:\Program files\mysql-connectorj.


2. Create an SQL file called instructions.sql for example and put the SQL commands to peform in this file.



3. Open a build.xml file and the write the following instructions:

   <project name="myproject" default="db.build">
   
   <target name="db.build">	
     <sql driver="com.mysql.jdbc.Driver"
          url="jdbc:mysql://localhost:3306/database?useUnicode=true&amp;characterEncoding=UTF-8"
          userid="username" password="password" >
       <classpath>
         <pathelement path="c:/program files/mysql-connectorj/mysql-connector-java-3.1.12-bin.jar"/>      
       </classpath>
       <transaction  src="${sql.path}/db_structure.sql"/>
     </sql>
   </target>	    
   
   </project>
   




4. Under Eclipse, right click on the build.xml and choose Run as | Ant build. The commands are played against the database. You should read something like this in the console:

   db.build:
         [sql] Executing file: C:\Mes documents\eclipse\myproject\sql\instructions.sql
         [sql] 9 of 9 SQL statements executed successfully
   

Note 1: ?useUnicode=true&characterEncoding=UTF-8 is only to add in the url element if your database is using the UTF-8 charaset encoding.

Note 2: there is perhaps a pretty solution to avoid the password to be in clear text in the build file, but I don't know how to do that. If someone has got an idea...

11 comments

Comments:

Comment from: nagaraj [Visitor]

when i am executing sql script file from ant script.its throwing

[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Line 1: Incorrect syntax near 'go'.

the syntax is"







"

Permalink 04/22/06 @ 11:38

Comment from: Mick Killianey [Visitor]

To avoid your password being in clear text, one handy solution is to use properties for the username/password like this:



and just make sure to set the properties before that statement. One way to do this is by having ant include a properties file (presumably that ISN'T checked into your source control system) like this:

# Lines beginning with # are comments
some.username=yourusernamehere
some.password=yourpasswordhere

If this file was called "sqllogin.properties", you include it like this:



This way, the ant build file gets checked into your source control system, but the file with username/password doesn't.

Finally, if it makes you uncomfortable to store your password in a .properties file on your machine, you always have the option of using ant's "input" task to set a property by prompting the user.

Permalink 04/29/06 @ 09:58

Comment from: Mick Killianey [Visitor]

Whoops...the comment maker stripped out my ant examples. Replace the [] with less-than/greater-than to get the two missing examples in my previous comment:

(1) [sql driver="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/database?useUnicode=true&characterEncoding=UTF-8"
userid="${some.username}" password="${some.password}" ]

(2) {property file="sqllogin.properties"]

mk

Permalink 04/29/06 @ 10:01

Comment from: Carol Beckley [Visitor] · http://www.eclipsemag.net

Hi, thought this might of use to this blog's readership.

There's a new Eclipse Magazine (www.eclipsemag.net) out there. The inaugural issue of the magazine is available for FREE. You can download it from here. Once you register, you also have FREE access to the magazine for the months of September, October, and November 2006.

Permalink 08/29/06 @ 11:25

Comment from: Serge Baccou [Member] · http://www.baccoubonneville.com

Carol,

If you are seeking for a writer for your magazine, you can contact me.

FYI, I will speak at Java User Group conference (Thursday September 28th, 2006 in Strasbourg) about Eclipse RCP.

Serge Baccou

Permalink 08/29/06 @ 12:53

Comment from: Joseph [Visitor]

Hi Serge

The page you refered too in the post above isn't available to everyone. Is it normal ?

Furthermore, are the slides downloadable somewhere ?

Thanks in advance

Joseph

Permalink 10/02/06 @ 11:03

Comment from: Serge Baccou [Member] · http://www.baccoubonneville.com

Hi Joseph,

I do not understand what page you are talking about. Can you be more precise?

About the slides, are you talking about the Java User Group conference held in Strabourg?

Permalink 10/02/06 @ 12:19

Comment from: Joseph [Visitor]

I'm speaking about this link :
http://www.baccoubonneville.com/index.php?option=com_content&task=view&id=48&Itemid=35

When trying to open it I get :
"You are not authorized to view this resource.
You need to login."

Regarding the slides, I'm indeed refering the JUG conference held in Strasbourg.

Best regards,
Joseph

Permalink 10/03/06 @ 10:27

Comment from: Joseph [Visitor]

NB :

le formulaire de prise de contact là :
http://www.baccoubonneville.com/index.php?option=com_contact&task=view&contact_id=1&Itemid=44
me renvoit (avec Firefox) à la page d'erreur ayant l'url http://www.baccoubonneville.com/index.php?option=com_contact&Itemid=44 et avec comme texte :
Precondition Failed

The precondition on the request for the URL /index.php evaluated to false.

C'est dommage !

Permalink 10/03/06 @ 12:32

Comment from: Serge Baccou [Member] · http://www.baccoubonneville.com

The page http://www.baccoubonneville.com/index.php?option=com_content&task=view&id=48&Itemid=35 was to make some promotion about the JUG conference. It has been unpublished as soon as the conference has been done.

The slides are now on our web site http://www.baccoubonneville.com.

For the problem you have about the contact form, I will investigate what is going wrong with this.

Serge

Permalink 10/03/06 @ 12:46

Comment from: Janaka Dalugama [Visitor]

to avoid 'go' syntax error use delimiter.

Ex ;


<sql driver="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/database?useUnicode=true&amp;characterEncoding=UTF-8"
userid="username" password="password" delimiter='GO' >
.....

Permalink 12/04/07 @ 12:06

Leave a comment:

Name:

Email:

Your email address will not be displayed on this site.

Site/Url:

Your URL will be displayed.

Comment text:

Allowed XHTML tags: <p, ul, ol, li, dl, dt, dd, address, blockquote, ins, del, span, bdo, br, em, strong, dfn, code, samp, kdb, var, cite, abbr, acronym, q, sub, sup, tt, i, b, big, small>

Options:

Auto-BR (Line breaks become <br />)
Remember me (Set cookies for name, email and url)
Allow message form (Allow users to contact you through a message form (your email will NOT be displayed.))

Captcha:

Please enter the characters from the image above. (case insensitive)

Captcha code:

Pingbacks:

No Pingbacks for this post yet...