# Sql Transaction 'Ant equivalent'

**URL:** <https://discuss.gradle.org/t/sql-transaction-ant-equivalent/6681>\
**Category:** Old Forum Archive\
**Created:** [August 4, 2014, 12:13pm UTC](https://discuss.gradle.org/t/sql-transaction-ant-equivalent/6681 "2014-08-04T12:13:00Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Firas\_Fafi](https://avatars.discourse-cdn.com/v4/letter/f/7bcc69/32.png) [@Firas\_Fafi](https://discuss.gradle.org/u/Firas_Fafi)\
**Post date:** [August 4, 2014, 12:13pm UTC](https://discuss.gradle.org/t/sql-transaction-ant-equivalent/6681/1 "2014-08-04T12:13:00Z")

</div>

Hello, I want to run sql transaction, something equivalent to this Ant script:

```gradle
<sql classpathref="build.class.path" driver="${db.driver}" url="${db.url_prod}" userid="${db.user}" password="${db.passwd}"
encoding="UTF-8">
   <transaction src="${basedir}/customer_specific_data/${customer}.sql" />
  </sql>

```

Well I know that we can run Ant command from gradle, but what I’m looking for is a pure functions in Gradle. Thank you!!

---

<div class="post-metadata">

**Author:** ![Lance](https://sea1.discourse-cdn.com/gradle/user_avatar/discuss.gradle.org/lance/32/787_2.png) [@Lance](https://discuss.gradle.org/u/Lance)\
**Post date:** [August 4, 2014, 12:17pm UTC](https://discuss.gradle.org/t/sql-transaction-ant-equivalent/6681/2 "2014-08-04T12:17:00Z")

</div>

See Sql.WithTransaction(…) [[http://groovy.codehaus.org/api/groovy/sql/Sql.html#withTransaction(groovy.lang.Closure)](http://groovy.codehaus.org/api/groovy/sql/Sql.html#withTransaction(groovy.lang.Closure))]

eg:

```gradle
def db = [url:'jdbc:hsqldb:mem:testDB', user:'sa', password:'', driver:'org.hsqldb.jdbc.JDBCDriver']
def sql = Sql.newInstance(db.url, db.user, db.password, db.driver)
sql.withTransaction {
   def file = new File("${basedir}/customer_specific_data/${customer}.sql")
   sql.execute file.text
}

```

---

<div class="post-metadata">

**Author:** ![Firas\_Fafi](https://avatars.discourse-cdn.com/v4/letter/f/7bcc69/32.png) [@Firas\_Fafi](https://discuss.gradle.org/u/Firas_Fafi)\
**Post date:** [August 4, 2014, 1:47pm UTC](https://discuss.gradle.org/t/sql-transaction-ant-equivalent/6681/3 "2014-08-04T13:47:00Z")

</div>

Thank you Lance. I think that we need to define the classpath, to make this work correctly. Actually I use many configurations

```gradle
configurations
{
myproject
liquibase
gwt ....
}
....

```

So my new question is: how to pass mysql dependencies into the classpath? Thank you!!

---

<div class="post-metadata">

**Author:** ![Lance](https://sea1.discourse-cdn.com/gradle/user_avatar/discuss.gradle.org/lance/32/787_2.png) [@Lance](https://discuss.gradle.org/u/Lance)\
**Post date:** [August 4, 2014, 3:10pm UTC](https://discuss.gradle.org/t/sql-transaction-ant-equivalent/6681/4 "2014-08-04T15:10:00Z")

</div>

```gradle
buildscript {
    dependencies {
        classpath 'mysql:mysql-connector-java:5.1.x'
        }
}

```

---

<div class="post-metadata">

**Author:** ![Firas\_Fafi](https://avatars.discourse-cdn.com/v4/letter/f/7bcc69/32.png) [@Firas\_Fafi](https://discuss.gradle.org/u/Firas_Fafi)\
**Post date:** [August 11, 2014, 9:43am UTC](https://discuss.gradle.org/t/sql-transaction-ant-equivalent/6681/5 "2014-08-11T09:43:00Z")

</div>

Actually this not my situation. First this is how I have defined my custom configuration:

```gradle
configurations
{liquibase}
dependencies
{
 liquibase 'mysql:mysql-connector-java:'+mysqlConnectorJavaVersion
  liquibase 'org.liquibase:liquibase-core:2.0.3'
  }

```

Then I have defined my task:

```gradle
import groovy.sql.Sql
task InstanceInitialize<<{
  def db = [url:'jdbc:mysql://127.0.0.1:3306/DB_CI?autoReconnect=true', user:'db_user', password:'2store4GOOD', driver:'com.mysql.jdbc.Driver']
  def transaction = Sql.newInstance(db.url, db.user, db.password,db.driver)
  transaction.withTransaction {
     def file = new File("$projectDir/customer_specific_data/CI.sql")
     transaction.execute file.text
  }
  }

```

Well this works fine in Eclipse but not in Console or terminal. I have found solution which consist of adding dynamically a library to the classpath:

```gradle
import Groovy.sql.Sql
 this.class.classLoader.rootLoader.addURL( new URL("file:///d:/drivers/ojdbc14.jar") )
def driver="oracle.jdbc.driver.OracleDriver";
def sql = Sql.newInstance("jdbc:oracle:thin:@hostname:port:schema", "scott", "tiger", driver);

```

source:[http://groovy.codehaus.org/How+can+I+dynamically+add+a+library+to+the+classpath](http://groovy.codehaus.org/How+can+I+dynamically+add+a+library+to+the+classpath)

My new question is :

How to mix all these stuffs to get full working task in all environments(Eclipse, terminal…) Thank you again !!

---

<div class="post-metadata">

**Author:** ![Firas\_Fafi](https://avatars.discourse-cdn.com/v4/letter/f/7bcc69/32.png) [@Firas\_Fafi](https://discuss.gradle.org/u/Firas_Fafi)\
**Post date:** [August 11, 2014, 10:45am UTC](https://discuss.gradle.org/t/sql-transaction-ant-equivalent/6681/6 "2014-08-11T10:45:00Z")

</div>

Well finally I have found the solution:

```gradle
import groovy.sql.Sql
task InstanceInitialize<<{
  def SqlClasspath=configurations.liquibase{exclude group:'org.liquibase'}
  SqlClasspath.each { file ->
      println "Adding URL: $file"
    gradle.class.classLoader.addURL(file.toURI().toURL())
  }
  def sql = Sql.newInstance(url:"jdbc:mysql://127.0.0.1:3306/$DB?autoReconnect=true",user:"$DBUser",password:"$DBPasswd", driver:"$DBDriver")
  sql.withTransaction {
     def file = new File("$projectDir/customer_specific_data/CI.sql")
     sql.execute file.text
  }
  }

```

Thank you !!