Re: [sqlite] sqlite problem - field names missing in select

[Brian Curley]

now that is awesome...

Of course, I first attempted it on SQLiteStudio using its 3.15 version of
sqlite. Looks like it missed it by one major release level; documentation
says it arrived as of 3.16.

I've tried it on my CLI version, which is 3.19+.

Good stuff. Thanks, Simon.

Regards.

Brian P Curley



On Fri, Feb 23, 2018 at 1:44 PM, Simon Slavin  wrote:

> On 23 Feb 2018, at 6:28pm, Brian Curley  wrote:
>
> > If there's pragmas like table_info available...can these be made
> available
> > for parsing, or used as virtual tables for selection, etc?
>
> In current versions of SQLite, you can use the results of PRAGMAs as if
> they are tables.  See the "PRAGMA functions" section of
>
> 
>
> For example, information about the columns in an index can be read using
> the index_info pragma as follows:
>
> sqlite> CREATE TABLE members (name TEXT COLLATE NOCASE,
>   phone TEXT COLLATE NOCASE,
>   weekrank INTEGER,
>   yearrank INTEGER);
> sqlite> CREATE INDEX m_ry ON members (yearrank, weekrank);
> sqlite> .headers ON
> sqlite> .mode column
> sqlite> PRAGMA index_info(m_ry);
> seqno   cid name
> --  --  --
> 0   3   yearrank
> 1   2   weekrank
>
> The same content can be read using a SELECT command:
>
> sqlite> SELECT * FROM pragma_index_info('m_ry');
> seqno   cid name
> --  --  --
> 0   3   yearrank
> 1   2   weekrank
>
> And, as with any other SELECT, you can modify the way the results are
> returned in the SELECT command ...
>
> sqlite> SELECT * FROM pragma_index_info('m_ry') ORDER BY cid;;
> seqno   cid name
> --  --  --
> 1   2   weekrank
> 0   3   yearrank
>
> Simon.
> ___
> 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] sqlite problem - field names missing in select

[Simon Slavin]

On 23 Feb 2018, at 6:28pm, Brian Curley  wrote:

> If there's pragmas like table_info available...can these be made available
> for parsing, or used as virtual tables for selection, etc?

In current versions of SQLite, you can use the results of PRAGMAs as if they 
are tables.  See the "PRAGMA functions" section of



For example, information about the columns in an index can be read using the 
index_info pragma as follows:

sqlite> CREATE TABLE members (name TEXT COLLATE NOCASE,
  phone TEXT COLLATE NOCASE,
  weekrank INTEGER,
  yearrank INTEGER);
sqlite> CREATE INDEX m_ry ON members (yearrank, weekrank);
sqlite> .headers ON
sqlite> .mode column
sqlite> PRAGMA index_info(m_ry);
seqno   cid name  
--  --  --
0   3   yearrank  
1   2   weekrank  

The same content can be read using a SELECT command:

sqlite> SELECT * FROM pragma_index_info('m_ry');
seqno   cid name  
--  --  --
0   3   yearrank  
1   2   weekrank  

And, as with any other SELECT, you can modify the way the results are returned 
in the SELECT command ...

sqlite> SELECT * FROM pragma_index_info('m_ry') ORDER BY cid;;
seqno   cid name  
--  --  --
1   2   weekrank  
0   3   yearrank  

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

Re: [sqlite] sqlite problem - field names missing in select

[Brian Curley]

Not exactly sure of the OP's full issue.

This does arise on occasion though, especially for when you try to maintain
a self-sustaining database. Ignoring the API for a moment, there is some
rationale for keeping metadata available for use. My primary use is as a
CLI toolkit, if not as a desktop workbench to convert data.

If there's pragmas like table_info available...can these be made available
for parsing, or used as virtual tables for selection, etc? (These could
facilitate some of the metadata questions, probably without much overhead,
to my knowledge at least.)

I love that sqlite_master can be wrangled to an extent; I can generate sql
to get table counts en masse, for example, but not without passing the
generated sql between UIs. If there's a tabular option available, it would
eliminate external reliance on code, if not simulate dynamic sql in some
cases.

On the other hand, if I am missing something in front of my face...I'm sure
that someone here will make me aware.

Regards.

Brian P Curley



On Feb 23, 2018 1:06 PM, "R Smith"  wrote:

>
> On 2018/02/23 2:46 PM, M wrote:
>
>> sqlite has an integral problem, field names cannot be selected each one
>> inside a program, there is way to do it, but it is not straight and
>> complicates the software programs, and makes the program complicated and
>> not readble.
>>
>> when you try to do something with the selected fields/columns, a lot of
>> code can be reduced, if only there where field names - the ability to work
>> with them directly.
>>
>
> Let me get this straight: Are you saying the query "SELECT Field1, Field2,
> Field3 FROM myTable;" does not work for you, but "SELECT * FROM myTable;"
> does?
>
> or, are you saying that both queries work fine, but you have no way of
> telling what the names (Field1, Field2, etc.) are once you receive the data?
>
> Both of these are of course utterly possible in SQLite and required in
> principle by any SQL RDBMS system - so if possible, could you perhaps post
> some small bit of code that illustrates what happens for you and say what
> it is that you expect to happen/be possible but that isn't working (even
> perhaps code that works for MySQL/PostGres/etc.).  Anything to make more
> clear what you mean so we will be able to show how it works in sqlite.
>
> Kindly also mention your programming platform, OS and any other relevant
> information you can think of. The more you say, the easier for us to help.
>
> Cheers,
> Ryan
>
>
> ___
> 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] sqlite problem - field names missing in select

[R Smith]

On 2018/02/23 2:46 PM, M wrote:
sqlite has an integral problem, field names cannot be selected each 
one inside a program, there is way to do it, but it is not straight 
and complicates the software programs, and makes the program 
complicated and not readble.


when you try to do something with the selected fields/columns, a lot 
of code can be reduced, if only there where field names - the ability 
to work with them directly.


Let me get this straight: Are you saying the query "SELECT Field1, 
Field2, Field3 FROM myTable;" does not work for you, but "SELECT * FROM 
myTable;" does?


or, are you saying that both queries work fine, but you have no way of 
telling what the names (Field1, Field2, etc.) are once you receive the data?


Both of these are of course utterly possible in SQLite and required in 
principle by any SQL RDBMS system - so if possible, could you perhaps 
post some small bit of code that illustrates what happens for you and 
say what it is that you expect to happen/be possible but that isn't 
working (even perhaps code that works for MySQL/PostGres/etc.).  
Anything to make more clear what you mean so we will be able to show how 
it works in sqlite.


Kindly also mention your programming platform, OS and any other relevant 
information you can think of. The more you say, the easier for us to help.


Cheers,
Ryan


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

Re: [sqlite] sqlite problem - field names missing in select

[J Decker]

https://sqlite.org/c3ref/column_name.html
You can get the column names.

On Fri, Feb 23, 2018 at 4:46 AM, M  wrote:

> sqlite has an integral problem, field names cannot be selected each one
> inside a program, there is way to do it, but it is not straight and
> complicates the software programs, and makes the program complicated and
> not readble.
>
> when you try to do something with the selected fields/columns, a lot of
> code can be reduced, if only there where field names - the ability to work
> with them directly.
>
>
> ---
> This email has been checked for viruses by Avast antivirus software.
> https://www.avast.com/antivirus
>
> ___
> 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

[sqlite] sqlite problem - field names missing in select

[M]

sqlite has an integral problem, field names cannot be selected each one 
inside a program, there is way to do it, but it is not straight and 
complicates the software programs, and makes the program complicated and 
not readble.


when you try to do something with the selected fields/columns, a lot of 
code can be reduced, if only there where field names - the ability to 
work with them directly.



---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

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