• FH versions: V5, V6, and V7
• Category: External Databases 

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()