Re: [sqlite] Why don't I get a syntax error?
+-Le 04/03/2004 16:36 -0800, Will Leshner a dit : | Shouldn't this SQL produce a syntax error (notice the lack of a closing | quote): | | SELECT * FROM foo WHERE a='1 Well, at that point the statement is not terminated, a could contain newlines, so, '1 ' is valid :) What's your point ? -- Mathieu Arnold - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Bad Database file
Hi, I just checked a database which was giving me problems using PRAGMA integrity_check; and discovered it was corrupt. sqlite> PRAGMA integrity_check; *** in database main *** List of tree roots: invalid page number 24 List of tree roots: invalid page number 23 What would be the reasons for a database to go corrupt? Anyone have any ideas regards Greg O - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Bad Database file
Greg Obleshchuk wrote: sqlite> PRAGMA integrity_check; *** in database main *** List of tree roots: invalid page number 24 List of tree roots: invalid page number 23 What would be the reasons for a database to go corrupt? Anyone have any ideas Versions of SQLite prior to 2.8.12 contain bugs that can (in rare circumstances) corrupt the database file. See http://www.sqlite.org/cvstrac/tktview?tn=529 http://www.sqlite.org/cvstrac/tktview?tn=552 http://www.sqlite.org/cvstrac/tktview?tn=561 Or, there could be undiscovered bugs in SQLite. The other thing is that a process can go haywire and either write garbage directly into your database file, or into memory used by the SQLite library. An example of the latter happened to me a few days ago. XFree86 segfaulted on me. After I logged back in, I found that one of my databases was no longer working correctly. Further investigation showed that one the CREATE TABLE statements that SQLite stores in the SQLITE_MASTER table had been corrupted, preventing SQLite from initializing correctly. The correct SQL should have been: CREATE TABLE _Peer( peerid INTEGER PRIMARY KEY, -- ID number for this peer dbproxy name TEXT UNIQUE, -- name of the peer dbproxy ip TEXT UNIQUE, -- IP address of the peer contact TIME, -- Time of last known contact attempt TIME, -- Time of last attempt to contact desired BOOLEAN -- True if we want this link to be up ) But after the XFree86 crash, that entry contained the following text instead: CREATE TABLE _Peer( peerid INTEGER PRIMARY KEY, -- ID number for this peer dbproxy name TEXT UNIQUE, -- name of the peer dbproxy ip TEXT UNIQUE, -- IP address of the peer nnection to :0.0 broken (explicit kill or server shutdown). attempt TIME, -- Time of last attempt to contact desired BOOLEAN -- True if we want this link to be up ) Luckily for me, the overwrite occurred in the middle of a text field where it could be corrected. If the overwrite had occurred in some other part of the database file it might have corrupted the file structure making recovery more difficult. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Compute clauses
In Sybase there is a transact SQL extension clause called "compute" so if I was to write something like: select client_id, company from client where client_id <10 order by group_code compute sum(client_id) I would get a result like this: client_id company --- -- 1 Company 1 2 Company 2 3 Company 3 4 Company 4 5 Company 5 6 Company 6 7 Company 7 8 Company 8 9 Company 9 Compute Result: --- 45 is there anything like this in sqllite which can give me a computed result ? Thank you Jeff Edwards - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] Compute clauses
Yup. SELECT SUM(client_id) FORM client; That will return the sum of client_id; Regards, PeterP - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Compute clauses
I appreciate your response but perhaps you misunderstand me. What I illustrated was a way to have totals after the detail. The way to get this in Sybase is to use the "compute" clause. I am after something similar in sqlite if it exists. Thanks, Jeff Edwards On 05/03/2004, at 8:33 PM, Peter Pistorius wrote: Yup. SELECT SUM(client_id) FORM client; That will return the sum of client_id; Regards, PeterP - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Why don't I get a syntax error?
On Mar 5, 2004, at 2:14 AM, Mathieu Arnold wrote: Well, at that point the statement is not terminated, a could contain newlines, so, '1 ' is valid :) What's your point ? Right. I didn't word my question very well. Let me put it this way. Say I have an SQLite wrapper and a user of my wrapper makes a mistake and types the above SQL believing they have terminated the quoted but haven't. How can I, in my wrapper, determine that there is something incomplete about that SQL. I know there is a function in the SQLite API that checks for completeness, but I don't believe that will help me in this case. Thanks for any help. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Why don't I get a syntax error?
Will Leshner wrote: Right. I didn't word my question very well. Let me put it this way. Say I have an SQLite wrapper and a user of my wrapper makes a mistake and types the above SQL believing they have terminated the quoted but haven't. How can I, in my wrapper, determine that there is something incomplete about that SQL. I know there is a function in the SQLite API that checks for completeness, but I don't believe that will help me in this case. Append a single ';' character to the string then call sqlite_complete(). -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Compute clauses
He didn't misunderstand you. The only way to get the result you indicated in SQLite is to use 2 SQL statements. If you're worried that another process might change the data between the 2 statements, put them in a transaction: begin;select data...;select sum(X)...;end; If you're worried about efficiency or speed, since the table will be scanned twice, it might (or might not) be better to do the sum in your host language. Regards - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
