Using a Database

There are a few libraries to access databases with Lua out there, but only LuaSQL provides uniform access to different databases.

Here is a simple example of how to use LuaSQL:

#!/usr/bin/env cgilua.cgi
<%
-- 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>