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