Fetching a single sqlite record

Solar2D General Questions/Discussion
#1

I’m looking at using database for a few things,and wondering if it’s possible to fetch just a single record from a database?

I’m aware I can use “LIMIT 1” or I can do something like:-

for row in db:nrows("SELECT COUNT(\*) as count FROM levels") do  
 local userCnt = row.count  
end

But that’s not ideal to get just a single row. I’ve looked up different docs which mention using db:first_row but that doesn’t appear to work, same with first_irow.

Anyone know a better way to do this?

Thanks in advance for any advice anyone has :slight_smile: [import]uid: 133056 topic_id: 24565 reply_id: 324565[/import]

#2

I tested this with urows, but I assume this should work with nrows as well.

The function db:urows(sql) returns two parameters, the first one being the iterator function (for more info on iterators, have a look at PIL 7.1), the second return value is a parameter that should be passed to that iterator function (an SQLite Virtual Machine). In a for loop, this is taken care of automatically (that is, for each iteration, the second return value, if present, is passed to the first return value, the iterator function).

However, that does not mean that an iterator can only be used in a for loop. The following two snippets, for instance, do the same thing:
[lua]for ret1, ret2 in db:urows(“SELECT ret1, ret2 FROM table”)
print(ret1, ret2)
end[/lua]

and

[lua]while true do
local func, param = db:urows(“SELECT ret1, ret2 FROM table”)
local ret1, ret2 = func(param)
if not ret1 then break end
print(ret1, ret2)
end[/lua]

In such a loop situation, the former is naturally the preferred method. However, if we take the loop out of the latter snippet, we can fetch one result:

[lua]local func, param = db:urows(“SELECT ret1, ret2 FROM table”)
print(func(param))
end[/lua]

Now suppose our table looks like this:

| ret1 | ret2 |
|------|------|
| 12 | 34 |
| 56 | 78 |

Then our piece of code would print 12 34, as only the first row was selected.

So there you have your single row selection! [import]uid: 141607 topic_id: 24565 reply_id: 104422[/import]