db = {}

local db_name = "test"
local db_user = "root"
local db_pass = ""
local db_host = "localhost"

function logerror(err)
    if err then
        file = io.open("error.log", "a")
        file:write(string.format("%s %s\n", os.date("%Y/%m/%d %H:%M:%S"), err))
        file:close()
    end
end

function log2file(str)
    file = io.open("test.log", "a")
    file:write(str .. "\n")
    file:close()
end

-- load luasql.dll
local luasql = require("luasql.mysql")

-- set connection to database
db.env = luasql.mysql()
db.con, err = db.env:connect(db_name, db_user, db_pass, db_host)
logerror(err)

-- some connection settings
sql = "SET NAMES 'utf8'"
res, err = db.con:execute(sql)
logerror(err)

sql = "SET COLLATION_CONNECTION='utf8_general_ci'"
res, err = db.con:execute(sql)
logerror(err)

-- try to delete table
sql = "DROP TABLE people"
res, err = db.con:execute(sql)
logerror(err)

-- try to create the new table
sql = "CREATE TABLE people(name varchar(50), email varchar(50))"
res, err = db.con:execute(sql)
logerror(err)


-- add a few elements
list = {
    { name="Jose das Couves", email="jose@couves.com", },
    { name="Manoel Joaquim", email="manoel.joaquim@cafundo.com", },
    { name="Maria das Dores", email="maria@dores.com", },
}
for i, p in pairs (list) do
    sql = string.format("INSERT INTO people VALUES ('%s', '%s')", p.name, p.email)
    res, err = db.con:execute(sql)
    logerror(err)
    log2file("inserted " .. res)
end

-- read added elements from database
log2file("reading from database")

-- retrieve a cursor
sql = "SELECT name, email from people"
cur, err = db.con:execute(sql)
logerror(err)

-- write all rows in the log
row = cur:fetch ({}, "a")   -- the rows will be indexed by field names
while row do
    log2file(string.format("Name: %s, E-mail: %s", row.name, row.email))
    row = cur:fetch (row, "a")  -- reusing the table of results
end

-- close cursor
cur:close()

-- close connection and environment
db.con:close()
db.env:close()

function prompt_read(prompt)
    print(prompt)
    return io.read()
end

prompt_read("Press any key")