RE: lsqlite3

["Leinen, Rick" <RLeinen@...>]

Thanks,

 

I’m confused with stmt:step().  What does it do?  I read about it and did a couple of experiments, but it still doesn’t make sense.

 

Also, I don’t see sqlite3.ROW listed at http://lua.sqlite.org/index.cgi/doc/tip/doc/lsqlite3.wiki.  Should I be looking elsewhere?

 

You’re right about the variable name.  Result of copy and paste.

 

Rick

 

From: lua-l-bounces@lists.lua.org [mailto:lua-l-bounces@lists.lua.org] On Behalf Of Doug Currie
Sent: Thursday, September 08, 2016 6:20 PM
To: Lua mailing list
Subject: Re: lsqlite3

 

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