The following code allows the easy read and write of data to any ADO supported database, you just need to use the correct connection string.
The included example can create a PC database, create a table, insert data and list it.
Requires: luacom
Code
Based on module from amsplugins (site closed in 2014).
-
--[[ @Title: Work with Database Tables @Author: Jane Taubman @Version: 1.0 @LastUpdated: 31 December 2011 @Description: Example code showing the reading and writing of a MDB (Access Database) from with in Lua. ]] ------------------------------------------------------- Start of ADO Module do local dblua_data = {} require("luacom") function DBCreate(file) local Catalog = luacom.CreateObject("ADOX.Catalog") Catalog:Create(connectionstr) end function DBOpen(connection_string) dblua_data.connection = luacom.CreateObject("ADODB.Connection") assert(dblua_data.connection) dblua_data.connection.ConnectionString = connection_string dblua_data.connection:Open() end function DBClose() dblua_data.connection:Close() dblua_data.connection = nil dblua_data.recordset = nil end function DBExec(statement) if statement == "%BEGIN" then dblua_data.connection:BeginTrans() return elseif statement == "%COMMIT" then dblua_data.connection:CommitTrans() return elseif statement == "%ROLLBACK" then dblua_data.connection:RollbackTrans() return end if dblua_data.recordset == nil then dblua_data.recordset = luacom.CreateObject("ADODB.RecordSet") elseif dblua_data.recordset.State ~= 0 then dblua_data.recordset:Close() end dblua_data.recordset:Open(statement, dblua_data.connection) end function DBRow() if dblua_data.recordset == nil then return nil elseif dblua_data.recordset.ActiveConnection == nil then return nil end if dblua_data.recordset.EOF == true then return nil end local row = {} local fields = dblua_data.recordset.Fields local i = 0 while i < fields.Count do local field = fields:Item(i) row[i] = field.Value row[field.Name] = field.Value i = i + 1 end dblua_data.recordset:MoveNext() return row end end ------------------------------------------------------- End of ADO Module ------------------------------------------------------- Check File Existance function file_exists(name) local f=io.open(name,"r") if f~=nil then io.close(f) return true else return false end end ------------------------------------------------------- Main Program Code dbfile = "d:\\temp\\test3.mdb" connectionstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="..dbfile if not(file_exists(dbfile)) then DBCreate(connectionstr) end DBOpen(connectionstr) DBExec("create table test (name char(30), phone char(20))") DBExec("insert into test values ('Bill Gates', '666-6666')") DBExec("insert into test values ('Paul Allen', '606-0606')") DBExec("insert into test values ('George Bush', '123-4567')") DBExec("select * from test where name <> 'Bill Gates'") t = DBRow() while t ~= nil do print(tostring(t.name).."\t"..tostring(t.phone)) t = DBRow() end DBExec("drop table test") DBClose() collectgarbage()
Last update: 16 Dec 2020