LUA/sqlite3/EXISTS

For Developers
1

I am looking for a one-liner to detect the existance of a row. Contents not required. I tried variations of this one:
result=con:execute("SELECT EXISTS(SELECT 1 from table_articles where name = 'teddybear')")

print (result)
SQLite3 cursor (0x77cd2780)
with no direct result. I always got a reference to the cursor. Instead, I would like to have i.g. true/false. Any ideas ?

3

This is pretty much the universal behaviour of all current SQL database interfaces when an SQL statement is executed regardless of language in use. If your database interface (LuaSQL?) doesn't support alternative mechanisms (some do, most don't) you have to use the cursor machinery and SQL aggregate functions to achieve what you need - a calling language wrapper function then simplifies use.

The usual SQL aggregate function for a lot of this type of usage is count(), but quite often (as in your example) it is simpler just to select the target rows and count them in your calling routine, e.g. something like

local cursor = con:execute("SELECT 1 from table_articles where name = 'teddybear'")
local row_exists = False
while cursor:fetch() do
    row_exists = True
    break
end

NB: untested pseudo code - I'm not fluent in Lua

4

Thank you. However, that is, what I am actually doing. But it needs 2 calls to sqlite3. I thought, it might be possible just to use only one request. "counter" does not work for this requirement, either. Bad luck.

5

Many SQL interface libraries have a way of reporting the number of rows affected (or returned) by an SQL statement which would avoid the need to start fetching the rows - e.g. Python DBAPI compatible implementations of cursors have a rowcount attribute for this - but I didn't see anything like this in a quick look at the LuaSQL docs.