Re: [sqlite] Tcl binding: Quirk with Tcl variable reference in eval method

[Richard Hipp]

On 1/23/18, Rolf Ade  wrote:
>
> While being able to use Tcl variable references inside db eval SQL
> statements (as in
>
> set name "foo'bar"
> db eval {SELECT * FROM sometable WHERE somecolumn = $name}
>
> ) this does work only for "simple" Tcl variable references.

That is correct.

You can use an array variable, but the index part must be a constant.
For example:

set x(name) "whatever"
db eval {SELECT * FROM tab WHERE col = $x(name)}

As you observce, you cannot use another variable as the index to 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

[sqlite] Tcl binding: Quirk with Tcl variable reference in eval method

[Rolf Ade]

While being able to use Tcl variable references inside db eval SQL
statements (as in

set name "foo'bar"
db eval {SELECT * FROM sometable WHERE somecolumn = $name}

) this does work only for "simple" Tcl variable references.

This script shows this:

package require sqlite3
sqlite3 db ":memory:"
puts [db version]
db eval {
CREATE TABLE test(name text);
INSERT INTO test(name) VALUES('one');
}
set array(a) "one"
set key "a"
puts "\$array(\$key) has the value: '$array($key)'"
puts "Same as \$array(a): '$array(a)'"
db eval {
DELETE FROM test WHERE name = $array($key)
}
puts "State after first DELETE (using \$array(\$key)); nothing deleted"
puts [db eval {SELECT count(1) FROM test}]
puts [db eval {SELECT * FROM test}]
db eval {
DELETE FROM test WHERE name = $array(a)
}
puts "State after second DELETE (using (\$array(a)); now the row is deleted"
puts [db eval {SELECT count(1) FROM test}]
puts [db eval {SELECT * FROM test}]


While $array($key) is a perfect variable reference in Tcl scripts
(depending of course of the values of array and key) it isn't inside a
SQL script provided to the eval method.

I naively expected the sqlite3 SQL parser to revert to Tcl_ParseVar()
(or Tcl_ParseVarName()) to resolve Tcl variable references inside the
SQL - which would happily resolve $array($key) or even more complicated
constructs as $x([expr {$index + 1}]) - but it is obviously done in
another, simpler way.

I suspect there are good reasons for this "limitation" and this is no
big deal, the feature (with its current capabilities) is still very
helpful. Maybe I'm the only one being so bold to have expected
$array($key) to work inside the SQL statement. But perhaps a word of
warning in https://www.sqlite.org/tclsqlite.html#eval would prevent
others to have similar expectations.


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