Sql Transaction 'Ant equivalent'

Old Forum
1

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

<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!!

2

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

eg:

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
}
1 Like
3

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

configurations
{
myproject
liquibase
gwt ....
}
....

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

4 
buildscript {
    dependencies {
        classpath 'mysql:mysql-connector-java:5.1.x'
        }
}
5

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

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

Then I have defined my task:

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:

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

My new question is :

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

6

Well finally I have found the solution:

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 !!