Re: [sqlite] Updating a table from itself

[Igor Tandetnik]

On 6/8/2013 2:51 PM, Dave Wellman wrote:

update t1 from (select c1,c2 from t1) as dt1
set c2 = dt1.c2
where t1.c1 = dt1.c2 - 1;


update t1 set c2 = coalesce((select c2 from t1 dt1 where t1.c1 = dt1.c2 
- 1), c2);


--
Igor Tandetnik

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

[sqlite] Updating a table from itself

[Dave Wellman]

Hi,

Can someone please point me in the direction of the SQLite syntax structure
for updating a table by joining to itself. I need to update one row with the
contents of another. 

 

Based on my normal database (Teradata) I was looking for something like

 

update t1 from (select c1,c2 from t1) as dt1

set c2 = dt1.c2

where t1.c1 = dt1.c2 - 1;

 

Or

 

update t1 as a

set c2 = t1.c2

where a.c1 = t1.c2 - 1;

 

I can't see either of these syntax options in the documentation.

 

All pointers greatly appreciated.

 

Cheers,

Dave

 

 

Ward Analytics Ltd - information in motion

Tel: +44 (0) 118 9740191

Fax: +44 (0) 118 9740192

www:   http://www.ward-analytics.com

 

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR

Registered company number: 3917021 Registered in England and Wales.

 

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

Re: [sqlite] Transaction

[Simon Slavin]

On 8 Jun 2013, at 5:27pm, RSmith  wrote:

> Thank you very much for taking the time - this did answer my question.

You're welcome.

> If I may one more question related to this:  Let's say I am using 
> transactions as validation stops on a long list of inputs - So
> that the main program does some checking along the way, I have two choices, I 
> can start with a BEGIN and do COMMIT at the very end,
> and possibly ROLLBACK everything once I get an error and start over. 
> Alternatively (and this is obviously what I want to do), I can
> do BEGIN, then check say 1000 records, then COMMIT, then BEGIN, check 1000, 
> COMMIT, etc. This way if I get an error, I need only
> rollback a little bit.
> 
> The question is, how high is the overhead on BEGIN-COMMITs?

I don't know the really detailed answer to this.  If you care down to the 
millisecond then the answer depends on which journaling mode you're using, and 
on technical details about your operating system and file system.  The problem 
is that the actual time varies tremendously depending on how cheap and nasty 
your hard disk is, whether your databus is choked and other stuff we can't 
guess.

Frequent advice on this list is to assume that you will want to batch your 
transactions somehow, and put your 1000 into a variable you can change.  Then 
try your app with several different values for the variable.  If I understand 
your description then setting this variable to 100 would be equivalent to 
your other scenario: just doing everything in one huge batch.  You don't make 
complicated changes each time, you just change the number in one line of code.

The opposite advice is this: version 1 of your code should ignore timing and 
just do whatever reflects the way your data relates to real life.  Just think 
about what your data really 'means' and use transactions and SAVEPOINTs that 
reflect that.  Then run your app.  A second or two either way isn't anything to 
care about.  Only if your app is /unacceptably/ slow do you then have to start 
worrying about writing contra-intuitive code.

And with those two pieces of advice, I hope I have covered all bases in an 
unbiassed manner.  I hope you can forgive me for the fact that they contradict 
one-another.

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

Re: [sqlite] Strange table behaviour after text import with sqlite3.exe

[Bart Smissaert]

I did (select * from qqq) but not from the sqlite3 shell.
I can see that via the shell you get the output as you mention.
My output was via a VB wrapper and I take it that the zero showing instead
of FIELD1
has to do with that. Will have a look at that and thanks for alerting me to
this discrepancy
between the output from sqlite3.exe and from this wrapper.

RBS


On Sat, Jun 8, 2013 at 6:50 PM, Keith Medcalf  wrote:

>
> I don't think you did because if you had then what you would have seen
> would be as follows:
>
> SQLite version 3.7.17 2013-06-05 16:17:21
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> CREATE TABLE QQQ([FIELD1] INTEGER, [FIELD2] TEXT);
> sqlite> .mode csv
> sqlite> .import csv.txt QQQ
> sqlite> select * from QQQ;
> FIELD1,FIELD2
> 1,ABC
> 2,BCD
> 3,CDE
>
> Which clearly indicates that there are 4 rows in table  and that the
> value of FIELD1 where field2='FIELD2' is 'FIELD1'.  Of course, if you want
> column headers, then you see the same result:
>
> sqlite> .header  on
> sqlite> select * from QQQ;
> FIELD1,FIELD2
> FIELD1,FIELD2
> 1,ABC
> 2,BCD
> 3,CDE
>
> Or are you saying that you misinterpreted the first row of results from
> the select as column headers when they were not?  I do not see how you
> could possibly obtain something that looks like:
>
> > > > Table will then be like this:
> > > >
> > > > FIELD1 FIELD2
> > > > -
> > > > 0 FIELD2
> > > > 1 ABC
> > > > 2 BCD
> > > > 3 CDE
>
> ---
> ()  ascii ribbon campaign against html e-mail
> /\  www.asciiribbon.org
>
>
> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > boun...@sqlite.org] On Behalf Of Bart Smissaert
> > Sent: Saturday, 08 June, 2013 10:52
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] Strange table behaviour after text import with
> > sqlite3.exe
> >
> > Not sure what you are getting at now.
> >
> > > Select * from QQQ
> >
> > That is exactly what I did.
> >
> > It is all cleared up now in any case.
> >
> >
> > RBS
> >
> >
> > On Sat, Jun 8, 2013 at 4:56 PM, Keith Medcalf 
> wrote:
> >
> > > > Table will then be like this:
> > > >
> > > > FIELD1 FIELD2
> > > > -
> > > > 0 FIELD2
> > > > 1 ABC
> > > > 2 BCD
> > > > 3 CDE
> > >
> > > How did you arrive at this -- the table looks NOTHING like what you (I
> > can
> > > only assume) you ASSUMED it looks like.
> > >
> > > Instead of assuming what the table looks like, why not actually SEE
> what
> > > it looks like by executing:
> > >
> > > Select * from QQQ;
> > >
> > > The error of your untested assumption should then be clear ...
> > >
> > > ---
> > > ()  ascii ribbon campaign against html e-mail
> > > /\  www.asciiribbon.org
> > >
> > >
> > > > -Original Message-
> > > > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > > > boun...@sqlite.org] On Behalf Of Bart Smissaert
> > > > Sent: Saturday, 08 June, 2013 08:43
> > > > To: General Discussion of SQLite Database
> > > > Subject: [sqlite] Strange table behaviour after text import with
> > > > sqlite3.exe
> > > >
> > > > Have table defined like this:
> > > >
> > > > CREATE TABLE QQQ([FIELD1] INTEGER, [FIELD2] TEXT)
> > > >
> > > > Table is empty, so has no records.
> > > >
> > > > Then I import a text file with this data:
> > > >
> > > > FIELD1,FIELD2
> > > > 1,ABC
> > > > 2,BCD
> > > > 3,CDE
> > > >
> > > > This is via sqlite3.exe with:
> > > >
> > > > .mode csv
> > > > .import textfilename QQQ
> > > >
> > > > Table will then be like this:
> > > >
> > > > FIELD1 FIELD2
> > > > -
> > > > 0 FIELD2
> > > > 1 ABC
> > > > 2 BCD
> > > > 3 CDE
> > > >
> > > > This is all fine and as expected.
> > > > However I am unable to produce any records when doing a select
> > > > with a where clause specifying field1 to be zero.
> > > >
> > > > Tried all:
> > > > select * from qqq where field1 = 0
> > > > select * from qqq where field1 = '0'
> > > > select * from qqq where field1 = ''
> > > > select * from qqq where field1 is null
> > > >
> > > > Nil producing a record.
> > > >
> > > > Any idea what is going on here or what I might be doing wrong?
> > > >
> > > >
> > > > RBS
> > > > ___
> > > > 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
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> 

Re: [sqlite] Strange table behaviour after text import with sqlite3.exe

[Keith Medcalf]

I don't think you did because if you had then what you would have seen would be 
as follows:

SQLite version 3.7.17 2013-06-05 16:17:21
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE QQQ([FIELD1] INTEGER, [FIELD2] TEXT);
sqlite> .mode csv
sqlite> .import csv.txt QQQ
sqlite> select * from QQQ;
FIELD1,FIELD2
1,ABC
2,BCD
3,CDE

Which clearly indicates that there are 4 rows in table  and that the value 
of FIELD1 where field2='FIELD2' is 'FIELD1'.  Of course, if you want column 
headers, then you see the same result:

sqlite> .header  on
sqlite> select * from QQQ;
FIELD1,FIELD2
FIELD1,FIELD2
1,ABC
2,BCD
3,CDE

Or are you saying that you misinterpreted the first row of results from the 
select as column headers when they were not?  I do not see how you could 
possibly obtain something that looks like:

> > > Table will then be like this:
> > >
> > > FIELD1 FIELD2
> > > -
> > > 0 FIELD2
> > > 1 ABC
> > > 2 BCD
> > > 3 CDE

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Bart Smissaert
> Sent: Saturday, 08 June, 2013 10:52
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Strange table behaviour after text import with
> sqlite3.exe
> 
> Not sure what you are getting at now.
> 
> > Select * from QQQ
> 
> That is exactly what I did.
> 
> It is all cleared up now in any case.
> 
> 
> RBS
> 
> 
> On Sat, Jun 8, 2013 at 4:56 PM, Keith Medcalf  wrote:
> 
> > > Table will then be like this:
> > >
> > > FIELD1 FIELD2
> > > -
> > > 0 FIELD2
> > > 1 ABC
> > > 2 BCD
> > > 3 CDE
> >
> > How did you arrive at this -- the table looks NOTHING like what you (I
> can
> > only assume) you ASSUMED it looks like.
> >
> > Instead of assuming what the table looks like, why not actually SEE what
> > it looks like by executing:
> >
> > Select * from QQQ;
> >
> > The error of your untested assumption should then be clear ...
> >
> > ---
> > ()  ascii ribbon campaign against html e-mail
> > /\  www.asciiribbon.org
> >
> >
> > > -Original Message-
> > > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > > boun...@sqlite.org] On Behalf Of Bart Smissaert
> > > Sent: Saturday, 08 June, 2013 08:43
> > > To: General Discussion of SQLite Database
> > > Subject: [sqlite] Strange table behaviour after text import with
> > > sqlite3.exe
> > >
> > > Have table defined like this:
> > >
> > > CREATE TABLE QQQ([FIELD1] INTEGER, [FIELD2] TEXT)
> > >
> > > Table is empty, so has no records.
> > >
> > > Then I import a text file with this data:
> > >
> > > FIELD1,FIELD2
> > > 1,ABC
> > > 2,BCD
> > > 3,CDE
> > >
> > > This is via sqlite3.exe with:
> > >
> > > .mode csv
> > > .import textfilename QQQ
> > >
> > > Table will then be like this:
> > >
> > > FIELD1 FIELD2
> > > -
> > > 0 FIELD2
> > > 1 ABC
> > > 2 BCD
> > > 3 CDE
> > >
> > > This is all fine and as expected.
> > > However I am unable to produce any records when doing a select
> > > with a where clause specifying field1 to be zero.
> > >
> > > Tried all:
> > > select * from qqq where field1 = 0
> > > select * from qqq where field1 = '0'
> > > select * from qqq where field1 = ''
> > > select * from qqq where field1 is null
> > >
> > > Nil producing a record.
> > >
> > > Any idea what is going on here or what I might be doing wrong?
> > >
> > >
> > > RBS
> > > ___
> > > 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



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

Re: [sqlite] Strange table behaviour after text import with sqlite3.exe

[Bart Smissaert]

Not sure what you are getting at now.

> Select * from QQQ

That is exactly what I did.

It is all cleared up now in any case.


RBS


On Sat, Jun 8, 2013 at 4:56 PM, Keith Medcalf  wrote:

> > Table will then be like this:
> >
> > FIELD1 FIELD2
> > -
> > 0 FIELD2
> > 1 ABC
> > 2 BCD
> > 3 CDE
>
> How did you arrive at this -- the table looks NOTHING like what you (I can
> only assume) you ASSUMED it looks like.
>
> Instead of assuming what the table looks like, why not actually SEE what
> it looks like by executing:
>
> Select * from QQQ;
>
> The error of your untested assumption should then be clear ...
>
> ---
> ()  ascii ribbon campaign against html e-mail
> /\  www.asciiribbon.org
>
>
> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > boun...@sqlite.org] On Behalf Of Bart Smissaert
> > Sent: Saturday, 08 June, 2013 08:43
> > To: General Discussion of SQLite Database
> > Subject: [sqlite] Strange table behaviour after text import with
> > sqlite3.exe
> >
> > Have table defined like this:
> >
> > CREATE TABLE QQQ([FIELD1] INTEGER, [FIELD2] TEXT)
> >
> > Table is empty, so has no records.
> >
> > Then I import a text file with this data:
> >
> > FIELD1,FIELD2
> > 1,ABC
> > 2,BCD
> > 3,CDE
> >
> > This is via sqlite3.exe with:
> >
> > .mode csv
> > .import textfilename QQQ
> >
> > Table will then be like this:
> >
> > FIELD1 FIELD2
> > -
> > 0 FIELD2
> > 1 ABC
> > 2 BCD
> > 3 CDE
> >
> > This is all fine and as expected.
> > However I am unable to produce any records when doing a select
> > with a where clause specifying field1 to be zero.
> >
> > Tried all:
> > select * from qqq where field1 = 0
> > select * from qqq where field1 = '0'
> > select * from qqq where field1 = ''
> > select * from qqq where field1 is null
> >
> > Nil producing a record.
> >
> > Any idea what is going on here or what I might be doing wrong?
> >
> >
> > RBS
> > ___
> > 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] Transaction

[RSmith]

Apologies, this went to wrong mailbox first...
--

Thank you very much for taking the time - this did answer my question.

If I may one more question related to this:  Let's say I am using transactions 
as validation stops on a long list of inputs - So
that the main program does some checking along the way, I have two choices, I 
can start with a BEGIN and do COMMIT at the very end,
and possibly ROLLBACK everything once I get an error and start over. 
Alternatively (and this is obviously what I want to do), I can
do BEGIN, then check say 1000 records, then COMMIT, then BEGIN, check 1000, 
COMMIT, etc. This way if I get an error, I need only
rollback a little bit.

The question is, how high is the overhead on BEGIN-COMMITs?
In other words... Should I try to maybe change the program to do all checking 
in one pass, then write to the DB in another pass?
(which is better if Transaction overhead is high, but worse if Transaction 
overhead is negligible).

Thanks again!

On 2013/06/08 17:16, Simon Slavin wrote:

 On 8 Jun 2013, at 3:35pm, RSmith   wrote:
 These two pieces of code do not do the same thing.

 (A) talks about three sets of changes: one overall set, which includes two 
different subsets.  Consider you were updating your league tables after a day 
in which two tennis matches were played.  You need to make a change to details 
about match A, then a change to details about match B, and you also need to 
make changes to your league positions.  The change to details about  match A 
have nothing to do with the change to details about match B.  So you have 
released SAVEPOINT A before you start SAVEPOINT B.

 (B) talks about a nested set of changes.  You do not want the change to 
SAVEPOINT A to happen unless the change to SAVEPOINT B worked.  Here, suppose 
you are recording the result of a race.  You have to modify both the details of 
the race and the individual stats of the winner.  Here, if there is a problem 
with modifying the result of the race (SAVEPOINT B) then you need to make sure 
that you do not modify the stats of the individual runner (SAVEPOINT A)

 As you can see, the two options are suitable for different situations.  You 
should use whatever structure reflects the changes you are making.

 But your basic question was about how long these things took.  Assuming that 
you do reach RELEASE (and not ROLLBACK) on both, they will take near-enough the 
same time.  However, the BEGIN/COMMIT in (B) is not needed because all the 
changes made are in at least one SAVEPOINT.  Leaving it out may speed up your 
program slightly.  THe outer SAVEPOINT has exactly the same effect as having a 
BEGIN/COMMIT.

 Simon.



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

Re: [sqlite] Strange table behaviour after text import with sqlite3.exe

[Keith Medcalf]

> Table will then be like this:
> 
> FIELD1 FIELD2
> -
> 0 FIELD2
> 1 ABC
> 2 BCD
> 3 CDE

How did you arrive at this -- the table looks NOTHING like what you (I can only 
assume) you ASSUMED it looks like.

Instead of assuming what the table looks like, why not actually SEE what it 
looks like by executing:

Select * from QQQ;

The error of your untested assumption should then be clear ...

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Bart Smissaert
> Sent: Saturday, 08 June, 2013 08:43
> To: General Discussion of SQLite Database
> Subject: [sqlite] Strange table behaviour after text import with
> sqlite3.exe
> 
> Have table defined like this:
> 
> CREATE TABLE QQQ([FIELD1] INTEGER, [FIELD2] TEXT)
> 
> Table is empty, so has no records.
> 
> Then I import a text file with this data:
> 
> FIELD1,FIELD2
> 1,ABC
> 2,BCD
> 3,CDE
> 
> This is via sqlite3.exe with:
> 
> .mode csv
> .import textfilename QQQ
> 
> Table will then be like this:
> 
> FIELD1 FIELD2
> -
> 0 FIELD2
> 1 ABC
> 2 BCD
> 3 CDE
> 
> This is all fine and as expected.
> However I am unable to produce any records when doing a select
> with a where clause specifying field1 to be zero.
> 
> Tried all:
> select * from qqq where field1 = 0
> select * from qqq where field1 = '0'
> select * from qqq where field1 = ''
> select * from qqq where field1 is null
> 
> Nil producing a record.
> 
> Any idea what is going on here or what I might be doing wrong?
> 
> 
> RBS
> ___
> 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] Transaction

[Simon Slavin]

On 8 Jun 2013, at 3:35pm, RSmith  wrote:

> A) so to have:
> BEGIN
>  SAVEPOINT A
>  ...
>  RELEASE A
>  SAVEPOINT B
>  ...
>  RELEASE B
>  ...
> COMMIT;
> 
> B) or to have:
> BEGIN
>  SAVEPOINT A
>   ...
>SAVEPOINT B
>  ...
>RELEASE B
>  ...
>  RELEASE A
> COMMIT;
> 
> I realise both ways have code efficiency issues, but just is there a real 
> execution speed difference?

These two pieces of code do not do the same thing.

(A) talks about three sets of changes: one overall set, which includes two 
different subsets.  Consider you were updating your league tables after a day 
in which two tennis matches were played.  You need to make a change to details 
about match A, then a change to details about match B, and you also need to 
make changes to your league positions.  The change to details about  match A 
have nothing to do with the change to details about match B.  So you have 
released SAVEPOINT A before you start SAVEPOINT B.

(B) talks about a nested set of changes.  You do not want the change to 
SAVEPOINT A to happen unless the change to SAVEPOINT B worked.  Here, suppose 
you are recording the result of a race.  You have to modify both the details of 
the race and the individual stats of the winner.  Here, if there is a problem 
with modifying the result of the race (SAVEPOINT B) then you need to make sure 
that you do not modify the stats of the individual runner (SAVEPOINT A)

As you can see, the two options are suitable for different situations.  You 
should use whatever structure reflects the changes you are making.

But your basic question was about how long these things took.  Assuming that 
you do reach RELEASE (and not ROLLBACK) on both, they will take near-enough the 
same time.  However, the BEGIN/COMMIT in (B) is not needed because all the 
changes made are in at least one SAVEPOINT.  Leaving it out may speed up your 
program slightly.  THe outer SAVEPOINT has exactly the same effect as having a 
BEGIN/COMMIT.

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

Re: [sqlite] Strange table behaviour after text import with sqlite3.exe

[Bart Smissaert]

Yes, thanks, all clear now.

RBS


On Sat, Jun 8, 2013 at 4:03 PM, Michael Black  wrote:

> Or to get all the non-integer records.
>
>
> select * from qqq where typeof(field1) <> 'integer';
>
> Mike
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Bart Smissaert
> Sent: Saturday, June 08, 2013 9:58 AM
> To: rsm...@rsweb.co.za; General Discussion of SQLite Database
> Subject: Re: [sqlite] Strange table behaviour after text import with
> sqlite3.exe
>
> Aaah, OK, that answers my question!
> This is something I hadn't realised at all and good to know that one.
> Thanks for clearing this up.
>
> RBS
>
>
>
> On Sat, Jun 8, 2013 at 3:48 PM, RSmith  wrote:
>
> > Yes, FIELD1 values are formatted int he output to be displayed as 0 since
> > it is a INTEGER field, but the real value of FIELD1 is "FIELD1" for the
> 0th
> > record, since that is what was imported from the CSV. The formatted value
> > is not always the same as the real value.
> >
> > try:
> >
> > select * from qqq where field1 = "FIELD1"
> >
> >
> > It will pop out a record I'm sure.
> >
> >
> >
> > On 2013/06/08 16:42, Bart Smissaert wrote:
> >
> >> Have table defined like this:
> >>
> >> CREATE TABLE QQQ([FIELD1] INTEGER, [FIELD2] TEXT)
> >>
> >> Table is empty, so has no records.
> >>
> >> Then I import a text file with this data:
> >>
> >> FIELD1,FIELD2
> >> 1,ABC
> >> 2,BCD
> >> 3,CDE
> >>
> >> This is via sqlite3.exe with:
> >>
> >> .mode csv
> >> .import textfilename QQQ
> >>
> >> Table will then be like this:
> >>
> >> FIELD1 FIELD2
> >> --**---
> >> 0 FIELD2
> >> 1 ABC
> >> 2 BCD
> >> 3 CDE
> >>
> >> This is all fine and as expected.
> >> However I am unable to produce any records when doing a select
> >> with a where clause specifying field1 to be zero.
> >>
> >> Tried all:
> >> select * from qqq where field1 = 0
> >> select * from qqq where field1 = '0'
> >> select * from qqq where field1 = ''
> >> select * from qqq where field1 is null
> >>
> >> Nil producing a record.
> >>
> >> Any idea what is going on here or what I might be doing wrong?
> >>
> >>
> >> RBS
> >> __**_
> >> sqlite-users mailing list
> >> sqlite-users@sqlite.org
> >>
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<
> http://sqli
> te.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<
> http://sqli
> te.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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange table behaviour after text import with sqlite3.exe

[Michael Black]

Or to get all the non-integer records.


select * from qqq where typeof(field1) <> 'integer';

Mike

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Bart Smissaert
Sent: Saturday, June 08, 2013 9:58 AM
To: rsm...@rsweb.co.za; General Discussion of SQLite Database
Subject: Re: [sqlite] Strange table behaviour after text import with
sqlite3.exe

Aaah, OK, that answers my question!
This is something I hadn't realised at all and good to know that one.
Thanks for clearing this up.

RBS



On Sat, Jun 8, 2013 at 3:48 PM, RSmith  wrote:

> Yes, FIELD1 values are formatted int he output to be displayed as 0 since
> it is a INTEGER field, but the real value of FIELD1 is "FIELD1" for the
0th
> record, since that is what was imported from the CSV. The formatted value
> is not always the same as the real value.
>
> try:
>
> select * from qqq where field1 = "FIELD1"
>
>
> It will pop out a record I'm sure.
>
>
>
> On 2013/06/08 16:42, Bart Smissaert wrote:
>
>> Have table defined like this:
>>
>> CREATE TABLE QQQ([FIELD1] INTEGER, [FIELD2] TEXT)
>>
>> Table is empty, so has no records.
>>
>> Then I import a text file with this data:
>>
>> FIELD1,FIELD2
>> 1,ABC
>> 2,BCD
>> 3,CDE
>>
>> This is via sqlite3.exe with:
>>
>> .mode csv
>> .import textfilename QQQ
>>
>> Table will then be like this:
>>
>> FIELD1 FIELD2
>> --**---
>> 0 FIELD2
>> 1 ABC
>> 2 BCD
>> 3 CDE
>>
>> This is all fine and as expected.
>> However I am unable to produce any records when doing a select
>> with a where clause specifying field1 to be zero.
>>
>> Tried all:
>> select * from qqq where field1 = 0
>> select * from qqq where field1 = '0'
>> select * from qqq where field1 = ''
>> select * from qqq where field1 is null
>>
>> Nil producing a record.
>>
>> Any idea what is going on here or what I might be doing wrong?
>>
>>
>> RBS
>> __**_
>> 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

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

Re: [sqlite] Strange table behaviour after text import with sqlite3.exe

[Bart Smissaert]

Aaah, OK, that answers my question!
This is something I hadn't realised at all and good to know that one.
Thanks for clearing this up.

RBS



On Sat, Jun 8, 2013 at 3:48 PM, RSmith  wrote:

> Yes, FIELD1 values are formatted int he output to be displayed as 0 since
> it is a INTEGER field, but the real value of FIELD1 is "FIELD1" for the 0th
> record, since that is what was imported from the CSV. The formatted value
> is not always the same as the real value.
>
> try:
>
> select * from qqq where field1 = "FIELD1"
>
>
> It will pop out a record I'm sure.
>
>
>
> On 2013/06/08 16:42, Bart Smissaert wrote:
>
>> Have table defined like this:
>>
>> CREATE TABLE QQQ([FIELD1] INTEGER, [FIELD2] TEXT)
>>
>> Table is empty, so has no records.
>>
>> Then I import a text file with this data:
>>
>> FIELD1,FIELD2
>> 1,ABC
>> 2,BCD
>> 3,CDE
>>
>> This is via sqlite3.exe with:
>>
>> .mode csv
>> .import textfilename QQQ
>>
>> Table will then be like this:
>>
>> FIELD1 FIELD2
>> --**---
>> 0 FIELD2
>> 1 ABC
>> 2 BCD
>> 3 CDE
>>
>> This is all fine and as expected.
>> However I am unable to produce any records when doing a select
>> with a where clause specifying field1 to be zero.
>>
>> Tried all:
>> select * from qqq where field1 = 0
>> select * from qqq where field1 = '0'
>> select * from qqq where field1 = ''
>> select * from qqq where field1 is null
>>
>> Nil producing a record.
>>
>> Any idea what is going on here or what I might be doing wrong?
>>
>>
>> RBS
>> __**_
>> 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] Strange table behaviour after text import with sqlite3.exe

[Bart Smissaert]

Whatever it gets turned into, my question is how I can select that record
with a where
clause specifying field1?
Surely, there must be some way.

RBS


On Sat, Jun 8, 2013 at 3:48 PM, Michael Black  wrote:

> What makes you think field1 gets turned into a zero?  Fields are really
> typeless in SQLite3
>
> Your .dump should look like this:
>
> SQLite version 3.7.16.2 2013-04-12 11:52:43
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> CREATE TABLE QQQ([FIELD1] INTEGER, [FIELD2] TEXT);
> sqlite> .mode csv
> sqlite> .import qqq qqq
> Error: cannot open "qqq"
> sqlite> .import qqq.txt qqq
> sqlite> .dump
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE QQQ([FIELD1] INTEGER, [FIELD2] TEXT);
> INSERT INTO "QQQ" VALUES('FIELD1','FIELD2');
> INSERT INTO "QQQ" VALUES(1,'ABC');
> INSERT INTO "QQQ" VALUES(2,'BCD');
> INSERT INTO "QQQ" VALUES(3,'CDE');
> COMMIT;
>
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Bart Smissaert
> Sent: Saturday, June 08, 2013 9:43 AM
> To: General Discussion of SQLite Database
> Subject: [sqlite] Strange table behaviour after text import with
> sqlite3.exe
>
> Have table defined like this:
>
> CREATE TABLE QQQ([FIELD1] INTEGER, [FIELD2] TEXT)
>
> Table is empty, so has no records.
>
> Then I import a text file with this data:
>
> FIELD1,FIELD2
> 1,ABC
> 2,BCD
> 3,CDE
>
> This is via sqlite3.exe with:
>
> .mode csv
> .import textfilename QQQ
>
> Table will then be like this:
>
> FIELD1 FIELD2
> -
> 0 FIELD2
> 1 ABC
> 2 BCD
> 3 CDE
>
> This is all fine and as expected.
> However I am unable to produce any records when doing a select
> with a where clause specifying field1 to be zero.
>
> Tried all:
> select * from qqq where field1 = 0
> select * from qqq where field1 = '0'
> select * from qqq where field1 = ''
> select * from qqq where field1 is null
>
> Nil producing a record.
>
> Any idea what is going on here or what I might be doing wrong?
>
>
> RBS
> ___
> 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] Strange table behaviour after text import with sqlite3.exe

[RSmith]

Yes, FIELD1 values are formatted int he output to be displayed as 0 since it is a INTEGER field, but the real value of FIELD1 is 
"FIELD1" for the 0th record, since that is what was imported from the CSV. The formatted value is not always the same as the real value.


try:

select * from qqq where field1 = "FIELD1"


It will pop out a record I'm sure.


On 2013/06/08 16:42, Bart Smissaert wrote:

Have table defined like this:

CREATE TABLE QQQ([FIELD1] INTEGER, [FIELD2] TEXT)

Table is empty, so has no records.

Then I import a text file with this data:

FIELD1,FIELD2
1,ABC
2,BCD
3,CDE

This is via sqlite3.exe with:

.mode csv
.import textfilename QQQ

Table will then be like this:

FIELD1 FIELD2
-
0 FIELD2
1 ABC
2 BCD
3 CDE

This is all fine and as expected.
However I am unable to produce any records when doing a select
with a where clause specifying field1 to be zero.

Tried all:
select * from qqq where field1 = 0
select * from qqq where field1 = '0'
select * from qqq where field1 = ''
select * from qqq where field1 is null

Nil producing a record.

Any idea what is going on here or what I might be doing wrong?


RBS
___
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] Strange table behaviour after text import with sqlite3.exe

[Michael Black]

What makes you think field1 gets turned into a zero?  Fields are really
typeless in SQLite3

Your .dump should look like this:

SQLite version 3.7.16.2 2013-04-12 11:52:43
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE QQQ([FIELD1] INTEGER, [FIELD2] TEXT);
sqlite> .mode csv
sqlite> .import qqq qqq
Error: cannot open "qqq"
sqlite> .import qqq.txt qqq
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE QQQ([FIELD1] INTEGER, [FIELD2] TEXT);
INSERT INTO "QQQ" VALUES('FIELD1','FIELD2');
INSERT INTO "QQQ" VALUES(1,'ABC');
INSERT INTO "QQQ" VALUES(2,'BCD');
INSERT INTO "QQQ" VALUES(3,'CDE');
COMMIT;



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Bart Smissaert
Sent: Saturday, June 08, 2013 9:43 AM
To: General Discussion of SQLite Database
Subject: [sqlite] Strange table behaviour after text import with sqlite3.exe

Have table defined like this:

CREATE TABLE QQQ([FIELD1] INTEGER, [FIELD2] TEXT)

Table is empty, so has no records.

Then I import a text file with this data:

FIELD1,FIELD2
1,ABC
2,BCD
3,CDE

This is via sqlite3.exe with:

.mode csv
.import textfilename QQQ

Table will then be like this:

FIELD1 FIELD2
-
0 FIELD2
1 ABC
2 BCD
3 CDE

This is all fine and as expected.
However I am unable to produce any records when doing a select
with a where clause specifying field1 to be zero.

Tried all:
select * from qqq where field1 = 0
select * from qqq where field1 = '0'
select * from qqq where field1 = ''
select * from qqq where field1 is null

Nil producing a record.

Any idea what is going on here or what I might be doing wrong?


RBS
___
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] Strange table behaviour after text import with sqlite3.exe

[Bart Smissaert]

Have table defined like this:

CREATE TABLE QQQ([FIELD1] INTEGER, [FIELD2] TEXT)

Table is empty, so has no records.

Then I import a text file with this data:

FIELD1,FIELD2
1,ABC
2,BCD
3,CDE

This is via sqlite3.exe with:

.mode csv
.import textfilename QQQ

Table will then be like this:

FIELD1 FIELD2
-
0 FIELD2
1 ABC
2 BCD
3 CDE

This is all fine and as expected.
However I am unable to produce any records when doing a select
with a where clause specifying field1 to be zero.

Tried all:
select * from qqq where field1 = 0
select * from qqq where field1 = '0'
select * from qqq where field1 = ''
select * from qqq where field1 is null

Nil producing a record.

Any idea what is going on here or what I might be doing wrong?


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

Re: [sqlite] Transaction

[RSmith]

Ugh, of course, stupid mistake in explanation -
Of course it must be SAVEPOINT, not again BEGIN.

A) so to have:
BEGIN
  SAVEPOINT A
  ...
  RELEASE A
  SAVEPOINT B
  ...
  RELEASE B
  ...
COMMIT;

B) or to have:
BEGIN
  SAVEPOINT A
   ...
SAVEPOINT B
  ...
RELEASE B
  ...
  RELEASE A
COMMIT;

I realise both ways have code efficiency issues, but just is there a real 
execution speed difference?


On 2013/06/08 16:20, Igor Tandetnik wrote:

On 6/8/2013 10:17 AM, RSmith wrote:

Could someone please shortly discuss performance considerations for
having nested Transactions vs. sequential transactions in SQLite.


There ain't no such thing as a nested transaction. The second BEGIN statement 
will simply fail.


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

Re: [sqlite] Transaction

[Igor Tandetnik]

On 6/8/2013 10:17 AM, RSmith wrote:

Could someone please shortly discuss performance considerations for
having nested Transactions vs. sequential transactions in SQLite.


There ain't no such thing as a nested transaction. The second BEGIN 
statement will simply fail.

--
Igor Tandetnik

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

[sqlite] Transaction

[RSmith]

Hi all,

I've read some of the standard help on sqlite.org but have not studied the code at all, so turning to the experts for some advice 
(there is no urgency, I would just really like some insight).


Could someone please shortly discuss performance considerations for having nested Transactions vs. sequential transactions in 
SQLite. I realise that nested ones are safer, but Im always looking to find performance increases. Now, to my mind, in the following 
two examples the safety is no issue:

A)
  Begin
  Begin
 ...
  Commit;
  Begin
 ...
  Commit;
  Begin
 ...
  Commit;
  Commit;

B)
  Begin
  Begin
  Begin
 Begin
 ...
  Commit;
  
  Commit;
  
  Commit;
  
  Commit;

- I think that B will be faster, or at least, that's what I seem to glean from 
the help texts - but I'm not very sure.
Is B faster or A faster? and if so, are there circumastances in which this 
might differ?
Any other considerations needed to keep in mind?

Thank you very much and have a great day!
RS

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

[sqlite] "unable to open database file" after upgrade from 1.0.85 to 86

[Leaf, Peter]

I made no changes to my application other than updating the sqlite control via 
Nuget. Now when my application runs it throws the following error:

SqliteException (0x80004005): Unable to open database file.

If I run using 1.0.85 I get no error.

Any help would be greatly appreciated.

Thanks,

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