Re: [sqlite] Working with SQLite-Net in Framework 2.0

[Steve Rogers]

On 7/11/2014 1:02 AM, Joe Mistachkin wrote:

Steve Rogers wrote:

Can the two versions exist in a VS 2010 development environment on the
same machine?
I hope I have clarified that important detail.


I'm not sure as I've never tested that setup.  I do know that only the
setup package for Visual Studio 2010 (which uses the .NET Framework 4)
will allow it to actually make use of the design-time components for
System.Data.SQLite.

For your particular situation, quite a lot depends on whether or not
you need the design-time support for SQLite.

I do not need design-time support for SQLite in VS2010,  even though I 
have installed it.
I have been using SQLite Expert Personal 3 instead of the design-time 
support in VS2010

because it does more than the design -time support.



https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki




On the above linked page, the "Using Native Library Pre-Loading" and
"Deployment Guidelines" sections merit special attention.

I'll check it out.


--
Joe Mistachkin

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


-
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2014.0.4716 / Virus Database: 3986/7832 - Release Date: 07/10/14




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

Re: [sqlite] WITH syntax error

[Dan Kennedy]

On 07/13/2014 01:24 AM, Staffan Tylen wrote:

According to sqlite3 I'm on 3.8.3:

SQLite version 3.8.3 2014-02-03 14:04:11


I remember now. There was a bug regarding compound SELECT statements 
that use CTEs discovered shortly after 3.8.3 was released:


  http://www.sqlite.org/src/info/67bfd59d9087a987
  http://www.sqlite.org/src/info/31a19d11b97088296a

The fix appeared in 3.8.4. If you upgrade, the statement will work.

You'll note that I said the statement "should" work in 3.8.3. Not that 
it does. :)


Dan.












On Sat, Jul 12, 2014 at 8:06 PM, Dan Kennedy  wrote:


On 07/13/2014 12:29 AM, Staffan Tylen wrote:


The following statement is flagged as invalid, so what's the correct way
of
coding it?

WITH A AS (SELECT 'A'),
 B AS (SELECT 'B')
SELECT *
FROM A
UNION
SELECT *
FROM B
;


This statement should work in SQLite 3.8.3 or newer.


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


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


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

Re: [sqlite] WITH syntax error

[Keith Medcalf]

SQLite version 3.8.6 2014-07-07 18:03:38
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .version
SQLite 3.8.6 2014-07-07 18:03:38 1cec1e030035e5253fb7ebbdfe5c1a3029e4e29b
sqlite>   WITH A AS (SELECT 'A'),
   ...>B AS (SELECT 'B')
   ...>   SELECT *
   ...>   FROM A
   ...>   UNION
   ...>   SELECT *
   ...>   FROM B
   ...>   ;
A
B
sqlite>

>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Staffan Tylen
>Sent: Saturday, 12 July, 2014 11:30
>To: sqlite-users@sqlite.org
>Subject: [sqlite] WITH syntax error
>
>The following statement is flagged as invalid, so what's the correct way
>of
>coding it?
>
>  WITH A AS (SELECT 'A'),
>   B AS (SELECT 'B')
>  SELECT *
>  FROM A
>  UNION
>  SELECT *
>  FROM B
>  ;
>
>Staffan
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] WITH syntax error

[RSmith]

On 2014/07/12 20:37, Staffan Tylen wrote:

Ryan

"After your final Select statement, the constructed "WITH" table no longer exists, it's scope is only visible to the select 
following the declaration, so anything after a UNION is a new select and as such cannot refer to anything inside the previous 
select's constructs or clauses."


This is exactly what I've looked for in the documentation, but I've been unable to find any mentioning of this limitation. The 
closest I found was this:


 *

The WITH clause must appear at the beginning of a top-level SELECT 
 statement or at
the beginning of a subquery. The WITH clause cannot be prepended to the 
second or subsequent SELECT statement of a compound
select .

There is no mention here that the WITH clause in not visible to subsequent SELECT statements. Knowing this I'll use your "last 
option" as that seems to do what I'm looking for.

Thanks.


Agreed, this might also have changed since 3.8.3 or may still change - A 
clarification in the documentation is probably a good idea.

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

Re: [sqlite] WITH syntax error

[Staffan Tylen]

Ryan

"After your final Select statement, the constructed "WITH" table no longer
exists, it's scope is only visible to the select following the declaration,
so anything after a UNION is a new select and as such cannot refer to
anything inside the previous select's constructs or clauses."

This is exactly what I've looked for in the documentation, but I've been
unable to find any mentioning of this limitation. The closest I found was
this:


   -

   The WITH clause must appear at the beginning of a top-level SELECT
    statement or at the beginning
   of a subquery. The WITH clause cannot be prepended to the second or
   subsequent SELECT statement of a compound select
   .

There is no mention here that the WITH clause in not visible to subsequent
SELECT statements. Knowing this I'll use your "last option" as that seems
to do what I'm looking for.
Thanks.

Staffan



On Sat, Jul 12, 2014 at 8:26 PM, RSmith  wrote:

>
> On 2014/07/12 19:29, Staffan Tylen wrote:
>
>> The following statement is flagged as invalid, so what's the correct way
>> of
>> coding it?
>>
>>WITH A AS (SELECT 'A'),
>> B AS (SELECT 'B')
>>SELECT *
>>FROM A
>>UNION
>>SELECT *
>>FROM B
>>;
>>
>
> Hi Staffan,
>
> What is wrong with it? Depends what you intended to do? Can you say in
> normal English what you would like to achieve or have the SQL engine return
> from the proposed query?
>
> To demonstrate what I mean, your current query basically says to the
> engine: "Imagine there's 2 boxes in the sky, called A and B without form,
> and we try to stuff a value into each, then show me what is all in box A
> and then, for my next query (which should be appended or UNION'd to the
> previous query), get a list of all things in a Table named B (which doesn't
> exist in the DB as far as we can tell)."
>
> There is no plausible way to do this.
>
> A simple fix as an example might be:
>
> WITH A(x) AS (SELECT 'a'), B(x) AS (SELECT 'b')
>   SELECT A.*, B.* FROM A,B
>
> Which adds a form to both WITH table specifications, populates it with
> values and then ask to list the values next to each other. It is important
> to understand that A and B does not exist in the DB, they are just
> constructs of the imagination of the query and only valid within the scope
> of the single select following the construct.
>
> Another possible solution might be:
>
> WITH A(x) AS (SELECT 'a' UNION SELECT 'b')
>   SELECT A.* FROM A
>
> Which adds form and a union in the table with which added records will be
> produced.
>
> Depends what you want. One thing to note, the structure of a WITH
> statement as given in the documentation boils down to the form:
>
> WITH table(schema) AS (Initial SELECT Statement [UNION Recursive-Select])
> final SELECT stmt.
>
> (Ref: http://www.sqlite.org/lang_with.html)
>
> After your final Select statement, the constructed "WITH" table no longer
> exists, it's scope is only visible to the select following the declaration,
> so anything after a UNION is a new select and as such cannot refer to
> anything inside the previous select's constructs or clauses. This, by the
> way, is true for any union'd select, you cannot refer to defines from
> inside the select preceding the union, it's not just a "WITH" construct
> quirk. (Normally anyway, it may differ slightly between engines, if anyone
> knows more specific, please add a thought).
>
> So to be sure, your query can be interpreted:
>
>   WITH A AS (SELECT 'A'),   -- Make tables A and B without form and try to
> insert literals, which
>B AS (SELECT 'B')-- is a bad convention but still possible in
> SQLite.
>   SELECT *
>   FROM A  -- read only from the one table named A
>   UNION
>   SELECT *-- start a new select
>   FROM B  -- What table B? This second select has no reference to such
> a table.
>   ;
>
>
> As a last option, if you absolutely need to Union the values, a sub-select
> will do the job of keeping the "with" construct relevant to the entire
> select query, maybe something like this:
>
> WITH
> A AS (SELECT 'a'),
> B AS (SELECT 'b')
> SELECT * FROM (
>
> SELECT * FROM A
> UNION
> SELECT * FROM B
> );
>
> Hope this helps!
> Ryan
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WITH syntax error

[RSmith]

On 2014/07/12 19:29, Staffan Tylen wrote:

The following statement is flagged as invalid, so what's the correct way of
coding it?

   WITH A AS (SELECT 'A'),
B AS (SELECT 'B')
   SELECT *
   FROM A
   UNION
   SELECT *
   FROM B
   ;


Hi Staffan,

What is wrong with it? Depends what you intended to do? Can you say in normal English what you would like to achieve or have the SQL 
engine return from the proposed query?


To demonstrate what I mean, your current query basically says to the engine: "Imagine there's 2 boxes in the sky, called A and B 
without form, and we try to stuff a value into each, then show me what is all in box A and then, for my next query (which should be 
appended or UNION'd to the previous query), get a list of all things in a Table named B (which doesn't exist in the DB as far as we 
can tell)."


There is no plausible way to do this.

A simple fix as an example might be:

WITH A(x) AS (SELECT 'a'), B(x) AS (SELECT 'b')
  SELECT A.*, B.* FROM A,B

Which adds a form to both WITH table specifications, populates it with values and then ask to list the values next to each other. It 
is important to understand that A and B does not exist in the DB, they are just constructs of the imagination of the query and only 
valid within the scope of the single select following the construct.


Another possible solution might be:

WITH A(x) AS (SELECT 'a' UNION SELECT 'b')
  SELECT A.* FROM A

Which adds form and a union in the table with which added records will be 
produced.

Depends what you want. One thing to note, the structure of a WITH statement as 
given in the documentation boils down to the form:

WITH table(schema) AS (Initial SELECT Statement [UNION Recursive-Select]) final 
SELECT stmt.

(Ref: http://www.sqlite.org/lang_with.html)

After your final Select statement, the constructed "WITH" table no longer exists, it's scope is only visible to the select following 
the declaration, so anything after a UNION is a new select and as such cannot refer to anything inside the previous select's 
constructs or clauses. This, by the way, is true for any union'd select, you cannot refer to defines from inside the select 
preceding the union, it's not just a "WITH" construct quirk. (Normally anyway, it may differ slightly between engines, if anyone 
knows more specific, please add a thought).


So to be sure, your query can be interpreted:

  WITH A AS (SELECT 'A'),   -- Make tables A and B without form and try to 
insert literals, which
   B AS (SELECT 'B')-- is a bad convention but still possible in SQLite.
  SELECT *
  FROM A  -- read only from the one table named A
  UNION
  SELECT *-- start a new select
  FROM B  -- What table B? This second select has no reference to such a 
table.
  ;


As a last option, if you absolutely need to Union the values, a sub-select will do the job of keeping the "with" construct relevant 
to the entire select query, maybe something like this:


WITH
A AS (SELECT 'a'),
B AS (SELECT 'b')
SELECT * FROM (
SELECT * FROM A
UNION
SELECT * FROM B
);

Hope this helps!
Ryan

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

Re: [sqlite] WITH syntax error

[Staffan Tylen]

According to sqlite3 I'm on 3.8.3:

SQLite version 3.8.3 2014-02-03 14:04:11



On Sat, Jul 12, 2014 at 8:06 PM, Dan Kennedy  wrote:

> On 07/13/2014 12:29 AM, Staffan Tylen wrote:
>
>> The following statement is flagged as invalid, so what's the correct way
>> of
>> coding it?
>>
>>WITH A AS (SELECT 'A'),
>> B AS (SELECT 'B')
>>SELECT *
>>FROM A
>>UNION
>>SELECT *
>>FROM B
>>;
>>
>
> This statement should work in SQLite 3.8.3 or newer.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WITH syntax error

[Dan Kennedy]

On 07/13/2014 12:29 AM, Staffan Tylen wrote:

The following statement is flagged as invalid, so what's the correct way of
coding it?

   WITH A AS (SELECT 'A'),
B AS (SELECT 'B')
   SELECT *
   FROM A
   UNION
   SELECT *
   FROM B
   ;


This statement should work in SQLite 3.8.3 or newer.

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

Re: [sqlite] WITH syntax error

[Petite Abeille]

On Jul 12, 2014, at 7:29 PM, Staffan Tylen  wrote:

> The following statement is flagged as invalid, so what's the correct way of
> coding it?

Flagged by whom? Invalid how?

Either way, from SQLIte point of view, looks legit the way it is.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Questions from a novice - basic browsing of records ina listview.

[RSmith]

On 2014/07/12 14:26, - wrote:

Hello Ryan,

Thanks for your response.   I was writing a lengthy reply when I realized
that most of what I said in it where repetitions of what I have already said
earlier, so I deleted it.

To be honest, its well possible that I currently just can't wrap my head
about the non-strict way SQLite seems to work (its not really what I'm
accustomed to)  ...


Hi Rudy,

The response is a pleasure and as to the wrapping your head around it, we'll gladly assist. I do not mean to re-iterate things 
either, but you add inaccurate statements, so please allow me to be (once more) very clear - SQLite is in no way "non-strict". Maybe 
you meant non-constrictive or constrained? The limits are wide and mean to accommodate as many use-cases as possible, but they are 
very strict, you cannot slip-through one extra column above the limit, as an example.


I understand the impulse needing compartmentalized absolutes, but it isn't viable. You have no problem understanding the road-laws, 
they have limits too, but there are exceptions everywhere. You must drive in a certain lane and your car is not allowed to be wider 
than a lane, except for instance when a truck is delivering a cooling tower that's 3-lanes wide, then we use special escorts and 
traffic control interventions to move the load. There is however no point in making those exceptions part of standard 
road-rule-studies when teaching a teenager to drive - good thing too, because the list of possible and even plausible exceptions 
would dwarf the library of congress.


SQL is the road-system and you are the town-planner. It is your job to figure out the use-case and model the limits to suit it. It 
is SQLite's task to attempt supplying SQL data interfaces for whatever that mold transpires to be.


If you are used to anything less, then please consider this new horizon a step 
in the right direction. :)


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

[sqlite] WITH syntax error

[Staffan Tylen]

The following statement is flagged as invalid, so what's the correct way of
coding it?

  WITH A AS (SELECT 'A'),
   B AS (SELECT 'B')
  SELECT *
  FROM A
  UNION
  SELECT *
  FROM B
  ;

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

Re: [sqlite] Questions from a novice - basic browsing of records ina listview.

[-]

Hello Ryan,

Thanks for your response.   I was writing a lengthy reply when I realized
that most of what I said in it where repetitions of what I have already said
earlier, so I deleted it.

To be honest, its well possible that I currently just can't wrap my head
about the non-strict way SQLite seems to work (its not really what I'm
accustomed to)  ...

Regards,
Rudy Wieser


- Original Message -
From: RSmith 
To: General Discussion of SQLite Database 
Sent: Friday, July 11, 2014 2:24 AM
Subject: Re: [sqlite] Questions from a novice - basic browsing of records
ina listview.


>
> >
> >> What if the table has 10^16 or more items?
> > Is that number within the limits as set by SQLite3 ?  Than its my
intention
> > to handle it.
>
> No, I just made that up out of thin air. SQLite's maximum row limit is
2^63-1 I believe. It is unreachable on current physical media.
>
> > Oh, there are some there, like a SQLITE_MAX_SQL_LENGTH of (max) a gig,
and a SQLITE_MAX_COLUMN of (max) 32767. Together that would
> > mean a maximum column-name length of about 32768 chars//
>
> No, it does not mean that at all. Your inference is not only wrong but
also unneeded, meaning that you are imagining relationships
> where there are none. The limits for max sql length and max column are
very clear, and in no way and by no virtue does it imply that
> the one informs the other. Why do you imagine that this is necessarily so?
>
> To be clear - when you go inside an elevator - you might see a weight
limit stated as "500Kg / 13 Persons". This does not mean the
> limit per person is 500/13=38Kg at all, and there is no reason in the
known universe to imagine that it does. (Good thing too cause
> I won't ever get to use the elevator). It does mean that even if you find
an array of 20Kg children, you still cannot pack more than
> 13 in there, and if you have 5 really big (100Kg+) people hopping on ahead
of you, best to wait for the next one. The limit
> statement is not fuzzy.
>
> > ...//Also, the above SQLITE_MAX_COLUMN can be changed at *compile* time.
What am I supposed to do with my regular API (max 2000
> > columns) when encountering a database made with such an altered API ?
Will it even cause troubles at all ? *That* is what I mean
> > with "vague or fuzzy".
>
> If this was true it would be reason for concern - but the limit you refer
to is a SQL language construct limit, not a table-width
> limit so the worry is not justified - but In a query one might be able to
dream up a set of extracted columns that tops the query
> limit easily even if the underlying tables only sports a few columns. For
this you will need to plan. The paradigm here is to cater
> for what is relevant. Very few people make queries longer than a few
columns, but they might. It's a kind of bell curve, and if the
> users are those lying at the 6th+ standard deviation of column-count
requirements, chances are they will have compiled their own
> sqlite version by now, and if they did not, ask them politely to adhere to
whichever limit you picked. There is no SQLite-imposed
> hard limit (other than the physical), in stead, it supports the wide gamut
of needs that cover the 99% bulk, and people with special
> needs roll their own (using the various compiler directives and the like).
>
> You don't even need to check this, SQLite will do it for you. Send a query
with 101 columns, it will return a
> DUDE_THATS_TOO_MANY_COLUMNS result in stead, the (real) codes being
available in the documentation. But if you like there is nothing
> wrong with setting your own maximum columns and doing your own checking,
but the ability doesn't mean SQLite is soft in the head -
> just that it isn't restrictive and you have some freedom of design.
>
> >> most all of them can be adjusted to suit folks like yourself
> >> who wish to push the limits.
> > I'm afraid you misunderstood. Its not my intention to push those limits,
but
> > I'll be damned if I let my intended basic database-browser refuse to
display
> > a legal table because *I* could not find a way to handle it.
>
> Not misunderstood, just a bit tongue-in-cheek, but the nuance probably
misplaced, I apologise.
>
> > Maybe in the end I still have to conceede defeat and use a few arbitrary
> > limits, taking the risk the program cannot handle every table. But not
> > before I tried my d*mn best not to do let that happen. :-)
>
> There is no such risk. SQLite will open each and every valid table in
existence, and you can query it so long as the query itself
> conforms. In this regard you are quite justified to fear a table with 200
columns and you have a 99 col query limit, so you wont be
> able to ask for every column by name, though * will still work.  One might
say that 99 columns is more than any user might want to
> or be able to really look at... but if you disagree (and even I disagree,
I think probably 200 is closer to a sensible human limit),
> then you might simply decide