Using LuaSQL for ODBC Connections

Using ODBC drivers to interact with databases such as MySQL

Installing a MySQL server (optional, when you don't already have one)

Install a mysql server on your machine or on a network.
Run "MySQL 5.5 Command Line Client" from the start menu.
Create a database by typing "CREATE DATABASE myDataBase;" and pressing enter.

Installing and setting up the ODBC driver.

Download and install the MySQL ODBC driver from
Run "Data Sources (ODBC)" from the start menu.
Go to the "System DSN" tab.
Click the "Add" button.
Enter the connection settings for your MySQL database (make sure you made a database first).
The "Data Source Name" is the name you must use when connecting to this database using odbc in lua.

Example code:

env = assert(luasql.odbc())
con = assert(env:connect("My_ODBC_Data_Source_Name"))

res = con:execute("DROP TABLE people")
res = assert (con:execute("CREATE TABLE people(name  varchar(50), email varchar(50))")

-- add a few elements
list = {
  { name="Jose das Couves", email="jose@couves.com" },
  { name="Manoel Joaquim", email="manoel.joaquim@cafundo.com" },
  { name="Maria das Dores", email="maria@dores.com" },
}

for i, p in pairs (list) do
  res = assert (con:execute(string.format("INSERT INTO people VALUES ('%s', '%s')", p.name, p.email)))
end

-- retrieve a cursor
cur = assert (con:execute("SELECT name, email from people"))

-- print all rows
row = cur:fetch ({}, "a")-- the rows will be indexed by field names
while row do
  print(string.format("Name: %s, E-mail: %s", row.name, row.email))
  row = cur:fetch (row, "a")-- reusing the table of results
end

-- close everything
cur:close()
con:close()
env:close()

You can confirm that this wrote data to the MySQL database by opening the "MySQL 5.5 Command Line Client" from the start menu and entering

mysql> use myDataBaseName;
mysql> SELECT * FROM people;

This should show the contents written to the database from the lua script.