Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migrated to Confluence 5.3



Be sure that derby.jar is either:

  • in the groovy lib folder
  • explicitly set in the classpath (ie. set CLASSPATH=%CLASSPATH%;/path/to/derby.jar)
  • loaded at runtime (ie. this.class.classLoader.rootLoader.addURL(new URL("file:derby.jar")) )

If you are using JDK 1.5 or greater, a LinkageError will occur. Removing the mx4j-3.0.2.jar library from %GROOVY_HOME%\lib will make it work again, but a better solution would be preferable.
Look at http://jira.codehaus.org/browse/GROOVY-2303 for further information, or ask on the user@groovy.codehaus.org.

This will create derby.log and derbyDB folder in the folder where the script is.

If something goes wrong, simply remove derbyDB folder, and everything will be clean for next run. 

Code Block
//uncomment the next line to load the derby jar at runtime :
//set CLASSPATH=%CLASSPATH%;d:\\path\\to\\derby.jar

import groovy.sql.*
import java.sql.*

protocol = 'jdbc:derby:'
def props = new Properties()
props.put('user', 'user1')
props.put('password', 'user1')

def sql = Sql.newInstance("${protocol}derbyDB;create=true", props)

/* Creating table, adding few lines, updating one */
sql.execute('create table people(id int, name varchar(40), second_name varchar(40), phone varchar(30), email varchar(50))')

println("Created table 'people'")

sql.execute("insert into people values (1,'John', 'Doe', '123456','johndoe@company.com')")
sql.execute("insert into people values (2,'Bill', 'Brown', '324235','billbrown@company.com')")
sql.execute("insert into people values (3,'Jack', 'Daniels', '443323','jackdaniels@company.com')")

println('Inserted people')

sql.execute("update people set phone='443322', second_name='Daniel''s'where id=3")

println('Updated person')

/* Simple query */
def rows = sql.rows('SELECT * FROM people ORDER BY id')
rows.each {println it}

/* Dropping table 'people' */
sql.execute('drop table people')
println ("Table 'people' dropped")

try{
DriverManager.getConnection('jdbc:derby:;shutdown=true')
}
catch (SQLException se){
   gotSQLExc = true
}

println('Finish!')