SO . . .
sqlite in Corona is great! The documentation is not so great! Please help me with what should be a very simple answer.
This command in SQL returns either a 0 or 1. That’s it:
SELECT EXISTS (SELECT * FROM myFavorites WHERE id=8)
How do I get the return value? My limited lua knowledge told me to set up a function and grab it with “event.response” but that wasn’t the answer.
When I run the above command, how do I return my 1 or 0?
Hi, I’m dealing with the same issue, did you find a solution? I want to check if a value exists for a column, but my function for select is always returning 0.
require "sqlite3" local path = system.pathForFile( "data2.db", system.DocumentsDirectory) local db = sqlite3.open( path ) local tablesetup = [[CREATE TABLE IF NOT EXISTS test (name);]] db:exec( tablesetup ) local people = {'John', 'Mark', 'Fred', 'Sam'} for i=1,#people do local q = [[INSERT INTO test VALUES (']] .. people[i] .. [[');]] db:exec( q ) end for row in db:nrows("SELECT \* FROM test") do print(row.name) end local result = db:exec("SELECT EXISTS(SELECT 1 FROM test WHERE name='John');") print("Select result is " .. result) local function onSystemEvent( event ) if event.type == "applicationExit" then if db and db:isopen() then db:close() end end end Runtime:addEventListener( "system", onSystemEvent ) This is my solution for my “friendExists” function, it look kind of crappy but works. Maybe there is something better out there?
[lua]
function Db:friendExists(friendName, ownerID)
local query = “SELECT id FROM friends WHERE UPPER(name) = UPPER(’” … friendName … “’) AND ownerid = " … ownerID … " LIMIT 1;”
for row in self.db:nrows(query) do
return true
end
return false
end
[/lua]
Hi, I’m dealing with the same issue, did you find a solution? I want to check if a value exists for a column, but my function for select is always returning 0.
require "sqlite3" local path = system.pathForFile( "data2.db", system.DocumentsDirectory) local db = sqlite3.open( path ) local tablesetup = [[CREATE TABLE IF NOT EXISTS test (name);]] db:exec( tablesetup ) local people = {'John', 'Mark', 'Fred', 'Sam'} for i=1,#people do local q = [[INSERT INTO test VALUES (']] .. people[i] .. [[');]] db:exec( q ) end for row in db:nrows("SELECT \* FROM test") do print(row.name) end local result = db:exec("SELECT EXISTS(SELECT 1 FROM test WHERE name='John');") print("Select result is " .. result) local function onSystemEvent( event ) if event.type == "applicationExit" then if db and db:isopen() then db:close() end end end Runtime:addEventListener( "system", onSystemEvent ) This is my solution for my “friendExists” function, it look kind of crappy but works. Maybe there is something better out there?
[lua]
function Db:friendExists(friendName, ownerID)
local query = “SELECT id FROM friends WHERE UPPER(name) = UPPER(’” … friendName … “’) AND ownerid = " … ownerID … " LIMIT 1;”
for row in self.db:nrows(query) do
return true
end
return false
end
[/lua]