Differences

This shows you the differences between two versions of the page.

--- lua:db:exec [2016/02/07 21:26] – created vodur
+++ lua:db:exec [2016/02/08 05:37] (current) – vodur
@@ Line 1: / Line 1: @@
-db.exec( statement )
+<font 18px/inherit;;inherit;;inherit>** db.exec( statement ) **</font>
 Execute sql statement.
+If statement is a query, return value is a table with the query results.
+See also [[:lua:db:escape|db.escape()]].
+===== Examples: =====
+Check if a table exists:
+<code lua>
+if #db.exec[[SELECT name FROM sqlite_master WHERE type='table' AND name='grraka_animals']] <1 then
+  -- table doesn't exist, do something here
+end
+</code>
+\\
+Create a table:
+<code lua>
+db.exec[[CREATE TABLE grraka_animals(
+                     animal_name TEXT,
+                     location TEXT)]]
+</code>
+Insert values into table:
+<code lua>
+db.exec[[INSERT INTO grraka_animals( animal_name, location ) VALUES( 'Dog', 'land')]]
+</code>
+Bulk insert using transaction:
+<code lua>
+local animals=
+{
+{animal_name="Aardvark",location="land" },
+{animal_name="Albatross",location="air" },
+{animal_name="Alligator",location="water" },
+{animal_name="Alpaca",location="land" },
+{animal_name="Ant",location="land" }
+}
+db.exec[[BEGIN]]
+for _,entry in pairs(animals) do
+    db.exec( ([[INSERT INTO grraka_animals(animal_name, location)
+                      VALUES( '%s', '%s')]]):format( db.escape(entry.animal_name), db.escape(entry.location))
+end
+db.exec[[END]]
+</code>
+Run a query and process the results:
+<code lua>
+local result=db.exec[[SELECT animal_name, location FROM grraka_animals ORDER BY animal_name]]
+for _,row in result do
+    say(row.animal_name.." lives in "..row.location)
+end
+</code>
+See sqlite3 general documentation for further reference on statements.