[sqlite] Do you have sqlite-3.1.5.tar.gz?
Hi ryan bel brillo, Do you have sqlite-3.1.5.tar.gz? If you have , could you give me one? Thanks sincerely zhang jie ryan bel brillo <[EMAIL PROTECTED]> 写道： Hi zhang jie, I don���t think there was a problem on the creation and updating of the database. You see, I log every sql statement execute from database creation and updates, and I didn���t see any sql error or any error in that case. Also, I open the database using command line in linux and it works fine. -Original Message- From: 陆�� [mailto:[EMAIL PROTECTED] Sent: Monday, March 13, 2006 7:39 PM To: sqlite-users@sqlite.org Subject: [sqlite] why i can not open the new database i created at AT91ARM9200? Hi ryan bel brillo, First of all,thank you for your reply. The problem is not a compatibility with database created in linux and windows . I downloaded source code sqlite-2.8.17.tar.gz through your website under windows OS. But I gunziped and cross-compiled it for AT91ARM9200(development board) under linux OS. I run the binary file "sqlite" at AT91ARM9200. #./sqlite test.sqlite //Am I creating a new database"test.sqlite"? sqlite>CREATE TABLE test(name varchar(80); SQL erro:unable to open database test.sqlite # #./sqlite test.db //Am I creating a new database"test.db"? sqlite>CREATE TABLE test(name varchar(80); SQL erro:unable to open database test.db # # vi test.sqlite # chmod 777 test.sqlite # ./sqlite test.sqlite sqlite>CREATE TABLE test(name varchar(80)); SQL erro:can not write to test.sqlite,it is readonly file. sqlite> # vi test.db # chmod 777 test.db # ./sqlite test.db sqlite>CREATE TABLE test(name varchar(80)锛�num smallint); SQL erro:can not write to test.db,it is readonly file. sqlite> So why i can not open the new database i created at AT91ARM9200? I guess i didn't create database correctly,aren't you? sincerely zhang jie ryan bel brillo ��锛? Hi all, Im new in this list and with sqlite. Im not sure if this topic have been discussed before. Anyway, I have this problem regarding sqlite. I created a c program that would create a new sqlite database from some sql file then update it every five minutes using sql statement stored in a postgresql database. Im using a version 3.3.2 sqlite database and sqlite3 library in c. The sqlite database and c program was executed in a linux OS. When I tried to open my created database in a SQLite database browser in windows It shows nothing but in linux it shows the correct tables and data. Also, I create a sqlite database in windows then open it in linux it works fine. Also, I created a simple sqlite database manually but it still cant be read in windows. Is there a compatibility problem with database created in linux and windows? I remember this problem on occurs on sqlite version 2.6.2 or earlier.. Or is there a problem in the way I create the database? The sqlite browser im using can read sqlite database version 3.x. Thanks - ��1G���璐归��绠辩�惧鹃�插句�?lt;br> __ 赶快注册雅虎超大容量免费邮箱? http://cn.mail.yahoo.com
Re: [sqlite] Database created in linux cant be read in windows
"ryan bel brillo" <[EMAIL PROTECTED]> wrote: > > On linux, I got an sqlite database version 3.3.2 while on windows its > sqlite database version 3.2.1, I'll upgrade my sqlite database to > version 3.3.x > > So that's explain why sqlite database created on windows can be read > just fine in linux. > The file format is backwards but not forwards compatible. You can get version 3.3.4 to generate the older file format by doing: PRAGMA legacy_file_format=1; You can also recompile with: -DSQLITE_DEFAULT_FILE_FORMAT=1 -- D. Richard Hipp <[EMAIL PROTECTED]>
RE: [sqlite] Database created in linux cant be read in windows
>>> -Original Message- >>> From: Christian Smith [mailto:[EMAIL PROTECTED] >>> Sent: Tuesday, March 14, 2006 4:06 AM >>> To: sqlite-users@sqlite.org >>> Subject: RE: [sqlite] Database created in linux cant be read in windows >>> >>> On Wed, 15 Mar 2006, ryan bel brillo wrote: >>> >>> > Im using a software ipswitch WS_FTP to transfer the file from linux >>> to >>> >windows. Im not sure before if I configured it to binary transfer but >>> I >>> >tried it again and make sure to configure it and set it to binary >>> >transfer, still im getting the same result. >>> > >>> > Currently, Im trying to create the database in windows then use the >>> >created database as my base db in linux. Then I'll just have to update >>> >the base db in linux using the different sql statement in postgresql >>> and >>> >transfer it back to windows if the base db can still be read with the >>> >updates. >>> > >>> > On trying to read the file on sqlite3 command line program in >>> >>> windows; >>> >I'll try it later. >>> >>> >>> What version of SQLite are you using on Windows? 3.x where x<3 has >>> problems reading databases created with 3.3 and above due to a small >>> change in file format. Make sure you have 3.3.x on Windows as well. >>> >>> The database itself should otherwise be compatible. >>> >>> Christian >>> >>> -- >>> /"\ >>> \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL >>> X - AGAINST MS ATTACHMENTS >>> / \ >>> On linux, I got an sqlite database version 3.3.2 while on windows its sqlite database version 3.2.1, I'll upgrade my sqlite database to version 3.3.x So that's explain why sqlite database created on windows can be read just fine in linux.
RE: [sqlite] Recursive sqlite3_exec call
I would like to be more specific about the scenario I encountered. Suppose the following SQL statement is executed by calling sqlite3_exec: SELECT mySqlFunction() Inside the body of the mySqlFuntion() the following statements are executed through calls to sqlite3_exec: CREATE TEMP TABLE MyTempTable AS SELECT ... Then a couple of SELECT x, y FROM MyTempTable ... And finally: DROP TABLE MyTempTable The last statement execution fails on Linux (Fedora Core 4) with the following error "database table is locked" but not on Windows XP. What would be a reason for that? Thanks, Iulian. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 14, 2006 5:14 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Recursive sqlite3_exec call "Iulian Popescu" <[EMAIL PROTECTED]> wrote: > Hello, > > > > Is that possible to define a SQL function that calls sqlite3_exec on the > same db handler through which was executed (passed to the function as user > data)? > Yes. But a table being read cannot be written by the recursive call. So you cannot do an UPDATE on the same row you are looking at. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Recursive sqlite3_exec call
"Iulian Popescu" <[EMAIL PROTECTED]> wrote: > Hello, > > > > Is that possible to define a SQL function that calls sqlite3_exec on the > same db handler through which was executed (passed to the function as user > data)? > Yes. But a table being read cannot be written by the recursive call. So you cannot do an UPDATE on the same row you are looking at. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] Recursive sqlite3_exec call
Hello, Is that possible to define a SQL function that calls sqlite3_exec on the same db handler through which was executed (passed to the function as user data)? Thanks, Iulian.
Re: [sqlite] RE:Re: [sqlite] SQLite memory leak on Windows CE
On 3/14/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > Hello again, > Thank for your answers: > first of all thank you who pointed that i should use a close(db) at line 17 > and call sqlite3_free_table(result) even if there was an error, but these > solutions didn´t solve my problem (the execution code never reached that > point because ret value was always SQLITE_OK). > Do anyone of you work with SQLite on Windows CE? Sorry, I don't have windows CE available to me to test on.
Re: [sqlite] WHERE expression problem
Eno Thereska <[EMAIL PROTECTED]> wrote: > Hi, > > I noticed the following strange problem when the expression to WHERE > contains clauses that refer to the same column. Here are three queries > and their output: > > > select count(*) from table1 > where ((timestamp >13448180261410868) and (timestamp <= 13448182164507680)); > > output: 100 > > > select count(*) from table1 > where (timestamp<=13448180261410868); > > output: 46 > > select count(*) from table1; > > output: 100 > This problem is described by ticket #1188 http://www.sqlite.org/cvstrac/tktview?tn=1188 And was fixed for version 3.2.2 on 2005-March-31. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] RE:Re: [sqlite] SQLite memory leak on Windows CE
Hello again,
Thank for your answers:
first of all thank you who pointed that i should use a close(db) at line 17 and
call sqlite3_free_table(result) even if there was an error, but these solutions
didn´t solve my problem (the execution code never reached that point because
ret value was always SQLITE_OK).
Do anyone of you work with SQLite on Windows CE?
if you guys work with it then it might be interesting if you try my code (with
a quite big table) and examine the memory using the remote performance monitor
of EVC 4.0 under the tools menu.
Thank you everyone
Eduardo Esteban
---Mensaje original---You might look to recoding your application to not use
get_table, a
memory hog. Instead use step in a loop.
Jay Sprenkle wrote:
> On 3/14/06, [EMAIL PROTECTED] wrote:
>
>>Hi all,
>>I am having real problems with SQLite on Windows CE (.NET 4.1 and
Pocket PC 2003).
>
>
>> ret = sqlite3_get_table(db, "SELECT * FROM artic",
&result, &rows, &cols, &errmsg);
>> if (ret != SQLITE_OK)
>> {
>> MessageBox(_T("Error en SQL"),_T("Error"),MB_OK);
>> sqlite3_free(errmsg);
>
>
> You might call sqlite3_free_table(result) here. Without looking at the
source
> code it might have allocated a result even though there was an error.
>
>
>
>>return;
>> }
>> else {
>> MessageBox(_T("Erroron SQL
sentence"),_T("Error"),MB_OK);
>> }
>>sqlite3_free_table(result);
>> sqlite3_close(db);
>>}
>
>
>
> Did you look at the source code for sqlite3_get_table()?
> It's easy to get and may be very helpful.
Automóviles, telefonía, imagen y sonido, belleza... ¡encuentra gratis todo lo
que necesitas! http://clasificados.wanadoo.es/
Re: [sqlite] WHERE expression problem
Eno Thereska <[EMAIL PROTECTED]> wrote: > the integer is a 64-bit value. Does SQLite have > trouble with those? Nope. Not that I am aware of. > > Although it appears the above is not the problem, I am curious if SQlite > will ever store an integer as a string, if the column type is > explicitely created to be an integer? No. Integers are always stored as integers when put in an integer column. What version of SQLite are you using? What operating system? Did you build SQLite yourself or use a precompiled binary? Please send me your database by private email and I will have a look. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Re: - [sqlite] WHERE expression problem
I tried all bracket combinations before emailing. It doesn't seem to be a problem with the brackets. Thanks Eno [EMAIL PROTECTED] wrote: Try: select count(*) from table1 where (timestamp >13448180261410868 and timestamp <= 13448182164507680); not: select count(*) from table1 where ((timestamp >13448180261410868) and (timestamp <= 13448182164507680)); Regards. rayB ** PLEASE CONSIDER OUR ENVIRONMENT BEFORE PRINTING * *** Confidentiality and Privilege Notice *** This e-mail is intended only to be read or used by the addressee. It is confidential and may contain legally privileged information. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone, and you should destroy this message and kindly notify the sender by reply e-mail. Confidentiality and legal privilege are not waived or lost by reason of mistaken delivery to you. Qantas Airways Limited ABN 16 009 661 901 Visit Qantas online at http://qantas.com -- Eno Thereska - Carnegie Mellon University Parallel Data Laboratory CIC Building Cube 2221-D 5000 Forbes Ave Pittsburgh, PA 15213 Tel: 412-268-5908
Re: [sqlite] SQLite memory leak on Windows CE
On March 14, 2006 12:03 pm, [EMAIL PROTECTED] wrote:
> Hi all,
> I am having real problems with SQLite on Windows CE (.NET 4.1 and
> Pocket PC 2003). I have tried everything and it seems that there is a
> memory leak with SQLite on Windows CE. I have test it with SQLite
> versions 3.2.2 and 3.3.4 and SQLite seems that it reserves memory and
> doesn´t free it after using it, memery is freed only after the main
> program has finished. Take this simple example (it fails also with
> more complicated ones). I have test it using a quite big table
> (20,000 records).
>
> Please i really need help, because my program consumes all the memory
> and i need to finish it next week: Here is the sample source code to
> test (this is done with EVC 4.0 and a MFC application) void
1>CMemoryMDlg::OnButton1() {
2> char *errmsg;
3> char **result;
4> sqlite3 *db;
5> int ret, rows, cols;
6>
7> int n = sqlite3_open("\\mysqlitebd.db", &db);
8> if (db == 0) {
9>MessageBox(_T("Error openning BD"),_T(""),MB_OK);
10>return;
11> }
12> ret = sqlite3_get_table(db, "SELECT * FROM artic",
13>&result, &rows, &cols, &errmsg);
14> if (ret != SQLITE_OK) {
15>MessageBox(_T("Error en SQL"),_T("Error"),MB_OK);
16>sqlite3_free(errmsg);
17>return;
18> } else {
19>MessageBox(_T("Error on SQL sentence"),_T("Error"),MB_OK);
20> }
21> sqlite3_free_table(result);
22> sqlite3_close(db);
23>}
Looking at your code, please note that from line 12 onwards you NOW
have a database open so you have to CLOSE(db). I notice that you only
close it at line 22 but forgot/failed to do a CLOSE(db) before you
RETURN on line 17.
I recommend you check your other routines for a common fault in
forgetting to close all open handles and allocated memories before
doing a RETURN if you hit an error.
I did not test your code but note the above just from reading your
code. Other readers may point out other issues besides the one I
mentioned.
Hope that helps.
Re: [sqlite] WHERE expression problem
Thanks for the quick reply. >SELECT DISTINCT typeof(timestamp) FROM table1; returned "integer". Now the integer is a 64-bit value. Does SQLite have trouble with those? Although it appears the above is not the problem, I am curious if SQlite will ever store an integer as a string, if the column type is explicitely created to be an integer? Thanksm Eno [EMAIL PROTECTED] wrote: Eno Thereska <[EMAIL PROTECTED]> wrote: Hi, I noticed the following strange problem when the expression to WHERE contains clauses that refer to the same column. Here are three queries and their output: select count(*) from table1 where ((timestamp >13448180261410868) and (timestamp <= 13448182164507680)); output: 100 select count(*) from table1 where (timestamp<=13448180261410868); output: 46 select count(*) from table1; output: 100 Some of your timestamp values might be stored as strings instead of numbers. A string always compares greater than a number. What does this show you: SELECT DISTINCT typeof(timestamp) FROM table1; If this is the problem, you can fix it quickly by doing: UPDATE table1 SET timestamp=timestamp+0; -- D. Richard Hipp <[EMAIL PROTECTED]> -- Eno Thereska - Carnegie Mellon University Parallel Data Laboratory CIC Building Cube 2221-D 5000 Forbes Ave Pittsburgh, PA 15213 Tel: 412-268-5908
Re: [sqlite] Re: - [sqlite] WHERE expression problem
Try: select count(*) from table1 where (timestamp >13448180261410868 and timestamp <= 13448182164507680); not: select count(*) from table1 where ((timestamp >13448180261410868) and (timestamp <= 13448182164507680)); Regards. rayB ** PLEASE CONSIDER OUR ENVIRONMENT BEFORE PRINTING * *** Confidentiality and Privilege Notice *** This e-mail is intended only to be read or used by the addressee. It is confidential and may contain legally privileged information. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone, and you should destroy this message and kindly notify the sender by reply e-mail. Confidentiality and legal privilege are not waived or lost by reason of mistaken delivery to you. Qantas Airways Limited ABN 16 009 661 901 Visit Qantas online at http://qantas.com
Re: [sqlite] Re: - [sqlite] WHERE expression problem
Sorry, I should have said "at or before time A" :) The problem still remains (taking off the equal sign doesn't change anything). Thanks Eno [EMAIL PROTECTED] wrote: "The second query says that before time A there are 46 entries." No, that is not what the query states: select count(*) from table1 where (timestamp<=13448180261410868); ^ Regards. rayB ** PLEASE CONSIDER OUR ENVIRONMENT BEFORE PRINTING * *** Confidentiality and Privilege Notice *** This e-mail is intended only to be read or used by the addressee. It is confidential and may contain legally privileged information. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone, and you should destroy this message and kindly notify the sender by reply e-mail. Confidentiality and legal privilege are not waived or lost by reason of mistaken delivery to you. Qantas Airways Limited ABN 16 009 661 901 Visit Qantas online at http://qantas.com -- Eno Thereska - Carnegie Mellon University Parallel Data Laboratory CIC Building Cube 2221-D 5000 Forbes Ave Pittsburgh, PA 15213 Tel: 412-268-5908
Re: [sqlite] WHERE expression problem
Eno Thereska <[EMAIL PROTECTED]> wrote: > Hi, > > I noticed the following strange problem when the expression to WHERE > contains clauses that refer to the same column. Here are three queries > and their output: > > > select count(*) from table1 > where ((timestamp >13448180261410868) and (timestamp <= 13448182164507680)); > > output: 100 > > > select count(*) from table1 > where (timestamp<=13448180261410868); > > output: 46 > > select count(*) from table1; > > output: 100 > Some of your timestamp values might be stored as strings instead of numbers. A string always compares greater than a number. What does this show you: SELECT DISTINCT typeof(timestamp) FROM table1; If this is the problem, you can fix it quickly by doing: UPDATE table1 SET timestamp=timestamp+0; -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] SQLite memory leak on Windows CE
You might look to recoding your application to not use get_table, a
memory hog. Instead use step in a loop.
Jay Sprenkle wrote:
On 3/14/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
Hi all,
I am having real problems with SQLite on Windows CE (.NET 4.1 and Pocket PC
2003).
ret = sqlite3_get_table(db, "SELECT * FROM artic", &result, &rows,
&cols, &errmsg);
if (ret != SQLITE_OK)
{
MessageBox(_T("Error en SQL"),_T("Error"),MB_OK);
sqlite3_free(errmsg);
You might call sqlite3_free_table(result) here. Without looking at the source
code it might have allocated a result even though there was an error.
return;
}
else {
MessageBox(_T("Erroron SQL sentence"),_T("Error"),MB_OK);
}
sqlite3_free_table(result);
sqlite3_close(db);
}
Did you look at the source code for sqlite3_get_table()?
It's easy to get and may be very helpful.
[sqlite] Re: - [sqlite] WHERE expression problem
"The second query says that before time A there are 46 entries." No, that is not what the query states: select count(*) from table1 where (timestamp<=13448180261410868); ^ Regards. rayB ** PLEASE CONSIDER OUR ENVIRONMENT BEFORE PRINTING * *** Confidentiality and Privilege Notice *** This e-mail is intended only to be read or used by the addressee. It is confidential and may contain legally privileged information. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone, and you should destroy this message and kindly notify the sender by reply e-mail. Confidentiality and legal privilege are not waived or lost by reason of mistaken delivery to you. Qantas Airways Limited ABN 16 009 661 901 Visit Qantas online at http://qantas.com
Re: [sqlite] SQLite memory leak on Windows CE
On 3/14/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>
> Hi all,
> I am having real problems with SQLite on Windows CE (.NET 4.1 and Pocket PC
> 2003).
> ret = sqlite3_get_table(db, "SELECT * FROM artic", &result,
> &rows, &cols, &errmsg);
> if (ret != SQLITE_OK)
> {
> MessageBox(_T("Error en SQL"),_T("Error"),MB_OK);
> sqlite3_free(errmsg);
You might call sqlite3_free_table(result) here. Without looking at the source
code it might have allocated a result even though there was an error.
> return;
> }
> else {
> MessageBox(_T("Erroron SQL sentence"),_T("Error"),MB_OK);
> }
> sqlite3_free_table(result);
> sqlite3_close(db);
> }
Did you look at the source code for sqlite3_get_table()?
It's easy to get and may be very helpful.
[sqlite] WHERE expression problem
Hi, I noticed the following strange problem when the expression to WHERE contains clauses that refer to the same column. Here are three queries and their output: select count(*) from table1 where ((timestamp >13448180261410868) and (timestamp <= 13448182164507680)); output: 100 select count(*) from table1 where (timestamp<=13448180261410868); output: 46 select count(*) from table1; output: 100 This cannot be (unless I am missing something). The first query says that between times A and B there are 100 entries. The second query says that before time A there are 46 entries. The last query says that in total there are 100 entries. My logic says that if the first and second queries return the true answer, the total number of entries should be 100 + 46 = 146. I counted things manually and realized that query 1 is the culprit. It is ignoring the first part of the statement and only evaluating the second part (timestamp <= 13448182164507680) Anyone has had similar problems? Thanks Eno
[sqlite] SQLite memory leak on Windows CE
Hi all,
I am having real problems with SQLite on Windows CE (.NET 4.1 and Pocket PC
2003).
I have tried everything and it seems that there is a memory leak with SQLite on
Windows CE.
I have test it with SQLite versions 3.2.2 and 3.3.4 and SQLite seems that it
reserves memory and doesn´t free it after using it, memery is freed only after
the main program has finished.
Take this simple example (it fails also with more complicated ones). I have
test it using a quite big table (20,000 records).
Please i really need help, because my program consumes all the memory and i
need to finish it next week:
Here is the sample source code to test (this is done with EVC 4.0 and a MFC
application)
void CMemoryMDlg::OnButton1()
{
char *errmsg;
char **result;
sqlite3 *db;
int ret, rows, cols;
int n = sqlite3_open("\\mysqlitebd.db", &db);
if (db == 0)
{
MessageBox(_T("Error openning BD"),_T(""),MB_OK);
return;
}
ret = sqlite3_get_table(db, "SELECT * FROM artic", &result,
&rows, &cols, &errmsg);
if (ret != SQLITE_OK)
{
MessageBox(_T("Error en SQL"),_T("Error"),MB_OK);
sqlite3_free(errmsg);
return;
}
else {
MessageBox(_T("Error on SQL
sentence"),_T("Error"),MB_OK);
}
sqlite3_free_table(result);
sqlite3_close(db);
}
Automóviles, telefonía, imagen y sonido, belleza... ¡encuentra gratis todo lo
que necesitas! http://clasificados.wanadoo.es/
Re: [sqlite] Re: Re: Multithreaded SQLITE_BUSY not handled?
On 3/14/06, Dave Brown <[EMAIL PROTECTED]> wrote: > Thanks Jay, but perhaps you could provide some useful links to the reading > material you suggest instead of just stating what you think I need to do? I tried to be helpful and point out where to start. The encyclopedia is in front of you, but you have to turn the pages. > For example, http://www.sqlite.org/lockingv3.html provides only minimal > information on the lock types in Sqlite3. http://sqlite.org/lang_transaction.html > > At any rate, I do understand what is going on with the deadlocking, and yes > I see that a begin immediate is needed for all threads in this case. So I > should restate a question I had posed to Igor earlier -- perhaps there could > be a pragma which caused all *write-transactions* to be begin-immediate? > This would allow 1-line INSERTs (executed without a formal transaction > wrapper) to also be begin-immediate. That only works with specific usage patterns and hurts with others. I think it's probably a good suggestion to look into. Dr. Hipp owns the code so you might suggest it the next time he asks for enhancement suggestions.
Re: [sqlite] Re: Re: Multithreaded SQLITE_BUSY not handled?
Thanks Jay, but perhaps you could provide some useful links to the reading material you suggest instead of just stating what you think I need to do? For example, http://www.sqlite.org/lockingv3.html provides only minimal information on the lock types in Sqlite3. At any rate, I do understand what is going on with the deadlocking, and yes I see that a begin immediate is needed for all threads in this case. So I should restate a question I had posed to Igor earlier -- perhaps there could be a pragma which caused all *write-transactions* to be begin-immediate? This would allow 1-line INSERTs (executed without a formal transaction wrapper) to also be begin-immediate. -Dave On 3/14/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote: > > On 3/13/06, Dave Brown <[EMAIL PROTECTED]> wrote: > > Yeah if I put BEGIN IMMEDIATE in thread1 as well, then it works, but as > you > > say I don't see why I need to do this. Perhaps the implicit transaction > > which is created in my 1-line INSERT statement isn't an immediate > > transaction?? > > > > Seems like there should be a flag or pragma or something to force all > > transactions to be immediate. > > Dave, you need to go read more about deadlocks (and some about how > locks are created) > to understand this. All of your threads must have the begin immediate or > it > won't solve the problem. Understanding what's going on is the key to > success >
Re: [sqlite] Help needed to diagnose "NOT IN" query
David, I asked a similar question, and here is drh's response to me, adapted to your situation. I think it is probably applicable to you as well. I'm not sure this is the type of query that can be made to go really fast no matter what. I actually haven't gotten around to doing this yet, so I can't comment on how well it works. To implement this, SQLite creates a temporary index that contains all unique values of holiday_list.number. Then it scans through the table and for each entry where holiday.hol_id is not in the temp index. Do make sure that your temp tables are stored in RAM. Compile with -DTEMP_STORE=2. Or at run-time set PRAGMA temp_store=memory; That will put the temporary index in RAM and things will tend to go faster. Brett On 3/14/06, CARTER-HITCHIN, David, FM <[EMAIL PROTECTED]> wrote: > Hi, > > Hope someone out there can help - I've got this query which runs VERY > slowly: > > SELECTab_tran.ins_num, holiday.hol_id, holiday.ins_num > FROM ab_tran, holiday > WHERE ab_tran.ins_num = holiday.ins_num > AND holiday.hol_id NOT IN ( SELECT number from holiday_list ) > > All the tables are fairly chunky, except holiday_list which has 89 rows. I > just realised that holiday.ins_num is needlessly selected but I doubt very > much that would be causing things to slow down massively. I can run this on > a similar powered Sybase server and it runs in a couple of minutes - with > SQLite, using an in-memory database I've left it all night and it didn't > finish! Evidently something is wrong somewhere. I've created indexes on > all the columns in the join and the "NOT IN" clause, but that didn't seem to > help. > > Is there anything else I check? > > I've run the EXPLAIN (output below), but I'm a novice here, not sure what > could be wrong. > > Any ideas will be highly appreciated. > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:476 - 64 rows extracted in > 0.03532 seconds. > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 0 Goto0 60 > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 1 Integer 0 0 > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 2 OpenRead0 > 3 > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 3 SetNumColumns 0 > 7 > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 4 Integer 0 0 > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 5 OpenRead1 > 5 > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 6 SetNumColumns 1 > 2 > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 7 Integer 0 0 > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 8 OpenRead3 > 137675 keyinfo(1,BINARY) > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 9 Rewind 0 56 > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 10Column 0 1 > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 11NotNull -1 14 > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 12Pop 1 0 > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 13Goto0 55 > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 14MakeRecord 1 > 0 i > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 15MemStore0 > 0 > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 16MoveGe 3 55 > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 17MemLoad 0 0 > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 18IdxGE 3 55 > + > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 19RowKey 3 0 > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 20IdxIsNull 1 > 54 > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 21IdxRowid3 > 0 > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 22MoveGe 1 0 > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 23MemLoad 1 0 > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 24If 0 40 > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 25MemInt 1 1 > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 26OpenVirtual 4 > 0 keyinfo(1,BINARY) > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 27SetNumColumns 4 > 1 > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 28Integer 0 0 > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 29OpenRead2 > 6 > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 30SetNumColumns 2 > 1 > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 31Rewind 2 39 > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 32Column 2 0 > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 33NotNull -1 36 > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 34Pop 1 0 > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 35Goto0 38 > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 36MakeRecord 1 > 0 i > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 37IdxInsert 4 > 0 > Tue Mar 14 12:05:10 2006 DEBUG at
[sqlite] Help needed to diagnose "NOT IN" query
Hi, Hope someone out there can help - I've got this query which runs VERY slowly: SELECTab_tran.ins_num, holiday.hol_id, holiday.ins_num FROM ab_tran, holiday WHERE ab_tran.ins_num = holiday.ins_num AND holiday.hol_id NOT IN ( SELECT number from holiday_list ) All the tables are fairly chunky, except holiday_list which has 89 rows. I just realised that holiday.ins_num is needlessly selected but I doubt very much that would be causing things to slow down massively. I can run this on a similar powered Sybase server and it runs in a couple of minutes - with SQLite, using an in-memory database I've left it all night and it didn't finish! Evidently something is wrong somewhere. I've created indexes on all the columns in the join and the "NOT IN" clause, but that didn't seem to help. Is there anything else I check? I've run the EXPLAIN (output below), but I'm a novice here, not sure what could be wrong. Any ideas will be highly appreciated. Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:476 - 64 rows extracted in 0.03532 seconds. Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 0 Goto0 60 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 1 Integer 0 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 2 OpenRead0 3 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 3 SetNumColumns 0 7 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 4 Integer 0 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 5 OpenRead1 5 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 6 SetNumColumns 1 2 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 7 Integer 0 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 8 OpenRead3 137675 keyinfo(1,BINARY) Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 9 Rewind 0 56 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 10Column 0 1 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 11NotNull -1 14 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 12Pop 1 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 13Goto0 55 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 14MakeRecord 1 0 i Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 15MemStore0 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 16MoveGe 3 55 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 17MemLoad 0 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 18IdxGE 3 55 + Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 19RowKey 3 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 20IdxIsNull 1 54 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 21IdxRowid3 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 22MoveGe 1 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 23MemLoad 1 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 24If 0 40 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 25MemInt 1 1 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 26OpenVirtual 4 0 keyinfo(1,BINARY) Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 27SetNumColumns 4 1 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 28Integer 0 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 29OpenRead2 6 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 30SetNumColumns 2 1 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 31Rewind 2 39 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 32Column 2 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 33NotNull -1 36 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 34Pop 1 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 35Goto0 38 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 36MakeRecord 1 0 i Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 37IdxInsert 4 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 38Next2 32 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 39Close 2 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 40Integer 1 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 41Column 1 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 42NotNull -1 46 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 43Pop 2 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 44Null0 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 45Goto0 49 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 46MakeRecord 1 0 i Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 47Found 4 49 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 48AddImm -1 0 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 49If 1 54 Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 50Column 0 1 Tue Mar 14 12:05:10 2006 DEBUG at C
Re: [sqlite] Re: Re: Multithreaded SQLITE_BUSY not handled?
On 3/13/06, Dave Brown <[EMAIL PROTECTED]> wrote: > Yeah if I put BEGIN IMMEDIATE in thread1 as well, then it works, but as you > say I don't see why I need to do this. Perhaps the implicit transaction > which is created in my 1-line INSERT statement isn't an immediate > transaction?? > > Seems like there should be a flag or pragma or something to force all > transactions to be immediate. Dave, you need to go read more about deadlocks (and some about how locks are created) to understand this. All of your threads must have the begin immediate or it won't solve the problem. Understanding what's going on is the key to success
Re: [sqlite] Database created in linux cant be read in windows
> Im using a software ipswitch WS_FTP to transfer the file from linux to > windows. Im not sure before if I configured it to binary transfer but I > tried it again and make sure to configure it and set it to binary > transfer, still im getting the same result. Try setting the transfer mode to binary and seeing if that fixes it.
Re: [sqlite] sqlite performance with sizeable tables
On Mon, 13 Mar 2006 [EMAIL PROTECTED] wrote: > [snip aio API stuff] >After finding out about this api, I found out that at least mysql and >postgresql use it, so I am guessing that changing the sql engine to >generate batches of read/writes is possible. > >My guess is that using this api will increase performance a lot as the >hard drive heads won't have to go back and forth, seeking at random >places on the disk (thus reducing the impact of having small caches). It would only be available for reads. Writes are written to the hard disk using fsync, which will allow the OS to order outstanding writes in whatever order it sees optimal. Reads must be done in order in general, as you must know what you want to read before trying to read it (think of trying to navigate a btree in parallel. You can't as you don't know which node to read in until you processed the current node) so you'll get little benefit in performance. The only other option to use AIO would be to traverse multiple btrees in parallel, such as when you have multiple cursors open in your query. But then you have potentially complicated the virtual machine. > >Nicolas > Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
RE: [sqlite] Database created in linux cant be read in windows
On Wed, 15 Mar 2006, ryan bel brillo wrote: > Im using a software ipswitch WS_FTP to transfer the file from linux to >windows. Im not sure before if I configured it to binary transfer but I >tried it again and make sure to configure it and set it to binary >transfer, still im getting the same result. > > Currently, Im trying to create the database in windows then use the >created database as my base db in linux. Then I'll just have to update >the base db in linux using the different sql statement in postgresql and >transfer it back to windows if the base db can still be read with the >updates. > > On trying to read the file on sqlite3 command line program in windows; >I'll try it later. What version of SQLite are you using on Windows? 3.x where x<3 has problems reading databases created with 3.3 and above due to a small change in file format. Make sure you have 3.3.x on Windows as well. The database itself should otherwise be compatible. Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] sqlite reporting an unexpected version number UPDATE
Okay, here's what broke: yum System: FC4 Yum requires rpm sqlite-3.1.2-3, which provides those 'stale' libraries I referred to. References: http://www.fedoraforum.org/forum/archive/index.php/t-94027.html http://nasrat.livejournal.com/42829.html Chuck Lima wrote: Thanks everyone for your prompt responses. Here is what I found. I was running the correct executable, however I had some stale .so files hanging around down in /usr/lib, while the ones that I wanted to use where in /usr/local/lib . I blew away the ones down in /usr/lib, reran ldconfig and all is right with the world. I'll wait to see what breaks when trying to access those other libraries. Gracias, -Chuck Pam Greene wrote: On 3/7/06, Chuck Lima <[EMAIL PROTECTED]> wrote: Hi, I just compiled and installed sqlite-3.3.4 on FC4 and it seems to be running just fine, except for a few things. sqlite3 -version returns 3.1.4 This in itself is not a problem, but I can't seem to get constraints to work and the documentation indicates that I need version >= 3.3 . Is the -version switch merely misreporting the real version number or does the sqlite-3.3.4.tar.gz file actually contain the 3.1.4 files? There's another possibility. Have you ever had another version of sqlite installed? Try writing a tiny program with the following, or its equivalent, in it: fprintf(stdout, "library info: %d %d\n", SQLITE_VERSION_NUMBER, sqlite3_libversion_number()); The first is the constant defined in the sqlite3.h file your program is using. The second is the value of that constant that was compiled into the library you're using. If they don't match, or if they don't match what sqlite3 -version is reporting, then something's wrong with your installation -- probably you have two copies of sqlite in different places. - Pam
[sqlite] why i can not open the new database i created at AT91ARM9200?
Hi ryan bel brillo, I didn't see sql erro when I create database in linux , but there are thoes sql erroes when I created database in arm-linux after I ported sqlite2.8.17 on AT91ARM9200 successfully. Sqlite is embeddable database. Why can not it create database in arm-linux correctly ? sincerely zhang jie ryan bel brillo <[EMAIL PROTECTED]> 写道： Hi zhang jie, I don���t think there was a problem on the creation and updating of the database. You see, I log every sql statement execute from database creation and updates, and I didn���t see any sql error or any error in that case. Also, I open the database using command line in linux and it works fine. -Original Message- From: 陆�� [mailto:[EMAIL PROTECTED] Sent: Monday, March 13, 2006 7:39 PM To: sqlite-users@sqlite.org Subject: [sqlite] why i can not open the new database i created at AT91ARM9200? Hi ryan bel brillo, First of all,thank you for your reply. The problem is not a compatibility with database created in linux and windows . I downloaded source code sqlite-2.8.17.tar.gz through your website under windows OS. But I gunziped and cross-compiled it for AT91ARM9200(development board) under linux OS. I run the binary file "sqlite" at AT91ARM9200. #./sqlite test.sqlite //Am I creating a new database"test.sqlite"? sqlite>CREATE TABLE test(name varchar(80); SQL erro:unable to open database test.sqlite # #./sqlite test.db //Am I creating a new database"test.db"? sqlite>CREATE TABLE test(name varchar(80); SQL erro:unable to open database test.db # # vi test.sqlite # chmod 777 test.sqlite # ./sqlite test.sqlite sqlite>CREATE TABLE test(name varchar(80)); SQL erro:can not write to test.sqlite,it is readonly file. sqlite> # vi test.db # chmod 777 test.db # ./sqlite test.db sqlite>CREATE TABLE test(name varchar(80)锛�num smallint); SQL erro:can not write to test.db,it is readonly file. sqlite> So why i can not open the new database i created at AT91ARM9200? I guess i didn't create database correctly,aren't you? sincerely zhang jie ryan bel brillo ��锛? Hi all, Im new in this list and with sqlite. Im not sure if this topic have been discussed before. Anyway, I have this problem regarding sqlite. I created a c program that would create a new sqlite database from some sql file then update it every five minutes using sql statement stored in a postgresql database. Im using a version 3.3.2 sqlite database and sqlite3 library in c. The sqlite database and c program was executed in a linux OS. When I tried to open my created database in a SQLite database browser in windows It shows nothing but in linux it shows the correct tables and data. Also, I create a sqlite database in windows then open it in linux it works fine. Also, I created a simple sqlite database manually but it still cant be read in windows. Is there a compatibility problem with database created in linux and windows? I remember this problem on occurs on sqlite version 2.6.2 or earlier.. Or is there a problem in the way I create the database? The sqlite browser im using can read sqlite database version 3.x. Thanks - ��1G���璐归��绠辩�惧鹃�插句�?lt;br> - 雅虎1G免费邮箱百分百防垃圾信 雅虎助手-搜索、杀毒、防骚扰
