http://permalink.gmane.org/gmane.comp.db.sqlite.general hourly 1 1901-01-01T00:00+00:00 http://gmane.org/img/gmane-25t.png http://gmane.org http://permalink.gmane.org/gmane.comp.db.sqlite.general/43107 Hi All, Has anyone encountered an indefinite database lock? This is a condition where the database is in a locked state and no process is connected to it. The database cannot be unlocked by any means and is no longer usable. My environment is FreeBSD 6.2, SQLite 3.3.7. Several processes are reading/writing data to the database. This appears to be intermittent. I'm still figuring out how to replicate it. Is this a known bug in 3.3.7? Thanks _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Ronnel P. Maglasang 2008-12-05T03:51:34 http://permalink.gmane.org/gmane.comp.db.sqlite.general/43106 You are inside a database (inventory.db). Once inside it, its name doesn't matter (by inside, I mean, you have opened the db in the sqlite3 shell, or you have connected to the database have have a db handle to work with). You say "SELECT * FROM food" and you get your food. Couldn't be simpler. Once in the db, you ATTACH another db. In that case you can use the name of the ATTACHed db. See the syntax in the docs. P Kishor 2008-12-05T03:43:46 http://permalink.gmane.org/gmane.comp.db.sqlite.general/43105 anyone can help me? ----- Original Message ---- From: Igor Tandetnik <itandetnik-fH9DrAxc5Jo< at >public.gmane.org> To: sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org Sent: Thursday, November 27, 2008 12:07:31 PM Subject: Re: [sqlite] how do this stuff in sqlite "Rachmat Febfauza" <mathium1-/E1597aS9LQAvxtiuMwx3w< at >public.gmane.org> wrote in message news:410951.35692.qm-txAPP12TKUiB9c0Qi4KiSs0jms80CZx+QQ4Iyu8u01E< at >public.gmane.org Define "don't work". Do you get an error? What's the error text? Begin and End are keywords in SQLite (SQL is case insensitive). If you insist on naming your columns this way, you have to enclose the names in double quotes, just as you did when creating awal1 and akhir1. Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users-CzDR Rachmat Febfauza 2008-12-05T02:56:11 http://permalink.gmane.org/gmane.comp.db.sqlite.general/43104 Hi, as far as I can see from source code, there are support for various R-tree variants: /* ** Exactly one of the following must be set to 1. */ #define VARIANT_GUTTMAN_QUADRATIC_SPLIT 0 #define VARIANT_GUTTMAN_LINEAR_SPLIT 0 #define VARIANT_RSTARTREE_SPLIT 1 My issue is the speed of populating the rtree, and doing so in memory while the application starts. As far as I can remember when I once studied rtrees, the various R-/R+/R* -variants performs differently on inserts (though there is a win on lookup). It could be interesting to try out the various implementations in sqlite, but has the other variants been tested and can be relied on ? Anyways, the sqlite package is a neat tool ! Regards, Øyvind Idland _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Oyvind Idland 2008-12-04T19:26:18 http://permalink.gmane.org/gmane.comp.db.sqlite.general/43103 Database name has nothing to do with file name. The main database (one you open with sqlite3_open) is always named "main", and a temporary database is named "temp". In an ATTACH statement, you specify names for additional databases. Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Igor Tandetnik 2008-12-04T18:58:02 http://permalink.gmane.org/gmane.comp.db.sqlite.general/43102 Hi all, I can't seem to use fully qualified table names (dbName.tableName). For instance, I create a database file inventory.db, and create a table food. "SELECT * FROM inventory.food" does not work, nor does "SELECT * FROM inventory.db.food" bc it says "no such table ...". Since there is no CREATE DATABASE statement, I'm wondering what qualifies as a database in sqlite. I expect it to be the name of the database file. This seems like a simple question, and I bet I'm just being a bonehead. Please help me out. Thanks, jb _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Julian Bui 2008-12-04T18:53:18 http://permalink.gmane.org/gmane.comp.db.sqlite.general/43101 I have successfully compiled the source for version 3.6.6.2 for Mac OS X PowerPC that I have placed in a compressed tar file that I wouldn't mind making available to be placed in the download section of the web site. You only have Mac OS X Intel binary. I created the tar flie from /usr and it contains: ./bin/sqlite3 ./include/sqlite3.h ./include/sqlite3ext.h ./lib/libsqlite3.0.dylib ./lib/libsqlite3.a ./lib/libsqlite3.dylib ./lib/libsqlite3.la ./lib/libulockmgr.0.0.0.dylib ./lib/libulockmgr.dylib ./lib/pkgconfig/sqlite3.pc And the compressed tar file is about 2.5 MB. -rw-r--r-- 1 jeffrey staff 2508800 Dec 4 13:29 src/ sqlite3.6.6.2.osx-powerpc.tar.gz If anyone is interested in it, just let me know what I need to do to upload the compressed tar file. Jeffrey Thompson jthompsonic-Re5JQEeQqe8AvxtiuMwx3w< at >public.gmane.org http://itdiscuss.org _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cg Jeffrey Thompson 2008-12-04T18:48:42 http://permalink.gmane.org/gmane.comp.db.sqlite.general/43100 Brad, Puneet, Thanks for getting back to me so quickly. I feel like a fool ;-) The error of "misuse of aggregate function MAX()" confused me. I ran the same query in SQL Server, got an error message that I understood and realised instantly that my brain wasn't switched on and that I needed to do it exactly as shown in Puneet's example. Lesson of the day for me is: "If something doesn't work in Sqlite, try the same thing in SQL Server before assuming the problem is with Sqlite as opposed to me!" Cheers Keith P Kishor-3 wrote: bondington 2008-12-04T17:17:10 http://permalink.gmane.org/gmane.comp.db.sqlite.general/43099 Hello Donald, I have managed to speed up some of my SQLite queries by about 300% by writing them from joins to sub queries. I am comparing SQLite against a custom SQL implementation we have here. It looks like it is our cursor implementation that is so much faster than SQLite the regular queries are roughly comparable. I'm going to step through our custom SQL cursor code and see if how it is managing that sort of speed. I can't really post my table schema without first obscuring like I did the original test queries it as it contains sensitive information but I'll try taking a look at the schema output. Daniel -----Original Message----- From: sqlite-users-bounces-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org [mailto:sqlite-users-bounces-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org] On Behalf Of Griggs, Donald Sent: Wednesday, December 03, 2008 3:51 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite performance woe Hi again, Daniel, So I guess you're still having certain queries that take a Brown, Daniel 2008-12-04T16:54:25 http://permalink.gmane.org/gmane.comp.db.sqlite.general/43098 As Brad said, your query doesn't make much sense as is, but you can still do it like so -- [10:15 PM] ~/Sites$sqlite3 SQLite version 3.5.9 Enter ".help" for instructions sqlite> create table foo (a); sqlite> insert into foo values ('2008-12-01'); sqlite> insert into foo values ('2008-12-02'); sqlite> insert into foo values ('2008-12-03'); sqlite> select * from foo; 2008-12-01 2008-12-02 2008-12-03 sqlite> select max(a) from foo; 2008-12-03 sqlite> select min(a) from foo; 2008-12-01 sqlite> select * from foo where date('now') > (select max(a) from foo); 2008-12-01 2008-12-02 2008-12-03 sqlite> select * from foo where date('now') < (select max(a) from foo); sqlite> P Kishor 2008-12-04T16:43:41 http://permalink.gmane.org/gmane.comp.db.sqlite.general/43097 What is it that you are actually trying to do with this query? As formulated (even if it were syntactically correct, which I don't think it is), you are either going to get every row in the table, or no rows at all. Since the current date ("now") is either greater than the maximum date in the table, or it is not, and you're not comparing to a column in each row, only the aggregate, the resulting condition will either be true for every row in the table, or false for every row. Brad _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Brad Stiles 2008-12-04T16:37:31 http://permalink.gmane.org/gmane.comp.db.sqlite.general/43096 Hi, I've been using SQL for years in SQL Server but I've only recently started using Sqlite. I've got to grip with most of the little differences but I can't for the life of me figure out how to do a query where I compare the current date with the maximum date of a field in a table. E.g. SELECT * FROM MyTableWithDates WHERE datetime("now") > MAX(dtEndDate) I keep getting an error of "misuse of aggregate function MAX()" I've defined dtEndDate as DATE and as TIMESTAMP but its made no difference. I've also tried changing the line to things like WHERE datetime("now") > MAX(datetime(dtEndDate)) but that hasn't worked. Please help because this driving me mad! Cheers bondington 2008-12-04T16:29:32 http://permalink.gmane.org/gmane.comp.db.sqlite.general/43095 Transactions are global on a database connection. Once you issue a BEGIN TRANSACTION; on a database connection then all work on that same connection is part of that transaction untill you COMMIT or ROLLBACK the transaction. -Steve _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Stephen Woodbridge 2008-12-04T15:24:20 http://permalink.gmane.org/gmane.comp.db.sqlite.general/43094 Regarding: "Does there any test case exist for ODBC testing of sqlite, how one can be sure about sqlite working with ODBC." ========================================= Sqlite itself does not interface directly with ODBC. Some nice folks have written ODBC wrappers, though, and it is those folks you'd want to contact regarding their testing. I don't know what wrapper you're using, but one nice ODBC implementation is provided by Christian Werner at: http://www.ch-werner.de/sqliteodbc/ _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Griggs, Donald 2008-12-04T15:18:36 http://permalink.gmane.org/gmane.comp.db.sqlite.general/43093 Dear Cariotoglou Mike, I am using VC++ for last 6 years to develop utilities and some small projects. Formerly I was using MS-ACCESS and/or some database classes in my projects. Once I felt the power of sqlite I started using it from 2003 / 2004. After that I have not used any ADO / ODBC for my databases. I have felt the easyness of the moto "NO CONFIGURATION" of sqlite. I have developed some very use full VC++ classes to handle the sqlite database file. I was producing reports with my-own list-view control. Now some simple formated reports are developed but if any changes in the report will result a total EXE replace. That's why I am trying some external utility that could support my EXE to produce simple formated reports. I noticed that your DLL can open some FR3 or some FRF extention files. THE NEED IS THAT REPORT FORMAT WILL BE SAVED AS AN EXTERNAL FILE OF THE FORMAT THAT YOUR DLL CAN READ AND ALLIGN THE REPORT-VIEWER WINDOW FOR PRINTING. The dataset that you specified can be XML or sqlite database it Rajesh Nair 2008-12-04T14:43:29 http://permalink.gmane.org/gmane.comp.db.sqlite.general/43092 In the latest CVS code, the Unix interface for SQLite has been extensively reorganized and cleaned up. It passes all regression tests on Linux and MacOSX and so we have high confidence in it. Nevertheless, we would appreciate it if people could test out the latest code from CVS on Unix systems other than Linux and MacOSX. Thanks. D. Richard Hipp drh-X1OJI8nnyKUAvxtiuMwx3w< at >public.gmane.org _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp 2008-12-04T14:20:23 http://permalink.gmane.org/gmane.comp.db.sqlite.general/43091 "Hernan Eguiluz" <heguiluz-/E1597aS9LQAvxtiuMwx3w< at >public.gmane.org> wrote in message news:873847.53359.qm-5/2AaPLe40MHBU+L9ui1Svu2YVrzzGjVVpNB7YpNyf8< at >public.gmane.org SELECT column1, column1 LIKE '%PATTERN%', column2, column2 LIKE '%PATTERN%' from mytable WHERE column1 LIKE '%PATTERN%' or column2 LIKE '%PATTERN%'; The two new columns would contain 0 or 1. If you don't want to repeat each pattern twice, you could try something like this: select column1, matches1, column2, matches2 from (select column1, column1 LIKE '%PATTERN%' matches1, column2, column2 LIKE '%PATTERN%' matches2 from mytable) where matches1 or matches2; Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Igor Tandetnik 2008-12-04T13:32:22 http://permalink.gmane.org/gmane.comp.db.sqlite.general/43090 Hi, I need to search for matches of a string in two different fields of a database table (column1, column2) and need to know which on of the two matched. I can do this (ignoring duplicates) with the following code: SELECT column1, 1 FROM TABLE WHERE column1 LIKE "%PATTERN%" UNION SELECT column2, 2 FROM TABLE WHERE column2 LIKE "%PATTERN%" the 1 and 2 indicate which column matched and will be by a higher level of my program. But, for performance reasons, I would like to scan the table once (it can have many records.) The problem is that a query like this SELECT column1, column2 from TABLE WHERE column1 LIKE "%PATTERN%" or column2 LIKE "%PATTERN%" won't tell me what column matched the LIKE. Is there a way to achieve this? Thanks in advance, Hernan. _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Hernan Eguiluz 2008-12-04T06:25:17 http://permalink.gmane.org/gmane.comp.db.sqlite.general/43089 "Ernany" <ernany03-Re5JQEeQqe8AvxtiuMwx3w< at >public.gmane.org> wrote in message news:f53ac9f0812040225s5d6bdd8di6af3941ab4343f6-JsoAwUIsXosN+BqQ9rBEUg< at >public.gmane.org Keep the first instance of "set", remove the other two. As in update Bens set STATUS=..., Responsavel=..., Country=...; See ATTACH statement: http://sqlite.org/lang_attach.html Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Igor Tandetnik 2008-12-04T12:46:33 http://permalink.gmane.org/gmane.comp.db.sqlite.general/43088 Thanks for responses :) Whereby "objects" you mean "rows," no? You are getting upward of 5500 Yep, I meant rows. Inserting rows in the data table is much faster (1.000.000 in 20 secs or so). I am of course not expecting that inserts into a tree is as fast as a flat table, but a little boost wouldnt hurt. Its probably worth mentioning, that I am using a memory resident db, which I create at startup. The idea is to simply have a fast memory cache. I am have tried to wrap it inside a transaction, my pattern is basically sqlite3_exec(db, "BEGIN TRANSACTION;", 0, 0, 0); sqlite3_prepare_v2( <main table>); sqlite3_prepare_v2( <rtree>); for(.....) { sqlite3_bind(<main table>); sqlite3_step(<main table>); sqlite3_reset(<main table>); sqlite3_bind(<rtree>); sqlite3_step(<rtree>); sqlite3_reset(<rtree>); } sqlite3_finalize(<main table>); sqlite3_finalize(<rtree>); sqlite3_exec(db, "COMMIT TRANSACTION;", 0, 0, 0); One thing I havent figured out, is, how is a transaction related to prepared statements ? Oyvind Idland 2008-12-04T11:50:49 http://permalink.gmane.org/gmane.comp.db.sqlite.general/43087 Hi, Does there any test case exist for ODBC testing of sqlite, how one can be sure about sqlite working with ODBC. Regards _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users goldy 2008-12-04T10:53:47 Search the mailing list at Gmane query http://search.gmane.org/?group=$group=gmane.comp.db.sqlite.general