Re: [sqlite] Intermittent SQLITE_CANTOPEN on Windows
http://www.mail-archive.com/sqlite-users@sqlite.org/msg34453.html On Mon, Oct 13, 2008 at 6:52 PM, Doug <[EMAIL PROTECTED]> wrote: > I'm using SQLite 3.5.6 on Windows and intermittently get SQLITE_CANTOPEN > when doing an insert. When that fails, I can use the debugger to go back up > and step through the same lines again (using the same database handle - > nothing opened or closed in between) and it will work. > > I am using sqlite3_bind_blob with the following: INSERT OR REPLACE INTO > BlobTable (BlobKey, BlobVal) Values ('exampleKey', ?) > in case that makes any difference (the SQLITE_CANTOPEN code is returned from > sqlite3_step). > > I doubt this has anything to do with SQLite as it's been working perfectly > for years, but I also can't figure out what has changed on my system such > that this would be happening now. > > Thanks in advance for any ideas. > > Doug > > > > ___ > 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] Efficient updating of arbitrary columns.
On Oct 14, 2008, at 4:26 PM, Peter van Hardenberg wrote: > Hi all, > > We're using SQLite to back the media collection in Songbird, and our > database includes a table which often has a varying set of columns > updated. At the moment, I'm using a set of prepared statements that > look like this: > > UPDATE table SET columnN = ? WHERE table_id = ?; > > I talked with Habbie in IRC (who recommended I post to the list) and > he advised trying a query like this: > > UPDATE table SET > column1 = CASE 1=? THEN ? ELSE column1 END, > column2 = CASE 1=? THEN ? ELSE column2 END, > column3 = CASE 1=? THEN ? ELSE column3 END > WHERE table_id = ?; > > This would theoretically allow me to prepare a single query once and > to simply bind a "1" and a value to each property I want to change > with the rest staying the same. Unfortunately, the query executes > very slowly, about an order of magnitude slower than simply piecing > together the query as a string each time and compiling it from > scratch. I tried the experiment on Linux. I used: CREATE TABLE t1(a,b,c,d); -- insert over 1000 rows of data. UPDATE t1 SET a = CASE WHEN 1=$ax THEN $av END, b = CASE WHEN 1=$bx THEN $bv END, c = CASE WHEN 1=$cx THEN $cv END, d = CASE WHEN 1=$dx THEN $dv END WHERE rowid=$rid where the bx, bv, cx, cv, dx, and dv values were all unbound and thus understood as NULL. I prepared the statement once and reused it to do 1000 inserts. The average time was 26.058 milliseconds. Then I did: UPDATE t1 SET a=$av WHERE rowid=$rid I prepared the statement separately 1000 times. The average time was 55.458 milliseconds. Everything was done inside a single transaction. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Efficient updating of arbitrary columns.
Hi all, We're using SQLite to back the media collection in Songbird, and our database includes a table which often has a varying set of columns updated. At the moment, I'm using a set of prepared statements that look like this: UPDATE table SET columnN = ? WHERE table_id = ?; I talked with Habbie in IRC (who recommended I post to the list) and he advised trying a query like this: UPDATE table SET column1 = CASE 1=? THEN ? ELSE column1 END, column2 = CASE 1=? THEN ? ELSE column2 END, column3 = CASE 1=? THEN ? ELSE column3 END WHERE table_id = ?; This would theoretically allow me to prepare a single query once and to simply bind a "1" and a value to each property I want to change with the rest staying the same. Unfortunately, the query executes very slowly, about an order of magnitude slower than simply piecing together the query as a string each time and compiling it from scratch. On IRC, Habbie expressed some surprise that it was so slow. While I'm no expert with analyzing the VM output, I suspect SQLite is making some suboptimal decisions about how, or how often, to pull the data required to execute the query. Any suggestions on how to improve the performance of this query, or another one like it? Thanks, -pvh ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problem : SQLite Database error
Heya guys, iv got a database which was made my a program called BluePhoneElite, its a Mac OS X piece of software which pairs with a mobile phone over bluetooth to allow messages and calls to be managed from the computer... awesome piece of software untill it breaks. Somehow the database has become corrupted, but my experience with programming and SQL is kinda limited, so turn to you guys... There are two databases, one for messages (text's) and the other for calls ! both are .sqlite extensions (e.g messages.sqlite & calls.sqlite) Iv managed to open them in SQLite Expert Personal 1.7.31 and look within the data, this is where it gets annoying, everything seems to look okay to me the data is perfectly readable (aka, no random symbols or things which look out of place)... But, when i ask SQLite Personal to check the integreity of the databases i get this; Messages.sqlite *** in database main *** On tree page 7686 cell 67: invalid page number 7692 On tree page 7686 cell 67: Child page depth differs On tree page 7686 cell 68: invalid page number 7693 On tree page 7686 cell 69: invalid page number 7694 On tree page 7686 cell 70: invalid page number 7701 On tree page 7686 cell 71: invalid page number 7702 On tree page 7686 cell 72: invalid page number 7695 On tree page 7686 cell 73: invalid page number 7697 On tree page 7686 cell 74: invalid page number 7700 On tree page 7686 cell 75: invalid page number 7696 On tree page 7686 cell 76: invalid page number 7698 On page 7686 at right child: invalid page number 7699 Calls.sqlite *** in database main *** On page 273 at right child: invalid page number 361 Suffice to say these messages dont mean much to me, anyone else have any ideas ? Also, when i scroll through the data, i keep getting alert messages popping up saying "The database disk image is malformed" and the details of this error just says Exception message: The database disk image is malformed Exception class: ESQLiteException Date/Time: 2008-10-11 23:02:35.734 Im kinda desperate to get these databases back into a working state, can anyone help ? Regards Aaron ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] transaction question
On Oct 14, 2008, at 9:06 PM, Gene Allen wrote: > I have a large sqlite database and I'm inserting a bunch of records > into it. > My question is this..shouldn't the -journal be getting larger since > I've > wrapped all the inserts inside a transaction? > > > > I'm watching the file sizes change and the main file is getting > larger, but > the journal files is staying at 44kb.Is there something > different I have > to do while large Thanks > > > > Gene Allen > > ___ > 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] Foreign Key Triggers: ABORT, FAIL, or ROLLBACK
On Oct 14, 2008, at 1:07 AM, John Belli wrote: > What is the recommended conflict to raise during an FK enforcement > trigger? I'm not asking about how to create the triggers, I've figured > that part out; I just want to know which conflict should be used. I > think I'm asking, which do other db systems tend to use? ABORT seems right to me. Causes the current statement to have no effect, but does not rollback the current transaction. Dan. > > > > JAB > -- > John A. Belli > Software Engineer > Refrigerated Transport Electronics, Inc. > http://www.rtelectronics.com > > ___ > 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] transaction question
I have a large sqlite database and I'm inserting a bunch of records into it. My question is this..shouldn't the -journal be getting larger since I've wrapped all the inserts inside a transaction? I'm watching the file sizes change and the main file is getting larger, but the journal files is staying at 44kb.Is there something different I have to do while large http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compile SQLite3 for MS Windows Driver Kit user-mode application
Hi again, First, I am not an expert in WDK programming. But there are a few nice samples very close to what I need and thus I tried to compile a WDK sample with the sqlite3 DLL. The sample is a minifilter driver, which consists of two parts: the minifilter driver itself, which runs in kernel-mode and a user-mode application that catches the filtered requests through a communication port bridging the kernel- and user-mode. I changed the user-mode application to use the sqlite3 api. To start with, I didn't do much in the code, just open/close a database. In order to build the user-mode application, I generated a lib-file for the sqlite3.dll. However, the WDK environment ignored the lib-file entirely whatever I tried. I couldn't figure out why but I guess this does not really work. For example, the standard C libraries and headers are all shipped separately with the WDK. My second approach was to compile sqlite3 myself within the WDK. But the WDK build environment requires warnings to be treated as errors which in my opinion makes perfect sense. (I woudln't believe 10 agreeing programmers either; I have seen examples where all of them agreed that there would be no coding error. But of course this was the case. Btw, why checking intellectually that the conversions are safe when fixing it properly takes no time in comparison?? I don't believe it is checked.) Also, the number of warnings/errors is higher than in normal Win32 build (679 warnings to 524 warnings on warning level 4). Plus 1 error regarding localtime_s (C4013, undefined). This error is crazy as the function is in the defined by the WDK as well as included to the source. But this is a side note only. Mark Spiegel wrote: > Not sure why the Win32 DLL is not compatible. I would think it should > be. You might want to work that out first. Can you elaborate? > > As for compiling with the WDK, it can be done. The amalgamated source > is > > best.> > The flood of warnings is a pain. SQLite dev claims they are all > > > spurious, but with so many I wouldn't venture to guess how they can tell.> > > > For W32, you should be able to disable "treat warnings as errors" for > > > just the sqlite3.c file if your development organization allows this. > If > > you are building 64 bits, then you have more work to do. The last > time I > > ported in new SQLite source, it still cast 32 bit integers into > 64 bit > > pointers. The WDK compiler isn't going to allow this without > some source > > modifications. I did write a ticket so this might be > fixed. As of the > > last time I checked, it was not.> > Bjorn Rauch wrote:> > Hello,> > > > Has > > anybody tried to compile SQLite3 with the MS WDK? The Win32 > > DLL is not compatible as far as I understand and recompiling with the > > WDK is > necessary. But using the source code as is results in many > > warnings (mostly conversion errors). The WDK does not tollerate these.> > > > > > Best regards,> > Bjorn _ Explore the seven wonders of the world http://search.msn.com/results.aspx?q=7+wonders+world=en-US=QBRE ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] problem with sqlite3_exec() and select sql statemant
Use the correct SQL delimiter for a literal - single quotes, e.g. 'google.com'. Hari wrote: > Hi as i am new to sqlite. > I have problem when i am using sqlite3_exec() function with select > as i am using sqlite3_open() to opening database > then creating table and inserting some information using sqlite3_exec() > and 'create table' and 'insert into' > then again if i use sqlite3_exec() with select like > my sql statement is : select * from my_table where > Primarykey="google.com" > where google.com primary key and its related information is not already > in my table but it is returning > SQLITE_OK...even it must be return some error as that primary key is not > in my table. > .how should i get the error...when using select and if primary key will > not be there ..after creating table with using sqlite3_exec() function + > select sql statemant. > > Thanks in advance for any ideas > Harioum > ___ > 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] sqlite3_open() problem
I run sqlite3.exe under Windows 95 like this: C:\sqlite3 test sqlite>create table t(id); Then sqlite3 crashes. Leandro dos Santos Ribeiro wrote: > Leandro dos Santos Ribeiro wrote: > >> D. Richard Hipp wrote: >> >> >>> On Oct 10, 2008, at 4:59 PM, <[EMAIL PROTECTED]> <[EMAIL PROTECTED] >>> > wrote: >>> >>> >>> >>> Hello Team. I have a problem with running SQLite. I am running linux 2.6.17 on *ARM* and basically problem is that my application is crushing on *sqlite3_open*() function while the sqlite3 command shell is running without problems. >>> The command-line shell uses sqlite3_open() too. So if it works there, >>> I do not understand why it is not working in your program. Have you >>> run your program in a debugger to see exactly where it is crashing? >>> >>> >>> >>> D. Richard Hipp >>> [EMAIL PROTECTED] >>> >>> >>> >>> ___ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >>> >>> >>> >> Hello D. Richard Hipp, >> >> Yes, I did run the program in a debugger and it is crashing when the >> program tries to execute the sqlite3_open() function. >> It seems that I'm not the only one with that problem, but I didn't find >> the answer yet. >> Thanks . >> >> >> Best regards. >> >> Leandro S. Ribeiro >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> >> > Hello everybody, > > I found the problem with the sqlite3_open() crashing. > To solve it I just added the libs libpthread and libdl to my project. > Thanks to all. > > Best regards > Leandro S. Ribeiro > ___ > 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] Foreign Key Triggers: ABORT, FAIL, or ROLLBACK
What is the recommended conflict to raise during an FK enforcement trigger? I'm not asking about how to create the triggers, I've figured that part out; I just want to know which conflict should be used. I think I'm asking, which do other db systems tend to use? JAB -- John A. Belli Software Engineer Refrigerated Transport Electronics, Inc. http://www.rtelectronics.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Testing for existence of extension
Am Tuesday, 14. October 2008 schrieb Roger Binns: > You may want to create a ticket asking for a method of getting the names > of available extensions. See #3436. Thanks for the answer. Christoph ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] problem with sqlite3_exec() and select sql statemant
Hello Hariom, - use single quotes around text constants: select * from my_table where Primarykey='google.com' - use sqlite3_prepare and sqlite3_step to select data. First call sqlite3_prepare for your statement and then sqlite3_step in al loop until it returns SQLITE_DONE - It is not an error if a select statement returns no rows because of a where clause. In this case, the first call to sqlite3_stepreturns SQLITE_DONE Martin Hari schrieb: > Hi as i am new to sqlite. > I have problem when i am using sqlite3_exec() function with select > as i am using sqlite3_open() to opening database > then creating table and inserting some information using sqlite3_exec() > and 'create table' and 'insert into' > then again if i use sqlite3_exec() with select like > my sql statement is : select * from my_table where > Primarykey="google.com" > where google.com primary key and its related information is not already > in my table but it is returning > SQLITE_OK...even it must be return some error as that primary key is not > in my table. > .how should i get the error...when using select and if primary key will > not be there ..after creating table with using sqlite3_exec() function + > select sql statemant. > > Thanks in advance for any ideas > Harioum > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- * Codeswift GmbH * Traunstr. 30 A-5026 Salzburg-Aigen Tel: +49 (0) 8662 / 494330 Mob: +49 (0) 171 / 4487687 Fax: +49 (0) 12120 / 204645 [EMAIL PROTECTED] www.codeswift.com / www.swiftcash.at Codeswift Professional IT Services GmbH Firmenbuch-Nr. FN 202820s UID-Nr. ATU 50576309 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Joining 2 views
On Oct 14, 2008, at 4:39 AM, Guenther Schmidt wrote: > Hi, > > unfortunately I've hit a point where I run a query where one VIEW > joins > another VIEW. > > Both views are rather large and since there is no index on the fields > where they join the query takes very very long. (About 15 min). > > Does anybody here know a solution to this problem? What are the view definitions and what query are you running? It may be that you can add an index to one of the underlying real tables that will help. Dan. > Best regards > > Günther > > ___ > 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] problem with sqlite3_exec() and select sql statemant
Hi as i am new to sqlite. I have problem when i am using sqlite3_exec() function with select as i am using sqlite3_open() to opening database then creating table and inserting some information using sqlite3_exec() and 'create table' and 'insert into' then again if i use sqlite3_exec() with select like my sql statement is : select * from my_table where Primarykey="google.com" where google.com primary key and its related information is not already in my table but it is returning SQLITE_OK...even it must be return some error as that primary key is not in my table. .how should i get the error...when using select and if primary key will not be there ..after creating table with using sqlite3_exec() function + select sql statemant. Thanks in advance for any ideas Harioum ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users