Dear Corona Labs SQLite Engineer,
I have a problem with the way Corona SDK implements its SQLite API calls. There is no return feedback on whether your action succeeded or not. I can check most of the actions afterwards with a second SQLite action to see if the first succeeded but this adds unnecessary overhead to the app. Is it at all possible to improve the way SQLite API works so that we get return results or is this an underlying OS shortcoming?
Thanks for the clarification.
I agree with @ksan. We are heavily using sqlite in our app, and it is hard to build robust code without knowing upfront if the call was successful or not,.
According to the docs, we use the LuaSQLite bindings documented here: http://luasqlite.luaforge.net/lsqlite3.html
and according to that doc for db:exec(), it should return a numeric code that indicates the results of that query “The
function returns sqlite3.OK on success or else a numerical error code
(see Numerical error and result codes).” and sqllite3.OK = 0 apparently.
Have you tried to see if our db:exec() returns anything?
Rob
Yes!!! This works. Thank you very much for this information. This definitely should go in the API docs and tutorials.
I managed to verify that a completed db:exec transaction returns 0
I also managed to create an error situation and got a return of 1. Life is good.
That works ok for me, example:
[lua]local query = [[UPDATE names SET status = 0 WHERE id = 2]]
local result = self.db:exec( query )
if result == 1 then print('Something went wrong with query ', query) end
[/lua]
Its not great because you get no info what actually went wrong. So I copy paste the query into my sqlite manager and test it there to get some feedback.
Sorry didn’t see your reply 
Have you found a better way to return one row? I’m doing it like this
[lua]function Db:getGameRow(id)
local query = “SELECT * FROM games WHERE id = '” … id … “’ LIMIT 1”
for row in self.db:nrows(query) do
return (row)
end
end
[/lua]
Thats what the Love it, like it but and hate it buttons are for!
I went ahead and hit Like it But for you.
Rob
Rob, I think this is a bigger issue than Love It/Hate It can handle. I can’t hate it if I don’t know its missing in the first place. I think its a case of how deep the documenters should be thinking. This capability should also be ideally demonstrated in the tutorials as well. But thanks for the info and hitting the button. Most appreciated.
No worries. I think your method is as good as any. Looks like a nice tight function.
Just looked into the codes a little more and two possible return codes puzzle me a little. These are listed under error states but have positive sounding descriptions. Any ideas?
return 100 = “ROW” – sqlite3_step() has another row ready
return 101 = “DONE” – sqlite3_step() has finished executing
@ksan, those are return when you use sqlite3_step function.
The sqlite3_step functionn will return “done” when all rows have been iterated (query finish), as oppose as “row” when a row is returned successfully.
However the way we are iterating row in Corona, I don’t think we will ever receive those return cod, but I may be wrong.
@nmichaud, thank you very much for your input. I was looking at those other sqlite3 functions and wondering how much of it is exposed to us in Corona SDK. One of these days I’m going to run through some of them and try. Some interesting looking functions in there.
I think they should all be working and when it comes to errors some functions give you more than just a return code.
for example:
sqlite3.open
In case of an error, the function returns nil, an error code and an error message.
stmt:step
if result is sqlite3.ERROR: a run-time error (such as a constraint violation) has occurred. stmt:step() should not be called again. More information may be found by calling db:errmsg(). A more specific error code (can be obtained by calling stmt:reset().
most other functions will return an error code as result.
When you get an error code you can always call:
db:errmsg()
db:error_message()
Returns a string that contains an error message for the most recent failed call associated with database db.
Thank you @primoz. Very insightful.
I tried and found that db:errmsg() & db:error_message() seems to be returning the same text and they are very informative. Not sure if these will always return the same text though.
To @jonjonsson’s point earlier on… Not knowing what went wrong makes it hard to fix things nbut these commands help a lot. For example, in my test case I try to insert into a db which does not accept duplicates in primary key.
On the second attempt to insert a given record I get a return of 19. If you look it up on the link Rob kindly provided this will tell you Constraint and another link will provide further info but not say anything new : “Abort due to constraint violation”. Then the db:error_message() will tell you : “PRIMARY KEY must be unique”. BINGO!!!
I am very happy and thankful for the information shared here. It will help me build much more robust database apps.
Just to share. Before I query the SQLlite, i always check whether the DB table is there or not. If not, it will create it first ( would want no results than an error)
local majorTableSetup = [[CREATE TABLE IF NOT EXISTS]] ...... local result = db:exec( majorTableSetup ) -- Check the 'result' here if needed. if result ~= 0 then -- Error in creating the table. end local query = "SELECT " ..... local returnData = {} for row in db:nrows(query) do -- create table at next available array index table.insert(returnData, row.major) endI agree with @ksan. We are heavily using sqlite in our app, and it is hard to build robust code without knowing upfront if the call was successful or not,.
According to the docs, we use the LuaSQLite bindings documented here: http://luasqlite.luaforge.net/lsqlite3.html
and according to that doc for db:exec(), it should return a numeric code that indicates the results of that query “The
function returns sqlite3.OK on success or else a numerical error code
(see Numerical error and result codes).” and sqllite3.OK = 0 apparently.
Have you tried to see if our db:exec() returns anything?
Rob
Yes!!! This works. Thank you very much for this information. This definitely should go in the API docs and tutorials.
I managed to verify that a completed db:exec transaction returns 0
I also managed to create an error situation and got a return of 1. Life is good.
That works ok for me, example:
[lua]local query = [[UPDATE names SET status = 0 WHERE id = 2]]
local result = self.db:exec( query )
if result == 1 then print('Something went wrong with query ', query) end
[/lua]
Its not great because you get no info what actually went wrong. So I copy paste the query into my sqlite manager and test it there to get some feedback.
Sorry didn’t see your reply
Have you found a better way to return one row? I’m doing it like this
[lua]function Db:getGameRow(id)
local query = “SELECT * FROM games WHERE id = '” … id … “’ LIMIT 1”
for row in self.db:nrows(query) do
return (row)
end
end
[/lua]