>On 6 Mar 2012, rod wrote:
>> On 2/9/12, rod <crimson.blue.2 at gmail.com> wrote:
>> >
>> > 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:
>> 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:
>>
>> sql trace {set ::count 0; set ::last_sql }
>>
>> proc fqry {row {sep "\t"} {header 1}} {
>> 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}
>>
>>
>
>Personally, your first methods seemed just as good, and no more
>inelegant.
By using the trace the counter automatically gets reset. I think in
my first case
I was having to code in a counter reset prior to each sql statement.
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.
I'm likely missing some important nuance but the trace command did
capture the full sql statement.
When the select statement produces an empty table could you the then
reuse the text of that
select statement to create a table (albeit an empty table) in memory?
Then one could analyze
that temp table for the headers in question?
Last night I think I confirmed that a
CREATE table AS select ....;
Does actually produce a table even when the select statement returns
an empty set.
Using the ::last_sql variable from my trace command above:
CREATE TEMP TABLE table_that_is_only_used_to_get_headers AS $::last_sql;
Then the headers in this empty table could be identified via the
PRAGMA table_info??
PRAGMA table_info (table_that_is_only_used_to_get_headers)
Granted this is unelegant but better than tinkering with the shell?
Thanks for the reply, note that I am quite new to both sqlite and
particularly to tcl.
I'm excited anyway and I'm going to attempt to modify my
proc fqry
to do some formating and possibly some post processing hopefully to
produce output to
stdout or to a specified results file. Maybe output like this:
SELECT
col2,
col4,
col6 as Results
FROM table 1
col2 col4 Results
---- ---- -------
val val val
val val val
.. .. ..
val val val
=== === ======
## Sum
## Avg
#### Max
#### Min
later rod.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
