On 3 Mar 2012, rod wrote:
On 2/9/12, rod <crimson.blue.2 at gmail.com> wrote: > My apologies if this question should be directed to some other list. > > I'm looking for a better way to printout the: > Column Headers, > followed by the table contents - > in comma separated value (tsv) format > from a SELECT statement: > > > So far I have two solutions, neither seems elegant enough. > > The second solution makes use of a test of countI'm excited, I may have solved my problem. By setting a trace I am able to reset a variable called counter to -0- every time a new sql statment is compiled. In addition I also save the sql statement to be evaluated to a variable called ::last_sql via: # this trace is executed at the end of the compiling of an sql statement sql trace {set ::count 0; set ::last_sql } Then this proc will print the headers if ::count=0 (ie only prior to processing the the first row of the select results. proc fqry {row {sep "\t"} {header 1}} { # you can't pass the value in the array to the proc # you need to upvar it to another var upvar $row g_row set val {} set col {} set col_name {} foreach {col_name } $g_row(*) { lappend val $g_row($col_name) lappend col $col_name set len [string length $col_name] set dash [string repeat - $len] lappend hd $dash } #always prints the vals; only print headers and "-------"'s if counter==0 if {$::count==0 && $header } {puts [join $col $sep]; puts $hd} puts [join $val $sep] incr ::count } Usage: %sql eval "Select col2, col4, col6 as Results from table 1" q_array {fqry q_array} Q) Does this seem like the way to accomplish this??? Are there other typical uses of trace that I should be aware of prior to expanding on this solution.
Personally, your first methods seemed just as good, and no more inelegant. When you posted earlier, I looked for a way, using the TCL interface, to get the column names once SQLite has compiled the statement but before it starts fetching rows. The column names are available then, and it solves the problem that I am about to pose. Unfortunately, the TCL interface provides no access, as now written.
What I find very unsatisfactory about both of your solutions, and about anything that looks reasonably doable through the present TCL interface, is that if the result set is empty, no header will be emitted. I find that for things I do, the possibility of an empty table cannot be precluded. Nevertheless, the process that uses the table still has to "query" it, unless there is special logic to see that it is missing or completely empty (including the header). Such special-casing is very distasteful to programmers who think in terms of sets.
This issue is what has led me to create a variant of the SQLite3 shell that has no more trouble doing the "right" header output when the result set is empty than when there is at least one row (that would have sufficed to trigger header emission with the original code).
Best regards, -- Larry Brasfield _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
