sqlite problem - field names missing in select

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
M-2
Reply | Threaded
Open this post in threaded view
|

sqlite problem - field names missing in select

M-2
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite problem - field names missing in select

R Smith-2
In reply to this post by M-2

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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" <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite problem - field names missing in select

Simon Slavin-3
On 23 Feb 2018, at 6:28pm, Brian Curley <[hidden email]> 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

<https://sqlite.org/pragma.html>

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> wrote:

> On 23 Feb 2018, at 6:28pm, Brian Curley <[hidden email]> 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
>
> <https://sqlite.org/pragma.html>
>
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users