Re: Need some assistance with snippet of Lua/Sqlite code

[Alek Paunov <alex@...>]

On 11.03.2012 13:06, Jeff Smith wrote:
> what I dont like is the fact that I have to add the blob as a NULL using
> the initial bind_values line, which then gets corrected with the next
> bind_blob line. I am sure there is a more correct way of doing this ?

stmt:bind_values is a simply convenience shortcut, you are free to use 
the basic form - stmt:bind (see the attachment) per parameter.

Also the proper place of db:prepare is outside of the loops. Do not 
forget stmt:finalize and error codes checking after every library api 
(sql or not) invocation.

Notes above apply for every sql connector (of course method names may vary).

Welcome to DB world :-)
Alek

local sqlite3 = require('lsqlite3')
local blobStr
local stmt
local db = sqlite3.open([[test-insert.sqlite]])

db:exec([[CREATE TABLE IF NOT EXISTS tblApplications (AppFileName VARCHAR , AppBlob BLOB, GeneralNotes VARCHAR)]])
stmt = db:prepare[[ INSERT INTO tblApplications (AppFileName, AppBlob, GeneralNotes) VALUES( ?, ?, ?)]];

for i=1, 5 do
   local blobStr = tostring(i)
   local someAppName = "Some Appname " .. tostring(i)
   local someGenNotes = "Some GenNotes " .. tostring(i)

   stmt:bind(1, someAppName)
   stmt:bind_blob(2, blobStr)
   stmt:bind(3, someGenNotes)

   stmt:step()
   stmt:reset()
end

stmt:finalize()
db:close()