Re: [sqlite] pragma_table_info column name is interpreted as reserved work?
On Sun, 12 Feb 2017 12:40:27 -0700 "Keith Medcalf" wrote: > I am aware that it works if you quote the apparently reserved word > "notnull". That's the queer part. I can imagine "not" and "null" as keywords, but not "nonnull". --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] pragma_table_info column name is interpreted as reserved work?
On Sunday, 12 February, 2017 11:03, Domingo Alvarez Duarte wrote: > Hello Keith ! > You can try this that works: > select m.name as TableName, > i.cidas 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'; Yes, I am aware that it works if you quote the apparently reserved word "notnull". That is however ugly beyond belief and quoted identifiers make my eyes bleed (just like Perl :) ). It would be nice if the default column name were not a reserved word ... > On 12/02/17 15:24, Keith Medcalf wrote: > > select m.name as TableName, > > i.cidas ColumnID, > > i.name as ColumnName, > > i.type as Affinity, > > i.notnullas 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'; > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] pragma_table_info column name is interpreted as reserved work?
Hello Keith ! You can try this that works: select m.name as TableName, i.cidas 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'; On 12/02/17 15:24, Keith Medcalf wrote: select m.name as TableName, i.cidas ColumnID, i.name as ColumnName, i.type as Affinity, i.notnullas 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'; ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] pragma_table_info column name is interpreted as reserved work?
select m.name as TableName, i.cidas ColumnID, i.name as ColumnName, i.type as Affinity, i.notnullas 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.cidas 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