1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
|
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>LuaSQL: Database connectivity for the Lua programming language</title>
<link rel="stylesheet" href="doc.css" type="text/css"/>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
</head>
<body>
<div id="container">
<div id="product">
<div id="product_logo"><a href="http://www.keplerproject.org">
<img alt="LuaSQL logo" src="luasql.png"/>
</a></div>
<div id="product_name"><big><b>LuaSQL</b></big></div>
<div id="product_description">Database connectivity for the Lua programming language</div>
</div> <!-- id="product" -->
<div id="main">
<div id="navigation">
<h1>LuaSQL</h1>
<ul>
<li><a href="index.html">Home</a>
<ul>
<li><a href="index.html#overview">Overview</a></li>
<li><a href="index.html#status">Status</a></li>
<li><a href="index.html#download">Download</a></li>
<li><a href="index.html#credits">Credits</a></li>
<li><a href="index.html#contact">Contact us</a></li>
</ul>
</li>
<li><a href="manual.html">Manual</a>
<ul>
<li><a href="manual.html#introduction">Introduction</a></li>
<li><a href="manual.html#compiling">Compiling</a></li>
<li><a href="manual.html#installation">Installation</a></li>
<li><a href="manual.html#errors">Error handling</a></li>
<li><a href="manual.html#drivers">Drivers</a></li>
<li><a href="manual.html#environment_object">Environment</a></li>
<li><a href="manual.html#connection_object">Connection</a></li>
<li><a href="manual.html#cursor_object">Cursor</a></li>
<li><a href="manual.html#postgres_extensions">PostgreSQL</a></li>
<li><a href="manual.html#mysql_extensions">MySQL</a></li>
<li><a href="manual.html#oracle_extensions">Oracle</a></li>
<li><a href="manual.html#sqlite3_extensions">SQLite3</a></li>
</ul>
</li>
<li><strong>Examples</strong></li>
<li><a href="history.html">History</a></li>
<li><a href="http://github.com/keplerproject/luasql">Project</a>
<ul>
<li><a href="https://github.com/lunarmodules/luasql/issues">Bug Tracker</a></li>
</ul>
</li>
<li><a href="license.html">License</a></li>
</ul>
</div> <!-- id="navigation" -->
<div id="content">
<h2><a name="examples"></a>Examples</h2>
<p>Here is an example of the basic use of the library.
After that, another example shows how to create an
<a href="#iterator_example">iterator</a> over the result of a SELECT
query.</p>
<h3><a name="basic_use"></a>Basic use</h3>
<pre class="example">
-- load driver
local driver = require "luasql.postgres"
-- create environment object
env = assert (driver.postgres())
-- connect to data source
con = assert (env:connect("luasql-test"))
-- reset our table
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, the rows will be indexed by field names
row = cur:fetch ({}, "a")
while row do
print(string.format("Name: %s, E-mail: %s", row.name, row.email))
-- reusing the table of results
row = cur:fetch (row, "a")
end
-- close everything
cur:close() -- already closed because all the result set was consumed
con:close()
env:close()
</pre>
<p>And the output of this script should be:</p>
<pre class="example">
Name: Jose das Couves, E-mail: jose@couves.com
Name: Manoel Joaquim, E-mail: manoel.joaquim@cafundo.com
Name: Maria das Dores, E-mail: maria@dores.com
</pre>
<h3><a name="iterator_example"></a>Iterator</h3>
<p>It may be useful to offer an iterator for the resulting rows:</p>
<pre class="example">
function rows (connection, sql_statement)
local cursor = assert (connection:execute (sql_statement))
return function ()
return cursor:fetch()
end
end
</pre>
<p>Here is how the iterator is used:</p>
<pre class="example">
env = assert (require"luasql.mysql".mysql())
con = assert (env:connect"my_db")
for id, name, address in rows (con, "select * from contacts") do
print (string.format ("%s: %s", name, address))
end
</pre>
<p>Obviously, the code above only works if there is a table called contacts with the columns id, name and address in this order. At the end of the loop the cursor will be automatically closed by the driver.</p>
<h3><a name="To_be_closed"></a>To-be-closed Objects</h3>
<p>
<strong style="color: red;">Compatibility Note:</strong>
To-be-closed variables is a feature introduced in Lua 5.4. Therefore, this functionality can only be used if LuaSQL (version > 2.6) is compiled against Lua version 5.4 or higher.
</p>
<pre class="example">
function getName(db, id)
-- This code requires Lua 5.4 or higher due to the use of to-be-closed variables.
local cur <close> = db:execute("SELECT name FROM contacts WHERE id = " .. id)
return cur:fetch()
end
</pre>
</div> <!-- id="content" -->
</div> <!-- id="main" -->
<div id="about">
<p><a href="http://validator.w3.org/check?uri=referer">Valid XHTML 1.0!</a></p>
<p><small>$Id: examples.html,v 1.16 2008/06/11 00:26:13 jasonsantos Exp $</small></p>
</div> <!-- id="about" -->
</div> <!-- id="container" -->
</body>
</html>
|
