Does SQL task support a single transaction for multiple sql files?

[da...@netscape.net]

Hi,

We are using ANT for our deployments and want to automate the database updates as well. We have multiple sql files for a single release to make it easier to maintain them.

When we do a deployment I want to run all the scripts in a single transaction so that if any go wrong I can roll them all back. Unfortunately the SQL task says that it creates a new transaction for each file, this means that if one fails it won't roll back them all. It needs to be all or nothing when it comes to the database as the new code we deploy will expect all the changes there and if we roll back, the old code will expect none of the changes.

Any suggestions?
Do I need to create my own custom task?

Cheers Dave

__________________________________________________________________
The NEW Netscape 7.0 browser is now available. Upgrade now! http://channels.netscape.com/ns/browsers/download.jsp 

Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/

Re: Does SQL task support a single transaction for multiple sql files?

[Conor MacNeill <co...@cortexebusiness.com.au>]

davexmail@netscape.net wrote:
> Hi,
> 
> We are using ANT for our deployments and want to automate the database updates as well. We have multiple sql files for a single release to make it easier to maintain them.
> 
> When we do a deployment I want to run all the scripts in a single transaction so that if any go wrong I can roll them all back. Unfortunately the SQL task says that it creates a new transaction for each file, this means that if one fails it won't roll back them all. It needs to be all or nothing when it comes to the database as the new code we deploy will expect all the changes there and if we roll back, the old code will expect none of the changes.
> 
> Any suggestions?
> Do I need to create my own custom task?
> 

You could extend the current sql task to accept filesets for the nested 
<transaction> elements, so that all files in the fileset are executed within 
that transaction. Note, however, that filesets are unordered, which may not 
always be useful for applying sql statements. YMMV ...


Conor

Re: Does SQL task support a single transaction for multiple sql files?

[Martin <mg...@hotmail.com>]

Basically he needs to exercise transaction capability of the Database
what he should do is setup commit capabilities on the database your are
connecting to
then at the VERY end of all the insert/update/delete SQL Statements
execute the SQL command COMMIT;
and that will commit all your changes to the Database
if he bugs out for any reason the changes will ALL be lost
-Martin

----- Original Message -----
From: "Erik Hatcher" <ja...@ehatchersolutions.com>
To: "Ant Users List" <us...@ant.apache.org>
Sent: Monday, February 17, 2003 8:10 PM
Subject: Re: Does SQL task support a single transaction for multiple sql
files?


> Would <concat>'enating the files together do the trick for you?
>
>
> On Monday, February 17, 2003, at 06:56  PM, davexmail@netscape.net
> wrote:
>
> > Hi,
> >
> > We are using ANT for our deployments and want to automate the database
> > updates as well. We have multiple sql files for a single release to
> > make it easier to maintain them.
> >
> > When we do a deployment I want to run all the scripts in a single
> > transaction so that if any go wrong I can roll them all back.
> > Unfortunately the SQL task says that it creates a new transaction for
> > each file, this means that if one fails it won't roll back them all.
> > It needs to be all or nothing when it comes to the database as the new
> > code we deploy will expect all the changes there and if we roll back,
> > the old code will expect none of the changes.
> >
> > Any suggestions?
> > Do I need to create my own custom task?
> >
> > Cheers Dave
> >
> > __________________________________________________________________
> > The NEW Netscape 7.0 browser is now available. Upgrade now!
> > http://channels.netscape.com/ns/browsers/download.jsp
> >
> > Get your own FREE, personal Netscape Mail account today at
> > http://webmail.netscape.com/
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
> > For additional commands, e-mail: user-help@ant.apache.org
> >
> >
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
> For additional commands, e-mail: user-help@ant.apache.org
>

Re: Does SQL task support a single transaction for multiple sql files?

[Erik Hatcher <ja...@ehatchersolutions.com>]

Would <concat>'enating the files together do the trick for you?


On Monday, February 17, 2003, at 06:56  PM, davexmail@netscape.net 
wrote:

> Hi,
>
> We are using ANT for our deployments and want to automate the database 
> updates as well. We have multiple sql files for a single release to 
> make it easier to maintain them.
>
> When we do a deployment I want to run all the scripts in a single 
> transaction so that if any go wrong I can roll them all back. 
> Unfortunately the SQL task says that it creates a new transaction for 
> each file, this means that if one fails it won't roll back them all. 
> It needs to be all or nothing when it comes to the database as the new 
> code we deploy will expect all the changes there and if we roll back, 
> the old code will expect none of the changes.
>
> Any suggestions?
> Do I need to create my own custom task?
>
> Cheers Dave
>
> __________________________________________________________________
> The NEW Netscape 7.0 browser is now available. Upgrade now! 
> http://channels.netscape.com/ns/browsers/download.jsp
>
> Get your own FREE, personal Netscape Mail account today at 
> http://webmail.netscape.com/
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
> For additional commands, e-mail: user-help@ant.apache.org
>
>