@Grab('org.hsqldb:hsqldb:2.3.2')
@GrabConfig(systemClassLoader=true)

import groovy.sql.Sql

url = "jdbc:hsqldb:file:/tmp/testdb/testdb"
user = 'test'
password = 'test'
driver = 'org.hsqldb.jdbcDriver'

sql = Sql.newInstance(url, user, password, driver)

// Drop existing Table
sql.execute '''
  DROP TABLE people
'''

// Create DB if not exist
sql.execute '''
  CREATE TABLE people (
    id          INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1  INCREMENT BY 1),
    firstname   VARCHAR(64),
    lastname    VARCHAR(64)
  );
'''

sql.execute("INSERT INTO people (firstname,lastname) VALUES(:firstname, :lastname)", [firstname: "Tom", lastname: "Tom"])
sql.execute("INSERT INTO people (firstname,lastname) VALUES(:firstname, :lastname)", [firstname: "Tim", lastname: "Tim"])


query = "SELECT * FROM people"

sql.eachRow(query) { row ->
  println "$row.id - ${row.firstname}::$row.lastname"
}

sql.close()