Re: [sqlite] Error: malformed database schema - near ")"
On Fri, Aug 8, 2008 at 5:21 PM, Mark Easton <[EMAIL PROTECTED]> wrote: > Hi, > > We are using SQLite version 3.3.8 on Debian linux. Every now and again we > are getting corrupted databases. We suspect it is because the computer is > being turned off mid database operation. > > We have given full permissions to the folder the database is in and to the > database itself. > > The most important question - is how can I recover this database? When I > type in "pragama integrity_check" it still returns the error "Error: > malformed database schema - near ")"". I seem to recall someone else having a similar problem, once. Can you open the database and do "select * from sqlite_master;", then sanity check the "sql" column for syntax errors? Several months ago, someone had managed to create a bunch of tables with an extra "," at the end due to a parser bug, and when they upgraded the new parser rejected the old schema's SQL. -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Error: malformed database schema - near ")"
Hi, We are using SQLite version 3.3.8 on Debian linux. Every now and again we are getting corrupted databases. We suspect it is because the computer is being turned off mid database operation. We have given full permissions to the folder the database is in and to the database itself. The most important question - is how can I recover this database? When I type in "pragama integrity_check" it still returns the error "Error: malformed database schema - near ")"". The second question is what can I do to prevent this problem occurring? Many thanks for your help. Mark ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] c# .net beginTrans...where is endtrans?
using (DbTransaction trans = connection.BeginTransaction())
{
// loop goes here
//
// loop ends here
trans.Commit();
}
Best place to get answers to .NET-related SQLite questions (and more) is at
http://sqlite.phxsoftware.com/forums
Robert
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of danblack101
Sent: Friday, August 08, 2008 2:01 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] c# .net beginTrans...where is endtrans?
Hi,
I have a c# program which has to process a large amount of updates at one
time.
I have previously used:
connection.beginTransaction()
commandObject
update SQLite with command object
commandObject.Transaction.Commit
This worked nicely.
But now I have modulised my code so that I have a generic
updateFunction(String) inside this function I create a commandObject and do
the update which is fine, but
Now I have reached a point where I want to process a large amount of updates
therefore need transaction boundaries. So now I have
connection.beginTransaction()
updateFunction(String)
How can I commit, I dont have a command object that performs the
commit/endTrans, I dont want to commit inside the updateFunction ?
Any help is greatly appreciated
Dan
--
View this message in context:
http://www.nabble.com/c--.net-beginTrans...where-is-endtrans--tp18898927p188
98927.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] c# .net beginTrans...where is endtrans?
Hi, I have a c# program which has to process a large amount of updates at one time. I have previously used: connection.beginTransaction() commandObject update SQLite with command object commandObject.Transaction.Commit This worked nicely. But now I have modulised my code so that I have a generic updateFunction(String) inside this function I create a commandObject and do the update which is fine, but Now I have reached a point where I want to process a large amount of updates therefore need transaction boundaries. So now I have connection.beginTransaction() updateFunction(String) How can I commit, I dont have a command object that performs the commit/endTrans, I dont want to commit inside the updateFunction ? Any help is greatly appreciated Dan -- View this message in context: http://www.nabble.com/c--.net-beginTrans...where-is-endtrans--tp18898927p18898927.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] side effect of unary + surprise
I opened ticket 3279 because of an unexpected difference in behavior between the following selects when col1 is defined as text, but only contains integers. (The first and third return the relevant rows, but the middle query returns nothing.) sqlite> select * from test where col1=1; sqlite> select * from test where +col1=1; sqlite> select * from test where col1='1'; This bug was closed because the unary + also has the effect of eliminating affinity, thus preventing coercion so an integer != text, and thus the code works as designed. It seems to me, then, that the code fails the test of least surprise. For example, the page http://www.sqlite.org/optoverview.html (section 6.0) indicates that the unary + is a no-op and only has the effect of preventing the term from constraining an index. It seems to me that either the above webpage should be updated or coercion should still occur with the use of the unary +. Steve Friedman ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fts3 and JOIN sometimes results in inability to use MATCH operator
Hello! В сообщении от Wednesday 06 August 2008 17:42:19 Alexandre Courbot написал(а): > I first noticed that behavior in a much larger query with plenty of > joins. Reordering them gives me a query that works. Is there any > reason for this? I don't know sqlite enough to affirm this is a bug, > but this looks suspicious to me. Shall I fill in a trac ticket? I think you may create a trac ticket. Best regards, Alexey. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems with Create Table and a prepare statement
2008/8/8 Daniel Hellsson <[EMAIL PROTECTED]> > 2008/8/8 Igor Tandetnik <[EMAIL PROTECTED]> > > "Daniel Hellsson" >> <[EMAIL PROTECTED]> wrote in >> message news:[EMAIL PROTECTED]<[EMAIL PROTECTED]> >> > I am writing unit tests for my database functions. >> > In one test I'm opening the database. >> > Then I open the database again with a new pointer. >> > With that pointer I create the table with >> > >> > "CREATE TABLE IF NOT EXISTS foo \ >> > ( id INTEGER PRIMARY KEY, \ >> > bar_id INTEGER, \ >> > hoopy INTEGER, \ >> > frood_id INTEGER )" >> > >> > then I close that connection (with sqlite3_close) and with the old >> > pointer I do an sqlite3_prepare_v2 with >> > >> > "SELECT frood_id,hoopy FROM ingredients WHERE bar_id=1" >> > >> > This is where I get an SQLITE_SCHEMA error (from prepare) with the >> > error message: table 'foo' does not exist. >> >> What is the relation between 'foo' and 'ingredients' ? The statement >> never mentions foo, as far as I can tell. >> >> Assuming it's a typo, this is a known issue. SQLite caches the database >> schema when opening the database, and re-reads it every time it detects >> the schema has changed. But sqlite3_prepare* works off of the cached >> schema: it would be too expensive to actually read the file on disk >> every time a statement is prepared. >> >> So changes to the schema made on a different connection aren't visible >> until the database file is read by any statement. If you want to make >> sure you pick up the latest changes, run something like "select * from >> sqlite_master where 0;" >> >> Igor Tandetnik >> >> > Sorry for the confusion. I was trying to obfuscate what I was doing (this > is a commercial non-open-source project). :-) > > > > Ingredients was supposed to be 'foo', so it is indeed a typo. of sorts. :) > > > > Ah, ok, so this is a known problem. It was surprising to me since > everything else worked so nicely and only this one thing did not work. I > will probably have to rewrite my tests so that they open new connections to > the database instead of using existing connections like they do now. It is > only for convenience that I do that now. > > > A thousand thanks to you for your quick reply! > > > > /Daniel > Update: I tested your "empty" select statement as provided above, and the cache was not updated. Not until I actually selected something (type='table') was the cache updated. Isn't there any proper way to invalidate the cache? Like a sqlite3_refresh() or something. /Daniel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems with Create Table and a prepare statement
2008/8/8 Igor Tandetnik <[EMAIL PROTECTED]> > "Daniel Hellsson" > <[EMAIL PROTECTED]> wrote in > message news:[EMAIL PROTECTED]<[EMAIL PROTECTED]> > > I am writing unit tests for my database functions. > > In one test I'm opening the database. > > Then I open the database again with a new pointer. > > With that pointer I create the table with > > > > "CREATE TABLE IF NOT EXISTS foo \ > > ( id INTEGER PRIMARY KEY, \ > > bar_id INTEGER, \ > > hoopy INTEGER, \ > > frood_id INTEGER )" > > > > then I close that connection (with sqlite3_close) and with the old > > pointer I do an sqlite3_prepare_v2 with > > > > "SELECT frood_id,hoopy FROM ingredients WHERE bar_id=1" > > > > This is where I get an SQLITE_SCHEMA error (from prepare) with the > > error message: table 'foo' does not exist. > > What is the relation between 'foo' and 'ingredients' ? The statement > never mentions foo, as far as I can tell. > > Assuming it's a typo, this is a known issue. SQLite caches the database > schema when opening the database, and re-reads it every time it detects > the schema has changed. But sqlite3_prepare* works off of the cached > schema: it would be too expensive to actually read the file on disk > every time a statement is prepared. > > So changes to the schema made on a different connection aren't visible > until the database file is read by any statement. If you want to make > sure you pick up the latest changes, run something like "select * from > sqlite_master where 0;" > > Igor Tandetnik > > Sorry for the confusion. I was trying to obfuscate what I was doing (this is a commercial non-open-source project). :-) Ingredients was supposed to be 'foo', so it is indeed a typo. of sorts. :) Ah, ok, so this is a known problem. It was surprising to me since everything else worked so nicely and only this one thing did not work. I will probably have to rewrite my tests so that they open new connections to the database instead of using existing connections like they do now. It is only for convenience that I do that now. A thousand thanks to you for your quick reply! /Daniel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems with Create Table and a prepare statement
"Daniel Hellsson" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I am writing unit tests for my database functions. > In one test I'm opening the database. > Then I open the database again with a new pointer. > With that pointer I create the table with > > "CREATE TABLE IF NOT EXISTS foo \ > ( id INTEGER PRIMARY KEY, \ > bar_id INTEGER, \ > hoopy INTEGER, \ > frood_id INTEGER )" > > then I close that connection (with sqlite3_close) and with the old > pointer I do an sqlite3_prepare_v2 with > > "SELECT frood_id,hoopy FROM ingredients WHERE bar_id=1" > > This is where I get an SQLITE_SCHEMA error (from prepare) with the > error message: table 'foo' does not exist. What is the relation between 'foo' and 'ingredients' ? The statement never mentions foo, as far as I can tell. Assuming it's a typo, this is a known issue. SQLite caches the database schema when opening the database, and re-reads it every time it detects the schema has changed. But sqlite3_prepare* works off of the cached schema: it would be too expensive to actually read the file on disk every time a statement is prepared. So changes to the schema made on a different connection aren't visible until the database file is read by any statement. If you want to make sure you pick up the latest changes, run something like "select * from sqlite_master where 0;" Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problems with Create Table and a prepare statement
Hi all. I have a problem. Unfortunately I have not been able to create a short example to replicate my problem and the code itself is very long and complicated. Background: - My code is not multithreaded. - However, I am opening the same file more than once. I am writing unit tests for my database functions. In one test I'm opening the database. Then I open the database again with a new pointer. With that pointer I create the table with "CREATE TABLE IF NOT EXISTS foo \ ( id INTEGER PRIMARY KEY, \ bar_id INTEGER, \ hoopy INTEGER, \ frood_id INTEGER )" then I close that connection (with sqlite3_close) and with the old pointer I do an sqlite3_prepare_v2 with "SELECT frood_id,hoopy FROM ingredients WHERE bar_id=1" This is where I get an SQLITE_SCHEMA error (from prepare) with the error message: table 'foo' does not exist. The scary part is that if I inject a new prepare/step sequence where I get all tables from sqlite_master (in which 'foo' is listed), then suddenly the select call works! I think there is some SQLITE processing here that is either not committed properly to the database or not finished before the sqlite3_exec call returns. Anyone with any similar experiences? Thank ye all kindly for any help! /Daniel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem building on Solaris
"Larry Martell" <[EMAIL PROTECTED]> writes: > I'm trying to build the amalgamation on Solaris, and it's failing: > >> make > cd . && /bin/bash > /home/martella/sqlite3/sqlite-amalgamation-3.6.1/missing --run > aclocal-1.9 > /usr/local/share/aclocal/winsz.m4:5: warning: underquoted definition > of AM_HEADER_TIOCGWINSZ_NEEDS_SYS_IOCTL > run info '(automake)Extending aclocal' > or see http://sources.redhat.com/automake/automake.html#Extending%20aclocal > /usr/local/share/aclocal/vorbis.m4:9: warning: underquoted definition > of XIPH_PATH_VORBIS > /usr/local/share/aclocal/termios.m4:5: warning: underquoted definition > of AM_SYS_POSIX_TERMIOS > /usr/local/share/aclocal/strtod.m4:30: warning: underquoted definition > of AM_FUNC_STRTOD > aclocal: file `/usr/local/share/aclocal/sdl.m4' does not exist > make: *** [aclocal.m4] Error 1 Try to install the latest Autotools m4, autoconf, automake from sunfreeware (I suppose you use gnu gcc). When building sqlite-amalgamation I use: CFLAGS="-Os -DSQLITE_ENABLE_FTS3=1 -R/usr/local/lib" ./configure -- Radek ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proposed removal of (mis-)feature
Hi, palmer ristevski wrote: > He is my vote. > I like the fact that conventions #1 and #2 make things compatible with other > databases. > Does #3, by chance, do the same with another database not mentioned > (eg. Oracle). > > If it does not have any compatibility with any other database, > ONLY then I say that one should drop #3 quoting convention. > > Palmer > I believe that MySQL allows double quotes to be used as string delimiters by default, but then this would not be a compatibility break with MySQL since MySQL also supports the standard use of single quotes for this purpose by default, and does not have the same "identifier, else a string" behaviour anyway. Oh, and +1 to removing #3, if it matters now after so many votes in that direction :) Regards, Eugene ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
