Re: [sqlite] pragma_table_info column name is interpreted as reserved work?

[James K. Lowden]

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?

[Keith Medcalf]

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?

[Domingo Alvarez Duarte]

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?

[Keith Medcalf]

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