Function
dbFetch
Runs a query and returns its result right away. One call instead of dbQuery, dbPoll and dbFree. Works with SELECT, INSERT, UPDATE and DELETE.
Syntax
table dbFetch(element connection, string query, mixed ...)Arguments
connectionelement- A handle from
dbConnect. querystring- SQL query with
?in place of values. ...mixed- Values for the
?placeholders, safely quoted.
Returns
A table of rows (each row is column = value). It also has rows.insertId (the inserted row's ID), rows.affectedRows (the number of changed rows) and rows.columns (the column names in order). On error false and the error message.
Example
local db = dbConnect("mysql", "glowna")
addCommandHandler("top", function(player)
async(function()
local rows, err = dbFetch(db, "SELECT nick, kasa FROM gracze ORDER BY kasa DESC LIMIT 5")
if not rows then
outputChatBox("Błąd bazy: " .. err, player)
return
end
for i, row in ipairs(rows) do
outputChatBox(i .. ". " .. row.nick .. " - $" .. row.kasa, player)
end
end)
end)
addCommandHandler("rejestracja", function(player)
async(function()
local result = dbFetch(db, "INSERT INTO gracze (nick, kasa) VALUES (?, ?)", getPlayerName(player), 500)
if result then
outputChatBox("Twoje konto ma numer " .. result.insertId, player)
end
end)
end)Notes
Easiest inside async. In an async(function() ... end) task the server does not wait for the database: only that task is paused while the game and other scripts keep running. Outside a task dbFetch works too, but the server stands still until the database answers. With fast SQLite that does not matter, with MySQL use async.
The insertId and affectedRows fields stay out of the way. ipairs, pairs and #rows see only the rows. A NULL value from the database arrives as false.
See also
dbFetchOneRuns a query and returns only the first row. Handy when you look for a single record, for example a player's account.dbFetchValueRuns a query and returns a single value: the first column of the first row. For counting (COUNT(*)), sums and single fields.asyncRuns a function as a task (coroutine). The code in the task runs immediately until its first wait (sleep,await,waitForEvent). Then it yields and the server and the game keep running. When the wait is over, the task continues from where it stopped. This lets you write steps spread over time (a countdown, a database query, an HTTP response, waiting for a player) one after another, without nested callbacks and timers.dbExecRuns a query that returns no result (INSERT, UPDATE, CREATE). Arguments replace the?placeholders.