Using a Database

There is a few libraries to access databases with Lua out there, but only LuaSQLprovides uniform access to different databases.

Here is a simple example of how to use LuaSQL:

-- Initialization (1)
require"luasql.postgres"
local env = luasql.postgres ()
local conn = assert (env:connect ("lpg"))

-- Building SQL statement (2)
course_list = { "Music", "Literature", }
for i, course in ipairs (course_list) do
    course_list[i] = "'"..course.."'"
end
local c_list = table.concat (course_list, ",")
local stmt = [=[
    select a.id, a.name
        from alumn a inner join course c on (a.course_id = c.id)
        where c.name in (]=]..c_list..")"

-- SQL execution and error handling (3)
local cur, err = conn:execute (stmt)
if not cur then
    error (err.." SQL = [=["..stmt.."]=]")
end

-- Iteration loop (4)
local id, name = cur:fetch ()
while id do
    cgilua.put (string.format ("<tr><td>id = %%s<td>name = %%s</tr>", id, name))
    id, name = cur:fetch ()
end

However, it would be better to define an iterator in order to use it inside a LuaPage:

function lines (stmt)
    local cur = assert (conn:execute(stmt))
    return function ()
        return cur:fetch()
    end
end



<table>
  <%% for id, name in lines"select id, name from alumn" do %%>
  <tr>
    <td><%%= id %%>
    <td><%%= name %%>
  </tr>
  <%% end %%>
</table>