[sqlite] solving the infamous, irritating "database locked" problem
I've been a reading a lot on the "database locked" problem, but still
need guidance trying to locate the source of my problem.
environment: DBI/DBD::SQLite (latest versions) with SQLite3 (3.2.1) on
Mac OS X (10.3.9).
I am trying to update a table via the web. The UPDATE in question is
the very first (and the only) DML statement. Yet, I get the "database
locked" error.
The sequence of commands is
my $sql = "UPDATE statement";
my $sth = $dbh->prepare(qq{$sql});
$sth->execute;
$dbh->commit;
My question is: could I set some kind of "trace" that tells me what is
going on with SQLite?
Many thanks.
--
Puneet Kishor
Re: [sqlite] solving the infamous, irritating "database locked" problem
I'm not familar with the wrapper you're using, but don't you have a
commit without a matching begin? Did you establish a lock on the table
before
trying to update? Are you updating a table that you currently are reading from?
As in:
select * from t;
for each result
update t set field = blah;
next
On 6/9/05, Puneet Kishor <[EMAIL PROTECTED]> wrote:
> I've been a reading a lot on the "database locked" problem, but still
> need guidance trying to locate the source of my problem.
>
> environment: DBI/DBD::SQLite (latest versions) with SQLite3 (3.2.1) on
> Mac OS X (10.3.9).
>
> I am trying to update a table via the web. The UPDATE in question is
> the very first (and the only) DML statement. Yet, I get the "database
> locked" error.
>
> The sequence of commands is
>
> my $sql = "UPDATE statement";
> my $sth = $dbh->prepare(qq{$sql});
> $sth->execute;
> $dbh->commit;
>
> My question is: could I set some kind of "trace" that tells me what is
> going on with SQLite?
>
> Many thanks.
>
> --
> Puneet Kishor
>
>
--
---
You a Gamer? If you're near Kansas City:
Conquest 36
https://events.reddawn.net
The Castles of Dereth Calendar: a tour of the art and architecture of
Asheron's Call
http://www.lulu.com/content/77264
Re: [sqlite] solving the infamous, irritating "database locked" problem
On Jun 9, 2005, at 9:35 AM, Jay Sprenkle wrote:
I'm not familar with the wrapper you're using, but don't you have a
commit without a matching begin?
The commit is in the "wrapper," not in the SQL. When making a database
connection, I specified to turn AutoCommit to OFF. Hence, I have to
explicitly $dbh->commit (unless I understand DBI incorrectly, in which
case, I hope some Perl-ers on this list might correct me).
Did you establish a lock on the table
before
trying to update?
Nope, nothing that I did to create such a lock. The UPDATE in question
is the very first statement.
Are you updating a table that you currently are reading from?
As in:
select * from t;
for each result
update t set field = blah;
next
Nope. the sequence I've described below is exactly how it goes. Create
an UPDATE statement. Execute it. Commit it. Wait for the error message
to pop up telling me that the database is locked.
Haumph!
On 6/9/05, Puneet Kishor <[EMAIL PROTECTED]> wrote:
I've been a reading a lot on the "database locked" problem, but still
need guidance trying to locate the source of my problem.
environment: DBI/DBD::SQLite (latest versions) with SQLite3 (3.2.1) on
Mac OS X (10.3.9).
I am trying to update a table via the web. The UPDATE in question is
the very first (and the only) DML statement. Yet, I get the "database
locked" error.
The sequence of commands is
my $sql = "UPDATE statement";
my $sth = $dbh->prepare(qq{$sql});
$sth->execute;
$dbh->commit;
My question is: could I set some kind of "trace" that tells me what is
going on with SQLite?
Many thanks.
--
Puneet Kishor
--
---
You a Gamer? If you're near Kansas City:
Conquest 36
https://events.reddawn.net
The Castles of Dereth Calendar: a tour of the art and architecture of
Asheron's Call
http://www.lulu.com/content/77264
--
Puneet Kishor
[sqlite] Problem with ORDER BY involving ROUND
I received results in the wrong order when using ORDER BY on a column computed with ROUND, and am interested in knowing if anyone has encountered a similar problem. I have a solution to my problem at the bottom of this email, but am not sure it should be necessary. I appreciate all comments. Given my table sqlite> .dump foo BEGIN TRANSACTION; CREATE TABLE foo (Numerator INT NOT NULL, Denominator INT NOT NULL); INSERT INTO "foo" VALUES(42, 1); INSERT INTO "foo" VALUES(208, 1); INSERT INTO "foo" VALUES(197, 1); COMMIT; Given this query and output it is obvious that the order of the returned rows are not in the order one would expect: sqlite> SELECT Numerator, Denominator, ROUND(((1.0 * Numerator) / (1.0 * Denominator)), 0) AS MyAvg FROM foo ORDER BY MyAvg DESC; Numerator Denominator MyAvg 42 1 42 208 1 208 197 1 197 Here, removing the ROUND returns the rows in the expected order. SELECT Numerator, Denominator, ((1.0 * Numerator) / (1.0 * Denominator)) AS MyAvg FROM foo ORDER BY MyAvg DESC; Numerator Denominator MyAvg 208 1 208 197 1 197 42 1 42 THE SOLUTION: ROUND returns a STRING! If one wishes to so something similar using ROUND, multiply by 1 (e.g. ROUND( a/b, 0 ) * 1). Is there any reason why ROUND doesn't return a double? Thanks! -- Gerry Blanchette
Re: [sqlite] solving the infamous, irritating "database locked" problem
Hi,
I'm not sure of your environment, but here's some tidbits that might
help:
If you app crashes mid-way (say due to a syntax error or an exception
popped), the db can remain locked.And... if you are doing webby
related work and calling sqlite and something crashes, the lock isn't
released since the process that acquired the lock is still running
(the web server). Sometimes restarting the webserver helps, other
wise you have to delete the journal file.
I'm still working on bulletproofing an app, but you may wish to wrap
your core code in a try/catch block and then close/commit/release the
connection to make sure you don't lock the db by mistake.
hope this helps,
--nickg
On Jun 9, 2005, at 10:35 AM, Jay Sprenkle wrote:
I'm not familar with the wrapper you're using, but don't you have a
commit without a matching begin? Did you establish a lock on the table
before
trying to update? Are you updating a table that you currently are
reading from?
As in:
select * from t;
for each result
update t set field = blah;
next
On 6/9/05, Puneet Kishor <[EMAIL PROTECTED]> wrote:
I've been a reading a lot on the "database locked" problem, but still
need guidance trying to locate the source of my problem.
environment: DBI/DBD::SQLite (latest versions) with SQLite3
(3.2.1) on
Mac OS X (10.3.9).
I am trying to update a table via the web. The UPDATE in question is
the very first (and the only) DML statement. Yet, I get the "database
locked" error.
The sequence of commands is
my $sql = "UPDATE statement";
my $sth = $dbh->prepare(qq{$sql});
$sth->execute;
$dbh->commit;
My question is: could I set some kind of "trace" that tells me
what is
going on with SQLite?
Many thanks.
--
Puneet Kishor
--
---
You a Gamer? If you're near Kansas City:
Conquest 36
https://events.reddawn.net
The Castles of Dereth Calendar: a tour of the art and architecture of
Asheron's Call
http://www.lulu.com/content/77264
[sqlite] quoting strings issue
While I await some insight into my previously posted "database locking" problem, I have a question regarding quoting text. Does SQLite have any issues with anything other than single-quotes? For example, with colon, or semi-colon? For example, I find occasional complaints if I try to update/insert a string like -- $text = 'Some long-winded text with lot''s of different things like: * lists * more lists text with semi-colon; and even such: stuff.'; UPDATE tbl SET col = $text WHERE ... causes error to effect that sql prepare failed (again, Perl/DBI specific errors). -- Puneet Kishor
Re: [sqlite] quoting strings issue
--- Puneet Kishor <[EMAIL PROTECTED]> wrote: > While I await some insight into my previously posted > "database locking" > problem, I have a question regarding quoting text. > > Does SQLite have any issues with anything other than > single-quotes? For > example, with colon, or semi-colon? > > For example, I find occasional complaints if I try to > update/insert a > string like -- > > $text = 'Some long-winded text with lot''s of different > things like: > > * lists > * more lists > > text with semi-colon; and even such: stuff.'; > > UPDATE tbl SET col = $text WHERE ... > > causes error to effect that sql prepare failed (again, > Perl/DBI > specific errors). > > > -- > Puneet Kishor > > I think your $dbh->prepare() would be likely to fail if the value you're updating isn't properly quoted for the SQL statement. The Perl DBI lets you get past this to some extent by allowing you to use replaceable vars in the prepared statement, and passing those vars as args to $dbh->execute(). DBI also offers a quote() method against the $dbh that could help avoid problems executing a prepared stmt. IOW, something like. $sql = "update t1 set a = ?"; $string = $dbh->quote( qq(some long string; has many 'single quotes') ); $sth = $dbh->prepare($sql); $rc = $sth->execute($string); will probably eliminate both the prepare() error, and an UPDATE error later. -Clark
Re: [sqlite] quoting strings issue
Clark Christensen wrote: IOW, something like. $sql = "update t1 set a = ?"; $string = $dbh->quote( qq(some long string; has many 'single quotes') ); $sth = $dbh->prepare($sql); $rc = $sth->execute($string); will probably eliminate both the prepare() error, and an UPDATE error later. -Clark Clark, The arguments passed to sqlite as parameters should not be quoted. These strings do not pass through the parser, they are used as literal values when the SQL statement is executed. If you do quote this string, the quotes will be included in the value of field a in your database. You'll have to excuse my PERL (it's not a language I use)... but if arguments to execute() are passed as parameters to sqlite then you should do something like this. $sql = "update t1 set a = ?"; $string = "some long string; has many 'single quotes'"; $sth = $dbh->prepare($sql); $rc = $sth->execute($string); HTH Dennis Cote
Re: [sqlite] quoting strings issue
--- Dennis Cote <[EMAIL PROTECTED]> wrote: > Clark Christensen wrote: > > >IOW, something like. > > > >$sql = "update t1 set a = ?"; > >$string = $dbh->quote( qq(some long string; has many > >'single quotes') ); > >$sth = $dbh->prepare($sql); > >$rc = $sth->execute($string); > > > >will probably eliminate both the prepare() error, and an > >UPDATE error later. > > > > -Clark > > > > > > > > > Clark, > > The arguments passed to sqlite as parameters should not > be quoted. These > strings do not pass through the parser, they are used as > literal values > when the SQL statement is executed. If you do quote this > string, the > quotes will be included in the value of field a in your > database. > > You'll have to excuse my PERL (it's not a language I > use)... but if > arguments to execute() are passed as parameters to sqlite > then you > should do something like this. > > $sql = "update t1 set a = ?"; > $string = "some long string; has many 'single quotes'"; > $sth = $dbh->prepare($sql); > $rc = $sth->execute($string); > > HTH > Dennis Cote > Even better. Thanks Dennis! -Clark
Re: [sqlite] quoting strings issue
Thanks to those who responded. However, this thread is going away from what I really asked... not how to quote a string, but to confirm whether or not SQLite had any idiosyncrasies related to string-quoting other than the normal "escape single-quotes within the string." Btw, I am using $dbh->quote. On Jun 9, 2005, at 1:02 PM, Dennis Cote wrote: Clark Christensen wrote: IOW, something like. $sql = "update t1 set a = ?"; $string = $dbh->quote( qq(some long string; has many 'single quotes') ); $sth = $dbh->prepare($sql); $rc = $sth->execute($string); will probably eliminate both the prepare() error, and an UPDATE error later. -Clark Clark, The arguments passed to sqlite as parameters should not be quoted. These strings do not pass through the parser, they are used as literal values when the SQL statement is executed. If you do quote this string, the quotes will be included in the value of field a in your database. You'll have to excuse my PERL (it's not a language I use)... but if arguments to execute() are passed as parameters to sqlite then you should do something like this. $sql = "update t1 set a = ?"; $string = "some long string; has many 'single quotes'"; $sth = $dbh->prepare($sql); $rc = $sth->execute($string); HTH Dennis Cote -- Puneet Kishor
Re: [sqlite] quoting strings issue
On Thu, 2005-06-09 at 14:29 -0500, Puneet Kishor wrote: > Thanks to those who responded. However, this thread is going away from > what I really asked... not how to quote a string, but to confirm > whether or not SQLite had any idiosyncrasies related to string-quoting > other than the normal "escape single-quotes within the string." > SQLite has no idiosyncrasies regarding string quoting. However, I cannot speak for the Perl DBI interface to SQLite - I am unfamiliar with that code. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] quoting strings issue
> Thanks to those who responded. However, this thread is going away from > what I really asked... not how to quote a string, but to confirm > whether or not SQLite had any idiosyncrasies related to string-quoting > other than the normal "escape single-quotes within the string." You're doing it the hard way. if you bind program variables to parameters in the sql instead of building sql with literal data strings then you don't have to escape quotes at all. The sql interpreter never looks at them and they don't have to be quoted.
Re: [sqlite] quoting strings issue
On Jun 9, 2005, at 2:30 PM, D. Richard Hipp wrote: On Thu, 2005-06-09 at 14:29 -0500, Puneet Kishor wrote: Thanks to those who responded. However, this thread is going away from what I really asked... not how to quote a string, but to confirm whether or not SQLite had any idiosyncrasies related to string-quoting other than the normal "escape single-quotes within the string." SQLite has no idiosyncrasies regarding string quoting. However, I cannot speak for the Perl DBI interface to SQLite - I am unfamiliar with that code. Thanks for the confirmation. That is what I was seeking. Now I can focus my debugging efforts elsewhere. Now, if only I can also figure out the db locking issue, all will be copacetic (fer now). -- Puneet Kishor
[sqlite] #line macros in parse.c
Are the #line macros really needed? Because on Windows if you try to compile SQLite with Assembly, Machine Code, Source Code output files generated at the same time ( Properties->C/C++ -> Output Files: Assembler Output = Assembly, Machine Code and Source ) which is the /FAcs flag, the compilation breaks because it tries to find the file "parse.y" which doesn't exist. Ultimately this is probably a MSFT compiler bug, but right now it's breaking the compile if you have this flag turned on, and I'm wondering if these #line macros are really needed or are they just leftover from debugging? Thanks, Dave
Re: [sqlite] #line macros in parse.c
On June 9, 2005 03:49 pm, Brown, Dave wrote: > Are the #line macros really needed? Because on Windows if you try to > compile SQLite with Assembly, Machine Code, Source Code output files > generated at the same time ( Properties->C/C++ -> Output Files: Assembler > Output = Assembly, Machine Code and Source ) which is the /FAcs flag, the > compilation breaks because it tries to find the file "parse.y" which > doesn't exist. > > Ultimately this is probably a MSFT compiler bug, but right now it's > breaking the compile if you have this flag turned on, and I'm wondering if > these #line macros are really needed or are they just leftover from > debugging? Sounds like they were generated from parse.y. Yacc/Bison insert line counts so you have an idea where in the original file an error is located. > Thanks, > > Dave -- Thomas Fjellstrom [EMAIL PROTECTED]
RE: [sqlite] #line macros in parse.c
I dont know how they were generated, but they are in the source zipfile you download from the sqlite.org site. -Dave -Original Message- From: Thomas Fjellstrom [mailto:[EMAIL PROTECTED] Sent: Thursday, June 09, 2005 3:16 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] #line macros in parse.c On June 9, 2005 03:49 pm, Brown, Dave wrote: > Are the #line macros really needed? Because on Windows if you try to > compile SQLite with Assembly, Machine Code, Source Code output files > generated at the same time ( Properties->C/C++ -> Output Files: > Assembler Output = Assembly, Machine Code and Source ) which is the > /FAcs flag, the compilation breaks because it tries to find the file > "parse.y" which doesn't exist. > > Ultimately this is probably a MSFT compiler bug, but right now it's > breaking the compile if you have this flag turned on, and I'm > wondering if these #line macros are really needed or are they just > leftover from debugging? Sounds like they were generated from parse.y. Yacc/Bison insert line counts so you have an idea where in the original file an error is located. > Thanks, > > Dave -- Thomas Fjellstrom [EMAIL PROTECTED]
Re: [sqlite] #line macros in parse.c
On Thu, 2005-06-09 at 14:49 -0700, Brown, Dave wrote: > Are the #line macros really needed? Because on Windows if you try to compile > SQLite with Assembly, Machine Code, Source Code output files generated at > the same time ( Properties->C/C++ -> Output Files: Assembler Output = > Assembly, Machine Code and Source ) which is the /FAcs flag, the > compilation breaks because it tries to find the file "parse.y" which doesn't > exist. > parse.c is a generated file. The #line directives are needed to trace error message back to the original source file, parse.y. If your (broken) compiler insists on having parse.y present, then why not just download it from the website. http://www.sqlite.org/cvstrac/getfile/sqlite/src/parse.y -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Problem with ORDER BY involving ROUND
> Is there any reason why ROUND doesn't return a double? Because the IEEE storage format most (all?) computers use to store floating point numbers is binary based, many values that can be stored exactly in a base 10 system are rounded slightly to fit into the binary system. This is similar to the way the value 1/3 cannot be stored exactly in a base 10 system. You can see an example of this rounding using sqlite: sqlite> select (0.5 - 0.4) - 0.1; -2.77555756156289e-17 This is, incidentally, why you should be very careful comparing floating point numbers in C using "==": sqlite> select (0.5 - 0.4) == 0.1; 0 Google for "IEEE floating point format" if you require a coherent explanation :) __ Discover Yahoo! Use Yahoo! to plan a weekend, have fun online and more. Check it out! http://discover.yahoo.com/
Re: [sqlite] solving the infamous, irritating "database locked" problem
On Thursday 09 June 2005 7:15 am, Puneet Kishor wrote:
> I've been a reading a lot on the "database locked" problem, but still
> need guidance trying to locate the source of my problem.
>
> environment: DBI/DBD::SQLite (latest versions) with SQLite3 (3.2.1) on
> Mac OS X (10.3.9).
>
> I am trying to update a table via the web. The UPDATE in question is
> the very first (and the only) DML statement. Yet, I get the "database
> locked" error.
>
> The sequence of commands is
>
> my $sql = "UPDATE statement";
> my $sth = $dbh->prepare(qq{$sql});
> $sth->execute;
> $dbh->commit;
>
> My question is: could I set some kind of "trace" that tells me what is
> going on with SQLite?
>
Try this
my $sql = "UPDATE statement";
my $sth = $dbh->prepare(qq{$sql}) || die $dbh->errstr;
$dbh->begin_work;
$sth->execute || die $dbh->errstr;
$dbh->commit;
Scott
--
POPFile, the OpenSource EMail Classifier
http://popfile.sourceforge.net/
Linux 2.6.11.4-21.7-default x86_64
