Re: [sqlite] Kind of pivot table

[Richard Damon]

On 7/9/18 12:48 PM, Jens Alfke wrote:
>> On Jul 7, 2018, at 11:49 PM, Keith Medcalf  wrote:
>>
>> Why not use MOD (%) as in
>>
>> ABS(RANDOM() % 6)
> Because modulo bias. If the RHS doesn't evenly divide the size of the range 
> of RANDOM(), some results will be slightly more likely than others.
>
> https://zuttobenkyou.wordpress.com/2012/10/18/generating-random-numbers-without-modulo-bias/
>  
> 
>
> Of course in this case, where the range of RANDOM() is probably 2^32, the 
> bias will be very small. But in some use cases the bias can be magnified by 
> subsequent operations and can still skew the eventual result. A Google search 
> turns up several hits that promise fairer algorithms.
>
> https://www.google.com/search?client=safari=en=random+modulo+bias=UTF-8=UTF-8
>  
> 
>
> —Jens
But ANY random number generator that generates a finite number of
results is almost certainly going to have a similar bias, as you divide
up those numbers into bins. To get rid of the bias you generally need to
throw out some values so you have an equal number in each bin (unless
you luck out and start having an even number in each bin). Your second
solution did this. If your first solution was good enough, so should
have MOD.

-- 
Richard Damon

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

Re: [sqlite] I'm getting a compiler error on a SQLIte build on Ubuntu 16.04 (which surprises me)

[David Burgess]

I'm using gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609

on 64 bit.

I have built both 64 and 32 bit versions from sqlite-src-324
No problems.

Check your build-essential install?

On Tue, Jul 10, 2018 at 1:05 AM, Dan Kennedy  wrote:
> On 07/09/2018 09:26 PM, Rob Willett wrote:
>>
>> Dan,
>>
>> Thanks for the information. We did try that first, failed and just assumed
>> we were idiots and went to the the other download.
>>
>> Just downloaded it again, so we can check and be on the safe side, and we
>> get the same issue.
>>
>> cp /jambuster/src/sqlite-src-324/ext/session/sqlite3session.h .
>> ./libtool --mode=compile --tag=CC gcc -g -O2 -DSQLITE_OS_UNIX=1 -I.
>> -I/jambuster/src/sqlite-src-324/src
>> -I/jambuster/src/sqlite-src-324/ext/rtree
>> -I/jambuster/src/sqlite-src-324/ext/icu
>> -I/jambuster/src/sqlite-src-324/ext/fts3
>> -I/jambuster/src/sqlite-src-324/ext/async
>> -I/jambuster/src/sqlite-src-324/ext/session -D_HAVE_SQLITE_CONFIG_H
>> -DBUILD_sqlite -DNDEBUG -I/usr/include/tcl8.6 -DSQLITE_THREADSAFE=1
>> -DSQLITE_HAVE_ZLIB=1  -DSQLITE_TEMP_STORE=1 -c sqlite3.c
>> libtool: compile:  gcc -g -O2 -DSQLITE_OS_UNIX=1 -I.
>> -I/jambuster/src/sqlite-src-324/src
>> -I/jambuster/src/sqlite-src-324/ext/rtree
>> -I/jambuster/src/sqlite-src-324/ext/icu
>> -I/jambuster/src/sqlite-src-324/ext/fts3
>> -I/jambuster/src/sqlite-src-324/ext/async
>> -I/jambuster/src/sqlite-src-324/ext/session -D_HAVE_SQLITE_CONFIG_H
>> -DBUILD_sqlite -DNDEBUG -I/usr/include/tcl8.6 -DSQLITE_THREADSAFE=1
>> -DSQLITE_HAVE_ZLIB=1 -DSQLITE_TEMP_STORE=1 -c sqlite3.c  -fPIC -DPIC -o
>> .libs/sqlite3.o
>> gcc: internal compiler error: Killed (program cc1)
>> Please submit a full bug report,
>> with preprocessed source if appropriate.
>> See  for instructions.
>> Makefile:715: recipe for target 'sqlite3.lo' failed
>>
>> ./configure seems OK, tcl looks OK (which was our worry), we think there
>> is a genuine bug in the compiler. . We still need the sql_analyzer
>> program as well :) Its one of those days, I have bad compilers and the UK
>> government could fall.
>
>
>
> After running [./configure], open the generated Makefile and remove all the
> "-O2" switches you can find. Then run [make sqlite3_analyzer].
>
> You'll get an un-optimized binary of course, but there's a good chance this
> will bypass the compiler bug. And it won't make much difference unless your
> databases are really big.
>
> Dan.
>
>
>
>
>
>
>
>>
>> Rob
>>
>> On 9 Jul 2018, at 15:02, Dan Kennedy wrote:
>>
>>> On 07/09/2018 03:56 PM, rob.sql...@robertwillett.com wrote:

 Hi

 I'm trying to build the SQLite packages from the source. I **only** want
 it to get a copy of sqlite_analyse which for some reason doesn't appear to
 be easily available as a compiled option. The download package doesn't work
 on Ubuntu 16.04 and as far as we can see, there are no other downloadable
 binaries that don't come from China. For some reason, I don't really want 
 to
 download something from Beijing
>>>
>>>
>>> The Linux binaries on sqlite.org are for 32-bit systems. For them to work
>>> on Ubuntu 16.04, you probably need to install multiarch support.
>>>
>>> Or, to build sqlite3_analyzer from source you need the full source
>>> distribution:
>>>
>>>   https://www.sqlite.org/2018/sqlite-src-324.zip
>>>
>>> Dan.
>>>
>>>
>>>

 The full steps I followed are at the very bottom of this e-mail. They
 are a direct copy from my terminal output.

 **TLDR Summary**

 1. OS is a reasonably fresh (< 4 weeks old) Ubuntu 16.04 LTS Server
 running under OpenVZ on a commercial ISP. It's patched to the current
 levels. I don't think this is a problem as we don't have any other issues
 but...

 2. SQLite3 is already installed as the normal (and older) SQLIte that is
 distributed with Ubuntu 16.04 LTS

 3. gcc is installed. I have created a small C program to test that it
 can compile (Hello World) and it's fine. (gcc (Ubuntu
 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609)

 4. Downloaded https://www.sqlite.org/2018/sqlite-autoconf-324.tar.gz
 through wget.

 5. Checked the sha1sum and it matches the sqlite page.

 6. Untarred the file into it's own directory

 7. cd'ed to directory and ran ./configure.

 8. Checked output of configure and nothing seemed wrong. Details are
 below.

 9. Ran make and get a compiler failure!  (see below). I'm gobsmacked
 that the compiler has failed to be honest. So my first assumption is that 
 we
 have cocked up something. However we've googled and checked and can't see
 anybody with a similar issue. I suspect that one of the Define statements 
 is
 not playing nicely but not wholly sure which one OR we are missing a 
 package
 that needs to be installed, e.g. TCL x,y or z.

 

Re: [sqlite] Round-tripping SQLite back and forth between text representation.

[Simon Slavin]

On 10 Jul 2018, at 1:52am, Randall Smith  wrote:

> I'm curious if there is some standard or normal way to convert a SQLite DB to 
> a text representation, and then recreate the DB content from the text.  
> Naively, this seems hard or impossible as a general problem, but perhaps I am 
> missing something.

Yep.  It's done a lot, to convert a database to a different SQL engine, or 
rescue data from a corrupt database.  See section 10 of



For more information read the documentation about the '.dump' and '.read' 
commands on the same page.  Or ask here.

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

Re: [sqlite] Round-tripping SQLite back and forth between text representation.

[Keith Medcalf]

.dump in the command line shell?


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Randall Smith
>Sent: Monday, 9 July, 2018 18:53
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Round-tripping SQLite back and forth between text
>representation.
>
>I'm curious if there is some standard or normal way to convert a
>SQLite DB to a text representation, and then recreate the DB content
>from the text.  Naively, this seems hard or impossible as a general
>problem, but perhaps I am missing something.
>
>Thanks in advance for any advice or suggestions.
>
>Randall.
>___
>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] Round-tripping SQLite back and forth between text representation.

[J Decker]

does it have to be text?  There was serialization added to sqlite
https://www.sqlite.org/c3ref/serialize.html

On Mon, Jul 9, 2018 at 5:52 PM Randall Smith 
wrote:

> I'm curious if there is some standard or normal way to convert a SQLite DB
> to a text representation, and then recreate the DB content from the text.
> Naively, this seems hard or impossible as a general problem, but perhaps I
> am missing something.
>
> Thanks in advance for any advice or suggestions.
>
> Randall.
> ___
> 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] Round-tripping SQLite back and forth between text representation.

[Randall Smith]

I'm curious if there is some standard or normal way to convert a SQLite DB to a 
text representation, and then recreate the DB content from the text.  Naively, 
this seems hard or impossible as a general problem, but perhaps I am missing 
something.

Thanks in advance for any advice or suggestions.

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

Re: [sqlite] CHECK IGNORE?

[Richard Hipp]

On 7/9/18, R Smith  wrote:
> a - Does it affect the UPSERT statement's ON CONFLICT clause? i.e. does
> INSERT ON CONFLICT...  trigger the conflict resolution when the
> CHECK fails?

No UPSERT only works for uniqueness constraints (UNIQUE or PRIMARY KEY).


> b - If so, would it be real hard to make it so for column ... CHECK(...)
> ON CONFLICT... too?
>

We follow the lead of PostgreSQL.  If you can convince the PostgreSQL
devs to support upsert on check constraints, we will consider it.
-- 
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

Re: [sqlite] CHECK IGNORE?

[R Smith]

On 2018/07/10 12:53 AM, Richard Hipp wrote:

CREATE TABLE t1(x INT, CHECK(x!=5) ON CONFLICT IGNORE);
However, the parser silently discards the ON CONFLICT clause.  I think
this has always been the case.


Well, I feel embarrassed, I have used this CHECK conflict clause a good 
number of times (usually the FAIL option) and simply never tested it for 
actual in-use failure, and I'm usually the first to warn about the 
dangers of assumption. :)


Two questions:
a - Does it affect the UPSERT statement's ON CONFLICT clause? i.e. does 
INSERT ON CONFLICT...  trigger the conflict resolution when the 
CHECK fails?
b - If so, would it be real hard to make it so for column ... CHECK(...) 
ON CONFLICT... too?


Thanks,
Ryan

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

Re: [sqlite] CHECK IGNORE?

[Richard Hipp]

On 7/9/18, Thomas Kurz  wrote:
> Hello,
>
> is there a way to have Sqlite ignore check violations?
>
> I would like to do:
>
> CREATE TABLE  (name TEXT NOT NULL CHECK (name<>'') ON CONFLICT IGNORE
>
> but the "on conflict" is not accepted here.

It does not appear that SQLite has ever acted upon "ON CONFLICT"
exceptions on CHECK constraints.  Such clauses are accepted for CHECK
constraints not associated with a particular column.  For example:

   CREATE TABLE t1(x INT, CHECK(x!=5) ON CONFLICT IGNORE);

However, the parser silently discards the ON CONFLICT clause.  I think
this has always been the case.
-- 
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

Re: [sqlite] CHECK IGNORE?

[Simon Slavin]

On 9 Jul 2018, at 10:21pm, Thomas Kurz  wrote:

> However, this is a bit confusing as e.g. this works fine:
> 
> CREATE TABLE test (col1 TEXT UNIQUE ON CONFLICT IGNORE);

There is actually a way to do what you want.  You do it with a TRIGGER which 
does the test and drops the offending row using RAISE (IGNORE) .



However, this may be horribly confusing to anyone else who has to understand 
your programming.  They may be trying to insert a row and magically nothing 
happens.  It may be better to have the IGNORE command in the INSERT command 
that they will be looking at rather than in a part of the schema they may not 
be thinking about.

On the other hand, it might better suit the way your software works.

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

Re: [sqlite] CHECK IGNORE?

[Simon Slavin]

On 9 Jul 2018, at 10:21pm, Thomas Kurz  wrote:

> However, this is a bit confusing as e.g. this works fine:
> 
> CREATE TABLE test (col1 TEXT UNIQUE ON CONFLICT IGNORE);

By 'works fine' do you mean you didn't get a syntax error, or do you mean that 
the clause actually does what you want, both to accept and reject new rows ?  
The diagrams on



suggest that your clause is not handled correctly.

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

Re: [sqlite] CHECK IGNORE?

[Thomas Kurz]

> Yes.  The ON CONFLICT clause goes in the INSERT command.

Ok, thank you very much.

However, this is a bit confusing as e.g. this works fine:

CREATE TABLE test (col1 TEXT UNIQUE ON CONFLICT IGNORE);

May I suggest the appropriate extension for CHECK ON CONFLICT IGNORE or is 
there a thoughtful decision for not having this construct?

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

Re: [sqlite] CHECK IGNORE?

[Simon Slavin]

On 9 Jul 2018, at 10:00pm, Thomas Kurz  wrote:

> I get a "syntax error near on":
> CREATE TABLE test (col1 TEXT CHECK (col1<>'') ON CONFLICT IGNORE);
> 
> Am I using a wrong syntax?

Yes.  The ON CONFLICT clause goes in the INSERT command.

CREATE TABLE test (col1 TEXT CHECK (col1<>''));
INSERT OR IGNORE INTO test (col1) VALUES ('hello');

An alternative, recently added so available only in recent versions of SQLite3, 
is to do this:

CREATE TABLE test (col1 TEXT CHECK (col1<>''));
INSERT INTO test (col1) VALUES ('hello') ON CONFLICT DO NOTHING;

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

Re: [sqlite] CHECK IGNORE?

[Thomas Kurz]

Thank you, but I think there is a misunderstanding. I do want the constraint to 
be checked, but I want to ignore the attempt to insert values that would 
violate the check constraint.

According to the "on conflict" documentation 
(https://sqlite.org/lang_conflict.html), "The ON CONFLICT clause applies to 
UNIQUE, NOT NULL, CHECK, and PRIMARY KEY constraints."

But I when I try this, I get a "syntax error near on":
CREATE TABLE test (col1 TEXT CHECK (col1<>'') ON CONFLICT IGNORE);

Am I using a wrong syntax? Or am I misinterpreting the documentation?

Kind regards,
Thomas


- Original Message - 
From: Richard Hipp 
To: SQLite mailing list 
Sent: Monday, July 9, 2018, 22:03:29
Subject: [sqlite] CHECK IGNORE?

On 7/9/18, Thomas Kurz  wrote:
> is there a way to have Sqlite ignore check violations?


https://www.sqlite.org/pragma.html#pragma_ignore_check_constraints



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

Re: [sqlite] CHECK IGNORE?

[Richard Hipp]

On 7/9/18, Thomas Kurz  wrote:
> is there a way to have Sqlite ignore check violations?
>

https://www.sqlite.org/pragma.html#pragma_ignore_check_constraints



-- 
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] CHECK IGNORE?

[Thomas Kurz]

Hello,

is there a way to have Sqlite ignore check violations?

I would like to do:

CREATE TABLE  (name TEXT NOT NULL CHECK (name<>'') ON CONFLICT IGNORE

but the "on conflict" is not accepted here.

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

[sqlite] Feature request: degenerates in Lemon's report

[Cezary H. Noweta]

Hello,

Could you allow an outputting of autoreduced states when they produce 
conflicts? I have noticed that such errors are caused by small and 
hard-to-find remnants in a grammar file. There is no other way to detect 
such errors. A few lines are getting the problem off (function 
lemon.c:ReportOutput):


==
--- "../../sqlite-src-324/tool/lemon.c"	2018-06-04 
21:51:19.0 +0200

+++ lemon.c 2018-07-09 19:58:39.852430500 +0200
@@ -3294,9 +3740,19 @@

   fp = file_open(lemp,".out","wb");
   if( fp==0 ) return;
-  for(i=0; inxstate; i++){
+  for(i=0; instate; i++){
 stp = lemp->sorted[i];
-fprintf(fp,"State %d:\n",stp->statenum);
+if( i>=lemp->nxstate ){
+  for(ap=stp->ap; ap!=0; ap=ap->next){
+if( ap->type==SSCONFLICT ) break;
+if( ap->type==SRCONFLICT ) break;
+if( ap->type==RRCONFLICT ) break;
+  }
+  if( ap==0 ) continue;
+  fprintf(fp,"Degenerated state %d:\n",stp->statenum);
+} else {
+  fprintf(fp,"State %d:\n",stp->statenum);
+}
 if( lemp->basisflag ) cfp=stp->bp;
 else  cfp=stp->cfp;
 while( cfp ){
==

-- best regards

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

Re: [sqlite] Kind of pivot table

[Jens Alfke]

> On Jul 7, 2018, at 11:49 PM, Keith Medcalf  wrote:
> 
> Why not use MOD (%) as in
> 
> ABS(RANDOM() % 6)

Because modulo bias. If the RHS doesn't evenly divide the size of the range of 
RANDOM(), some results will be slightly more likely than others.

https://zuttobenkyou.wordpress.com/2012/10/18/generating-random-numbers-without-modulo-bias/
 


Of course in this case, where the range of RANDOM() is probably 2^32, the bias 
will be very small. But in some use cases the bias can be magnified by 
subsequent operations and can still skew the eventual result. A Google search 
turns up several hits that promise fairer algorithms.

https://www.google.com/search?client=safari=en=random+modulo+bias=UTF-8=UTF-8
 


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

Re: [sqlite] What happens when a call contains two SQL statement

[Simon Slavin]

On 9 Jul 2018, at 4:13pm, Simon Slavin  wrote:

> You can find (especially if you're willing to pay) standards for SQL since 
> the 1992 one: 1999, 2003, 2006, 2008 etc..  However, they quickly became so 
> large and complicated that no implementation of SQL implemented as much of 
> the later standards as was done for the 1992 version.

I wanted to emphasise a point that Thomas Kurz made.  The 1992 standard was 
made publicly available for free.  Therefore many implementors got hold of it 
and followed it, and discussion of behaviour could refer back to the standard 
to clarify what implementations should and should not be doing.  It's fully in 
the spirit of Open Standard.

The 2008 version of the standard is not bad.  But you're meant to pay the ISO 
for a copy, and there are many limitations on posting quotes in public.  As you 
can imagine, this greatly hampers discussion of behaviour and identification of 
bugs.

Part 1 of the current standard, ISO/IEC 9075-1:2016, for example, is 78 pages 
long and costs CHF178 (= 150 Euro = US$180).  I didn't look up all the prices, 
but part 14 (sic) is 444 (sic) pages long and costs CHF198 (= 170 Euro = 
US$200).  I would encounter some difficulty in writing software to conform to a 
444 page document if I couldn't discuss that document with people outside my 
organisation.

Far from an Open Standard.  Which is why you will rarely see it quoted. 

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

Re: [sqlite] What happens when a call contains two SQL statement

[Simon Slavin]

On 9 Jul 2018, at 1:57pm, Aaron Elkins  wrote:

> Thank you for the interesting explanations for SQL specific cases, and I am 
> also interested in the SQL standard document after reading your email, can 
> you point me to the right place? 

SQL-86 was the first widely-adopted SQL standard.  However later standards 
added much-needed features to the language, giving it its dominant position 
throughout database programming.  The 1992 version of SQL was the first and 
last document for what has become standard SQL:



You will note, for example, that there are still no DATE, TIME or DATETIME 
types.  If you look at a later standard and don't know why it's weird, go back 
to this 1992 standard and see how the things in this standard guided what 
changes could be made without breaking compatibility.

You can find (especially if you're willing to pay) standards for SQL since the 
1992 one: 1999, 2003, 2006, 2008 etc..  However, they quickly became so large 
and complicated that no implementation of SQL implemented as much of the later 
standards as was done for the 1992 version.

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

Re: [sqlite] I'm getting a compiler error on a SQLIte build on Ubuntu 16.04 (which surprises me)

[Dan Kennedy]

On 07/09/2018 09:26 PM, Rob Willett wrote:

Dan,

Thanks for the information. We did try that first, failed and just 
assumed we were idiots and went to the the other download.


Just downloaded it again, so we can check and be on the safe side, and 
we get the same issue.


cp /jambuster/src/sqlite-src-324/ext/session/sqlite3session.h .
./libtool --mode=compile --tag=CC gcc -g -O2 -DSQLITE_OS_UNIX=1 -I. 
-I/jambuster/src/sqlite-src-324/src 
-I/jambuster/src/sqlite-src-324/ext/rtree 
-I/jambuster/src/sqlite-src-324/ext/icu 
-I/jambuster/src/sqlite-src-324/ext/fts3 
-I/jambuster/src/sqlite-src-324/ext/async 
-I/jambuster/src/sqlite-src-324/ext/session 
-D_HAVE_SQLITE_CONFIG_H -DBUILD_sqlite -DNDEBUG -I/usr/include/tcl8.6 
-DSQLITE_THREADSAFE=1 -DSQLITE_HAVE_ZLIB=1  -DSQLITE_TEMP_STORE=1 -c 
sqlite3.c
libtool: compile:  gcc -g -O2 -DSQLITE_OS_UNIX=1 -I. 
-I/jambuster/src/sqlite-src-324/src 
-I/jambuster/src/sqlite-src-324/ext/rtree 
-I/jambuster/src/sqlite-src-324/ext/icu 
-I/jambuster/src/sqlite-src-324/ext/fts3 
-I/jambuster/src/sqlite-src-324/ext/async 
-I/jambuster/src/sqlite-src-324/ext/session 
-D_HAVE_SQLITE_CONFIG_H -DBUILD_sqlite -DNDEBUG -I/usr/include/tcl8.6 
-DSQLITE_THREADSAFE=1 -DSQLITE_HAVE_ZLIB=1 -DSQLITE_TEMP_STORE=1 -c 
sqlite3.c  -fPIC -DPIC -o .libs/sqlite3.o

gcc: internal compiler error: Killed (program cc1)
Please submit a full bug report,
with preprocessed source if appropriate.
See  for instructions.
Makefile:715: recipe for target 'sqlite3.lo' failed

./configure seems OK, tcl looks OK (which was our worry), we think 
there is a genuine bug in the compiler. . We still need the 
sql_analyzer program as well :) Its one of those days, I have bad 
compilers and the UK government could fall.



After running [./configure], open the generated Makefile and remove all 
the "-O2" switches you can find. Then run [make sqlite3_analyzer].


You'll get an un-optimized binary of course, but there's a good chance 
this will bypass the compiler bug. And it won't make much difference 
unless your databases are really big.


Dan.








Rob

On 9 Jul 2018, at 15:02, Dan Kennedy wrote:


On 07/09/2018 03:56 PM, rob.sql...@robertwillett.com wrote:

Hi

I'm trying to build the SQLite packages from the source. I **only** 
want it to get a copy of sqlite_analyse which for some reason 
doesn't appear to be easily available as a compiled option. The 
download package doesn't work on Ubuntu 16.04 and as far as we can 
see, there are no other downloadable binaries that don't come from 
China. For some reason, I don't really want to download something 
from Beijing


The Linux binaries on sqlite.org are for 32-bit systems. For them to 
work on Ubuntu 16.04, you probably need to install multiarch support.


Or, to build sqlite3_analyzer from source you need the full source 
distribution:


  https://www.sqlite.org/2018/sqlite-src-324.zip

Dan.





The full steps I followed are at the very bottom of this e-mail. 
They are a direct copy from my terminal output.


**TLDR Summary**

1. OS is a reasonably fresh (< 4 weeks old) Ubuntu 16.04 LTS Server 
running under OpenVZ on a commercial ISP. It's patched to the 
current levels. I don't think this is a problem as we don't have any 
other issues but...


2. SQLite3 is already installed as the normal (and older) SQLIte 
that is distributed with Ubuntu 16.04 LTS


3. gcc is installed. I have created a small C program to test that 
it can compile (Hello World) and it's fine. (gcc (Ubuntu 
5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609)


4. Downloaded 
https://www.sqlite.org/2018/sqlite-autoconf-324.tar.gz through 
wget.


5. Checked the sha1sum and it matches the sqlite page.

6. Untarred the file into it's own directory

7. cd'ed to directory and ran ./configure.

8. Checked output of configure and nothing seemed wrong. Details are 
below.


9. Ran make and get a compiler failure!  (see below). I'm gobsmacked 
that the compiler has failed to be honest. So my first assumption is 
that we have cocked up something. However we've googled and checked 
and can't see anybody with a similar issue. I suspect that one of 
the Define statements is not playing nicely but not wholly sure 
which one OR we are missing a package that needs to be installed, 
e.g. TCL x,y or z.


root@preprod1:/jambuster/src/sqlite/sqlite-autoconf-324# make
/bin/bash ./libtool  --tag=CC   --mode=compile gcc 
-DPACKAGE_NAME=\"sqlite\" -DPACKAGE_TARNAME=\"sqlite\" 
-DPACKAGE_VERSION=\"3.24.0\" -DPACKAGE_STRING=\"sqlite\ 3.24.0\" 
-DPACKAGE_BUGREPORT=\"http://www.sqlite.org\; -DPACKAGE_URL=\"\" 
-DPACKAGE=\"sqlite\" -DVERSION=\"3.24.0\" -DSTDC_HEADERS=1 
-DHAVE_SYS_TYPES_H=1 -DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 
-DHAVE_STRING_H=1 -DHAVE_MEMORY_H=1 -DHAVE_STRINGS_H=1 
-DHAVE_INTTYPES_H=1 -DHAVE_STDINT_H=1 -DHAVE_UNISTD_H=1 
-DHAVE_DLFCN_H=1 -DLT_OBJDIR=\".libs/\" -DHAVE_FDATASYNC=1 
-DHAVE_USLEEP=1 -DHAVE_LOCALTIME_R=1 -DHAVE_GMTIME_R=1 

Re: [sqlite] I'm getting a compiler error on a SQLIte build on Ubuntu 16.04 (which surprises me)

[Rob Willett]

Dan,

Thanks for the information. We did try that first, failed and just 
assumed we were idiots and went to the the other download.


Just downloaded it again, so we can check and be on the safe side, and 
we get the same issue.


cp /jambuster/src/sqlite-src-324/ext/session/sqlite3session.h .
./libtool --mode=compile --tag=CC gcc -g -O2 -DSQLITE_OS_UNIX=1 -I. 
-I/jambuster/src/sqlite-src-324/src 
-I/jambuster/src/sqlite-src-324/ext/rtree 
-I/jambuster/src/sqlite-src-324/ext/icu 
-I/jambuster/src/sqlite-src-324/ext/fts3 
-I/jambuster/src/sqlite-src-324/ext/async 
-I/jambuster/src/sqlite-src-324/ext/session -D_HAVE_SQLITE_CONFIG_H 
-DBUILD_sqlite -DNDEBUG -I/usr/include/tcl8.6 -DSQLITE_THREADSAFE=1   
-DSQLITE_HAVE_ZLIB=1  -DSQLITE_TEMP_STORE=1 -c sqlite3.c
libtool: compile:  gcc -g -O2 -DSQLITE_OS_UNIX=1 -I. 
-I/jambuster/src/sqlite-src-324/src 
-I/jambuster/src/sqlite-src-324/ext/rtree 
-I/jambuster/src/sqlite-src-324/ext/icu 
-I/jambuster/src/sqlite-src-324/ext/fts3 
-I/jambuster/src/sqlite-src-324/ext/async 
-I/jambuster/src/sqlite-src-324/ext/session -D_HAVE_SQLITE_CONFIG_H 
-DBUILD_sqlite -DNDEBUG -I/usr/include/tcl8.6 -DSQLITE_THREADSAFE=1 
-DSQLITE_HAVE_ZLIB=1 -DSQLITE_TEMP_STORE=1 -c sqlite3.c  -fPIC -DPIC -o 
.libs/sqlite3.o

gcc: internal compiler error: Killed (program cc1)
Please submit a full bug report,
with preprocessed source if appropriate.
See  for instructions.
Makefile:715: recipe for target 'sqlite3.lo' failed

./configure seems OK, tcl looks OK (which was our worry), we think there 
is a genuine bug in the compiler. . We still need the sql_analyzer 
program as well :) Its one of those days, I have bad compilers and the 
UK government could fall.


Rob

On 9 Jul 2018, at 15:02, Dan Kennedy wrote:


On 07/09/2018 03:56 PM, rob.sql...@robertwillett.com wrote:

Hi

I'm trying to build the SQLite packages from the source. I **only** 
want it to get a copy of sqlite_analyse which for some reason doesn't 
appear to be easily available as a compiled option. The download 
package doesn't work on Ubuntu 16.04 and as far as we can see, there 
are no other downloadable binaries that don't come from China. For 
some reason, I don't really want to download something from 
Beijing


The Linux binaries on sqlite.org are for 32-bit systems. For them to 
work on Ubuntu 16.04, you probably need to install multiarch support.


Or, to build sqlite3_analyzer from source you need the full source 
distribution:


  https://www.sqlite.org/2018/sqlite-src-324.zip

Dan.





The full steps I followed are at the very bottom of this e-mail. They 
are a direct copy from my terminal output.


**TLDR Summary**

1. OS is a reasonably fresh (< 4 weeks old) Ubuntu 16.04 LTS Server 
running under OpenVZ on a commercial ISP. It's patched to the current 
levels. I don't think this is a problem as we don't have any other 
issues but...


2. SQLite3 is already installed as the normal (and older) SQLIte that 
is distributed with Ubuntu 16.04 LTS


3. gcc is installed. I have created a small C program to test that it 
can compile (Hello World) and it's fine. (gcc (Ubuntu 
5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609)


4. Downloaded 
https://www.sqlite.org/2018/sqlite-autoconf-324.tar.gz through 
wget.


5. Checked the sha1sum and it matches the sqlite page.

6. Untarred the file into it's own directory

7. cd'ed to directory and ran ./configure.

8. Checked output of configure and nothing seemed wrong. Details are 
below.


9. Ran make and get a compiler failure!  (see below). I'm gobsmacked 
that the compiler has failed to be honest. So my first assumption is 
that we have cocked up something. However we've googled and checked 
and can't see anybody with a similar issue. I suspect that one of the 
Define statements is not playing nicely but not wholly sure which one 
OR we are missing a package that needs to be installed, e.g. TCL x,y 
or z.


root@preprod1:/jambuster/src/sqlite/sqlite-autoconf-324# make
/bin/bash ./libtool  --tag=CC   --mode=compile gcc 
-DPACKAGE_NAME=\"sqlite\" -DPACKAGE_TARNAME=\"sqlite\" 
-DPACKAGE_VERSION=\"3.24.0\" -DPACKAGE_STRING=\"sqlite\ 3.24.0\" 
-DPACKAGE_BUGREPORT=\"http://www.sqlite.org\; -DPACKAGE_URL=\"\" 
-DPACKAGE=\"sqlite\" -DVERSION=\"3.24.0\" -DSTDC_HEADERS=1 
-DHAVE_SYS_TYPES_H=1 -DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 
-DHAVE_STRING_H=1 -DHAVE_MEMORY_H=1 -DHAVE_STRINGS_H=1 
-DHAVE_INTTYPES_H=1 -DHAVE_STDINT_H=1 -DHAVE_UNISTD_H=1 
-DHAVE_DLFCN_H=1 -DLT_OBJDIR=\".libs/\" -DHAVE_FDATASYNC=1 
-DHAVE_USLEEP=1 -DHAVE_LOCALTIME_R=1 -DHAVE_GMTIME_R=1 
-DHAVE_DECL_STRERROR_R=1 -DHAVE_STRERROR_R=1 -DHAVE_POSIX_FALLOCATE=1 
-DHAVE_ZLIB_H=1 -I.-D_REENTRANT=1 -DSQLITE_THREADSAFE=1  
-DSQLITE_ENABLE_FTS5 -DSQLITE_ENABLE_JSON1 -DSQLITE_HAVE_ZLIB  
-DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_RTREE  -g -O2 -MT sqlite3.lo -MD 
-MP -MF .deps/sqlite3.Tpo -c -o sqlite3.lo sqlite3.c
libtool: compile:  gcc -DPACKAGE_NAME=\"sqlite\" 

Re: [sqlite] I'm getting a compiler error on a SQLIte build on Ubuntu 16.04 (which surprises me)

[Dan Kennedy]

On 07/09/2018 03:56 PM, rob.sql...@robertwillett.com wrote:

Hi

I'm trying to build the SQLite packages from the source. I **only** 
want it to get a copy of sqlite_analyse which for some reason doesn't 
appear to be easily available as a compiled option. The download 
package doesn't work on Ubuntu 16.04 and as far as we can see, there 
are no other downloadable binaries that don't come from China. For 
some reason, I don't really want to download something from Beijing


The Linux binaries on sqlite.org are for 32-bit systems. For them to 
work on Ubuntu 16.04, you probably need to install multiarch support.


Or, to build sqlite3_analyzer from source you need the full source 
distribution:


  https://www.sqlite.org/2018/sqlite-src-324.zip

Dan.





The full steps I followed are at the very bottom of this e-mail. They 
are a direct copy from my terminal output.


**TLDR Summary**

1. OS is a reasonably fresh (< 4 weeks old) Ubuntu 16.04 LTS Server 
running under OpenVZ on a commercial ISP. It's patched to the current 
levels. I don't think this is a problem as we don't have any other 
issues but...


2. SQLite3 is already installed as the normal (and older) SQLIte that 
is distributed with Ubuntu 16.04 LTS


3. gcc is installed. I have created a small C program to test that it 
can compile (Hello World) and it's fine. (gcc (Ubuntu 
5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609)


4. Downloaded 
https://www.sqlite.org/2018/sqlite-autoconf-324.tar.gz through wget.


5. Checked the sha1sum and it matches the sqlite page.

6. Untarred the file into it's own directory

7. cd'ed to directory and ran ./configure.

8. Checked output of configure and nothing seemed wrong. Details are 
below.


9. Ran make and get a compiler failure!  (see below). I'm gobsmacked 
that the compiler has failed to be honest. So my first assumption is 
that we have cocked up something. However we've googled and checked 
and can't see anybody with a similar issue. I suspect that one of the 
Define statements is not playing nicely but not wholly sure which one 
OR we are missing a package that needs to be installed, e.g. TCL x,y 
or z.


root@preprod1:/jambuster/src/sqlite/sqlite-autoconf-324# make
/bin/bash ./libtool  --tag=CC   --mode=compile gcc 
-DPACKAGE_NAME=\"sqlite\" -DPACKAGE_TARNAME=\"sqlite\" 
-DPACKAGE_VERSION=\"3.24.0\" -DPACKAGE_STRING=\"sqlite\ 3.24.0\" 
-DPACKAGE_BUGREPORT=\"http://www.sqlite.org\; -DPACKAGE_URL=\"\" 
-DPACKAGE=\"sqlite\" -DVERSION=\"3.24.0\" -DSTDC_HEADERS=1 
-DHAVE_SYS_TYPES_H=1 -DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 
-DHAVE_STRING_H=1 -DHAVE_MEMORY_H=1 -DHAVE_STRINGS_H=1 
-DHAVE_INTTYPES_H=1 -DHAVE_STDINT_H=1 -DHAVE_UNISTD_H=1 
-DHAVE_DLFCN_H=1 -DLT_OBJDIR=\".libs/\" -DHAVE_FDATASYNC=1 
-DHAVE_USLEEP=1 -DHAVE_LOCALTIME_R=1 -DHAVE_GMTIME_R=1 
-DHAVE_DECL_STRERROR_R=1 -DHAVE_STRERROR_R=1 -DHAVE_POSIX_FALLOCATE=1 
-DHAVE_ZLIB_H=1 -I.-D_REENTRANT=1 -DSQLITE_THREADSAFE=1  
-DSQLITE_ENABLE_FTS5 -DSQLITE_ENABLE_JSON1 -DSQLITE_HAVE_ZLIB  
-DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_RTREE  -g -O2 -MT sqlite3.lo -MD 
-MP -MF .deps/sqlite3.Tpo -c -o sqlite3.lo sqlite3.c
libtool: compile:  gcc -DPACKAGE_NAME=\"sqlite\" 
-DPACKAGE_TARNAME=\"sqlite\" -DPACKAGE_VERSION=\"3.24.0\" 
"-DPACKAGE_STRING=\"sqlite 3.24.0\"" 
-DPACKAGE_BUGREPORT=\"http://www.sqlite.org\; -DPACKAGE_URL=\"\" 
-DPACKAGE=\"sqlite\" -DVERSION=\"3.24.0\" -DSTDC_HEADERS=1 
-DHAVE_SYS_TYPES_H=1 -DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 
-DHAVE_STRING_H=1 -DHAVE_MEMORY_H=1 -DHAVE_STRINGS_H=1 
-DHAVE_INTTYPES_H=1 -DHAVE_STDINT_H=1 -DHAVE_UNISTD_H=1 
-DHAVE_DLFCN_H=1 -DLT_OBJDIR=\".libs/\" -DHAVE_FDATASYNC=1 
-DHAVE_USLEEP=1 -DHAVE_LOCALTIME_R=1 -DHAVE_GMTIME_R=1 
-DHAVE_DECL_STRERROR_R=1 -DHAVE_STRERROR_R=1 -DHAVE_POSIX_FALLOCATE=1 
-DHAVE_ZLIB_H=1 -I. -D_REENTRANT=1 -DSQLITE_THREADSAFE=1 
-DSQLITE_ENABLE_FTS5 -DSQLITE_ENABLE_JSON1 -DSQLITE_HAVE_ZLIB 
-DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_RTREE -g -O2 -MT sqlite3.lo -MD 
-MP -MF .deps/sqlite3.Tpo -c sqlite3.c -fPIC -DPIC -o .libs/sqlite3.o

gcc: internal compiler error: Killed (program cc1)
Please submit a full bug report,
with preprocessed source if appropriate.
See  for instructions.
Makefile:539: recipe for target 'sqlite3.lo' failed
make: *** [sqlite3.lo] Error 1

10. I checked the Makefile but there's no obvious issue

11. We've done this three times now with the same result.

12. The fact the compiler barfs is worrying, we know how many people 
use SQLite so for this sort of error to occur is a little surprising 
and we still thinksqlite-us...@mailinglists.sqlite.org its our fault, 
but we cannot see what we have done wrong.


Any help or suggestions welcomed. I have to say I am sick to the back 
teeth of reading websites that purport to tell you how to compile 
SQLite to find that it simply consists of apt-get install sqlite3 (or 
whatever). The signal to noise ration here is very, very low. We need 
a clickbait filter.


Thanks

Rob
-


**Detailed 

Re: [sqlite] I'm getting a compiler error on a SQLIte build on Ubuntu 16.04 (which surprises me)

[Rob Willett]

Clemens,

Thanks. We were up to date anyway, but we did check and we still get the 
same error.


It does look like a compiler bug, also nobody else seems to have 
reported it, which I think is very odd. I cannot be the first person to 
compile this version of SQLite on Ubuntu 16.04 server.


We'll work out how to report it. I still have the same problem of trying 
to get hold of a copy of SQLite_analyze though :( Which is what I wanted 
to do in the first place.


Rob

On 9 Jul 2018, at 11:24, Clemens Ladisch wrote:


rob.sql...@robertwillett.com wrote:

gcc: internal compiler error: Killed (program cc1)


This is a compiler bug.  Check if updating gcc to a current version 
helps.



Please submit a full bug report,
with preprocessed source if appropriate.
See  for instructions.


Otherwise, do this.


Regards,
Clemens
___
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] What happens when a call contains two SQL statement

[Thomas Kurz]

As far as I know you have to purchase the documents.

- Original Message - 
From: Aaron Elkins 
To: SQLite mailing list 
Sent: Monday, July 9, 2018, 14:57:07
Subject: [sqlite] What happens when a call contains two SQL statement

Hi Ryan, 

Thank you for the interesting explanations for SQL specific cases, and I am 
also interested in the SQL standard document after reading your email, can you 
point me to the right place? 

Sorry, I did not trust myself to find the official SQL standard by googling.

- Aaron



> On Jul 9, 2018, at 8:35 PM, R Smith  wrote:

> I see Gunter already answered this for the specific case, I'll just add some 
> simple rules of thumb for transactions in general:

> 1. SQLite automagically starts a transaction ONLY when faced with a single 
> statement. The second statement in your query/list/script is in its own 
> transaction and not the same one as the first statement.
> 2. If at any point you need more than one statement to be run together in a 
> single transaction, you need to start with BEGIN TRANSACTION, add all 
> statements and end it with either END, COMMIT or ROLLBACK.
> 3. Point 2 is True for ALL SQL Engines.
> 4. Typically once you've started a transaction, you control when it is 
> committed or rolled back. This is typically done in your code, but you can of 
> course set SQL conflict handling to have a vote in what happens.

> In SQLite specifically, the word "TRANSACTION" is optional, i.e. BEGIN 
> TRANSACTION is equivalent to just BEGIN, and END TRANSACTION is the same as 
> just END. Ditto for ROLLBACK TRANSACTION and just ROLLBACK.

> END is also equivalent to COMMIT - it saves the changes and busts out of the 
> current transaction (Important note: It does this EVEN if there were errors. 
> Unless specifically instructed to roll back, a transaction is perfectly happy 
> to complete and commit the successful bits - this is true for most (if not 
> all) SQL Engines).

> In any INSERT/UPDATE data altering statement, you can add an ON CONFLICT 
> clause which specifically instructs how to handle the current statement and 
> transaction flow.

> The options are:

> - ON CONFLICT ABORT (the default if nothing is specified) - which tells the 
> engine to Stop and Undo this current command's updates, but do not stop the 
> transaction, keep changes by previous statements and keep going with the next 
> statements. [Kill me, but not the others]

> - ON CONFLICT FAIL - which tells the engine the current update failure should 
> STOP the current statement in its tracks, but not undo any changes up to now 
> caused by the current statement, and still continue with any next statements. 
> [Don't let me continue, but don't kill me and don't kill the others]

> - ON CONFLICT IGNORE - which tells the engine that this fail is o.k. and it 
> may BOTH continue with updates for this current statement AND soldier on with 
> the next statements in the transaction. [No harm done, Forgive and Forget, 
> Peace and Love, let's move on...]

> - ON CONFLICT ROLLBACK  - which undoes the entire set of changes since the 
> BEGIN. [This is SPART! Kill everything!]

> - ON CONFLICT REPLACE - this one is more complicated but essentially it tries 
> to remove the old data (where needed) and shove the new data in there, but in 
> all cases it doesn't stop the TRANSACTION. [Please don't kill us... I'll do 
> anything you want!]


> Note also that a statement "failing" here means it ran into a constraint 
> conflict and so cannot fulfill its correctly-understood obligation.  If the 
> statement fails programmatically because it is a nonsense statement that 
> cannot be correctly understood by the parser/query planner, then in my 
> experience the transaction is rolled back automatically (much the same as if 
> there was a power-failure, an IO error, etc.).  I'm not sure this is 100% 
> always the case?


> Some things to watch out for:

> - Unlike some other SQL engines, in SQLite transactions do not nest. [There 
> can be only one!]. Nesting behaviour is however possible using SAVEPOINT 
> instructions. (See the docs).

> - SQLite wrappers in your favourite programming environment will often 
> automatically start transactions or automatically group statements in 
> transactions unbeknown to the programmer - check your wrapper's documentation 
> and config.

> - SQLite CONFLICT clauses can be set upon table creation for constraints, for 
> example the table schema you are writing a transaction for may have a 
> declaration like:
> CREATE TABLE t1 (
>   ID INT PRIMARY KEY ON CONFLICT ROLLBACK,
>   Name TEXT CHECK len(Name) > 0 ON CONFLICT FAIL,
>   ...
>   etc.
> );
> Now you can override this in the transaction by specifying your own ON 
> CONFLICT clause when doing an INSERT, for example, but it's worth noting that 
> in case you don't have a conflict clause, and as such are expecting the 
> default behaviour of "ON CONFLICT ABORT", you might be surprised by an entire 
> transaction 

Re: [sqlite] What happens when a call contains two SQL statement

[Aaron Elkins]

Hi Ryan, 

Thank you for the interesting explanations for SQL specific cases, and I am 
also interested in the SQL standard document after reading your email, can you 
point me to the right place? 

Sorry, I did not trust myself to find the official SQL standard by googling.

- Aaron



> On Jul 9, 2018, at 8:35 PM, R Smith  wrote:
> 
> I see Gunter already answered this for the specific case, I'll just add some 
> simple rules of thumb for transactions in general:
> 
> 1. SQLite automagically starts a transaction ONLY when faced with a single 
> statement. The second statement in your query/list/script is in its own 
> transaction and not the same one as the first statement.
> 2. If at any point you need more than one statement to be run together in a 
> single transaction, you need to start with BEGIN TRANSACTION, add all 
> statements and end it with either END, COMMIT or ROLLBACK.
> 3. Point 2 is True for ALL SQL Engines.
> 4. Typically once you've started a transaction, you control when it is 
> committed or rolled back. This is typically done in your code, but you can of 
> course set SQL conflict handling to have a vote in what happens.
> 
> In SQLite specifically, the word "TRANSACTION" is optional, i.e. BEGIN 
> TRANSACTION is equivalent to just BEGIN, and END TRANSACTION is the same as 
> just END. Ditto for ROLLBACK TRANSACTION and just ROLLBACK.
> 
> END is also equivalent to COMMIT - it saves the changes and busts out of the 
> current transaction (Important note: It does this EVEN if there were errors. 
> Unless specifically instructed to roll back, a transaction is perfectly happy 
> to complete and commit the successful bits - this is true for most (if not 
> all) SQL Engines).
> 
> In any INSERT/UPDATE data altering statement, you can add an ON CONFLICT 
> clause which specifically instructs how to handle the current statement and 
> transaction flow.
> 
> The options are:
> 
> - ON CONFLICT ABORT (the default if nothing is specified) - which tells the 
> engine to Stop and Undo this current command's updates, but do not stop the 
> transaction, keep changes by previous statements and keep going with the next 
> statements. [Kill me, but not the others]
> 
> - ON CONFLICT FAIL - which tells the engine the current update failure should 
> STOP the current statement in its tracks, but not undo any changes up to now 
> caused by the current statement, and still continue with any next statements. 
> [Don't let me continue, but don't kill me and don't kill the others]
> 
> - ON CONFLICT IGNORE - which tells the engine that this fail is o.k. and it 
> may BOTH continue with updates for this current statement AND soldier on with 
> the next statements in the transaction. [No harm done, Forgive and Forget, 
> Peace and Love, let's move on...]
> 
> - ON CONFLICT ROLLBACK  - which undoes the entire set of changes since the 
> BEGIN. [This is SPART! Kill everything!]
> 
> - ON CONFLICT REPLACE - this one is more complicated but essentially it tries 
> to remove the old data (where needed) and shove the new data in there, but in 
> all cases it doesn't stop the TRANSACTION. [Please don't kill us... I'll do 
> anything you want!]
> 
> 
> Note also that a statement "failing" here means it ran into a constraint 
> conflict and so cannot fulfill its correctly-understood obligation.  If the 
> statement fails programmatically because it is a nonsense statement that 
> cannot be correctly understood by the parser/query planner, then in my 
> experience the transaction is rolled back automatically (much the same as if 
> there was a power-failure, an IO error, etc.).  I'm not sure this is 100% 
> always the case?
> 
> 
> Some things to watch out for:
> 
> - Unlike some other SQL engines, in SQLite transactions do not nest. [There 
> can be only one!]. Nesting behaviour is however possible using SAVEPOINT 
> instructions. (See the docs).
> 
> - SQLite wrappers in your favourite programming environment will often 
> automatically start transactions or automatically group statements in 
> transactions unbeknown to the programmer - check your wrapper's documentation 
> and config.
> 
> - SQLite CONFLICT clauses can be set upon table creation for constraints, for 
> example the table schema you are writing a transaction for may have a 
> declaration like:
> CREATE TABLE t1 (
>   ID INT PRIMARY KEY ON CONFLICT ROLLBACK,
>   Name TEXT CHECK len(Name) > 0 ON CONFLICT FAIL,
>   ...
>   etc.
> );
> Now you can override this in the transaction by specifying your own ON 
> CONFLICT clause when doing an INSERT, for example, but it's worth noting that 
> in case you don't have a conflict clause, and as such are expecting the 
> default behaviour of "ON CONFLICT ABORT", you might be surprised by an entire 
> transaction roll-back when INSERTing a duplicate ID, or the current statement 
> stopping when one empty Name value occurs.
> 
> 
> That's basically the short version of what you need to know about 
> transactions 

Re: [sqlite] What happens when a call contains two SQL statement

[R Smith]

I see Gunter already answered this for the specific case, I'll just add 
some simple rules of thumb for transactions in general:


1. SQLite automagically starts a transaction ONLY when faced with a 
single statement. The second statement in your query/list/script is in 
its own transaction and not the same one as the first statement.
2. If at any point you need more than one statement to be run together 
in a single transaction, you need to start with BEGIN TRANSACTION, add 
all statements and end it with either END, COMMIT or ROLLBACK.

3. Point 2 is True for ALL SQL Engines.
4. Typically once you've started a transaction, you control when it is 
committed or rolled back. This is typically done in your code, but you 
can of course set SQL conflict handling to have a vote in what happens.


In SQLite specifically, the word "TRANSACTION" is optional, i.e. BEGIN 
TRANSACTION is equivalent to just BEGIN, and END TRANSACTION is the same 
as just END. Ditto for ROLLBACK TRANSACTION and just ROLLBACK.


END is also equivalent to COMMIT - it saves the changes and busts out of 
the current transaction (Important note: It does this EVEN if there were 
errors. Unless specifically instructed to roll back, a transaction is 
perfectly happy to complete and commit the successful bits - this is 
true for most (if not all) SQL Engines).


In any INSERT/UPDATE data altering statement, you can add an ON CONFLICT 
clause which specifically instructs how to handle the current statement 
and transaction flow.


The options are:

- ON CONFLICT ABORT (the default if nothing is specified) - which tells 
the engine to Stop and Undo this current command's updates, but do not 
stop the transaction, keep changes by previous statements and keep going 
with the next statements. [Kill me, but not the others]


- ON CONFLICT FAIL - which tells the engine the current update failure 
should STOP the current statement in its tracks, but not undo any 
changes up to now caused by the current statement, and still continue 
with any next statements. [Don't let me continue, but don't kill me and 
don't kill the others]


- ON CONFLICT IGNORE - which tells the engine that this fail is o.k. and 
it may BOTH continue with updates for this current statement AND soldier 
on with the next statements in the transaction. [No harm done, Forgive 
and Forget, Peace and Love, let's move on...]


- ON CONFLICT ROLLBACK  - which undoes the entire set of changes since 
the BEGIN. [This is SPART! Kill everything!]


- ON CONFLICT REPLACE - this one is more complicated but essentially it 
tries to remove the old data (where needed) and shove the new data in 
there, but in all cases it doesn't stop the TRANSACTION. [Please don't 
kill us... I'll do anything you want!]



Note also that a statement "failing" here means it ran into a constraint 
conflict and so cannot fulfill its correctly-understood obligation.  If 
the statement fails programmatically because it is a nonsense statement 
that cannot be correctly understood by the parser/query planner, then in 
my experience the transaction is rolled back automatically (much the 
same as if there was a power-failure, an IO error, etc.).  I'm not sure 
this is 100% always the case?



Some things to watch out for:

- Unlike some other SQL engines, in SQLite transactions do not nest. 
[There can be only one!]. Nesting behaviour is however possible using 
SAVEPOINT instructions. (See the docs).


- SQLite wrappers in your favourite programming environment will often 
automatically start transactions or automatically group statements in 
transactions unbeknown to the programmer - check your wrapper's 
documentation and config.


- SQLite CONFLICT clauses can be set upon table creation for 
constraints, for example the table schema you are writing a transaction 
for may have a declaration like:

CREATE TABLE t1 (
  ID INT PRIMARY KEY ON CONFLICT ROLLBACK,
  Name TEXT CHECK len(Name) > 0 ON CONFLICT FAIL,
  ...
  etc.
);
Now you can override this in the transaction by specifying your own ON 
CONFLICT clause when doing an INSERT, for example, but it's worth noting 
that in case you don't have a conflict clause, and as such are expecting 
the default behaviour of "ON CONFLICT ABORT", you might be surprised by 
an entire transaction roll-back when INSERTing a duplicate ID, or the 
current statement stopping when one empty Name value occurs.



That's basically the short version of what you need to know about 
transactions in SQLite.

Cheers!
Ryan


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

[sqlite] SQLite.NET Class Library Documentation Feedback: Designer Support

[Roger Schlueter]

SQLite.NET Help
    Introduction to System.Data .SQLite
        Initializing Design-Time Support
            Express Edition Limitations

This whole section should be removed since it is no longer correct, 
especially the part in red.  If you want to include the versions of 
Visual Studio just say "All versions since 2005" but in that case, 
change the title of the section.

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

Re: [sqlite] I'm getting a compiler error on a SQLIte build on Ubuntu 16.04 (which surprises me)

[Clemens Ladisch]

rob.sql...@robertwillett.com wrote:
> gcc: internal compiler error: Killed (program cc1)

This is a compiler bug.  Check if updating gcc to a current version helps.

> Please submit a full bug report,
> with preprocessed source if appropriate.
> See  for instructions.

Otherwise, do this.


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

[sqlite] I'm getting a compiler error on a SQLIte build on Ubuntu 16.04 (which surprises me)

[rob . sqlite]

Hi

I'm trying to build the SQLite packages from the source. I **only** want 
it to get a copy of sqlite_analyse which for some reason doesn't appear 
to be easily available as a compiled option. The download package 
doesn't work on Ubuntu 16.04 and as far as we can see, there are no 
other downloadable binaries that don't come from China. For some reason, 
I don't really want to download something from Beijing


The full steps I followed are at the very bottom of this e-mail. They 
are a direct copy from my terminal output.


**TLDR Summary**

1. OS is a reasonably fresh (< 4 weeks old) Ubuntu 16.04 LTS Server 
running under OpenVZ on a commercial ISP. It's patched to the current 
levels. I don't think this is a problem as we don't have any other 
issues but...


2. SQLite3 is already installed as the normal (and older) SQLIte that is 
distributed with Ubuntu 16.04 LTS


3. gcc is installed. I have created a small C program to test that it 
can compile (Hello World) and it's fine. (gcc (Ubuntu 
5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609)


4. Downloaded https://www.sqlite.org/2018/sqlite-autoconf-324.tar.gz 
through wget.


5. Checked the sha1sum and it matches the sqlite page.

6. Untarred the file into it's own directory

7. cd'ed to directory and ran ./configure.

8. Checked output of configure and nothing seemed wrong. Details are 
below.


9. Ran make and get a compiler failure!  (see below). I'm gobsmacked 
that the compiler has failed to be honest. So my first assumption is 
that we have cocked up something. However we've googled and checked and 
can't see anybody with a similar issue. I suspect that one of the Define 
statements is not playing nicely but not wholly sure which one OR we are 
missing a package that needs to be installed, e.g. TCL x,y or z.


root@preprod1:/jambuster/src/sqlite/sqlite-autoconf-324# make
/bin/bash ./libtool  --tag=CC   --mode=compile gcc 
-DPACKAGE_NAME=\"sqlite\" -DPACKAGE_TARNAME=\"sqlite\" 
-DPACKAGE_VERSION=\"3.24.0\" -DPACKAGE_STRING=\"sqlite\ 3.24.0\" 
-DPACKAGE_BUGREPORT=\"http://www.sqlite.org\; -DPACKAGE_URL=\"\" 
-DPACKAGE=\"sqlite\" -DVERSION=\"3.24.0\" -DSTDC_HEADERS=1 
-DHAVE_SYS_TYPES_H=1 -DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 
-DHAVE_STRING_H=1 -DHAVE_MEMORY_H=1 -DHAVE_STRINGS_H=1 
-DHAVE_INTTYPES_H=1 -DHAVE_STDINT_H=1 -DHAVE_UNISTD_H=1 -DHAVE_DLFCN_H=1 
-DLT_OBJDIR=\".libs/\" -DHAVE_FDATASYNC=1 -DHAVE_USLEEP=1 
-DHAVE_LOCALTIME_R=1 -DHAVE_GMTIME_R=1 -DHAVE_DECL_STRERROR_R=1 
-DHAVE_STRERROR_R=1 -DHAVE_POSIX_FALLOCATE=1 -DHAVE_ZLIB_H=1 -I.
-D_REENTRANT=1 -DSQLITE_THREADSAFE=1  -DSQLITE_ENABLE_FTS5 
-DSQLITE_ENABLE_JSON1 -DSQLITE_HAVE_ZLIB  -DSQLITE_ENABLE_FTS3 
-DSQLITE_ENABLE_RTREE  -g -O2 -MT sqlite3.lo -MD -MP -MF 
.deps/sqlite3.Tpo -c -o sqlite3.lo sqlite3.c
libtool: compile:  gcc -DPACKAGE_NAME=\"sqlite\" 
-DPACKAGE_TARNAME=\"sqlite\" -DPACKAGE_VERSION=\"3.24.0\" 
"-DPACKAGE_STRING=\"sqlite 3.24.0\"" 
-DPACKAGE_BUGREPORT=\"http://www.sqlite.org\; -DPACKAGE_URL=\"\" 
-DPACKAGE=\"sqlite\" -DVERSION=\"3.24.0\" -DSTDC_HEADERS=1 
-DHAVE_SYS_TYPES_H=1 -DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 
-DHAVE_STRING_H=1 -DHAVE_MEMORY_H=1 -DHAVE_STRINGS_H=1 
-DHAVE_INTTYPES_H=1 -DHAVE_STDINT_H=1 -DHAVE_UNISTD_H=1 -DHAVE_DLFCN_H=1 
-DLT_OBJDIR=\".libs/\" -DHAVE_FDATASYNC=1 -DHAVE_USLEEP=1 
-DHAVE_LOCALTIME_R=1 -DHAVE_GMTIME_R=1 -DHAVE_DECL_STRERROR_R=1 
-DHAVE_STRERROR_R=1 -DHAVE_POSIX_FALLOCATE=1 -DHAVE_ZLIB_H=1 -I. 
-D_REENTRANT=1 -DSQLITE_THREADSAFE=1 -DSQLITE_ENABLE_FTS5 
-DSQLITE_ENABLE_JSON1 -DSQLITE_HAVE_ZLIB -DSQLITE_ENABLE_FTS3 
-DSQLITE_ENABLE_RTREE -g -O2 -MT sqlite3.lo -MD -MP -MF 
.deps/sqlite3.Tpo -c sqlite3.c  -fPIC -DPIC -o .libs/sqlite3.o

gcc: internal compiler error: Killed (program cc1)
Please submit a full bug report,
with preprocessed source if appropriate.
See  for instructions.
Makefile:539: recipe for target 'sqlite3.lo' failed
make: *** [sqlite3.lo] Error 1

10. I checked the Makefile but there's no obvious issue

11. We've done this three times now with the same result.

12. The fact the compiler barfs is worrying, we know how many people use 
SQLite so for this sort of error to occur is a little surprising and we 
still thinksqlite-us...@mailinglists.sqlite.org its our fault, but we 
cannot see what we have done wrong.


Any help or suggestions welcomed. I have to say I am sick to the back 
teeth of reading websites that purport to tell you how to compile SQLite 
to find that it simply consists of apt-get install sqlite3 (or 
whatever). The signal to noise ration here is very, very low. We need a 
clickbait filter.


Thanks

Rob
-


**Detailed summary**

wget https://www.sqlite.org/2018/sqlite-autoconf-324.tar.gz
--2018-07-09 08:28:39--  
https://www.sqlite.org/2018/sqlite-autoconf-324.tar.gz
Resolving www.sqlite.org (www.sqlite.org)... 45.33.6.223, 
2600:3c00::f03c:91ff:fe96:b959
Connecting to www.sqlite.org (www.sqlite.org)|45.33.6.223|:443... 
connected.

HTTP request sent, 

Re: [sqlite] [EXTERNAL] What happens when a call contains two SQL statement

[Hick Gunter]

Only the "on conflict" clause

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Cecil Westerhof
Gesendet: Montag, 09. Juli 2018 08:38
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] What happens when a call contains two SQL 
statement

2018-07-09 8:28 GMT+02:00 Hick Gunter :

> Why should a failure in transaction #2 rollback transaction #1?
>

​I was thinking that, but was not sure. I thought that maybe everything in a 
call would be seen as a transaction. But that is not the case then: every 
statement in a call is its own transaction.

​​


> If you want this behaviour, do "begin; delete ...; insert ... on
> conflict rollback; commit;" to make both statements run in one
> transaction
>

Is this standard SQL, or SQLite specific?



> -Ursprüngliche Nachricht-
> Von: sqlite-users
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von Cecil Westerhof
> Gesendet: Montag, 09. Juli 2018 08:21
> An: SQLite mailing list 
> Betreff: [EXTERNAL] [sqlite] What happens when a call contains two SQL
> statement
>
> I am working with Tcl. The best is of-course a general answer, but if
> it is depending on the used language I will be satisfied with the Tcl answer.
> ;-)
>
> Say I have the following code:
> set SQLCmd "
> DELETE FROM testing
> WHERE  key = 12
> ;
> INSERT INTO testing
> (key, value)
> VALUES
> (12, 'Just some text')
> "
> db eval ${sqlCmd}
>
> If the insert goes wrong, will the delete be rolled back, or not?
>
> I could use INSERT OR REPLACE, but the above code would be database
> independent.
>

--
Cecil Westerhof
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] What happens when a call contains two SQL statement

[Cecil Westerhof]

2018-07-09 8:28 GMT+02:00 Hick Gunter :

> Why should a failure in transaction #2 rollback transaction #1?
>

​I was thinking that, but was not sure. I thought that maybe everything in
a call would be seen as a transaction. But that is not the case then: every
statement in a call is its own transaction.

​​


> If you want this behaviour, do "begin; delete ...; insert ... on conflict
> rollback; commit;" to make both statements run in one transaction
>

Is this standard SQL, or SQLite specific?



> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von Cecil Westerhof
> Gesendet: Montag, 09. Juli 2018 08:21
> An: SQLite mailing list 
> Betreff: [EXTERNAL] [sqlite] What happens when a call contains two SQL
> statement
>
> I am working with Tcl. The best is of-course a general answer, but if it
> is depending on the used language I will be satisfied with the Tcl answer.
> ;-)
>
> Say I have the following code:
> set SQLCmd "
> DELETE FROM testing
> WHERE  key = 12
> ;
> INSERT INTO testing
> (key, value)
> VALUES
> (12, 'Just some text')
> "
> db eval ${sqlCmd}
>
> If the insert goes wrong, will the delete be rolled back, or not?
>
> I could use INSERT OR REPLACE, but the above code would be database
> independent.
>

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

Re: [sqlite] Use with Visual Studio

[J Decker]

there's also nuget package for sqlite which handles updates pretty well.
(IMO)

On Sun, Jul 8, 2018 at 11:13 PM Mike King  wrote:

> Same here. Again, I download it from the main system.data site. No problems
> at all.
>
> Cheers
>
> On Mon, 9 Jul 2018 at 06:57, Chris Locke  wrote:
>
> > I use system.data.sqlite.dll (taken from here:
> > https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki)
> > with no problems in both VS 2017 Professional and VS 2017 Community.
> >
> > Thanks,
> > Chris
> >
> >
> > On Mon, Jul 9, 2018 at 2:47 AM Roger Schlueter  wrote:
> >
> > > I am considering using the .net version of SQLite but have two
> questions:
> > >
> > >  1. The documentation lists the versions of Visual Studio that are
> > > supported.  VS2017 is NOT listed.  Is VS2017 supported.
> > >  2. The documentation states "Due to Visual Studio licensing
> > > restrictions, the Express Editions can no longer be supported."
> > > (Yes, in red).  However, Microsoft no longer uses the phrase
> > > "Express Edition" but rather calls the freebie version "Community".
> > > Is this just semantics or does the red warning still apply to
> > > Community?  Also, I am unaware of any "licensing restrictions" on
> > > the Community editions that would preclude the use of SQLite.  Are
> > > there such restrictions and, if so, what are they?
> > >
> > > Roger
> > >
> > > ___
> > > 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-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] [EXTERNAL] What happens when a call contains two SQL statement

[Hick Gunter]

Why should a failure in transaction #2 rollback transaction #1?

If you want this behaviour, do "begin; delete ...; insert ... on conflict 
rollback; commit;" to make both statements run in one transaction

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Cecil Westerhof
Gesendet: Montag, 09. Juli 2018 08:21
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] What happens when a call contains two SQL statement

I am working with Tcl. The best is of-course a general answer, but if it is 
depending on the used language I will be satisfied with the Tcl answer. ;-)

Say I have the following code:
set SQLCmd "
DELETE FROM testing
WHERE  key = 12
;
INSERT INTO testing
(key, value)
VALUES
(12, 'Just some text')
"
db eval ${sqlCmd}

If the insert goes wrong, will the delete be rolled back, or not?

I could use INSERT OR REPLACE, but the above code would be database independent.

--
Cecil Westerhof
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] What happens when a call contains two SQL statement

[Cecil Westerhof]

I am working with Tcl. The best is of-course a general answer, but if it is
depending on the used language I will be satisfied with the Tcl answer. ;-)

Say I have the following code:
set SQLCmd "
DELETE FROM testing
WHERE  key = 12
;
INSERT INTO testing
(key, value)
VALUES
(12, 'Just some text')
"
db eval ${sqlCmd}

If the insert goes wrong, will the delete be rolled back, or not?

I could use INSERT OR REPLACE, but the above code would be database
independent.

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

Re: [sqlite] Use with Visual Studio

[Mike King]

Same here. Again, I download it from the main system.data site. No problems
at all.

Cheers

On Mon, 9 Jul 2018 at 06:57, Chris Locke  wrote:

> I use system.data.sqlite.dll (taken from here:
> https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki)
> with no problems in both VS 2017 Professional and VS 2017 Community.
>
> Thanks,
> Chris
>
>
> On Mon, Jul 9, 2018 at 2:47 AM Roger Schlueter  wrote:
>
> > I am considering using the .net version of SQLite but have two questions:
> >
> >  1. The documentation lists the versions of Visual Studio that are
> > supported.  VS2017 is NOT listed.  Is VS2017 supported.
> >  2. The documentation states "Due to Visual Studio licensing
> > restrictions, the Express Editions can no longer be supported."
> > (Yes, in red).  However, Microsoft no longer uses the phrase
> > "Express Edition" but rather calls the freebie version "Community".
> > Is this just semantics or does the red warning still apply to
> > Community?  Also, I am unaware of any "licensing restrictions" on
> > the Community editions that would preclude the use of SQLite.  Are
> > there such restrictions and, if so, what are they?
> >
> > Roger
> >
> > ___
> > 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users