Ant build should fail on java.sql.Exception

[Khushwinder Chahal]

Hi All,

I am using the following ant script for creating my databases:

<sql
    driver="org.database.jdbcDriver"
    url="jdbc:database-url"
    userid="sa"
    password="pass"  onerror="abort">
  <transaction  src="data1.sql"/>
  <transaction  src="data2.sql"/>
  <transaction  src="data3.sql"/>
</sql>

Because i am using onerror="abort" It is aborting the ant build if any file produces the sql exception and stop executing that specific sql file.
For example if data3.sql having some err it will abort the ant but data1.sql and data2.sql files have already executed and their results do not roll back.
Is it possible to abort the whole operation rather that aborting the execution of a single sql file.?
What i want is that if any one of the sql files produces sql exception, all files which have already been executed before that file also roll back their output.

If anybody has any idea please help me.

Thanks and Regards,

[Peter Johnson]

You currently have set up the task to run three separate transactions. Try placing all of the sql files into a single transaction like this:

<sql  
    driver="org.database.jdbcDriver"  
    url="jdbc:database-url"  
    userid="sa"  
    password="pass"  onerror="abort">  
  <transaction>
    <fileset dir="." includes="data1.sql,data2.sql,data3.sql" />
  </transaction>  
</sql> 

[Khushwinder Chahal]

Thanks a lot Peter for your reply.

when i am trying to execute like the following :

<?xml version="1.0"?>
<project name="testdatabase"  basedir=".">
<property file="database.properties"/>
<target name="init-db" >
		<sql driver="${driver}" url="${database.server.url};database=test" userid="${sa.username}" password="${sa.password}" autocommit="true" onerror="abort" caching="false"       classpath="${lib.dir}/jdbc_drivers/jtds-1.2.jar" delimiter="${sql.delimiter}">
		
	    	 <transaction>  
			  <fileset dir="." includes="init_db.sql,baseline.sql,baseline_data.sql" />  
		    </transaction>  
		</sql>
	</target>
	</project>

Its giving me err that

build failed
build.xml:5: only single argument resource collections are supported.

One more thing is that in my rela project my script files are not in a single folder they are in separate folders.
Please suggest me any way out of this problem.

Thanks and Regards,

[Khushwinder Chahal]

Now i am trying to do it like the following :

<?xml version="1.0"?>
<project name="testdatabase"  basedir=".">
<property file="database.properties"/>
<target name="init-db" >
		<sql driver="${driver}" url="${database.server.url};database=test" userid="${sa.username}" password="${sa.password}" autocommit="false" onerror="abort" caching="false" classpath="${lib.dir}/jdbc_drivers/jtds-1.2.jar" delimiter="${sql.delimiter}">
		
<path>
    <fileset dir=".">
      <include name="*.sql"/>
    </fileset>
  </path>

		</sql>
	</target>
	</project>

It is stop executing a specific file's script if it contains any err because autocommit="false" but it does not roll back the results of other script files which have been executed already.
If any body has any idea?
thanks and regards,

[Peter Johnson]

The error "only single argument resource collections are supported. " tells me that the sql task will not let you span a transaction across multiple SQL source files. So try this: use the concat task to create a temporary file containing all of the SQL sources, give that temp file to the sql task, delete the temp file afterwards.