Re: [sqlite] Help with Sqlite

[Graeme]

As the previous rely said, this is not a sqlite problem. I am not sure what it 
is because Firefox by itself does not seem to allow direct editing of the 
sqlite databases it uses (are you using some extension?).

I suggest you ask on the Ubuntu forums. Also, look for the issue in the Wubi 
launchpad bug tracker. If you are using a FF extension see what support the 
author of that offers. Incidentally, Wubi has some limitations compared to a 
proper (i.e. on its own partition) Linux install and is probably better for 
trying out Ubuntu rather than permanent use.

Graeme

On Wednesday 20 August 2008 10:01:51 Jeffrey Needle wrote:
> I'm using the install of Firefox that comes with the Wubi install of
> Linux.  I like sqlite, but have a little problem.  Perhaps someone can
> help.
>
> When I add a new record to a database, an entry screen comes up with my
> fields and the ability to enter the new record.  But the information I
> type into the input field seems placed in the field a bit too low --
> about half of each letter is cut off at the bottom and I can't really
> read what I'm typing.
>
> Is there a way to fix this?  Has anyone else had this experience?
>
> Thanks.



-- 
Graeme Pietersz
http://moneyterms.co.uk/
http://pietersz.co.uk/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with Sqlite

[P Kishor]

On 8/20/08, Jeffrey Needle <[EMAIL PROTECTED]> wrote:
> I'm using the install of Firefox that comes with the Wubi install of
>  Linux.  I like sqlite, but have a little problem.  Perhaps someone can
>  help.

You don't have any problem with SQLite. Your problem is with whatever
this Wubi thing  is and with Firefox. You will likely get much more
directed help asking on the appropriate forum, unless someone on this
list happens to use the same.


>
>  When I add a new record to a database, an entry screen comes up with my
>  fields and the ability to enter the new record.  But the information I
>  type into the input field seems placed in the field a bit too low --
>  about half of each letter is cut off at the bottom and I can't really
>  read what I'm typing.
>
>  Is there a way to fix this?  Has anyone else had this experience?
>
>  Thanks.
>
>  --
>
>  
>  Jeffrey Needle
>  [EMAIL PROTECTED]
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Help with Sqlite

[Jeffrey Needle]

I'm using the install of Firefox that comes with the Wubi install of
Linux.  I like sqlite, but have a little problem.  Perhaps someone can
help.

When I add a new record to a database, an entry screen comes up with my
fields and the ability to enter the new record.  But the information I
type into the input field seems placed in the field a bit too low --
about half of each letter is cut off at the bottom and I can't really
read what I'm typing.

Is there a way to fix this?  Has anyone else had this experience?

Thanks.

-- 


Jeffrey Needle
[EMAIL PROTECTED]

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

Re: [sqlite] Multiple connection to in-memory database

[Alex Katebi]

You don't need to open a second connection. The sole connection can be used
from any thread.


On Tue, Aug 19, 2008 at 6:17 PM, vincent cridlig <[EMAIL PROTECTED]> wrote:

> Hi,
>
> I would like to use transactions from separate threads, each thread having
> one connection to a single in-memory db.
>
> I just read in the sqlite doc that in-memory sqlite databases (using
> sqlite3_open(":memory:", ...)) are private to a single connection.
> Is there a way to open a second connection to the same in-memory database
> (for example from a second thread)? Has someone ever tried to do (or
> implement) that?
>
> Any help appreciated.
>
> Thanks
> Vincent
>
>
>
>  _
> Envoyez avec Yahoo! Mail. Une boite mail plus intelligente
> http://mail.yahoo.fr
> ___
> 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] sqlite 4 bytes write

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Julian Qian wrote:
> I would like us to understand the write patterns of Sqlite. 

It looks like the 4 bytes is some sort of header for each database page.
 Your trace seems to be of the Android emulator rather than your
application (there are no syncs or fcntl).

If you want to see why each write happens then attach to the process
using a debugger and put a breakpoint on the relevant VFS write routine
(eg unixWrite for Unix/Linux systems).  Examining the backtrace will
then tell you which code is responsible.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFIq2/OmOOfHg372QQRAh8JAJ4qIC6pVttNXdoykgNmw+jLz7kI0ACfboOZ
DpaOfJx08KVNMRUZSnnx3vE=
=QFie
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] pragma table_info on a table in attached db

[Mrinal Kant]

>Try:
>PRAGMA [database.]table_info(table-name);

>  (  Kees Nuyt

Thankyou Kees for the solution
But, I am facing another problem which is as follows: I open an sqlite
db and create a table "trial". pragma main.table_info(trial) shows
correct info. Then I create a temp table with the same name "trial".
Now, pragma main.table_info(trial) shows the info about the temp
table. pragma temp.table_info(trial) also shows info about the temp
table. How do I get the table_info of the main.trial in this case?

sqlite3.exe trial.sqlite
SQLite version 3.5.2
Enter ".help" for instructions
sqlite> create table trial (col_main);
sqlite> pragma main.table_info(trial);
0|col_main||0||0
sqlite> create temp table trial (col_temp);
sqlite> pragma main.table_info(trial);
0|col_temp||0||0
sqlite> pragma temp.table_info(trial);
0|col_temp||0||0
sqlite>

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

Re: [sqlite] vfs implementation question

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Jeffrey Becker wrote:
> Cool.  I'm actually working on extending Robert Simson's ADO.Net
> provider to allow vfs implementations to be written in managed code.

I'm doing the same in my Python wrapper for SQLite (APSW).  One feature
I have implemented is that the VFS can inherit from an existing one.
That makes it easier for the developer to do things like a VFS that is
like an existing one, but with only specified changes.  For example you
could easily make one that obfuscates the file by xoring the data with a
constant by writing about 10 lines of code.  To make testing easier you
can make an xWrite that will do I/O errors on demand to verify your own
code can handle SQLite telling you there was an I/O error.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFIq2zZmOOfHg372QQRApzOAJ9W2sGJe7iwk3B9XlzuWEJol/FQXgCg48qu
ExBykKM3fDTqrUtwgBSU7zc=
=zx60
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Segmentation fault

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Paul Simon wrote:
> Is this a bug? Or do I need to compile the source differently than what the 
> README file directs?

The chances that you found a bug in SQLite that noone else nor the test
suite nor valgrind has hit is vanishingly small :-)

> sqlite 3.6.1
> mac ox 10.4 power-pc

If you can run Linux then use valgrind (which supports PowerPC
processors as well).  It will tell you precisely what the problem is,
including stack traces.

There is a debugging malloc library on MacOS known as gmalloc (guard
malloc) which may help locate the problem as well, but is nowhere near
as sophisticated or useful as valgrind.

http://developer.apple.com/documentation/Darwin/Reference/ManPages/man3/libgmalloc.3.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFIq2qkmOOfHg372QQRArJeAJ9xNwESakP1000+66OVZYjatrSD/ACbBhMI
2mPhZirGIH74J6D/2JAOnEM=
=87VK
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Correct SQL name resolution on AS clauses in a SELECT?

[Dennis Cote]

D. Richard Hipp wrote:
> Consider a table:
> 
>  CREATE TABLE t1(a INTEGER, b INTEGER);
>  INSERT INTO t1 VALUES(1,2);
>  INSERT INTO t1 VALUES(9,8);
> 
> I am reworking (read: trying to fix) the name resolution algorithm in  
> SQLite and I am wondering what is the correct behavior of the  
> following queries?  Can anybody tell me what PostgreSQL or MySQL or  
> SQL Server or Oracle do with these?
> 
>  SELECT a AS b, b AS a FROM t1 ORDER BY a;
>  SELECT b AS a, a AS b FROM t1 ORDER BY a;
>  SELECT a, b AS a FROM t1 ORDER BY a;
>  SELECT a AS x, b AS x ORDER BY x;
> 
>  SELECT a AS b, b AS a WHERE a=1;
>  SELECT a AS b, b AS a WHERE a=2;
>  SELECT a AS x, b AS x WHERE x=1;
> 

The following are results from PostgreSQL 8.3.3 on the tests. I changed 
the inserted values as suggested by Robert Simpson to better indicate 
the effects of ordering (and changed the a=2 in the subsequent tests to 
a=6 to match the inserted values). I also duplicated the last 4 tests 
with a FROM clause added to indicate the table the columns should be from.

CREATE TABLE t1(a INTEGER, b INTEGER);
INSERT INTO t1 VALUES(1,6);
INSERT INTO t1 VALUES(9,5);

SELECT a AS b, b AS a FROM t1 ORDER BY a;
b;a
9;5
1;6

SELECT b AS a, a AS b FROM t1 ORDER BY a;
a;b
5;9
6;1

SELECT a, b AS a FROM t1 ORDER BY a;
ERROR:  ORDER BY "a" is ambiguous
LINE 1: SELECT a, b AS a FROM t1 ORDER BY a;
   ^

SELECT a AS x, b AS x ORDER BY x;
ERROR:  column "a" does not exist
LINE 1: SELECT a AS x, b AS x ORDER BY x;
^

SELECT a AS b, b AS a WHERE a=1;
ERROR:  column "a" does not exist
LINE 1: SELECT a AS b, b AS a WHERE a=1;
^

SELECT a AS b, b AS a WHERE a=6;
ERROR:  column "a" does not exist
LINE 1: SELECT a AS b, b AS a WHERE a=6;
^

SELECT a AS x, b AS x WHERE x=1;
ERROR:  column "a" does not exist
LINE 1: SELECT a AS x, b AS x WHERE x=1;
^

SELECT a AS x, b AS x FROM t1 ORDER BY x;
ERROR:  ORDER BY "x" is ambiguous
LINE 1: SELECT a AS x, b AS x FROM t1 ORDER BY x;
^

SELECT a AS x, b AS x FROM t1 ORDER BY x;
ERROR:  ORDER BY "x" is ambiguous
LINE 1: SELECT a AS x, b AS x FROM t1 ORDER BY x;
^
SELECT a AS b, b AS a FROM t1 WHERE a=1;
b;a
1;6

SELECT a AS b, b AS a FROM t1 WHERE a=6;
b;a

SELECT a AS x, b AS x FROM t1 WHERE x=1;
ERROR:  column "x" does not exist
LINE 1: SELECT a AS x, b AS x FROM t1 WHERE x=1;
 ^

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

Re: [sqlite] Reducing SQLite Memory footprint(!)

[D. Richard Hipp]

On Aug 19, 2008, at 6:24 PM, Brown, Daniel wrote:

> Good afternoon list,
>
> Are there any known techniques for reducing SQLite's memory footprint?

Construct your database into a file (say "test.db").  Then run the  
sqlite3_analyzer utility (available for download on the SQLite  
website) over that file and post the results.  The sqlite3_analyzer  
utility will give us additional information that might suggest ways of  
reducing the size of the database file.

See also http://www.hwaci.com/sw/sqlite/prosupport.html#compress


D. Richard Hipp
[EMAIL PROTECTED]



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

[sqlite] Reducing SQLite Memory footprint(!)

[Brown, Daniel]

Good afternoon list,

Are there any known techniques for reducing SQLite's memory footprint?
I am currently evaluating SQLite as a possible replacement to our
current proprietary database solution.  However SQLite is very memory
intensive compared to our current solution (although SQLite is faster
and more feature rich), e.g. 9MB for our current solution versus 28 MB
for SQLite with the same source data.  Due to our platforms limitations
we need to be able to run our database solution completely in RAM in our
worst case environment we don't have any writable storage available:
this prevents the use of SQLite's paging facility except for read only
tables.  Our current solution achieves its small memory footprint by bit
packing where we specify the minimum and maximum values for each column
the system then uses the smallest number of bits possible to represent
integer, bit and float values, strings are the exception which we use
UTF-8 to store.  

So is there any existing techniques I can leverage to reduce the memory
footprint for SQLite?  Ideally I'd like to be able to stay inside our
original memory footprint of 9 MB, which I think could be achievable
with a combination of paging our read only tables from disc and keeping
our writable tables 100% in memory with some sort of compression being
applied.

Cheers,

Daniel Brown | Software Engineer
"The best laid schemes o' mice an' men, gang aft agley"


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

Re: [sqlite] vfs implementation question

[Jeffrey Becker]

Cool.  I'm actually working on extending Robert Simson's ADO.Net
provider to allow vfs implementations to be written in managed code.
I hope to get at least two custom vfs implementations into his
codebase, a custom memory backed vfs and a true client-server vfs.

On Tue, Aug 19, 2008 at 5:45 PM, Richard Klein
<[EMAIL PROTECTED]> wrote:
> D. Richard Hipp wrote:
>> On Aug 19, 2008, at 9:02 AM, Jeffrey Becker wrote:
>>
>>
>>> Should the xLock member of sqlite3_io_methods object block until the
>>> lock can be acquired?
>>
>> xLock does not block on any of the built-in VFSes.  But if you want to
>> make your own custom VFS that blocks on locks, I don't  know of any
>> reason why that wouldn't work.
> I wrote a custom VFS that blocks on locks, and it works just fine.
>
> I also implemented a subset of the five locking levels of SQLite:
> just UNLOCKED and EXCLUSIVE.  This means that as soon as a transaction
> asks for a SHARED lock, it actually gets an EXCLUSIVE lock, which
> locks out all other transactions until the first one commits.
>
> This works fine in an embedded application where there are only a
> few threads, whose transactions execute quickly.
>
> - Richard Klein
>
> ___
> 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] Multiple connection to in-memory database

[vincent cridlig]

Hi,

I would like to use transactions from separate threads, each thread having one 
connection to a single in-memory db.

I just read in the sqlite doc that in-memory sqlite databases (using 
sqlite3_open(":memory:", ...)) are private to a single connection.
Is there a way to open a second connection to the same in-memory database (for 
example from a second thread)? Has someone ever tried to do (or implement) that?

Any help appreciated.

Thanks
Vincent


  
_ 
Envoyez avec Yahoo! Mail. Une boite mail plus intelligente http://mail.yahoo.fr
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] vfs implementation question

[Richard Klein]

D. Richard Hipp wrote:
> On Aug 19, 2008, at 9:02 AM, Jeffrey Becker wrote:
>
>   
>> Should the xLock member of sqlite3_io_methods object block until the
>> lock can be acquired?
>
> xLock does not block on any of the built-in VFSes.  But if you want to  
> make your own custom VFS that blocks on locks, I don't  know of any  
> reason why that wouldn't work.
I wrote a custom VFS that blocks on locks, and it works just fine.

I also implemented a subset of the five locking levels of SQLite:
just UNLOCKED and EXCLUSIVE.  This means that as soon as a transaction
asks for a SHARED lock, it actually gets an EXCLUSIVE lock, which
locks out all other transactions until the first one commits.

This works fine in an embedded application where there are only a
few threads, whose transactions execute quickly.

- Richard Klein

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

Re: [sqlite] Correct SQL name resolution on AS clauses in a SELECT?

[Robert Simpson]

Slight change so you can see the order by results more clearly:

(Sql Server 2005)
CREATE TABLE #t1(a int, b int);
INSERT INTO #t1 VALUES(1,6);
INSERT INTO #t1 VALUES(9,5);

SELECT a AS b, b AS a FROM #t1 ORDER BY a;
b   a
--- ---
9   5
1   6


SELECT b AS a, a AS b FROM #t1 ORDER BY a;
a   b
--- ---
5   9
6   1

SELECT a, b AS a FROM #t1 ORDER BY a;
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'a'.

SELECT a AS x, b AS x FROM #t1 ORDER BY x;
Msg 209, Level 16, State 1, Line 2
Ambiguous column name 'x'.

SELECT a AS b, b AS a FROM #t1 WHERE a=1;
b   a
--- ---
1   6

SELECT a AS b, b AS a FROM #t1 WHERE a=6;
b   a
--- ---

SELECT a AS x, b AS x FROM #t1 WHERE x=1;
Msg 207, Level 16, State 1, Line 1
Invalid column name 'x'.




-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp
Sent: Tuesday, August 19, 2008 2:00 PM
To: General Discussion of SQLite Database
Subject: SPAM: [sqlite] Correct SQL name resolution on AS clauses in a
SELECT?

Consider a table:

 CREATE TABLE t1(a INTEGER, b INTEGER);
 INSERT INTO t1 VALUES(1,2);
 INSERT INTO t1 VALUES(9,8);

I am reworking (read: trying to fix) the name resolution algorithm in  
SQLite and I am wondering what is the correct behavior of the  
following queries?  Can anybody tell me what PostgreSQL or MySQL or  
SQL Server or Oracle do with these?

 SELECT a AS b, b AS a FROM t1 ORDER BY a;
 SELECT b AS a, a AS b FROM t1 ORDER BY a;
 SELECT a, b AS a FROM t1 ORDER BY a;
 SELECT a AS x, b AS x ORDER BY x;

 SELECT a AS b, b AS a WHERE a=1;
 SELECT a AS b, b AS a WHERE a=2;
 SELECT a AS x, b AS x WHERE x=1;




D. Richard Hipp
[EMAIL PROTECTED]



___
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] SPAM: Correct SQL name resolution on AS clauses in a SELECT?

[Robert Simpson]

Sql Server 2005:

SELECT a AS b, b AS a FROM #t1 ORDER BY a;
b   a
--- ---
1   2
9   8


SELECT b AS a, a AS b FROM #t1 ORDER BY a;
a   b
--- ---
2   1
8   9

SELECT a, b AS a FROM #t1 ORDER BY a;
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'a'.

SELECT a AS x, b AS x FROM #t1 ORDER BY x;
Msg 209, Level 16, State 1, Line 2
Ambiguous column name 'x'.

SELECT a AS b, b AS a FROM #t1 WHERE a=1;
b   a
--- ---
1   2

SELECT a AS b, b AS a FROM #t1 WHERE a=2;
b   a
--- ---

SELECT a AS x, b AS x FROM #t1 WHERE x=1;
Msg 207, Level 16, State 1, Line 1
Invalid column name 'x'.



-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp
Sent: Tuesday, August 19, 2008 2:00 PM
To: General Discussion of SQLite Database
Subject: SPAM: [sqlite] Correct SQL name resolution on AS clauses in a
SELECT?

Consider a table:

 CREATE TABLE t1(a INTEGER, b INTEGER);
 INSERT INTO t1 VALUES(1,2);
 INSERT INTO t1 VALUES(9,8);

I am reworking (read: trying to fix) the name resolution algorithm in  
SQLite and I am wondering what is the correct behavior of the  
following queries?  Can anybody tell me what PostgreSQL or MySQL or  
SQL Server or Oracle do with these?

 SELECT a AS b, b AS a FROM t1 ORDER BY a;
 SELECT b AS a, a AS b FROM t1 ORDER BY a;
 SELECT a, b AS a FROM t1 ORDER BY a;
 SELECT a AS x, b AS x ORDER BY x;

 SELECT a AS b, b AS a WHERE a=1;
 SELECT a AS b, b AS a WHERE a=2;
 SELECT a AS x, b AS x WHERE x=1;




D. Richard Hipp
[EMAIL PROTECTED]



___
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] Correct SQL name resolution on AS clauses in a SELECT?

[D. Richard Hipp]

Consider a table:

 CREATE TABLE t1(a INTEGER, b INTEGER);
 INSERT INTO t1 VALUES(1,2);
 INSERT INTO t1 VALUES(9,8);

I am reworking (read: trying to fix) the name resolution algorithm in  
SQLite and I am wondering what is the correct behavior of the  
following queries?  Can anybody tell me what PostgreSQL or MySQL or  
SQL Server or Oracle do with these?

 SELECT a AS b, b AS a FROM t1 ORDER BY a;
 SELECT b AS a, a AS b FROM t1 ORDER BY a;
 SELECT a, b AS a FROM t1 ORDER BY a;
 SELECT a AS x, b AS x ORDER BY x;

 SELECT a AS b, b AS a WHERE a=1;
 SELECT a AS b, b AS a WHERE a=2;
 SELECT a AS x, b AS x WHERE x=1;




D. Richard Hipp
[EMAIL PROTECTED]



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

[sqlite] .import with .separator and quoted strings

[Wilson, Ron P]

I'm trying to import a table using the command line tool.

 

sqlite> .separator ,

sqlite> .import export.csv library

export.csv line 1: expected 53 columns of data but found 77

 

sqlite> .mode csv

sqlite> .import export.csv library

export.csv line 1: expected 53 columns of data but found 77

 

All entries are quoted strings, but some of them have commas within the
strings.  It appears that SQLite is ignoring the string quoting and
taking all commas literally.  Is this intended?  The same import works
fine in Excel with 53 columns resulting.  I have also tried tab
delimited and apparently some of the strings in this dataset also
contain tabs.

 

sqlite> .mode tabs

sqlite> .import export.txt library

export.txt line 162: expected 53 columns of data but found 55

 

I don't control the data source, and I would really like to avoid
pre-munging the data.

 

RW

 

sqlite>select level from sqlGuruOMeter where name="Ron Wilson";
2

 

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

[sqlite] sqlite 4 bytes write

[Julian Qian]

I would like us to understand the write patterns of Sqlite. . That
write pattern happens first to the journal then to the database. I
found a lot of 4 byte writes.

What are these 4 byte writes and can the be avoided? I would also like
data on the write patterns for a 1k,2k,128k, and 256k writes to a
database. Is there some flags we can set in Sqlite to less these
writes?

below is the write log of 3.5.9
[pid  3452] lseek(54, 0, SEEK_SET)  = 0
[pid  3452] write(54, "??\5?
?c?\0\0\0\0iܷ?\0\0\4?\0\0\2\0\0\0\10\0ttac"..., 512) = 512
[pid  3452] lseek(54, 512, SEEK_SET)= 512
[pid  3452] write(54, "\0\0\0\7", 4)= 4
[pid  3452] lseek(54, 516, SEEK_SET)= 516
[pid  3452] write(54,
"\n\0\0\0\22\6K\0\7\227\7q\7E\6?\7?\6?\7?\6?\7?\7$\6K\6"..., 1024) =
1024
[pid  3452] lseek(54, 2564, SEEK_SET)   = 2564
[pid  3452] write(54, "iܸ?", 4)= 4
[pid  3452] lseek(54, 2568, SEEK_SET)   = 2568
[pid  3452] write(54, "\0\0\0\6", 4)= 4
[pid  3452] lseek(54, 2572, SEEK_SET)   = 2572
[pid  3452] write(54,
"\r\7\212\0\22\4C\2\7?\7r\0070\6?\6?\6\234\6l\7`\6Q\4?\7"..., 1024) =
1024
[pid  3452] lseek(54, 4620, SEEK_SET)   = 4620
[pid  3452] write(54, "iܹ=", 4)= 4
[pid  3452] lseek(54, 4624, SEEK_SET)   = 4624
[pid  3452] write(54, "\0\0\0\1", 4)= 4
[pid  3452] lseek(54, 4628, SEEK_SET)   = 4628
[pid  3452] write(54, "SQLite format 3\0\10\0\1\1\0@
\0\0\6\r\0\0\0\0"..., 1024) = 1024
[pid  3452] lseek(54, 6676, SEEK_SET)   = 6676
[pid  3452] write(54, "iܷ?", 4)= 4
[pid  3452] close(55)   = 0
[pid  3452] lseek(54, 8, SEEK_SET)  = 8
[pid  3452] write(54, "\0\0\0\3", 4)= 4
[pid  3452] lseek(29, 0, SEEK_SET)  = 0
[pid  3452] write(29, "SQLite format 3\0\10\0\1\1\0@
\0\0\6\16\0\0\0\0"..., 1024) = 1024
[pid  3452] lseek(29, 10240, SEEK_SET)  = 10240
[pid  3452] write(29,
"\r\7\212\0\22\4C\2\7?\7r\0070\6?\6?\6\234\6l\7`\6Q\4?\7"..., 1024) =
1024
[pid  3452] lseek(29, 12288, SEEK_SET)  = 12288
[pid  3452] write(29,
"\n\0\0\0\22\6K\0\7\227\7q\7E\6?\7?\6?\7?\6?\7?\7$\6K\6"..., 1024) =
1024
[pid  3452] close(54)   = 0
[pid  3452] lseek(29, 24, SEEK_SET) = 24

for 3.6:
[pid   211] 
open("/data/data/com.google.android.providers.gmail/databases/[EMAIL 
PROTECTED]",
O_RDWR|O_CREAT|O_EXCL|O_LARGEFILE|O_NOFOLLOW, 0644) = 55
[pid   211] open("/data/data/com.google.android.providers.gmail/databases",
O_RDONLY|O_LARGEFILE) = 56
[pid   211] lseek(55, 0, SEEK_SET)  = 0
[pid   211] write(55, "ÙÕ\5ù
¡c×\0\0\0\0\221FßÈ\0\0\2~\0\0\2\0\0\0\4\0\0\0\0\0"..., 512) = 512
[pid   211] open("/dev/ashmem", O_RDWR|O_LARGEFILE) = 58
[pid   211] close(58)   = 0
[pid   211] open("/dev/ashmem", O_RDWR|O_LARGEFILE) = 58
[pid   211] close(58)   = 0
[pid   211] open("/dev/ashmem", O_RDWR|O_LARGEFILE) = 58
[pid   211] close(58)   = 0
[pid   211] writev(3, [{"\3", 1}, {"gmail-ls\0", 9}, {"Starting
purging messages. Oldes"..., 78}], 3) = 88
[pid   211] open("/dev/ashmem", O_RDWR|O_LARGEFILE) = 58
[pid   211] writev(3, [{"\3", 1}, {"gmail-ls\0", 9}, {"Finished
purging messages \0", 27}], 3) = 37
[pid   211] close(58)   = 0
[pid   211] close(56)   = 0
[pid   211] close(55)   = 0
[pid   211] lseek(39, 24, SEEK_SET) = 24
[pid   211] open("/dev/ashmem", O_RDWR|O_LARGEFILE) = 55
[pid   211] lseek(39, 24, SEEK_SET) = 24
[pid   211] close(55)   = 0
[pid   211] writev(3, [{"\3", 1}, {"gmail-ls\0", 9}, {"considering
sending notification"..., 102}], 3) = 112
[pid   211] lseek(39, 24, SEEK_SET) = 24
[pid   211] 
open("/data/data/com.google.android.providers.gmail/databases/[EMAIL 
PROTECTED]",
O_RDWR|O_CREAT|O_EXCL|O_LARGEFILE|O_NOFOLLOW, 0644) = 55
[pid   211] open("/data/data/com.google.android.providers.gmail/databases",
O_RDONLY|O_LARGEFILE) = 56
[pid   211] lseek(55, 0, SEEK_SET)  = 0
[pid   211] write(55, "ÙÕ\5ù
¡c×\0\0\0\0É\25á?\0\0\2~\0\0\2\0\0\0\4\0\0\0\0\0"..., 512) = 512
[pid   211] lseek(55, 512, SEEK_SET)= 512
[pid   211] write(55, "\0\0\0\17", 4)   = 4
[pid   211] lseek(55, 516, SEEK_SET)= 516
[pid   211] write(55,
"\n\0\0\0\2\3Å\0\3â\3Å\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 1024) =
1024
[pid   211] lseek(55, 1540, SEEK_SET)   = 1540
[pid   211] write(55, "É\25á?", 4)  = 4
[pid   211] lseek(55, 1544, SEEK_SET)   = 1544
[pid   211] write(55, "\0\0\0\16", 4)   = 4
[pid   211] lseek(55, 1548, SEEK_SET)   = 1548
[pid   211] write(55,
"\r\0\0\0\2\3¾\0\3à\3¾\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 1024) =
1024
[pid   211] lseek(55, 2572, SEEK_SET)   = 2572
[pid   211] write(55, "É\25á?", 4)  = 4
[pid   211] lseek(55, 2576, SEEK_SET)   = 2576
[pid   211] write(55, "\0\0\0\1", 4)= 4
[pid   211] lseek(55, 2580, SEEK_SET)   = 2580
[pid   211] write(55, "SQLite format 3\0\4\0\1\1\0@
\0\0\34t\0\0\0\0"..., 1024) = 1024
[pid   211] lseek(55, 3604, SEEK_SET)   = 3604
[pid   211] write(55, "É\25á?", 4)  = 4
[pid   211] close(56)   = 0

Re: [sqlite] rtree performance problems?

[Hartwig Wiesmann]

Am 18.08.2008 um 21:32 schrieb Dennis Cote:

> Thomas Sailer wrote:
>>
>> Interestingly, the original query is extremely compute-bound, there  
>> is
>> almost no disk activity!
>>
>> Looking at the output opcodes from the queries, I can't see any
>> significant difference. Though I have to admit I'm by far no expert  
>> in
>> vmdb opcodes...
>>
>
> You can use "explain query plan " to get some clues as to
> what is happening, but the vdbe code shows it best.
>
> The original query is doing a full table scan through all 2.6 million
> records in the main table, and for each record it uses the rtree to
> locate the 20 records inside the rectangle set by your limits, then it
> compares the id of each of these records to see if it matches the id  
> of
> the main table record. For each match it dumps all the data in both
> records.
>
> The second query use the rtree index to locate the 20 records in the
> limit rectangle, and then uses the btree index to locate those 20
> records quickly, and then dumps the data for that record.
>
> HTH
> Dennis Cote
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
Hello,

I had an identical experience a few days ago and posted it under a  
different topic. Because SQLite is not doing well as mentioned in the  
example of the documentation I suggest to change the documentation  
using the proposed solution. I also had to find it out the hard way  
and I think by modifying the documentation there are at least a few  
people less who have to go that way.

BTW: I suppose the same problem occurs when using fts?

Hartwig


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

Re: [sqlite] 2 Questions from a newbie

[Dennis Cote]

cstrader wrote:
> OK, this seems really helpful, but I'm afraid you've lost me a bit.
> 
> 1.  Third normal??? :)

See http://en.wikipedia.org/wiki/Third_normal_form for an explanation.

> 
> 2.  Your idea would be to have one table that's relatively static, with the 
> names (and perhaps other data) of the stocks, a 2nd table that holds prices? 
> I'm not sure I see the advantage of this approach.
> 

The advantage is that it gives you uniform access to all your data in 
the same way. It can easily be filtered to get the data for an 
individual stock, or the data over a specified range of time, etc.


> 3.  What's "Instance DateTime?"
> 

The timestamp of the price quote.

> 4.  Are you saying that I could use "GROUP BY" to get daily, monthly, 
> yearly, etc?
> 

No, you would use a range condition like this

   select ...
   from Price
   where stockID = :id
   and instance between '2008-08-01 00:00:00' and datetime('now')
   order by instance;

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

Re: [sqlite] 2 Questions from a newbie

[Fred Williams]

1.  Third Normal, as in "Database normalization."  (Database 101)  No
offence intended, you said you are a beginner.

2.  Right.  My "unlimited" stock and prices advantage.  No creating a table
for each new stock.  No creating a table or adding columns (Both time and
logically intensive.) for each new stock or price.  And just generally
FLEXIBILITY without extreme pain!

3.  The date and time the price was quoted.

4.  Exactly.

A good book:  Oracle,  "Case Method, Entity Relationship Modeling."

Good luck!

Fred

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of cstrader
Sent: Tuesday, August 19, 2008 12:24 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] 2 Questions from a newbie


OK, this seems really helpful, but I'm afraid you've lost me a bit.

1.  Third normal??? :)

2.  Your idea would be to have one table that's relatively static, with the
names (and perhaps other data) of the stocks, a 2nd table that holds prices?
I'm not sure I see the advantage of this approach.

3.  What's "Instance DateTime?"

4.  Are you saying that I could use "GROUP BY" to get daily, monthly,
yearly, etc?

Thanks, very helpful...just what I'm needing!


> Third normal says:
>
> Table:  Stock
> ID Integer Primary Key,
> Symbol Varchar(n),
> ... Other stuff 
>
> Table: DaylyPrice (I'd call it "Price")
> StockID Integer  (Foreign key: Stock:ID)
> Instance DateTime
> Price Integer (Price times 100/1000/1, Your choice)
>
> Then you can have virtually unlimited stocks with virtually unlimited
> prices.  Price sampling can be done at any rate (i.e. seconds, hourly,
> daily, weekly, etc.)  Your queries would determine "closing price" hourly,
> dally, monthly, etc.
>
> Thoughts to ponder:  How to handle stock "splits", Mergers, and ?
>
> Fred
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Behalf Of cstrader
> Sent: Tuesday, August 19, 2008 11:53 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] 2 Questions from a newbie
>
>
> OK, cool idea... let me try that.
>
>
> - Original Message -
> From: "Jeff Hamilton" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>; "General Discussion of SQLite Database"
> 
> Sent: Tuesday, August 19, 2008 12:51 PM
> Subject: Re: [sqlite] 2 Questions from a newbie
>
>
>> If all 2,400 tables have the same 9 columns you could try using a
>> single table and adding a column for the ticker of the stock and then
>> add an index to that column to allow quick lookups based on the
>> ticker.
>>
>> -Jeff
>>
>> On Tue, Aug 19, 2008 at 9:44 AM, .:UgumugU:. <[EMAIL PROTECTED]> wrote:
>>> Hi cstrader,
>>>
>>> Just send some table structure and the explain what you are trying todo.
>>> In my opinion it is not a good way to deal with 2400 tables :)
>>>
>>> ugumugu
>>>
>>> cstrader yazm?s,:
 I'm just starting with SQLite (from vb.net) and could use some advice.

 I have a set of 2400 (could get substantially higher) tables in a
 single
 database (each table contains daily stock prices for a different
 stock).
 Each table has 9 columns (all text for now) and some several thousand
 rows.
 So far so good.

 I need to read those tables sequentially and operate on them.  It takes
 about 18 seconds to open each of  the tables in sequence.  (i.e. loop
 through 2400 tables with a select command opening each one)

 First question... does that sound about right in terms of speed?  Is
 there a
 way to store the data that might be faster?  The data are primarily
 single
 precision -- I'm using text format, but perhaps that's not best?

 Second question:

 When I open each table I need to add some blank columns to it.  So I
 include
 some "0 as NewCol" lines to create the new columns with initial 0s.

 However, adding 20 new columns in this manner increases the total time
 for
 the loop from 19 seconds to  49 seconds.  This seems like a long time.
 Is
 there a faster way to get these empty columns in?

 More questions later I'm sure...

 Thanks in advance

 cs

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


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

[sqlite] Segmentation fault

[Paul Simon]

Hi List, I get the following segmentation fault whenever I exit sqlite3. The 
segmentation fault does not seem to happen If I write something to the database 
then exit.

sqlite3(27109,0xa000ed88) malloc: *** error for object 0x1806c00: incorrect 
checksum for freed object - object was probably modified after being freed, 
break at szone_error to debug
sqlite3(27109,0xa000ed88) malloc: *** set a breakpoint in szone_error to debug
Segmentation fault

Is this a bug? Or do I need to compile the source differently than what the 
README file directs?

sqlite 3.6.1
mac ox 10.4 power-pc

Thanks,
Paul

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

Re: [sqlite] 2 Questions from a newbie

[cstrader]

OK, this seems really helpful, but I'm afraid you've lost me a bit.

1.  Third normal??? :)

2.  Your idea would be to have one table that's relatively static, with the 
names (and perhaps other data) of the stocks, a 2nd table that holds prices? 
I'm not sure I see the advantage of this approach.

3.  What's "Instance DateTime?"

4.  Are you saying that I could use "GROUP BY" to get daily, monthly, 
yearly, etc?

Thanks, very helpful...just what I'm needing!


> Third normal says:
>
> Table:  Stock
> ID Integer Primary Key,
> Symbol Varchar(n),
> ... Other stuff 
>
> Table: DaylyPrice (I'd call it "Price")
> StockID Integer  (Foreign key: Stock:ID)
> Instance DateTime
> Price Integer (Price times 100/1000/1, Your choice)
>
> Then you can have virtually unlimited stocks with virtually unlimited
> prices.  Price sampling can be done at any rate (i.e. seconds, hourly,
> daily, weekly, etc.)  Your queries would determine "closing price" hourly,
> dally, monthly, etc.
>
> Thoughts to ponder:  How to handle stock "splits", Mergers, and ?
>
> Fred
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Behalf Of cstrader
> Sent: Tuesday, August 19, 2008 11:53 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] 2 Questions from a newbie
>
>
> OK, cool idea... let me try that.
>
>
> - Original Message -
> From: "Jeff Hamilton" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>; "General Discussion of SQLite Database"
> 
> Sent: Tuesday, August 19, 2008 12:51 PM
> Subject: Re: [sqlite] 2 Questions from a newbie
>
>
>> If all 2,400 tables have the same 9 columns you could try using a
>> single table and adding a column for the ticker of the stock and then
>> add an index to that column to allow quick lookups based on the
>> ticker.
>>
>> -Jeff
>>
>> On Tue, Aug 19, 2008 at 9:44 AM, .:UgumugU:. <[EMAIL PROTECTED]> wrote:
>>> Hi cstrader,
>>>
>>> Just send some table structure and the explain what you are trying todo.
>>> In my opinion it is not a good way to deal with 2400 tables :)
>>>
>>> ugumugu
>>>
>>> cstrader yazm?s,:
 I'm just starting with SQLite (from vb.net) and could use some advice.

 I have a set of 2400 (could get substantially higher) tables in a 
 single
 database (each table contains daily stock prices for a different 
 stock).
 Each table has 9 columns (all text for now) and some several thousand
 rows.
 So far so good.

 I need to read those tables sequentially and operate on them.  It takes
 about 18 seconds to open each of  the tables in sequence.  (i.e. loop
 through 2400 tables with a select command opening each one)

 First question... does that sound about right in terms of speed?  Is
 there a
 way to store the data that might be faster?  The data are primarily
 single
 precision -- I'm using text format, but perhaps that's not best?

 Second question:

 When I open each table I need to add some blank columns to it.  So I
 include
 some "0 as NewCol" lines to create the new columns with initial 0s.

 However, adding 20 new columns in this manner increases the total time
 for
 the loop from 19 seconds to  49 seconds.  This seems like a long time.
 Is
 there a faster way to get these empty columns in?

 More questions later I'm sure...

 Thanks in advance

 cs

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


>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> 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] 2 Questions from a newbie

[Fred Williams]

Third normal says:

Table:  Stock
ID  Integer Primary Key,
Symbol  Varchar(n),
... Other stuff 

Table: DaylyPrice (I'd call it "Price")
StockID Integer  (Foreign key: Stock:ID)
InstanceDateTime
Price   Integer (Price times 100/1000/1, 
Your choice)

Then you can have virtually unlimited stocks with virtually unlimited
prices.  Price sampling can be done at any rate (i.e. seconds, hourly,
daily, weekly, etc.)  Your queries would determine "closing price" hourly,
dally, monthly, etc.

Thoughts to ponder:  How to handle stock "splits", Mergers, and ?

Fred

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of cstrader
Sent: Tuesday, August 19, 2008 11:53 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] 2 Questions from a newbie


OK, cool idea... let me try that.


- Original Message -
From: "Jeff Hamilton" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; "General Discussion of SQLite Database"

Sent: Tuesday, August 19, 2008 12:51 PM
Subject: Re: [sqlite] 2 Questions from a newbie


> If all 2,400 tables have the same 9 columns you could try using a
> single table and adding a column for the ticker of the stock and then
> add an index to that column to allow quick lookups based on the
> ticker.
>
> -Jeff
>
> On Tue, Aug 19, 2008 at 9:44 AM, .:UgumugU:. <[EMAIL PROTECTED]> wrote:
>> Hi cstrader,
>>
>> Just send some table structure and the explain what you are trying todo.
>> In my opinion it is not a good way to deal with 2400 tables :)
>>
>> ugumugu
>>
>> cstrader yazm?s,:
>>> I'm just starting with SQLite (from vb.net) and could use some advice.
>>>
>>> I have a set of 2400 (could get substantially higher) tables in a single
>>> database (each table contains daily stock prices for a different stock).
>>> Each table has 9 columns (all text for now) and some several thousand
>>> rows.
>>> So far so good.
>>>
>>> I need to read those tables sequentially and operate on them.  It takes
>>> about 18 seconds to open each of  the tables in sequence.  (i.e. loop
>>> through 2400 tables with a select command opening each one)
>>>
>>> First question... does that sound about right in terms of speed?  Is
>>> there a
>>> way to store the data that might be faster?  The data are primarily
>>> single
>>> precision -- I'm using text format, but perhaps that's not best?
>>>
>>> Second question:
>>>
>>> When I open each table I need to add some blank columns to it.  So I
>>> include
>>> some "0 as NewCol" lines to create the new columns with initial 0s.
>>>
>>> However, adding 20 new columns in this manner increases the total time
>>> for
>>> the loop from 19 seconds to  49 seconds.  This seems like a long time.
>>> Is
>>> there a faster way to get these empty columns in?
>>>
>>> More questions later I'm sure...
>>>
>>> Thanks in advance
>>>
>>> cs
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

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

Re: [sqlite] 2 Questions from a newbie

[cstrader]

Well I need to have the new columns for subsequent processing of the data. 
I have some complex functions that I need to use to modify the existing data 
into the new columns.  I don't think I can do that with user-defined 
functions within the select command itself.  Does that make any sense?


- Original Message - 
From: "Greg Morphis" <[EMAIL PROTECTED]>
To: "General Discussion of SQLite Database" 
Sent: Tuesday, August 19, 2008 1:01 PM
Subject: Re: [sqlite] 2 Questions from a newbie


> Also why are you adding new columns? doesn't sound like you have the
> db set up properly..
>
> On Tue, Aug 19, 2008 at 11:53 AM, cstrader <[EMAIL PROTECTED]> 
> wrote:
>> OK, cool idea... let me try that.
>>
>>
>> - Original Message -
>> From: "Jeff Hamilton" <[EMAIL PROTECTED]>
>> To: <[EMAIL PROTECTED]>; "General Discussion of SQLite Database"
>> 
>> Sent: Tuesday, August 19, 2008 12:51 PM
>> Subject: Re: [sqlite] 2 Questions from a newbie
>>
>>
>>> If all 2,400 tables have the same 9 columns you could try using a
>>> single table and adding a column for the ticker of the stock and then
>>> add an index to that column to allow quick lookups based on the
>>> ticker.
>>>
>>> -Jeff
>>>
>>> On Tue, Aug 19, 2008 at 9:44 AM, .:UgumugU:. <[EMAIL PROTECTED]> wrote:
 Hi cstrader,

 Just send some table structure and the explain what you are trying 
 todo.
 In my opinion it is not a good way to deal with 2400 tables :)

 ugumugu

 cstrader yazm?s,:
> I'm just starting with SQLite (from vb.net) and could use some advice.
>
> I have a set of 2400 (could get substantially higher) tables in a 
> single
> database (each table contains daily stock prices for a different 
> stock).
> Each table has 9 columns (all text for now) and some several thousand
> rows.
> So far so good.
>
> I need to read those tables sequentially and operate on them.  It 
> takes
> about 18 seconds to open each of  the tables in sequence.  (i.e. loop
> through 2400 tables with a select command opening each one)
>
> First question... does that sound about right in terms of speed?  Is
> there a
> way to store the data that might be faster?  The data are primarily
> single
> precision -- I'm using text format, but perhaps that's not best?
>
> Second question:
>
> When I open each table I need to add some blank columns to it.  So I
> include
> some "0 as NewCol" lines to create the new columns with initial 0s.
>
> However, adding 20 new columns in this manner increases the total time
> for
> the loop from 19 seconds to  49 seconds.  This seems like a long time.
> Is
> there a faster way to get these empty columns in?
>
> More questions later I'm sure...
>
> Thanks in advance
>
> cs
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>

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

>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> 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] 2 Questions from a newbie

[Greg Morphis]

Also why are you adding new columns? doesn't sound like you have the
db set up properly..

On Tue, Aug 19, 2008 at 11:53 AM, cstrader <[EMAIL PROTECTED]> wrote:
> OK, cool idea... let me try that.
>
>
> - Original Message -
> From: "Jeff Hamilton" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>; "General Discussion of SQLite Database"
> 
> Sent: Tuesday, August 19, 2008 12:51 PM
> Subject: Re: [sqlite] 2 Questions from a newbie
>
>
>> If all 2,400 tables have the same 9 columns you could try using a
>> single table and adding a column for the ticker of the stock and then
>> add an index to that column to allow quick lookups based on the
>> ticker.
>>
>> -Jeff
>>
>> On Tue, Aug 19, 2008 at 9:44 AM, .:UgumugU:. <[EMAIL PROTECTED]> wrote:
>>> Hi cstrader,
>>>
>>> Just send some table structure and the explain what you are trying todo.
>>> In my opinion it is not a good way to deal with 2400 tables :)
>>>
>>> ugumugu
>>>
>>> cstrader yazm?s,:
 I'm just starting with SQLite (from vb.net) and could use some advice.

 I have a set of 2400 (could get substantially higher) tables in a single
 database (each table contains daily stock prices for a different stock).
 Each table has 9 columns (all text for now) and some several thousand
 rows.
 So far so good.

 I need to read those tables sequentially and operate on them.  It takes
 about 18 seconds to open each of  the tables in sequence.  (i.e. loop
 through 2400 tables with a select command opening each one)

 First question... does that sound about right in terms of speed?  Is
 there a
 way to store the data that might be faster?  The data are primarily
 single
 precision -- I'm using text format, but perhaps that's not best?

 Second question:

 When I open each table I need to add some blank columns to it.  So I
 include
 some "0 as NewCol" lines to create the new columns with initial 0s.

 However, adding 20 new columns in this manner increases the total time
 for
 the loop from 19 seconds to  49 seconds.  This seems like a long time.
 Is
 there a faster way to get these empty columns in?

 More questions later I'm sure...

 Thanks in advance

 cs

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


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

Re: [sqlite] 2 Questions from a newbie

[cstrader]

OK, cool idea... let me try that.


- Original Message - 
From: "Jeff Hamilton" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; "General Discussion of SQLite Database" 

Sent: Tuesday, August 19, 2008 12:51 PM
Subject: Re: [sqlite] 2 Questions from a newbie


> If all 2,400 tables have the same 9 columns you could try using a
> single table and adding a column for the ticker of the stock and then
> add an index to that column to allow quick lookups based on the
> ticker.
>
> -Jeff
>
> On Tue, Aug 19, 2008 at 9:44 AM, .:UgumugU:. <[EMAIL PROTECTED]> wrote:
>> Hi cstrader,
>>
>> Just send some table structure and the explain what you are trying todo.
>> In my opinion it is not a good way to deal with 2400 tables :)
>>
>> ugumugu
>>
>> cstrader yazm?s,:
>>> I'm just starting with SQLite (from vb.net) and could use some advice.
>>>
>>> I have a set of 2400 (could get substantially higher) tables in a single
>>> database (each table contains daily stock prices for a different stock).
>>> Each table has 9 columns (all text for now) and some several thousand 
>>> rows.
>>> So far so good.
>>>
>>> I need to read those tables sequentially and operate on them.  It takes
>>> about 18 seconds to open each of  the tables in sequence.  (i.e. loop
>>> through 2400 tables with a select command opening each one)
>>>
>>> First question... does that sound about right in terms of speed?  Is 
>>> there a
>>> way to store the data that might be faster?  The data are primarily 
>>> single
>>> precision -- I'm using text format, but perhaps that's not best?
>>>
>>> Second question:
>>>
>>> When I open each table I need to add some blank columns to it.  So I 
>>> include
>>> some "0 as NewCol" lines to create the new columns with initial 0s.
>>>
>>> However, adding 20 new columns in this manner increases the total time 
>>> for
>>> the loop from 19 seconds to  49 seconds.  This seems like a long time. 
>>> Is
>>> there a faster way to get these empty columns in?
>>>
>>> More questions later I'm sure...
>>>
>>> Thanks in advance
>>>
>>> cs
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 

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

Re: [sqlite] 2 Questions from a newbie

[Jeff Hamilton]

If all 2,400 tables have the same 9 columns you could try using a
single table and adding a column for the ticker of the stock and then
add an index to that column to allow quick lookups based on the
ticker.

-Jeff

On Tue, Aug 19, 2008 at 9:44 AM, .:UgumugU:. <[EMAIL PROTECTED]> wrote:
> Hi cstrader,
>
> Just send some table structure and the explain what you are trying todo.
> In my opinion it is not a good way to deal with 2400 tables :)
>
> ugumugu
>
> cstrader yazm?s,:
>> I'm just starting with SQLite (from vb.net) and could use some advice.
>>
>> I have a set of 2400 (could get substantially higher) tables in a single
>> database (each table contains daily stock prices for a different stock).
>> Each table has 9 columns (all text for now) and some several thousand rows.
>> So far so good.
>>
>> I need to read those tables sequentially and operate on them.  It takes
>> about 18 seconds to open each of  the tables in sequence.  (i.e. loop
>> through 2400 tables with a select command opening each one)
>>
>> First question... does that sound about right in terms of speed?  Is there a
>> way to store the data that might be faster?  The data are primarily single
>> precision -- I'm using text format, but perhaps that's not best?
>>
>> Second question:
>>
>> When I open each table I need to add some blank columns to it.  So I include
>> some "0 as NewCol" lines to create the new columns with initial 0s.
>>
>> However, adding 20 new columns in this manner increases the total time for
>> the loop from 19 seconds to  49 seconds.  This seems like a long time.  Is
>> there a faster way to get these empty columns in?
>>
>> More questions later I'm sure...
>>
>> Thanks in advance
>>
>> cs
>>
>> ___
>> 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] 2 Questions from a newbie

[.:UgumugU:.]

Hi cstrader,

Just send some table structure and the explain what you are trying todo. 
In my opinion it is not a good way to deal with 2400 tables :)

ugumugu

cstrader yazm?s,:
> I'm just starting with SQLite (from vb.net) and could use some advice.
>
> I have a set of 2400 (could get substantially higher) tables in a single
> database (each table contains daily stock prices for a different stock).
> Each table has 9 columns (all text for now) and some several thousand rows.
> So far so good.
>
> I need to read those tables sequentially and operate on them.  It takes
> about 18 seconds to open each of  the tables in sequence.  (i.e. loop
> through 2400 tables with a select command opening each one)
>
> First question... does that sound about right in terms of speed?  Is there a
> way to store the data that might be faster?  The data are primarily single
> precision -- I'm using text format, but perhaps that's not best?
>
> Second question:
>
> When I open each table I need to add some blank columns to it.  So I include
> some "0 as NewCol" lines to create the new columns with initial 0s.
>
> However, adding 20 new columns in this manner increases the total time for
> the loop from 19 seconds to  49 seconds.  This seems like a long time.  Is
> there a faster way to get these empty columns in?
>
> More questions later I'm sure...
>
> Thanks in advance
>
> cs
>
> ___
> 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] 2 Questions from a newbie

[cstrader]

I'm just starting with SQLite (from vb.net) and could use some advice.

I have a set of 2400 (could get substantially higher) tables in a single
database (each table contains daily stock prices for a different stock).
Each table has 9 columns (all text for now) and some several thousand rows.
So far so good.

I need to read those tables sequentially and operate on them.  It takes
about 18 seconds to open each of  the tables in sequence.  (i.e. loop
through 2400 tables with a select command opening each one)

First question... does that sound about right in terms of speed?  Is there a
way to store the data that might be faster?  The data are primarily single
precision -- I'm using text format, but perhaps that's not best?

Second question:

When I open each table I need to add some blank columns to it.  So I include
some "0 as NewCol" lines to create the new columns with initial 0s.

However, adding 20 new columns in this manner increases the total time for
the loop from 19 seconds to  49 seconds.  This seems like a long time.  Is
there a faster way to get these empty columns in?

More questions later I'm sure...

Thanks in advance

cs

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

[sqlite] Detecting DB Integrity and Recovery

[Imran Habib]

I am currently writing a method that detects that database is corrupt or not
and return SQLITE_OK if OK otherwise an error.

If I detect an error then I delete the existing database file.and then
Create the new database file with the same name and after that populate the
database file with the default data or some known data. I know it is quite
bad approach.

The C language code of the method is like this.


int Verify_DB_integrity(sqlite3 *db_Handle)
{

   int  rVal = SQLITE_OK;
   char errMsg[400];
   sqlite3_stmt *pStmt;

   rVal = sqlite3_prepare(db_Handle, "PRAGMA integrity_check", sizeof(PRAGMA
integrity_check),
, NU_NULL);

   if((rVal != SQLITE_OK) || (pStmt == NU_NULL))
   {
   return rVal;
   }

   rVal = sqlite3_step(pStmt);

   if (rVal == SQLITE_ROW)
   {

   strcpy(errMsg,(const char *) sqlite3_column_text(pStmt,0));

   rVal = sqlite3_finalize(pStmt);

   if((rVal != SQLITE_OK))
   {
   return rVal;
   }

   if (strcmp(errMsg,"ok") == 0)
   {
   return SQLITE_OK;
   }

   else
   {
   rVal  =   sqlite3_exec(Db_Handle, "VACUM",  0, NU_NULL, NU_NULL);
   if((rVal != SQLITE_OK) || (pStmt == NU_NULL))
   {
   return rVal;
   }

   rVal = sqlite3_prepare(Db_Handle, "PRAGMA integrity_check",
sizeof(PRAGMA integrity_check),
, NU_NULL);
   if((rVal != SQLITE_OK) || (pStmt == NU_NULL))
   {
   return rVal;
   }

  rVal = sqlite3_step(pStmt);
  if (rVal == SQLITE_ROW)
  {
   strcpy(errMsg,(const char *) sqlite3_column_text(pStmt,0));

   rVal = sqlite3_finalize(pStmt);

   if((rVal != SQLITE_OK))
   {
   return rVal;
   }

   if (strcmp(errMsg,"ok") == 0)
   {
   return SQLITE_OK;
   }
   }

}
else
{

   return rVal;
}

return rVal;

}


Now I have some questions.

1. Can you give me suggestions that above code is enough to check that
database is corrupt or not and if it is not enough then what statement or
functionality can be added so that  it could detect all the possible or
maximum corrupt scenarios.

2. When I add some extra characters at the end of  the database file though
some text editor and then call into that file "PRAGMA integrity_check"
statement. I get the error

 "SQLITE_NOTADB 26 /* File opened that is not a database file */".

Do you think that this error suggests that database file has been corrupt.
Should not this statement return OK and put the description of error in the
row of record.

3. As I am currently deleting the database file when I get the error from
the above method and I know it should be used as a last resort that's why I
also want to improve the above method.

But even if, after all the checking I get the error from the above method
and now I want to recover my database so what could be the best methods for
that.As far as know I can try
select statement on the corrupted database and if that statement
successfully executes then copy the records in to new database. I did not
try it though.

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

Re: [sqlite] "Database is Locked"

[D. Richard Hipp]

On Aug 19, 2008, at 11:14 AM, <[EMAIL PROTECTED]> <[EMAIL PROTECTED]>  
wrote:

> Are there
> any problems with creating the database with 3.5.7 and then reading  
> it with 3.6.1?

There are not suppose to be any difference.  Nobody else has reported  
differences.

D. Richard Hipp
[EMAIL PROTECTED]



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

Re: [sqlite] "Database is Locked"

[rob]

Below is the call to open a new database. This comes back successful.
   sqlite3_open16(pchFileName, _sqliteDB) 
pchFileName: is the path to the database 
m_sqliteDB: is the database handler

This function executes a PRAGMA statement.
sqlite3_exec(m_sqliteDB, m_formatBuffer, g_sqlCallback, this, _sqliteErrMsg);

These are the two functions that are called. The open comes back successful, 
but when
the exec is called it returns with a "database is locked" error.


>  ---Original Message---
>  From: Robert Simpson <[EMAIL PROTECTED]>
>  Subject: Re: [sqlite] "Database is Locked"
>  Sent: Aug 19 '08 15:16
>  
>  Show and tell time!  Lets see some code :)
>  
>  -Original Message-
>  From: [EMAIL PROTECTED]
>  [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED]
>  Sent: Tuesday, August 19, 2008 8:15 AM
>  To: General Discussion of SQLite Database
>  Subject: Re: [sqlite] "Database is Locked"
>  
>  I haven't changed anything. I recompiled with 3.5.7 and it worked fine. Are
>  there
>  any problems with creating the database with 3.5.7 and then reading it with
>  3.6.1?
>  
>  Thanks
>  
>  >  ---Original Message---
>  >  From: D. Richard Hipp <[EMAIL PROTECTED]>
>  >  Subject: Re: [sqlite] "Database is Locked"
>  >  Sent: Aug 19 '08 14:38
>  >  
>  >  
>  >  On Aug 19, 2008, at 10:33 AM, <[EMAIL PROTECTED]> <[EMAIL PROTECTED]>  
>  >  wrote:
>  >  
>  >  > I currently have a project that runs on a Windows XP machine. The  
>  >  > project was working with
>  >  > SQLite 3.5.7, but I recently upgraded to 3.6.1 . Since the upgrade I  
>  >  > have been getting
>  >  > an "Database is Locked" error message on any type of query that is  
>  >  > ran after the database is
>  >  > "opened". I am assuming that when the database is opened it is being  
>  >  > locked somehow. I am
>  >  > using sqlite3_open16 to open the database and sqlite3_exec to  
>  >  > execute a simple PRAGMA
>  >  > statement. If anyone knows of a solution to this problem I would  
>  >  > appreciate the help.
>  >  >
>  >  
>  >  What else have you changed other than 3.5.7 ->  3.6.1?  If you pull  
>  >  out 3.6.1 and recompile with 3.5.7 again does the problem go away?
>  >  
>  >  I do not recall making any changes to locking behavior between 3.5.7  
>  >  and 3.6.1
>  >  
>  >  D. Richard Hipp
>  >[EMAIL PROTECTED]
>  >  
>  >  
>  >  
>  >  ___
>  >  sqlite-users mailing list
>  >[EMAIL PROTECTED]
>  >  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>  >  
>  ___
>  sqlite-users mailing list
>  sqlite-users@sqlite.org
>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>  
>  
>  ___
>  sqlite-users mailing list
>  sqlite-users@sqlite.org
>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Database is Locked"

[Robert Simpson]

Show and tell time!  Lets see some code :)

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED]
Sent: Tuesday, August 19, 2008 8:15 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] "Database is Locked"

I haven't changed anything. I recompiled with 3.5.7 and it worked fine. Are
there
any problems with creating the database with 3.5.7 and then reading it with
3.6.1?

Thanks

>  ---Original Message---
>  From: D. Richard Hipp <[EMAIL PROTECTED]>
>  Subject: Re: [sqlite] "Database is Locked"
>  Sent: Aug 19 '08 14:38
>  
>  
>  On Aug 19, 2008, at 10:33 AM, <[EMAIL PROTECTED]> <[EMAIL PROTECTED]>  
>  wrote:
>  
>  > I currently have a project that runs on a Windows XP machine. The  
>  > project was working with
>  > SQLite 3.5.7, but I recently upgraded to 3.6.1 . Since the upgrade I  
>  > have been getting
>  > an "Database is Locked" error message on any type of query that is  
>  > ran after the database is
>  > "opened". I am assuming that when the database is opened it is being  
>  > locked somehow. I am
>  > using sqlite3_open16 to open the database and sqlite3_exec to  
>  > execute a simple PRAGMA
>  > statement. If anyone knows of a solution to this problem I would  
>  > appreciate the help.
>  >
>  
>  What else have you changed other than 3.5.7 ->  3.6.1?  If you pull  
>  out 3.6.1 and recompile with 3.5.7 again does the problem go away?
>  
>  I do not recall making any changes to locking behavior between 3.5.7  
>  and 3.6.1
>  
>  D. Richard Hipp
>  [EMAIL PROTECTED]
>  
>  
>  
>  ___
>  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] "Database is Locked"

[rob]

I haven't changed anything. I recompiled with 3.5.7 and it worked fine. Are 
there
any problems with creating the database with 3.5.7 and then reading it with 
3.6.1?

Thanks

>  ---Original Message---
>  From: D. Richard Hipp <[EMAIL PROTECTED]>
>  Subject: Re: [sqlite] "Database is Locked"
>  Sent: Aug 19 '08 14:38
>  
>  
>  On Aug 19, 2008, at 10:33 AM, <[EMAIL PROTECTED]> <[EMAIL PROTECTED]>  
>  wrote:
>  
>  > I currently have a project that runs on a Windows XP machine. The  
>  > project was working with
>  > SQLite 3.5.7, but I recently upgraded to 3.6.1 . Since the upgrade I  
>  > have been getting
>  > an "Database is Locked" error message on any type of query that is  
>  > ran after the database is
>  > "opened". I am assuming that when the database is opened it is being  
>  > locked somehow. I am
>  > using sqlite3_open16 to open the database and sqlite3_exec to  
>  > execute a simple PRAGMA
>  > statement. If anyone knows of a solution to this problem I would  
>  > appreciate the help.
>  >
>  
>  What else have you changed other than 3.5.7 ->  3.6.1?  If you pull  
>  out 3.6.1 and recompile with 3.5.7 again does the problem go away?
>  
>  I do not recall making any changes to locking behavior between 3.5.7  
>  and 3.6.1
>  
>  D. Richard Hipp
>  [EMAIL PROTECTED]
>  
>  
>  
>  ___
>  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] "Database is Locked"

[D. Richard Hipp]

On Aug 19, 2008, at 10:33 AM, <[EMAIL PROTECTED]> <[EMAIL PROTECTED]>  
wrote:

> I currently have a project that runs on a Windows XP machine. The  
> project was working with
> SQLite 3.5.7, but I recently upgraded to 3.6.1 . Since the upgrade I  
> have been getting
> an "Database is Locked" error message on any type of query that is  
> ran after the database is
> "opened". I am assuming that when the database is opened it is being  
> locked somehow. I am
> using sqlite3_open16 to open the database and sqlite3_exec to  
> execute a simple PRAGMA
> statement. If anyone knows of a solution to this problem I would  
> appreciate the help.
>

What else have you changed other than 3.5.7 ->  3.6.1?  If you pull  
out 3.6.1 and recompile with 3.5.7 again does the problem go away?

I do not recall making any changes to locking behavior between 3.5.7  
and 3.6.1

D. Richard Hipp
[EMAIL PROTECTED]



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

[sqlite] "Database is Locked"

[rob]

I currently have a project that runs on a Windows XP machine. The project was 
working with
SQLite 3.5.7, but I recently upgraded to 3.6.1 . Since the upgrade I have been 
getting
an "Database is Locked" error message on any type of query that is ran after 
the database is
"opened". I am assuming that when the database is opened it is being locked 
somehow. I am
using sqlite3_open16 to open the database and sqlite3_exec to execute a simple 
PRAGMA
statement. If anyone knows of a solution to this problem I would appreciate the 
help.

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

Re: [sqlite] vfs implementation question

[D. Richard Hipp]

On Aug 19, 2008, at 9:37 AM, Jeffrey Becker wrote:

> So in the cases where the lock cant be acquired, the built in vfs
> implementations return SQLITE_BUSY?
>

Yes

D. Richard Hipp
[EMAIL PROTECTED]



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

Re: [sqlite] vfs implementation question

[Jeffrey Becker]

So in the cases where the lock cant be acquired, the built in vfs
implementations return SQLITE_BUSY?

On Tue, Aug 19, 2008 at 9:16 AM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
>
> On Aug 19, 2008, at 9:02 AM, Jeffrey Becker wrote:
>
>> Should the xLock member of sqlite3_io_methods object block until the
>> lock can be acquired?
>
> xLock does not block on any of the built-in VFSes.  But if you want to
> make your own custom VFS that blocks on locks, I don't  know of any
> reason why that wouldn't work.
>
> D. Richard Hipp
> [EMAIL PROTECTED]
>
>
>
> ___
> 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] GCC give a warning while passing a struct as the, user data to the callback function of sqlite3_exec()

[Midmay]

Enrique Ramirez wrote:
> I noticed what you mean (to reply to my own post). I just noticed the
> original subject and this thread's subject are different:
> 
> (original) [sqlite] GCC give a warning while passing a struct as the
> user data to the callback function of sqlite3_exec()
> (this thread) [sqlite] GCC give a warning while passing a struct as
> the, user data to the callback function of sqlite3_exec()
> 
> Notice the comma? Maybe you modified it by mistake. Gmail uses
> subjects to thread messages.

Oh, I didn't notice that I have add an ',' in the second mail.

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

Re: [sqlite] vfs implementation question

[D. Richard Hipp]

On Aug 19, 2008, at 9:02 AM, Jeffrey Becker wrote:

> Should the xLock member of sqlite3_io_methods object block until the
> lock can be acquired?

xLock does not block on any of the built-in VFSes.  But if you want to  
make your own custom VFS that blocks on locks, I don't  know of any  
reason why that wouldn't work.

D. Richard Hipp
[EMAIL PROTECTED]



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

Re: [sqlite] GCC give a warning while passing a struct as the, user data to the callback function of sqlite3_exec()

[Midmay]

Enrique Ramirez wrote:
> Looking at your address I can see you also use gmail. Threading for
> this message is working fine for me. Are you using the web client or
> an external mail program?
> 


In fact, I'm using thunderbird, but it may doesn't matters. When i
subscribed, I turn digest mode on, may be it cause the issues. Now I
have turn it off, seems that my reply before has been list correctly.

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

Re: [sqlite] GCC give a warning while passing a struct as the, user data to the callback function of sqlite3_exec()

[Enrique Ramirez]

I noticed what you mean (to reply to my own post). I just noticed the
original subject and this thread's subject are different:

(original) [sqlite] GCC give a warning while passing a struct as the
user data to the callback function of sqlite3_exec()
(this thread) [sqlite] GCC give a warning while passing a struct as
the, user data to the callback function of sqlite3_exec()

Notice the comma? Maybe you modified it by mistake. Gmail uses
subjects to thread messages.

Hope this helps,

> On Tue, Aug 19, 2008 at 8:58 AM, Midmay <[EMAIL PROTECTED]> wrote:
>> I have another problem now, it sounds silly but...
>>
>> Each time I reply, it create a new topic, but not listing the reply
>> under the original mail. How to make my reply mail is exactly list under
>> the original mail somebody posted?
>>
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> // --
> Enrique Ramirez Irizarry
> Lead Developer
> Indie Code Labs
> http://www.indiecodelabs.com
>



-- 
// --
Enrique Ramirez Irizarry
Lead Developer
Indie Code Labs
http://www.indiecodelabs.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] vfs implementation question

[Jeffrey Becker]

Should the xLock member of sqlite3_io_methods object block until the
lock can be acquired?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GCC give a warning while passing a struct as the, user data to the callback function of sqlite3_exec()

[Enrique Ramirez]

Looking at your address I can see you also use gmail. Threading for
this message is working fine for me. Are you using the web client or
an external mail program?

On Tue, Aug 19, 2008 at 8:58 AM, Midmay <[EMAIL PROTECTED]> wrote:
> Greetings,
>
>> As Michael Knigge has suggested, you may just rename the parameter to
>> something else and then have something like
>>
>>MyStruct* data = (MyStruct*)foo;
>>
>> as the first line in your callback() function.
>
> Got it.
>
> Sorry but I make a mistake before, the '3rd argument' ought to be '4th
> argument', so the question should be:
>
>It means that while passing the 4th argument in sqlite3_exec(), the
> argument has been casted to void* ?
>
> However, thank you for your explanation for the callback function.
>
>
> I have another problem now, it sounds silly but...
>
> Each time I reply, it create a new topic, but not listing the reply
> under the original mail. How to make my reply mail is exactly list under
> the original mail somebody posted?
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
// --
Enrique Ramirez Irizarry
Lead Developer
Indie Code Labs
http://www.indiecodelabs.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GCC give a warning while passing a struct as the, user data to the callback function of sqlite3_exec()

[Midmay]

Greetings,

> As Michael Knigge has suggested, you may just rename the parameter to
> something else and then have something like
>
>MyStruct* data = (MyStruct*)foo;
>
> as the first line in your callback() function.

Got it.

Sorry but I make a mistake before, the '3rd argument' ought to be '4th
argument', so the question should be:

It means that while passing the 4th argument in sqlite3_exec(), the
argument has been casted to void* ?

However, thank you for your explanation for the callback function.


I have another problem now, it sounds silly but...

Each time I reply, it create a new topic, but not listing the reply
under the original mail. How to make my reply mail is exactly list under
the original mail somebody posted?



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

Re: [sqlite] sqlite .dll package doesn't have .h file

[Mihai Limbasan]

Joshua Hudson wrote:

A precompiled .dll package is provided. I want to use it.
The package doesn't contain a .h file to #include in my program.

Now what?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
You take the header file from the amalgamation source tarball 
corresponding to the version you're using?


--
Multumesc,
Mihai Limbasan

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

Re: [sqlite] sqlite .dll package doesn't have .h file

[Michael Knigge]

Joshua Hudson schrieb:
> A precompiled .dll package is provided. I want to use it.
> The package doesn't contain a .h file to #include in my program.
> 
> Now what?

Download the source and take the .h file from the source code? Just as 
an idea



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

[sqlite] sqlite .dll package doesn't have .h file

[Joshua Hudson]

A precompiled .dll package is provided. I want to use it.
The package doesn't contain a .h file to #include in my program.

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

Re: [sqlite] Using SQLite as an application file format (C++)

[Chris Wedgwood]

On Mon, Aug 18, 2008 at 04:38:03PM -0400, Darren Landrum wrote:

> Software synthesis applications, particularly disk-streaming
> samplers, are very high-performance programs, so I'd like to keep
> disk I/O as clear as possible. Every touch of a GUI widget will
> result in a query run on the database, so it's best that that be in
> memory, I think.

On a modern OS unless you lock the memory that can (and does) happen
even if things are loaded into memory since the OS will often push
those pages out to swap/paging (more so under high buffered IO).

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

Re: [sqlite] GCC give a warning while passing a struct as the, user data to the callback function of sqlite3_exec()

[Markus Thiele]

Greetings,

> It works. Just ((MyStruct *) data)->x looks strange.

As Michael Knigge has suggested, you may just rename the parameter to 
something else and then have something like

MyStruct* data = (MyStruct*)foo;

as the first line in your callback() function.

In fact I believe in C implicit casts from void* are allowed, so the 
explicit cast on the right is probably unnecessary. However, C++ 
compilers definitely do not allow implicit casts from void*, so you 
might as well put it, just in case.

> It means that while passing the 3rd argument in sqlite3_exec(), the
> argument has been casted to void* ?

The 3rd argument is "callback", it's expected to be of type

int (*callback)(void*, int, char**, char**)

your original callback however had a different type, namely

int (*callback)(MyStruct*, int, char**, char**)

I'm not exactly sure what the semantics for function pointer casts are, 
if there are any at all. To (maybe) answer your question, casts from 
pointer types to void* are always allowed, that is why your 4th argument 
  of type MyStruct* is accepted without any complaints, even though 
sqlite3_exec() expects a void* there.

The warning had nothing to do with the 4th argument ("first argument to 
callback") at all, only with the 3rd argument ("callback function").

The program happens to run correctly because the internal 
representation, etc. of the function happens to be the same with both 
parameter types (MyStruct* and void*), but that is undefined behavior 
and it might well crash on a different platform or with a different 
compiler (although I'd guess in this particular case it's probably 
rather unlikely).

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

Re: [sqlite] GCC give a warning while passing a struct as the, user data to the callback function of sqlite3_exec()

[Midmay]

Thank you, Markus!

> Simply change the type of the first argument to your callback function
> to void* and the problem should go away. Of course you'll then have to
> cast back to MyStruct* inside callback() to actually use the value.

It works. Just ((MyStruct *) data)->x looks strange.

> The problem here is the type of your callback() function. By declaring
> the first argument to it as MyStruct*, rather than void* as in the
> function pointer type of the 3rd argument to sqlite3_exec(), you're
> creating an incompatible function pointer type.

It means that while passing the 3rd argument in sqlite3_exec(), the
argument has been casted to void* ?

BTW: It is the first time I use mail list, hope I'm replying correctly.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GCC give a warning while passing a struct as the user data to the callback function of sqlite3_exec()

[Michael Knigge]

> however, the program runs well. What may causing the warning? what
> should i do to get rid of this warning.

The warning is produced because your callback() isn't

int callback(void *, int, char **, char **);


try this:

int callback(void *foo, int argc, char **argv, char **azColName)
{
MyStruct *data = (MyStruct *)foo;
...
}


haven't checked, but this should blow away the warning


Bye,
Michael

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

Re: [sqlite] GCC give a warning while passing a struct as the user data to the callback function of sqlite3_exec()

[Markus Thiele]

Greetings,

> I declare a struct and pass it as the 4th argument of sqlite3_exec(),
> and define the 1st argument of callback function a pointer to this kind
> of struct. while compiling, GCC give a warning like this:
>   ae.c: In function ‘main’:
>   ae.c:56: warning: passing argument 3 of ‘sqlite3_exec’ from
> incompatible pointer type

The problem here is the type of your callback() function. By declaring 
the first argument to it as MyStruct*, rather than void* as in the 
function pointer type of the 3rd argument to sqlite3_exec(), you're 
creating an incompatible function pointer type.

> however, the program runs well. What may causing the warning? what
> should i do to get rid of this warning.

Simply change the type of the first argument to your callback function 
to void* and the problem should go away. Of course you'll then have to 
cast back to MyStruct* inside callback() to actually use the value.

Hope this helps,
-- Markus Thiele
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] GCC give a warning while passing a struct as the user data to the callback function of sqlite3_exec()

[Midmay]

I declare a struct and pass it as the 4th argument of sqlite3_exec(),
and define the 1st argument of callback function a pointer to this kind
of struct. while compiling, GCC give a warning like this:
ae.c: In function ‘main’:
ae.c:56: warning: passing argument 3 of ‘sqlite3_exec’ from
incompatible pointer type


however, the program runs well. What may causing the warning? what
should i do to get rid of this warning.

Thanks!

Here is an simple example:

#include 
#include 
#include 

typedef struct
{
int x;
int y;
}MyStruct;


static int callback(MyStruct *data, int argc, char **argv, char
**azColName){
   int i;
   printf ("%d, %d\n", data->x, data->y);

   for(i=0; ix = 1;
   data->y = 2;


   if( argc!=3 ){
 fprintf(stderr, "Usage: %s DATABASE SQL-STATEMENT\n", argv[0]);
 exit(1);
   }
   rc = sqlite3_open(argv[1], );
   if( rc != SQLITE_OK){
 fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
 sqlite3_close(db);
 exit(1);
   }
   rc = sqlite3_exec(db, argv[2], callback, data, );
   if( rc!=SQLITE_OK ){
 fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
   }
   sqlite3_close(db);

   free (data);
   return 0;
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users