Re: [sqlite] Column headers of result

[Dennis Cote]

BareFeet wrote:
>
>   
>>> How can I get just the column headers without all the result rows?
>>>   
>> Turn headers on, then perform a search which gives no results.
>> 
>
> Unfortunately, the sqlite3 command line tool does not show the headers  
> when there are no result rows. 
>
>   
You can change that behavior with the pragma empty_result_callbacks (see 
http://www.sqlite.org/pragma.html).

SQLite version 3.6.16
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t(a,b);
sqlite> pragma empty_result_callbacks=1;
sqlite> .header on
sqlite> select * from t;
a|b

HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Column headers of result

[Simon Slavin]

On 5 Jul 2009, at 2:35am, BareFeet wrote:

> Hi Simon,


Just a reminder that you're posting to a list, not to me personally.

>> What are you trying to do ?  Find all the columns in a TABLE or find
>> all the columns in an arbitrary SELECT ?  If it's the former use
>> PRAGMA table_info for the table.
>
> Thanks, yes, I am familiar with pragma table_info to return the column
> headers of a table or view. But I need the same functionality for the
> result of an arbitrary sequence of SQL commands.

But an arbitary sequence of SQL commands doesn't have a list of  
columns.  It might involve two different unrelated tables, for  
example.  Or more than one SELECT commands.  Or no SELECT commands.   
Or INSERT commands that change more than one table.  Or one INSERT  
command that results in changes to two different tables.  I'm sorry  
but I still don't understand which columns you expect your  
functionality to produce given that you specify your sequence of  
commands must be arbitrary.

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

Re: [sqlite] Column headers of result

[BareFeet]

Hi Simon,

>> In any case, it's not possible to convert an ad hoc query (ie not  
>> known beforehand) into one that returns no rows.
>
> Sure it is.  Just add 'WHERE 1=2' to it if there's no WHERE clause,  
> or 'AND 1=2' if there is.

Increasing potential complexity of the "ad hoc" SQL command would  
require increasingly complex parsing to know what to append. eg we'd  
have to check for "begin/commit" and "union" and an existing "limit"  
etc. It is not practical.

>>> It's difficult in the command-line tool, but easy using function
>>> calls
>>> since there's a function call specially intended for it:
>>>
>>> 
>>
>> It would be great to see a pragma or built in SQL function that
>> returned the column headers of a given query.
>
> There is one: the one you just quoted my giving the URL for.

Sorry, let me clarify. I mean that I would like to see a pragma or  
function that can be used in an SQL expression (not a C function), so  
it can be used from the command line.

> If you want the smallest fastest possible query that will return at  
> least one row, do not use 'ORDER BY' and do use 'LIMIT 1'.

As mentioned in my other email, this doesn't lend itself to ad hoc (ie  
unknown beforehand) SQL commands.

> What are you trying to do ?  Find all the columns in a TABLE or find  
> all the columns in an arbitrary SELECT ?  If it's the former use  
> PRAGMA table_info for the table.

Thanks, yes, I am familiar with pragma table_info to return the column  
headers of a table or view. But I need the same functionality for the  
result of an arbitrary sequence of SQL commands.

> If the latter, replacing your 'WHERE' and 'ORDER BY' clauses with  
> LIMIT 1 will give you the right result if there's any data at all.

That will only work if it's a simple select, but not if it contains  
begin/commit, some inserts before the select, an existing limit etc.

Thanks for the thoughts,
Tom
BareFeet

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

Re: [sqlite] Column headers of result

[BareFeet]

Hi Simon,

 How can I get just the column headers without all the result rows?
>>>
>>> Turn headers on, then perform a search which gives no results.
>>
>> Unfortunately, the sqlite3 command line tool does not show the  
>> headers
>> when there are no result rows. In any case, it's nit possible to
>> convert an ad hoc query (ie not known beforehand) into one that
>> returns no rows.
>
> Add "limit 0" to the end of the select statement

That will work if it's just a simple select statement without "union"  
or an existing "limit" etc. But if it is a compound select or a series  
of commands (eg inserts then a select), then appending "limit" to the  
end will fail.

>>> It's difficult in the command-line tool, but easy using function  
>>> calls
>>> since there's a function call specially intended for it:
>>>
>>> 
>>
>> It would be great to see a pragma or built in SQL function that
>> returned the column headers of a given query.
>
> This is only a limitation in the shell (or using sqlite3_exec); column
> headers can be queried from prepared statements as already pointed
> out.

Yes, thanks, noted. My query/wish is to get that info from the command  
line.

Thanks,
Tom
BareFeet

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

Re: [sqlite] Column headers of result

[Simon Slavin]

On 5 Jul 2009, at 12:31am, BareFeet wrote:

> Hi Simon,
>
>>> How can I get just the column headers without all the result rows?
>>
>> Turn headers on, then perform a search which gives no results.
>
> Unfortunately, the sqlite3 command line tool does not show the headers
> when there are no result rows.

Whoops.  You're right.  If the query returns no rows, there are no  
columns, so there are no column headers.

> In any case, it's nit possible to
> convert an ad hoc query (ie not known beforehand) into one that
> returns no rows.

Sure it is.  Just add 'WHERE 1=2' to it if there's no WHERE clause, or  
'AND 1=2' if there is.

>> It's difficult in the command-line tool, but easy using function  
>> calls
>> since there's a function call specially intended for it:
>>
>> 
>
> It would be great to see a pragma or built in SQL function that
> returned the column headers of a given query.

There is one: the one you just quoted my giving the URL for.  If you  
want the smallest fastest possible query that will return at least one  
row, do not use 'ORDER BY' and do use 'LIMIT 1'.  But if no rows are  
returned even that will fail.

What are you trying to do ?  Find all the columns in a TABLE or find  
all the columns in an arbitrary SELECT ?  If it's the former use  
PRAGMA table_info for the table.  If the latter, replacing your  
'WHERE' and 'ORDER BY' clauses with LIMIT 1 will give you the right  
result if there's any data at all.

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

Re: [sqlite] Column headers of result

[Simon Davies]

2009/7/5 BareFeet :
>>>
> Hi Simon,
>
>>> How can I get just the column headers without all the result rows?
>>
>> Turn headers on, then perform a search which gives no results.
>
> Unfortunately, the sqlite3 command line tool does not show the headers
> when there are no result rows. In any case, it's nit possible to
> convert an ad hoc query (ie not known beforehand) into one that
> returns no rows.

Add "limit 0" to the end of the select statement

>
>> It's difficult in the command-line tool, but easy using function calls
>> since there's a function call specially intended for it:
>>
>> 
>
> It would be great to see a pragma or built in SQL function that
> returned the column headers of a given query.

This is only a limitation in the shell (or using sqlite3_exec); column
headers can be queried from prepared statements as already pointed
out.

>
>>
> Thanks,
> Tom
> BareFeet

Rgds,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Column headers of result

[BareFeet]

>>
Hi Simon,

>> How can I get just the column headers without all the result rows?
>
> Turn headers on, then perform a search which gives no results.

Unfortunately, the sqlite3 command line tool does not show the headers  
when there are no result rows. In any case, it's nit possible to  
convert an ad hoc query (ie not known beforehand) into one that  
returns no rows.

> It's difficult in the command-line tool, but easy using function calls
> since there's a function call specially intended for it:
>
> 

It would be great to see a pragma or built in SQL function that  
returned the column headers of a given query.

>
Thanks,
Tom
BareFeet
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Column headers of result

[Simon Slavin]

On 29 Jun 2009, at 1:05pm, BareFeet wrote:

> How can I get just the column headers without all the result rows?

Turn headers on, then perform a search which gives no results.

It's difficult in the command-line tool, but easy using function calls  
since there's a function call specially intended for it:



Note that if your SELECT uses expressions, rather than direct  
references to columns, the results may not be what you expected.

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

Re: [sqlite] Column headers of result

[BareFeet]

Thanks Jens and John for your replies.

Yes, I am familiar with the "headers on" option. Sorry, I should have  
mentioned.

However, that prepends the headers to the result rows. It would also  
be ambiguous if a column header contains a pipe or quote or newline  
(which is unlikely but possible).

How can I get just the column headers without all the result rows?

Thanks,
Tom
BareFeet

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

Re: [sqlite] Column headers of result

[John Machin]

On 29/06/2009 2:57 PM, BareFeet wrote:
> Hi,
> 
> Is there any way in the command line to get the columns in a query  
> result?
> 
> For example, given an ad-hoc SQL command, such as:
> 
> begin;
> insert into MyTableOrView select * from SomeSource;
> select * from MyTableOrView join SomeOtherTableOrView where condition;
> end;
> 
> how can I get the column headers in the result?
> 
> I know I can get the column info of a table using pragma table_info,  
> but I don't think that works for an ad-hoc query.
> 

SQLite version 3.6.14
Enter ".help" for instructions<<<=== ever noticed this before?
Enter SQL statements terminated with a ";"
sqlite> .help
[snip]
.header(s) ON|OFF  Turn display of headers on or off
[snip]
sqlite> select 1 as one, 2 as two;
1|2
sqlite> .header on
sqlite> select 1 as one, 2 as two;
one|two
1|2
sqlite> select 1 as one, 2 as two, 3;
one|two|3
1|2|3
sqlite> create table foo (bar int);
sqlite> insert into foo values(42);
sqlite> select * from foo;
bar
42
sqlite> select bar as rab from foo;
rab
42
sqlite>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Column headers of result

[Jens Miltner]

Am 29.06.2009 um 06:57 schrieb BareFeet:

> Hi,
>
> Is there any way in the command line to get the columns in a query
> result?
>
> For example, given an ad-hoc SQL command, such as:
>
> begin;
> insert into MyTableOrView select * from SomeSource;
> select * from MyTableOrView join SomeOtherTableOrView where condition;
> end;
>
> how can I get the column headers in the result?
>
> I know I can get the column info of a table using pragma table_info,
> but I don't think that works for an ad-hoc query.

use ".headers on" in the commandline (or run the tool with the -header  
option) - the first line will contain the column names.

HTH,


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

[sqlite] Column headers of result

[BareFeet]

Hi,

Is there any way in the command line to get the columns in a query  
result?

For example, given an ad-hoc SQL command, such as:

begin;
insert into MyTableOrView select * from SomeSource;
select * from MyTableOrView join SomeOtherTableOrView where condition;
end;

how can I get the column headers in the result?

I know I can get the column info of a table using pragma table_info,  
but I don't think that works for an ad-hoc query.

Thanks,
Tom
BareFeet

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