Hi,
I’m doing some work with Corona and SQLite. Having used all the examples on this site and ones I can find on the internet I cannot find an example to select a single record / row (i.e. select name,age,title from tbuser where id=1 ) ?
Could anyone help with an example or point me in the direction of another related forum? [import]uid: 10893 topic_id: 3520 reply_id: 303520[/import]
Fetching a Single Row
Often you only need the first row that a SELECT statement returns. For example counting the number of rows in a table and so on. Using db:rows() and related for such a task is a mess, instead you should use db:first_row() and related for this task:
row = db:first_irow(“SELECT count(*) FROM test”)
print(row[1])
row = db:first_row(“SELECT count(*) AS count FROM test”)
print(row.count)
count = db:first_cols(“SELECT count(*) FROM test”)
print(count)
There doesn’t exist a caveat in db:first_cols() like in db:cols(), instead always exactly the columns of the first row from the SELECT statement are returned.
[import]uid: 10893 topic_id: 3520 reply_id: 10696[/import]
ok I just got round to testing this and it does work - HELP!
stmt = db:prepare(“SELECT Title,Site,Due FROM tbHelpDeskJobs where id=”…id)
row = stmt:first_row()
DataObjects.lua:91: attempt to call method ‘first_row’ (a nil value)
stack traceback:
[C]: in function ‘first_row’… [import]uid: 10893 topic_id: 3520 reply_id: 11155[/import]
did you get any joy with this?
I am struggling to get anything other than a 1 or a 0 to print with variations of the following
tester = db2:exec[[SELECT column FROM table WHERE id = ‘number’]]
print(tester)
cheers,
Rob [import]uid: 2131 topic_id: 3520 reply_id: 13208[/import]
I’m having the exact same problem, and it is driving me insane. If I execute the following in SQLiteManager it returns the number that is in the database:
SELECT score FROM scores WHERE portal = '1' and level = '1'Returns “2185”.
But when I run the following code in Corona:
[code]oldScore = db:exec( “SELECT score FROM scores WHERE portal = ‘1’ and level = ‘1’” )
print( oldScore )[/code]
Returns “0”
What am I doing wrong? [import]uid: 7227 topic_id: 3520 reply_id: 16082[/import]
don’t know if this is what your after but,
This will get you a single entry
dbase is table name, lim2 is how many records you want ( so that would be 1)
and lim is where to start ie what row
doing the statement first allows you to use vars, couldn’t get it to work directly
simple3 = 'SELECT \* FROM dbase LIMIT '..lim2..' OFFSET '..lim
for col1,col2 in db2:urows( simple3 ) do
print(col1)
print(col2
end [import]uid: 2131 topic_id: 3520 reply_id: 16102[/import]
db:exec runs sql code, and returns 0 if it succeeds.
db:nrows is an iterator that will return a table with named columns for each item.
so for one item you’d do something like this.
[lua]for a in db:nrows(‘SELECT timestamp FROM table WHERE something= ‘1’ LIMIT ‘1’’)
do
output = a[timestamp]
end[/lua] [import]uid: 3 topic_id: 3520 reply_id: 16159[/import]
Sean, remember that SQlite restart bug we spoke about? Seems to be limited to the simulator, not been able to replicate it on device yet. [import]uid: 13089 topic_id: 3520 reply_id: 16504[/import]
Hi ,
Is there any other way that…without for loop we can get the single record from sql statement?
Thanks,
Krunal [import]uid: 33757 topic_id: 3520 reply_id: 22929[/import]
What have you tried that hasn’t worked? Maybe it can serve as a starting point for others to try out. [import]uid: 8045 topic_id: 3520 reply_id: 22931[/import]
Hi Luna,
Thankx for reply.
I am tried with,
for row in db:nrows(“SELECT * FROM Employee where Id =”…id) do
detailScreenText.text = “ID = “… row.Id…”\nName :”…row.Name
end
Its working fine for me.
but i want to do somethings like,
row = db:[function name](“SELECT * FROM Employee where Id =”…id)
so after that i can use row.Id easily…
Now you can understand what i want.
Thanks.
Krunal
[import]uid: 33757 topic_id: 3520 reply_id: 22932[/import]
@Krunal try this link:
http://luasqlite.luaforge.net/lsqlite3.html#examples
There are samples there that might do what you want. [import]uid: 8045 topic_id: 3520 reply_id: 22933[/import]
@ Luna:
I knw about this but there i hv describe this b’cas u told me that what u want so thats y i hv just write this db:[function]…
now u getting…what i want… [import]uid: 33757 topic_id: 3520 reply_id: 22934[/import]
@ seanh
Sean I tried your for loop but I can not get this to work:
local db = getDatabase()
local stmt = db:prepare("SELECT count(id) FROM ? LIMIT 1")
stmt:bind( 1, inTable.tableName )
for row in stmt:rows() do
print(row[1])
end
db:close() It is giving me the error:
database.lua:60: attempt to index global 'stmt' (a nil value) Line 60 maps to line six above.
Any idea how stmt gets to nil? 2 lines above I don’t get an error using stmt for the bind [import]uid: 12090 topic_id: 3520 reply_id: 42822[/import]
just coming back to the first post…
i really don’t know how that should work just with a single row
row = db:first_row(“SELECT count(*) AS count FROM test”)
print(row.count)
just a single command, (no loop) and a simple result (amount of rows)
but it does not work…i also looked into the sqlite documentation,
there not even a command “first_row” … so what ?
till now i still have to work with
for row in mdfunc.db:nrows(“SELECT count(*) AS count FROM data LIMIT 1”) do
print (row.count)
end
till now its still the fastest solution I found
chris
[import]uid: 4795 topic_id: 3520 reply_id: 54765[/import]
Hi all,
Any solutions solved fro this Sql nil value problem.
Me i have been using the WHERE
for row in db:nrows(“SELECT * FROM database WHERE Num=”…Var) do
Where Var is a Random number derived from math.random.
Now i am having a problem where a nil value is appearing SOMETIMES - have checked the database and the field has a value.
Only thing i can think of is that the result gets passed through a couple of variables (a = b, c = b, Display.newText(c, …) - just thought i would get this question out before i try reducing the transfers, or start coding a work around test loop ie if Nil then redo database call.
Thanks
tone
[import]uid: 199068 topic_id: 3520 reply_id: 134582[/import]
Hi all,
Any solutions solved fro this Sql nil value problem.
Me i have been using the WHERE
for row in db:nrows(“SELECT * FROM database WHERE Num=”…Var) do
Where Var is a Random number derived from math.random.
Now i am having a problem where a nil value is appearing SOMETIMES - have checked the database and the field has a value.
Only thing i can think of is that the result gets passed through a couple of variables (a = b, c = b, Display.newText(c, …) - just thought i would get this question out before i try reducing the transfers, or start coding a work around test loop ie if Nil then redo database call.
Thanks
tone
[import]uid: 199068 topic_id: 3520 reply_id: 134582[/import]
Please check out my stack overflow answer related to this post:
http://stackoverflow.com/questions/10343255/sqlite-how-do-i-get-a-one-row-result-back-luasqlite3
Cheers,
Rob
Please check out my stack overflow answer related to this post:
http://stackoverflow.com/questions/10343255/sqlite-how-do-i-get-a-one-row-result-back-luasqlite3
Cheers,
Rob
Great answer Rob and really helpful. Was trying to be a bit too “smart” for my own good.
Simple works !