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?

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.

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'.....

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

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:

1
2
3

oldScore = db:exec( "SELECT score FROM scores WHERE portal = '1' and level = '1'" )
 
print( oldScore )

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

1 2 3 4 5

simple3 = 'SELECT * FROM dbase LIMIT '..lim2..' OFFSET '..lim for col1,col2 in db2:urows( simple3 ) do print(col1) print(col2 end

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.

1 2 3 4

for a in db:nrows('SELECT timestamp FROM table WHERE something= '1' LIMIT '1'') do         output = a[timestamp] end

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.

Hi ,

Is there any other way that..without for loop we can get the single record from sql statement?

Thanks,
Krunal

@Krunal

What have you tried that hasn't worked? Maybe it can serve as a starting point for others to try out.

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

@Krunal try this link:
http://luasqlite.luaforge.net/lsqlite3.html#examples

There are samples there that might do what you want.

@ 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...

@ seanh
Sean I tried your for loop but I can not get this to work:

1 2 3 4 5 6 7 8 9 10

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()

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

views:2223 update：2011/10/5 8:48:05