Re: lsqlite3

[Doug Currie <doug.currie@...>]

On Thu, Sep 8, 2016 at 8:26 PM, Leinen, Rick <RLeinen@leviton.com> wrote:

 I see how you can print the values in a database table using db:nrows in a For statement, but what if I want just a single entry?

Using a for loop with db:nrows takes care of the prepare, step, and finalize that are necessary. So, it is usually the easiest approach. Why are you not interested in all the results of the query? Perhaps you should use 'limit 1' if you only want one result.

 

This one works, but doesn’t seem very elegant:

 

[...]

 

This one doesn’t:

 

local sqlite3 = require("lsqlite3")

local db = sqlite3.open("test.db");

 

db:exec [[

  DROP TABLE if exists numbers;

  CREATE TABLE numbers(num1,num2,str);

  INSERT INTO numbers VALUES(1,11,"ABC");

  INSERT INTO numbers VALUES(2,22,"DEF");

  INSERT INTO numbers VALUES(3,33,"UVW");

  INSERT INTO numbers VALUES(4,44,"XYZ");

  ]]

 

local insert_stmt = db:prepare('SELECT * FROM numbers WHERE num1=1')

x = insert_stmt:get_names()

print(x[3])

x = insert_stmt:get_value(0)

at this point, you have not stepped the vm insert_stmt. You need to 

if sqlite3.ROW ==  insert_stmt:step() then
    x = insert_stmt:get_value(0)
end
insert_stmt:finalize() -- or insert_stmt:reset() if you want to reuse the insert_stmt
 

print(x[1])

 

Should I be doing this another way?  I thought I would be able to pull queries into a Lua table, but I don’t see a way to do that.

BTW, insert_stmt is a strange name to use for a select statement.

Use the for loop, it's much easier. If you only want one value, then construct your sql to return one row.

e