Re: lsqlite3, no way to bind null?

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

On Nov 10, 2011, at 3:40 AM, HyperHacker wrote:

> I'm using lsqlite3 with prepared statements like (simplified example):
> 
> local stmt = db:prepare("INSERT INTO items (x, y, z) VALUES (?, ?, ?)")
> stmt:bind_values(a, b, c)
> stmt:step()
> stmt:finalize()
> 
> The problem is if, for example, a is nil and x is a numeric, nullable
> type such as INTEGER, x ends up being not null but zero. Seemingly the
> only way to actually insert null is to explicitly specify it in the
> query, instead of as a parameter, which kinda defeats the purpose of
> parameters. Is there some workaround?


It's working for me in lsqlite3 version 0.8 ( http://lua.sqlite.org/index.cgi/home )

tmp e$ sqlite3 tmp.db
SQLite version 3.7.7 2011-06-23 19:49:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table items (x integer, y, z);
sqlite> insert into items values (1, 2, 3);
sqlite> insert into items values (null, 5, 6);
sqlite> select * from items;
1|2|3
|5|6
sqlite> .exit
tmp e$ lua
Lua 5.1.4  Copyright (C) 1994-2008 Lua.org, PUC-Rio
> require "lsqlite3"
> =sqlite3.version()
3.7.7
> db = sqlite3.open("tmp.db");
> b = 7;
> c = 8;
> stmt = db:prepare("INSERT INTO items (x, y, z) VALUES (?, ?, ?)")
> stmt:bind_values(a, b, c)
> stmt:step()
> stmt:finalize()
> for row in db:rows "select x, y, z from items" do print (row[1], row[2], row[3]) end
1	2	3
nil	5	6
nil	7	8


e