Thread necromancy!
Back in 2007 I expressed a desire to efficiently insert a *list* of
values all at once, where the entire list is contained within a single
Tcl variable. The notation would be to use the variable name, prefixed
with $ or :, in place of the value list, intentionally omitting the
parentheses which would otherwise be required by the SQL syntax.
I know it's possible to extract the list into separate variables, one
per element, or to construct the query (with quoting and proper
apostrophe doubling), but both approaches incur performance penalties,
and the latter is subject to injection attacks if done hastily.
Here's what I wish I could type:
foreach row $table {
db eval {INSERT INTO xyzdata VALUES $row}
}
And here's what works today (the former is slow due to Tcl shimmering
and SQL recompilation, the latter example assumes five columns):
foreach row $table {
db eval "INSERT INTO xyzdata VALUES\
('[join [string map {' ''} $row] ',']')"
}
foreach row $table {
lassign $row a b c d e
db eval {INSERT INTO xyzdata VALUES ($a, $b, $c, $d, $e)}
}
How about inserting NULLs? The two present-day implementations I offer
don't allow that, though you could play games with nullif(). For the
new notation, I would suggest treating missing columns as NULLs. (By
the way, treat extra columns as errors.) This gives some NULL
capability, though only the final columns can be NULL. And if the
variable itself is NULL, let all columns be NULL.
Inserting multiple rows? Since the variable takes the place of the
parenthesized value list (including the parentheses themselves), just
separate the variables with the several rows using commas. Or mix and
match with traditional value lists:
db eval {INSERT INTO xyzdata VALUES $row1, $row2, (1, $y, $z)}
Taking all rows from a variable containing a list of lists? That goes
beyond the scope of this proposal since it doesn't naturally fit into
the existing syntax.
When I asked about this years ago, I was told that this conflicts
irreconcilably with the fundamental structure of the parser. Is that
still true?
For reference, here are extracts from my old emails:
On 10/4/2007 9:35 PM, Andy Goth wrote:
See the bottom of http://wiki.tcl.tk/2633 for more details.
My current project would benefit from the ability to expand a Tcl
variable into multiple SQL values.
This expansion will happen within SQLite and not Tcl. That's the
whole point; it eliminates injection attacks and allows the compiled
SQL to be cached inside the Tcl_Obj.
On 10/5/2007 10:01 AM, Andy Goth wrote:
$ db eval {insert into xyzdata values $x}
Note the lack of parentheses around $x. This distinguishes it from
the case of inserting a single value "1 2 3" into xyzdata.
Footnote: here are the nullif() games I refer to above. This code
treats empty string as NULL. That's not quite the behavior I wanted to
propose though, so I pushed this text to the end of the email.
foreach row $table {
db eval "INSERT INTO xyzdata VALUES\
(nullif('[join [string map {' ''} $row] ',''),nullif(']',''))"
}
foreach row $table {
lassign $row a b c d e
db eval {INSERT INTO xyzdata VALUES (nullif($a, ''), nullif($b, ''),
nullif($c, ''), nullif($d, ''), nullif($e, ''))}
}
--
Andy Goth | <andrew.m.goth/at/gmail/dot/com>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
