lsqlite3 dynamic update

[dennis]

heya all

 

 

when I want to update an value in my database it needs to be done dynamically (by the user) for example. when the value in the database = 1, the user may want to change that to 0 (when he sets the Bloom off) I can do it hard coded by using:

for test in db:nrows("UPDATE test SET bloom = 1 WHERE bloom = 0")

 

I already have an placeholder for this which is

 

local bloomval =1

 

How to do this so the value of bloomval = 1 can be set by the user ?

Regards,

[ChrisMAN]

I don't quite understand what you are asking. If i understand this is a straight forward way about it.

 

local settings = db:nrows("Select * from settings")[1]  or CreateSettings() -- singleton row
function SetSetting(field, value)
db:exec("UPDATE settings SET " .. field .. " = " value")
settings[field] = value
end

[dennis]

okay thanks

what I wanted to do is that I have sliders and check boxes, those will set an value.

I want that value to be returned to the code and updated in the database...

And the code fragment I showed will do that but I can not add my own value to it.

[Rick]

A good number of sql engines have a concept they call prepared statements. This allows you to define parameters and set those parameters with values. The reason you want to use these instead of building the sql string yourself is to avoid something they call sql injection. Here is an example on how to do it using Lua: http://www.nessie.de/mroth/lua-sqlite3/documentation.html#ref11 . This is the preferred method. You also get a speed boost by using prepared statements.

[ChrisMAN]

If you want to hook into a setter for a table you will have to use a metatable __index/__newindex function.

 

http://www.lua.org/pil/13.4.1.html

[dennis]

Thanks