Lua-Sqlite3 get "rowid"

Question

When I run the code:

row = db:first_row("SELECT MIN(rowid) from table")
local minID = row.rowid

row = db:first_row("SELECT MAX(rowid) from table")
local maxID = row.rowid

I get an error saying:

Runtime error: ...\db_test\main.lua:33: attempt to call method 'first_row' (a nil value)
stack traceback:
    [C]: in function 'first_row'
    ...\main.lua:33: in main chunk

That exact same SQL query works in Python on the same database.

From the sqlite documentation

"ROWIDs and the INTEGER PRIMARY KEY

Every row of every SQLite table has a 64-bit signed integer key that uniquely identifies the row within its table. This integer is usually called the "rowid". The rowid value can be accessed using one of the special case-independent names "rowid", "oid", or "rowid" in place of a column name."

For anyone interested, the working code in my case is:

local minId
local maxId
for row in db:nrows("SELECT MIN(rowid) AS `rowmin` FROM " .. table) do
    minId = row.rowmin
end
for row in db:nrows("SELECT MAX(rowid) AS `rowmax` FROM " .. table) do
    maxId = row.rowmax
end

hjpotter92 hjpotter92 · Accepted Answer · 2013-03-28T10:11:08

If you are using the lua-sqlite3 wrapper, you'd need to iterate using db:rows() method and not the first_row. The call will be:

row = db:rows("SELECT MIN(rowid) AS `rowmin`, MAX(rowid) AS `rowmax` FROM table")
local minID, maxID = row.rowmin, row.rowmax

Based on @CL 's suggestion in comments:

row = db:rows("SELECT MIN(rowid) AS `rowmin` FROM table")
local minID = row.rowmin
row = db:rows("SELECT MAX(rowid) AS `rowmax` FROM table")
local maxID = row.rowmax

Lua-Sqlite3 get "rowid"

For anyone interested, the working code in my case is:

1 Answers