On Fri, Apr 04, 2008 at 01:43:24PM -0700, Steven Fisher wrote:
> On 04-Apr-2008, at 1:17 PM, Nicolas Williams wrote:
> > Sure there is:
> >
> > const char *sqlite3_column_decltype(sqlite3_stmt*,int);
> > int sqlite3_column_type(sqlite3_stmt*, int iCol);
>
> This would be useful but, again, that's not at all what I want. I'm
> looking for column NAMES, not contents.
const char *sqlite3_column_name(sqlite3_stmt*, int N);
const void *sqlite3_column_name16(sqlite3_stmt*, int N);
> Maybe it'd be better to explain this with psuedo code.
>
> This is what I want to do:
>
> sqlite3_prepare_v2( db, "SELECT ColumnA,ColumnB FROM ATable;", -1,
> &stmt, &tail );
> int column_a_idx = sqlite3_column_index( stmt, "ColumnA" );
> int column_b_idx = sqlite3_column_index( stmt, "ColumnB" );
> while ( sqlite3_step( db ) == SQLITE_ROW ) {
> sqlite3_column_text( stmt, column_a_idx, avalue );
> sqlite3_column_text( stmt, column_b_idx, bvalue );
> }
> sqlite3_fianlize( stmt );
You can have multiple columns with the same name:
sqlite> create table foo(a,b);
sqlite> insert into foo values (1, 2);
sqlite> .header on
sqlite> select a as c, b as c from foo;
c|c
1|2
sqlite>
So sqlite3_column_index(stmt, "ColumnA") isn't likely to be what you
really want. sqlite3_column_name() seems much more appropriate.
> I'm avoiding hard an expectation here that column a is in position 0,
> and column b in position 1. This doesn't matter for such a simple
> query, but for larger queries future proofing the code from changes to
> queries is just good practice.
>
> This code won't run, though, because sqlite3_column_index doesn't
> exist. I need to write my own. That means I need to replace
> sqlite3_column_index with find_column, which is defined something like
> this:
>
> int find_column( sqlite3_stmt * stmt, const char * name )
> {
> int count = sqlite3_column_count( stmt );
> for ( int i = 0; i < count; i++ ) {
> const char * column = sqlite3_column_name( stmt, i );
> if ( stricmp( column, name ) == 0 )
> return i;
> }
> return -1;
> }
Right, except for the thing about multiple columns with the same name
being OK.
> There's two problems here:
> 1. I need to define something to find the column at all. There's a way
> to find binding indexes by name, so why not columns? I understand the
> need to avoid code bloat, but surely a way to future proof code by not
> having to hard-coding column positions is worth the size delta.
See above.
> 2. I need to use stricmp for comparing column names. I'd rather use
> the same comparison that sqlite3 uses for comparing column NAMES.
Why can't you use strcasecmp()?
IMO a SQLite-specific version of strcasecmp() is only really valuable if
it can deal with user-defined collations. Otherwise what's the point?
You already have straight strcasecmp() implementations elsewhere (even
ones aware of UTF-8 and UTF-16).
Nico
--
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
