Re: [sqlite] [System.Data.SQLite.DLL] Retrieving table names with column names
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
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 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 > 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
[sqlite] Bug 6c266900 - Is there still interest?
I've reproduced it with the latest snapshot and am willing to work on this in case there is still interest in it being fixed. And if the team doesn't think it demands an overall architectural rewrite, of course. :-) Ref.: http://www.sqlite.org/src/tktview?name=6c266900a2 Snapshot used: https://www.sqlite.org/snapshot/sqlite-snapshot-201607151001.tar.gz ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The upcoming "pi" release of SQLite
On 23/07/16 08:16, Richard Hipp wrote: > Draft change log: https://www.sqlite.org/draft/releaselog/3_14_0.html Please please don't make the new trace/profile API expand the SQL by default. There are two problems with expanding by default: - The text no longer matches what the programmer had in their code. You can't grep for what trace reports if it is always expanded. Heck you can't even tell if two queries are the same except the bound parameters without fairly sophisticated parsing. It is best practise to use bound parameters and it should be encouraged, so penalising that is unhelpful. - It consumes considerably more memory and cpu, which makes it less likely to be something you normally use. That significantly decreases the value of the feature versus using it most of the time. Essentially it is a one way un-reversible conversion of what the programmer wrote, and hence significantly less helpful. Roger signature.asc Description: OpenPGP digital signature ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_scrub_backup() not mentioned in 3.14 release log
Absolutely, thanks for the catch! This link might be even better as it always points to the latest version on trunk: https://www.sqlite.org/src/artifact?ci=trunk&filename=ext/misc/scrub.c Ralf On 24.07.2016 15:19, Simon Slavin wrote: By which you mean, of course, http://www.sqlite.org/cgi/src/artifact/ea0903701e3ac02b ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_scrub_backup() not mentioned in 3.14 release log
On 24 Jul 2016, at 11:38am, Ralf Junker wrote: > > http://127.0.0.1:8080/artifact/ea0903701e3ac02b > > sqlite3_scrub_backub() is not mentioned in the 3.14 draft release log nor > documentation. By which you mean, of course, http://www.sqlite.org/cgi/src/artifact/ea0903701e3ac02b Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The upcoming "pi" release of SQLite
Hello, It seems that the problem reported here: http://www.mail-archive.com/sqlite-users%40mailinglists.sqlite.org/msg97627.html is not fixed. SQLite version 3.14.0 2016-07-23 05:22:02 sqlite> select 0x1g; 1 sqlite> Regards. On Sat, Jul 23, 2016 at 5:16 PM, Richard Hipp wrote: > The next release of SQLite will be the "pi" release - version 3.14. > It will probably occur within the next two weeks. > > Draft change log: https://www.sqlite.org/draft/releaselog/3_14_0.html > > Code snapshot: > https://www.sqlite.org/snapshot/sqlite-snapshot-201607230522.tar.gz > > Testing and (especially) documentation work is on-going. Please try > out the snapshot. Look over the changes. Speak up loudly and quickly > if you have any issues. If you do not want to post to this mailing > list, you can send feedback directly to my email address shown below. > > Thanks. > > -- > D. Richard Hipp > d...@sqlite.org > ___ > 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] sqlite3_scrub_backup() not mentioned in 3.14 release log
On 23.07.2016 17:16, Richard Hipp wrote: Look over the changes. Speak up loudly and quickly if you have any issues. http://127.0.0.1:8080/artifact/ea0903701e3ac02b sqlite3_scrub_backub() is not mentioned in the 3.14 draft release log nor documentation. * Will it be part of the next version? * Is it safe to use for production? * It is officially part of SQLite? Ralf ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users