Re: [sqlite] Bug? SQLite command-line result is different from Perl DBI::Sqlite result

[Igor Korot]

Hi,

On Tue, Jan 7, 2020 at 2:01 AM Clemens Ladisch  wrote:
>
> Richard Hipp wrote:
> > On 1/5/20, Keith Medcalf  wrote:
> >> select * from a, b, c using (id); -- very strange result
> >
> > PostgreSQL and MySQL process the query as follows:
> >
> >SELECT * FROM a, (b JOIN c USING(id));
> >
> > SQLite processes the query like this:
> >
> >SELECT * FROM (a,b) JOIN c USING (id);
> >
> > I don't know which is correct.  Perhaps the result is undefined.
>
> Assuming the following query:
>
>   SELECT * FROM a, b JOIN c USING (id);
>
> SQL-92 says:
> |7.4  
> |
> | ::= FROM  [ {   reference> }... ]
> |
> |6.3  
> |
> | ::=
> |[ [ AS ] 
> |   [] ]
> | |  [ AS ] 
> |   []
> | | 
> |
> |7.5  
> |
> | ::=
> |   
> | | 
> | |   
> |
> | ::=
> |  CROSS JOIN 
> |
> | ::=
> |  [ NATURAL ] [  ] JOIN
> |[  ]
>
> It is not possible to have such a  inside a , so
> b and c must be joined first.
>
> SQLite actually parses the comma as a join:
>
>   SELECT * FROM a CROSS JOIN b JOIN c USING (id);
>
> If the query were written like this, joining a and b first would be
> correct.  (As far as I can see, the standard does not say how to handle
> ambiguous parts of the grammar, so it would also be allowed to produce
> "b JOIN c" first.)

That's why one should never use that "MS JOIN extension" and should
simply write:

SELECT ... FROM a,b,c WHERE a.x = b.x AND b.x = c.y AND...;

Thank you.

>
>
> 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] Bug? SQLite command-line result is different from Perl DBI::Sqlite result

[Clemens Ladisch]

Richard Hipp wrote:
> On 1/5/20, Keith Medcalf  wrote:
>> select * from a, b, c using (id); -- very strange result
>
> PostgreSQL and MySQL process the query as follows:
>
>SELECT * FROM a, (b JOIN c USING(id));
>
> SQLite processes the query like this:
>
>SELECT * FROM (a,b) JOIN c USING (id);
>
> I don't know which is correct.  Perhaps the result is undefined.

Assuming the following query:

  SELECT * FROM a, b JOIN c USING (id);

SQL-92 says:
|7.4  
|
| ::= FROM  [ {   
}... ]
|
|6.3  
|
| ::=
|[ [ AS ] 
|   [] ]
| |  [ AS ] 
|   []
| | 
|
|7.5  
|
| ::=
|   
| | 
| |   
|
| ::=
|  CROSS JOIN 
|
| ::=
|  [ NATURAL ] [  ] JOIN
|[  ]

It is not possible to have such a  inside a , so
b and c must be joined first.

SQLite actually parses the comma as a join:

  SELECT * FROM a CROSS JOIN b JOIN c USING (id);

If the query were written like this, joining a and b first would be
correct.  (As far as I can see, the standard does not say how to handle
ambiguous parts of the grammar, so it would also be allowed to produce
"b JOIN c" first.)


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

Re: [sqlite] Bug? SQLite command-line result is different from Perl DBI::Sqlite result

[Keith Medcalf]

On Sunday, 5 January, 2020 04:42, Richard Hipp  wrote:

>On 1/5/20, Keith Medcalf  wrote:

>> Hrm.  Inconsistent/incorrect results.  Consider:

>> create table a(id integer primary key, a);
>> insert into a values (1,1), (2,1), (3,1);
>> create table b(id integer primary key, b);
>> insert into b values (1,2), (3,2), (4,2);
>> create table c(id integer primary key, c);
>> insert into c values (1,3), (4,3), (5,3);

>> select * from a, b, c using (id); -- very strange result

>> id  a   id  b   c
>> --  --  --  --  --
>> 1   1   1   2   3
>> 1   1   3   2   3
>> 1   1   4   2   3

>PostgreSQL and MySQL process the query as follows:

>   SELECT * FROM a, (b JOIN c USING(id));

>SQLite processes the query like this:

>   SELECT * FROM (a,b) JOIN c USING (id);

>I don't know which is correct.  Perhaps the result is undefined.

>Note that both MySQL and SQLite do allow you to use parentheses, as
>shown in my examples, to define the order of evaluation.  PostgreSQL
>does not, sadly.

>MS-SQL does not (as far as I can tell) support the USING syntax on a
>join.

Aha!  So as far as SQLite is concerned the syntax "... JOIN  USING ()" is 
effectively binding the using expression for the nested nested loop descent 
into into table  only and does not bind against the immediately preceeding 
LHS JOIN table.  

By adding some indexes and order by that cause the nesting order to change it 
appear that "a, b join c using (id)" is always processed as selecting the first 
lexically named id column irrepective of nesting order (that is "a, b join c 
using (id)" always becomes "a, b, c where a.id == c.id" and that "b, a join c 
using (id)" always becomes "a, b, c where b.id == c.id" even when the optimizer 
chooses to re-arrange the nesting order (such as by additional indexes and 
order by's)).

Since the "id" column to use is ambiguous for descent into "c" should not an 
"ambiguous column name" error be thrown?  The ambiguity only does not exist if 
ALL columns named "id" (for all tables that could be in an outer loop 
respective to "c") are constrained equal -- that is a,b,c using (id) -> a, b, c 
where a.id == b.id and a.id == c.id and b.id == c.id.

Since "natural join" devolves into a using, does not the same problem exist 
there as well?

Since any change is likely to have an effect on already existing and functional 
applications, could the behaviour be documented somewhere perhaps?

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



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

Re: [sqlite] SQLite command-line result is different from Perl DBI::Sqlite result (Keith Medcalf)

[Simon Slavin]

On 5 Jan 2020, at 5:01pm, Amer Neely  wrote:

> But the question still remains, why the different results?

The optimizer gets improved from time to time in SQLite versions.  SQLite 
solved the problem faster by breaking down your query differently: deciding 
whether to do one scan or use two indexes, which table to search first, etc..

Your query was appears to violate the SQL standard, by failing to define the 
JOIN.  It should probably have resulted in a syntax error, but it didn't.  And 
it just happened that one version of SQLite interpreted the query one way, 
another interpreted it another way.

I advise you change your software to use one of the queries DRH supplied:

>   SELECT * FROM a, (b JOIN c USING(id));
> 
>   SELECT * FROM (a,b) JOIN c USING (id);

This will remove the ambiguity, meaning that however SQLite interprets the 
query in the future, or even if you switch to a different SQL engine, you get 
the result you wanted.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug? SQLite command-line result is different from Perl DBI::Sqlite result

[Amer Neely]

> On 1/5/20, Keith Medcalf  wrote:
>>
>> Hrm.  Inconsistent/incorrect results.  Consider:
>>
>> create table a(id integer primary key, a);
>> insert into a values (1,1), (2,1), (3,1);
>> create table b(id integer primary key, b);
>> insert into b values (1,2), (3,2), (4,2);
>> create table c(id integer primary key, c);
>> insert into c values (1,3), (4,3), (5,3);
>>
>> select * from a, b, c using (id); -- very strange result
>>
>> id  a   id  b   c
>> --  --  --  --  --
>> 1   1   1   2   3
>> 1   1   3   2   3
>> 1   1   4   2   3
> 
> PostgreSQL and MySQL process the query as follows:
> 
>SELECT * FROM a, (b JOIN c USING(id));
> 
> SQLite processes the query like this:
> 
>SELECT * FROM (a,b) JOIN c USING (id);
> 
> I don't know which is correct.  Perhaps the result is undefined.
> 
> Note that both MySQL and SQLite do allow you to use parentheses, as
> shown in my examples, to define the order of evaluation.  PostgreSQL
> does not, sadly.
> 
> MS-SQL does not (as far as I can tell) support the USING syntax on a join.
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org

Ahh. More to learn. Thank you for the use of parentheses, I will have to
check my queries for that.

i did manage to get the query working by grouping on artists.artistid.
-- 
Amer Neely
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite command-line result is different from Perl DBI::Sqlite result (Keith Medcalf)

[Amer Neely]

> On Saturday, 4 January, 2020 18:31, Amer Neely 
> wrote:
> 
>> I'm fairly new to SQLite, but have been using MySQL / mariadb in a local
>> and web-based environment for several years. So far I'm happy and
>> impressed with SQLite, but I recently noticed some odd behaviour with
>> one of my queries.
>> Using the command-line in a shell (Mac High Sierra) I get a particular
>> result from a query. The exact same query in a Perl script gives me a
>> different result. To my mind it is a simple query, getting the 5 latest
>> additions to my music library.
>> Command-line:
>> select artists.artist, artists.artistid, cds.title, cds.artistid,
>> cds.cdid, genres.genre, genres.artistid from artists, genres inner join
>> cds using (artistid) group by artists.artistid order by cds.id desc
>> limit 5;
>> gives me the correct result. However, in a Perl script it gives me a
>> different result. How is that possible? Could it be a Perl::DBI issue?
>> Many thanks for anyone able to shed some light on this.
> 
> Your select does not constrain artists so the result is non-deterministic
> in that the result will depend on how the query planner decides to
> execute the query.  That is, you have not specified any join constraints
> on artists.
> 
> SELECT * FROM A, B JOIN C USING (D);
> 
> means
> 
> SELECT *
>  FROM A, B, C
> WHERE B.D == C.D;
> 
> if you thought it meant
> 
> SELECT *
>  FROM A, B, C
> WHERE A.D == B.D
>   AND B.D == C.D;
> 
> then that is likely the reason for the discrepancy.
> 
> 
Thank you for your time and consideration. I have managed to 'solve'
this problem by constraining on artists.artistid by grouping on that.
But the question still remains, why the different results?


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

Re: [sqlite] SQLite command-line result is different from Perl DBI::Sqlite result (Igor Korot)

[Amer Neely]

-- > What is your version of Perl and the SQLite module?

This is perl 5, version 18, subversion 2 (v5.18.2) built for
darwin-thread-multi-2level
(with 2 registered patches, see perl -V for more detail)

Copyright 1987-2013, Larry Wall

SQLite version 3.30.1 2019-10-10 20:19:45
Enter ".help" for usage hints.

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

Re: [sqlite] Bug? SQLite command-line result is different from Perl DBI::Sqlite result

[Richard Hipp]

On 1/5/20, Richard Hipp  wrote:
>
> Note that both MySQL and SQLite do allow you to use parentheses, as
> shown in my examples, to define the order of evaluation.  PostgreSQL
> does not, sadly.
>

Apparently, in PostgreSQL you have to say:

   SELECT * FROM (SELECT * FROM a, b) AS x JOIN c USING(id);

-- 
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] Bug? SQLite command-line result is different from Perl DBI::Sqlite result

[Richard Hipp]

On 1/5/20, Keith Medcalf  wrote:
>
> Hrm.  Inconsistent/incorrect results.  Consider:
>
> create table a(id integer primary key, a);
> insert into a values (1,1), (2,1), (3,1);
> create table b(id integer primary key, b);
> insert into b values (1,2), (3,2), (4,2);
> create table c(id integer primary key, c);
> insert into c values (1,3), (4,3), (5,3);
>
> select * from a, b, c using (id); -- very strange result
>
> id  a   id  b   c
> --  --  --  --  --
> 1   1   1   2   3
> 1   1   3   2   3
> 1   1   4   2   3

PostgreSQL and MySQL process the query as follows:

   SELECT * FROM a, (b JOIN c USING(id));

SQLite processes the query like this:

   SELECT * FROM (a,b) JOIN c USING (id);

I don't know which is correct.  Perhaps the result is undefined.

Note that both MySQL and SQLite do allow you to use parentheses, as
shown in my examples, to define the order of evaluation.  PostgreSQL
does not, sadly.

MS-SQL does not (as far as I can tell) support the USING syntax on a join.

-- 
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] Bug? SQLite command-line result is different from Perl DBI::Sqlite result

[Keith Medcalf]

Hrm.  Inconsistent/incorrect results.  Consider:

create table a(id integer primary key, a);
insert into a values (1,1), (2,1), (3,1);
create table b(id integer primary key, b);
insert into b values (1,2), (3,2), (4,2);
create table c(id integer primary key, c);
insert into c values (1,3), (4,3), (5,3);

select * from a, b, c using (id); -- very strange result

id  a   id  b   c
--  --  --  --  --
1   1   1   2   3
1   1   3   2   3
1   1   4   2   3

select * from a, b using (id), c using (id); -- correct result

id  a   b   c
--  --  --  --
1   1   2   3

The first query should be processed as:

select * from a, b, c where b.id == c.id;

id  a   id  b   id  c
--  --  --  --  --  --
1   1   1   2   1   3
2   1   1   2   1   3
3   1   1   2   1   3
1   1   4   2   4   3
2   1   4   2   4   3
3   1   4   2   4   3

but with the c.id (third id column omitted).

Or it should be processed as the second query if the "using (id)" constraint 
applies to everywhere an "id" field is found, not just the LHS and RHS tables 
of the immediately proceeding join.

also 
select * from a natural join b natural join c;
-- returns no rows despite the column "id" existing commonly in all tables

This is with the current development release (and as far as I can tell, all 
prior versions).

-- 
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  On
>Behalf Of Keith Medcalf
>Sent: Saturday, 4 January, 2020 19:32
>To: SQLite mailing list 
>Subject: Re: [sqlite] SQLite command-line result is different from Perl
>DBI::Sqlite result
>
>
>
>
>
>--
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a lot about anticipated traffic volume.
>
>On Saturday, 4 January, 2020 18:31, Amer Neely 
>wrote:
>
>>I'm fairly new to SQLite, but have been using MySQL / mariadb in a local
>>and web-based environment for several years. So far I'm happy and
>>impressed with SQLite, but I recently noticed some odd behaviour with
>>one of my queries.
>>Using the command-line in a shell (Mac High Sierra) I get a particular
>>result from a query. The exact same query in a Perl script gives me a
>>different result. To my mind it is a simple query, getting the 5 latest
>>additions to my music library.
>>Command-line:
>>select artists.artist, artists.artistid, cds.title, cds.artistid,
>>cds.cdid, genres.genre, genres.artistid from artists, genres inner join
>>cds using (artistid) group by artists.artistid order by cds.id desc
>>limit 5;
>>gives me the correct result. However, in a Perl script it gives me a
>>different result. How is that possible? Could it be a Perl::DBI issue?
>>Many thanks for anyone able to shed some light on this.
>
>Your select does not constrain artists so the result is non-deterministic
>in that the result will depend on how the query planner decides to
>execute the query.  That is, you have not specified any join constraints
>on artists.
>
>SELECT * FROM A, B JOIN C USING (D);
>
>means
>
>SELECT *
>  FROM A, B, C
> WHERE B.D == C.D;
>
>if you thought it meant
>
>SELECT *
>  FROM A, B, C
> WHERE A.D == B.D
>   AND B.D == C.D;
>
>then that is likely the reason for the discrepancy.
>
>
>
>
>___
>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] SQLite command-line result is different from Perl DBI::Sqlite result

[Keith Medcalf]

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

On Saturday, 4 January, 2020 18:31, Amer Neely  wrote:

>I'm fairly new to SQLite, but have been using MySQL / mariadb in a local
>and web-based environment for several years. So far I'm happy and
>impressed with SQLite, but I recently noticed some odd behaviour with
>one of my queries.
>Using the command-line in a shell (Mac High Sierra) I get a particular
>result from a query. The exact same query in a Perl script gives me a
>different result. To my mind it is a simple query, getting the 5 latest
>additions to my music library.
>Command-line:
>select artists.artist, artists.artistid, cds.title, cds.artistid,
>cds.cdid, genres.genre, genres.artistid from artists, genres inner join
>cds using (artistid) group by artists.artistid order by cds.id desc
>limit 5;
>gives me the correct result. However, in a Perl script it gives me a
>different result. How is that possible? Could it be a Perl::DBI issue?
>Many thanks for anyone able to shed some light on this.

Your select does not constrain artists so the result is non-deterministic in 
that the result will depend on how the query planner decides to execute the 
query.  That is, you have not specified any join constraints on artists.

SELECT * FROM A, B JOIN C USING (D);

means

SELECT * 
  FROM A, B, C
 WHERE B.D == C.D;

if you thought it meant

SELECT *
  FROM A, B, C
 WHERE A.D == B.D
   AND B.D == C.D;

then that is likely the reason for the discrepancy.




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

Re: [sqlite] SQLite command-line result is different from Perl DBI::Sqlite result

[Igor Korot]

Hi,

On Sat, Jan 4, 2020 at 7:31 PM Amer Neely  wrote:
>
> Hello all,
> I'm fairly new to SQLite, but have been using MySQL / mariadb in a local
> and web-based environment for several years. So far I'm happy and
> impressed with SQLite, but I recently noticed some odd behaviour with
> one of my queries.
> Using the command-line in a shell (Mac High Sierra) I get a particular
> result from a query. The exact same query in a Perl script gives me a
> different result. To my mind it is a simple query, getting the 5 latest
> additions to my music library.
> Command-line:
> select artists.artist, artists.artistid, cds.title, cds.artistid,
> cds.cdid, genres.genre, genres.artistid from artists, genres inner join
> cds using (artistid) group by artists.artistid order by cds.id desc
> limit 5;
> gives me the correct result. However, in a Perl script it gives me a
> different result. How is that possible? Could it be a Perl::DBI issue?
> Many thanks for anyone able to shed some light on this.

What is your version of Perl and the SQLite module?

Thank you.

> --
> Amer Neely
> ___
> 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] SQLite command-line result is different from Perl DBI::Sqlite result

[Amer Neely]

Hello all,
I'm fairly new to SQLite, but have been using MySQL / mariadb in a local
and web-based environment for several years. So far I'm happy and
impressed with SQLite, but I recently noticed some odd behaviour with
one of my queries.
Using the command-line in a shell (Mac High Sierra) I get a particular
result from a query. The exact same query in a Perl script gives me a
different result. To my mind it is a simple query, getting the 5 latest
additions to my music library.
Command-line:
select artists.artist, artists.artistid, cds.title, cds.artistid,
cds.cdid, genres.genre, genres.artistid from artists, genres inner join
cds using (artistid) group by artists.artistid order by cds.id desc
limit 5;
gives me the correct result. However, in a Perl script it gives me a
different result. How is that possible? Could it be a Perl::DBI issue?
Many thanks for anyone able to shed some light on this.
-- 
Amer Neely
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite DELUXE Re: sqlite command line tool NUL support

[Luke Amery]

https://www.sqlite.org/privatebranch.html

This command both creates the new repository and populates it with all the
latest SQLite could
->
This command both creates the new repository and populates it with all the
latest SQLite code

On Wed, Jan 24, 2018 at 10:44 PM Richard Hipp  wrote:

> On 1/24/18, petern  wrote:
> > Have you worked out an automated way for your changes to shadow and
> > auto-merge from the official trunk?
>
> https://www.sqlite.org/privatebranch.html
>
> --
> 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] SQLite DELUXE Re: sqlite command line tool NUL support

[Richard Hipp]

On 1/24/18, petern  wrote:
> Have you worked out an automated way for your changes to shadow and
> auto-merge from the official trunk?

https://www.sqlite.org/privatebranch.html

-- 
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] SQLite DELUXE Re: sqlite command line tool NUL support

[J Decker]

On Wed, Jan 24, 2018 at 1:30 PM, petern  wrote:

> Have you worked out an automated way for your changes to shadow and
> auto-merge from the official trunk?  That is, aside from collision edits,
> is it automated?  Longer term, your shadow distribution also needs
> new/merged test cases and a regression test run to re-qualify the merged
> changes at each cycle.
>
>
(yes, git pretty much manages a 'if merged with master compatibility')
Sometimes there may be modifications in master that affect the patch (so
far there may have been one or two)

(against my own fossil->github echo which can get me fossil head without
delay)
https://github.com/d3x0r/sqlite3/compare/escape-inline-nul-strings

then I port to this... making sure tests pass at least as well as expected
(usually)
(against mackyle repo which lags the official repository a little)
https://github.com/mackyle/sqlite/compare/master...d3x0r:escape-shell-inline-nuls

(fossil head composite)
https://github.com/d3x0r/sqlite3/compare/MySqlite
(against mackle; which would work great for a base of an official switch
(if never))
https://github.com/mackyle/sqlite/compare/master...d3x0r:MySqlite


> It seems to me there are many small usability changes which could be
> incorporated into a more comprehensive SQLite shadow release,  call it
> "SQLite DELUXE".  For example, here is another straightforward and useful
> change by Cezary that was never adopted despite having zero impact by
> conditional compilation switch:  http://sqlite.chncc.eu/
>
> The argument against improving generality of the official release because
> it slows down some phones will never go away.  Your changes may have to
> shadow the official release forever.
>
> Peter
>
> On Wed, Jan 24, 2018 at 11:46 AM, J Decker  wrote:
>
> > This is a picture.  This is a tortoise git log view of merges.
> >
> > https://drive.google.com/open?id=1RosGlprJRoutFsou2XDRlflxc8xWoHks
> >
> > On Wed, Jan 24, 2018 at 8:59 AM, J Decker  wrote:
> >
> > >
> > > So for output of a select in the shell ( unlike .dump ) is this.
> > >
> > > for(i=1; i > >   utf8_printf(p->out, ",%s", sqlite3_column_text(pSelect, i));
> > > }
> > >
> > > option 1) add text conversion in the column_text to escape NUL
> > characters.
> > > in TEXT... then everyone everywhere would inherit the change.
> > > 'asdf' is as valid as ''||'a'||'s'||'d'||'f'||''
> > >
> > > ( the postgres/sqlite way is to use  "\'||CHAR(0)||\'" (as a C string)
> if
> > > '\0' is required outside of a bind )
> > >
> > > 2) output literal characters regardless of terminal...
> > > //utf8_printf(p->out, "%s", z);
> > > raw_fwrite( z, 1, sqlite3_column_bytes(pSelect, 0), p->out);
> > >  for(i=1; i > >   //utf8_printf(p->out, ",%s", sqlite3_column_text(pSelect, i));
> > >   raw_fputc(p->out, ',');
> > >   raw_fwrite( sqlite3_column_text(pSelect, i), 1,
> > >   sqlite3_column_bytes(pSelect, 0), p->out);
> > > }
> > >
> > > 3)
> > >
> > > .dump uses
> > > output_quoted_string
> > > output_quoted_escaped_string
> > > (the latter of which will wrap "text\r\n" with
> > > replace(replace("text\\r\\n" ),'\\r',char(13)),'\\n',char(10))
> > > so it could additionally wrap that with
> > >
> > > the latter of which will wrap "u\0tf\r\n" with
> > > replace(replace(replace("u\\0tf\\r\\n" ),'\\r',char(13)),'\\n',char(1
> > > 0)),'\\0',char(0))
> > >
> > > instead of
> > > replace(replace("u'||CHAR(0)||'tf\\r\\n" ),'\\r',char(13)),'\\n',char(
> > 10))
> > >
> > >
> > >
> > >
> > > --
> > > * Change select * output to write full length text value
> > > * Change .dump output to include supporting char(0) substitution ( !
> > Shell
> > > Newlines )
> > > * Change other .dump output to inline subst '||CHAR(0)||'  (
> > > ShellHasFlag(p, SHFLG_Newlines) )
> > >
> > > https://drive.google.com/open?id=1UXaFBRoNypZBhkpfPh7TAwncJMbBT1cH
> > >
> > >
> > > Sample output (shell without newlines)
> > > "d\n and" has a char \x14
> > >
> > > (shell only; doesn't change test1.c)
> > >
> > > SQLite version 3.23.0 2018-01-24 01:58:49
> > > Enter ".help" for usage hints.
> > > sqlite> .dump test
> > > PRAGMA foreign_keys=OFF;
> > > BEGIN TRANSACTION;
> > > CREATE TABLE test (a);
> > > INSERT INTO test VALUES(0);
> > > INSERT INTO test VALUES(replace('TEST\0NUL\0','\0',char(0)));
> > > INSERT INTO test VALUES(replace('TEST\0\0NUL\0','\0',char(0)));
> > > INSERT INTO test VALUES(0);
> > > INSERT INTO test VALUES(0);
> > > INSERT INTO test VALUES(replace(replace(replace('char test\rand\n\0
> and
> > > all\0','\r',char(13)),'\n',char(10)),'\0',char(0)));
> > > INSERT INTO test VALUES(replace(replace('char test\rand\n and
> > > all(null)','\r',char(13)),'\n',char(10)));
> > > INSERT INTO test VALUES(replace(replace('char test\rand\n and
> > > all(null)','\r',char(13)),'\n',char(10)));
> > > INSERT INTO test VALUES(replace(replace(replace('char test\rand\n\0
> 

[sqlite] SQLite DELUXE Re: sqlite command line tool NUL support

[petern]

Have you worked out an automated way for your changes to shadow and
auto-merge from the official trunk?  That is, aside from collision edits,
is it automated?  Longer term, your shadow distribution also needs
new/merged test cases and a regression test run to re-qualify the merged
changes at each cycle.

It seems to me there are many small usability changes which could be
incorporated into a more comprehensive SQLite shadow release,  call it
"SQLite DELUXE".  For example, here is another straightforward and useful
change by Cezary that was never adopted despite having zero impact by
conditional compilation switch:  http://sqlite.chncc.eu/

The argument against improving generality of the official release because
it slows down some phones will never go away.  Your changes may have to
shadow the official release forever.

Peter

On Wed, Jan 24, 2018 at 11:46 AM, J Decker  wrote:

> This is a picture.  This is a tortoise git log view of merges.
>
> https://drive.google.com/open?id=1RosGlprJRoutFsou2XDRlflxc8xWoHks
>
> On Wed, Jan 24, 2018 at 8:59 AM, J Decker  wrote:
>
> >
> > So for output of a select in the shell ( unlike .dump ) is this.
> >
> > for(i=1; i >   utf8_printf(p->out, ",%s", sqlite3_column_text(pSelect, i));
> > }
> >
> > option 1) add text conversion in the column_text to escape NUL
> characters.
> > in TEXT... then everyone everywhere would inherit the change.
> > 'asdf' is as valid as ''||'a'||'s'||'d'||'f'||''
> >
> > ( the postgres/sqlite way is to use  "\'||CHAR(0)||\'" (as a C string) if
> > '\0' is required outside of a bind )
> >
> > 2) output literal characters regardless of terminal...
> > //utf8_printf(p->out, "%s", z);
> > raw_fwrite( z, 1, sqlite3_column_bytes(pSelect, 0), p->out);
> >  for(i=1; i >   //utf8_printf(p->out, ",%s", sqlite3_column_text(pSelect, i));
> >   raw_fputc(p->out, ',');
> >   raw_fwrite( sqlite3_column_text(pSelect, i), 1,
> >   sqlite3_column_bytes(pSelect, 0), p->out);
> > }
> >
> > 3)
> >
> > .dump uses
> > output_quoted_string
> > output_quoted_escaped_string
> > (the latter of which will wrap "text\r\n" with
> > replace(replace("text\\r\\n" ),'\\r',char(13)),'\\n',char(10))
> > so it could additionally wrap that with
> >
> > the latter of which will wrap "u\0tf\r\n" with
> > replace(replace(replace("u\\0tf\\r\\n" ),'\\r',char(13)),'\\n',char(1
> > 0)),'\\0',char(0))
> >
> > instead of
> > replace(replace("u'||CHAR(0)||'tf\\r\\n" ),'\\r',char(13)),'\\n',char(
> 10))
> >
> >
> >
> >
> > --
> > * Change select * output to write full length text value
> > * Change .dump output to include supporting char(0) substitution ( !
> Shell
> > Newlines )
> > * Change other .dump output to inline subst '||CHAR(0)||'  (
> > ShellHasFlag(p, SHFLG_Newlines) )
> >
> > https://drive.google.com/open?id=1UXaFBRoNypZBhkpfPh7TAwncJMbBT1cH
> >
> >
> > Sample output (shell without newlines)
> > "d\n and" has a char \x14
> >
> > (shell only; doesn't change test1.c)
> >
> > SQLite version 3.23.0 2018-01-24 01:58:49
> > Enter ".help" for usage hints.
> > sqlite> .dump test
> > PRAGMA foreign_keys=OFF;
> > BEGIN TRANSACTION;
> > CREATE TABLE test (a);
> > INSERT INTO test VALUES(0);
> > INSERT INTO test VALUES(replace('TEST\0NUL\0','\0',char(0)));
> > INSERT INTO test VALUES(replace('TEST\0\0NUL\0','\0',char(0)));
> > INSERT INTO test VALUES(0);
> > INSERT INTO test VALUES(0);
> > INSERT INTO test VALUES(replace(replace(replace('char test\rand\n\0 and
> > all\0','\r',char(13)),'\n',char(10)),'\0',char(0)));
> > INSERT INTO test VALUES(replace(replace('char test\rand\n and
> > all(null)','\r',char(13)),'\n',char(10)));
> > INSERT INTO test VALUES(replace(replace('char test\rand\n and
> > all(null)','\r',char(13)),'\n',char(10)));
> > INSERT INTO test VALUES(replace(replace(replace('char test\rand\n\0 and
> > all 23\0','\r',char(13)),'\n',char(10)),'\0',char(0)));
> > INSERT INTO test VALUES(replace(replace(replace('char test\rand\n\0 and
> > all merged content\0','\r',char(13)),'\n',char(10)),'\0',char(0)));
> > COMMIT;
> > sqlite> select * from test
> >...> ;
> > 0
> > TEST NUL
> > TEST  NUL
> > 0
> > 0
> > andr test
> >   and all
> > andr test
> > and all
> > andr test
> > and all
> > andr test
> >   and all 23
> > andr test
> >   and all merged content
> > sqlite>
> >
> ___
> 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] sqlite command line tool NUL support

[J Decker]

This is a picture.  This is a tortoise git log view of merges.

https://drive.google.com/open?id=1RosGlprJRoutFsou2XDRlflxc8xWoHks

On Wed, Jan 24, 2018 at 8:59 AM, J Decker  wrote:

>
> So for output of a select in the shell ( unlike .dump ) is this.
>
> for(i=1; i   utf8_printf(p->out, ",%s", sqlite3_column_text(pSelect, i));
> }
>
> option 1) add text conversion in the column_text to escape NUL characters.
> in TEXT... then everyone everywhere would inherit the change.
> 'asdf' is as valid as ''||'a'||'s'||'d'||'f'||''
>
> ( the postgres/sqlite way is to use  "\'||CHAR(0)||\'" (as a C string) if
> '\0' is required outside of a bind )
>
> 2) output literal characters regardless of terminal...
> //utf8_printf(p->out, "%s", z);
> raw_fwrite( z, 1, sqlite3_column_bytes(pSelect, 0), p->out);
>  for(i=1; i   //utf8_printf(p->out, ",%s", sqlite3_column_text(pSelect, i));
>   raw_fputc(p->out, ',');
>   raw_fwrite( sqlite3_column_text(pSelect, i), 1,
>   sqlite3_column_bytes(pSelect, 0), p->out);
> }
>
> 3)
>
> .dump uses
> output_quoted_string
> output_quoted_escaped_string
> (the latter of which will wrap "text\r\n" with
> replace(replace("text\\r\\n" ),'\\r',char(13)),'\\n',char(10))
> so it could additionally wrap that with
>
> the latter of which will wrap "u\0tf\r\n" with
> replace(replace(replace("u\\0tf\\r\\n" ),'\\r',char(13)),'\\n',char(1
> 0)),'\\0',char(0))
>
> instead of
> replace(replace("u'||CHAR(0)||'tf\\r\\n" ),'\\r',char(13)),'\\n',char(10))
>
>
>
>
> --
> * Change select * output to write full length text value
> * Change .dump output to include supporting char(0) substitution ( ! Shell
> Newlines )
> * Change other .dump output to inline subst '||CHAR(0)||'  (
> ShellHasFlag(p, SHFLG_Newlines) )
>
> https://drive.google.com/open?id=1UXaFBRoNypZBhkpfPh7TAwncJMbBT1cH
>
>
> Sample output (shell without newlines)
> "d\n and" has a char \x14
>
> (shell only; doesn't change test1.c)
>
> SQLite version 3.23.0 2018-01-24 01:58:49
> Enter ".help" for usage hints.
> sqlite> .dump test
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE test (a);
> INSERT INTO test VALUES(0);
> INSERT INTO test VALUES(replace('TEST\0NUL\0','\0',char(0)));
> INSERT INTO test VALUES(replace('TEST\0\0NUL\0','\0',char(0)));
> INSERT INTO test VALUES(0);
> INSERT INTO test VALUES(0);
> INSERT INTO test VALUES(replace(replace(replace('char test\rand\n\0 and
> all\0','\r',char(13)),'\n',char(10)),'\0',char(0)));
> INSERT INTO test VALUES(replace(replace('char test\rand\n and
> all(null)','\r',char(13)),'\n',char(10)));
> INSERT INTO test VALUES(replace(replace('char test\rand\n and
> all(null)','\r',char(13)),'\n',char(10)));
> INSERT INTO test VALUES(replace(replace(replace('char test\rand\n\0 and
> all 23\0','\r',char(13)),'\n',char(10)),'\0',char(0)));
> INSERT INTO test VALUES(replace(replace(replace('char test\rand\n\0 and
> all merged content\0','\r',char(13)),'\n',char(10)),'\0',char(0)));
> COMMIT;
> sqlite> select * from test
>...> ;
> 0
> TEST NUL
> TEST  NUL
> 0
> 0
> andr test
>   and all
> andr test
> and all
> andr test
> and all
> andr test
>   and all 23
> andr test
>   and all merged content
> sqlite>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite command line tool NUL support

[J Decker]

So for output of a select in the shell ( unlike .dump ) is this.

for(i=1; iout, ",%s", sqlite3_column_text(pSelect, i));
}

option 1) add text conversion in the column_text to escape NUL characters.
in TEXT... then everyone everywhere would inherit the change.
'asdf' is as valid as ''||'a'||'s'||'d'||'f'||''

( the postgres/sqlite way is to use  "\'||CHAR(0)||\'" (as a C string) if
'\0' is required outside of a bind )

2) output literal characters regardless of terminal...
//utf8_printf(p->out, "%s", z);
raw_fwrite( z, 1, sqlite3_column_bytes(pSelect, 0), p->out);
 for(i=1; iout, ",%s", sqlite3_column_text(pSelect, i));
  raw_fputc(p->out, ',');
  raw_fwrite( sqlite3_column_text(pSelect, i), 1,
  sqlite3_column_bytes(pSelect, 0), p->out);
}

3)

.dump uses
output_quoted_string
output_quoted_escaped_string
(the latter of which will wrap "text\r\n" with
replace(replace("text\\r\\n" ),'\\r',char(13)),'\\n',char(10))
so it could additionally wrap that with

the latter of which will wrap "u\0tf\r\n" with
replace(replace(replace("u\\0tf\\r\\n" ),'\\r',char(13)),'\\n',char(
10)),'\\0',char(0))

instead of
replace(replace("u'||CHAR(0)||'tf\\r\\n" ),'\\r',char(13)),'\\n',char(10))




--
* Change select * output to write full length text value
* Change .dump output to include supporting char(0) substitution ( ! Shell
Newlines )
* Change other .dump output to inline subst '||CHAR(0)||'  (
ShellHasFlag(p, SHFLG_Newlines) )

https://drive.google.com/open?id=1UXaFBRoNypZBhkpfPh7TAwncJMbBT1cH


Sample output (shell without newlines)
"d\n and" has a char \x14

(shell only; doesn't change test1.c)

SQLite version 3.23.0 2018-01-24 01:58:49
Enter ".help" for usage hints.
sqlite> .dump test
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test (a);
INSERT INTO test VALUES(0);
INSERT INTO test VALUES(replace('TEST\0NUL\0','\0',char(0)));
INSERT INTO test VALUES(replace('TEST\0\0NUL\0','\0',char(0)));
INSERT INTO test VALUES(0);
INSERT INTO test VALUES(0);
INSERT INTO test VALUES(replace(replace(replace('char test\rand\n\0 and
all\0','\r',char(13)),'\n',char(10)),'\0',char(0)));
INSERT INTO test VALUES(replace(replace('char test\rand\n and
all(null)','\r',char(13)),'\n',char(10)));
INSERT INTO test VALUES(replace(replace('char test\rand\n and
all(null)','\r',char(13)),'\n',char(10)));
INSERT INTO test VALUES(replace(replace(replace('char test\rand\n\0 and all
23\0','\r',char(13)),'\n',char(10)),'\0',char(0)));
INSERT INTO test VALUES(replace(replace(replace('char test\rand\n\0 and all
merged content\0','\r',char(13)),'\n',char(10)),'\0',char(0)));
COMMIT;
sqlite> select * from test
   ...> ;
0
TEST NUL
TEST  NUL
0
0
andr test
  and all
andr test
and all
andr test
and all
andr test
  and all 23
andr test
  and all merged content
sqlite>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite command line tool fails to dump data

[Simon Slavin]

On 22 Jan 2018, at 4:46pm, J Decker  wrote:

> create table [with\0nul] ( `col\0``umn` )

Could you not ?  In fact, could everybody not ?

[Goes to look for ice bag or strong alcohol, whichever appears first.]

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

Re: [sqlite] sqlite command line tool fails to dump data

[J Decker]

On Sun, Jan 21, 2018 at 11:48 PM, Clemens Ladisch 
wrote:

> J Decker wrote:
> >> *If any NUL characters occur at byte| offsets less than the value of the
> >> fourth parameter then the resulting| string value will contain embedded
> >> NULs.*
> >
> > So it's best used as data, and not keys
> > and I see unless custom aggregate()s or function()s ...
>
> If you want embedded NULs, use blobs.
>
> But it's not a blob, it's text that I'm saving.


> > insert into ? (?,?) values(?,?)
> > with bind ( 'ta\0le', '\0','\1', 'hello\0','\0world' )
> >
> > bad things happen :)  but what if I ?
>
> In this case, the bad thing that happens is a syntax error; you cannot
> use parameters for table/column names.
>
> And SQL statements cannot contain embedded NULs; parsing stops at the
> detected end of the string.
>
> by SQL you mean  PSSQL and Sqlite

MySQL https://dev.mysql.com/doc/refman/5.7/en/string-literals.html
TSQL
https://docs.microsoft.com/en-us/sql/t-sql/functions/string-escape-transact-sql
Oracle can use Chr(0); but its tools can often be misleading

And sqlite internally has no problems storing and retrieving the data
faithfully; it's just the command line tool (sqlite3) and TCL tests that
have issues.



I can do this to insert NUL character...

sqlite test.db
create table test (a)
insert into test (a) values ( "test"||char(0)||"one" )
.dump test

So I CAN escape NUL chars in sqlite by replacing them with '||char(0)||'


From the standard.
The stand makes no mention of NUL or \0 either to allow or disallow, so
it's undefined but it's not that 'cannot contain NUL'

create table [with\0nul] ( `col\0``umn` )

is easily parsable, and all that has to be done is keep the tokens as a
whole (string,length) and not fall back to strlen, and trust the original
character count given to prepare.


http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

spaces are used to separate syntactic elements. Multiple spaces and
 line breaks are treated as a single space. Apart from those symbols
 to which special functions were given above, other characters and
 character strings in a formula stand for themselves. In addition,
 if the symbols to the right of the definition operator in a produc-
 tion consist entirely of BNF symbols, then those symbols stand for
 themselves and do not take on their special meaning.


For every portion of the string enclosed in square brackets,
either delete the brackets and their contents or change the
brackets to braces.




(from SQL 92) By this, I shouldn't also be able to use ~, `, Γειά σου Κόσμ,
Привет мир, or any other UNICODE character. (that is if you say things not
listed are " cannot contain embedded ;"

5.1  

 Define the terminal symbols of the SQL language and the elements of
 strings.

 Format

  ::=

  | 

  ::=

  | 

  ::=

  | 
  | 

  ::=

  | 

  ::=
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O
  | P | Q | R | S | T | U | V | W | X | Y | Z

  ::=
a | b | c | d | e | f | g | h | i | j | k | l | m | n | o
  | p | q | r | s | t | u | v | w | x | y | z

  ::=
  0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9

  ::=

  | 
  | 
  | 
  | 
  | 
  | 
  | 
  | 
  | 
  | 
  | 
  | 
  | 
  | 
  | 
  | 
  | 
  | 
  | 
  | 

  ::= !! space character in character set in use
  ::= "
  ::= %
  ::= &
  ::= '
  ::= (
  ::= )
  ::= *
  ::= +
  ::= ,
  ::= -
  ::= .
  ::= /
  ::= :
  ::= ;
  ::= <
  ::= =
  ::= >
  ::= ?
  ::= [
  ::= ]
  ::= _
  ::= |

 General Rules

 1) There is a one-to-one correspondence between the symbols con-
tained in  and the symbols
contained in  such that, for
all i, the symbol defined as the i-th alternative for  corresponds to the symbol defined as
the i-th alternative for .


> 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] sqlite command line tool fails to dump data

[Clemens Ladisch]

J Decker wrote:
>> *If any NUL characters occur at byte| offsets less than the value of the
>> fourth parameter then the resulting| string value will contain embedded
>> NULs.*
>
> So it's best used as data, and not keys
> and I see unless custom aggregate()s or function()s ...

If you want embedded NULs, use blobs.

> insert into ? (?,?) values(?,?)
> with bind ( 'ta\0le', '\0','\1', 'hello\0','\0world' )
>
> bad things happen :)  but what if I ?

In this case, the bad thing that happens is a syntax error; you cannot
use parameters for table/column names.

And SQL statements cannot contain embedded NULs; parsing stops at the
detected end of the string.


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

Re: [sqlite] sqlite command line tool fails to dump data

[J Decker]

On Sun, Jan 21, 2018 at 7:42 AM, Clemens Ladisch  wrote:

> J Decker wrote:
> > insert into test (a,b) values ( ?,? )
> >   bind 'hello\0world.' 'te\0st'
>
>  says:
> | If a non-negative fourth parameter is provided to sqlite3_bind_text()
> | or sqlite3_bind_text16() or sqlite3_bind_text64() then that parameter
> | must be the byte offset where the NUL terminator would occur assuming
> | the string were NUL terminated.
>
> *If any NUL characters occur at byte| offsets less than the value of the
> fourth parameter then the resulting| string value will contain embedded
> NULs.*


So it's best used as data, and not keys
and I see unless custom aggregate()s or function()s ...


> The result of expressions
> | involving strings with embedded NULs is undefined.
>
>
A JSON dump which would be an addition to not break other compatibility
could store the correct value
the .dump works from a sqlite_value which has text() and bytes() which
would encode in json with '\0' since it needs \, 0, " (n, f, t, b, u (for
very high characters) ) etc all escaped...
then it's also reading from a different command pipe (importing a file)


As a side question

insert into ? (?,?) values(?,?)
with bind ( 'ta\0le', '\0','\1', 'hello\0','\0world' )

bad things happen :)  but what if I ?


> 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] sqlite command line tool fails to dump data

[Clemens Ladisch]

J Decker wrote:
> insert into test (a,b) values ( ?,? )
>   bind 'hello\0world.' 'te\0st'

 says:
| If a non-negative fourth parameter is provided to sqlite3_bind_text()
| or sqlite3_bind_text16() or sqlite3_bind_text64() then that parameter
| must be the byte offset where the NUL terminator would occur assuming
| the string were NUL terminated. If any NUL characters occur at byte
| offsets less than the value of the fourth parameter then the resulting
| string value will contain embedded NULs. The result of expressions
| involving strings with embedded NULs is undefined.


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

Re: [sqlite] sqlite command line tool fails to dump data

[Luuk]

On 21-01-18 16:16, J Decker wrote:
> create table test (a,b)
> insert into test (a,b) values ( ?,? )
>   bind 'hello\0world.' 'te\0st'

luuk@opensuse:~/tmp> sqlite3
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table test(a,b);
sqlite> insert into test values ('hello\0world','te\0st');
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test(a,b);
INSERT INTO "test" VALUES('hello\0world','te\0st');
COMMIT;
sqlite>


who told you that 'bind' works on the shell??
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite command line tool fails to dump data

[J Decker]

sqlite test.db
create table test (a,b)
insert into test (a,b) values ( ?,? )
  bind 'hello\0world.' 'te\0st'

.dump table

-- output

CREATE TABLE test (a,b);
INSERT INTO test VALUES('hello','te');
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] command shell .timeout

[Roman Fleysher]

Thank you, Richard.

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Richard Hipp [d...@sqlite.org]
Sent: Thursday, August 03, 2017 3:38 PM
To: SQLite mailing list
Subject: Re: [sqlite] command shell .timeout

On 8/3/17, Roman Fleysher <roman.fleys...@einstein.yu.edu> wrote:
> Dear SQLiters,
>
> I am using sqlit3 command shell. It has ".timeout" command. What is the
> difference between:
>
> .timeout MS
> PRAGMA busy_timeout = milliseconds;

They accomplish the same thing.  The ".timeout" command (which I had
forgotten about) existed long before the "PRAGMA busy_timeout" command
was created.  Both commands invoke sqlite3_busy_timeout() for you.
--
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] command shell .timeout

[Richard Hipp]

On 8/3/17, Roman Fleysher  wrote:
> Dear SQLiters,
>
> I am using sqlit3 command shell. It has ".timeout" command. What is the
> difference between:
>
> .timeout MS
> PRAGMA busy_timeout = milliseconds;

They accomplish the same thing.  The ".timeout" command (which I had
forgotten about) existed long before the "PRAGMA busy_timeout" command
was created.  Both commands invoke sqlite3_busy_timeout() for you.
-- 
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] command shell .timeout

[Roman Fleysher]

Dear SQLiters,

I am using sqlit3 command shell. It has ".timeout" command. What is the 
difference between:

.timeout MS
PRAGMA busy_timeout = milliseconds;

I am getting "database is locked" when accessing the same file from multiple 
concurrent shells and trying to set timeouts to avoid this.

(By the way, PRAGMA busy_timeout = milliseconds; prints new timeout to screen. 
I would expect printing only if new value is not provided.)

Thank you

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

Re: [sqlite] (dot) commands will execute from sqlite command line but not from query

[Richard Hipp]

On 12/31/16, Paul Lambert  wrote:
>
> Issue 2.  No matter how I mix the use of .system in a trigger I cannot get
> the syntax checker to allow for  (dot) functions .system or .shell to
> work.  I know there is a qualifier about where the (dot) functions are
> parsed but I have successfully used other (dot) functions in a trigger.
> Triggering external applications on database events is a critical function.

The dot-commands are implemented by the command-line shell program.
But triggers are run by the core SQLite library.  So there is no way
for a trigger to run a dot-command.

-- 
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] (dot) commands will execute from sqlite command line but not from query

[Simon Slavin]

On 31 Dec 2016, at 7:55pm, Paul Lambert  wrote:

> Issue 2.  No matter how I mix the use of .system in a trigger I cannot get
> the syntax checker to allow for  (dot) functions .system or .shell to
> work.

Dot functions are not part of SQLite.  They are handled directly by the 
command-line tool.  The SQLite API does not understand them.  Sorry.

If you need to call a system command directly from SQLite you will need to 
implement your own external function.

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

[sqlite] (dot) commands will execute from sqlite command line but not from query

[Paul Lambert]

On Linux with sqlite 3.13.0  installedI have successfully invoked the (dot)
commands .system and .shell.

Below are the results.  In both instances the application at the target
location launched as expected.  (Ignore the Gtk message as it relates to
the modal dialog box employed in the the application)

sqlite> .system /home/EB30750/Documents/Provox/deltvImport

Gtk-Message: GtkDialog mapped without a transient parent. This is
discouraged.
System command returns 65280
sqlite>

sqlite> .shell /home/EB30750/Documents/Provox/deltvImport

Gtk-Message: GtkDialog mapped without a transient parent. This is
discouraged.
System command returns 65280
sqlite>


Issue 1:  Both of these commands wait for the external program to exist
before returning.  Typically, where there are two versions, one will return
without waiting for the child process to terminate and the other one will
wait.  I see these two functions as the same as the .system function can
invoke the shell this way .system /usr/sh echo 'hello world'  Or .system C:\
*Windows*\System32\cmd type 'hello world'  Might want to consider making
one of these return with no wait.


Issue 2.  No matter how I mix the use of .system in a trigger I cannot get
the syntax checker to allow for  (dot) functions .system or .shell to
work.  I know there is a qualifier about where the (dot) functions are
parsed but I have successfully used other (dot) functions in a trigger.
Triggering external applications on database events is a critical function.


sqlite> CREATE TRIGGER testor
   ...> BEFORE INSERT ON moz_cookies
   ...> BEGIN
   ...> DELETE FROM moz_cookies WHERE name = 'mimeMapApp';
   ...> .system /home/EB30750/Documents/Provox/deltvImport;
   ...> END;
Error: near ".": syntax error
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Command line not accepting multi-line statements.

[John G]

Thanks, that worked.

John Gillespie

On 8 November 2016 at 14:30, Richard Hipp  wrote:

> On 11/8/16, John G  wrote:
> > I normally use the 3.8.8.3 supplied with MacOS El Capitan.
> > I downloaded version 3.15.1 from the Download page - precompiled
> > command-line tools : (sqlite-tools-osx-x86-3150100.zip).
> >
> > When I tried copying and pasting multiple or multi-line statements from
> my
> > editor (jEdit) the command-line shell ignored everything after the first
> > line. This is not the case with 3.8.8.3.
> >
>
> This appears to be a bug in the "linenoise" library that we link
> against when building the precompiled shell - it has nothing to do
> with SQLite.  See the https://github.com/antirez/linenoise/issues/75
> bug report.  We first started linking precompiled SQLite binaries
> against linenoise with 3.8.9 (2015-04-08) and you are the first person
> to notice the difference.
>
> We'll see if we can't update the linenoise implementations on our
> build machines and upload new binaries for 3.15.1
>
> In the meantime, you can always build SQLite yourself from sources.
> On a mac, just type "./configure; make" (after installing Xcode, which
> is free on the apple store).
>
> --
> 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] Command line not accepting multi-line statements.

[Dominique Devienne]

On Tue, Nov 8, 2016 at 3:30 PM, Richard Hipp  wrote:

> On 11/8/16, John G  wrote:
> > I normally use the 3.8.8.3 supplied with MacOS El Capitan.
> > I downloaded version 3.15.1 from the Download page - precompiled
> > command-line tools : (sqlite-tools-osx-x86-3150100.zip).
> >
> > When I tried copying and pasting multiple or multi-line statements from
> my
> > editor (jEdit) the command-line shell ignored everything after the first
> > line. This is not the case with 3.8.8.3.
> >
>
> This appears to be a bug in the "linenoise" library that we link
> against when building the precompiled shell - it has nothing to do
> with SQLite.  See the https://github.com/antirez/linenoise/issues/75
> bug report.  We first started linking precompiled SQLite binaries
> against linenoise with 3.8.9 (2015-04-08) and you are the first person
> to notice the difference.
>
> We'll see if we can't update the linenoise implementations on our
> build machines and upload new binaries for 3.15.1
>
> In the meantime, you can always build SQLite yourself from sources.
> On a mac, just type "./configure; make" (after installing Xcode, which
> is free on the apple store).
>

FWIW, I'm using linenoise-ng in my own command line app (see below),
wrote a couple commands in VS, each on its own line, and pasted them to
my linenoise-ng based app running on Linux in a Putty window, and
both lines where pasted (and executed) OK.

Unlike the original, the -ng variant works OK on Windows,
but is C++11 based. But it doesn't seem to be bug free either,
got a crash using its linenoiseHistorySetMaxLen().

Supports prompt coloring using ANSI escape codes too,
which work in both the DOS prompt, Putty, and RedHat terminal.

Since I consider this a bit "experimental", I have an SQLite-inspired
.linenoise on/off "dot" command, to switch to/from basic cout/getstring :)

// From https://github.com/arangodb/linenoise-ng
// Commit df1cfb41e3de9d2e716016d0571338ceed62290f
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Command line not accepting multi-line statements.

[Richard Hipp]

On 11/8/16, John G  wrote:
> I normally use the 3.8.8.3 supplied with MacOS El Capitan.
> I downloaded version 3.15.1 from the Download page - precompiled
> command-line tools : (sqlite-tools-osx-x86-3150100.zip).
>
> When I tried copying and pasting multiple or multi-line statements from my
> editor (jEdit) the command-line shell ignored everything after the first
> line. This is not the case with 3.8.8.3.
>

This appears to be a bug in the "linenoise" library that we link
against when building the precompiled shell - it has nothing to do
with SQLite.  See the https://github.com/antirez/linenoise/issues/75
bug report.  We first started linking precompiled SQLite binaries
against linenoise with 3.8.9 (2015-04-08) and you are the first person
to notice the difference.

We'll see if we can't update the linenoise implementations on our
build machines and upload new binaries for 3.15.1

In the meantime, you can always build SQLite yourself from sources.
On a mac, just type "./configure; make" (after installing Xcode, which
is free on the apple store).

-- 
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] Command line not accepting multi-line statements.

[John G]

I normally use the 3.8.8.3 supplied with MacOS El Capitan.
I downloaded version 3.15.1 from the Download page - precompiled
command-line tools : (sqlite-tools-osx-x86-3150100.zip).

When I tried copying and pasting multiple or multi-line statements from my
editor (jEdit) the command-line shell ignored everything after the first
line. This is not the case with 3.8.8.3.

If I put ALL the statements on a single long  line they are accepted.

Is this a bug? Am doing something wrong? Tongue in wrong position?

Thanks for any help.

John Gillespie


Text in my editor:

create table people (pid integer primary key,name text);
insert into people values(1, 'John Smith');
insert into people values(2, 'Alan Smith');
insert into people values(3, 'Elsie Jones');
select * from people
 where name like '%Smith';
    I copied this block of text and pasted to the
command-line shells  ...


Using 3.8.8.3 from bash :

sqlite 528 % sqlite3
SQLite version 3.8.8.3 2015-02-25 13:29:11
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table people (pid integer primary key,name text);
sqlite> insert into people values(1, 'John Smith');
sqlite> insert into people values(2, 'Alan Smith');
sqlite> insert into people values(3, 'Elsie Jones');
sqlite> select * from people
   ...>  where name like '%Smith';
1|John Smith
2|Alan Smith
sqlite>
    as expected

Using 3.15.1 from bash :

sqlite 527 % ~/bin/sqlite3_15
SQLite version 3.15.1 2016-11-04 12:08:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table people (pid integer primary key,name text);
sqlite>
 - input truncated at first line
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Regarding file extension for sqlite command prompt

[R.A. Nagy]

Use .read to load & execute .sql files.

The .open is for databases, not external / textual command files.

Here are some introductory videos:

https://www.youtube.com/playlist?list=PLItP5KoawLqkPV2jqAVCH79fZGO5k0Uzy



On Fri, Jun 24, 2016 at 6:07 AM, Kaja Varunkumar 
wrote:

> Hello,
>
>  I am new to SQLite, I have seen the  commands for command prompt of
> SQLite. I have some query files with format .sql. Whenever I try to open
> the files using command .open and read the files . I am getting an error of
> file encrypted and not in the database.   My question's, are there any
> format to open files in SQLite? After creating the tables in one session
> that are not shown in another session.  How to avoid this case ?
>
>
> Thanks,
>  K Varun Kumar
> ___
> 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] Regarding file extension for sqlite command prompt

[Kaja Varunkumar]

Hello,

 I am new to SQLite, I have seen the  commands for command prompt of
SQLite. I have some query files with format .sql. Whenever I try to open
the files using command .open and read the files . I am getting an error of
file encrypted and not in the database.   My question's, are there any
format to open files in SQLite? After creating the tables in one session
that are not shown in another session.  How to avoid this case ?


Thanks,
 K Varun Kumar
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Command line sqlite3 program bug

[Domingo Alvarez Duarte]

Hello !  

Every day is a day to learn something new !  

It also have the command line -bail for my example "sqlite3 -bail a.db <
the.sql".  

Interesting that how easy I can miss the basics "when everything else fail,
read the manual !".  

Thank you !  

Cheers !  
>  Tue Sep 08 2015 9:40:37 pm CEST CEST from "Gerry Snyder"
>  Subject: Re: [sqlite] Command line sqlite3 
>program
>bug
>
>  The Command Line Interface has the command:
> 
> ..bail on
> 
> which will do what you want.
> 
> HTH,
> 
> Gerry Snyder
> ---
> On 9/8/2015 9:54 AM, Domingo Alvarez Duarte wrote:
>  
>>Hello !
>> 
>> After seem several emails from a user asking about how to use sqlite3
>>through
>> shell scripts, I remember my experiences with sqlite3 but didn't mind to
>> report it, but now I think that it's worth mention it because it'll hurt
>> several users.
>> 
>> The bug/problem is that the sqlite3 command line when feed with a sql
>>script
>> with commands wrapped by a transaction if there is any error in the middle
>>of
>> it sqlite3 reports the error but do not stop/abort the transaction and the
>> database end up in a dirty state.
>> 
>> __example to show the bug/problem
>> 
>> BEGIN;
>> 
>> DROP TABLE IF EXISTS a; --to allow run more than once
>> 
>> CREATE TABLE a(b); --after the next line error this should be rolled back
>> 
>> INSERT INTO a(c) VALUES(3); -- intentional error and no stop/rollback
>> 
>> INSERT INTO a(b) values(4);
>> 
>> COMMIT;
>> 
>> __
>> 
>> __blank database after been feed by the above sql script
>> 
>> PRAGMA foreign_keys=OFF;
>> BEGIN TRANSACTION;
>> CREATE TABLE a(b);
>> INSERT INTO "a" VALUES(4);
>> COMMIT;
>> 
>> __
>> 
>> Cheers !
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> 
>> 

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



?

[sqlite] Command line sqlite3 program bug

[Domingo Alvarez Duarte]

Hello !  

After seem several emails from a user asking about how to use sqlite3 through
shell scripts, I remember my experiences with sqlite3 but didn't mind to
report it, but now I think that it's worth mention it because it'll hurt
several users.  

The bug/problem is that the sqlite3 command line when feed with a sql script
with commands wrapped by a transaction if there is any error in the middle of
it sqlite3 reports the error but do not stop/abort the transaction and the
database end up in a dirty state.  

__example to show the bug/problem  

BEGIN;  

DROP TABLE IF EXISTS a; --to allow run more than once  

CREATE TABLE a(b); --after the next line error this should be rolled back  

INSERT INTO a(c) VALUES(3); -- intentional error and no stop/rollback  

INSERT INTO a(b) values(4);  

COMMIT;  

__  

__blank database after been feed by the above sql script  

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE a(b);
INSERT INTO "a" VALUES(4);
COMMIT;  

__  

Cheers !

[sqlite] Command line sqlite3 program bug

[Gerry Snyder]

The Command Line Interface has the command:

.bail on

which will do what you want.

HTH,

Gerry Snyder
---
On 9/8/2015 9:54 AM, Domingo Alvarez Duarte wrote:
> Hello !
>
> After seem several emails from a user asking about how to use sqlite3 through
> shell scripts, I remember my experiences with sqlite3 but didn't mind to
> report it, but now I think that it's worth mention it because it'll hurt
> several users.
>
> The bug/problem is that the sqlite3 command line when feed with a sql script
> with commands wrapped by a transaction if there is any error in the middle of
> it sqlite3 reports the error but do not stop/abort the transaction and the
> database end up in a dirty state.
>
> __example to show the bug/problem
>
> BEGIN;
>
> DROP TABLE IF EXISTS a; --to allow run more than once
>
> CREATE TABLE a(b); --after the next line error this should be rolled back
>
> INSERT INTO a(c) VALUES(3); -- intentional error and no stop/rollback
>
> INSERT INTO a(b) values(4);
>
> COMMIT;
>
> __
>
> __blank database after been feed by the above sql script
>
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE a(b);
> INSERT INTO "a" VALUES(4);
> COMMIT;
>
> __
>
> Cheers !
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] How initialize a database file from unix shell without landing in SQLite command prompt?

[Mikael]

I have a file DBDEFINITION.TXT that I want to use to initialize [execute
on] the database DB.SQLITE.

How do I do something like

$ sqlite3 -exec DBDEFINITION.TXT DB.SQLITE
$


?

All methods I tried end me up in the SQLite prompt, and that is what I
don't want. Like, doing sqlite3 -init DBDEFINITION.TXT DB.SQLITE and having
".quit" as last line in the definition file.

Thanks.

[sqlite] How initialize a database file from unix shell without landing in SQLite command prompt?

[R.Smith]

On 2015-03-14 10:51 AM, Mikael wrote:
> I have a file DBDEFINITION.TXT that I want to use to initialize [execute
> on] the database DB.SQLITE.
>
> How do I do something like
>
> $ sqlite3 -exec DBDEFINITION.TXT DB.SQLITE
> $

A piped file is always the answer - for anything really. If a piped file 
is not specified then the console becomes the standard input to the 
program and you end up in the console input cycle (which is what happens 
for you).

# sqlite3 mydb.db < somefile.sql

- will execute with mydb.db all the SQL statements (if no errors occur) 
inside somefile.sql - Note that this doesn't wrap the file in an 
implicit transaction, if the file fails halfway through and you did not 
start an explicit transaction, it won't be rolled back.

Good luck!
Ryan

[sqlite] How initialize a database file from unix shell without landing in SQLite command prompt?

[Neville Dastur]

Just pipe the text file to sqlite3
# sqlite3 DB.SQLITE :
>I have a file DBDEFINITION.TXT that I want to use to initialize [execute
>on] the database DB.SQLITE.
>How do I do something like
>$ sqlite3 -exec DBDEFINITION.TXT DB.SQLITE
>$
>?
>All methods I tried end me up in the SQLite prompt, and that is what I
>don't want. Like, doing sqlite3 -init DBDEFINITION.TXT DB.SQLITE and having
>".quit" as last line in the definition file.
>Thanks.
>___
>sqlite-users mailing list
>sqlite-users at mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Command line shell not flushing stderr when interactive

[Peter Aronson]

Microsoft seems to only make the stderr stream unbuffered when writing to a 
character device: "The stdout and stderr functions are flushed whenever they 
are full or, if you are writing to a character device, after each library 
call."  It doesn't seem to consider pipe that emacs is reading from a character 
device.  This seems to violate the ISO C standard, which I believe requires 
stderr to be unbuffered or line-buffered at start-up, but I doubt that 
Microsoft is losing any sleep over that.
 
My office mate, who also uses Emacs on Windows, modified our local copy of 
shell.c to deal with this.  First, he made a similar addition of a fflush that 
you did (our changes are bracketed by ifdef ESRI):
 
static int process_input(struct callback_data *p, FILE *in){
  char *zLine = 0;
  char *zSql = 0;
  int nSql = 0;
  int nSqlPrior = 0;
  char *zErrMsg;
  int rc;
  int errCnt = 0;
  int lineno = 0;
  int startline = 0;
  while( errCnt==0 || !bail_on_error || (in==0 && stdin_is_interactive) ){
#ifdef ESRI
    fflush(stderr);
#endif
    fflush(p->out);
    free(zLine);
 
And made this addition to main to get interactive behavior when running is an 
Emacs *shell* window:
 
int main(int argc, char **argv){
  char *zErrMsg = 0;
  struct callback_data data;
  const char *zInitFile = 0;
  char *zFirstCmd = 0;
  int i;
  int rc = 0;
  if( strcmp(sqlite3_sourceid(),SQLITE_SOURCE_ID)!=0 ){
    fprintf(stderr, "SQLite header and source version mismatch\n%s\n%s\n",
    sqlite3_sourceid(), SQLITE_SOURCE_ID);
    exit(1);
  }
  Argv0 = argv[0];
  main_init();
  stdin_is_interactive = isatty(0);
#ifdef ESRI
  if (!stdin_is_interactive) {
    /* If Emacs shell window's TERM is set to "emacs". 
    ** Then set interactive mode on to redirect STDIN to Emacs shell window.
    */
    char *env_var = getenv("TERM");
    if (env_var) {
  if (!strcmp (env_var, "emacs"))
    stdin_is_interactive = 1;
    }
  }
#endif

Peter

From: Christopher Wellons <well...@nullprogram.com>
>To: sqlite-users@sqlite.org 
>Sent: Sunday, January 19, 2014 9:10 AM
>Subject: [sqlite] Command line shell not flushing stderr when interactive
>
>
>
>When the shell is set to interactive (i.e. "-interactive"), the output
>(stdout) is flushed with every prompt (shell.c:422) but stderr is not.
>In some situations this leads to no error messages being displayed until
>the stderr buffer fills.
>
>This happens when running the official sqlite3 binary as subprocess of
>Emacs under Windows 7. The error messages do not appear in a timely
>fashion. I was unable to trigger the misbehavior in a plain shell so my
>only demo is a bit of Emacs Lisp. When this Elisp code below is run, a
>buffer will pop up that *should* contain the output of .help. Under
>Windows it does not. The same occurs even when it's launched via a shell
>subprocess using "2>&1", so it's not simply an issue with Emacs not
>reading from the subprocess's stderr output fast enough.
>
>    (let* ((buffer (generate-new-buffer "sqlite"))
>          (proc (start-process "sqlite" buffer "sqlite3" "-interactive")))
>      (process-send-string proc ".help\n")
>      (pop-to-buffer buffer))
>
>I suspect it has to do with being compiled without readline, which is
>why it behaves better elsewhere. I couldn't figure out how to link with
>libreadline on Windows, though, so I couldn't test this.
>
>With the following change to the amalgamation release I got the behavior
>I was looking for: timely error messages from the SQLite command line
>shell. I understand this is probably not the Right Way to do this, but
>it's just a demonstation of a possible fix.
>
>--- a/shell.c
>+++ b/shell.c
>@@ -418,6 +418,7 @@ static char *one_input_line(FILE *in, char *zPrior, int 
>isCont
>    zResult = readline(zPrompt);
>    if( zResult && *zResult ) add_history(zResult);
>#else
>+    fflush(stderr);
>    printf("%s", zPrompt);
>    fflush(stdout);
>    zResult = local_getline(zPrior, stdin);
>___
>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] Command line shell not flushing stderr when interactive

[Simon Slavin]

On 19 Jan 2014, at 7:32pm, Luuk  wrote:

> It is acceptable—and normal—for standard output and standard error to be 
> directed to the same destination, such as the text terminal. Messages appear 
> in the same order as the program writes them, unless buffering is involved. 
> (For example, a common situation is when the standard error stream is 
> unbuffered but the standard output stream is line-buffered; in this case, 
> text written to standard error later may appear on the terminal earlier, if 
> the standard output stream's buffer is not yet full.)
> 
> source:
> http://en.wikipedia.org/wiki/Standard_streams#Standard_error_.28stderr.29

Buffering matters only if an app is going to use stderr as a warning stream 
instead of its original purpose of "I'm about to crash and here's why.".

When stderr was thought up, a program wrote some text to it just before it 
quit.  The question of buffering wasn't important because any buffer would be 
flushed an instant later when the program that wrote it quit.  So it didn't 
matter whether stderr was buffered or not.

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

Re: [sqlite] Command line shell not flushing stderr when interactive

[Luuk]

On 19-01-2014 19:59, Christopher Wellons wrote:



When the shell is set to interactive (i.e. "-interactive"), the output
(stdout) is flushed with every prompt (shell.c:422) but stderr is not.



Stderr is suppose to be unbuffered so that flushing is not required.  Or is
that different for windows?


According to the stderr Linux man page stderr is unbuffered, which would
be why I'm not having a problem in Linux:


The stream stderr is unbuffered.  The  stream  stdout  is  line-buffered
when  it  points  to  a  terminal.


I'm unable to find any documentation about this for Windows, but since
I'm seeing stderr buffering it must not be unbuffered in Windows.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



Is this not biting you?


It is acceptable—and normal—for standard output and standard error to be 
directed to the same destination, such as the text terminal. Messages 
appear in the same order as the program writes them, unless buffering is 
involved. (For example, a common situation is when the standard error 
stream is unbuffered but the standard output stream is line-buffered; in 
this case, text written to standard error later may appear on the 
terminal earlier, if the standard output stream's buffer is not yet full.)


source:
http://en.wikipedia.org/wiki/Standard_streams#Standard_error_.28stderr.29

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

Re: [sqlite] Command line shell not flushing stderr when interactive

[Christopher Wellons]

>> When the shell is set to interactive (i.e. "-interactive"), the output
>> (stdout) is flushed with every prompt (shell.c:422) but stderr is not.

> Stderr is suppose to be unbuffered so that flushing is not required.  Or is
> that different for windows?

According to the stderr Linux man page stderr is unbuffered, which would
be why I'm not having a problem in Linux:

> The stream stderr is unbuffered.  The  stream  stdout  is  line-buffered
> when  it  points  to  a  terminal.

I'm unable to find any documentation about this for Windows, but since
I'm seeing stderr buffering it must not be unbuffered in Windows.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Command line shell not flushing stderr when interactive

[Richard Hipp]

On Sun, Jan 19, 2014 at 11:10 AM, Christopher Wellons <
well...@nullprogram.com> wrote:

>
> When the shell is set to interactive (i.e. "-interactive"), the output
> (stdout) is flushed with every prompt (shell.c:422) but stderr is not.
>

Stderr is suppose to be unbuffered so that flushing is not required.  Or is
that different for windows?



> In some situations this leads to no error messages being displayed until
> the stderr buffer fills.
>
> This happens when running the official sqlite3 binary as subprocess of
> Emacs under Windows 7. The error messages do not appear in a timely
> fashion. I was unable to trigger the misbehavior in a plain shell so my
> only demo is a bit of Emacs Lisp. When this Elisp code below is run, a
> buffer will pop up that *should* contain the output of .help. Under
> Windows it does not. The same occurs even when it's launched via a shell
> subprocess using "2>&1", so it's not simply an issue with Emacs not
> reading from the subprocess's stderr output fast enough.
>
> (let* ((buffer (generate-new-buffer "sqlite"))
>(proc (start-process "sqlite" buffer "sqlite3" "-interactive")))
>   (process-send-string proc ".help\n")
>   (pop-to-buffer buffer))
>
> I suspect it has to do with being compiled without readline, which is
> why it behaves better elsewhere. I couldn't figure out how to link with
> libreadline on Windows, though, so I couldn't test this.
>
> With the following change to the amalgamation release I got the behavior
> I was looking for: timely error messages from the SQLite command line
> shell. I understand this is probably not the Right Way to do this, but
> it's just a demonstation of a possible fix.
>
> --- a/shell.c
> +++ b/shell.c
> @@ -418,6 +418,7 @@ static char *one_input_line(FILE *in, char *zPrior,
> int isCont
>  zResult = readline(zPrompt);
>  if( zResult && *zResult ) add_history(zResult);
>  #else
> +fflush(stderr);
>  printf("%s", zPrompt);
>  fflush(stdout);
>  zResult = local_getline(zPrior, stdin);
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

[sqlite] Command line shell not flushing stderr when interactive

[Christopher Wellons]

When the shell is set to interactive (i.e. "-interactive"), the output
(stdout) is flushed with every prompt (shell.c:422) but stderr is not.
In some situations this leads to no error messages being displayed until
the stderr buffer fills.

This happens when running the official sqlite3 binary as subprocess of
Emacs under Windows 7. The error messages do not appear in a timely
fashion. I was unable to trigger the misbehavior in a plain shell so my
only demo is a bit of Emacs Lisp. When this Elisp code below is run, a
buffer will pop up that *should* contain the output of .help. Under
Windows it does not. The same occurs even when it's launched via a shell
subprocess using "2>&1", so it's not simply an issue with Emacs not
reading from the subprocess's stderr output fast enough.

(let* ((buffer (generate-new-buffer "sqlite"))
   (proc (start-process "sqlite" buffer "sqlite3" "-interactive")))
  (process-send-string proc ".help\n")
  (pop-to-buffer buffer))

I suspect it has to do with being compiled without readline, which is
why it behaves better elsewhere. I couldn't figure out how to link with
libreadline on Windows, though, so I couldn't test this.

With the following change to the amalgamation release I got the behavior
I was looking for: timely error messages from the SQLite command line
shell. I understand this is probably not the Right Way to do this, but
it's just a demonstation of a possible fix.

--- a/shell.c
+++ b/shell.c
@@ -418,6 +418,7 @@ static char *one_input_line(FILE *in, char *zPrior, int 
isCont
 zResult = readline(zPrompt);
 if( zResult && *zResult ) add_history(zResult);
 #else
+fflush(stderr);
 printf("%s", zPrompt);
 fflush(stdout);
 zResult = local_getline(zPrior, stdin);
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Command-line utility

[RSmith]

http://www.sqlite.org/download.html

scroll to "Precompiled Binaries for Windows"
It runs just fine on 32 bit windows.

Adam


Hi, thanks, and yes I have these, but am specifically interested in the latest development trunk, which I don't think is included on 
this page in compiled form (unless I'm mistaken in which case please point me in the right direction).






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

Re: [sqlite] Command-line utility

[Adam DeVita]

http://www.sqlite.org/download.html

scroll to "Precompiled Binaries for Windows"
It runs just fine on 32 bit windows.

Adam

On Thu, Jul 11, 2013 at 12:20 PM, RSmith  wrote:
> Could someone send me a build with the current trunk of the command-line
> utility for Windows 32Bit with the standard option set for testing purposes
> please, or point me to where I can download it if a standard build already
> exists.
> Thanks!
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Command-line utility

[RSmith]

Could someone send me a build with the current trunk of the command-line utility for Windows 32Bit with the standard option set for 
testing purposes please, or point me to where I can download it if a standard build already exists.

Thanks!



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

Re: [sqlite] Command history not working in sqlite3 tool for MAC OS

[Neo Anderson]

Yes, I installed the latest version myself.

> Date: Fri, 11 May 2012 07:46:16 +0100
> From: amit.k.chaudh...@gmail.com
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Command history not working in sqlite3 tool for MAC OS
> 
> My Mac is running system provided sqlite3 (3.6.12) and up arrow returns
> last command as expected.  Did you install 3.7.11 your self, if so I wonder
> if you missed a component (e.g. one which provides the cmd line
> functionality).
> 
> On Fri, May 11, 2012 at 6:33 AM, Neo Anderson <neo_in_mat...@msn.com> wrote:
> 
> >
> > The UP/DOWN arrow keys do not work in sqlite3 for MAC OS X.
> > For example, I start sqlite3 and hit UP key, then I get:
> >
> > $ sqlite3 Documents/test.db
> > SQLite version 3.7.11 2012-03-20 11:35:50
> > Enter ".help" for instructions
> > Enter SQL statements terminated with a ";"
> > sqlite> ^[[A
> >
> > Is this a known problem?
> >
> > ___
> > 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] command line to get number of tables in sqlite

[Mohit Sindhwani]

On 22/12/2011 7:25 AM, Matt Young wrote:

select count() from sqlite_master;

No??

...where type='table';




Cheers,
Mohit.
22/12/2011 | 12:51 PM.

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

Re: [sqlite] command line to get number of tables in sqlite

[Matt Young]

select count() from sqlite_master;

No??

On Wed, Dec 21, 2011 at 10:32 AM, smallboat  wrote:

> Hello,
>
> I have a sqlite file. I would like to open it and know how many tables in
> it.
>
> What is the command line to open a sqlite file and get to know how many
> tables in it? Thanks.
>
> Regards,
> Joe
>
> ___
> 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] command line to get number of tables in sqlite

[Roger Andersson]

Somthing like
sqlite3  sqlite.file
sqlite> select count(*) from sqlite_master where type = 'table';

/Roger
On 12/21/11 19:32, smallboat wrote:

Hello,

I have a sqlite file. I would like to open it and know how many tables in it.

What is the command line to open a sqlite file and get to know how many tables 
in it? Thanks.



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

Re: [sqlite] command line to get number of tables in sqlite

[Tim Streater]

On 21 Dec 2011 at 18:32, smallboat  wrote: 

> I have a sqlite file. I would like to open it and know how many tables in it.
>
> What is the command line to open a sqlite file and get to know how many tables
> in it? Thanks.

See:

http://www.sqlite.org/sqlite.html

and:

http://www.sqlite.org/faq.html#q7

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

[sqlite] command line to get number of tables in sqlite

[smallboat]

Hello, 

I have a sqlite file. I would like to open it and know how many tables in it. 

What is the command line to open a sqlite file and get to know how many tables 
in it? Thanks.

Regards,
Joe

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

Re: [sqlite] Command line option equivalent for .read

[David Walker]

Thanks Simon that does it

It does strike me though that there would be some value in making the in app 
'.' (dot) commands and the command line options consistent especially as there 
has been an increase in the number of both as new versions have come about. 
From a programming perspective I suspect it would also be more maintainable as 
well

So you get:
 .header [ON|OFF] and -[no]header
But you only get
.echo [ON|OFF] and -echo so there is no -noecho option

There is a .read but no -read

There is .mode csv and a -csv but  wouldn't it be better to have a -mode csv 
(and by implication a -mode tcl where there is not -tcl)

Don't get me wrong this is not a major whinge - I love sqlite and appreciate 
the effort of the developers put into maintaining it but as it grows having a 
consistent single command set for .commands and options would be a good thing

David M Walker
Data Management & Warehousing
0118 321 5930
dav...@datamgmt.com
http://www.datamgmt.com

> Date: Thu, 8 Dec 2011 17:26:55 +
> From: Simon Davies <simon.james.dav...@gmail.com>
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Subject: Re: [sqlite] Command line option equivalent for .read
> Message-ID:
>   <CANG6AhQjNs08OM93iaE0xNONbTRPJsRRzhKkSsdy+N3b=fp...@mail.gmail.com>
> Content-Type: text/plain; charset=ISO-8859-1
> 
> On 8 December 2011 17:05, David Walker <dav...@datamgmt.com> wrote:
>> Hi,
>> 
>> Having played around with a shell script that calls SQLite I have noticed 
>> that I can something like
>> 
>> ? ? ? ?.separator STRING ? ? ?at the SQLite prompt
>> ? ? ? ?.mode line
>> 
>> or I can do
>> 
>> ? ? ? ?sqlite3 -separator STRING at the command line
>> ? ? ? ?sqlite3 - line
>> 
>> This is both useful and consistent
>> However if I want to execute a file I can only do
>> 
>> ? ? ? ?.read FILENAME ? ? at the SQLite prompt
>> 
>> or I can do either of
>> 
>> ? ? ? ?cat FILENAME | sqlite3
>> or
>> ? ? ? ?sqlite3 > 
>> Is there an equivalent '-read' for the command line and if not might there 
>> be in the future?
>> The '-init' option does not seem to perform the same function although it 
>> does read the file
> 
> Does
>sqlite3 theDb ".read FILENAME"
> do what you want?
> 
>> 
>> It is inconsistent and when writing wrapper shell/perl scripts on occasions 
>> makes scripts a little more complex
>> e.g. try doing it with a Perl IO::CaptureOutput(qxx) call where you don't 
>> have access to piped stdin :-(
>> 
>> rgds
>> davidw
>> 
> 
> Regards,
> Simon

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

Re: [sqlite] Command line option equivalent for .read

[Simon Davies]

On 8 December 2011 17:05, David Walker  wrote:
> Hi,
>
> Having played around with a shell script that calls SQLite I have noticed 
> that I can something like
>
>        .separator STRING      at the SQLite prompt
>        .mode line
>
> or I can do
>
>        sqlite3 -separator STRING at the command line
>        sqlite3 - line
>
> This is both useful and consistent
> However if I want to execute a file I can only do
>
>        .read FILENAME     at the SQLite prompt
>
> or I can do either of
>
>        cat FILENAME | sqlite3
> or
>        sqlite3 
> Is there an equivalent '-read' for the command line and if not might there be 
> in the future?
> The '-init' option does not seem to perform the same function although it 
> does read the file

Does
sqlite3 theDb ".read FILENAME"
do what you want?

>
> It is inconsistent and when writing wrapper shell/perl scripts on occasions 
> makes scripts a little more complex
> e.g. try doing it with a Perl IO::CaptureOutput(qxx) call where you don't 
> have access to piped stdin :-(
>
> rgds
> davidw
>

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

[sqlite] Command line option equivalent for .read

[David Walker]

Hi,

Having played around with a shell script that calls SQLite I have noticed that 
I can something like

.separator STRING  at the SQLite prompt
.mode line

or I can do 

sqlite3 -separator STRING at the command line
sqlite3 - line

This is both useful and consistent
However if I want to execute a file I can only do

.read FILENAME at the SQLite prompt

or I can do either of

cat FILENAME | sqlite3
or
sqlite3 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite command shell - read/process file then exit?

[Kees Nuyt]

On Mon, 21 Nov 2011 14:01:47 +, Simon Davies
 wrote:

>On 21 November 2011 13:41, SupportLists  wrote:
>> Hi,
>>
>> I am trying to use sqlite3 command shell with -init filename.
>>
>> The file has:
>>
>> CREATE TABLE griddata (id_suit VARCHAR(14) PRIMARY KEY,xpos DECIMAL(7),ypos
>> DECIMAL(7),cellvalue DECIMAL(14,5) DEFAULT -);
>> .separator ","
>> .import /home/cquiros/temp/layers/layers/New/lgp001.csv griddata
>> ALTER TABLE griddata ADD COLUMN colour char(9);
>> ALTER TABLE griddata ADD COLUMN classCode integer;
>> .quit
>>
>> All the commands works except .quit  . How can I execute the shell with a
>> -init and then exit when it finished processing the file?
>
>sqlite3 -init filename tst.db ".quit"
>
>or
>
>sqlite3 tst.db ".read filename"

or 

sqlite3 tst.db http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite command shell - read/process file then exit?

[Simon Davies]

On 21 November 2011 13:41, SupportLists  wrote:
> Hi,
>
> I am trying to use sqlite3 command shell with -init filename.
>
> The file has:
>
> CREATE TABLE griddata (id_suit VARCHAR(14) PRIMARY KEY,xpos DECIMAL(7),ypos
> DECIMAL(7),cellvalue DECIMAL(14,5) DEFAULT -);
> .separator ","
> .import /home/cquiros/temp/layers/layers/New/lgp001.csv griddata
> ALTER TABLE griddata ADD COLUMN colour char(9);
> ALTER TABLE griddata ADD COLUMN classCode integer;
> .quit
>
> All the commands works except .quit  . How can I execute the shell with a
> -init and then exit when it finished processing the file?

sqlite3 -init filename tst.db ".quit"

or

sqlite3 tst.db ".read filename"

>
> Many thanks,
> Carlos.

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

[sqlite] sqlite command shell - read/process file then exit?

[SupportLists]

Hi,

I am trying to use sqlite3 command shell with -init filename.

The file has:

CREATE TABLE griddata (id_suit VARCHAR(14) PRIMARY KEY,xpos 
DECIMAL(7),ypos DECIMAL(7),cellvalue DECIMAL(14,5) DEFAULT -);

.separator ","
.import /home/cquiros/temp/layers/layers/New/lgp001.csv griddata
ALTER TABLE griddata ADD COLUMN colour char(9);
ALTER TABLE griddata ADD COLUMN classCode integer;
.quit

All the commands works except .quit  . How can I execute the shell with 
a -init and then exit when it finished processing the file?


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

Re: [sqlite] Creating a database with a script or SQLite command

[Ivan Shmakov]

> Pete Helgren writes:

 > I may end up going this direction, at the moment I am not having much
 > luck with the conditional copy in Busybox.  Your suggestion:

 > cp -n newdatabase.db /data/newdatabase.db

 > Isn't supported in the version of Busybox that I am running.  Also
 > the script example I tried:

 > if  [ -f /data/newdatabase.db];
 > then
 > echo "Nothing to do, database exists"
 > else
 > cp newdatabase.db /data/newdatabase.db
 > fi

 > delivers the error  [:missing]

The primary token delimiter in POSIX Shell is space.  Hence, the
following line:

   if  [ -f /data/newdatabase.db];

Is understood as: “check if the file ‘/data/newdatabase.db]’
exists” (note the closing bracket), and it certainly lacks a
closing bracket for the ‘test’ (AKA ‘[’) command.

The solution would be as follows:

 - if  [ -f /data/newdatabase.db];
 + if  [ -f /data/newdatabase.db ];

OTOH, the when the ‘test’ form of the command is used, closing
bracket is not necessary, thus:

   if  test -f /data/newdatabase.db ;

Please also consider joining the news:comp.unix.shell newsgroup
(e. g., on Aioe, nntp://aioe.org/comp.unix.shell/), as there're
quite a few folks familiar with the arcane art of Unix Shell
programming.  (AFAIK, Thunderbird has the support for the
Internet News service.)

[…]

-- 
FSF associate member #7257  Coming soon: Software Freedom Day
http://mail.sf-day.org/lists/listinfo/ planning-ru (ru), sfd-discuss (en)

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

Re: [sqlite] Creating a database with a script or SQLite command

[Simon Slavin]

On 31 Aug 2011, at 3:53am, Pete Helgren wrote:

> I may end up going this direction, at the moment I am not having much luck 
> with the conditional copy in Busybox.  Your suggestion:
> 
> cp -n newdatabase.db /data/newdatabase.db
> 
> Isn't supported in the version of Busybox that I am running. 

Oh, you're running BusyBox, not a standard shell.  okay, well here's a doc for 
BusyBox:



see whether cp will overwrite without any options set.  Try it out.  See what 
happens.

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

Re: [sqlite] Creating a database with a script or SQLite command

[Jay A. Kreibich]

On Tue, Aug 30, 2011 at 08:29:06PM -0600, Pete Helgren scratched on the wall:
> The only issue I had was finding an example of how I could do all of
> what you describe below in bash script.  For example, if I put this
> in a script:
> 
> sqlite3 newdatabase.db
> 
> and save that as createdb.sh and execute it then the script never
> completes because SQLite is at the sqlite> prompt, waiting for
> commands.  Hence that option is a non-starter.

  You need to give sqlite3 a command, or it will go into interactive
  mode.  That's how the shell is designed to work.
  
  You can do this, however:

$ sqlite3 newdatabase.db .exit

  The existence of the command will cause sqlite3 to execute the
  command and quit, without going into interactive mode.  As I 
  explained before, this specific example won't actually create a
  database file, however.

  I suppose you could do something this:

sqlite3 newdatabase.db "CREATE TABLE IF NOT EXISTS ..."
sqlite3 newdatabase.db "CREATE TABLE IF NOT EXISTS ..."
...

  But that seems a bit wasteful.  If you want to do all your
  initialization in one pass, I would do something like this:

sqlite3 newdatabase.db << EOF
CREATE TABLE IF NOT EXISTS t1 ( a, b, c );
CREATE TABLE IF NOT EXISTS t2 ( d, e, f );
EOF

  -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Creating a database with a script or SQLite command

[Pete Helgren]

I may end up going this direction, at the moment I am not having much 
luck with the conditional copy in Busybox.  Your suggestion:


cp -n newdatabase.db /data/newdatabase.db

Isn't supported in the version of Busybox that I am running.  Also the 
script example I tried:


if  [ -f /data/newdatabase.db];
then
echo "Nothing to do, database exists"
else
cp newdatabase.db /data/newdatabase.db
fi

delivers the error  [:missing]

So I'll have to work through the scripting.  Sure would be nice to have 
something like "sqlite3 newdatabase.db .exit" work so that it would just 
create the DB and exit


Pete Helgren
Value Added Software, Inc
www.asaap.com
www.opensource4i.com


On 8/30/2011 8:14 PM, Simon Slavin wrote:

Forgot to mention: copying an existing database file also lets you set up the 
file the way you want without having to issue separate commands.  For instance, 
you could create blank tables.  Or set a specific page size.  Or include some 
sort of DRM or security check in the 'blank' file.

Simon.
___
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] Creating a database with a script or SQLite command

[Pete Helgren]

The only issue I had was finding an example of how I could do all of 
what you describe below in bash script.  For example, if I put this in a 
script:


sqlite3 newdatabase.db

and save that as createdb.sh and execute it then the script never 
completes because SQLite is at the sqlite> prompt, waiting for 
commands.  Hence that option is a non-starter.


Pete Helgren
Value Added Software, Inc
www.asaap.com
www.opensource4i.com


On 8/30/2011 8:23 PM, Jay A. Kreibich wrote:

   Of course, I'm not sure what the big deal is.  By default, if you
   attempt to open an SQLite database file that does not exist, the
   system will just go ahead and create it.  This sounds like exactly
   the desired behavior.  There is no need to pre-create the file.

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

Re: [sqlite] Creating a database with a script or SQLite command

[Jay A. Kreibich]

On Tue, Aug 30, 2011 at 09:54:21PM -0400, Igor Tandetnik scratched on the wall:
> Pete Helgren  wrote:
> > I have a need to create a database if it doesn't already exist.  The
> > obvious solution is to just use:
> > 
> > sqlite3 newdatabase.db
> > 
> > Except that it not only creates the db but also opens that db for
> > commands.  I am running this from a script so I want to just want to run
> > the command from a script so that I know the database exists before
> > issuing other commands.
> 
> Try something like
> 
> echo ".exit" | sqlite3 newdatabase.db

  Except that won't work**, since creating the database file is a lazy
  operation.  There are several ways to force the creation of a
  zero-byte file (open/commit a transaction, for example), but that can
  be done with something as simple as "touch(1)".
 
  Creating the file and writing the full database header (making it a
  recognizable SQLite file) requires putting something into the
  sqlite3_master table (e.g. creating a user-defined table).  This
  could be done with any CREATE TABLE IF NOT EXISTS... statement.


  Of course, I'm not sure what the big deal is.  By default, if you
  attempt to open an SQLite database file that does not exist, the
  system will just go ahead and create it.  This sounds like exactly
  the desired behavior.  There is no need to pre-create the file.
  
  Assuming the start-up process continues with a series of CREATE
  TABLE IF NOT EXISTS... statements, a new database will have the file
  created and defined, while an existing database will create/ignore
  the tables depending on the existing structure.



  ** Who are you, and what did you do with Igor?

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Creating a database with a script or SQLite command

[Simon Slavin]

Forgot to mention: copying an existing database file also lets you set up the 
file the way you want without having to issue separate commands.  For instance, 
you could create blank tables.  Or set a specific page size.  Or include some 
sort of DRM or security check in the 'blank' file.

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

Re: [sqlite] Creating a database with a script or SQLite command

[Simon Slavin]

On 31 Aug 2011, at 2:53am, Pete Helgren wrote:

> Thanks.  I'll add a little more info
> 
> This script is used to set up the initial DB in a programmable device that 
> will then record data to the database and the database should never be 
> replaced.  So I just figured there would be a simple way to issue the sqlite 
> commands in script.

The mechanism in SQLite which creates a database is to open one that doesn't 
exist.  There's no command or C function which just makes a database without 
opening it.  You could, of course, hack that functionality out of the source 
code but I think that's a poor solution.

> Even found an example using a createdb command, although I could never see 
> where that was an SQLite command
> 
> So, you suggest I script it like so:
> 
> if [ -f /data/newdatabase.db];
> then
> echo "Nothing to do, database exists"
> else
> cp newdatabase.db /data/newdatabase.db
> fi
> 
> I am not much of a Linux guy so the scripting might be wrong.

That would do fine.  But as a single-command alternative you could use 'cp -n':

cp -n newdatabase.db /data/newdatabase.db

the '-n' means 'don't replace an existing file'.  I tested it on my Unix box.  
I believe it's implemented in Linux, but you should definitely test it because 
I don't have Linux here.

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

Re: [sqlite] Creating a database with a script or SQLite command

[Igor Tandetnik]

Pete Helgren  wrote:
> I have a need to create a database if it doesn't already exist.  The
> obvious solution is to just use:
> 
> sqlite3 newdatabase.db
> 
> Except that it not only creates the db but also opens that db for
> commands.  I am running this from a script so I want to just want to run
> the command from a script so that I know the database exists before
> issuing other commands.

Try something like

echo ".exit" | sqlite3 newdatabase.db

-- 
Igor Tandetnik

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

Re: [sqlite] Creating a database with a script or SQLite command

[Pete Helgren]

Thanks.  I'll add a little more info

This script is used to set up the initial DB in a programmable device 
that will then record data to the database and the database should never 
be replaced.  So I just figured there would be a simple way to issue the 
sqlite commands in script.  Even found an example using a createdb 
command, although I could never see where that was an SQLite command


So, you suggest I script it like so:

if [ -f /data/newdatabase.db];
then
echo "Nothing to do, database exists"
else
cp newdatabase.db /data/newdatabase.db
fi

I am not much of a Linux guy so the scripting might be wrong.

Pete Helgren
Value Added Software, Inc
www.asaap.com
www.opensource4i.com


On 8/30/2011 7:38 PM, Simon Slavin wrote:

On 31 Aug 2011, at 2:36am, Pete Helgren wrote:


I have a need to create a database if it doesn't already exist.  The obvious 
solution is to just use:

sqlite3 newdatabase.db

Except that it not only creates the db but also opens that db for commands.

Make yourself an empty database file and keep it somewhere safe.  When you need 
a new one just copy this existing file, and rename and/or move it to the right 
folder.

Simon.
___
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] Creating a database with a script or SQLite command

[Simon Slavin]

On 31 Aug 2011, at 2:36am, Pete Helgren wrote:

> I have a need to create a database if it doesn't already exist.  The obvious 
> solution is to just use:
> 
> sqlite3 newdatabase.db
> 
> Except that it not only creates the db but also opens that db for commands.

Make yourself an empty database file and keep it somewhere safe.  When you need 
a new one just copy this existing file, and rename and/or move it to the right 
folder.

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

[sqlite] Creating a database with a script or SQLite command

[Pete Helgren]

I have a need to create a database if it doesn't already exist.  The 
obvious solution is to just use:


sqlite3 newdatabase.db

Except that it not only creates the db but also opens that db for 
commands.  I am running this from a script so I want to just want to run 
the command from a script so that I know the database exists before 
issuing other commands.


I searched around the Internet for what I thought would be an easy 
answer and didn't find one.  I am running SQLite 3.3.13 from BusyBox 1.1.3


Thanks

--
Pete Helgren
Value Added Software, Inc
www.asaap.com
www.opensource4i.com

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

Re: [sqlite] Howto...multi-sqlite command string through sqlite3_prepare_v2() to create SINGLE statement

[e-mail mgbg25171]

David, Simon...that's good to know...Thank you very much indeed!


On 21 June 2011 13:19, Simon Slavin  wrote:

>
> On 21 Jun 2011, at 12:59pm, e-mail mgbg25171 wrote:
>
> > I was looking at prepare/step/finalise as a means of avoiding the
> callback
> > inherent in sqlite3_exec().
>
> You do not need to use the callback if you don't want it to do anything.
>  Just pass a NULL there.
>
> Simon.
> ___
> 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] Howto...multi-sqlite command string through sqlite3_prepare_v2() to create SINGLE statement

[Simon Slavin]

On 21 Jun 2011, at 12:59pm, e-mail mgbg25171 wrote:

> I was looking at prepare/step/finalise as a means of avoiding the callback
> inherent in sqlite3_exec().

You do not need to use the callback if you don't want it to do anything.  Just 
pass a NULL there.

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

Re: [sqlite] Howto...multi-sqlite command string through sqlite3_prepare_v2() to create SINGLE statement

[David Bicking]

Since none of the statements is a SELECT, as far as I know the callback 
would never be called. You can pass a zero as the callback address.

get_table will also handle all the statements in one pass, but will 
return an empty able, so you might as well use exec.

David

On 06/21/2011 07:59 AM, e-mail mgbg25171 wrote:
> Thank you for the clarification re...
> sqlite3_prepareXXX() only processing 1 statement at a time as opposed to
> sqlite3_exec() which...
> can handle "combined multi statements" in one shot.
> I was looking at prepare/step/finalise as a means of avoiding the callback
> inherent in sqlite3_exec().
> In the example I saw...the "combined multi statements" string was processed
> by SQLite3_Get_Table which...
> I assume can also handle "combined multi statements"
> Thank you both for your assistance.
> As you can probably gather...this is very new to me.
>
> On 21 June 2011 12:48, David Bicking  wrote:
>
>> On 06/21/2011 07:22 AM, e-mail mgbg25171 wrote:
>>> The commented out lines work.
>>> I'm wondering...
>>> a) is it possible to do what's not commented out
>>> b) what's the syntax re the "sql =..." and "sql +=..." lines
>>> Any help much appreciated!
>>>   >
>>> sql = "BEGIN";   //you need to add newline here
>>> sql += "create table episodes (id integer primary key, season int, name
>>> text)";
>>> sql += "insert into episodes(id, season, name) Values(1,2,'bill')";
>>> sql += "insert into episodes(id, season, name) Values(2,3,'bob')";
>>> sql += "COMMIT";
>>> rc = sqlite3_prepare(db, sql.c_str(), strlen( sql.c_str() ),,);
>>> rc = sqlite3_step(stmt);
>>
>> You will need to add semicolons within the quotes between each statement
>> as someone has already pointed out.
>>
>> Secondly, prepare only prepares one statement, so you would have to loop
>> through the statements. My C is rusty, but I think it is something like:
>>
>> tail = sql.c_str();
>> while (tail)
>> {
>>rc = sqlite3_prepare(db, tail, strlen(tail),,);
>>rc = sqlite3_step(stmt);
>>rc = sqlite3_finalize(stmt);
>> }
>>
>>
>> Alternatively, you can run the combined multi statements through
>> sqlite3_exec() in one shot.
>>
>> David
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Howto...multi-sqlite command string through sqlite3_prepare_v2() to create SINGLE statement

[e-mail mgbg25171]

Thank you Igor


On 21 June 2011 12:52, Igor Tandetnik <itandet...@mvps.org> wrote:

> e-mail mgbg25171 <mgbg25...@blueyonder.co.uk> wrote:
> > Howto...multi-sqlite command string through sqlite3_prepare_v2() to
> create SINGLE statement
>
> You can't do that.
>
> > sql = "BEGIN";   //you need to add newline here
> > sql += "create table episodes (id integer primary key, season int, name
> > text)";
> > sql += "insert into episodes(id, season, name) Values(1,2,'bill')";
> > sql += "insert into episodes(id, season, name) Values(2,3,'bob')";
> > sql += "COMMIT";
>
> sqlite3_exec can be used to execute multiple statements like this in a
> single call. You do need to terminate each statement with a semicolon.
> --
> Igor Tandetnik
>
> ___
> 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] Howto...multi-sqlite command string through sqlite3_prepare_v2() to create SINGLE statement

[e-mail mgbg25171]

Thank you for the clarification re...
sqlite3_prepareXXX() only processing 1 statement at a time as opposed to
sqlite3_exec() which...
can handle "combined multi statements" in one shot.
I was looking at prepare/step/finalise as a means of avoiding the callback
inherent in sqlite3_exec().
In the example I saw...the "combined multi statements" string was processed
by SQLite3_Get_Table which...
I assume can also handle "combined multi statements"
Thank you both for your assistance.
As you can probably gather...this is very new to me.

On 21 June 2011 12:48, David Bicking  wrote:

> On 06/21/2011 07:22 AM, e-mail mgbg25171 wrote:
> > The commented out lines work.
> > I'm wondering...
> > a) is it possible to do what's not commented out
> > b) what's the syntax re the "sql =..." and "sql +=..." lines
> > Any help much appreciated!
> > >
> > sql = "BEGIN";   //you need to add newline here
> > sql += "create table episodes (id integer primary key, season int, name
> > text)";
> > sql += "insert into episodes(id, season, name) Values(1,2,'bill')";
> > sql += "insert into episodes(id, season, name) Values(2,3,'bob')";
> > sql += "COMMIT";
> > rc = sqlite3_prepare(db, sql.c_str(), strlen( sql.c_str() ),,);
> > rc = sqlite3_step(stmt);
>
> You will need to add semicolons within the quotes between each statement
> as someone has already pointed out.
>
> Secondly, prepare only prepares one statement, so you would have to loop
> through the statements. My C is rusty, but I think it is something like:
>
> tail = sql.c_str();
> while (tail)
> {
>   rc = sqlite3_prepare(db, tail, strlen(tail), , );
>   rc = sqlite3_step(stmt);
>   rc = sqlite3_finalize(stmt);
> }
>
>
> Alternatively, you can run the combined multi statements through
> sqlite3_exec() in one shot.
>
> David
> ___
> 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] Howto...multi-sqlite command string through sqlite3_prepare_v2() to create SINGLE statement

[Igor Tandetnik]

e-mail mgbg25171 <mgbg25...@blueyonder.co.uk> wrote:
> Howto...multi-sqlite command string through sqlite3_prepare_v2() to create 
> SINGLE statement

You can't do that.

> sql = "BEGIN";   //you need to add newline here
> sql += "create table episodes (id integer primary key, season int, name
> text)";
> sql += "insert into episodes(id, season, name) Values(1,2,'bill')";
> sql += "insert into episodes(id, season, name) Values(2,3,'bob')";
> sql += "COMMIT";

sqlite3_exec can be used to execute multiple statements like this in a single 
call. You do need to terminate each statement with a semicolon.
-- 
Igor Tandetnik

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

Re: [sqlite] Howto...multi-sqlite command string through sqlite3_prepare_v2() to create SINGLE statement

[David Bicking]

On 06/21/2011 07:22 AM, e-mail mgbg25171 wrote:
> The commented out lines work.
> I'm wondering...
> a) is it possible to do what's not commented out
> b) what's the syntax re the "sql =..." and "sql +=..." lines
> Any help much appreciated!
> >
> sql = "BEGIN";   //you need to add newline here
> sql += "create table episodes (id integer primary key, season int, name
> text)";
> sql += "insert into episodes(id, season, name) Values(1,2,'bill')";
> sql += "insert into episodes(id, season, name) Values(2,3,'bob')";
> sql += "COMMIT";
> rc = sqlite3_prepare(db, sql.c_str(), strlen( sql.c_str() ),,);
> rc = sqlite3_step(stmt);

You will need to add semicolons within the quotes between each statement 
as someone has already pointed out.

Secondly, prepare only prepares one statement, so you would have to loop 
through the statements. My C is rusty, but I think it is something like:

tail = sql.c_str();
while (tail)
{
   rc = sqlite3_prepare(db, tail, strlen(tail), , );
   rc = sqlite3_step(stmt);
   rc = sqlite3_finalize(stmt);
}


Alternatively, you can run the combined multi statements through 
sqlite3_exec() in one shot.

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

Re: [sqlite] Howto...multi-sqlite command string through sqlite3_prepare_v2() to create SINGLE statement

[e-mail mgbg25171]

Thank you very much for the response.
Unless I'm doing something foolish (always a possibility) that doesn't seem
to work so...
here's the whole test program.

#include "stdafx.h"
#include "sqlite3.h"
#include "stdio.h"
#include "string.h"

#include "string"
#include "iostream"
using namespace std;
int _tmain(int argc, _TCHAR* argv[]) //default project main
{
int rc, i, ncols;
sqlite3 *db;
sqlite3_stmt *stmt;
//char *sql;
//replaced by
string sql;

const char *tail;


rc = sqlite3_open("foods.db", );
if(rc) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
return(1);
}
//== my additions to get thie definitive guide example working

//sql = "create table episodes (id integer primary key, season int, name
text);";
rc = sqlite3_prepare(db, sql, strlen(sql), , );
replaced by
//rc = sqlite3_prepare(db, sql.c_str(), strlen( sql.c_str() ), ,
);
//rc = sqlite3_step(stmt);
//
//sql = "insert into episodes(id, season, name) Values(1,2,'bill');";
rc = sqlite3_prepare(db, sql, strlen(sql), , );
replaced by
//rc = sqlite3_prepare(db, sql.c_str(), strlen( sql.c_str() ), ,
);
//rc = sqlite3_step(stmt);
//
//sql = "insert into episodes(id, season, name) Values(2,3,'bob')";
rc = sqlite3_prepare(db, sql, strlen(sql), , );
replaced by
//rc = sqlite3_prepare(db, sql.c_str(), strlen( sql.c_str() ), ,
);
//rc = sqlite3_step(stmt);

sql = "BEGIN;";   //you need to add newline here
sql += "create table episodes (id integer primary key, season int, name
text);";
sql += "insert into episodes(id, season, name) Values(1,2,'bill');";
sql += "insert into episodes(id, season, name) Values(2,3,'bob');";
sql += "COMMIT;";
rc = sqlite3_prepare(db, sql.c_str(), strlen( sql.c_str() ), , );
rc = sqlite3_step(stmt);

//

sql = "select * from episodes;";
//rc = sqlite3_prepare(db, sql, strlen(sql), , );
//replaced by
rc = sqlite3_prepare(db, sql.c_str(), strlen(sql.c_str()), , );

if(rc != SQLITE_OK) {
fprintf(stderr, "SQL error: %s\n", sqlite3_errmsg(db));
}

rc = sqlite3_step(stmt);
ncols = sqlite3_column_count(stmt);
while(rc == SQLITE_ROW) {

for(i=0; i < ncols; i++) {
fprintf(stderr, "'%s' ", sqlite3_column_text(stmt, i));
}
fprintf(stderr, "\n");
rc = sqlite3_step(stmt);
}
sqlite3_finalize(stmt);
sqlite3_close(db);

//if( remove( "foods.db" ) != 0 ) perror( "Error deleting file" ); else
puts( "File successfully deleted" );

getchar();

return 0;
}




On 21 June 2011 12:32, Black, Michael (IS) <michael.bla...@ngc.com> wrote:

> I believe this will work if you put the SQL-required semi-colons at the end
> of your statements.
>
>
>
> sql = "BEGIN;";   //you need to add newline here
> sql += "create table episodes (id integer primary key, season int, name
> text);";
> sql += "insert into episodes(id, season, name) Values(1,2,'bill');";
> sql += "insert into episodes(id, season, name) Values(2,3,'bob');";
> sql += "COMMIT;";
>
> I would say most people don't do this as any error returns won't tell you
> much.  You'd normally prepare each seperately.
>
>
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> NG Information Systems
>
> Advanced Analytics Directorate
>
>
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
> behalf of e-mail mgbg25171 [mgbg25...@blueyonder.co.uk]
> Sent: Tuesday, June 21, 2011 6:22 AM
> To: sqlite-users@sqlite.org
> Subject: EXT :[sqlite] Howto...multi-sqlite command string through
> sqlite3_prepare_v2() to create SINGLE statement
>
> The commented out lines work.
> I'm wondering...
> a) is it possible to do what's not commented out
> b) what's the syntax re the "sql =..." and "sql +=..." lines
> Any help much appreciated!
> [code]
> //sql = "create table episodes (id integer primary key, season int, name
> text);";
> rc = sqlite3_prepare(db, sql, strlen(sql), , );
> replaced by
> //rc = sqlite3_prepare(db, sql.c_str(), strlen( sql.c_str() ), ,
> );
> //rc = sqlite3_step(stmt);
> //
> //sql = "insert into episodes(id, season, name) Values(1,2,'bill');";
> rc = sqlite3_prepare(db, sql, strlen(sql), , );
> replaced by
> //rc = sqlite3_prepare(db, sql.c_str(), strlen( sql.c_str() ), ,
> );
> //rc = sqlite3_step(stmt);
> //
> //sql = "i

Re: [sqlite] Howto...multi-sqlite command string through sqlite3_prepare_v2() to create SINGLE statement

[Black, Michael (IS)]

I believe this will work if you put the SQL-required semi-colons at the end of 
your statements.



sql = "BEGIN;";   //you need to add newline here
sql += "create table episodes (id integer primary key, season int, name
text);";
sql += "insert into episodes(id, season, name) Values(1,2,'bill');";
sql += "insert into episodes(id, season, name) Values(2,3,'bob');";
sql += "COMMIT;";

I would say most people don't do this as any error returns won't tell you much. 
 You'd normally prepare each seperately.





Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of e-mail mgbg25171 [mgbg25...@blueyonder.co.uk]
Sent: Tuesday, June 21, 2011 6:22 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Howto...multi-sqlite command string through 
sqlite3_prepare_v2() to create SINGLE statement

The commented out lines work.
I'm wondering...
a) is it possible to do what's not commented out
b) what's the syntax re the "sql =..." and "sql +=..." lines
Any help much appreciated!
[code]
//sql = "create table episodes (id integer primary key, season int, name
text);";
rc = sqlite3_prepare(db, sql, strlen(sql), , );
replaced by
//rc = sqlite3_prepare(db, sql.c_str(), strlen( sql.c_str() ), ,
);
//rc = sqlite3_step(stmt);
//
//sql = "insert into episodes(id, season, name) Values(1,2,'bill');";
rc = sqlite3_prepare(db, sql, strlen(sql), , );
replaced by
//rc = sqlite3_prepare(db, sql.c_str(), strlen( sql.c_str() ), ,
);
//rc = sqlite3_step(stmt);
//
//sql = "insert into episodes(id, season, name) Values(2,3,'bob')";
rc = sqlite3_prepare(db, sql, strlen(sql), , );
replaced by
//rc = sqlite3_prepare(db, sql.c_str(), strlen( sql.c_str() ), ,
);
//rc = sqlite3_step(stmt);

sql = "BEGIN";   //you need to add newline here
sql += "create table episodes (id integer primary key, season int, name
text)";
sql += "insert into episodes(id, season, name) Values(1,2,'bill')";
sql += "insert into episodes(id, season, name) Values(2,3,'bob')";
sql += "COMMIT";
rc = sqlite3_prepare(db, sql.c_str(), strlen( sql.c_str() ), , );
rc = sqlite3_step(stmt);
[code]
___
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] Howto...multi-sqlite command string through sqlite3_prepare_v2() to create SINGLE statement

[e-mail mgbg25171]

The commented out lines work.
I'm wondering...
a) is it possible to do what's not commented out
b) what's the syntax re the "sql =..." and "sql +=..." lines
Any help much appreciated!
[code]
//sql = "create table episodes (id integer primary key, season int, name
text);";
rc = sqlite3_prepare(db, sql, strlen(sql), , );
replaced by
//rc = sqlite3_prepare(db, sql.c_str(), strlen( sql.c_str() ), ,
);
//rc = sqlite3_step(stmt);
//
//sql = "insert into episodes(id, season, name) Values(1,2,'bill');";
rc = sqlite3_prepare(db, sql, strlen(sql), , );
replaced by
//rc = sqlite3_prepare(db, sql.c_str(), strlen( sql.c_str() ), ,
);
//rc = sqlite3_step(stmt);
//
//sql = "insert into episodes(id, season, name) Values(2,3,'bob')";
rc = sqlite3_prepare(db, sql, strlen(sql), , );
replaced by
//rc = sqlite3_prepare(db, sql.c_str(), strlen( sql.c_str() ), ,
);
//rc = sqlite3_step(stmt);

sql = "BEGIN";   //you need to add newline here
sql += "create table episodes (id integer primary key, season int, name
text)";
sql += "insert into episodes(id, season, name) Values(1,2,'bill')";
sql += "insert into episodes(id, season, name) Values(2,3,'bob')";
sql += "COMMIT";
rc = sqlite3_prepare(db, sql.c_str(), strlen( sql.c_str() ), , );
rc = sqlite3_step(stmt);
[code]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Command line client and Tcl interface behaves differently?

[Fredrik Karlsson]

Hi,

Sorry, never mind this post. I haven't found the problem actually yet,
but a "bare minimum" example fed directly to the interpreter through
the command line works correctly:

% package require sqlite3
3.7.2
% set inf [open 1_schema.sql r]
file6
% set sql [read $inf]
[... the contents of the read file is dumped ..]
% close $inf
% sqlite3 db :memory:
% db eval $sql
% puts [db eval {SELECT sqlite_version(), sqlite_source_id()}]
3.7.2 {2010-08-23 18:52:01 42537b60566f288167f1b5864a5435986838e3a3}
% db eval {select * from SQLITE_MASTER} values {parray values}
[... data concerning all tables are dumped... ]

So, the error is somewhere else in the code. Sorry about that. :-/

/Fredrik

-- 
"Life is like a trumpet - if you don't put anything into it, you don't
get anything out of it."
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Command line client and Tcl interface behaves differently?

[Fredrik Karlsson]

On Sat, Nov 20, 2010 at 2:43 PM, Richard Hipp  wrote:
> On Sat, Nov 20, 2010 at 8:25 AM, Fredrik Karlsson wrote:
>
>> Dear list,
>>
>> I am having a silly problem, and need your expertise. I just want to
>> initiate a SQLite database using a schema file in Tcl, but I just get
>> an empty database whatever I do. I asked this question on the Tcl
>> list, and got a reply which I interpret to mean that this is not a
>> problem in my Tcl knowledge - but in my SQLite implementation.
>>
>> Anyway, loading from the same SQL file within the "sqlite3" command
>> line client is no problem
>>
>> Here is what I am doing:
>>
>> proc init_db {dbDir {dbFile db.sqlite3} {force 1} } {
>>
>>        set dbFile [file join [file normalize $dbDir] $dbFile ]
>>        if { $force == 1 && [file exists $dbFile]} {
>>                file delete $dbFile
>>        }
>>        sqlite3 db $dbFile
>>        set schemaFile [file normalize [file join  .. setup 1_schema.sql] ]
>>
>>        if {! [file exists $schemaFile] } {
>>                return -code error "Unable to open schema file $schemaFile"
>>        }
>>        set inf [open $schemaFile r]
>>        set sql [read $inf]
>>        close $inf
>>
>> Add here:   puts $sql
> Let's see what file you are really loading
>
>
>>        db eval $sql
>>
>
> And here:  puts [db eval {SELECT sqlite_version(), sqlite_source_id()}]
>
>
>>
>>        db close
>>        return [file normalize $dbFile ]
>>
>> }
>>
>> Does the Tcl interface and the "sqlite3" binary behave differently
>> when parsing instructions from a file?
>>
>> The SQL code in the 1_schema.sql" file is just table definitions and
>> comments using the /* */ syntax which loads ok in the command like
>> client, so what could be wrong?
>>
>> Thankful for all the help I could get on this.
>>
>> /Fredrik
>>
>>
>> --
>> "Life is like a trumpet - if you don't put anything into it, you don't
>> get anything out of it."
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

Thank you Rickard for the quick response. This is really code that is
part of a benchmarking framework (which used to work, oddly enough) so
the output is a bit verbose.

-
$ tclsh8.6 run_all_benchmarks.tcl
[Sat Nov 20 14:50:09 CET 2010] [bench] [debug] '::bench::run -errors 1
-iters 10 -match Emu*simple*one* /usr/local/bin/tclsh8.6
./emuquery.benchmark'
[Sat Nov 20 14:50:09 CET 2010] [bench] [info] 'Benchmark
/usr/local/bin/tclsh8.6'
[Sat Nov 20 14:50:09 CET 2010] [bench] [info] 'emuquery.benchmark'
[Sat Nov 20 14:50:09 CET 2010] [bench] [info] 'Sourcing ./emuquery.benchmark'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'Running '
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'Copied 1 files to
/private/var/folders/N8/N8aqQyE1FE8Bb0ONohLfsk+++TM/-Tmp-'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info]
'/private/var/folders/N8/N8aqQyE1FE8Bb0ONohLfsk+++TM/-Tmp-'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'CREATE TABLE utterances ('
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'id INTEGER PRIMARY KEY
AUTOINCREMENT,'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'name TEXT,'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'filelength REAL,'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'updated_at TEXT,'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'checksum_algorithm TEXT,'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'checksum TEXT,'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'UNIQUE(name) ON CONFLICT FAIL'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] ');'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'CREATE TABLE levels ('
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'id INTEGER PRIMARY KEY
AUTOINCREMENT,'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'name TEXT UNIQUE ON
CONFLICT FAIL'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] ',weight FLOAT UNIQUE'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] ');'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'CREATE TABLE level_level ('
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'parent_id INTEGER
REFERENCES levels(id),'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'child_id INTEGER
REFERENCES levels(id),'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'PRIMARY KEY(parent_id,child_id)'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] ');'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'CREATE TABLE tc_level_level ('
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'ancestor_id INTEGER
REFERENCES levels(id),'
[Sat Nov 20 14:50:10 CET 2010] [bench] [info] 'descendant_id INTEGER
REFERENCES levels(id),'

Re: [sqlite] Command line client and Tcl interface behaves differently?

[Richard Hipp]

On Sat, Nov 20, 2010 at 8:25 AM, Fredrik Karlsson wrote:

> Dear list,
>
> I am having a silly problem, and need your expertise. I just want to
> initiate a SQLite database using a schema file in Tcl, but I just get
> an empty database whatever I do. I asked this question on the Tcl
> list, and got a reply which I interpret to mean that this is not a
> problem in my Tcl knowledge - but in my SQLite implementation.
>
> Anyway, loading from the same SQL file within the "sqlite3" command
> line client is no problem
>
> Here is what I am doing:
>
> proc init_db {dbDir {dbFile db.sqlite3} {force 1} } {
>
>set dbFile [file join [file normalize $dbDir] $dbFile ]
>if { $force == 1 && [file exists $dbFile]} {
>file delete $dbFile
>}
>sqlite3 db $dbFile
>set schemaFile [file normalize [file join  .. setup 1_schema.sql] ]
>
>if {! [file exists $schemaFile] } {
>return -code error "Unable to open schema file $schemaFile"
>}
>set inf [open $schemaFile r]
>set sql [read $inf]
>close $inf
>
> Add here:   puts $sql
Let's see what file you are really loading


>db eval $sql
>

And here:  puts [db eval {SELECT sqlite_version(), sqlite_source_id()}]


>
>db close
>return [file normalize $dbFile ]
>
> }
>
> Does the Tcl interface and the "sqlite3" binary behave differently
> when parsing instructions from a file?
>
> The SQL code in the 1_schema.sql" file is just table definitions and
> comments using the /* */ syntax which loads ok in the command like
> client, so what could be wrong?
>
> Thankful for all the help I could get on this.
>
> /Fredrik
>
>
> --
> "Life is like a trumpet - if you don't put anything into it, you don't
> get anything out of it."
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

[sqlite] Command line client and Tcl interface behaves differently?

[Fredrik Karlsson]

Dear list,

I am having a silly problem, and need your expertise. I just want to
initiate a SQLite database using a schema file in Tcl, but I just get
an empty database whatever I do. I asked this question on the Tcl
list, and got a reply which I interpret to mean that this is not a
problem in my Tcl knowledge - but in my SQLite implementation.

Anyway, loading from the same SQL file within the "sqlite3" command
line client is no problem

Here is what I am doing:

proc init_db {dbDir {dbFile db.sqlite3} {force 1} } {

set dbFile [file join [file normalize $dbDir] $dbFile ]
if { $force == 1 && [file exists $dbFile]} {
file delete $dbFile
}
sqlite3 db $dbFile
set schemaFile [file normalize [file join  .. setup 1_schema.sql] ]

if {! [file exists $schemaFile] } {
return -code error "Unable to open schema file $schemaFile"
}
set inf [open $schemaFile r]
set sql [read $inf]
close $inf

db eval $sql

db close
return [file normalize $dbFile ]

}

Does the Tcl interface and the "sqlite3" binary behave differently
when parsing instructions from a file?

The SQL code in the 1_schema.sql" file is just table definitions and
comments using the /* */ syntax which loads ok in the command like
client, so what could be wrong?

Thankful for all the help I could get on this.

/Fredrik


-- 
"Life is like a trumpet - if you don't put anything into it, you don't
get anything out of it."
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Command Line Shell.

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 08/11/2010 10:28 PM, Kirk Clemons wrote:
> I would like to be able to tell SQLite that if line 2 is bad just skip to 
> line 3 and so on until the database has been parsed all the through 
> maximizing the amount of data I retain.

It would require detailed knowledge of the corruption and what it
affected.  For example a very slow brute force algorithm would be to ask
for all rows with a rowid >=0, add one to the highest returned and do
another select greater than that.  Perhaps repeat in reverse order.

You are however far better off preventing the corruption in the first
place, ensuring a good backup and recovery strategy etc.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkxkaF0ACgkQmOOfHg372QRDWACcDUlXb5vpI00VvX5f0tbq548/
L4cAoMEbCws5s57qi+vj8Dz9sLKVc7H1
=FmQE
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite Command Line Shell.

[Kirk Clemons]

I am trying to alter the dump function of the command line shell. I am not 
completely familiar with C Programming so I am sort of in the dark here.
As I understand it's workings now the .dump command performs a 'select *' on 
each row in row id order. If it encounters an error it skips to the end of the 
database and takes each row in reverse order.
This means I get all the information before and after any errors.

This is great but ideally I would want any data in between as well. If I have a 
database with 100 rows and row 2 and 99 are corrupt
but everything in between is fine I lose my entire database when I call the 
dump command.

I would like to be able to tell SQLite that if line 2 is bad just skip to line 
3 and so on until the database has been parsed all the through maximizing the 
amount of data I retain.

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

Re: [sqlite] Command line tool always return the first column in a select

[Benoit Aubuchon]

I upgraded to 3.7.0 and it fixes the problem.

Thanks!

Ben

On Wed, Aug 4, 2010 at 8:07 AM, Black, Michael (IS)
<michael.bla...@ngc.com>wrote:

> I just ran your code on 3.6.23.1 and it works just fine.
>
> Can you try a newer version?
>
> x.sql:
> create table mytable (id VARCHAR(255), name VARCHAR(255), address
> VARCHAR(255), PRIMARY KEY(id));
> insert into mytable (id, name, address) VALUES ('123abc','charlie', '123
> st');
> insert into mytable (id, name, address) VALUES ('yyzz', 'bob',
> '456nowhere');
>
> SQLite version 3.6.23.1
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> .read x.sql
> sqlite> select * from mytable;
> 123abc|charlie|123 st
> yyzz|bob|456nowhere
>
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Northrop Grumman Information Systems
>
>
> 
>
> From: sqlite-users-boun...@sqlite.org on behalf of Benoit Aubuchon
> Sent: Tue 8/3/2010 4:55 PM
> To: sqlite-users@sqlite.org
> Subject: EXTERNAL:[sqlite] Command line tool always return the first column
> in a select
>
>
>
> Everytime I select something from a table I always get the first selected
> field only. Here's what I mean:
>
> # sqlite3 mytable.db
> SQLite version 3.6.22
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> *sqlite> .show*
> echo: off
>  explain: off
>  headers: off
> mode: list
> nullvalue: ""
>   output: stdout
> separator: "|"
>width:
> *sqlite> create table mytable (id VARCHAR(255), name VARCHAR(255), address
> VARCHAR(255), PRIMARY KEY(id));*
> *sqlite> insert into mytable (id, name, address) VALUES ('123abc',
> 'charlie', '123 st');*
> *sqlite> insert into mytable (id, name, address) VALUES ('yyzz', 'bob',
> '456
> nowhere');*
> *sqlite> select * from mytable;*
> 123abc
> yyzz
> *sqlite> .head ON*
> *sqlite> select * from mytable;*
> id
> 123abc
> yyzz
> *sqlite> select id, name, address from mytable;*
> id
> 123abc
> yyzz
> *sqlite> select name from mytable;*
> name
> charlie
> bob
> *sqlite> select name, address from mytable;*
> name
> charlie
> bob
> *sqlite> select address, id from mytable;*
> address
> 123 st
> 456 nowhere
> *sqlite> .dump*
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE mytable (id VARCHAR(255), name VARCHAR(255), address
> VARCHAR(255), PRIMARY KEY(id));
> INSERT INTO "mytable" VALUES('123abc','charlie','123 st');
> INSERT INTO "mytable" VALUES('yyzz','bob','456 nowhere');
> COMMIT;
> *sqlite> select id, name, address from mytable;*
> id
> 123abc
> yyzz
> *sqlite> .mode csv*
> *sqlite> select id, name, address from mytable;*
> id
> 123abc
> yyzz
> *sqlite> .mode line*
> *sqlite> select id, name, address from mytable;*
>   id = 123abc
>
>   id = yyzz
> *sqlite> select * from mytable;*
>   id = 123abc
>
>   id = yyzz
> *sqlite> .dump*
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE mytable (id VARCHAR(255), name VARCHAR(255), address
> VARCHAR(255), PRIMARY KEY(id));
> INSERT INTO "mytable" VALUES('123abc','charlie','123 st');
> INSERT INTO "mytable" VALUES('yyzz','bob','456 nowhere');
> COMMIT;
>
> # uname -a
> Linux appserver1 2.6.32-gentoo-r1 #1 SMP Wed Jan 13 05:48:57 EST 2010
> x86_64
> Intel(R) Xeon(R) CPU X5550 @ 2.67GHz GenuineIntel GNU/Linux
>
> If I use sqlite from PHP it works as expected. Its only from the command
> line that it doesn't show the other fields.
>
> Has anyone encountered this problem before?
>
> Thanks
>
> Ben
> ___
> 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] Command line tool always return the first column in a select

[Black, Michael (IS)]

I just ran your code on 3.6.23.1 and it works just fine.
 
Can you try a newer version?
 
x.sql:
create table mytable (id VARCHAR(255), name VARCHAR(255), address VARCHAR(255), 
PRIMARY KEY(id));
insert into mytable (id, name, address) VALUES ('123abc','charlie', '123 st');
insert into mytable (id, name, address) VALUES ('yyzz', 'bob', '456nowhere');

SQLite version 3.6.23.1
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .read x.sql
sqlite> select * from mytable;
123abc|charlie|123 st
yyzz|bob|456nowhere
 
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Benoit Aubuchon
Sent: Tue 8/3/2010 4:55 PM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:[sqlite] Command line tool always return the first column in 
a select



Everytime I select something from a table I always get the first selected
field only. Here's what I mean:

# sqlite3 mytable.db
SQLite version 3.6.22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
*sqlite> .show*
 echo: off
  explain: off
  headers: off
 mode: list
nullvalue: ""
   output: stdout
separator: "|"
width:
*sqlite> create table mytable (id VARCHAR(255), name VARCHAR(255), address
VARCHAR(255), PRIMARY KEY(id));*
*sqlite> insert into mytable (id, name, address) VALUES ('123abc',
'charlie', '123 st');*
*sqlite> insert into mytable (id, name, address) VALUES ('yyzz', 'bob', '456
nowhere');*
*sqlite> select * from mytable;*
123abc
yyzz
*sqlite> .head ON*
*sqlite> select * from mytable;*
id
123abc
yyzz
*sqlite> select id, name, address from mytable;*
id
123abc
yyzz
*sqlite> select name from mytable;*
name
charlie
bob
*sqlite> select name, address from mytable;*
name
charlie
bob
*sqlite> select address, id from mytable;*
address
123 st
456 nowhere
*sqlite> .dump*
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE mytable (id VARCHAR(255), name VARCHAR(255), address
VARCHAR(255), PRIMARY KEY(id));
INSERT INTO "mytable" VALUES('123abc','charlie','123 st');
INSERT INTO "mytable" VALUES('yyzz','bob','456 nowhere');
COMMIT;
*sqlite> select id, name, address from mytable;*
id
123abc
yyzz
*sqlite> .mode csv*
*sqlite> select id, name, address from mytable;*
id
123abc
yyzz
*sqlite> .mode line*
*sqlite> select id, name, address from mytable;*
   id = 123abc

   id = yyzz
*sqlite> select * from mytable;*
   id = 123abc

   id = yyzz
*sqlite> .dump*
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE mytable (id VARCHAR(255), name VARCHAR(255), address
VARCHAR(255), PRIMARY KEY(id));
INSERT INTO "mytable" VALUES('123abc','charlie','123 st');
INSERT INTO "mytable" VALUES('yyzz','bob','456 nowhere');
COMMIT;

# uname -a
Linux appserver1 2.6.32-gentoo-r1 #1 SMP Wed Jan 13 05:48:57 EST 2010 x86_64
Intel(R) Xeon(R) CPU X5550 @ 2.67GHz GenuineIntel GNU/Linux

If I use sqlite from PHP it works as expected. Its only from the command
line that it doesn't show the other fields.

Has anyone encountered this problem before?

Thanks

Ben
___
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] Command line tool always return the first column in a select

[Benoit Aubuchon]

Everytime I select something from a table I always get the first selected
field only. Here's what I mean:

# sqlite3 mytable.db
SQLite version 3.6.22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
*sqlite> .show*
 echo: off
  explain: off
  headers: off
 mode: list
nullvalue: ""
   output: stdout
separator: "|"
width:
*sqlite> create table mytable (id VARCHAR(255), name VARCHAR(255), address
VARCHAR(255), PRIMARY KEY(id));*
*sqlite> insert into mytable (id, name, address) VALUES ('123abc',
'charlie', '123 st');*
*sqlite> insert into mytable (id, name, address) VALUES ('yyzz', 'bob', '456
nowhere');*
*sqlite> select * from mytable;*
123abc
yyzz
*sqlite> .head ON*
*sqlite> select * from mytable;*
id
123abc
yyzz
*sqlite> select id, name, address from mytable;*
id
123abc
yyzz
*sqlite> select name from mytable;*
name
charlie
bob
*sqlite> select name, address from mytable;*
name
charlie
bob
*sqlite> select address, id from mytable;*
address
123 st
456 nowhere
*sqlite> .dump*
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE mytable (id VARCHAR(255), name VARCHAR(255), address
VARCHAR(255), PRIMARY KEY(id));
INSERT INTO "mytable" VALUES('123abc','charlie','123 st');
INSERT INTO "mytable" VALUES('yyzz','bob','456 nowhere');
COMMIT;
*sqlite> select id, name, address from mytable;*
id
123abc
yyzz
*sqlite> .mode csv*
*sqlite> select id, name, address from mytable;*
id
123abc
yyzz
*sqlite> .mode line*
*sqlite> select id, name, address from mytable;*
   id = 123abc

   id = yyzz
*sqlite> select * from mytable;*
   id = 123abc

   id = yyzz
*sqlite> .dump*
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE mytable (id VARCHAR(255), name VARCHAR(255), address
VARCHAR(255), PRIMARY KEY(id));
INSERT INTO "mytable" VALUES('123abc','charlie','123 st');
INSERT INTO "mytable" VALUES('yyzz','bob','456 nowhere');
COMMIT;

# uname -a
Linux appserver1 2.6.32-gentoo-r1 #1 SMP Wed Jan 13 05:48:57 EST 2010 x86_64
Intel(R) Xeon(R) CPU X5550 @ 2.67GHz GenuineIntel GNU/Linux

If I use sqlite from PHP it works as expected. Its only from the command
line that it doesn't show the other fields.

Has anyone encountered this problem before?

Thanks

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

[sqlite] Byte Order Mark in sqlite command line

[ttessier]

Hi, 

I just wanted to throw this out there. I was using sqlite command line in 
windows 7 today using a UTF-8 encoded file with A BOM. I was getting an issue 
where there was an sql error on line1
and a few records were getting inserted. This was a multi line file and each 
record insert was occuring on a separate line. I re-encoded the file to ascii 
without a BOM using notepad++ and everything worked fine. Didn't know if there 
was a bug or something with that.

Thanks,
Tim Tessier

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

Re: [sqlite] command line does not accept arrow keys

[Qianqian Fang]

hi Jay

Jay A. Kreibich wrote:
>   Yes.  Readline support is not on by default.
>
>   You need to compile it with -DHAVE_READLINE and add -lreadline
>   to the linker:
>
> $ cc -DHAVE_READLINE -o sqlite3 sqlite3.c shell.c -lreadline

recompiling sqlite3 from source code with your command
solved the problem. thank you so much

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

Re: [sqlite] command line does not accept arrow keys

[Jay A. Kreibich]

On Fri, Nov 20, 2009 at 10:29:20PM -0500, Qianqian Fang scratched on the wall:

> Is this possibly caused by how sqlite binary was compiled?

  Yes.  Readline support is not on by default.

  You need to compile it with -DHAVE_READLINE and add -lreadline
  to the linker:

$ cc -DHAVE_READLINE -o sqlite3 sqlite3.c shell.c -lreadline

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] command line does not accept arrow keys

[Qianqian Fang]

I don't know, is there a way I can tell?

the binary was downloaded from sqlite website, version
is 3.6.2. The binary stores in a remote server, running
Debian 3, I used ssh in a Terminator/gnome-terminal
bash shell.

I also installed sqlite on my local machine, running
Ubuntu Karmic, interestingly, the one from Ubuntu repo,
v2.8.17, does not have this problem, but the one
I downloaded from sqlite's website, v3.6.18, has the
same issue.

Is this possibly caused by how sqlite binary was
compiled?

Stephan Wehner wrote:
> On Fri, Nov 20, 2009 at 6:02 PM, Qianqian Fang
>  wrote:
>   
>> hi
>>
>> I can not type arrow keys in the sqlite3 command line, all the arrow
>> keys (as well as other keys in the small keyboard) will be shown as
>> escape sequence "^]]A".
>> 
>
> Could it be a problem with readline? See
>
> http://www.sqlite.org/cvstrac/wiki?p=ReadLine
>
> Stephan
>
>   
>> I am using Terminator/gnome-terminal, is there a setting I need to
>> twig in order to get this working?
>>
>> thanks
>>
>> Qianqian
>>
>> ___
>> 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