Copy Page

You are not logged in. Any changes you make will be marked as anonymous. You may want to Log In if you already have an account. You can also Sign Up for a new account.

Be sure that derby.jar is either:
<ul class="alternate">
	<li>in the groovy lib folder</li>
	<li>explicitly set in the classpath (ie. set CLASSPATH=%CLASSPATH%;/path/to/derby.jar)</li>
	<li>loaded at runtime (ie. this.class.classLoader.rootLoader.addURL(new URL(&quot;file:derby.jar&quot;)) )</li>
</ul>

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 <a href="http://jira.codehaus.org/browse/GROOVY-2303">http://jira.codehaus.org/browse/GROOVY-2303</a> 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. 
<table class="wysiwyg-macro" data-macro-name="code" style="background-image: url(/plugins/servlet/confluence/placeholder/macro-heading?definition=e2NvZGV9&amp;locale=en_GB&amp;version=2); background-repeat: no-repeat;" data-macro-body-type="PLAIN_TEXT"><tr><td class="wysiwyg-macro-body"><pre>
//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!')
</pre></td></tr></table>