Embedded Derby DB examples

//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 + ""${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!"')