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  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?

[Thiago Arrais]

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

[Roger Binns]

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

[Ralf Junker]

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=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

[Simon Slavin]

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

[gwenn]

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

[Ralf Junker]

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