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

println("START");
def props = new Properties();
props.put("user", "app");
props.put("password", "app");
try{
def sql = Sql.newInstance("jdbc:derby://localhost:1527/test", props);

sql.execute("drop table people");


/* 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")
}catch(Exception e){
e.printStackTrace();
}
try{
DriverManager.getConnection("jdbc:derby://localhost:1527/test;shutdown=true")
}
catch (SQLException se){
gotSQLExc = true
}

println("Finish!")