Examples
Here is an example that shows the basic use of the package.
dado = require"dado"
db = dado.connect ("dbname", "username")
db.conn:execute ("drop table mytable") -- direct use of LuaSQL, since the command can fail
db:assertexec [[
create table mytable (
key integer,
field1 varchar(10),
field2 varchar(10),
adate date
)]] -- this command might not fail
data = {
{ field1 = "val1", field2 = "val21", },
{ field1 = "val2", field2 = "val22", },
{ field1 = "val3", field2 = "val32", },
}
-- Insert
for index, record in ipairs(data) do
record.key = index
db:insert ("mytable", record)
end
-- Update
r = data[2]
r.field1 = "field2"
db:update ("mytable", r, "key = "..r.key)
-- Retrieving rows one-by-one
for field1, field2 in db:select ("field1, field2", "mytable", "key >= 1", "order by key") do
print(field1, field2)
end
-- Retrieving the entire result set
rows = db:selectall ("field1, field2", "mytable", "key >=1", "order by key")
for i = 1, #rows do
print (data[i].field1, rows[i].field1)
print (data[i].field2, rows[i].field2)
end
-- Delete
for i = 1, #data do
db:delete ("mytable", data[i].key)
end
The following example shows how to use an already opened connection.
env = luasql.postgres()
conn = env:connect ("dbname", "username")
new_db = dado.wrap_connection (conn)
-- use new_db as any other Dado connection
The module dado.sql offers some facilities to assemble SQL statements as shown in the following example.
dado = require"dado"
sql = require"dado.sql"
db = dado.connect ("dbname", "username")
cond = sql.AND {
field1 = "val1",
field2 = sql.subselect ("field2", "mytable", "key = 1"),
}
for field1, field2 in db:select ("field1, field2", "mytable", cond)() do
print (field1, field2)
end