Re: [sqlite] BUG Report on sqlite 3.6.20 "Error in SQL parser between sqlite3.3.4 and sqlite3.6.20"

[Dan Kennedy]

On Dec 30, 2009, at 4:52 AM, Wilson, Ronald wrote:

> I get the same error in 3.6.18, so probably the same solution  
> applies in 3.6.20.  I got the query to work with a sub-select.

Changed between 3.6.6 and 3.6.7 from the looks of things.



>
> SQLite version 3.6.18
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> BEGIN TRANSACTION;
> sqlite> CREATE TABLE basica(
>   ...>   x,
>   ...>   y,
>   ...>   suma
>   ...> );
> sqlite> INSERT INTO "basica" VALUES('Austria','1996-03',5904.0);
> sqlite> INSERT INTO "basica" VALUES('Austria','1996-04',21904.0);
> sqlite> INSERT INTO "basica" VALUES('Germany','1996-03',10545.0);
> sqlite> INSERT INTO "basica" VALUES('Germany','1996-04',13687.0);
> sqlite> INSERT INTO "basica" VALUES('USA','1996-03',21814.0);
> sqlite> INSERT INTO "basica" VALUES('USA','1996-04',13108.0);
> sqlite>
> sqlite> CREATE TABLE groupLimX(
>   ...>   x,
>   ...>   sumaXs
>   ...> );
> sqlite> INSERT INTO "groupLimX" VALUES('USA',305843.0);
> sqlite> INSERT INTO "groupLimX" VALUES('Germany',258820.0);
> sqlite> INSERT INTO "groupLimX" VALUES('Austria',140668.0);
> sqlite>
> sqlite> CREATE TABLE groupLimY(
>   ...>   y,
>   ...>   sumaYs
>   ...> );
> sqlite> INSERT INTO "groupLimY" VALUES('1996-04',113818.0);
> sqlite> INSERT INTO "groupLimY" VALUES('1996-03',102947.0);
> sqlite> COMMIT;
> sqlite>
> sqlite> .header on
> sqlite> SELECT * FROM groupLimY INNER JOIN groupLimX;
> y|sumaYs|x|sumaXs
> 1996-04|113818.0|USA|305843.0
> 1996-04|113818.0|Germany|258820.0
> 1996-04|113818.0|Austria|140668.0
> 1996-03|102947.0|USA|305843.0
> 1996-03|102947.0|Germany|258820.0
> 1996-03|102947.0|Austria|140668.0
> sqlite> CREATE TABLE mia AS SELECT * FROM groupLimY INNER JOIN  
> groupLimX;
> sqlite> SELECT * FROM mia LEFT JOIN basica USING (y, x);
> y|sumaYs|x|sumaXs|suma
> 1996-04|113818.0|USA|305843.0|13108.0
> 1996-04|113818.0|Germany|258820.0|13687.0
> 1996-04|113818.0|Austria|140668.0|21904.0
> 1996-03|102947.0|USA|305843.0|21814.0
> 1996-03|102947.0|Germany|258820.0|10545.0
> 1996-03|102947.0|Austria|140668.0|5904.0
> sqlite> SELECT * FROM (groupLimY INNER JOIN groupLimX)  LEFT JOIN  
> basica USING (y,x);
> SQL error: cannot join using column y - column not present in both  
> tables
>
> sqlite> SELECT * FROM (select x, sumaXs, y, sumaYs from groupLimY  
> INNER JOIN groupLimX) LEFT JOIN basica USING (y,x);
> x|sumaXs|y|sumaYs|suma
> USA|305843.0|1996-04|113818.0|13108.0
> Germany|258820.0|1996-04|113818.0|13687.0
> Austria|140668.0|1996-04|113818.0|21904.0
> USA|305843.0|1996-03|102947.0|21814.0
> Germany|258820.0|1996-03|102947.0|10545.0
> Austria|140668.0|1996-03|102947.0|5904.0
> sqlite>
>
> sqlite> SELECT * FROM (select * from groupLimY INNER JOIN groupLimX)  
> LEFT JOIN basica USING (y,x);
> y|sumaYs|x|sumaXs|suma
> 1996-04|113818.0|USA|305843.0|13108.0
> 1996-04|113818.0|Germany|258820.0|13687.0
> 1996-04|113818.0|Austria|140668.0|21904.0
> 1996-03|102947.0|USA|305843.0|21814.0
> 1996-03|102947.0|Germany|258820.0|10545.0
> 1996-03|102947.0|Austria|140668.0|5904.0
> sqlite>
>
> Ron Wilson, Engineering Project Lead
> (o) 434.455.6453, (m) 434.851.1612, www.harris.com
>
> HARRIS CORPORATION   |   RF Communications Division
> assuredcommunications(tm)
>
>
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>> boun...@sqlite.org] On Behalf Of javaj1...@elxala.com
>> Sent: Tuesday, December 29, 2009 8:56 AM
>> To: sqlite-users@sqlite.org
>> Subject: [sqlite] BUG Report on sqlite 3.6.20 "Error in SQL parser  
>> between
>> sqlite3.3.4 and sqlite3.6.20"
>>
>> Hello,
>>
>> I detect this problem because a program using sqlite command line  
>> works
>> on sqlite.3.3.4 but
>> it does not anymore using sqlite3.6.20
>>
>> PROBLEM TITLE: Unjustified Error joining tables in sqlite3.6.20 (in
>> previous version sqlite3.3.4 OK)
>>   OR Error in SQL parser between
>> sqlite3.3.4 and sqlite3.6.20
>>
>> TESTED WITH: sqlite3.6.20 and sqlite3.3.4 Windows command line exes
>>
>> HOW TO REPRODUCE IT:
>>   Execute following batch on both versions of sqlite
>>
>> sqlite3 < Fails3.6.20.sql
>>
>>   when using 3.6.20 we get the "unjustified error"
>>
>> Best regards,
>> Alejandro
>>
>>
>> Fails3.6.20.sql--
>> BEGIN TRANSACTION;
>> CREATE TABLE basica(
>>  x,
>>  y,
>>  suma
>> );
>> INSERT INTO "basica" VALUES('Austria','1996-03',5904.0);
>> INSERT INTO "basica" VALUES('Austria','1996-04',21904.0);
>> INSERT INTO "basica" VALUES('Germany','1996-03',10545.0);
>> INSERT INTO "basica" VALUES('Germany','1996-04',13687.0);
>> INSERT INTO "basica" VALUES('USA','1996-03',21814.0);
>> INSERT INTO "basica" VALUES('USA','1996-04',13108.0);
>>
>> CREATE TABLE groupLimX(
>>  x,
>>  sumaXs
>> );
>> INSERT INTO "groupLimX" VALUES('USA',305843.0);
>> INSERT INTO "groupLimX" VALUES('Germany',258820.0);
>> INSERT INTO "groupLimX" VALUES('Austria',140668.0);
>>
>> CREATE TABLE 

Re: [sqlite] fts3 issue with tokenizing of content during a query

[Dan Kennedy]

On Dec 30, 2009, at 6:25 AM, Nick Hodapp wrote:

> Hi -
>
> I'm using sqlite 3.6.21 with this
> patch >,
> which I found in this forum a few weeks ago.  I'm also using a custom
> tokenizer which I wrote.
>
> My scenario is this:  I am storing XHTML in the database, and I want  
> to
> FTS-enable this content.  I only want to index the text contained  
> within the
> XHTML elements, not the element names or attributes.  (e.g. " index
> this="or this">index this")  My tokenizer skips over element  
> names and
> attributes, then delegates the element textual content to the Porter
> tokenizer.  On return from the Porter tokenizer, I correct the token  
> offset
> and length values to be the actual offsets within the document (Porter
> tokenizer doesn't ever see the whole document, just a string within  
> a tag).
>
> I didn't want to ship my tokenizer with my app for two reasons.  1 -  
> I wrote
> it using an API not available to my client app, 2 - it doesn't make  
> sense
> because on the client the user will be entering search terms that  
> aren't
> surrounded by xml tags, which is what my tokenizer expects.   
> Instead, my
> client registers a tokenizer with the same name as my custom  
> tokenizer, but
> in fact it is registering a copy of the porter tokenizer.
>
> I expected this to work fine - and it appeared to, until I  
> discovered that
> it was pulling out text in some of the xml attributes - which  
> shouldn't be
> indexed.
>
> It turns out that FTS3 is re-tokenizing the content (not just the  
> search
> term) on the client (using my copy of the Porter tokenizer) and  
> returning
> those results.  I don't understand why - is this a bug or is this  
> normal
> behavior?

It runs the tokenizer on returned documents as part of the snippet() or
offsets() function. The full-text index doesn't actually store the byte
offsets returned by the tokenizer xNext() call, just the token number.
So you have to re-tokenize to figure out the byte offsets required by
snippet() or offsets().

Dan.



>  I expected the fts index to retain all of the token offsets/sizes
> such that they wouldn't have to be recomputed on the client.
>
> My workaround is to port my tokenizer so that it runs on the client,  
> and to
> wrap search terms in dummy xml tags like this.   But  
> I feel I
> shouldn't have to do this...
>
> Any feedback appreciated...
>
> Nick Hodapp
> ___
> 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] Archive Search Engine

[Simon Slavin]

On 30 Dec 2009, at 2:56am, Roger Binns wrote:

> Bill Marvin wrote:
>> It would be very helpful if there was a search engine for the sqlite-user 
>> mailing list archive.  
> 
> Gmane does a pretty good job and gives you other ways of reading the list
> (eg NNTP/news, RSS):
> 
>  http://gmane.org/info.php?group=gmane.comp.db.sqlite.general
> 
> To do a search, click on "Searching" on the left and put
> gmane.comp.db.sqlite.general in the group field.

Another way is to just do a web search for whatever you want.  I use this a 
lot.  Sometimes it comes up with a post to this list, but other times someone 
I've never heard of came up with a good solution and posted it to their blog or 
something.

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

Re: [sqlite] Archive Search Engine

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Bill Marvin wrote:
> It would be very helpful if there was a search engine for the sqlite-user 
> mailing list archive.  

Gmane does a pretty good job and gives you other ways of reading the list
(eg NNTP/news, RSS):

  http://gmane.org/info.php?group=gmane.comp.db.sqlite.general

To do a search, click on "Searching" on the left and put
gmane.comp.db.sqlite.general in the group field.

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

iEYEARECAAYFAks6wVEACgkQmOOfHg372QSF3wCfTRCm7bhoV9pFxGgFVwyEQLhV
7DAAnRmYeHHVaxrQ9lx8ZcGxaQ9hT/l5
=P74A
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .dump of utf16 database

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Kevin Ryde wrote:
> Perhaps it doesn't matter to anything.

The only effect is what happens behind the scenes.  If you primarily use the
- -16 interfaces to bind and retrieve text then the database also being utf16
means you avoid SQLite doing a conversion (assuming the byte orders also
match).  Some SQLite internals like query parsing are UTF8 only anyway (ie
the prepare-16 functions convert to UTF8 and call the UTF8 variants).

There may also be size differences in your database depending on the
distribution of code points in your text data and the size of strings in
proportion to other data and SQLite's metadata.

You can dress your immediate issue by doing this:

(echo 'pragma encoding="UTF-16";' ; sqlite3 old.db .dump ) | sqlite3 new.db

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

iEYEARECAAYFAks6wFQACgkQmOOfHg372QSioQCbBjDWAb1mSDKW4G3yOD1Igdz0
n2EAn19xGp4/HwJfj2Mgwqt8Sh9gjfCL
=v0qa
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] fts3 issue with tokenizing of content during a query

[Nick Hodapp]

Hi -

I'm using sqlite 3.6.21 with this
patch,
which I found in this forum a few weeks ago.  I'm also using a custom
tokenizer which I wrote.

My scenario is this:  I am storing XHTML in the database, and I want to
FTS-enable this content.  I only want to index the text contained within the
XHTML elements, not the element names or attributes.  (e.g. "index this")  My tokenizer skips over element names and
attributes, then delegates the element textual content to the Porter
tokenizer.  On return from the Porter tokenizer, I correct the token offset
and length values to be the actual offsets within the document (Porter
tokenizer doesn't ever see the whole document, just a string within a tag).

I didn't want to ship my tokenizer with my app for two reasons.  1 - I wrote
it using an API not available to my client app, 2 - it doesn't make sense
because on the client the user will be entering search terms that aren't
surrounded by xml tags, which is what my tokenizer expects.  Instead, my
client registers a tokenizer with the same name as my custom tokenizer, but
in fact it is registering a copy of the porter tokenizer.

I expected this to work fine - and it appeared to, until I discovered that
it was pulling out text in some of the xml attributes - which shouldn't be
indexed.

It turns out that FTS3 is re-tokenizing the content (not just the search
term) on the client (using my copy of the Porter tokenizer) and returning
those results.  I don't understand why - is this a bug or is this normal
behavior?  I expected the fts index to retain all of the token offsets/sizes
such that they wouldn't have to be recomputed on the client.

My workaround is to port my tokenizer so that it runs on the client, and to
wrap search terms in dummy xml tags like this.   But I feel I
shouldn't have to do this...

Any feedback appreciated...

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

[sqlite] .dump of utf16 database

[Kevin Ryde]

I was trying some .dump round-trips like

sqlite3 old.db .dump | sqlite3 new.db

and noticed if old.db is utf16, ie. pragma encoding=utf16, then new.db
doesn't get that but instead is utf8.  Is that intentional?  Would
slipping a pragma into the .dump output preserve the db coding?

(I don't want the .dump text output to be utf16, and this was only an
experiment to see what came out.  Perhaps it doesn't matter to
anything.)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG Report on sqlite 3.6.20 "Error in SQL parser between sqlite3.3.4 and sqlite3.6.20"

[Wilson, Ronald]

I get the same error in 3.6.18, so probably the same solution applies in 
3.6.20.  I got the query to work with a sub-select.

SQLite version 3.6.18
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> BEGIN TRANSACTION;
sqlite> CREATE TABLE basica(
   ...>   x,
   ...>   y,
   ...>   suma
   ...> );
sqlite> INSERT INTO "basica" VALUES('Austria','1996-03',5904.0);
sqlite> INSERT INTO "basica" VALUES('Austria','1996-04',21904.0);
sqlite> INSERT INTO "basica" VALUES('Germany','1996-03',10545.0);
sqlite> INSERT INTO "basica" VALUES('Germany','1996-04',13687.0);
sqlite> INSERT INTO "basica" VALUES('USA','1996-03',21814.0);
sqlite> INSERT INTO "basica" VALUES('USA','1996-04',13108.0);
sqlite>
sqlite> CREATE TABLE groupLimX(
   ...>   x,
   ...>   sumaXs
   ...> );
sqlite> INSERT INTO "groupLimX" VALUES('USA',305843.0);
sqlite> INSERT INTO "groupLimX" VALUES('Germany',258820.0);
sqlite> INSERT INTO "groupLimX" VALUES('Austria',140668.0);
sqlite>
sqlite> CREATE TABLE groupLimY(
   ...>   y,
   ...>   sumaYs
   ...> );
sqlite> INSERT INTO "groupLimY" VALUES('1996-04',113818.0);
sqlite> INSERT INTO "groupLimY" VALUES('1996-03',102947.0);
sqlite> COMMIT;
sqlite>
sqlite> .header on
sqlite> SELECT * FROM groupLimY INNER JOIN groupLimX;
y|sumaYs|x|sumaXs
1996-04|113818.0|USA|305843.0
1996-04|113818.0|Germany|258820.0
1996-04|113818.0|Austria|140668.0
1996-03|102947.0|USA|305843.0
1996-03|102947.0|Germany|258820.0
1996-03|102947.0|Austria|140668.0
sqlite> CREATE TABLE mia AS SELECT * FROM groupLimY INNER JOIN groupLimX;
sqlite> SELECT * FROM mia LEFT JOIN basica USING (y, x);
y|sumaYs|x|sumaXs|suma
1996-04|113818.0|USA|305843.0|13108.0
1996-04|113818.0|Germany|258820.0|13687.0
1996-04|113818.0|Austria|140668.0|21904.0
1996-03|102947.0|USA|305843.0|21814.0
1996-03|102947.0|Germany|258820.0|10545.0
1996-03|102947.0|Austria|140668.0|5904.0
sqlite> SELECT * FROM (groupLimY INNER JOIN groupLimX)  LEFT JOIN basica USING 
(y,x);
SQL error: cannot join using column y - column not present in both tables

sqlite> SELECT * FROM (select x, sumaXs, y, sumaYs from groupLimY INNER JOIN 
groupLimX) LEFT JOIN basica USING (y,x);
x|sumaXs|y|sumaYs|suma
USA|305843.0|1996-04|113818.0|13108.0
Germany|258820.0|1996-04|113818.0|13687.0
Austria|140668.0|1996-04|113818.0|21904.0
USA|305843.0|1996-03|102947.0|21814.0
Germany|258820.0|1996-03|102947.0|10545.0
Austria|140668.0|1996-03|102947.0|5904.0
sqlite>

sqlite> SELECT * FROM (select * from groupLimY INNER JOIN groupLimX) LEFT JOIN 
basica USING (y,x);
y|sumaYs|x|sumaXs|suma
1996-04|113818.0|USA|305843.0|13108.0
1996-04|113818.0|Germany|258820.0|13687.0
1996-04|113818.0|Austria|140668.0|21904.0
1996-03|102947.0|USA|305843.0|21814.0
1996-03|102947.0|Germany|258820.0|10545.0
1996-03|102947.0|Austria|140668.0|5904.0
sqlite>

Ron Wilson, Engineering Project Lead
(o) 434.455.6453, (m) 434.851.1612, www.harris.com

HARRIS CORPORATION   |   RF Communications Division 
assuredcommunications(tm)


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of javaj1...@elxala.com
> Sent: Tuesday, December 29, 2009 8:56 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] BUG Report on sqlite 3.6.20 "Error in SQL parser between
> sqlite3.3.4 and sqlite3.6.20"
> 
> Hello,
> 
> I detect this problem because a program using sqlite command line works
> on sqlite.3.3.4 but
> it does not anymore using sqlite3.6.20
> 
> PROBLEM TITLE: Unjustified Error joining tables in sqlite3.6.20 (in
> previous version sqlite3.3.4 OK)
>OR Error in SQL parser between
> sqlite3.3.4 and sqlite3.6.20
> 
> TESTED WITH: sqlite3.6.20 and sqlite3.3.4 Windows command line exes
> 
> HOW TO REPRODUCE IT:
>Execute following batch on both versions of sqlite
> 
>  sqlite3 < Fails3.6.20.sql
> 
>when using 3.6.20 we get the "unjustified error"
> 
> Best regards,
> Alejandro
> 
> 
> Fails3.6.20.sql--
> BEGIN TRANSACTION;
> CREATE TABLE basica(
>   x,
>   y,
>   suma
> );
> INSERT INTO "basica" VALUES('Austria','1996-03',5904.0);
> INSERT INTO "basica" VALUES('Austria','1996-04',21904.0);
> INSERT INTO "basica" VALUES('Germany','1996-03',10545.0);
> INSERT INTO "basica" VALUES('Germany','1996-04',13687.0);
> INSERT INTO "basica" VALUES('USA','1996-03',21814.0);
> INSERT INTO "basica" VALUES('USA','1996-04',13108.0);
> 
> CREATE TABLE groupLimX(
>   x,
>   sumaXs
> );
> INSERT INTO "groupLimX" VALUES('USA',305843.0);
> INSERT INTO "groupLimX" VALUES('Germany',258820.0);
> INSERT INTO "groupLimX" VALUES('Austria',140668.0);
> 
> CREATE TABLE groupLimY(
>   y,
>   sumaYs
> );
> INSERT INTO "groupLimY" VALUES('1996-04',113818.0);
> INSERT INTO "groupLimY" VALUES('1996-03',102947.0);
> COMMIT;
> 
> .header on
> SELECT * FROM groupLimY INNER JOIN groupLimX;
> CREATE TABLE mia AS SELECT * FROM groupLimY INNER JOIN groupLimX;
> SELECT * FROM mia LEFT JOIN basica USING 

Re: [sqlite] selective result columns

[Griggs, Donald]

I'm not clear.   Were you able to use Simon's syntax link:
   http://sqlite.awardspace.us/syntax/sqlitepg09.htm
To correct your syntax?

SQL works with sets, and will always return the same number of columns, but you 
*can* use sql to force a NULL, an empty string, or a space to be returned for a 
column value.  Does that not meet your need?

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

Re: [sqlite] selective result columns

[nomorecaddy]

I don't have access to that level of software, so that's the problem.  Thanks
for your response - I like the power of select case, and was hoping that
case could be applied in other areas as well.



Simon Slavin-3 wrote:
> 
> 
> On 29 Dec 2009, at 8:34pm, nomorecaddy wrote:
> 
>> I'm looking for a SQL query that returns a variable number of
>> columns.  Many of my columns contain NULL data, and I want to avoid
>> showing
>> the column altogether in that case.
> 
> That is something that must be handled by your software.  There is no way
> in SQL to return an answer to a SELECT which has a different number of
> columns in different records.  You could make SQL return columns with NULL
> in and your software could automatically recognise them and know not to
> print them.
> 
> Remember that SQL is a database engine.  It's job is to supply data.  It's
> your software's job to understand what needs to be done with it.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/selective-result-columns-tp26958131p26960765.html
Sent from the SQLite mailing list archive at Nabble.com.

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

[sqlite] Possible error when using overloaded name "oid" inside a trigger

[Craig Maudlin]

 There appears to be a problem with the use of an explicit column named
"oid" from inside an 'instead of insert' trigger on a view. This seems to
have been introduced in version 3.6.18 as the results differ from those of
version 3.6.17.

SQLite version 3.6.21 behaves the same as:
SQLite version 3.6.18
sqlite>
sqlite> create temp table foo (oid integer, nid integer);
sqlite>
sqlite> create temp view vxdata as select * from foo;
sqlite>
sqlite> create temp table log(a integer, b integer);
sqlite>
sqlite> create temp trigger tr instead of insert on vxdata
   ...>for each row begin
   ...>   insert into log values(new.oid, new.nid);
   ...>end;
sqlite>
sqlite> insert into vxdata(oid, nid) select 100, 200;
sqlite> insert into foo(oid, nid) select 100, 200;
sqlite> select * from log;
-1|200
sqlite> select * from foo;
100|200
sqlite> .exit


SQLite version 3.6.17
sqlite>
sqlite> create temp table foo (oid integer, nid integer);
sqlite>
sqlite> create temp view vxdata as select * from foo;
sqlite>
sqlite> create temp table log(a integer, b integer);
sqlite>
sqlite> create temp trigger tr instead of insert on vxdata
   ...>for each row begin
   ...>   insert into log values(new.oid, new.nid);
   ...>end;
sqlite>
sqlite> insert into vxdata(oid, nid) select 100, 200;
sqlite> insert into foo(oid, nid) select 100, 200;
sqlite> select * from log;
100|200
sqlite> select * from foo;
100|200
sqlite> .exit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] selective result columns

[Simon Slavin]

On 29 Dec 2009, at 8:34pm, nomorecaddy wrote:

> I'm looking for a SQL query that returns a variable number of
> columns.  Many of my columns contain NULL data, and I want to avoid showing
> the column altogether in that case.

That is something that must be handled by your software.  There is no way in 
SQL to return an answer to a SELECT which has a different number of columns in 
different records.  You could make SQL return columns with NULL in and your 
software could automatically recognise them and know not to print them.

Remember that SQL is a database engine.  It's job is to supply data.  It's your 
software's job to understand what needs to be done with it.

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

Re: [sqlite] undefined reference to `readline'

[Lutz Horn]

Hi,

Am 29.12.09 19:28, schrieb Angelo:
> sqlite-3.6.16/conftest.c:37: undefined reference to `readline'
> collect2: ld returned 1 exit status.
> 
> So it's clear, the linker does not find the readline function , but I don't 
> understand why.

Do you have the readline library including headers installed?

Lutz

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

[sqlite] BUG Report on sqlite 3.6.20 "Error in SQL parser between sqlite3.3.4 and sqlite3.6.20"

[javaj1...@elxala.com]

Hello,

I detect this problem because a program using sqlite command line works 
on sqlite.3.3.4 but

it does not anymore using sqlite3.6.20

PROBLEM TITLE: Unjustified Error joining tables in sqlite3.6.20 (in 
previous version sqlite3.3.4 OK)
  OR Error in SQL parser between 
sqlite3.3.4 and sqlite3.6.20


TESTED WITH: sqlite3.6.20 and sqlite3.3.4 Windows command line exes

HOW TO REPRODUCE IT:
  Execute following batch on both versions of sqlite
  
sqlite3 < Fails3.6.20.sql
  
  when using 3.6.20 we get the "unjustified error"


Best regards,
Alejandro


Fails3.6.20.sql--
BEGIN TRANSACTION;
CREATE TABLE basica(
 x,
 y,
 suma
);
INSERT INTO "basica" VALUES('Austria','1996-03',5904.0);
INSERT INTO "basica" VALUES('Austria','1996-04',21904.0);
INSERT INTO "basica" VALUES('Germany','1996-03',10545.0);
INSERT INTO "basica" VALUES('Germany','1996-04',13687.0);
INSERT INTO "basica" VALUES('USA','1996-03',21814.0);
INSERT INTO "basica" VALUES('USA','1996-04',13108.0);

CREATE TABLE groupLimX(
 x,
 sumaXs
);
INSERT INTO "groupLimX" VALUES('USA',305843.0);
INSERT INTO "groupLimX" VALUES('Germany',258820.0);
INSERT INTO "groupLimX" VALUES('Austria',140668.0);

CREATE TABLE groupLimY(
 y,
 sumaYs
);
INSERT INTO "groupLimY" VALUES('1996-04',113818.0);
INSERT INTO "groupLimY" VALUES('1996-03',102947.0);
COMMIT;

.header on
SELECT * FROM groupLimY INNER JOIN groupLimX;
CREATE TABLE mia AS SELECT * FROM groupLimY INNER JOIN groupLimX;
SELECT * FROM mia LEFT JOIN basica USING (y, x);

/* IN THIS LAST SELECT sqlite3 (3.6.20) FAILS!! WHILE sqlite 3.3.4 
SUCCESSED */
SELECT * FROM (groupLimY INNER JOIN groupLimX)  LEFT JOIN basica USING 
(y, x);

---




BEGIN TRANSACTION;
CREATE TABLE basica(
  x,
  y,
  suma
);
INSERT INTO "basica" VALUES('Austria','1996-03',5904.0);
INSERT INTO "basica" VALUES('Austria','1996-04',21904.0);
INSERT INTO "basica" VALUES('Germany','1996-03',10545.0);
INSERT INTO "basica" VALUES('Germany','1996-04',13687.0);
INSERT INTO "basica" VALUES('USA','1996-03',21814.0);
INSERT INTO "basica" VALUES('USA','1996-04',13108.0);

CREATE TABLE groupLimX(
  x,
  sumaXs
);
INSERT INTO "groupLimX" VALUES('USA',305843.0);
INSERT INTO "groupLimX" VALUES('Germany',258820.0);
INSERT INTO "groupLimX" VALUES('Austria',140668.0);

CREATE TABLE groupLimY(
  y,
  sumaYs
);
INSERT INTO "groupLimY" VALUES('1996-04',113818.0);
INSERT INTO "groupLimY" VALUES('1996-03',102947.0);
COMMIT;

.header on
SELECT * FROM groupLimY INNER JOIN groupLimX;
CREATE TABLE mia AS SELECT * FROM groupLimY INNER JOIN groupLimX;
SELECT * FROM mia LEFT JOIN basica USING (y, x);

/* IN THIS LAST SELECT sqlite3 (3.6.20) FAILS!! WHILE sqlite 3.3.4 SUCCESSED */
SELECT * FROM (groupLimY INNER JOIN groupLimX)  LEFT JOIN basica USING (y, x);
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] selective result columns

[nomorecaddy]

Thanks, I'm looking for a SQL query that returns a variable number of
columns.  Many of my columns contain NULL data, and I want to avoid showing
the column altogether in that case.



Simon Slavin-3 wrote:
> 
> 
> On 29 Dec 2009, at 6:29pm, nomorecaddy wrote:
> 
>> Is it possible to run an SQL query in sqlite that displays columns only
>> on a
>> condition?  For example:
>> select col1, (case when 1==2 then col2) from myTable
>> 
>> In this case, I only want to show one column (col1)
> 
> I'm not sure I understand your question, but I'll guess.  A SELECT command
> must return the same number of columns in each row.  You can use a
> conditional function to change what appears in each row and your CASE is
> fine, but you need to correct the syntax:
> 
> http://sqlite.awardspace.us/syntax/sqlitepg09.htm
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/selective-result-columns-tp26958131p26959565.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] selective result columns

[Simon Slavin]

On 29 Dec 2009, at 6:29pm, nomorecaddy wrote:

> Is it possible to run an SQL query in sqlite that displays columns only on a
> condition?  For example:
> select col1, (case when 1==2 then col2) from myTable
> 
> In this case, I only want to show one column (col1)

I'm not sure I understand your question, but I'll guess.  A SELECT command must 
return the same number of columns in each row.  You can use a conditional 
function to change what appears in each row and your CASE is fine, but you need 
to correct the syntax:

http://sqlite.awardspace.us/syntax/sqlitepg09.htm

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

[sqlite] selective result columns

[nomorecaddy]

Is it possible to run an SQL query in sqlite that displays columns only on a
condition?  For example:
select col1, (case when 1==2 then col2) from myTable

In this case, I only want to show one column (col1)

Thanks
-- 
View this message in context: 
http://old.nabble.com/selective-result-columns-tp26958131p26958131.html
Sent from the SQLite mailing list archive at Nabble.com.

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

[sqlite] undefined reference to `readline'

[Angelo]

Hi everybody,

sqlite 3.6.16 on linux debian.

Using the amalgamation src, I compile sqlite using configure --enable-readline. 
But
using the shell, I haven' t the history function.

The config.log file says:
sqlite-3.6.16/conftest.c:37: undefined reference to `readline'
collect2: ld returned 1 exit status.

So it's clear, the linker does not find the readline function , but I don't 
understand why.

Any idea ?

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

[sqlite] BUG Report -- schema.test does not check for authorization in build

[Noah Hart]

Test schema-13.1 fails with 
Error: {authorization not available in this build} 

Test needs to be bracket with
ifcapable auth {

do_test schema-13.1 {
  set S [sqlite3_prepare_v2 db "SELECT * FROM sqlite_master" -1 dummy]
  db function hello hello
  db function hello {}
  db auth auth


db auth fails because tclsqlite.c has

#ifdef SQLITE_OMIT_AUTHORIZATION
Tcl_AppendResult(interp, "authorization not available in this
build", 0);
return TCL_ERROR;
#else



Regards,

Noah Hart



CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


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

Re: [sqlite] Archive Search Engine

[Adam DeVita]

But there is a search engine on:

http://www.mail-archive.com/sqlite-users%40sqlite.org/info.html

It is right at the top.

Adam

On Tue, Dec 29, 2009 at 12:02 PM, Bill Marvin wrote:

>
> It would be very helpful if there was a search engine for the sqlite-user
> mailing list archive.  My question might have already been answered, but
> currently using the archive I have to manually look through the threads
> month by month.  It is like finding a needle in a haystack!
>
> Bill
>
>
> _
> Your E-mail and More On-the-Go. Get Windows Live Hotmail Free.
> http://clk.atdmt.com/GBL/go/171222985/direct/01/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
7100 Warden Ave, Unit 3
Markham ON, L3R 8B5
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Archive Search Engine

[Bill Marvin]

It would be very helpful if there was a search engine for the sqlite-user 
mailing list archive.  My question might have already been answered, but 
currently using the archive I have to manually look through the threads month 
by month.  It is like finding a needle in a haystack!

Bill

  
_
Your E-mail and More On-the-Go. Get Windows Live Hotmail Free.
http://clk.atdmt.com/GBL/go/171222985/direct/01/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Requirements for index-aware INSERT SELECT

[Max Vlasov]

The code I use can calculate data flow for sql queries (summing xRead iAmt
in VFS) and I noticed that many variations of INSERT SELECT led to very big
data flow (multiplication of the db size). I thought that such queries can
be optimized if both tables are indexed accordingly and finally the
following query

INSERT OR IGNORE INTO Table (Field) SELECT SomeOtherField FROM OtherTable
ORDER BY SomeOtherField

produced significant reduce in data flow. (Field Is indexed in Table). I
don't think the difference is related to some caching since the variant
without ORDER BY shows 50 MB data transfer for 17 MB base while adding ORDER
BY reduces it to 1 MB (1:50 ratio so far).

But my other query uses more complex schema (with LEFT JOIN and several
fields (although indexed together)). The problem is I could not optimize the
query to reduce the data flow in this case.
Are there any specific requirements for the inserts like the first one?
Couldn not to find information about this in the documentation.

Thanks

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

Re: [sqlite] SQLitedb problem

[Artur Reilin]

Because the data was deleted. If the data was deleted it doesn't mean,  
that the data is not more in the database. It just not overwritten or not  
cleaned by an vacuum command. Is that so hard to believe?

Artur

--

Am 28.12.2009, 19:09 Uhr, schrieb Othman Guessous  
:

> Hello,
>
> Thanks you for your reply.
>
> Ok i will explain my sqlite's problem with more details.
>
> The problem concern the address book sqlitedb file from my iPhone. There  
> is
> some data (contacts) from this file don't appear on my Iphone. And i  
> verfy
> that by using a sqlite application browser : SQLite Database Browser.It's
> the same results.
>
> But when i open the adressbook.sqlitedb file with notepad or pspad (the
> sqlitedb file is not an ascii file but we can read the ascii data stored  
> in
> this file) i can find these contacts. So i saw that they are not removed.
>
> I think that these data are ignored. So how data can be ignored on a
> sqlitedn file? How can i fix this problem so i can view them on my  
> iphone or
> SQLite Database Browser.
>
> Othman.
>
> On Mon, Dec 28, 2009 at 1:04 PM, Pavel Ivanov  wrote:
>
>> What's the problem with the answers already given?
>>
>> Pavel
>>
>> On Mon, Dec 28, 2009 at 6:13 AM, Othman Guessous
>>  wrote:
>> > Hello,
>> >
>> > I wait for your help. Please can you answer to me ASAP.
>> >
>> > Thanks,
>> > Othman.
>> >
>> > On Mon, Dec 21, 2009 at 12:35 AM, Othman Guessous <
>> guessous.oth...@gmail.com
>> >> wrote:
>> >
>> >> Hello,
>> >>
>> >> I have a problem with a sqlitedb file. So there is some data in this
>> >> sqlitedb file (on notepad or pspad we can found easily these data)  
>> but
>> they
>> >> don't appear on a sqlite application browser (i use SQLite Database
>> Browser
>> >> 2.0 b1)...it seems that these data are ignored.
>> >>
>> >> Then how can i fix my problem?
>> >>
>> >> Thanks,
>> >> Othman
>> >>
>> > ___
>> > 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] SQLitedb problem

[Othman Guessous]

I understand now.

Thank you Pavel for the link. But i don't understand why i don't receive the
answers to my gmail inbox. However, I joined the mailing list. So i'll check
my registration.

I thank everyone for the responses. I will read them with attention.

Othman.

On Mon, Dec 28, 2009 at 6:23 PM, Pavel Ivanov  wrote:

> http://www.mail-archive.com/sqlite-users@sqlite.org/msg49299.html
>
>
> Pavel
>
> On Mon, Dec 28, 2009 at 1:15 PM, Othman Guessous
>  wrote:
> > Thanks, for your remark. I paid no attention to the mailing list. In
> fact, I
> > wanted to respond to the list.
> >
> > But, i doesn't receive on my inbox answers from the mailing list.
> >
> > Is there any answers?
> >
> > Othman.
> >
> > On Mon, Dec 28, 2009 at 6:05 PM, Pavel Ivanov 
> wrote:
> >>
> >> 1. Don't reply directly to me. If you write your question to the
> >> mailing list all further discussion should take place in the list.
> >> 2. Do you realize that if you write your question to the mailing list
> >> then all members of the list will answer to the list, not directly to
> >> your e-mail? Yes, I wrote directly to you because I've realized that
> >> you don't read this list at all (why do you write to it then?).
> >> 3. Prove me wrong: did you read all the answers given you in the mailing
> >> list?
> >>
> >>
> >> Pavel
> >>
> >> On Mon, Dec 28, 2009 at 12:58 PM, Othman Guessous
> >>  wrote:
> >> > Hello Pavel,
> >> >
> >> > Thanks you for your reply.
> >> >
> >> > Ok i will explain my sqlite's problem with more details.
> >> >
> >> > The problem concern the address book sqlitedb file from my iPhone.
> There
> >> > is
> >> > some data (contacts) from this file don't appear on my Iphone. And i
> >> > verfy
> >> > that by using a sqlite application browser : SQLite Database
> >> > Browser.It's
> >> > the same results.
> >> >
> >> > But when i open the adressbook.sqlitedb file with notepad or pspad
> (the
> >> > sqlitedb file is not an ascii file but we can read the ascii data
> stored
> >> > in
> >> > this file) i can find these contacts. So i saw that they are not
> >> > removed.
> >> >
> >> > I think that these data are ignored. So how data can be ignored on a
> >> > sqlitedn file? How can i fix this problem so i can view them on my
> >> > iphone or
> >> > SQLite Database Browser.
> >> >
> >> > I thank you in advance for your return.
> >> >
> >> > Othman.
> >> >
> >> > On Mon, Dec 28, 2009 at 1:04 PM, Pavel Ivanov 
> >> > wrote:
> >> >>
> >> >> What's the problem with the answers already given?
> >> >>
> >> >> Pavel
> >> >>
> >> >> On Mon, Dec 28, 2009 at 6:13 AM, Othman Guessous
> >> >>  wrote:
> >> >> > Hello,
> >> >> >
> >> >> > I wait for your help. Please can you answer to me ASAP.
> >> >> >
> >> >> > Thanks,
> >> >> > Othman.
> >> >> >
> >> >> > On Mon, Dec 21, 2009 at 12:35 AM, Othman Guessous
> >> >> >  >> >> >> wrote:
> >> >> >
> >> >> >> Hello,
> >> >> >>
> >> >> >> I have a problem with a sqlitedb file. So there is some data in
> this
> >> >> >> sqlitedb file (on notepad or pspad we can found easily these data)
> >> >> >> but
> >> >> >> they
> >> >> >> don't appear on a sqlite application browser (i use SQLite
> Database
> >> >> >> Browser
> >> >> >> 2.0 b1)...it seems that these data are ignored.
> >> >> >>
> >> >> >> Then how can i fix my problem?
> >> >> >>
> >> >> >> Thanks,
> >> >> >> Othman
> >> >> >>
> >> >> > ___
> >> >> > 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] SQLitedb problem

[Othman Guessous]

Thanks, for your remark. I paid no attention to the mailing list. In fact, I
wanted to respond to the list.

But, i doesn't receive on my inbox answers from the mailing list.

Is there any answers?

Othman.

On Mon, Dec 28, 2009 at 6:05 PM, Pavel Ivanov  wrote:

> 1. Don't reply directly to me. If you write your question to the
> mailing list all further discussion should take place in the list.
> 2. Do you realize that if you write your question to the mailing list
> then all members of the list will answer to the list, not directly to
> your e-mail? Yes, I wrote directly to you because I've realized that
> you don't read this list at all (why do you write to it then?).
> 3. Prove me wrong: did you read all the answers given you in the mailing
> list?
>
>
> Pavel
>
> On Mon, Dec 28, 2009 at 12:58 PM, Othman Guessous
>  wrote:
> > Hello Pavel,
> >
> > Thanks you for your reply.
> >
> > Ok i will explain my sqlite's problem with more details.
> >
> > The problem concern the address book sqlitedb file from my iPhone. There
> is
> > some data (contacts) from this file don't appear on my Iphone. And i
> verfy
> > that by using a sqlite application browser : SQLite Database Browser.It's
> > the same results.
> >
> > But when i open the adressbook.sqlitedb file with notepad or pspad (the
> > sqlitedb file is not an ascii file but we can read the ascii data stored
> in
> > this file) i can find these contacts. So i saw that they are not removed.
> >
> > I think that these data are ignored. So how data can be ignored on a
> > sqlitedn file? How can i fix this problem so i can view them on my iphone
> or
> > SQLite Database Browser.
> >
> > I thank you in advance for your return.
> >
> > Othman.
> >
> > On Mon, Dec 28, 2009 at 1:04 PM, Pavel Ivanov 
> wrote:
> >>
> >> What's the problem with the answers already given?
> >>
> >> Pavel
> >>
> >> On Mon, Dec 28, 2009 at 6:13 AM, Othman Guessous
> >>  wrote:
> >> > Hello,
> >> >
> >> > I wait for your help. Please can you answer to me ASAP.
> >> >
> >> > Thanks,
> >> > Othman.
> >> >
> >> > On Mon, Dec 21, 2009 at 12:35 AM, Othman Guessous
> >> >  >> >> wrote:
> >> >
> >> >> Hello,
> >> >>
> >> >> I have a problem with a sqlitedb file. So there is some data in this
> >> >> sqlitedb file (on notepad or pspad we can found easily these data)
> but
> >> >> they
> >> >> don't appear on a sqlite application browser (i use SQLite Database
> >> >> Browser
> >> >> 2.0 b1)...it seems that these data are ignored.
> >> >>
> >> >> Then how can i fix my problem?
> >> >>
> >> >> Thanks,
> >> >> Othman
> >> >>
> >> > ___
> >> > 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] SQLitedb problem

[Othman Guessous]

Hello,

Thanks you for your reply.

Ok i will explain my sqlite's problem with more details.

The problem concern the address book sqlitedb file from my iPhone. There is
some data (contacts) from this file don't appear on my Iphone. And i verfy
that by using a sqlite application browser : SQLite Database Browser.It's
the same results.

But when i open the adressbook.sqlitedb file with notepad or pspad (the
sqlitedb file is not an ascii file but we can read the ascii data stored in
this file) i can find these contacts. So i saw that they are not removed.

I think that these data are ignored. So how data can be ignored on a
sqlitedn file? How can i fix this problem so i can view them on my iphone or
SQLite Database Browser.

Othman.

On Mon, Dec 28, 2009 at 1:04 PM, Pavel Ivanov  wrote:

> What's the problem with the answers already given?
>
> Pavel
>
> On Mon, Dec 28, 2009 at 6:13 AM, Othman Guessous
>  wrote:
> > Hello,
> >
> > I wait for your help. Please can you answer to me ASAP.
> >
> > Thanks,
> > Othman.
> >
> > On Mon, Dec 21, 2009 at 12:35 AM, Othman Guessous <
> guessous.oth...@gmail.com
> >> wrote:
> >
> >> Hello,
> >>
> >> I have a problem with a sqlitedb file. So there is some data in this
> >> sqlitedb file (on notepad or pspad we can found easily these data) but
> they
> >> don't appear on a sqlite application browser (i use SQLite Database
> Browser
> >> 2.0 b1)...it seems that these data are ignored.
> >>
> >> Then how can i fix my problem?
> >>
> >> Thanks,
> >> Othman
> >>
> > ___
> > 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] Using incremental BLOB I/O when processing result set

[Pavel Ivanov]

> Its a shame that a BLOB handle is not returned as a result of a query
> rather than the BLOB itself.

Let me correct you. It's not a shame, it's database specifics and it
has its good points. For me personally I'd hate if SQLite returned me
some abstract handle if I requested blob value.

Note: if you select blob value for example from MS SQL it's also
loaded as a whole into servers memory - you just don't care about
that. But depending on client implementation the blob value can be
also loaded in full into client's memory as well (despite your usage
of SQLGetData() function), so this behavior is not something
SQLite-specific.

Also note: for latest versions of MS SQL Microsoft strongly recommends
to not use text and image datatypes (real LOB types) but use
varchar(max) and varbinary(max) instead. These types can obtain any
value LOBs could get but they also will never return to you any handle
in case you've selected them - only the whole value as a bunch. So
again as you see this behavior is not SQLite-specific.

And the last note: I've never heard of any database engine developer
who cares about making it possible to use his DBMS with any other DBMS
in a "database independent manner". If somebody needs that
"independence" usually he writes database-specific drivers which have
independent API and internally handle all cases differently.


Pavel

On Tue, Dec 29, 2009 at 2:52 AM, Mark Hessling  wrote:
> Well that is very disappointing :-(
>
> Not being able to extract a portion of a BLOB from a result set without
> having the complete BLOB in memory makes it impractical to use BLOBs in
> SQLite in a database independent manner.
>
> Its a shame that a BLOB handle is not returned as a result of a query
> rather than the BLOB itself.
>
> Thanks for the clarification anyway.
>
> Cheers, Mark
>
> On Mon, 2009-12-28 at 21:17 -0500, Igor Tandetnik wrote:
>> Zaher Dirkey wrote:
>> > If i want to extract BLOB to a file that mean it is must the whale
>> > blob be loaded to memory before can save it?
>>
>> No, not with BLOB I/O. But you have to be careful: don't mention the BLOB 
>> field in your SELECT statement, retrieve the ROWID instead. Then use it in 
>> sqlite3_blob_open call.
>>
>> Igor Tandetnik
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> --
>
> * Mark Hessling, m...@rexx.org http://www.rexx.org/
> * Author of THE, a Free XEDIT/KEDIT editor, Rexx/SQL, Rexx/CURL,  etc.
> * Maintainer of Regina Rexx interpreter and Rexx/Tk
> * Use Rexx? join the Rexx Language Association: http://www.rexxla.org/
>
> ___
> 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] Which is faster raw file I/O or sqlite BLOB

[Pavel Ivanov]

It depends on numerous facts. I'd say if size of all your files is
measured in megabytes and you're not dealing with thousands of files
in the same directory then raw file I/O will be faster. If size of
files is measured mostly in tens or hundreds of bytes and you need
thousands and millions of them then SQLite will be faster.


Pavel

On Tue, Dec 29, 2009 at 3:55 AM, _h_  wrote:
> Hi,
>
> I am planning to use BLOB to store file contents.
> So can you please suggest, which is faster 'raw file I/O' or 'sqlite BLOB'.
>
>
> Thank you in advance.
> -H
> ___
> 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] cross-database time function.

[Alexey Pechnikov]

Hello!

On Tuesday 29 December 2009 04:21:07 Simon Slavin wrote:
> I agree that this is often an acceptable alternative.  But
> 
> * it's hard to decipher if you're reading the data by eye

SQLite internal juliandays format is not human readable too. 

> * the system does not deal with leap seconds correctly

It's not the problem becouse the format precision is 1 second.

> * the system terminates in 2038 (if you use Unix's old 32-bit standard)
> * one day you may need to read the data on a non-unix platform

In cross-platform Tcl:

tclsh8.5 [~]clock format 1000
Wed Nov 16 12:46:40 MSK 5138

> Nevertheless, if your data starts off as a Unix epoch, it can be fast and 
> convenient to just store it without having to do any conversion.

As example, Cisco devices and some Unix daemons produce datetime in this format.

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Which is faster raw file I/O or sqlite BLOB

[_h_]

Hi,

I am planning to use BLOB to store file contents.
So can you please suggest, which is faster 'raw file I/O' or 'sqlite BLOB'.


Thank you in advance.
-H
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users