Prepared statement for sqlite

Solar2D Lua Language
#1

I am trying to save game settings using sqlite backend.  I would like to properly escape string for the player name.  Here is what i am using but I am getting “attempt to index a number value”:

local player = “Anomymous”
local sound = 1
local music = 1

function M.saveSettings()
    local sql = “delete from settings”
    db:exec(sql)
    
    if player=="" then
        player = “anonymous”
    end
    --sql = “insert into settings (player, sound, music) values '” … player …"’,"…sound…","…music…")"
    stmt = db:prepare[[insert into settings (player, sound, music) values(:p_player, :p_sound, :p_music)]]
    stmt:bind_names({p_player=player, p_sound=sound, p_music=music}):exec()

end 

can anyone tell me what’s wrong with the statement?

Thanks,

Kuan

#2

stmt:bind_names returns a result code which is a number so you can not call :exec() on it

Furthermore prepared statements do not have an exec function use stmt:rows() or stmt:urows() or stmt:nrows() to return all rows or

stmt:step() to process one row at a time.

http://lua.sqlite.org/index.cgi/doc/tip/doc/lsqlite3.wiki#stmt_bind_names

#3

Thanks. I think the key is to use stmt:step().  I still cannot make the bind_names to work. 

This works:

    stmt = db:prepare[[insert into settings (player, sound, music) values(?, ?, ?)]]
    stmt:bind(1, player)
    stmt:bind(2, sound)
    stmt:bind(3, music)
    stmt:step()

#4

Are you saying this doesn’t work?

stmt = db:prepare[[insert into settings (player, sound, music) values(:p\_player, :p\_sound, :p\_music)]] stmt:bind\_names({p\_player=player, p\_sound=sound, p\_music=music}) stmt:step()
#5

It worked.  somehow, I copied and paste your code.  It just worked.  I must have missed something.

Thanks.

#6

stmt:bind_names returns a result code which is a number so you can not call :exec() on it

Furthermore prepared statements do not have an exec function use stmt:rows() or stmt:urows() or stmt:nrows() to return all rows or

stmt:step() to process one row at a time.

http://lua.sqlite.org/index.cgi/doc/tip/doc/lsqlite3.wiki#stmt_bind_names

#7

Thanks. I think the key is to use stmt:step().  I still cannot make the bind_names to work. 

This works:

    stmt = db:prepare[[insert into settings (player, sound, music) values(?, ?, ?)]]
    stmt:bind(1, player)
    stmt:bind(2, sound)
    stmt:bind(3, music)
    stmt:step()

#8

Are you saying this doesn’t work?

stmt = db:prepare[[insert into settings (player, sound, music) values(:p\_player, :p\_sound, :p\_music)]] stmt:bind\_names({p\_player=player, p\_sound=sound, p\_music=music}) stmt:step()
#9

It worked.  somehow, I copied and paste your code.  It just worked.  I must have missed something.

Thanks.