[sqlite] Problems passing parameters between SQLite + TCL
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
-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
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
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
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
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
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
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
> 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
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
-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?
-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?
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
-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
-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
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
-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
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&form=MFESRP&publ=WLHMTAG&crea=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.
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
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
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
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
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
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
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
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
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
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...
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.
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?
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
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
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?
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?
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
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
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
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
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
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
> 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
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
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?
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?
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