Tcl sqlite3 bindings - NULL handling in `$db eval $sql array { ... }`

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Tcl sqlite3 bindings - NULL handling in `$db eval $sql array { ... }`

Peter da Silva
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.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Richard Hipp-3
On 6/26/17, Peter da Silva <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 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" <[hidden email] on behalf of [hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Richard Hipp-3
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 <[hidden email]> wrote:

> On 6/26/17, 9:00 AM, "sqlite-users on behalf of Richard Hipp"
> <[hidden email] on behalf of [hidden email]>
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Peter da Silva
On 6/26/17, 11:15 AM, "[hidden email] on behalf of Richard Hipp" <[hidden email] on behalf of [hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Richard Hipp-3
On 6/26/17, Peter da Silva <[hidden email]> wrote:

> On 6/26/17, 11:15 AM, "[hidden email] on behalf of Richard Hipp"
> <[hidden email] on behalf of [hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Peter da Silva
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, "[hidden email] on behalf of Richard Hipp" <[hidden email] on behalf of [hidden email]> wrote:

    On 6/26/17, Peter da Silva <[hidden email]> wrote:
    > On 6/26/17, 11:15 AM, "[hidden email] on behalf of Richard Hipp"
    > <[hidden email] on behalf of [hidden email]> 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
    [hidden email]
   

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Richard Hipp-3
OK.  I'll back out the change, then.

On 6/26/17, Peter da Silva <[hidden email]> 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, "[hidden email] on behalf of Richard Hipp"
> <[hidden email] on behalf of [hidden email]> wrote:
>
>     On 6/26/17, Peter da Silva <[hidden email]> wrote:
>     > On 6/26/17, 11:15 AM, "[hidden email] on behalf of Richard Hipp"
>     > <[hidden email] on behalf of [hidden email]> 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
>     [hidden email]
>
>
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Peter da Silva
On 6/26/17, 11:48 AM, "[hidden email] on behalf of Richard Hipp" <[hidden email] on behalf of [hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...