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 it can fail db:assertexec [[ create table mytable ( key integer, field1 varchar(10), field2 varchar(10), adate date )]] 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 can help in some situations 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