[sqlite] Problems passing parameters between SQLite + TCL

[Walter Dnes]

  There's a lot more to this program, but I've cut it down to the bare
minimum that illustrates my problem.  I call a TCL script from the linux
commandline, and get an error message like so...


[waltdnes][~/SQLite] ./fragment 49.25 123 25
can't read "lat1": no such variable
while executing
"expr $lat1 / $radian "
(procedure "sql_distance" line 3)
invoked from within
"sql_distance 49.25 123 48.914 -123.7"
invoked from within
"db eval { select e_stnid, i_stnid, deci_lat, deci_long, elevation,
distance($lat_degrees, $long_degrees, deci_lat, deci_long) as dist
..."
(file "./fragment" line 21)


  Here is the cut-down program...


#!/usr/bin/tclsh
set lat_degrees [expr [lindex $argv 0]]
set long_degrees [expr [lindex $argv 1]]
set radius  [expr [lindex $argv 2]]
load /usr/lib/sqlite-3.6.17/libtclsqlite3.so
sqlite3 db :memory:
# Note: GIS convention has longitude negative in the western hemisphere.
# But end-users will get annoyed at having to enter the minus sign all the
# time.  So the conversion is done internally in the distance() function.
proc sql_distance {lat1, long1, lat2, long2} {
  set radian [expr 180 / 3.1415926]
  set lat1 [expr $lat1 / $radian ]
  set long1 [expr $long1 / $radian * (-1) ]
  set lat2 [expr $lat2 / $radian ]
  set long2 [expr $long2 / $radian ]
  return [expr {
acos(sin($lat1) * sin($lat2) + cos($lat1) * cos($lat2) * cos($long2 - 
$long1)) * 6371}]}
db function distance sql_distance

db eval {attach 'climate.sqlite' as cl}
db eval { select e_stnid, i_stnid, deci_lat, deci_long, elevation,
distance($lat_degrees, $long_degrees, deci_lat, deci_long) as dist
  from cl.stations
  where dist <= $radius} {puts stdout "$e_stnid, $i_stnid, $deci_lat, 
$deci_long, $elevation, $dist"}
db close


  I'm trying to do a select on all sites within a given radius of a
given point. I'm using the "spherical cosine law" to calculate distance.
The "sql_distance 49.25 123 48.914 -123.7" error message
shows that I successfully passed the first 2 command line parameters and
the last 2 came from an entry in table cl.stations.

  Given that all 4 parameters hace been passed tothe distance()
function, why are they undefined in the proc?

-- 
Walter Dnes 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Understanding database lock

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Akash Rao wrote:
> Wanted to understand the sqlite database lock a little better. 

Read this:

  http://www.sqlite.org/lockingv3.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkr6Y+oACgkQmOOfHg372QTxtQCbB/loEO/vENj2HHb9HXVN4xol
8EsAn2/OsmGiUp4ymdirGG+9ihDsBVVL
=Je68
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Understanding database lock

[Akash Rao]

Hello,

Wanted to understand the sqlite database lock a little better. Especially
while using perl DBI-sqlite module.

I did some tests and wanted to know if this is a known way of sqlite's
working.

I have a perl script that add numbers 1-1000 into a db.

Here is the code:
=
use DBI;

my $number;

for ($number=0;$number <= 1000;$number++)
 {
my $dbh = DBI->connect("dbi:SQLite:dbname=/opt/test.db","","",
  {RaiseError => 1, AutoCommit => 0});

   $dbh -> do("INSERT INTO test1 VALUES('$number')");

   $dbh -> commit();
   $dbh->disconnect();
 };


Tests:

Test 1. When two instances of the script are run at the same time (from two
different terminals), the update gets done. Though, there is a delay in the
second instance and finishes after the first one is done.

Test 2. Run one instance of the script and try to add to the db from the
sqlite3 command line (at the same time), I get "database locked" message.

Test 3. Run one instance of the script and simultaneously do a "select *
test1" from the command line, it does not show all the rows in the database.

I plan to use Apache+Perl+Sqlite to build a website that has about 100 users
updating the db through the browser.

I am using Sqlite 3.6.16 on RHEL.

Question 1: Do i need to worry about retry mechanism for "sqlite_busy or
database locked" scenarios. I do not intend to use the sqlite3 command line
for making updates.

Question 2: Is there a way to check if the database is busy or locked while
using Perl DBI?

Any pointers and sample code will help me immensely.

Thanks in advance.

Regards,
Akash
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reverse Referencing Rows

[Igor Tandetnik]

Rick Ratchford wrote:
> For example, say that I want to run this SQL statement to pull out SETS that
> start with a MM/DD of 12/28 to 01/05. That means, each 'set' would be from
> December 25 to January 05, which means that each 'set' will cross a year end
> date (where the year value increments by 1).
> 
> While the above SQL statement will do this, to a point, the first set is
> usually not correct. It will look like this:
> 
> Date  |  Year  |  Month  |  Day
> 
> 12/28/1988   1988   12   28
> 12/29/1988   1988   12   29
> 12/30/1988   1988   12   30
> 01/04/1988   1988   01   04
> 01/05/1988   1988   01   05
> 12/28/1989   1989   12   28
> 12/29/1989   1989   12   29
> 01/03/1989   1989   01   03
> 01/04/1989   1989   01   04
> 01/05/1989   1989   01   05
> 
> As you can see, the first set has a problem It goes from December 28, 1988
> to January 05, 1988, rather than January 05, 1989  like it should for the
> first SET.

Actually, it only seems this way due to the sorting order. If you just do 
"ORDER BY Year, Month, Day" you'll see what's going on. You have one set going 
from 12/28/87 to 01/05/88 (which just happens to be incomplete as you have no 
records in 1987), and another unrelated set going from 12/28/88 to 01/05/89. 
Your overcomplicated ORDER BY clause causes these two sets to interleave.

> I fugure the way to correct this issue is to make sure that each ROW
> (record) has a DATE that is greater than the last ROW.

So, just say that in ORDER BY.

> Is it possible to have the SQL statement above do this as well?

Yes.

Igor Tandetnik


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 for Mac OSX 10.5

[Jay A. Kreibich]

On Wed, Nov 11, 2009 at 11:23:18AM +0700, Dan Kennedy scratched on the wall:
> 
> On Nov 11, 2009, at 4:07 AM, Peter Haworth wrote:
> 
> > sqlite3 is rejecting a SELECT statement that includes the group_concat
> > function saying it's an unknown function, yet the same SELECT
> > statement works fine in the Firefox SQLite Manager extension.
> >
> > The version of sqlite3 on my Mac is 3.4.0 but it looks like the latest
> > version is 3.6.x.  Could that be the cause of the problem and if so,
> > where can I get that version (already compiled) for the Mac?
> 
>  From here:
> 
>http://www.sqlite.org/download.html


  The only pre-compiled OS X binary up there is the analyzer.

   -j


-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reverse Referencing Rows

[Rick Ratchford]

To add to my last post shown below, what I've done is added the "Date" to
the ORDER BY, thus putting the unwanted rows at the very top. It doesn't
remove it, but it does allow for stripping it off easier when transferring
to an array if that is the best way to go.

sSQL = "SELECT Date, Year, Month, Day, Open, High, Low, Close FROM [" &
gsTableName & "] " & _
"WHERE ((Month - " & lngStartMth & ")*100 + (Day - " &
lngStartDay & ") + 1300) % 1300 <= ((" & _
lngEndMth - lngStartMth & ") * 100 + (" & lngEndDay -
lngStartDay & ") + 1300) % 1300 " & _
"ORDER BY Date, Year, ((Month - " & lngStartMth & ")*100 + (Day
- " & lngStartDay & ") + 1300) % 1300"

Cheers!
 
Rick
 
 

#>-Original Message-
#>From: sqlite-users-boun...@sqlite.org 
#>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rick Ratchford
#>Sent: Tuesday, November 10, 2009 10:59 PM
#>To: 'General Discussion of SQLite Database'
#>Subject: [sqlite] Reverse Referencing Rows
#>
#>A while back, Igor gave me some help on pulling out mm/dd 
#>ranges (sets) from my table.
#>
#>This is the code that does that.
#>
#>sSQL = "SELECT Date, Year, Month, Day, Open, High, Low, 
#>Close FROM [" & gsTableName & "] " & _
#>"WHERE ((Month - " & lngStartMth & ")*100 + (Day 
#>- " & lngStartDay & ") + 1300) % 1300 <= ((" & _
#>lngEndMth - lngStartMth & ") * 100 + (" & 
#>lngEndDay - lngStartDay & ") + 1300) % 1300 " & _
#>"ORDER BY Year, ((Month - " & lngStartMth & 
#>")*100 + (Day - " & lngStartDay & ") + 1300) % 1300"
#>
#>The only problem I have is that there are some stray rows 
#>that are not wanted.
#>
#>For example, say that I want to run this SQL statement to 
#>pull out SETS that start with a MM/DD of 12/28 to 01/05. That 
#>means, each 'set' would be from December 25 to January 05, 
#>which means that each 'set' will cross a year end date (where 
#>the year value increments by 1).
#>
#>While the above SQL statement will do this, to a point, the 
#>first set is usually not correct. It will look like this:
#>
#>Date  |  Year  |  Month  |  Day
#>
#>12/28/1988   1988   12   28
#>12/29/1988   1988   12   29
#>12/30/1988   1988   12   30
#>01/04/1988   1988   01   04
#>01/05/1988   1988   01   05
#>12/28/1989   1989   12   28
#>12/29/1989   1989   12   29
#>01/03/1989   1989   01   03
#>01/04/1989   1989   01   04
#>01/05/1989   1989   01   05
#>
#>As you can see, the first set has a problem It goes from 
#>December 28, 1988 to January 05, 1988, rather than January 
#>05, 1989  like it should for the first SET.
#>
#>I fugure the way to correct this issue is to make sure that each ROW
#>(record) has a DATE that is greater than the last ROW.
#>
#>Is it possible to have the SQL statement above do this as well? 
#>
#>I know I can loop through this recordset, transferring each 
#>record to an array that has a date greater than the last 
#>record (row) date value. It's an extra step and easy to program.
#>
#>I'm just curious if it was possible within the above SQL 
#>statement, and whether it would be worth doing it via the SQL 
#>or would perform better if I added the looping into array step.
#>
#>Thank you.
#>
#>Rick
#>
#>
#>___
#>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] Reverse Referencing Rows

[Rick Ratchford]

A while back, Igor gave me some help on pulling out mm/dd ranges (sets) from
my table.

This is the code that does that.

sSQL = "SELECT Date, Year, Month, Day, Open, High, Low, Close FROM [" &
gsTableName & "] " & _
"WHERE ((Month - " & lngStartMth & ")*100 + (Day - " &
lngStartDay & ") + 1300) % 1300 <= ((" & _
lngEndMth - lngStartMth & ") * 100 + (" & lngEndDay -
lngStartDay & ") + 1300) % 1300 " & _
"ORDER BY Year, ((Month - " & lngStartMth & ")*100 + (Day - " &
lngStartDay & ") + 1300) % 1300"

The only problem I have is that there are some stray rows that are not
wanted.

For example, say that I want to run this SQL statement to pull out SETS that
start with a MM/DD of 12/28 to 01/05. That means, each 'set' would be from
December 25 to January 05, which means that each 'set' will cross a year end
date (where the year value increments by 1).

While the above SQL statement will do this, to a point, the first set is
usually not correct. It will look like this:

Date  |  Year  |  Month  |  Day

12/28/1988   1988   12   28
12/29/1988   1988   12   29
12/30/1988   1988   12   30
01/04/1988   1988   01   04
01/05/1988   1988   01   05
12/28/1989   1989   12   28
12/29/1989   1989   12   29
01/03/1989   1989   01   03
01/04/1989   1989   01   04
01/05/1989   1989   01   05

As you can see, the first set has a problem It goes from December 28, 1988
to January 05, 1988, rather than January 05, 1989  like it should for the
first SET.

I fugure the way to correct this issue is to make sure that each ROW
(record) has a DATE that is greater than the last ROW.

Is it possible to have the SQL statement above do this as well? 

I know I can loop through this recordset, transferring each record to an
array that has a date greater than the last record (row) date value. It's an
extra step and easy to program.

I'm just curious if it was possible within the above SQL statement, and
whether it would be worth doing it via the SQL or would perform better if I
added the looping into array step.

Thank you.

Rick


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 for Mac OSX 10.5

[Dan Kennedy]

On Nov 11, 2009, at 4:07 AM, Peter Haworth wrote:

> sqlite3 is rejecting a SELECT statement that includes the group_concat
> function saying it's an unknown function, yet the same SELECT
> statement works fine in the Firefox SQLite Manager extension.
>
> The version of sqlite3 on my Mac is 3.4.0 but it looks like the latest
> version is 3.6.x.  Could that be the cause of the problem and if so,
> where can I get that version (already compiled) for the Mac?

 From here:

   http://www.sqlite.org/download.html


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange problem with different sqlite versions accessing the same database

[Pavel Ivanov]

> Any idea?

Apparently you have problematic implementation of network drive.
Generally it's a very bad idea to use SQLite with file on network
drive because SQLite uses file system locking mechanisms which are
pretty bad working on network drives.

Pavel

On Tue, Nov 10, 2009 at 8:00 PM, Yuzem  wrote:
>
> Ok, I have replaced the older version and now I have the same sqlite version
> on both computers but the problem persist. Changes don't get saved on the
> computer that access the database trough the network.
>
> Any idea?
> --
> View this message in context: 
> http://old.nabble.com/Strange-problem-with-different-sqlite-versions-accessing-the-same-database-tp26257575p26294299.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

Re: [sqlite] Strange problem with different sqlite versions accessing the same database

[Yuzem]

Ok, I have replaced the older version and now I have the same sqlite version
on both computers but the problem persist. Changes don't get saved on the
computer that access the database trough the network.

Any idea?
-- 
View this message in context: 
http://old.nabble.com/Strange-problem-with-different-sqlite-versions-accessing-the-same-database-tp26257575p26294299.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

Re: [sqlite] db file locked after power loss

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Shawn Boyle wrote:
> The sqlite3_open() call returns  
> SQLITE_IOERR.  All attempts to copy the db file from the device fail.   

That indicates your device is having issues.  If you can't read the file
then neither can SQLite.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkr5+SQACgkQmOOfHg372QR7tgCglScRO8wQ6EOf71yPYSMq8WxQ
9t4AmgI3qR+9h6oeJMeSXn0RDVK8hglu
=ITq0
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is the dbFileVers written to journal files?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Scott Hess wrote:
> Someone was asking me a question about what happens if the wrong
> journal file gets applied to a database.  My answer was that terrible
> things happen.

See also http://www.sqlite.org/src/info/61d35ac210

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkr5+A0ACgkQmOOfHg372QSoUwCgmHrj55HbgPkMGPK5ZuHNtpg/
q0EAoMAaeapeHvGmjcC40+bbAfhJcQL+
=SCw0
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do I output multi-word strings without braces?

[D. Richard Hipp]

On Nov 10, 2009, at 2:01 PM, Walter Dnes wrote:
>
>  Oops, I forgot to mention that this is the TCL interface to SQLite.
> This is one of those "grey area" questions that could go to either TCL
> or SQLite forums, because it's an interaction between the two of them.
> I assume that some people here have dealt with this issue before.


SQLite began as a TCL extension.  The TCL bindings for SQLite are  
native to the SQLite source tree.  TCL is used to test SQLite.  TCL is  
required in order to build SQLite from original source files.  74% of  
the code in the source SQLite tree is TCL script.

We're comfortable having mixed Tcl/SQLite questions here.  
http://www.sqlite.org/docsrc/artifact/07b4fa3fd6

D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Output in currency format

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Peter Haworth wrote:
> I have a column defined with a type of FLOAT, which I believe is  
> treated as REAL by SQLite.  When selecting that column, I would like  
> it to be returned with a leading "$" sign and always have a decimal  
> point and two numbers after the decimal point. 

It is a spectacularly bad idea to use floating point for currency.  Numbers
that look simple to us humans such as .01 and .10 are truncated recurring
fractions in the binary representation used by the underlying hardware and
will introduce errors in your calculations.

This page helps show the details:

  http://docs.python.org/tutorial/floatingpoint.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkr59wgACgkQmOOfHg372QRxWACfVpaAtdRTQL6DS/vuphnZi+wu
ANYAnjnHZoOLPPfJWohRpPBYTUK8xM04
=Xbdt
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite for threads

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

jeff...@aol.com wrote:
> I have a program which uses threads, when writing to (sometime even reading 
>  from) SQLite, I always got the error of database is locked.

http://www.sqlite.org/lockingv3.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkr59MkACgkQmOOfHg372QTWtACgnwUwfpwayQgqtJIeHIP7f9TF
kN8AoNxAwShgBLXOto35gtA2QrzhLpz+
=ERsX
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is locked

[Jay A. Kreibich]

On Tue, Nov 10, 2009 at 06:04:20PM -0500, Frank Chang scratched on the wall:
> 
>  We have an application which uses Microsoft SQL Server 2005 
> Extended stored procedures in conjunction with Sqlite databases. 
> We have a C++ DLL which uses the following code to insert rows
> into a SQLite database:
> 
> sprintf(Command,"INSERT INTO [Keys] ([Key], [Cluster], "
> "[DupeGroup]) VALUES (\"%s\", \"%*.*s\", %d)",
> MCKey,BlockSize,BlockSize,MCKey,DupeGroup);

  Aside: using string functions to build query strings is a Bad Idea,
  and string constants in SQL use single-quotes, not double.


> When we run this UPDATE statement from two SQL Server 2005 Management 
> Studio clients concurrently, one of the client processes returns with
> the error code Database is locked and the other client process is
> suspended. Has anyone seen this problem?

  If you keep getting SQLITE_BUSY return codes sooner or later you're
  obligated too cancel the current statement and rollback any open
  transactions.

  See:   http://sqlite.org/c3ref/busy_handler.html

  In specific:

 The presence of a busy handler does not guarantee that it will be
 invoked when there is lock contention. If SQLite determines that
 invoking the busy handler could result in a deadlock, it will go
 ahead and return SQLITE_BUSY or SQLITE_IOERR_BLOCKED instead of
 invoking the busy handler. Consider a scenario where one process
 is holding a read lock that it is trying to promote to a reserved
 lock and a second process is holding a reserved lock that it is
 trying to promote to an exclusive lock. The first process cannot
 proceed because it is blocked by the second and the second process
 cannot proceed because it is blocked by the first. If both
 processes invoke the busy handlers, neither will make any
 progress. Therefore, SQLite returns SQLITE_BUSY for the first
 process, hoping that this will induce the first process to release
 its read lock and allow the second process to proceed.

  You're basically implementing a manual busy handler with your loop.
  Sooner or later it needs to give up and start over or things can
  remain locked.  If all you're doing is waiting, you might want to
  look at sqlite3_busy_timeout():

  http://sqlite.org/c3ref/busy_timeout.html

  This will keep trying if things still look safe, but will return
  SQLITE_BUSY right away if SQLite detects a possible deadlock.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPGRADE to 3.6.20 and python

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Cooper, Andrew wrote:
> This has probably been asked a lot of times before but I couldn't find
> an answer in the archives.

You should ask on the python sqlite list :-)  It is jointly maintained by
the authors of the two Python to SQLite bindings:

  http://groups.google.com/group/python-sqlite

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkr585IACgkQmOOfHg372QSDjACgmeA8rzqKT0KW9rEzIFGK25lf
Iv0AmwQUWRZiOVaMMKzG1I6SKp6Tge8X
=5KAA
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Database is locked

[Frank Chang]

 We have an application which uses Microsoft SQL Server 2005 Extended 
stored procedures in conjunction with Sqlite databases. We have a C++ DLL which 
uses the following code to insert rows into a SQLite database:

   

sprintf(Command,"INSERT INTO [Keys] ([Key], [Cluster], "

"[DupeGroup]) VALUES (\"%s\", \"%*.*s\", %d)",

MCKey,BlockSize,BlockSize,MCKey,DupeGroup);

 

 



while (Keys->Execute(Command)==SQLITE_BUSY) {

  #if defined(__unix)

  sleep(dRETRYDELAY);

  #else

  Sleep(dRETRYDELAY*1000);

#endif

}

 

   We use SQL Server 2005 Extended Stored Procedures and User Defined functions 
in the following manner in order to insert into the SQLite table using the 
above C++ code:

 

UPDATE MyTestDatabase SET

MatchKey = master.dbo.mdMUIncrementalBuildKeyEx(@Incremental, @Matchcode, 
Column12, Column53, Column52, Address, NULL, NULL, NULL, NULL, NULL, NULL),

@RetVal = master.dbo.mdMUIncrementalAddRecord(@Incremental),

Status = master.dbo.mdMUIncrementalGetStatusCode(@Incremental),

DupeGroup = master.dbo.mdMUIncrementalGetDupeGroup(@Incremental)

 

When we run this UPDATE statement from two SQL Server 2005 Management 
Studio clients concurrently, one of the client processes returns with the error 
code Database is locked and the other client process is suspended. Has anyone 
seen this problem? Is it a SQL Server 2005 problem or does it have anything to 
do with Sqlite? In particular, we are wondering why we get the SQLite error 
message "database is locked" when we are running concurrent SQL Server client 
processes? When we run just one SQL Server client process, everything works 
fine and we get no SQLite error messages. Thank you. 

 
  
_
Bing brings you maps, menus, and reviews organized in one place.
http://www.bing.com/search?q=restaurants=MFESRP=WLHMTAG=TEXT_MFESRP_Local_MapsMenu_Resturants_1x1
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Detach worked OK in 3.6.19 -- gets error #1 (Database is Locked) in 3.6.20.

[Fred Meier]

The following pseudo code works OK in 3.6.19 but the Detach gets error #1
(Database is Locked) in 3.6.20.
   1. Prepare a Select statement returning 1 or more rows
   2. Step the first row
   3. Exec Attach statement
   4. Exec Detach statement for previous Attach   (This is statement that
get error 1 in 3.6.20)

You may have decided to eliminate a dangerous feature?   I have coded a
workaround in my production applications.

Regards
Fred Meier
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] db file locked after power loss

[Shawn Boyle]

I'm running SQLite v3.4.0 on an embedded linux device.  I am testing a  
power loss scenario while updating a record.  When the device boots  
back up and the device occasionally gets into a state where the  
database file seems locked.  The sqlite3_open() call returns  
SQLITE_IOERR.  All attempts to copy the db file from the device fail.   
Attempts to attach to the db in a terminal window result in "SQL  
error: unable to open database: PEDB.db".  I have added a call to  
enable extended result codes, sqlite3_extended_result_codes().

Are there any other suggestions?

Thank you,
Shawn
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Get Max Samples

[Rick Ratchford]

Thanks Pavel, Matt and Igor. :-)

What more could I have provided? It's just a column and I have no idea how
group_concat works anyways. That's why I threw that in.

Yes, the largest SET is what I need. Didn't know to call it a SET until
after reading your comments.

Now to make sure I understand this lesson.

To GROUP BY, we're instructing the database to act on the data in these
SETS, right?

So each SET is then counted by "count(*)" and stored into 'cnt'.

Then max(cnt) will return the highest value, which in this case would be the
highest 'cnt' or largest size SET in other words.

I think I understand.

As for my code...

sSQL = "SELECT max(cnt) FROM (SELECT count(*) as cnt FROM [" & gsTableName &
"] GROUP BY Year)"

The above is the ACTUAL code I'm using. My SQL strings need to be assigned
to a string variable, such as sSQL shown above. Then I can pass this SQL
string variable my VB function that opens a recordset using that SQL
statement.

Set RsTemp = Cnn.OpenRecordset(sSQL)

The "Year" is the actual column that is in place of what I referred to
earlier as the SampleNumber. Samples are grouped by YEAR, such as...

1988
1988
1988
1988
1989
1989
1989
1989
1989
...

Thought it easier to ask the question based on SampleNumber rather than Year
to remove confusion. Also, the TableName must be inserted into the SQL
string via a string variable called gsTableName. That is why you see FROM ["
& gsTableName & "] GROUP BY Year in the statement.

This seems to do the trick.

Thanks ya'll.

:-)
Rick
 

#>-Original Message-
#>From: sqlite-users-boun...@sqlite.org 
#>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
#>Sent: Tuesday, November 10, 2009 2:33 PM
#>To: sqlite-users@sqlite.org
#>Subject: Re: [sqlite] Get Max Samples
#>
#>Rick Ratchford 
#>wrote: 
#>> How would you write the SQL statement that would return the maximum 
#>> number of a sample?
#>> 
#>> For example, if within the SampleNumber column, the SampleNumber 17 
#>> had more records (say there are 23 SampleNumber = 17 in the table, 
#>> more than any other), you wanted to return the value 23?
#>
#>select max(c) from (
#>select count(*) c from mytable group by SampleNumber );
#>
#>-- or
#>
#>select count(*) c from mytable
#>group by SampleNumber
#>order by c desc limit 1;
#>
#>> Is group_concat used here?
#>
#>You haven't shown any code. Had you done so, I could have 
#>easily told you whether or not you used group_concat in it.
#>
#>Igor Tandetnik
#>
#>___
#>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] sqlite3 for Mac OSX 10.5

[Peter Haworth]

sqlite3 is rejecting a SELECT statement that includes the group_concat  
function saying it's an unknown function, yet the same SELECT  
statement works fine in the Firefox SQLite Manager extension.

The version of sqlite3 on my Mac is 3.4.0 but it looks like the latest  
version is 3.6.x.  Could that be the cause of the problem and if so,  
where can I get that version (already compiled) for the Mac?

Thanks,

Pete Haworth








___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Get Max Samples

[Igor Tandetnik]

Rick Ratchford 
wrote: 
> How would you write the SQL statement that would return the maximum
> number of a sample?
> 
> For example, if within the SampleNumber column, the SampleNumber 17
> had more records (say there are 23 SampleNumber = 17 in the table,
> more than any other), you wanted to return the value 23?

select max(c) from (
select count(*) c from mytable group by SampleNumber
);

-- or

select count(*) c from mytable
group by SampleNumber
order by c desc limit 1;

> Is group_concat used here?

You haven't shown any code. Had you done so, I could have easily told you 
whether or not you used group_concat in it.

Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Get Max Samples

[Matt Sergeant]

On Tue, 10 Nov 2009 15:28:30 -0500, Pavel Ivanov wrote:
> You're right about max() and group_concat() will not help you either.
> You need something like this:
> 
> select max(cnt)
> from (select count(*) as cnt from table_name group by SampleNum)

That'll give you the count of the largest set. But not the actual 
value. For that you need to combine it with a HAVING clause. But I'll 
leave that as an exercise :-)

Matt.

__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
__
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Get Max Samples

[Pavel Ivanov]

You're right about max() and group_concat() will not help you either.
You need something like this:

select max(cnt)
from (select count(*) as cnt from table_name group by SampleNum)


Pavel

On Tue, Nov 10, 2009 at 3:24 PM, Rick Ratchford
 wrote:
>
> Suppose you had a column called SampleNumber.
>
> And in this column, you might have...
>
> 1
> 1
> 1
> 2
> 2
> 2
> 2
> 3
> 3
> 4
> 4
> 4
> 5
> 5
> 5
> 5
>
> etc.
>
> How would you write the SQL statement that would return the maximum number
> of a sample?
>
> For example, if within the SampleNumber column, the SampleNumber 17 had more
> records (say there are 23 SampleNumber = 17 in the table, more than any
> other), you wanted to return the value 23?
>
> If I use max(SampleNum), that will return only the highest value in the
> column rather than what I want, right?
>
> Is group_concat used here?
>
>
> Thank you.
>
> Rick
>
> ___
> 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] Request: SQLITE_USE_MALLOCA

[D. Richard Hipp]

On Nov 10, 2009, at 3:19 PM, Shaun Seckman (Firaxis) wrote:
>
>SQLite currently has a SQLITE_USE_ALLOCA define in  
> which
> it will attempt to use alloca instead of malloc when the usage fits.
> One of the common dangers with alloca is that if there is not enough
> stack space, bad things happen and it's usually very difficult to  
> debug.

Have you actually measured how much stack space SQLite is using (even  
with
SQLITE_USE_ALLOCA)?  Is excess stack space usage really a problem?

D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Get Max Samples

[Rick Ratchford]

Suppose you had a column called SampleNumber.

And in this column, you might have...

1
1
1
2
2
2
2
3
3
4
4
4
5
5
5
5

etc.

How would you write the SQL statement that would return the maximum number
of a sample?

For example, if within the SampleNumber column, the SampleNumber 17 had more
records (say there are 23 SampleNumber = 17 in the table, more than any
other), you wanted to return the value 23?

If I use max(SampleNum), that will return only the highest value in the
column rather than what I want, right?

Is group_concat used here?


Thank you.

Rick

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Request: SQLITE_USE_MALLOCA

[Shaun Seckman (Firaxis)]

Hello all,

SQLite currently has a SQLITE_USE_ALLOCA define in which
it will attempt to use alloca instead of malloc when the usage fits.
One of the common dangers with alloca is that if there is not enough
stack space, bad things happen and it's usually very difficult to debug.
Microsoft Visual Studio offers the function _malloca which will allocate
stack space if the amount is within a threshold OR it will malloc if the
amount is beyond the threshold.  I find myself frequently adding  the
following code to each new release of SQLite to support _malloca and was
curious if others would find it useful:

 

Just before "#ifdef SQLITE_USE_ALLOCA"..

#ifdef SQLITE_USE_MALLOCA

# define sqlite3StackAllocRaw(D,N)   _malloca(N)

# define sqlite3StackAllocZero(D,N)  memset(_malloca(N), 0, N)

# define sqlite3StackFree(D,P)   _freea(P);

#else

 

The two caveats I see that would probably prevent this from being
standard would be that _malloca may not exist on all platforms and that
_malloca can potentially bypass the memory management routines put in
place for SQLite (since _malloca will internally call malloc if there is
not enough stackspace).  Still, it serves as a nice safety net to those
who like using alloca but fear the dangers of it :)

 

-Shaun

 

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] implementing without right join...

[Sam Carleton]

This question is purely a theoretical and came up in a discussion today
about joins in DB's that allow both left and right:

Given three tables, A, B, and C, joined such that:  A left join B right join
C

Is my impression correct that this translates into: (outer join A and C)
inner join B
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Firefox SQLite Manager extension troubles.

[Dennis Cote]

Ted Rolle wrote:
> 
> I've done as you said many times, but SQLite Manager still looks for
> the old database, reports that it's not available.  

Ted,

Try turning off the option to open the last used database. In SQLite 
Manager Menu -> Tools -> Options then select Main tab and uncheck Open 
the Last Used Database. That may clear the saved database name.

After that you can try re-enabling the option after opening the new 
database file.

HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do I output multi-word strings without braces?

[Walter Dnes]

On Tue, Nov 10, 2009 at 03:06:08AM -0500, Walter Dnes wrote
> Given the following code fragment...
> 
> set xname [db eval { select name from elements where e_mtid = $element }]
> puts [format "Requested element ==> %s ==> %s" $element $xname]

  Oops, I forgot to mention that this is the TCL interface to SQLite.
This is one of those "grey area" questions that could go to either TCL
or SQLite forums, because it's an interaction between the two of them.
I assume that some people here have dealt with this issue before.

-- 
Walter Dnes 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Output in currency format

[Rich Shepard]

On Tue, 10 Nov 2009, Peter Haworth wrote:

> Is there a way to do this or should I plan on handling it within the
> application?

Pete,

   The latter. Display formatting is not part of SQL.

   You might also consider using integer values for money because the math is
more accurate.

Rich
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Output in currency format

[Pavel Ivanov]

There's no way to force SQLite to return exactly 2 decimal places for
you. You have to do it in your application or if you really-really
need to do it in sql you can do it like this (assuming you need column
col from table tab):

select '$'||case
when length(col) = 1
then '0.0'||col
when length(col) = 2
then '0.'||col
else
substr(col, 1, length(col) - 2)||'.'||substr(col, length(col) - 1)
end as col
from (select cast(round(col * 100) as text) as col from tab)


But I wouldn't do that if I were you. ;-)


Pavel

On Tue, Nov 10, 2009 at 1:17 PM, Peter Haworth  wrote:
> I have a column defined with a type of FLOAT, which I believe is
> treated as REAL by SQLite.  When selecting that column, I would like
> it to be returned with a leading "$" sign and always have a decimal
> point and two numbers after the decimal point.  I can use
> concatenation to get the "$" sign in there but have not been able to
> enforce the inclusion of a decimal point and two decimal places.
> Whole numbers are returned with no decimal point and no decimal
> places.  Numbers where the second decimal place would be a zero are
> returned with the decimal point but only one decimal place.
>
> I tried using the round function specifying 2 decimal places but this
> does not affect the output.  I have also tried using CAST to force the
> column into various other types but that also does not affect the
> output.
>
> Web searches suggest that the CONVERT function is available in other
> SQL implementations but I don't see that anywhere in the SQLite
> documentation.  Is there a way to do this or should I plan on handling
> it within the application?
>
> Thanks
>
> Pete Haworth
>
>
>
>
>
>
>
>
>
> ___
> 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] Is the dbFileVers written to journal files?

[Dan Kennedy]

On Nov 11, 2009, at 1:24 AM, Scott Hess wrote:

> Someone was asking me a question about what happens if the wrong
> journal file gets applied to a database.  My answer was that terrible
> things happen.
>
> I found myself wondering if the "File change counter" value is stored
> in the journal (*).

It is not.

   http://www.sqlite.org/fileformat.html#journal_header_format

Dan.


> It would seem like a cheap(ish) way to give some
> reasonable assertions about whether the journal belongs to the
> database file it's being applied to.  But if it's in there, I'm not
> having any luck finding it.  Has anyone already figured out that this
> is true?



>
> Thanks,
> scott
>
> (*) It would be more complicated than storing the original value.
> You'd probably have to store both the original and the target.  And
> that assumes that at rollback, the database file's header was either
> completely overwritten or not written at all, which may not match
> reality very well.
> ___
> 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] Is the dbFileVers written to journal files?

[Scott Hess]

Someone was asking me a question about what happens if the wrong
journal file gets applied to a database.  My answer was that terrible
things happen.

I found myself wondering if the "File change counter" value is stored
in the journal (*).  It would seem like a cheap(ish) way to give some
reasonable assertions about whether the journal belongs to the
database file it's being applied to.  But if it's in there, I'm not
having any luck finding it.  Has anyone already figured out that this
is true?

Thanks,
scott

(*) It would be more complicated than storing the original value.
You'd probably have to store both the original and the target.  And
that assumes that at rollback, the database file's header was either
completely overwritten or not written at all, which may not match
reality very well.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Output in currency format

[Peter Haworth]

I have a column defined with a type of FLOAT, which I believe is  
treated as REAL by SQLite.  When selecting that column, I would like  
it to be returned with a leading "$" sign and always have a decimal  
point and two numbers after the decimal point.  I can use  
concatenation to get the "$" sign in there but have not been able to  
enforce the inclusion of a decimal point and two decimal places.   
Whole numbers are returned with no decimal point and no decimal  
places.  Numbers where the second decimal place would be a zero are  
returned with the decimal point but only one decimal place.

I tried using the round function specifying 2 decimal places but this  
does not affect the output.  I have also tried using CAST to force the  
column into various other types but that also does not affect the  
output.

Web searches suggest that the CONVERT function is available in other  
SQL implementations but I don't see that anywhere in the SQLite  
documentation.  Is there a way to do this or should I plan on handling  
it within the application?

Thanks

Pete Haworth









___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fwd: SQLite 3.6.20 problem

[Shane Harrelson]

Fixed in check-in http://www.sqlite.org/src/info/8097c64acf


On Mon, Nov 9, 2009 at 10:52 AM, D. Richard Hipp  wrote:
>
>
> Begin forwarded message:
>
>> From: Ken Zalewski 
>> Date: November 9, 2009 9:45:22 AM EST
>> To: d...@hwaci.com
>> Subject: SQLite 3.6.20 problem
>>
>> Rich,
>>
>> It seems that the "-batch" option in 3.6.20 has stopped working.
>>
>> If I run "sqlite3 -batch blah.db < create_tables.sql"
>>
>> the blah.db database is never created.
>>
>> In versions up to and including 3.6.19, the blah.db file would
>> properly be created.
>
>
>> In fact, the -batch option, even when used with an already-existing
>> database, seems to have no effect.  If I run "sqlite3 -batch
>> existing.db < create_tables.sql", no tables are actually created.
>>
>> This seems to be a major change.  Does -batch serve any purpose any
>> longer?
>
> D. Richard Hipp
> d...@hwaci.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

Re: [sqlite] sqlite-users Digest, Vol 23, Issue 10

[Peter Haworth]

Thank you Igor, that's exactly what I need.

Pete Haworth


On Nov 10, 2009, at 4:00 AM, sqlite-users-requ...@sqlite.org wrote:

> You are looking for group_concat (http://sqlite.org/ 
> lang_aggfunc.html):
>
> select KeyA, DataA, group_concat(DataB)
> from TABLEA join TABLEB on TABLEA.TableBKey=TableB.KeyB
> group by KeyA;
>
> Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compile Virtualtext extension

[Gary_Gabriel]

Thanks Alexey,

Great response. I'll make the changes and get back to you.

Take care- Gary Gabriel


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite for threads

[Jean-Christophe Deschamps]

Hi,

> >
> > Maybe many others have asked this question, so I will say sorry if
> > that's true.
> > I have a program which uses threads, when writing to (sometime even
> > reading
> >  from) SQLite, I always got the error of database is locked.
> > I think since SQLite is a file db, so it get locked easily by multi-
> > threads or multi-processes.
> > But is there a way to resolve this problem instead of switching
> > database  to another one?
> >
> > Thanks.
>
>I used to have similar problems and solved it by following advice
>I got on this list, which is to start the transaction with:
>BEGIN IMMEDIATE;
>any time the database will be written to (ie an insert, update,
>delete, etc).
>
>That, along with looping on sqlite3_prepare_v2 and sqlite3_step any
>time you get SQLITE_BUSY, virtually solved the issue for me.

Don't forget to wrap _whole_ read-modify-write transactions inside the 
BEGIN IMMEDIATE; .. COMMIT; block, not only the write parts.

You may also want to set sqlite3_busy_timeout to a reasonable value 
avoiding most if not all SQLITE_BUSY conditions.  What is "reasonable" 
all depends on how long the slowest system-wide transaction is likely 
to take.





___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite for threads

[Doug]

> Hello,
> 
> Maybe many others have asked this question, so I will say sorry if
> that's true.
> I have a program which uses threads, when writing to (sometime even
> reading
>  from) SQLite, I always got the error of database is locked.
> I think since SQLite is a file db, so it get locked easily by multi-
> threads or multi-processes.
> But is there a way to resolve this problem instead of switching
> database  to another one?
> 
> Thanks.

I used to have similar problems and solved it by following advice 
I got on this list, which is to start the transaction with:
BEGIN IMMEDIATE;
any time the database will be written to (ie an insert, update, 
delete, etc).

That, along with looping on sqlite3_prepare_v2 and sqlite3_step any
time you get SQLITE_BUSY, virtually solved the issue for me.

HTH
Doug


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite for threads

[JeffHua]

Hello,
 
Maybe many others have asked this question, so I will say sorry if that's  
true.
I have a program which uses threads, when writing to (sometime even reading 
 from) SQLite, I always got the error of database is locked.
I think since SQLite is a file db, so it get locked easily by multi-threads 
 or multi-processes.
But is there a way to resolve this problem instead of switching  database 
to another one?
 
Thanks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] UPGRADE to 3.6.20 and python

[Cooper, Andrew]

This has probably been asked a lot of times before but I couldn't find
an answer in the archives.

 

I want to upgrade the version of sqlite in python 2.5 to use 3.6.20(the
latest)

 

What steps do I need to do to achieve this ?

Does pysqlite need to be installed and if so does this support the
latest version of sqlite ?

 

Oh and my target machine is Linux

 

Thanks in advance

 

Andy Cooper

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do I output multi-word strings without braces?

[Dan Kennedy]

On Nov 10, 2009, at 3:06 PM, Walter Dnes wrote:

> Given the following code fragment...
>
> set xname [db eval { select name from elements where e_mtid =  
> $element }]
> puts [format "Requested element ==> %s ==> %s" $element $xname]
>
> The "business rules" are such that I know I'll only get one row
> returned.  I get output like so...
>
> Requested element ==> abcdef ==> {FOO BAR}
>
> What I need is...
>
> Requested element ==> abcdef ==> FOO BAR
>
> What do I need to do to get rid of the braces around the output name?
> And no, that's not how the data looked in the tab-delimited file it  
> was
> imported from.

[db eval] returns a list of values. In this case your list consists of
a single string - "FOO BAR". Tcl adds the braces to distinguish this
list from the two element list that consists of "FOO" followed by "BAR".

If you know in advance that your query will return a single element,
use [db one] instead of [db eval]. To extract elements from a list, use
[lindex]. To summarize, either of the following will probably work for
you:

set xname [db eval { select name from elements where e_mtid =  
$element }]

or,

set xname [lindex [db eval { select ... }] 0]

Dan.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How do I output multi-word strings without braces?

[Walter Dnes]

  Given the following code fragment...

set xname [db eval { select name from elements where e_mtid = $element }]
puts [format "Requested element ==> %s ==> %s" $element $xname]

  The "business rules" are such that I know I'll only get one row
returned.  I get output like so...

Requested element ==> abcdef ==> {FOO BAR}

  What I need is...

Requested element ==> abcdef ==> FOO BAR

  What do I need to do to get rid of the braces around the output name?
And no, that's not how the data looked in the tab-delimited file it was
imported from.

-- 
Walter Dnes 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users