Re: [sqlite] Determining how many columns were returned in a query

2011-05-08 Thread Woody 
what language are you using? 
 
usually there is a property for the resultset object that will supply the 
number of columns in the result set and another property that will return the 
number of rows.  using the number of columns allows you to index into the 
columns in a loop retrieving each column name as well as some basic properties.
 
in my projects i have two tables that define my reports, one table is just the 
sql code and a descriptor, the second table defines the parameters, names, 
types and any sql code to verify the parameter is correctly entered.  the user 
scrolls thru a grid, picks the report to run, the code sees if there are 
parameters and what kind(date, int, etc, etc) build a screen to get the 
parameters, very them and then executes it.
 
The Sql code for the report uses select column_name as Header_Name syntax so my 
actual report handling logic just has to get the number of columns, loop thru 
that to get the header names, and data types for output masking, then just loop 
thru each row till its done.
 
The actual report handler code stays pretty simple, most of the actual report 
processing is done by sqllite.
 
Woody
Wizard, at large
"I'm in shape, round is a shape!"



 

--- On Sun, 5/8/11, Sam Carleton <scarle...@miltonstreet.com> wrote:


From: Sam Carleton <scarle...@miltonstreet.com>
Subject: Re: [sqlite] Determining how many columns were returned in a query
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Date: Sunday, May 8, 2011, 7:20 PM


On Sun, May 8, 2011 at 3:08 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> Out of interest, are you trying to analyse the results of a "SELECT *" ?
>  Because since it's your query in the first place, you should know what
> columns you asked for.
>

Nope, I NEVER do SELECT *, very, very evil!  Great for development and
testing, but not in code!

I happen to have a code path such that the select statement can return 1, 3
or 5 columns.  I know I could go based on count, but if I could do it by
name that would be safer.  I had not considered the point that multiple
columns could have the same name, though, so I fully understand why such a
function does not exist.

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

Re: [sqlite] Determining how many columns were returned in a query

2011-05-08 Thread Jean-Christophe Deschamps 

>I happen to have a code path such that the select statement can return 
>1, 3
>or 5 columns.  I know I could go based on count, but if I could do it by
>name that would be safer.  I had not considered the point that multiple
>columns could have the same name, though, so I fully understand why such a
>function does not exist.

Also beware that the names returned by sqlite3_column_name are in fact 
the aliases used (if any) by the select statement, thus users can foil 
you without bribing anyone nor subverting the engine.

Say you have a table T with (col_A, sigma), you can get the following:

select max(col_A) as sigma, total(sigma) / count(*) as col_A from T;

names returned by sqlite3_column_name will be 'sigma' and 'col_A'.
Gotcha!

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

Re: [sqlite] Determining how many columns were returned in a query

2011-05-08 Thread Sam Carleton 
On Sun, May 8, 2011 at 3:08 PM, Simon Slavin  wrote:

>
> Out of interest, are you trying to analyse the results of a "SELECT *" ?
>  Because since it's your query in the first place, you should know what
> columns you asked for.
>

Nope, I NEVER do SELECT *, very, very evil!  Great for development and
testing, but not in code!

I happen to have a code path such that the select statement can return 1, 3
or 5 columns.  I know I could go based on count, but if I could do it by
name that would be safer.  I had not considered the point that multiple
columns could have the same name, though, so I fully understand why such a
function does not exist.

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

Re: [sqlite] Determining how many columns were returned in a query

2011-05-08 Thread Igor Tandetnik 
Sam Carleton  wrote:
> On May 8, 2011, at 12:53 PM, "Igor Tandetnik"  wrote:
> 
>> Sam Carleton  wrote:
>>> I had it wrong in the email body, I meant how many columns are in query?
>> 
>> sqlite3_column_count. Don't even need to execute the query for that, just 
>> prepare it.
> 
> Ah, thank you!  Is my impression current there is no function call to get the 
> column index given a name?

Indeed there is no such function. Note that there may be more than one column 
with the same name.
-- 
Igor Tandetnik

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

Re: [sqlite] Determining how many columns were returned in a query

2011-05-08 Thread Simon Slavin 

On 8 May 2011, at 8:04pm, Igor Tandetnik wrote:

> Sam Carleton  wrote:
>> I want to go the other way: I have the string name, I need the index of the 
>> column, same concept as
>> sqlite3_bind_parameter_index(). 
> 
> You'll have to enumerate all columns, get the name of each, and compare it 
> with the desired name.

Out of interest, are you trying to analyse the results of a "SELECT *" ?  
Because since it's your query in the first place, you should know what columns 
you asked for.

Generally, experienced programmers don't use "SELECT *" inside real 
applications, although it can be useful for utilities.  The problem comes when 
you want to change your schema and find if difficult to find all the SELECTs 
you now have to modify.

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

Re: [sqlite] Determining how many columns were returned in a query

2011-05-08 Thread Igor Tandetnik 
Sam Carleton  wrote:
> On May 8, 2011, at 11:06 AM, "Jay A. Kreibich"  wrote:
> 
>> On Sun, May 08, 2011 at 11:00:29AM -0400, Sam Carleton scratched on the wall:
>> 
>>> Is there a way to find out the id of a particular column?
>> 
>>  sqlite3_column_name()
> 
> I want to go the other way: I have the string name, I need the index of the 
> column, same concept as
> sqlite3_bind_parameter_index(). 

You'll have to enumerate all columns, get the name of each, and compare it with 
the desired name.
-- 
Igor Tandetnik

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

Re: [sqlite] Determining how many columns were returned in a query

2011-05-08 Thread Sam Carleton 
On May 8, 2011, at 11:06 AM, "Jay A. Kreibich"  wrote:

> On Sun, May 08, 2011 at 11:00:29AM -0400, Sam Carleton scratched on the wall:
> 
>> Is there a way to find out the id of a particular column?
> 
>  sqlite3_column_name()

I want to go the other way: I have the string name, I need the index of the 
column, same concept as sqlite3_bind_parameter_index().

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

Re: [sqlite] Determining how many columns were returned in a query

2011-05-08 Thread Jay A. Kreibich 
On Sun, May 08, 2011 at 11:00:29AM -0400, Sam Carleton scratched on the wall:
> How does one go about finding out how many rows a query returns? 

  sqlite3_column_count()

> Is there a way to find out the id of a particular column?

  sqlite3_column_name()


   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Determining how many columns were returned in a query

2011-05-08 Thread Sam Carleton 
On May 8, 2011, at 12:53 PM, "Igor Tandetnik"  wrote:

> Sam Carleton  wrote:
>> I had it wrong in the email body, I meant how many columns are in query?
> 
> sqlite3_column_count. Don't even need to execute the query for that, just 
> prepare it.

Ah, thank you!  Is my impression current there is no function call to get the 
column index given a name? Like there is for getting the index of a binding. 

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

Re: [sqlite] Determining how many columns were returned in a query

2011-05-08 Thread Igor Tandetnik 
Sam Carleton  wrote:
> I had it wrong in the email body, I meant how many columns are in query?

sqlite3_column_count. Don't even need to execute the query for that, just 
prepare it.
-- 
Igor Tandetnik

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

Re: [sqlite] Determining how many columns were returned in a query

2011-05-08 Thread Mr. Puneet Kishor 

On May 8, 2011, at 11:46 AM, Sam Carleton wrote:

> On May 8, 2011, at 11:09 AM, Jean-Christophe Deschamps  
> wrote:
> 
>> 
>>> How does one go about finding out how many rows a query returns?
>> 
>> This is the number of time sqlite3_step can be called successfully 
>> until it returns SQLITE_DONE.
> 
> I had it wrong in the email body, I meant how many columns are in query?
> 

Since, ostensibly, you are the one who queried in the first place, shouldn't 
you know that already? Maybe there is more to this question that you are not 
stating.

Puneet.

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

Re: [sqlite] Determining how many columns were returned in a query

2011-05-08 Thread Sam Carleton 
On May 8, 2011, at 11:09 AM, Jean-Christophe Deschamps  
wrote:

> 
>> How does one go about finding out how many rows a query returns?
> 
> This is the number of time sqlite3_step can be called successfully 
> until it returns SQLITE_DONE.

I had it wrong in the email body, I meant how many columns are in query?

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

Re: [sqlite] Determining how many columns were returned in a query

2011-05-08 Thread Jean-Christophe Deschamps 

>How about:
>
>SELECT count() FROM ();

You can do that (and variations) but this is a completely distinct 
statement.

I meant that there is no possibility to recover the row count of a 
result set before it goes to completion (by iterating step), just 
because the SQLite engine has no idea itself.



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

Re: [sqlite] Determining how many columns were returned in a query

2011-05-08 Thread Samuel Adam 
On Sun, 08 May 2011 11:09:36 -0400, Simon Slavin   
wrote:

>
> On 8 May 2011, at 4:00pm, Sam Carleton wrote:
>
>> How does one go about finding out how many rows a query returns?
>
> This was asked earlier this week.  There is no magic way.  Step through  
> the rows and count them.
>
> You can, of course, do a preliminary SELECT for 'count(*)' and see what  
> answer is returned.

How about:

SELECT count() FROM ();

Depending on the query, this might be possible and/or more obvious:

SELECT count()
FROM 
WHERE ;

Quick test:

sqlite> CREATE TABLE "Test" ("col1" INTEGER, "col2" INTEGER);
sqlite> INSERT INTO "Test" VALUES (0, 1);
sqlite> INSERT INTO "Test" VALUES (1, 1);
sqlite> INSERT INTO "Test" VALUES (1, 2);
sqlite> SELECT count() FROM "Test" WHERE "col2" = 1;
2
sqlite> SELECT count() FROM "Test" WHERE "col2" = 2;
1
sqlite> SELECT count() FROM "Test" WHERE "col2" = 0;
0
sqlite> SELECT count() FROM
...> (SELECT "col1" FROM "Test" WHERE "col2" = 1);
2
sqlite> SELECT count() FROM
...> (SELECT "col1" FROM "Test" WHERE "col2" = 3);
0

That looks like a fairly “magic way” to me—and I have actually used that  
method with nontrivial queries.  Am I missing something?  Too, I know how  
an aggregate function is made; and I don’t see how this *wouldn’t* work  
for an obvious implementation of a count() function.

Very truly,

SAMUEL ADAM ◊ http://certifound.com/ ◊ I read list mail sporadically.
763 Montgomery Road ◊ Hillsborough, NJ  08844-1304 ◊ United States
April 15, 2011 Courtroom Video in re Adam v. Supreme Court of N.J.:
http://www.youtube.com/watch?v=GPw2W2-Ujyc
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Determining how many columns were returned in a query

2011-05-08 Thread Simon Slavin 

On 8 May 2011, at 4:09pm, Simon Slavin wrote:

> On 8 May 2011, at 4:00pm, Sam Carleton wrote:
> 
>> Is there a
>> way to find out the id of a particular column?
> 
> It depends what you think a column's id is.  But SQLite maintains a 
> pseudo-column of INTEGERs called 'id' or 'rowid' (several other names) which 
> you can ask for by name.  For instance
> 
> SELECT rowid,* FROM myTable

Whoops.  Sorry I somehow understood that as 'rowid'.  Columns don't have ids.  
See Jean-Christophe's reply for better details.

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

Re: [sqlite] Determining how many columns were returned in a query

2011-05-08 Thread Simon Slavin 

On 8 May 2011, at 4:00pm, Sam Carleton wrote:

> How does one go about finding out how many rows a query returns?

This was asked earlier this week.  There is no magic way.  Step through the 
rows and count them.

You can, of course, do a preliminary SELECT for 'count(*)' and see what answer 
is returned.

> Is there a
> way to find out the id of a particular column?

It depends what you think a column's id is.  But SQLite maintains a 
pseudo-column of INTEGERs called 'id' or 'rowid' (several other names) which 
you can ask for by name.  For instance

SELECT rowid,* FROM myTable

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

Re: [sqlite] Determining how many columns were returned in a query

2011-05-08 Thread Jean-Christophe Deschamps 

>How does one go about finding out how many rows a query returns?

This is the number of time sqlite3_step can be called successfully 
until it returns SQLITE_DONE.

>Is there a way to find out the id of a particular column?

AFAICT column don't have ids.  You can read column names or alias using 
sqlite3_column_name[16].

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

[sqlite] Determining how many columns were returned in a query

2011-05-08 Thread Sam Carleton 
How does one go about finding out how many rows a query returns?  Is there a
way to find out the id of a particular column?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users