Re: lsqlite3, no way to bind null?

[HyperHacker <hyperhacker@...>]

On Thu, Nov 10, 2011 at 09:11, Doug Currie <doug.currie@gmail.com> wrote:
>
> 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
>
>
>
>

Aha, sorry, I see what went wrong here. The parameter I was binding
wasn't actually nil, but false. nil does work correctly.

This is why I shouldn't code late at night.

-- 
Sent from my toaster.