Re: [sqlite] Tcl sqlite3 bindings - NULL handling in `$db eval $sql array { ... }`

[Peter da Silva]

On 6/26/17, 11:48 AM, "drhsql...@gmail.com on behalf of Richard Hipp" 
 wrote:
> OK.  I'll back out the change, then.

That’s definitely safer, it’s a super useful capability but needs to be applied 
selectively.
 

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tcl sqlite3 bindings - NULL handling in `$db eval $sql array { ... }`

[Richard Hipp]

OK.  I'll back out the change, then.

On 6/26/17, Peter da Silva  wrote:
> This is really a pretty major change.
>
> Our experience with the comparable options in Pgtcl and Speedtables is that
> there is likely a lot of code that assumes that all array elements are set
> in `$db eval “...” array { ... }` blocks that will error out with this
> change. I don’t think I would be comfortable using this in existing code
> without doing an extensive audit... and for third party packages getting
> changes propagated upstream.
>
> Making it a per-call option allows new code to use it safely, without
> impacting any other components that might be using the same database.
>
> On 6/26/17, 11:31 AM, "drhsql...@gmail.com on behalf of Richard Hipp"
>  wrote:
>
> On 6/26/17, Peter da Silva  wrote:
> > On 6/26/17, 11:15 AM, "drhsql...@gmail.com on behalf of Richard Hipp"
> >  wrote:
> >> If you get the latest check-in
> (https://www.sqlite.org/src/info/trunk)
> >> there is a new option on the "sqlite3" command called "-unsetnull 1"
> which
> >> causes "db eval" to work as you desire - by unsetting the array
> elements
> >> for NULL values.  This option is off by default for legacy
> compatibility.
> >
> > Could that be an option on the eval command rather than the db, so
> that
> > packages can safely use the feature on databases they don’t “own”?
> >
>
> It is per-connection.
>
> The change is sufficient minor and obscure that 99.9% of packages
> should work the same regardless of the setting.  The only reason for
> making it an option rather than the only way things happen is for the
> other 0.1% of application where it really will make a difference.
>
> --
> D. Richard Hipp
> d...@sqlite.org
>
>
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tcl sqlite3 bindings - NULL handling in `$db eval $sql array { ... }`

[Richard Hipp]

On 6/26/17, Peter da Silva  wrote:
> On 6/26/17, 11:15 AM, "drhsql...@gmail.com on behalf of Richard Hipp"
>  wrote:
>> If you get the latest check-in (https://www.sqlite.org/src/info/trunk)
>> there is a new option on the "sqlite3" command called "-unsetnull 1" which
>> causes "db eval" to work as you desire - by unsetting the array elements
>> for NULL values.  This option is off by default for legacy compatibility.
>
> Could that be an option on the eval command rather than the db, so that
> packages can safely use the feature on databases they don’t “own”?
>

It is per-connection.

The change is sufficient minor and obscure that 99.9% of packages
should work the same regardless of the setting.  The only reason for
making it an option rather than the only way things happen is for the
other 0.1% of application where it really will make a difference.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tcl sqlite3 bindings - NULL handling in `$db eval $sql array { ... }`

[Peter da Silva]

On 6/26/17, 11:15 AM, "drhsql...@gmail.com on behalf of Richard Hipp" 
 wrote:
> If you get the latest check-in (https://www.sqlite.org/src/info/trunk) there 
> is a new option on the "sqlite3" command called "-unsetnull 1" which causes 
> "db eval" to work as you desire - by unsetting the array elements for NULL 
> values.  This option is off by default for legacy compatibility.

Could that be an option on the eval command rather than the db, so that 
packages can safely use the feature on databases they don’t “own”?

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tcl sqlite3 bindings - NULL handling in `$db eval $sql array { ... }`

[Richard Hipp]

If you get the latest check-in (https://www.sqlite.org/src/info/trunk)
there is a new option on the "sqlite3" command called "-unsetnull 1"
which causes "db eval" to work as you desire - by unsetting the array
elements for NULL values.  This option is off by default for legacy
compatibility.

On 6/26/17, Peter da Silva  wrote:
> On 6/26/17, 9:00 AM, "sqlite-users on behalf of Richard Hipp"
> 
> wrote:
>> The "db nullvalue STRING" command lets you translate NULL values into the
>> string value of your choice.  But there is not (currently) a way to cause
>> NULL values to unset the corresponding member of the array.
>
> That’s what I’d call an “in-band” solution. It’s got problems when writing
> code that needs to generalize to unknown datasets...
>
> Also, does `db nullvalue` return the current null value, or would you need
> to track that externally? The documentation doesn’t say, but let’s see...
>
> % test nullvalue {\N}
> \N
> % test nullvalue
> \N
>
> So that’s workable if you know you have a guaranteed unique token-string you
> can use. Also, may want to update https://sqlite.org/tclsqlite.html to note
> that.
>
> Anyway, I ran into this testing my sqlite3 bridge for Pgtcl. It doesn’t seem
> like it would be hard to implement a clone of `$db select` that will do what
> I want, but it would be nice to have a standard binding.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tcl sqlite3 bindings - NULL handling in `$db eval $sql array { ... }`

[Peter da Silva]

On 6/26/17, 9:00 AM, "sqlite-users on behalf of Richard Hipp" 
 
wrote:
> The "db nullvalue STRING" command lets you translate NULL values into the 
> string value of your choice.  But there is not (currently) a way to cause 
> NULL values to unset the corresponding member of the array.

That’s what I’d call an “in-band” solution. It’s got problems when writing code 
that needs to generalize to unknown datasets...

Also, does `db nullvalue` return the current null value, or would you need to 
track that externally? The documentation doesn’t say, but let’s see...

% test nullvalue {\N}
\N
% test nullvalue
\N

So that’s workable if you know you have a guaranteed unique token-string you 
can use. Also, may want to update https://sqlite.org/tclsqlite.html to note 
that.

Anyway, I ran into this testing my sqlite3 bridge for Pgtcl. It doesn’t seem 
like it would be hard to implement a clone of `$db select` that will do what I 
want, but it would be nice to have a standard binding.
 

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tcl sqlite3 bindings - NULL handling in `$db eval $sql array { ... }`

[Richard Hipp]

On 6/26/17, Peter da Silva  wrote:
> What’s the best way to handle NULLs out of band when walking the results of
> a query:
>
> $sqlite_db eval “SELECT * FROM table ...” array {
>   ...
> }
>
> In other Tcl database bindings it’s common to return arrays containing
> possible null values with NULL values simply unset, so `[info exists]` can
> be used to distinguish nulls from the empty string. For example in Pgtcl you
> can specify `pg_select ... -withoutnulls` to omit nulls from returned rows -
> I don’t see anything analogous in sqlite3 but it’s possible I’m missing
> something.

The "db nullvalue STRING" command lets you translate NULL values into
the string value of your choice.  But there is not (currently) a way
to cause NULL values to unset the corresponding member of the array.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users