Re: [sqlite] [System.Data.SQLite.DLL] Retrieving table names with column names

[Chris Locke]

Thanks Ryan.  I guess I'm used to MS Access (can I mention those words in
this mailing list?) which recognised the 'table name.field name' convention
on all columns regardless.  It made things 'lazy' I guess.  If a table has
20 fields, then it can be a pain listing out every field required.  A
necessary evil, and 'the right thing', but still a pain...

Thanks,
Chris


On Fri, Jul 22, 2016 at 11:33 AM, R Smith  wrote:

>
>
> On 2016/07/21 11:20 PM, Chris Locke wrote:
>
>> I've a table I'm calling recursively.
>>
>> ...
>>
>> I know I can change my SQL statement to be explicit, and select each
>> required field and use AS, but is that the only solution?
>>
>
> It's not so much the "Only" way as it is the "Correct" way. Query planners
> between different engines all pop different values into the column name bit
> returned according to what uses the least CPU cycles (but perhaps still
> indicate the nature of the column) - which is the way we all want it. Once
> you actually NEED the names to be specifically something (as in your case)
> then the SQL standard provides for that by specifying that the Query engine
> *must* return an exact column name where you specify an "AS" clause for a
> selected column.
>
> Hence me saying that this is not so much the /only/ way as it is the
> /correct/ way to ensure your column names are ALWAYS returned exactly as
> you asked for it.
>
> As to your question about it being the Only way? - No - another way would
> be to use a different SQL engine that returns something that you like
> better, or an older version of SQLite when it behaved differently, or put
> your query in a CTE with named columns - but these ways are all silly for
> obvious reasons. Do the "AS" thing - it's how the the rest of us roll... :)
>
> 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] [System.Data.SQLite.DLL] Retrieving table names with column names

[Chris Locke]

Thank you for your prompt reply.  Thats no big issue then and I will code
it explicitly ... didn't want to do that if there was an option / some
other method I was missing.

Thanks,
Chris

On Fri, Jul 22, 2016 at 6:55 AM, Hick Gunter <h...@scigames.at> wrote:

> Short answer: YES.
>
> This question crops up regulary.
>
> The SQL Standard mandates only that column names set with AS be reliably
> returned. Otherwise each implementation is free to choose whatever name it
> deems appropriate, because - by omitting the AS clause - you state that you
> "don't care". The column name may be the unqualified or qualified field
> name or even the text of the expression. It may also change between
> executions of the same query, usually because the "shape" of your data has
> changed enough to make the query planner choose a different sequence of
> joins.
>
> Since your programming environment does seem to care about column names,
> you will have to set them explicitly.
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users-boun...@mailinglists.sqlite.org [mailto:
> sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Chris Locke
> Gesendet: Donnerstag, 21. Juli 2016 23:20
> An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
> Betreff: [sqlite] [System.Data.SQLite.DLL] Retrieving table names with
> column names
>
> I've a table I'm calling recursively.
>
> CREATE TABLE "staff" ( `id` TEXT, `logonName` TEXT, `firstname` TEXT,
> `surname` TEXT, `departmentId` TEXT, `managerId` TEXT,
> `holidayDaysEntitlement` INTEGER, `holidayDaysTaken` REAL, PRIMARY
> KEY(`id`) )
>
> managerId points to the same table, so my join is simply:
>
> select * from staff
> inner join departments on staff.departmentId=departments.id inner join
> staff as managers on staff.managerId=managers.id
>
> This works fine using DB Browser for SQLite, and adding WHERE clauses like
> '
> managers.id='1' ' etc.  (apologies for wrapping that in quotes...)
>
> Anyway.  Using system.data.sqlite.dll in vb.net, only the field names are
> returned.  Using this statement doesn't work:
> dim s1 as string=sqlReader("managers.firstname").ToString
>
> Retrieving the 12th field, gives me just 'firstname'
>  MsgBox(sqlReader.GetName(11))
>
> I know I can change my SQL statement to be explicit, and select each
> required field and use AS, but is that the only solution?
>
>
> Thanks,
> Chris
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: h...@scigames.at
>
> This communication (including any attachments) is intended for the use of
> the intended recipient(s) only and may contain information that is
> confidential, privileged or legally protected. Any unauthorized use or
> dissemination of this communication is strictly prohibited. If you have
> received this communication in error, please immediately notify the sender
> by return e-mail message and delete all copies of the original
> communication. Thank you for your cooperation.
>
>
> ___
> 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] [System.Data.SQLite.DLL] Retrieving table names with column names

[R Smith]

On 2016/07/21 11:20 PM, Chris Locke wrote:

I've a table I'm calling recursively.

...

I know I can change my SQL statement to be explicit, and select each
required field and use AS, but is that the only solution?


It's not so much the "Only" way as it is the "Correct" way. Query 
planners between different engines all pop different values into the 
column name bit returned according to what uses the least CPU cycles 
(but perhaps still indicate the nature of the column) - which is the way 
we all want it. Once you actually NEED the names to be specifically 
something (as in your case) then the SQL standard provides for that by 
specifying that the Query engine *must* return an exact column name 
where you specify an "AS" clause for a selected column.


Hence me saying that this is not so much the /only/ way as it is the 
/correct/ way to ensure your column names are ALWAYS returned exactly as 
you asked for it.


As to your question about it being the Only way? - No - another way 
would be to use a different SQL engine that returns something that you 
like better, or an older version of SQLite when it behaved differently, 
or put your query in a CTE with named columns - but these ways are all 
silly for obvious reasons. Do the "AS" thing - it's how the the rest of 
us roll... :)


Cheers,
Ryan

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

Re: [sqlite] [System.Data.SQLite.DLL] Retrieving table names with column names

[Hick Gunter]

Short answer: YES.

This question crops up regulary.

The SQL Standard mandates only that column names set with AS be reliably 
returned. Otherwise each implementation is free to choose whatever name it 
deems appropriate, because - by omitting the AS clause - you state that you 
"don't care". The column name may be the unqualified or qualified field name or 
even the text of the expression. It may also change between executions of the 
same query, usually because the "shape" of your data has changed enough to make 
the query planner choose a different sequence of joins.

Since your programming environment does seem to care about column names, you 
will have to set them explicitly.

-Ursprüngliche Nachricht-
Von: sqlite-users-boun...@mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Chris Locke
Gesendet: Donnerstag, 21. Juli 2016 23:20
An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Betreff: [sqlite] [System.Data.SQLite.DLL] Retrieving table names with column 
names

I've a table I'm calling recursively.

CREATE TABLE "staff" ( `id` TEXT, `logonName` TEXT, `firstname` TEXT, `surname` 
TEXT, `departmentId` TEXT, `managerId` TEXT, `holidayDaysEntitlement` INTEGER, 
`holidayDaysTaken` REAL, PRIMARY
KEY(`id`) )

managerId points to the same table, so my join is simply:

select * from staff
inner join departments on staff.departmentId=departments.id inner join staff as 
managers on staff.managerId=managers.id

This works fine using DB Browser for SQLite, and adding WHERE clauses like '
managers.id='1' ' etc.  (apologies for wrapping that in quotes...)

Anyway.  Using system.data.sqlite.dll in vb.net, only the field names are 
returned.  Using this statement doesn't work:
dim s1 as string=sqlReader("managers.firstname").ToString

Retrieving the 12th field, gives me just 'firstname'
 MsgBox(sqlReader.GetName(11))

I know I can change my SQL statement to be explicit, and select each required 
field and use AS, but is that the only solution?


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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

[sqlite] [System.Data.SQLite.DLL] Retrieving table names with column names

[Chris Locke]

I've a table I'm calling recursively.

CREATE TABLE "staff" ( `id` TEXT, `logonName` TEXT, `firstname` TEXT,
`surname` TEXT, `departmentId` TEXT, `managerId` TEXT,
`holidayDaysEntitlement` INTEGER, `holidayDaysTaken` REAL, PRIMARY
KEY(`id`) )

managerId points to the same table, so my join is simply:

select * from staff
inner join departments on staff.departmentId=departments.id
inner join staff as managers on staff.managerId=managers.id

This works fine using DB Browser for SQLite, and adding WHERE clauses like '
managers.id='1' ' etc.  (apologies for wrapping that in quotes...)

Anyway.  Using system.data.sqlite.dll in vb.net, only the field names are
returned.  Using this statement doesn't work:
dim s1 as string=sqlReader("managers.firstname").ToString

Retrieving the 12th field, gives me just 'firstname'
 MsgBox(sqlReader.GetName(11))

I know I can change my SQL statement to be explicit, and select each
required field and use AS, but is that the only solution?


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