select m.name as TableName,
i.cid as ColumnID,
i.name as ColumnName,
i.type as Affinity,
i.notnull as CanBeNull,
i.dflt_value as DefaultValue,
i.pk as PrimaryKeySeq
from sqlite_master as m,
pragma_table_info(m.name) as i
where m.type='table';
returns Error: near "notnull": syntax error
however, quoting "notnull" works:
select m.name as TableName,
i.cid as ColumnID,
i.name as ColumnName,
i.type as Affinity,
i."notnull" as CanBeNull,
i.dflt_value as DefaultValue,
i.pk as PrimaryKeySeq
from sqlite_master as m,
pragma_table_info(m.name) as i
where m.type='table';
returns:
advisory|0|advisory|integer|0||1
advisory|1|advisoryname|text|1|'Unknown'|0
advlink|0|program|text|1||1
advlink|1|advisory|integer|1||2
crew|0|crew|integer|0||1
crew|1|surname|text|1|''|0
crew|2|givenname|text|1|''|0
crewlink|0|program|text|1||1
crewlink|1|crew|integer|1||3
crewlink|2|role|integer|1||2
...
Is there a way to make the "column names" returned by a pragma (eg,
pragma_table_info) table not be interpreted as a reserved word? While it is
not a bug per-se, it is annoying to have to quote (what one would hope are)
properly composed column names (even though there are many folks who habitually
use column name quoting as a matter-of-course because many ill-conceived tools
generate badly-formed column-names or quote wildly). It one typically only
used well-formed column names, then having to quote is downright ugly ...
---
Life should not be a journey to the grave with the intention of arriving safely
in a pretty and well preserved body, but rather to skid in broadside in a cloud
of smoke, thoroughly used up, totally worn out, and loudly proclaiming "Wow!
What a Ride!"
-- Hunter S. Thompson
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
