Re: [sqlite] FTS5 MATCH using "string*" matches just the exact string

2018-09-10 Thread Eric Minbiole
> SELECT rowid,text > FROM table > WHERE table MATCH 'أعلم*'; > > And I have to add this that my data is Arabic text. > > This method must find words that contains 'أعلم' but it doesn't. What > should I do now. > > I just tried this in SQLite version 3.24.0 on a Mac, and it seems to work. Please

Re: [sqlite] New draft document on the new pointer-passing interfaces

2017-07-24 Thread Eric Minbiole
> Thank you very much for this, detailed, informative write-up, Dr Hipp. It's very helpful to see the sensible rationale behind the new interfaces. Thanks for continuing to enhance the API! ___ sqlite-users

Re: [sqlite] LIMIT doesn't return expected rows

2016-10-11 Thread Eric Minbiole
> Your problem is that although SQL accepts the clauses written in the order > you wrote them in, the LIMIT clause is processed before the ORDER BY clause. > > Is that really true? I had always thought that the ORDER BY was processed first, though I admit I don't see anything authoritative either

Re: [sqlite] unreached code in sqlite3.c?

2015-02-12 Thread Eric Minbiole
> If the statement will never be executed the compiler should simply optimise > it away. No warning is necessary unless you turn on some flag that highlights > such optimisations. Per the original post, I believe that the warning was expressly enabled using the [-Wunreachable-code] compiler

Re: [sqlite] Bug: analyze.c does not honor SQLITE_64BIT_STATS

2013-10-09 Thread Eric Minbiole
> With this change, tests pass again: > > #if sizeof(p->nRow) == sizeof(long long) > sqlite3_snprintf(24, zRet, "%lld", p->nRow); > #elseif sizeof(p->Row) = sizeof(long) > sqlite3_snprintf(24, zRet, "%ld", p->nRow); > #else > sqlite3_snprintf(24, zRet, "%d", p->nRow); >

Re: [sqlite] Hints for the query planner

2013-09-10 Thread Eric Minbiole
> (1) unlikely(EXPR) > (2) selective(EXPR) > (3) seldom(EXPR) > (4) seldom_true(EXPR) > (5) usually_not_true(EXPR) > > I quite like (2) "selective". I think it's reasonably descriptive on its own, and also works well with the optional second argument.

Re: [sqlite] Different read/write behaviour when using System.Data.SQLite .NET Wrapper and using database encryption through a password leads to data inconsistency

2013-09-04 Thread Eric Minbiole
We ran into a similar issue while working on our own (semi-custom) SQLite encryption mechanisms. In my case, it was caused by the SQLite page cache reading the "File Change Counter" (Header page, offset 24) directly from the ciphertext *before* decrypting the page. (Presumably, this is done for

Re: [sqlite] Problems (maybe a bug) when creating a backup using the command line interface '.backup' command

2012-12-21 Thread Eric Minbiole
Can you please try running "pragma integrity_check;" on the original database? That will give an indication of whether the original database has any data corruption. On Fri, Dec 21, 2012 at 4:13 AM, Marco ten Thije <> wrote: > Hello, > > We are facing a problem

Re: [sqlite] Converting in-memory sqlite database to char array

2012-11-29 Thread Eric Minbiole
As a first (simple) approach, I might use the standard backup API to back up to a temp file, then stream that file byte by byte over the communication protocol. I'm sure there may be other more direct-to-memory approaches, perhaps using a custom VFS. However, this approach should be simple and

Re: [sqlite] Memory Usage/ Drawbacks of Statements

2012-04-24 Thread Eric Minbiole
Similar to Pavel's suggestion, our implementation maintains a simple cache of prepared statements, keyed by the SQL query that created them. For example: pStatement = Cache.GetQuery("SELECT * FROM xyz"); would return the cached statement if the query had been seen before, or would auto-create

Re: [sqlite] sqlite3_open_v2 performance degrades as number of opens increase

2011-09-02 Thread Eric Minbiole
My first thought would be to check the amount of memory being used by your many connections. Each connection will consume a non-trivial amount of resources (page cache, file handles, OS file cache, etc.) It's certainly plausible that your overall system performance is reduced as you run out of

Re: [sqlite] Full Table Scan after Analyze

2011-03-25 Thread Eric Minbiole
> Suggestion: After you run ANALYZE, go back and modify values in > sqlite_stat1 (and sqlite_stat2 if you are using SQLITE_ENABLE_STAT2) with > "typical" values that you would expect to see in most databases. Yes, you > can read and write the sqlite_stat1 and sqlite_stat2 tables. Maybe you can

[sqlite] Full Table Scan after Analyze

2011-03-25 Thread Eric Minbiole
We have an application that has been using SQLite successfully for several years. We use SQLite for various purposes within our product, with a few dozen tables overall. Recently, I started to upgrade from version 3.6.3 to 3.7.5. During that time, I noticed that several previously fast indexed

Re: [sqlite] Building sqlite

2009-06-29 Thread Eric Minbiole
> I'm currently on Windows and I've set up a python script to download the > sqlite3 amalgamation. However, the ZIP file contains no build system for > sqlite. I had to create a custom CMake script to build sqlite3 into a > library. I do not wish to compile the C file with my source, it needs to

Re: [sqlite] Memory leak with sqlite3_exec on qnx 6.4.1

2009-06-29 Thread Eric Minbiole
> So it seems by best bet is to close and open the connection once every 5 > minutes or so? While this might "work", I don't think you should resort to that. As Kees noted earlier, there will be performance drawbacks if you close/re-open the database. As others have indicated, the heap

Re: [sqlite] CURRENT_TIMESTAMP precision

2009-05-15 Thread Eric Minbiole
Please ignore my previous post. Doug's suggestion is much better. ~Eric Eric Minbiole wrote: >> I would like CURRENT_TIMESTAMP to be more accurate than just one second, >> any suggestions on how I might do that once? My solution is all a C/C++ >> interface, so all featu

Re: [sqlite] CURRENT_TIMESTAMP precision

2009-05-15 Thread Eric Minbiole
> I would like CURRENT_TIMESTAMP to be more accurate than just one second, > any suggestions on how I might do that once? My solution is all a C/C++ > interface, so all features are open to me. One option would be to create and register a custom SQL function that returned the current time,

Re: [sqlite] record order in select

2009-04-24 Thread Eric Minbiole
> If "order by" isn't used in a select statment, does the result records > ordered in rowid? If you omit an ORDER BY clause, the order of the resulting data is undefined. It might happen to be ordered by rowid in some cases, but this is not guaranteed. (Might change in a future

Re: [sqlite] Slim down join results (all fields returned)

2009-04-17 Thread Eric Minbiole
> Currently, I return any needed data like this. > > select * from pubs,notes,publishers where pub_title like '%salem%' > and pubs.note_id=notes.note_id > and pubs.publisher_id=publishers.publisher_id > > And it works except for all fields in the matching tables being returned. > > Is

Re: [sqlite] Attach the data return an error message

2009-04-08 Thread Eric Minbiole
> Hi all, > I had the application using sqlite and executing the following sql statement: > executeStmt: Error in executing the statment database TCPADDB is already in > use. Error St = 1 , stmt = ATTACH DATABASE > \'/opt/phoenix/monitor/TCPFlowCurDayDB\' as TCPADDB; insert into >

Re: [sqlite] UTF-16 API a second class citizen?

2009-04-07 Thread Eric Minbiole
> Dear Group: > > When my application launches I want to open the associated database, and if > that fails because the file does not exist then I would create a new > database. > > sqlite3_open_v2() is ideal for this purpose because you can leave out > SQLITE_OPEN_CREATE flag, and specify

Re: [sqlite] Is it using an index?

2009-04-06 Thread Eric Minbiole
> If I have a query: > > SELECT foo FROM bar WHERE id = 14; > > How can I see if that query is optimized to use an index, and which index > it's using. I thought if you did an EXPLAIN it would show that, but I'm not > seeing it? Maybe it's not really using an index? Use the command "EXPLAIN

Re: [sqlite] "-journal" file?

2009-04-02 Thread Eric Minbiole
> Whenever my phBB3 install is failing on the PPC Mac Mini, it appears > that SQLite is producing an empty database file (size 0) plus a file > with the same name plus "-journal" appended. What kind of error is > this? Does this mean SQLite somehow crashed out while attempting to > write

Re: [sqlite] Source code position out of sync debugging in VS 2008

2009-03-28 Thread Eric Minbiole
> I've already tried rebuilding everything, checked the settings, etc... but > nothing seems to help. I get the feeling this is a problem with the file > being so large and containing so many symbols. Has anyone else experienced > this problem? Indeed, the Visual Studio debugger can only

Re: [sqlite] need partial index

2009-02-11 Thread Eric Minbiole
> let's say i have a table MY_TABLE(INT_ID int not null, STRING_ID > varchar(255), DATA blob, primary key(INT_ID)). table has millions of > rows. i need to map from STRING_ID to INT_ID, but only for some rows. at > the time of insertion i know whether the new row needs to be in that map > or

Re: [sqlite] Saving and loading SQLite pages from a buffer

2009-02-04 Thread Eric Minbiole
> That depends. If the change was made using the same database > connection that was passed into sqlite3_backup_init(), then only those > pages that changed are recopied. However, if an independent database > connection made the change, then the backup process has no way of > knowing

Re: [sqlite] Saving and loading SQLite pages from a buffer

2009-02-04 Thread Eric Minbiole
> This is excellent! I've been looking for a clean way to perform live backups. This (draft) API looks perfect. I have one clarification question about source database changes made during the backup. The documentation states: "If the

Re: [sqlite] segmentation violation in fulltest on Mac OS X

2009-01-19 Thread Eric Minbiole
> Of course, I wasted 4 hours tracking the problem down. This is > yet another episode that demonstrates how threads are a pernicious > evil that should be studiously avoided in any program that you > actually want to work. Threads cause hard-to-trace bugs. Threads > result in

Re: [sqlite] Compressed dump SQLite3 database

2009-01-19 Thread Eric Minbiole
> We need to produce copies of our databases for archive. > It is a requirement that the size of those copies being as small as > possible, without having to perform an external compression. > vacuum doesn't seem to perform a compression (it works on fragmented > data), is there any other way to

Re: [sqlite] Journal files

2008-12-01 Thread Eric Minbiole
> I am looking for a way to completely turn off the creation > of journal files. Any help is much appreciated. ___ sqlite-users mailing list

Re: [sqlite] how to copy a big table in limited memory

2008-11-26 Thread Eric Minbiole
> I use sqlite3 on resource limited embedded devices, and it > provide only 250K memory for sqlite. > Now, I have a table named test_tab,whose size is 300K bytes. > If I want to copy this big table to another table, this operation will fail > because of limitde memory. The first thing I

Re: [sqlite] sqlite 3.6.5 slow on windows

2008-11-25 Thread Eric Minbiole
> Here is a typical outputs from the program running in debug mode: Debug builds can be substantially slower. I would expect at least some performance improvement with a release build (optimizations on). > Notice my constraint is these SQL statements must run one by one, not within > a

Re: [sqlite] Escaped values throws error

2008-11-16 Thread Eric Minbiole
> This is my first foree into PDO-SQLite and I saw samples using binding, > I am using prepare() though without binding. > > So you think that had I used binding my escape issue would not be an issue ? Correct. Though I've not used PDO-SQLite (I use the C interface), I would think that using

Re: [sqlite] Escaped values throws error

2008-11-16 Thread Eric Minbiole
> Now I remember why I wasn't using single quotes in SQLite statements - > > because escaped single quoted values like > > 'Billy\'s bike' > > cause it to fail. Again I think its related to PHP-PDO SQLite > implementation. Have you considered using prepared statements and bound

Re: [sqlite] sqlite3_bind_text problem

2008-11-14 Thread Eric Minbiole
> I'm compiling sqlite using Visual C++ 6 on windows atm and maybe there is a > problem that only shows up in VC6 and not in gcc. So with that in mind I'm > building the Mac port of my software (which is built using gcc) and try to > reproduce the problem there. I guess if everything is perfect

Re: [sqlite] sqlite3_bind_text problem

2008-11-14 Thread Eric Minbiole
> Then a bind the values to that statement and write it to the DB. That all > works except for the TEXT field "Name". I'm calling sqlite3_bind_text like > this: > > char *Name = "Something"; > sqlite3_bind_text(Stmt, 3, (const char*)Name, -1, SQLITE_STATIC); > > And it

Re: [sqlite] Floating point numbers

2008-10-06 Thread Eric Minbiole
> Does anyone knows why floating point numbers are truncated when they are > written or read from the database?! SQLite stores real numbers as 8 byte IEEE floats, which can hold approximately 16 significant digits. See: You could get slightly more

Re: [sqlite] macro redefinition

2008-10-01 Thread Eric Minbiole
> I tried the first option and i am getting the following error : > -- Build started: Project: Source Tagging System, Configuration: Debug > Win32 -- > > Compiling... > sqlite3.c > c:\Documents and Settings\Administrator\My Documents\Visual Studio > Projects\Source Tagging

Re: [sqlite] macro redefinition

2008-09-30 Thread Eric Minbiole
> Hi, > I am getting the following error log when i try to build sqlite source : > ... > c:\Documents and Settings\Administrator\My Documents\Visual Studio > Projects\Source Tagging System\sqlite\where.c(2902): fatal error C1010: > unexpected end of file while looking for precompiled header

Re: [sqlite] SQLite 3.6.1 memory leak?

2008-08-30 Thread Eric Minbiole
Ulric Auger wrote: > Hi, > > Since I updated to SQLite 3.6.1 I have a memory leak when my application > exits. > > If I compile using SQLite 3.5.8 I don't have the memory leak. Be sure to call sqlite3_shutdown() just before the application exits-- this should free any outstanding resources

Re: [sqlite] GCC -ffast-math safe version of sqlite3IsNaN

2008-08-27 Thread Eric Minbiole
Brown, Daniel wrote: > Thanks for the clarification Roger, I guess it looks like I will need to > modify the compiler settings locally then. If you can modify the amalgamation source code, I would try updating sqlite3IsNan() to use the standard C isnan() macro. DRH commented in one of the

Re: [sqlite] SQLite DB backups on Windows

2008-08-01 Thread Eric Minbiole
> What we're thinking of doing is pushing the PENDING_BYTE from the first > byte past the 1GB boundary to somewhere deep in the 64-bit range (such > as perhaps the 1TB boundary). We would have to update many lock and > unlock calls in os_win.c to do so, mainly adding a high-order 32-bit >

Re: [sqlite] Datetime issue with the time part

2008-07-14 Thread Eric Minbiole
> Once again, all of these problems doesn't happen before the creation of > the Direct3D device. Does anyone ever used SQLite successfully in a > full-screen 3D game ? From what you describe, it seems that the compiler is performing single-precision, rather than double-precision, math. After

Re: [sqlite] Datetime issue with the time part

2008-07-14 Thread Eric Minbiole
Sebastien Robillard wrote: > Hi everyone, > I have an issue with datetimes that doesn't return the "time" part > correctly (always 00:00:00 or 18:00:00) when I use SQLite in my C++ > code. Whenever I use datetime('now'), or current_timestamp, the time is > not correct. However, it works

Re: [sqlite] printf-8.2 test failure on Solaris on SPARC 64-bit

2008-06-20 Thread Eric Minbiole
> printf-8.2... > Expected: [2147483647 2147483648 4294967295] > Got: [2147483647 18446744071562067968 18446744073709551615] > > The code looks like: > > > ... > do_test printf-8.2 { > sqlite3_mprintf_int {%lu %lu %lu} 0x7fff 0x8000 0x > } {2147483647 2147483648

Re: [sqlite] prepare peformances

2008-06-09 Thread Eric Minbiole
> I would expect that sqlite3_prepare would be faster in such a case, and > maybe Toms is pointing out a circumstance where recreating the query > seems to be faster. Or am I misreading the post? One possible explanation (stab in the dark): If many of the bound parameters are text (or blob)

Re: [sqlite] CANTOPEN error on Windows systems running TortoiseSVN

2008-06-04 Thread Eric Minbiole
> - TSVNCache.exe monitors files and for whatever reason it opens them > and performs some queries on them. > - SQLite creates a journal file. > - TSVNCache notices the new file and opens it. > - SQLite deletes the journal file and it now enters the "Delete > Pending" state since TSVNCache still

Re: [sqlite] How to speed up my queries?

2008-06-03 Thread Eric Minbiole
> -- Even if you only go down to 1'-by-1' granularity, you've divided the > world into 64,800 blocks. Assuming that your 840K cities are all over the > globe, and that about 70% of Earth is covered by water, that means that only > about 20,000 blocks would actually have cities in them. But with

Re: [sqlite] Bind arguments for insert and not null columns with default values

2008-05-20 Thread Eric Minbiole
> I have a table like this > > CREATE TABLE foo (bar TEXT NOT NULL DEFAULT 'default_value'); > > and I'd like to create a reusable statement to do inserts into foo, like this: > > INSERT INTO foo (bar) VALUES (?); > > Sometimes I have values for bar and sometimes I don't and want the >

Re: [sqlite] 3.5.8 alot slower than 3.5.7

2008-04-25 Thread Eric Minbiole
> This works great but when I upgraded from 3.5.7 to 3.5.8 the speed of > this select went from 0.2s to around 1 minute. And 3.5.8 is stealing > ALOT more memory. D. Richard Hipp had a very helpful work-around for this issue, by simply rearranging the terms of your join's ON clause. Take a

Re: [sqlite] OUTER JOIN and PRIMARY KEY in 3.5.8

2008-04-17 Thread Eric Minbiole
> Your work-around until I fix this is to say > > owners.owner_id = pets.owner_id > > instead if what you have. In other words, put the > table on the left side of the join before the equals > sign instead of after it. Good idea: Swapping the terms of the JOIN expression does seem to

[sqlite] OUTER JOIN and PRIMARY KEY in 3.5.8

2008-04-17 Thread Eric Minbiole
I have been using SQLite for about a year now, and have been extremely pleased. While testing 3.5.8, I noticed that a SELECT with LEFT OUTER JOIN seemed to stop using an index, resorting to a (slow) full table scan. A simple (contrived) example follows: CREATE TABLE pets ( pet_id