Busirane <
john....@gmail.com> wrote:
> What I want is to get a *list* of the selected rows. But
>
> db eval {SELECT * FROM t}
>
> returns the *concatenation* of the selected rows.
That it does. If you know how many columns are in the table, you can
do this:
set rows [list]
foreach {a b c} [db eval {select * from test;}] {
lappend rows [list $a $b $c]
}
And you can wrap that up in a proc to make it easy to call if you like.
> I am aware of the ability to append ?array? ?script? to the db eval
> command but those approaches require reassembling the vector for each
> row, accumulating them, and returning that.
Yep, which is basically what my code above does as well. Presumably
you don't want to do this.
> At
https://groups.google.com/forum/#!topic/comp.lang.tcl/Sm3IjX_CY6c I found this solution:
>
> db function mklist ::list
> db eval {SELECT mklist(col1, col2, ...colN) FROM t}
>
> and it works great if you specify the columns, as shown, but doesn't work for
>
> db eval {SELECT mklist(*) FROM t} ;# Doesn't work
>
> That just returns an empty list for each selected row. Why doesn't *
> work,
Only D. Richard Hipp could answer for why * works the way it works. As
for your why above in regards to the embedded function call, it is
likely related to a difference in how * is handled by the underlying
sql parser and executor vs. how explicit column specifications are
handled in the same.
> and is there a way to get a *list* of the selected rows without
> having to explicitly specify the columns?
Well, general good DB design says you should avoid use of * for final
designed queries. It is ok for test/prototype work, but the final
system should explicitly state the columns it wishes to have back from
the DB. This insulates the query from the addition of new columns to
the table at a later point in the future. If the table grows a new
column (a not unheard of change in the DB field) then a '*' query's
return set changes, and the code consuming it at best aborts, and at
worst creates corrupted data somewhere else in the DB.
With a query asking for explicit rows, adding columns is an invisible
change, the query works the same before and after the addition. Only
removing a column the query was expecting will effect the query, and
then generally by an error return for column not found. Reducing the
likelyhood that code will try to process bad data anyway, and produce
garbage results.