Re: [sqlite] DLL WITH SAMPLE CODE FOR VISUAL BASIC 6 TO ACCESS AN SQLITE3 DATABASE

Sang Wong writes: > Do you have a .DLL LIBRARY and > SAMPLE CODE showing how to use VISUAL BASIC 6 with an sqlite3 > DATABASE? Thank You. Check this out - highly recommended: http://www.vbforums.com/showthread.php?726437-VB6-SQLite-DB-Demos-%28based-on-the-RichClient-Framework%29

[sqlite] Mozilla wiki 'avoid SQLite'

Am Sun, 14 Jun 2015 21:01:33 -0400 schrieb Stephen Chrzanowski: > Its like going back to Visual Basic and including the > interpreter in your "compiled" application with your source code basically > copy/pasted right into the final EXE output (Encrypted/obfuscated/whatever). Off topic indeed,

[sqlite] Optimization Opportunity?

Hi dev team, not sure if this is actually a useful hint, but ... CREATE TABLE a(a1 INTEGER PRIMARY KEY); INSERT INTO a VALUES (1),(2),(3); CREATE TABLE b(a1 INTEGER REFERENCES a(a1),b1 INTEGER PRIMARY KEY); INSERT INTO b VALUES (1,11),(2,22),(3,33); CREATE UNIQUE INDEX b_ui ON b(a1,b1); CREATE

[sqlite] Optimization Opportunity?

Am Sun, 8 Mar 2015 14:06:51 +0100 schrieb E.Pasma: > Actually query one appears slightly faster, > Searching the PK index is faster as that is always a COVERING index. I was under the impression that the opposite is true, but I wasn't sure about that. > From the secunsary indexes only a part

[sqlite] minor documentation flaw

In https://sqlite.org/lang_expr.html, the anchor appears twice. Obviously the scond occurence should be Wolfgang

Re: [sqlite] Is sqlite case-sensitive?

>>> names), or without context (just validate syntax, e.g. that it can be >>> parsed)? >>> >> I am asking about this API since I think I remember seeing it once, but >> can't find it now >> > > i _think_ what you want is: > > http://sqlite.org/c3ref/complete.html I don't think so, because this

Re: [sqlite] Is sqlite case-sensitive?

Am Sun, 18 May 2014 19:15:18 +0200 schrieb RSmith: > But of course What kind of syntactical correctness can you hope to > check without a connection? [...] Completely agreed. I was just referring to the OP who asked for an "API to validate a SQL statement, either in the context of the

Re: [sqlite] R-Tree Storage Optimization for Points

Mohit Sindhwani writes: > However, since these are points that are stored in the table, x1=x2 and > y1=y2 when we do the insertion. As a former embedded systems engineer, > this feels like a waste since I can see that we are inserting exactly > the same value into the table. > >

Re: [sqlite] R-Tree Storage Optimization for Points

Dan Kennedy writes: > Probably not. The CREATE TABLE code above actually creates a > 1-dimensional r-tree with deceptive column names. Column "y" contains > the maximum value for the first dimension: > > SQLite version 3.8.5 2014-06-19 12:34:33 > Enter ".help" for usage

[sqlite] SQLite, Java and Custom Collation

Hello, a friend of mine needs to read from an SQLite database that I made. He needs to do it in Java, and what's special about the database is that it contains some indexes which are based on a custom collation (German umlaut). Does anyone know of a recommendable java library (JDBC or else)

Re: [sqlite] Need to be able to rename attributes

Am Sun, 15 May 2011 17:10:53 -0500 schrieb Mr. Puneet Kishor: > sqlite doesn't support changing the name of a table column (and, neither you > nor > your user should be doing this -- there is something strange with your app >requirements). That said, you can "rename" a column by creating a new

Re: [sqlite] Sqlite help

Am Mon, 4 Jul 2011 14:45:07 -0700 (PDT) schrieb ceekayCK: > unfortunately send me a .doc file You're at a loss there. Your friend could as well print this file and send you the printer output, essentially that would make no difference. A .doc file is basically a load of printer instructions,

Re: [sqlite] Sqlite help

Am Mon, 4 Jul 2011 15:39:30 -0700 (PDT) schrieb ceekayCK: > If i changed .doc file to .txt or csv, will this help? > Or put all data from .doc to excel, and then split Artist and title to > separate columns and export to csv? *If* you can manage a reasonable export to .csv (using Excel, if

Re: [sqlite] Sqlite help

Am Mon, 4 Jul 2011 16:10:10 -0700 (PDT) schrieb ceekayCK: > It was quite easy to export to csv (using excel) :) > Now i have in one column Artist , and in second column Title Fine. :-) > Ok So i use sqlite Manager in Firefox Unfortunately, I'm not too familiar with SQLite Manager. > What

[sqlite] storing coordinate pairs efficiently?

Dear group, since a couple of years now, I'm using SQLite for storing graphic objects like polygons and lines, with great success. Using the rtree extension makes spatial queries very efficient. In my lastest project, however, these objects are just single points (coordinate pairs). As I

[sqlite] storing coordinate pairs efficiently?

Simon, Gunter, thanks for your input. I also noticed that an index on (gx,gy) would help on covering index searches, however as you both noticed as well there are more fields in this table. But wait - probably for the first step (which points are located in the region in question anyway?) it

[sqlite] storing coordinate pairs efficiently?

OK, here are a few test results ... 350,000 points with no additional data ... timings with all data in cache and 17 records selected: #1 CREATE INDEX "pdata_spatial_index_x" ON "pdata"("gx"); CREATE INDEX "pdata_spatial_index_y" ON "pdata"("gy"); file size 24,200 KB, query time ~6.3 ms #2

[sqlite] minor documentation typo

minor documentation typo here: https://www.sqlite.org/rbu.html >All RBU updates us the "OR ROLLBACK" constraint handling mechanism.< should obviously be >All RBU updates use the "OR ROLLBACK" constraint handling mechanism.< Wolfgang

[sqlite] minor documentation typo

minor documentation typo here: https://www.sqlite.org/partialindex.html 3.0 Queries Using Partial Indexes [...] Example: Let the index by should be Example: Let the index be Greetings, Wolfgang

[sqlite] Version 3.13.0 coming soon

Am Tue, 3 May 2016 13:21:04 -0400 schrieb Richard Hipp: > On 5/3/16, nomad at null.net wrote: >> On Tue May 03, 2016 at 08:33:30AM -0400, Richard Hipp wrote: >>> >>> Yes. Apparently that is the new standard for security on unix >> >> The way I understood Rolf's comment was that he was pointing

Re: [sqlite] Is the absence of msvcrt.dll a known issue with SQLite Windows 2000

Am Sat, 5 Jun 2010 08:01:31 +0100 schrieb Frank Church: > On checking the sqlite3.dll docs it states the only additional requirement > for it is msvcrt.dll. Is that known to be a problem, or could it be > something else? I just want to know if there are some other known issues > between

Re: [sqlite] errors after sqlite3_step

Am Fri, 01 Oct 2010 14:11:51 -0700 schrieb Dave Dyer: > There ought to be a definitive list of what errors can occur > after sqlite3_step, and which (if any) are recoverable. IMHO that's impossible, at least because SQLite allows the definition of user defined functions. Now such a function may

Re: [sqlite] SQLITE : Constraint question

xplains in more detail what operations the CHECK constraint is capable of? Best, Wolfgang Enzinger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE : Constraint question

) requesting a documentation improvement if there are no objections. Regarding the CHECK statement, however, I think that there is a gap that should be filled by someone with more insight than I have ... BTW: My previous post was my first post to this list, and

Re: [sqlite] Sqlite reading all column data on selects.

Am Thu, 24 Sep 2009 14:18:33 -0400 schrieb D. Richard Hipp: > When autovacuum=FULL or autovacuum=INCREMENTAL and SQLite needs to > seek to the end of a long chain of overflow pages, it attempts to use > the "pointer map pages" that are available in these modes to locate > the pages without

Re: [sqlite] PRAGMA journal_mode

Am Sun, 11 Oct 2009 10:45:57 -0500 schrieb Jay A. Kreibich: > The docs for "PRAGMA journal_mode" are pretty clear on this: it > returns the current mode (i.e. after the command is run) because it may > not be able to change to the requested mode. Thanks. That made me realize that my local

[sqlite] Cannot ATTACH a database that contains a VIEW

Hi, it looks like I cannot ATTACH a database that contains a VIEW: sqlite> ATTACH DATABASE 'G:\Project\ASK_ORA\ask.db' AS dbsrc; SQL error: malformed database schema (ask_art) - view ask_art cannot reference objects in database main sqlite> Obviously SQLite tries to apply the view to tables in

Re: [sqlite] Cannot ATTACH a database that contains a VIEW

Pavel Ivanov writes: > Are you referencing "main" in your view explicitly? Bingo, that's in fact what I did. > If so then don't do it. Followed your advice and it works now, thanks! Looks like I was a bit *too* explicit here ... ;-) Wolfgang

Re: [sqlite] ICU collation

Am Sun, 20 Dec 2009 14:45:15 +0100 schrieb Sylvain Pointeau: > if you go on http://www.dict.cc and type "schoen" then it will give you >the answers "schön" ... I'm pretty sure they don't query their database for " = 'schön'" but for "LIKE 'schön'". And that's exactly what it is: like, but not

Re: [sqlite] ICU collation

Am Sun, 20 Dec 2009 19:24:58 +0100 schrieb Wolfgang Enzinger: > I'm pretty sure they don't query their database for " = 'schön'" but for > "LIKE 'schön'". Correction: I'm pretty sure they don't query their database for " = '

Re: [sqlite] Import a other database. Search for the fastes way

Am Sat, 9 Jan 2010 22:00:04 +0100 schrieb Carsten Giesen: [Transactions] > Thanks. That's rock > Greate. Next, play with some PRAGMA settings to achieve even more speed, especially: PRAGMA cache_size = Number-of-pages; PRAGMA page_size = bytes; Also (it's not clear whether you do this

[sqlite] result set not properly ordered in SQLite 3.7.15.1

Hi, I have this query: SELECT DISTINCT ask_fuo.id FROM ask_fuo INNER JOIN ask_art USING (id) INNER JOIN ask_art_personen USING (ora_nachweis_id) WHERE (ask_art_personen.code IN('M0E9','M0GC','M0YR')) AND ask_art_personen.bezug IN('B','D','S') GROUP BY ask_fuo.id ORDER BY ask_fuo.id; A sample

Re: [sqlite] New preview snapshots for 3.7.17

Richard Hipp writes: > > New amalgamation snapshots for the upcoming 3.7.17 release can be found at > > http://www.sqlite.org/draft/download.html > > Please try this snapshot in your applications and report success or > failure, either to this mailing list or directly to me.

[sqlite] dramatic performance drop somewhere between 3.7.09.0 and 3.7.17.0

Hallo list, I detected a dramatic performance drop with a certain query which occurred somewhere between versions 3.7.09.0 and 3.7.17.0. Now this is not a real showstopper because I also noticed than an ANALYZE cures the issue in the latter version immediately. However, the drop without ANALYZE

Re: [sqlite] dramatic performance drop somewhere between 3.7.09.0 and 3.7.17.0

Richard Hipp writes: > However, once we get the new query planner running and ready to beta-test, > we really, really would appreciate you giving it a spin and letting us know > if you encounter any problems with it. Alright, will do. Thanks for the great work! Wolfgang

Re: [sqlite] The next-generation query planner

Richard Hipp writes: > Amalgamations for the latest SQLite containing the NGQP are available from > the http://www.sqlite.org/draft/download.html page. This should be a > drop-in replacement for the amalgamation you are currently using. There > are no new APIs or compiler flags to

Re: [sqlite] The next-generation query planner

Richard Hipp writes: > > I try to compile with SQLITE_HAS_CODEC defined I get errors saying that > > sqlite3_key_v2 and sqlite3_rekey_v2 functions are undefined. Are these new > > APIs? > > > > Yes. You need to use your login and password to download the latest SEE > source - the

Re: [sqlite] The next-generation query planner

Richard Hipp writes: > The combination of schema, sqlite_stat1, and query is normally sufficient, > yes. > > Can you change (and shorten) the table and index and column names to > obscure their meaning and origin, and give us written permission to include > your case in the published

Re: [sqlite] how to use sqlite in excel vba?

Larry Brasfield writes: > Assuming you actually do need to compile something, (identity of which > you provide few good clues), you might consider a package I had good > luck with, a SQLite wrapper called 'Litex', available at >https://www.assembla.com/wiki/show/litex

Re: [sqlite] ATTACH DATABASE question

Am Thu, 27 Sep 2012 20:31:36 -0700 schrieb john_prov...@yahoo.com: > I’m confused about how to attach a database file to an existing > database. I open my main database ok, then to attach the second > database, I try to execute the following SQL: > > ATTACH DATABASE

[sqlite] minor formattig flaw in documentation

I just came across a minor formattig flaw in the VDBE documentation (https://www.sqlite.org/opcode.html); in the opcodes table, the description for the Cast opcode reads like this: --- Force the value in register P1 to be the type defined by P2. TEXT BLOB NUMERIC INTEGER REAL A

Re: [sqlite] Retrieve INTEGER PRIMARY KEY

Am Sat, 4 Feb 2017 09:04:58 -0600 schrieb Clyde Eisenbeis: > When I enter last_insert_rowid(), the compiler complains. I think > last_insert_rowid() is SQLite3. > > Is there an equivalent for System.Data.SQLite? I don't know, but you can use the SQL function of the same name:

[sqlite] web interface down

Hi group, I hope this is the right place ... The web interface for this mailing list (http://news.gmane.org/gmane.comp.db.sqlite.general) seems to be down for quite a while now, I'm getting timeouts constantly. The NNTP interface, however, works fine. Anyone can do anything about it? Cheers,

Re: [sqlite] web interface down

Am Tue, 30 Aug 2016 14:44:03 -0700 schrieb Darren Duncan: > On 2016-08-30 2:22 PM, Wolfgang Enzinger wrote: >> I hope this is the right place ... >> >> The web interface for this mailing list >> (http://news.gmane.org/gmane.comp.db.sqlite.general) seems to be down fo

[sqlite] Optimization opportunity

Hello, given the following: CREATE TABLE x( pk INTEGER PRIMARY KEY, description TEXT ); CREATE TABLE y( fk INTEGER REFERENCES x(pk), flags INTEGER ); CREATE INDEX yy ON y(fk); CREATE VIEW z AS SELECT fk, (flags&1) AS odd, (flags&2)>>1 AS even,

Re: [sqlite] Optimization opportunity

Am Fri, 14 Apr 2017 10:59:25 -0400 schrieb Richard Hipp: > Performing this rewrite of a view into a simple LEFT JOIN is trickier > than it seems at first glance. The rewrite works for the example you > provide. But subtle changes to the view can make the rewrite invalid. > For example: > >

Re: [sqlite] Optimization opportunity

Am Fri, 14 Apr 2017 15:14:12 -0400 schrieb Richard Hipp: > But I've spent Good Friday working around it. A thousand thanks! :-) > Please try using the tip of the left-join-view branch > (https://www.sqlite.org/src/timeline?c=left-join-view) and let me know > if that version works better for

Re: [sqlite] Lightweight solution for Unicode-savvy collation?

Am Thu, 27 Jul 2017 16:47:00 -0700 schrieb Jens Alfke: > The project I work on needs the ability to do Unicode-savvy string > collation, which SQLite doesn’t provide. But we’re somewhat sensitive to > code size, so we don’t want to just drop in the hugeness that is ICU. > We’ve looked at a couple

Re: [sqlite] Mailinglist question

Am Wed, 9 Aug 2017 17:02:42 -0500 schrieb Nico Williams: > I... would like to use NNTP, but I'd need someone to provide the > service. I'm connected to this mailing list as if it was a newsgroup using nntp://news.gmane.org:563/gmane.comp.db.sqlite.general. No authentication required. Of course,

Re: [sqlite] Mailinglist question

Am Thu, 10 Aug 2017 01:37:14 +0200 schrieb Wolfgang Enzinger: > I'm connected to this mailing list as if it was a newsgroup using > nntp://news.gmane.org:563/gmane.comp.db.sqlite.general. Bummer ... just a few hours after I posted this, that service seems to have stopped pulling any new

Re: [sqlite] SQLite DB on external USB HD - is it safe?

OK, thanks for the detailed analysis! :-) Am Tue, 09 May 2017 16:06:28 -0600 schrieb Keith Medcalf: [...] > If Windows reports that the filesystem is "local" then it is OK. If > Windows reports that the filesystem is "remote", then it is ungood. A > "drive mapped to the local computer" is a

[sqlite] SQLite DB on external USB HD - is it safe?

Dear group members, since it's wise to store SQLite databases on local HDs (as opposed to network filesystems) in order to avoid corruption, I would like to have my program check if this requirement is fulfilled. I'm on Windows so I use GetDriveType() for testing the DB path. Now I noticed that

Re: [sqlite] Thank you for your work

Am Fri, 19 May 2017 13:36:57 -0400 schrieb Richard Hipp: > On 5/19/17, Claudio Bantaloukas wrote: >> >> Lo and behold, the latest version handled these queries without issue, >> halved the time it took to run some other queries and has apparently not >> introduced

[sqlite] Minor issue: compile error with 3.19 and MSVC 2005

Hallo, I don't think this is a big deal, but probably helpful for one or another: I tried to compile SQLite 3.19 with MSVC 2005 and got a compile error C2143 at line 143542 of sqlite3.c. Removing the trailing semicolon from if( (zKey = sqlite3_uri_parameter(zOpen, "hexkey"))!=0 && zKey[0] ){;

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

Am Sat, 27 May 2017 19:20:00 -0400 schrieb Richard Hipp: > On 5/27/17, Thomas Flemming wrote: >> Hi, >> >> I have a table Pois with points of interest (geogr. coordinate, label, >> styleid) where I do regional querys using a rtree-index: >> >> SELECT Pois.* FROM Pois_bb, Pois

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

Am Mon, 29 May 2017 14:27:56 +0100 schrieb Thomas Flemming: > Ok, here is a sample to try these queries: > > http://files.qvgps.com/0-tom-public/Geonames_World_2017.zip > (825mb, 12 mio records) Just a few quick observations ... First, I would replace all column declarations like LONG PRIMARY

Re: [sqlite] Foreign keys - one to one, one to many relationship

Am Sat, 14 Oct 2017 13:01:59 -0400 schrieb Richard Damon: > you could create a record in the other table without a corresponding > record in the blob table, but that is sort of needed to avoid chicken > and egg issues with creating new records (not sure when foreign key > requirements are

Re: [sqlite] Should the INTEGER not be cast to a REAL

Am Thu, 7 Sep 2017 00:28:56 +0200 schrieb Cecil Westerhof: > 2017-09-07 0:20 GMT+02:00 Wolfgang Enzinger <sql...@enzinger.net>: >> Add this trigger and everything is fine. ;-) >> >> CREATE TRIGGER weights_float_force_datatype >> BEFORE INSERT ON weights &

Re: [sqlite] Should the INTEGER not be cast to a REAL

Am Thu, 7 Sep 2017 00:15:39 +0200 schrieb Cecil Westerhof: > 2017-09-07 0:05 GMT+02:00 R Smith : > >> On 2017/09/06 11:58 PM, R Smith wrote: >> >>> Your CHECK constraint should really find that the value is acceptable >>> when it is either a REAL, OR an INT, because both

Re: [sqlite] Knowing when not to use a R-Tree index, and clustering tables along spatial indexes

Am Fri, 29 Dec 2017 19:59:12 +0100 schrieb Andrea Aime: > With SQLite R-Tree I'm using either a join with the index virtual table, or > a subquery > retrieving the ids from the rtree. Regardless, the query is basically > ordering SQLite > to use the index. > So I was wondering, is there any

Re: [sqlite] EXTERNAL: SQLite 3.22.0 coming soon

Am Wed, 10 Jan 2018 02:25:35 + schrieb Edwards, Mark C.: > Release mode/x86 Visual Studio 2015 Prono problems with the new snapshot Same here with my ancient Visual Studio 2005. :-) Cheers Wolfgang ___ sqlite-users mailing list

Re: [sqlite] Knowing when not to use a R-Tree index, and clustering tables along spatial indexes

Am Mon, 1 Jan 2018 16:20:21 +0100 schrieb Clemens Ladisch: > It is indeed possible to change the query so that SQLite uses rowid > lookups for the R-tree filter (INDEX 1). However, any likelihood on the > R-tree search expression still did not make any difference. Do you have > an example?

Re: [sqlite] Knowing when not to use a R-Tree index, and clustering tables along spatial indexes

Am Mon, 1 Jan 2018 10:45:50 +0100 schrieb Clemens Ladisch: > Wolfgang Enzinger wrote: >> First, query the overall extent of your data, like this: >> SELECT MIN(mingx),MAX(maxgx),MIN(mingy),MAX(maxgy) FROM flst_shape_index; > > This results in a full table scan. Instead of

Re: [sqlite] Knowing when not to use a R-Tree index, and clustering tables along spatial indexes

Am Mon, 1 Jan 2018 20:30:10 +0100 schrieb Clemens Ladisch: > Wolfgang Enzinger wrote: >> Am Mon, 1 Jan 2018 16:20:21 +0100 schrieb Clemens Ladisch: >>> It is indeed possible to change the query so that SQLite uses rowid >>> lookups for the R-tree filter (INDEX 1

[sqlite] minor documentation flaw

In https://sqlite.org/lang_expr.html, the anchor appears twice. Obviously the second occurence should be Wolfgang ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Typo

Am Tue, 25 Dec 2018 08:59:45 +0100 schrieb gwenn: > There is a typo here: > https://sqlite.org/session/sqlite3changeset_op.html >> If pbIncorrect is not NULL, then *pbIndirect is set to true > Should be pbIndirect instead of pbIncorrect. Another one in https://www.sqlite.org/security.html: "...

Re: [sqlite] Last record

Am Tue, 15 Oct 2019 13:36:37 -0800 schrieb Adam Levy: > Although what Doug suggested could work, it doesn't make sense to me to add > an extra count or max query just to know how many results will be returned > when those results will be queried anyway. One typical use case I can think of is

Re: [sqlite] How to group this?

Am Mon, 10 Feb 2020 01:42:14 + schrieb Simon Slavin: > On 10 Feb 2020, at 1:25am, no...@null.net wrote: > > create two VIEWs, [...]. Index both VIEWs on (id, date), I don't think that creating an index on a view actually works, does it? Wolfgang

[sqlite] gmane.comp.db.sqlite.general

Hi, for those of us who - like me - prefer to access this mailing list via NNTP: the Newsserver's address has changed from news.gmane.org to news.gmane.io (sorry if this is old news; I couldn't find any information about that here.) Wolfgang ___