Re: [sqlite] Parallel execution of queries in threads
I checked with following scenario. There are 2 script 1) continuously inserting a row at a time in table with exclusive lock mode. 2) executing 4 SELECT queries. e.g. o SELECT column1, SUM(column2) FROM table_name WHERE column3 BETWEEN X AND Y GROUP BY column1; o SELECT column4, SUM(column2) FROM table_name WHERE column3 BETWEEN X AND Y GROUP BY column4; o SELECT column5, SUM(column2) FROM table_name WHERE column3 BETWEEN X AND Y GROUP BY column5; o SELECT column6, SUM(column2) FROM table_name WHERE column3 BETWEEN X AND Y GROUP BY column6; And I got same result in Sqlite 3.7.5 and 3.7.4. Thanks, Madhav On Fri, Feb 25, 2011 at 7:28 PM, Maddy wrote: > I had tried on sqlite version 3.7.4. > Let me check it on sqlite version 3.7.5. > > Thanks, > Madhav > > > On Thu, Feb 24, 2011 at 9:19 PM, Richard Hipp wrote: > >> >> >> On Thu, Feb 24, 2011 at 4:52 AM, Maddy wrote: >> >>> Hi, >>> I have 4 "SELECT" queries which are accessing same table. >>> When I executed these queries concurrently using pthreads in Linux, it >>> took >>> approximate 10 Seconds. >>> But same 4 queries executed sequentially it only took 2 seconds. >>> Total Rows in table are 10. >>> Sample query is "SELECT column1, sum(column2) from TABLE_NAME;" >>> >>> What can be the reason behind this? >>> >> >> >> Have you tried this using SQLite version 3.7.5 or are you using a prior >> version of SQLite? >> >> >> -- >> D. Richard Hipp >> d...@sqlite.org >> > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help for Garbled
>> for example, i insert String filled with gbk bytes and set the String >> charset 'UTF-8' > >What do you mean, "set String charset"? Set how and where? >-- >Igor Tandetnik Hi The String on java side is ok. > for example, i insert String filled with gbk bytes and set the String charset > 'UTF-8' means this: byte[] bytes = Utils.getGBKBytesFromUTF8(String Chinese); //convert the bytes String str = new String(bytes, "UTF-8"); I know this will be wrongNo doubt, but i woulld to find a type like this. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help for Garbled
On 3/1/2011 9:49 PM, 陈强 wrote: > How did the sqlite.exe work if insert data through it? > insert gbk string? Most likely. I wouldn't be surprised if these strings came out wrong on Java side, though. > can I insert data like it ? Probably, but I don't think you really want to. It'll be a struggle to handle such text in Java. > for example, i insert String filled with gbk bytes and set the String charset > 'UTF-8' What do you mean, "set String charset"? Set how and where? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help for Garbled
>Try running this command before starting sqlite.exe, but I'm not sure >this will help: > >mode con cp select=65001 Hi I tried to change the charset of console to utf-8 before, but it didn't work correctly. Because the app will besent to custom, so i want to make sqlite.exe display correctly. How did the sqlite.exe work if insert data through it? insert gbk string? can I insert data like it ? for example, i insert String filled with gbk bytes and set the String charset 'UTF-8' ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help for Garbled
On 3/1/2011 8:52 PM, 陈强 wrote: > But if i want to make the gbk console show String correctly, what should i do? Try running this command before starting sqlite.exe, but I'm not sure this will help: mode con cp select=65001 There also exists a number of nice graphical tools for working with SQLite databases: http://www.sqlite.org/cvstrac/wiki?p=ManagementTools I bet at least some of them handle UTF-8 and UTF-16 correctly. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help for Garbled
At 2011-03-02 09:17:48，"Igor Tandetnik" wrote: >On 3/1/2011 8:05 PM, 陈强 wrote: >> At 2011-03-01 20:56:49，"Igor Tandetnik" wrote: >> >>> 陈强 wrote: Why is the Chinese i Selected from SQLIte.exe and jdbc is not different. The Chinese from JDBC(rs.getString(index)) is normal but the Chinese from Sqlite.exe (Select data) is Garbled, my environment as list: >>> >>> Console window doesn't know UTF-8. The strings are fine, but the console >>> can't render them correctly. >> >> The charSet of the String insert into db is GBK, the console should show >> GBK String correctly. > >I'd double-check that if I were you. That would be highly unusual. How >do you put those strings into the database? If with a Java program, >realize that Java always represents all strings in UTF-16. > >I stronly doubt a simple rs.getString(index) would have worked correctly >if the string weren't stored in UTF-16 or UTF-8 in the database. SQLite >certainly wouldn't automatically convert from GBK to UTF-16 that Java >requires. > > >Show the output of this query, when run in sqlite.exe: > >select hex(MyStringField) from MyTable limit 10; > >Substitute your actual table and column names for MyTable and >MyStringField, of course. >-- >Igor Tandetnik > Hi Thanks a lot for your help! I'am a javaBeginner. I will send the output of the query as soon as possible, because it's not in my pc. But if i want to make the gbk console show String correctly, what should i do? i know if i put the gbk bytes into db, the String will be show correctly, but the column type will be blob, it's not the result i wanna. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help for Garbled
On 3/1/2011 8:05 PM, 陈强 wrote: > At 2011-03-01 20:56:49，"Igor Tandetnik" wrote: > >> 陈强 wrote: >>> Why is the Chinese i Selected from SQLIte.exe and jdbc is not different. >>> The Chinese from JDBC(rs.getString(index)) is normal but >>> the Chinese from Sqlite.exe (Select data) is Garbled, my environment as >>> list: >> >> Console window doesn't know UTF-8. The strings are fine, but the console >> can't render them correctly. > > The charSet of the String insert into db is GBK, the console should show GBK > String correctly. I'd double-check that if I were you. That would be highly unusual. How do you put those strings into the database? If with a Java program, realize that Java always represents all strings in UTF-16. I stronly doubt a simple rs.getString(index) would have worked correctly if the string weren't stored in UTF-16 or UTF-8 in the database. SQLite certainly wouldn't automatically convert from GBK to UTF-16 that Java requires. Show the output of this query, when run in sqlite.exe: select hex(MyStringField) from MyTable limit 10; Substitute your actual table and column names for MyTable and MyStringField, of course. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Fwd: Fwd: Fwd: fts virtual table questions
My problem is the term "virtual" I guess. I think of virtual as in-memory only or something. Once they are created in a SQLite database they seem to be as real as the other tables. >> I had an API problem, and I now think that the virtual table does not need >> to be created each time. I can't find documentation that covers this. > > I don't think there is documentation which covers this, because it's > how all tables (except temp tables) work. You create them, they're in > the database until you drop them. Virtual tables are slightly > different in that you could attempt to read the database with a sqlite > library which doesn't include the virtual table implementation, which > would naturally fail. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help for Garbled
At 2011-03-01 20:56:49，"Igor Tandetnik" wrote: >陈强 wrote: >> Why is the Chinese i Selected from SQLIte.exe and jdbc is not different. The >> Chinese from JDBC(rs.getString(index)) is normal but >> the Chinese from Sqlite.exe (Select data) is Garbled, my environment as >> list: > >Console window doesn't know UTF-8. The strings are fine, but the console can't >render them correctly. >-- >Igor Tandetnik > hi. The charSet of the String insert into db is GBK, the console should show GBK String correctly. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Source code for system.data.sqlite
Since sqlite has taken on system.data.sqlite for .net you will be seeing issues on this until a forum is set up for it. Please try to make sure the entire source tips are available for download. The last time I checked 1.0.68 (or ?) did not compile due to missing linq related files. Hopefully there would be some way to contribute source changes to the project in future, there is no way now. There is also no way to add on to issues in the "tracker", and no way to distinguish contributors to issues. I can't get into the dev forum or I would post this there. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fwd: Fwd: fts virtual table questions
On Tue, Mar 1, 2011 at 4:54 PM, Paul Shaffer wrote: > I had an API problem, and I now think that the virtual table does not need > to be created each time. I can't find documentation that covers this. I don't think there is documentation which covers this, because it's how all tables (except temp tables) work. You create them, they're in the database until you drop them. Virtual tables are slightly different in that you could attempt to read the database with a sqlite library which doesn't include the virtual table implementation, which would naturally fail. > The question on threading the virtual table construction still stands. It's just like any other SQLite threading. fts is internally implemented by calling back into SQLite core. -scott ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Fwd: Fwd: fts virtual table questions
I had an API problem, and I now think that the virtual table does not need to be created each time. I can't find documentation that covers this. The question on threading the virtual table construction still stands. If I don't have any response in a day or so I will close this issue. Original Message Subject: Fwd: fts virtual table questions Date: Mon, 28 Feb 2011 22:48:58 -0700 From: Paul Shaffer To: General Discussion of SQLite Database Edit: with doing a create virtual call --> without doing a create virtual call Original Message Subject: fts virtual table questions Date: Mon, 28 Feb 2011 22:46:32 -0700 From: Paul Shaffer To: General Discussion of SQLite Database I am still testing my results and am new to fts. To use fts, is "create virtual table" required every time my program runs and opens the sqlite database? I see that the table (plus a few other related fts tables) is physically present in the database after my program closes. I would prefer to not recreate this table every time I launch my windows forms application. I have tried to use the existing table with doing a create virtual call, but I get table not found exception. The table takes an additional 10 seconds or so to load at program start. If I have to create the virtual table every time my program runs, I will first resort to creating the table on a background thread. Do you know of any problems doing this aside from the usual threading concerns? How is this usually handled in desktop applications? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] tclsqlite Precompiled binary for tcl
Hi, is the precompiled binary for tcl not longer available? Best Regards ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query help
On 3/1/2011 6:47 PM, Jeff Archer wrote: > I think it will just happen to work out if I could get the first row > for each ImageID since the values should have been entered in > ascending order. I realize this will probably not be guaranteed to > get lowest X,Y but for my purpose at the moment this is OK. select * from Defects, Images where Defects.DefectID = ( select min(d2.DefectID) from Defects d2 where d2.ImageId = Images.ImageId ); -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query help
>From: Igor Tandetnik [mailto:itandet...@mvps.org] >Sent: Tuesday, March 01, 2011 5:47 PM > >On 3/1/2011 5:01 PM, Jeff Archer wrote: >> And this select which I would like to modify to only return lowest X,Y >> value for each ImageID. > >What does "lowest" mean? If you have two points (100, 200) and (200, 100), >which one is "lower"? >-- >Igor Tandetnik > Sorry, I guess I wasn't very clear. For this purpose either would be fine. The values should tend to be (1.5, 1.5). I think it will just happen to work out if I could get the first row for each ImageID since the values should have been entered in ascending order. I realize this will probably not be guaranteed to get lowest X,Y but for my purpose at the moment this is OK. Thanks, Jeff ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query help
On 3/1/2011 5:01 PM, Jeff Archer wrote: > And this select which I would like to modify to only return lowest X,Y value > for each ImageID. What does "lowest" mean? If you have two points (100, 200) and (200, 100), which one is "lower"? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Query help
Hi all, and thanks in advance for you help. I have the following schema CREATE TABLE [Scans] (ScanIDINTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,Timestamp DATETIME NOT NULL UNIQUE ,EndTime DATETIME NOT NULL DEFAULT CURRENT_TIME ,ResultVARCHAR ); CREATE TABLE [Images] (ImageID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,ScanIDINTEGER NOT NULL REFERENCES [Scans] DEFERRABLE INITIALLY DEFERRED ,Filename VARCHAR NOT NULL ,NoINTEGER NOT NULL ,X REAL NOT NULL DEFAULT 0.0 ,Y REAL NOT NULL DEFAULT 0.0 ,Z REAL NOT NULL DEFAULT 0.0 ,R INTEGER NOT NULL DEFAULT 0 ,C INTEGER NOT NULL DEFAULT 0 ,wMicrons REAL NOT NULL DEFAULT 0.0 ,hMicrons REAL NOT NULL DEFAULT 0.0 ,wPixels INTEGER NOT NULL DEFAULT 0 ,hPixels INTEGER NOT NULL DEFAULT 0 ,UNIQUE (ScanID, Filename, No)); CREATE INDEX Images_ScanID_Index on Images(ScanID); CREATE TABLE [Analyzers] (AnalyzerIDINTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,Name VARCHAR NOT NULL UNIQUE ); CREATE TABLE [Analysis] (AnalysisIDINTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,AnalyzerIDINTEGER NOT NULL REFERENCES [Analyzers] DEFERRABLE INITIALLY DEFERRED ,ScanIDINTEGER NOT NULL REFERENCES [Scans] DEFERRABLE INITIALLY DEFERRED ,Timestamp DATETIME NOT NULL ,EndTime DATETIME NOT NULL DEFAULT CURRENT_TIME ,NumDefectsINTEGER DEFAULT 0 ,ResultVARCHAR ); CREATE INDEX Analysis_AnalyzerID_Index on Analysis(AnalyzerID); CREATE INDEX Analysis_ScanID_Index on Analysis(ScanID); CREATE TABLE [Defects] (DefectID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,ImageID INTEGER NOT NULL REFERENCES [Images] DEFERRABLE INITIALLY DEFERRED ,AnalysisIDINTEGER NOT NULL REFERENCES [Analysis] DEFERRABLE INITIALLY DEFERRED ,X REAL NOT NULL DEFAULT 0.0 ,Y REAL NOT NULL DEFAULT 0.0 ,W REAL NOT NULL DEFAULT 0.0 ,H REAL NOT NULL DEFAULT 0.0 ,Area REAL NOT NULL DEFAULT 0.0 ); And this select which I would like to modify to only return lowest X,Y value for each ImageID. select Defects.DefectID , Defects.ImageID , Defects.AnalysisID , Defects.X , Defects.Y , Defects.W , Defects.H , Defects.Area , Images.X + Defects.X as DefectX , Images.Y + Defects.Y as DefectY from Defects join Images on Defects.ImageID = Images.ImageID I believe that the data is all stored such that the first stored defect for each ImageID will have the lowest X,Y values. At least for now this assumption is probably good enough. Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819-4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Re ad Lock not released..
On Tue, Mar 01, 2011 at 12:20:32PM -0800, mmudi scratched on the wall: > > We are using sqlite version 3.3.17 in a production environment, and are > facing an issue where a particular process is not releasing the read lock on > the sql file. This locks up the database when any write operations are > attempted by other processes. The process under question is not multi > threaded and stack trace reveals that the process is not stuck in an SQLite > library call but is processing other requests. > > A code review of the DB access API has not revealed any resources or calls > that have not been closed/freed properly. This can be verified by attempting to close the database connection. If there are still unresolved statements, an error will be returned. Additionally, you must actually call _reset() or _finalized() on a statement, even if it is run until _step() returns SQLITE_DONE. In some cases, in some versions of SQLite, the locks are not released until the statement is actually reset/finalized. > Questions > -- > From my debugging efforts, the issue appears to be that the process entered > the sqlite library at some point in its operation, acquired a read lock, and > returned from the library, but failed to release the read lock. Under what > circumstances can this occur? Many. But if you're correctly resetting all your statements, the most likely cause is an SQL "BEGIN" was issued without a corresponding "COMMIT" or "ROLLBACK." -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Re ad Lock not released..
> From my debugging efforts, the issue appears to be that the process entered > the sqlite library at some point in its operation, acquired a read lock, and > returned from the library, but failed to release the read lock. Under what > circumstances can this occur? This is a normal behavior for SQLite. When you start to execute some SELECT query first call to sqlite3_step acquires read lock on the database and returns to you (most probably with SQLITE_ROW result code). And then read lock is not released until you call sqlite3_reset or sqlite3_finalize on this statement. So check if you have some not finalized statements - they will hold read lock for you. Pavel On Tue, Mar 1, 2011 at 3:20 PM, mmudi wrote: > > We are using sqlite version 3.3.17 in a production environment, and are > facing an issue where a particular process is not releasing the read lock on > the sql file. This locks up the database when any write operations are > attempted by other processes. The process under question is not multi > threaded and stack trace reveals that the process is not stuck in an SQLite > library call but is processing other requests. > > A code review of the DB access API has not revealed any resources or calls > that have not been closed/freed properly. > > Questions > -- > From my debugging efforts, the issue appears to be that the process entered > the sqlite library at some point in its operation, acquired a read lock, and > returned from the library, but failed to release the read lock. Under what > circumstances can this occur? > > Is there a known issue in the sqlite library which fixes a similar problem, > that would necessitate us to upgrade to a newer version? > > Thanks in advance > Madhu > > -- > View this message in context: > http://old.nabble.com/Read-Lock-not-released..-tp31043990p31043990.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] EXT :Re: long insert statement failing on iPhone
pavel, > > I suspect that "as soon as the application launches" means that no > parallel threads are working yet (if they work already then look into > what those threads are doing). Then the problem could be in some > linking issues. E.g. you have some function that is named the same as > some library function and so you and/or SQLite calls your function > instead of from library. Or maybe some library you link with does > something bad (and maybe in a different thread). Or it could be just > different set of memory allocation calls so that in a different app > hit to incorrect memory address doesn't touch another memory region > which is corrupted in the initial app. Still running with valgrind > could give some insights. take a look at my last email about compilers. and yes, no other threads are started, i'm doing this in a method called directly from applicationDidFinishLaunching:, the first real method you have access to after an app fires up. if we decide to go down the road of trying to track the bug down in the sqlite code, then i'll definitely check out valgrind. thanks, mickm ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Re ad Lock not released..
We are using sqlite version 3.3.17 in a production environment, and are facing an issue where a particular process is not releasing the read lock on the sql file. This locks up the database when any write operations are attempted by other processes. The process under question is not multi threaded and stack trace reveals that the process is not stuck in an SQLite library call but is processing other requests. A code review of the DB access API has not revealed any resources or calls that have not been closed/freed properly. Questions -- >From my debugging efforts, the issue appears to be that the process entered the sqlite library at some point in its operation, acquired a read lock, and returned from the library, but failed to release the read lock. Under what circumstances can this occur? Is there a known issue in the sqlite library which fixes a similar problem, that would necessitate us to upgrade to a newer version? Thanks in advance Madhu -- View this message in context: http://old.nabble.com/Read-Lock-not-released..-tp31043990p31043990.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] EXT :Re: long insert statement failing on iPhone
hi all, i've just discovered that the bug is there when compiling with the LLVM compiler 1.6, which comes as a standard compiler with the iOS SDK, and is the direction that Apple is moving towards. the problem is NOT there when compiling with GCC 4.2. the problem is NOT there when compiling with LLVM GCC 4.2, which is described as "GCC 4.2 front-end with LLVM code generator". so for the moment we are going to try going ahead with the LLVM GCC 4.2 path. but there is something down in the parser code that LLVM doesn't like, even though it doesn't come up with a warning or error. we found earlier that when compiling with LLVM, there were issues with the following statement: rc = sqlite3PagerSetPagesize(pBt->pPager, &pBt->pageSize); in the function SQLITE_PRIVATE int sqlite3BtreeOpen. &pBt->pageSize was a null value when entering the function it was passed to. we ended up simply doing: u16 *pPageSize = &(pBt->pageSize); rc = sqlite3PagerSetPagesize(pBt->pPager, pPageSize ); so there are some issues with the LLVM compiler. will post again when i see what works, and what path we are going. it would obviously be nice to find out where in the sqlite code this is happening, but we just don't have the time to devote to that, unfortunately. mickm ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EXT :Re: long insert statement failing on iPhone
> i also took the same statement and tried to run it in our app in a > method called as soon as the application launches, and it fails. so we have > something going on at a different level. I suspect that "as soon as the application launches" means that no parallel threads are working yet (if they work already then look into what those threads are doing). Then the problem could be in some linking issues. E.g. you have some function that is named the same as some library function and so you and/or SQLite calls your function instead of from library. Or maybe some library you link with does something bad (and maybe in a different thread). Or it could be just different set of memory allocation calls so that in a different app hit to incorrect memory address doesn't touch another memory region which is corrupted in the initial app. Still running with valgrind could give some insights. Pavel On Tue, Mar 1, 2011 at 12:32 PM, Mickey Mestel wrote: > michael, > >> Have you run your test with and without crypto? If Apple can compile >> 3.6.23.2 to work you should be able to also (might be overly optimistic here >> but compilers are 100% deterministic, although not necessartiliy 100% binary >> match). > > we have encrypted and read a database with what we have in place > currently, but we aren't doing that at the moment. > > some somewhat disturbing news. i took the same sqlite.c file that is > built into our project and built another project, and prepared and ran the > same statement, and it succeeded. > > i also took the same statement and tried to run it in our app in a > method called as soon as the application launches, and it fails. so we have > something going on at a different level. > > i think now it is just pounding heads against the wall until it is > found. > > but any other thoughts are certainly welcome! > > mickm > ___ > 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] adding fdopen to VFS?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/01/2011 01:07 AM, Philip Graham Willoughby wrote: > I argue that this implies a problem in the Chromium sandbox rather than a > problem in the SQLite code. SQLite's implementation makes things worse because the xOpen implementation is not 5 lines of code wrapping a call to open() but considerably more meaning that overriding it is a lot of work. As for the sandbox, the traditional Unix mechanism for doing that has been chroot which works well for daemons, but is too hard to use for something like the Flash player or webkit renderer. Some docs: http://lwn.net/Articles/347547/ http://code.google.com/p/seccompsandbox/wiki/overview Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk1tLiMACgkQmOOfHg372QQlxgCgjEyFPKg8KT2riZitw6hcX/78 vAwAoNo3h1y6exbGpmIy3eIHIvNGVStO =GvCD -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EXT :Re: long insert statement failing on iPhone
michael, > Have you run your test with and without crypto? If Apple can compile > 3.6.23.2 to work you should be able to also (might be overly optimistic here > but compilers are 100% deterministic, although not necessartiliy 100% binary > match). we have encrypted and read a database with what we have in place currently, but we aren't doing that at the moment. some somewhat disturbing news. i took the same sqlite.c file that is built into our project and built another project, and prepared and ran the same statement, and it succeeded. i also took the same statement and tried to run it in our app in a method called as soon as the application launches, and it fails. so we have something going on at a different level. i think now it is just pounding heads against the wall until it is found. but any other thoughts are certainly welcome! mickm ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ANN: C#-SQLite 3.7.5
C#-SQLite has been updated to release 3.7.5 and is now ready for use. The 2/28 release features: * SQL_HAS_CODEC compiler option * Silverlight support * Windows 7 Phone Does not support WAL It now runs 54,618 of the tcl testharness tests without errors. The project is located at http://code.google.com/p/csharp-sqlite/ Please keep in mind the following: * C#-SQLite is an independent reimplementation of the SQLite software library * This is not an official version of SQLite * Bugs should not be reported to the SQLite.org ticket tracking system SQLite® is a registered trademark of Hipp, Wyrick & Company, Inc Enjoy, Noah Hart -- View this message in context: http://old.nabble.com/ANN%3A-C--SQLite-3.7.5-tp31041201p31041201.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] sqlite3_prepare_v2 and std::string using SQLite 3.7.5 - BUG or me?
On Tue, Mar 01, 2011 at 05:13:00PM +0100, pcun...@fsmail.net scratched on the wall: > > Your examples suggest that actually sqlite3-bind_text "is not > > accepting std::string", not sqlite3_prepare_v2. But how exactly it > > "doesn't accept"? You pass SQLITE_STATIC as 5th parameter there; are you > > sure > > you don't destroy or change your strings before statement is executed? > > Correct, it is sqlite3-bind_text() with the problem, as you can see I'm not > changing the values, they are declared just before use. If you pass them as SQLITE_STATIC, the "use" of those pointers continues after the bind call. The values must remain in-scope and valid until the statement is completely finished executing, reset, or finalized. I'm also curious why you're binding text values. It seems like integers would be more appropriate. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_prepare_v2 and std::string using SQLite 3.7.5 - BUG or me?
UPDATE - Problem solved ! I found the error. I am so sorry, I try to test and be a helpful member... :( I was calling sqlite3_prepare_v2() twice and it was canceling out everything apparently. I am building these queries on the fly so I just missed that. How this happend was because when building a manual query string you call sqlite3_prepare_v2() *after* you have built a string. When your building a string and binding you call sqlite3_prepare_v2() *first* then do the binding, the finally your step statement. I just go that mixed up. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_prepare_v2 and std::string using SQLite 3.7.5 - BUG or me?
> Your examples suggest that actually sqlite3-bind_text "is not
> accepting std::string", not sqlite3_prepare_v2. But how exactly it
> "doesn't accept"? You pass SQLITE_STATIC as 5th parameter there; are you sure
> you don't destroy or change your strings before statement is executed?
Correct, it is sqlite3-bind_text() with the problem, as you can see I'm not
changing the values, they are declared just before use. A step call does
not return any vales. All previous examples do.
while (sqlite3_step(sql_statement_local) == SQLITE_ROW)
{
uint64_t iAID = sqlite3_column_int64(sql_statement_local,0);
uint64_t iSIS = sqlite3_column_int64(sql_statement_local,1);
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_prepare_v2 and std::string using SQLite 3.7.5 - BUG or me?
Try using SQLITE_TRANSIENT instead of STATIC.
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of pcun...@fsmail.net [pcun...@fsmail.net]
Sent: Tuesday, March 01, 2011 9:43 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] sqlite3_prepare_v2 and std::string using SQLite 3.7.5 -
BUG or me?
Maybe I am to sleepy, but sqlite3_prepare_v2 is not accepting a std::string as
a parameter in the following example:
// Setup table structure
SQLiteCommand("CREATE TABLE IF NOT EXISTS USR_EVENTS(AID INTEGER PRIMARY KEY
AUTOINCREMENT,USER_ID BIGINT,GROUP_ID BIGINT,SIS_EVENTS_PTR BIGINT");
SQLiteCommand("CREATE INDEX index_name3 ON USR_EVENTS (USER_ID)");
SQLiteCommand("CREATE INDEX index_name4 ON USR_EVENTS (GROUP_ID)");
SQLiteCommand("CREATE INDEX index_name5 ON USR_EVENTS (SIS_EVENTS_PTR)");
SQLiteCommand("INSERT INTO USR_EVENTS (USER_ID, GROUP_ID, SIS_EVENTS_PTR)
VALUES ('0', '3', '1')");
// This query works
sCMD = "select AID, SIS_EVENTS_PTR from USR_EVENTS where (AID > '0') and
(USER_ID = '2' or GROUP_ID = '1' or GROUP_ID = '3')";
// This query works
sCMD = "select AID, SIS_EVENTS_PTR from USR_EVENTS where (AID > :1) and
(USER_ID = :2 or GROUP_ID = :3 or GROUP_ID = :4)";
sqlite3_prepare_v2(ppDb, sCMD.c_str(), -1, &sql_statement_local, NULL);
sqlite3_bind_text(sql_statement_local, 1, "0", 1,SQLITE_STATIC);
sqlite3_bind_text(sql_statement_local, 2, "2", 1,SQLITE_STATIC);
sqlite3_bind_text(sql_statement_local, 3, "1", 1,SQLITE_STATIC);
sqlite3_bind_text(sql_statement_local, 4, "3", 1,SQLITE_STATIC);
// PROBLEM !!! This does not work??? why???
std::string sAID = "0";
std::string USER_ID = "2";
std::string GROUP_IDa = "1";
std::string GROUP_IDb = "3";
sCMD = "select AID, SIS_EVENTS_PTR from USR_EVENTS where (AID > :1) and
(USER_ID = :2 or GROUP_ID = :3 or GROUP_ID = :4)";
sqlite3_prepare_v2(ppDb, sCMD.c_str(), -1, &sql_statement_local, NULL);
sqlite3_bind_text(sql_statement_local, 1, sAID.c_str(), 1,SQLITE_STATIC);
sqlite3_bind_text(sql_statement_local, 2, USER_ID.c_str(), 1,SQLITE_STATIC);
sqlite3_bind_text(sql_statement_local, 3, GROUP_IDa.c_str(), 1,SQLITE_STATIC);
sqlite3_bind_text(sql_statement_local, 4, GROUP_IDb.c_str(), 1,SQLITE_STATIC);
___
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] sqlite3_prepare_v2 and std::string using SQLite 3.7.5 - BUG or me?
Your examples suggest that actually sqlite3-bind_text "is not
accepting std::string", not sqlite3_prepare_v2. But how exactly it
"doesn't accept"?
You pass SQLITE_STATIC as 5th parameter there; are you sure you don't
destroy or change your strings before statement is executed?
Pavel
On Tue, Mar 1, 2011 at 10:43 AM, wrote:
> Maybe I am to sleepy, but sqlite3_prepare_v2 is not accepting a std::string
> as a parameter in the following example:
>
>
> // Setup table structure
>
> SQLiteCommand("CREATE TABLE IF NOT EXISTS USR_EVENTS(AID INTEGER PRIMARY KEY
> AUTOINCREMENT,USER_ID BIGINT,GROUP_ID BIGINT,SIS_EVENTS_PTR BIGINT");
> SQLiteCommand("CREATE INDEX index_name3 ON USR_EVENTS (USER_ID)");
> SQLiteCommand("CREATE INDEX index_name4 ON USR_EVENTS (GROUP_ID)");
> SQLiteCommand("CREATE INDEX index_name5 ON USR_EVENTS (SIS_EVENTS_PTR)");
> SQLiteCommand("INSERT INTO USR_EVENTS (USER_ID, GROUP_ID, SIS_EVENTS_PTR)
> VALUES ('0', '3', '1')");
>
>
> // This query works
> sCMD = "select AID, SIS_EVENTS_PTR from USR_EVENTS where (AID > '0') and
> (USER_ID = '2' or GROUP_ID = '1' or GROUP_ID = '3')";
>
> // This query works
> sCMD = "select AID, SIS_EVENTS_PTR from USR_EVENTS where (AID > :1) and
> (USER_ID = :2 or GROUP_ID = :3 or GROUP_ID = :4)";
> sqlite3_prepare_v2(ppDb, sCMD.c_str(), -1, &sql_statement_local, NULL);
> sqlite3_bind_text(sql_statement_local, 1, "0", 1,SQLITE_STATIC);
> sqlite3_bind_text(sql_statement_local, 2, "2", 1,SQLITE_STATIC);
> sqlite3_bind_text(sql_statement_local, 3, "1", 1,SQLITE_STATIC);
> sqlite3_bind_text(sql_statement_local, 4, "3", 1,SQLITE_STATIC);
>
> // PROBLEM !!! This does not work??? why???
> std::string sAID = "0";
> std::string USER_ID = "2";
> std::string GROUP_IDa = "1";
> std::string GROUP_IDb = "3";
> sCMD = "select AID, SIS_EVENTS_PTR from USR_EVENTS where (AID > :1) and
> (USER_ID = :2 or GROUP_ID = :3 or GROUP_ID = :4)";
> sqlite3_prepare_v2(ppDb, sCMD.c_str(), -1, &sql_statement_local, NULL);
> sqlite3_bind_text(sql_statement_local, 1, sAID.c_str(), 1,SQLITE_STATIC);
> sqlite3_bind_text(sql_statement_local, 2, USER_ID.c_str(), 1,SQLITE_STATIC);
> sqlite3_bind_text(sql_statement_local, 3, GROUP_IDa.c_str(), 1,SQLITE_STATIC);
> sqlite3_bind_text(sql_statement_local, 4, GROUP_IDb.c_str(), 1,SQLITE_STATIC);
>
>
> ___
> 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_prepare_v2 and std::string using SQLite 3.7.5 - BUG or me?
Maybe I am to sleepy, but sqlite3_prepare_v2 is not accepting a std::string as
a parameter in the following example:
// Setup table structure
SQLiteCommand("CREATE TABLE IF NOT EXISTS USR_EVENTS(AID INTEGER PRIMARY KEY
AUTOINCREMENT,USER_ID BIGINT,GROUP_ID BIGINT,SIS_EVENTS_PTR BIGINT");
SQLiteCommand("CREATE INDEX index_name3 ON USR_EVENTS (USER_ID)");
SQLiteCommand("CREATE INDEX index_name4 ON USR_EVENTS (GROUP_ID)");
SQLiteCommand("CREATE INDEX index_name5 ON USR_EVENTS (SIS_EVENTS_PTR)");
SQLiteCommand("INSERT INTO USR_EVENTS (USER_ID, GROUP_ID, SIS_EVENTS_PTR)
VALUES ('0', '3', '1')");
// This query works
sCMD = "select AID, SIS_EVENTS_PTR from USR_EVENTS where (AID > '0') and
(USER_ID = '2' or GROUP_ID = '1' or GROUP_ID = '3')";
// This query works
sCMD = "select AID, SIS_EVENTS_PTR from USR_EVENTS where (AID > :1) and
(USER_ID = :2 or GROUP_ID = :3 or GROUP_ID = :4)";
sqlite3_prepare_v2(ppDb, sCMD.c_str(), -1, &sql_statement_local, NULL);
sqlite3_bind_text(sql_statement_local, 1, "0", 1,SQLITE_STATIC);
sqlite3_bind_text(sql_statement_local, 2, "2", 1,SQLITE_STATIC);
sqlite3_bind_text(sql_statement_local, 3, "1", 1,SQLITE_STATIC);
sqlite3_bind_text(sql_statement_local, 4, "3", 1,SQLITE_STATIC);
// PROBLEM !!! This does not work??? why???
std::string sAID = "0";
std::string USER_ID = "2";
std::string GROUP_IDa = "1";
std::string GROUP_IDb = "3";
sCMD = "select AID, SIS_EVENTS_PTR from USR_EVENTS where (AID > :1) and
(USER_ID = :2 or GROUP_ID = :3 or GROUP_ID = :4)";
sqlite3_prepare_v2(ppDb, sCMD.c_str(), -1, &sql_statement_local, NULL);
sqlite3_bind_text(sql_statement_local, 1, sAID.c_str(), 1,SQLITE_STATIC);
sqlite3_bind_text(sql_statement_local, 2, USER_ID.c_str(), 1,SQLITE_STATIC);
sqlite3_bind_text(sql_statement_local, 3, GROUP_IDa.c_str(), 1,SQLITE_STATIC);
sqlite3_bind_text(sql_statement_local, 4, GROUP_IDb.c_str(), 1,SQLITE_STATIC);
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help for Garbled
陈强 wrote: > Why is the Chinese i Selected from SQLIte.exe and jdbc is not different. The > Chinese from JDBC(rs.getString(index)) is normal but > the Chinese from Sqlite.exe (Select data) is Garbled, my environment as list: Console window doesn't know UTF-8. The strings are fine, but the console can't render them correctly. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EXT :Re: long insert statement failing on iPhone
Have you run your test with and without crypto? If Apple can compile 3.6.23.2 to work you should be able to also (might be overly optimistic here but compilers are 100% deterministic, although not necessartiliy 100% binary match). Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Mickey Mestel [mmes...@epocrates.com] Sent: Monday, February 28, 2011 4:11 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] EXT :Re: long insert statement failing on iPhone michael, > No such thing as "close enough" when it comes to different versions. > i do agree, although having tried 3 different version with exactly the same behavior, i don't expect 3.6.23.2 to act any different, although you never know. > Can somebody find the 3.6.23.2 amalgamation for this guy? if someone has one, i would love to give it a try. mickm ___ 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] adding fdopen to VFS?
On 28 Feb 2011, at 20:38, Roger Binns wrote: >> Why is it not possible to create a custom VFS for the intended purpose >> and register it for the connections that need it? > > Because that custom VFS would be an almost duplicate of the existing VFS but > with a few key places changed. > > If the changes were at the granularity of the VFS methods then it would be > no problem to "inherit" as needed. Unfortunately they are within - for > example wanting to use the xOpen method but changing only the open() call > within. I argue that this implies a problem in the Chromium sandbox rather than a problem in the SQLite code. Either the Chromium sandbox does not allow files to be opened at all (in which case you can't have SQLite) or it does. If it does, why is the routine that allows you to do so named something other than open()? If you cannot open files but can ask a supervisor to open files on your behalf, the natural thing to do is to write a routine named open() that invokes the supervisor and give it the semantics of POSIX open(). Similarly for close() stat() and the various locking calls. Not only would this make SQLite work unmodified but it would make the next n things that have a similar issue work for free as well. Best Regards, Phil Willoughby -- Managing Director, StrawberryCat Limited StrawberryCat Limited is registered in England and Wales with Company No. 7234809. The registered office address of StrawberryCat Limited is: 107 Morgan Le Fay Drive Eastleigh SO53 4JH ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
