[sqlite] CEROD in System.Data.SQLite
I'm working on switching our custom build of System.Data.SQLite from our custom build and am having problems opening a CEROD database. Going back over our code, I see that I wrote in Open(): // TU: Need to split out "cerod" prepend to filename before expandfilename string pattern = "(:.*:)(.*)"; Match m = Regex.Match(fileName, pattern); if (m.Groups.Count == 3) { string prefix = m.Groups[1].ToString(); string cleanFileName = m.Groups[2].ToString(); cleanFileName = ExpandFileName(cleanFileName); fileName = prefix + cleanFileName; } else { fileName = ExpandFileName(fileName); } Maybe something similar is needed? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] recommended way to attach SQLite.Interop.dll
The new NuGet packages use MSBuild integration via a custom targets file to copy the interop DLLs to the "right" place at build-time. Sent from my iPod > On Feb 11, 2015, at 5:38 PM, E. Timothy Uy wrote: > > Hi, when I add the nuget package for System.Data.SQLite, I don't see the > interop.dll. However, it is in the nuget packages folder. Am I supposed to > manually link to this? Concerned about missing things if we update the > nuget. Ideally it should show up automagically. (maybe I missed something) > ___ > 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] recommended way to attach SQLite.Interop.dll
Hi, when I add the nuget package for System.Data.SQLite, I don't see the interop.dll. However, it is in the nuget packages folder. Am I supposed to manually link to this? Concerned about missing things if we update the nuget. Ideally it should show up automagically. (maybe I missed something) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] visx for regular old windows
Ok, I don't see it. But that is good to know (about MSBuild) On Wed, Feb 11, 2015 at 4:22 PM, Joe Mistachkin wrote: > I've been told that MSBuild will use it but that VS will not show/"see" > it. However, after learning this I didn't verify. Things may improve in > VS 2015. Perhaps we can find out from the CTP release? > > Sent from my iPod > > > On Feb 11, 2015, at 4:13 PM, E. Timothy Uy wrote: > > > > Does the visx for regular old windows actually work? After installing it > I > > don't see anything in VS2013. > > ___ > > 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] binding multiple values in a query
On 2/11/15, Igor Tandetnik wrote: > On 2/11/2015 5:46 PM, Jono Poff wrote: >> I wonder if anybody could give me a simple example in C to bind an array >> of values to a prepared statement? >>> > These approaches use stock capabilities of SQLite and don't require > heroic efforts (like patching source or implementing a virtual table). > That virtual table you mentioned is implemented at https://www.sqlite.org/src/artifact/9dc57417fb65bc78 (for integer values, at least). -- 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] binding multiple values in a query
On 2/11/2015 5:46 PM, Jono Poff wrote: I wonder if anybody could give me a simple example in C to bind an array of values to a prepared statement? The effect I'm looking for is sqlite3_stmt* stmt = Compile(db, "select * in Things where thing1 in ( );"); sqlite3_bind_???(stmt,0 ,); With small number of values, you can generate a string like "?, ?, ?" with one question mark per parameter, embed it into the statement between parens, and bind each element individually. Or, just sprintf them all into a string and embed into the query as literals (especially if they are numbers; with strings, beware SQL injection). With large number of values, create a temp table, insert them into that table one per row (using an auxiliary single-parameter INSERT statement), then reformulate your query to do a nested select on that table. These approaches use stock capabilities of SQLite and don't require heroic efforts (like patching source or implementing a virtual table). -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] visx for regular old windows
I've been told that MSBuild will use it but that VS will not show/"see" it. However, after learning this I didn't verify. Things may improve in VS 2015. Perhaps we can find out from the CTP release? Sent from my iPod > On Feb 11, 2015, at 4:13 PM, E. Timothy Uy wrote: > > Does the visx for regular old windows actually work? After installing it I > don't see anything in VS2013. > ___ > 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] visx for regular old windows
Does the visx for regular old windows actually work? After installing it I don't see anything in VS2013. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Library routine called out of sequence
On 11 Feb 2015, at 11:19pm, pablo Van wrote: > I have a problem with my delphi's application when I want run it on other > PC. I use ZeosLib in my application > The message is : Library routine called out of sequence What's happening is that SQLite relies on some calls being executed in a sequence, for instance _prepare() _step() _step() _step() ... _finalize() and the application is trying to do something like _prepare() _step() _finalize() _step() _step() ... If you wrote the Delphi application you might be able to fix it. If you didn't write it, and it works fine on one PC and not on the other /when they have the same data/, then you're going to have to contact the programmer to get it fixed. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Library routine called out of sequence
I have a problem with my delphi's application when I want run it on other PC. I use ZeosLib in my application The message is : Library routine called out of sequence How I can fix it? I need help urgent!!! please Thanks at all ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] binding multiple values in a query
Gr really? Thanks for the quick response tho! I'm already building it from source so I'll investigate! Cheers, Jono On 12/02/2015 11:53 a.m., Peter Aronson wrote: You can't do that in standard SQLite -- you can only bind single values. However, if you download the full source package (as opposed to the amalgamation) of SQLite, there are a pair of files under src, test_intarray.c and test_intarray.h, which implement a virtual table that would let you do that, as long as the array you wanted to bind was made up of integers. Although it wouldn't be hard to make a version that worked on floats or strings. Peter On Wednesday, February 11, 2015 3:46 PM, Jono Poff wrote: Hi, I wonder if anybody could give me a simple example in C to bind an array of values to a prepared statement? The effect I'm looking for is sqlite3_stmt* stmt = Compile(db, "select * in Things where thing1 in ( );"); sqlite3_bind_???(stmt,0 ,); Cheers, Jono -- -- This email, including any attachments, is only for the intended recipient. It is subject to copyright, is confidential and may be the subject of legal or other privilege, none of which is waived or lost by reason of this transmission. If you are not an intended recipient, you may not use, disseminate, distribute or reproduce such email, any attachments, or any part thereof. If you have received a message in error, please notify the sender immediately and erase all copies of the message and any attachments. Unfortunately, we cannot warrant that the email has not been altered or corrupted during transmission nor can we guarantee that any email or any attachments are free from computer viruses or other conditions which may damage or interfere with recipient data, hardware or software. The recipient relies upon its own procedures and assumes all risk of use and of opening any attachments. -- ___ 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 -- -- This email, including any attachments, is only for the intended recipient. It is subject to copyright, is confidential and may be the subject of legal or other privilege, none of which is waived or lost by reason of this transmission. If you are not an intended recipient, you may not use, disseminate, distribute or reproduce such email, any attachments, or any part thereof. If you have received a message in error, please notify the sender immediately and erase all copies of the message and any attachments. Unfortunately, we cannot warrant that the email has not been altered or corrupted during transmission nor can we guarantee that any email or any attachments are free from computer viruses or other conditions which may damage or interfere with recipient data, hardware or software. The recipient relies upon its own procedures and assumes all risk of use and of opening any attachments. -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] binding multiple values in a query
You can't do that in standard SQLite -- you can only bind single values. However, if you download the full source package (as opposed to the amalgamation) of SQLite, there are a pair of files under src, test_intarray.c and test_intarray.h, which implement a virtual table that would let you do that, as long as the array you wanted to bind was made up of integers. Although it wouldn't be hard to make a version that worked on floats or strings. Peter On Wednesday, February 11, 2015 3:46 PM, Jono Poff wrote: > > >Hi, > >I wonder if anybody could give me a simple example in C to bind an array >of values to a prepared statement? > >The effect I'm looking for is > >sqlite3_stmt* stmt = Compile(db, "select * in Things where thing1 in >( );"); > >sqlite3_bind_???(stmt,0 ,); > > >Cheers, >Jono > > > > >-- > >-- >This email, including any attachments, is only for the intended recipient. >It is subject to copyright, is confidential and may be the subject of legal >or other privilege, none of which is waived or lost by reason of this >transmission. >If you are not an intended recipient, you may not use, disseminate, >distribute or reproduce such email, any attachments, or any part thereof. >If you have received a message in error, please notify the sender >immediately and erase all copies of the message and any attachments. >Unfortunately, we cannot warrant that the email has not been altered or >corrupted during transmission nor can we guarantee that any email or any >attachments are free from computer viruses or other conditions which may >damage or interfere with recipient data, hardware or software. The >recipient relies upon its own procedures and assumes all risk of use and of >opening any attachments. >-- >___ >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] binding multiple values in a query
Hi, I wonder if anybody could give me a simple example in C to bind an array of values to a prepared statement? The effect I'm looking for is sqlite3_stmt* stmt = Compile(db, "select * in Things where thing1 in ( );"); sqlite3_bind_???(stmt,0 ,); Cheers, Jono -- -- This email, including any attachments, is only for the intended recipient. It is subject to copyright, is confidential and may be the subject of legal or other privilege, none of which is waived or lost by reason of this transmission. If you are not an intended recipient, you may not use, disseminate, distribute or reproduce such email, any attachments, or any part thereof. If you have received a message in error, please notify the sender immediately and erase all copies of the message and any attachments. Unfortunately, we cannot warrant that the email has not been altered or corrupted during transmission nor can we guarantee that any email or any attachments are free from computer viruses or other conditions which may damage or interfere with recipient data, hardware or software. The recipient relies upon its own procedures and assumes all risk of use and of opening any attachments. -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FTS5
Hi all, I found a previous FTS5 thread and, encouraged by the comments of Dan Kennedy, thought I would comment on the issue. - Smaller memory footprint and more speed is always great. I'm already very impressed with the speed but even faster is even better of course. My experience is that searches that produce few hits are very fast (well under a second on a db with 10M+ records). Searches that produce many hits (tens of thousands) are much slower: several seconds, or even minutes if there are 100,000+ hits. I can live with that, but improvement on queries with many hits would be welcome. - I would probably also make tokenize=unicode61 "remove_diacritics=0" the default tokenization behaviour instead of simple, but that's a minor issue. - In my usage, the most inconvenient limitation is that the first search term can't be negative in queries (i.e. MATCH foo NOT bar is good but MATCH NOT bar foo throws an error). I would also like to have negative-only queries (MATCH NOT f oo, returning all records that don't contain foo). Negative-only queries would mostly be used in combination (INTERSECT) with a positive query on another column. I know this is probably not a common need, but one can dream. - Fuzzy matching would be useful as well, but obviously that's a major feature and introducing it might well compromise performance. - Same for in-word matching (i.e. MATCH reasonable also matching "unreasonable") - Same for advanced matching like matching 3 out of 4 search terms if there is no match with 4 out of 4, or ranking hits based on how close to each other terms occur. - For some reason, searches like SELECT * FROM ftstable WHERE col1 MATCH ? INTERSECT SELECT * FROM ftstable WHERE col2 MATCH ? run very slowly for me. Much slower than running the two queries separately. This may not be related to FTS per se, and maybe the query could be written better. - BTW, will there be full backwards compatibility? And I assume one will need to recreate (export/reimport) existing databases with FTS5 in order to enjoy the new features, right? AF Context: "Fts5 will use less memory and be faster than fts4 (I think - initial testing has been positive). It will also be smaller, as we can do without a bunch of code that is used to workaround problems inherent in the file-format. ... The most user-visible change is the addition of an API that allows users to write their own auxiliary (i.e. snippet(), rank(), offsets()) functions: http://www.sqlite.org/src/artifact/064f9bf705e59d The included snippet() and rank() functions use this API. ... Fts5 is still in the experimental stage at the moment. If anybody has any ideas for useful features, or knows of problems with FTS4 that could be fixed in FTS5, don't keep them to yourself! - Dan Kennedy" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [ANN] ODB C++ ORM 2.4.0 Released, Adds Object Loading Views
I am pleased to announce the release of ODB 2.4.0. ODB is an open source object-relational mapping (ORM) system for C++. It allows you to persist C++ objects to a relational database without having to deal with tables, columns, or SQL and without manually writing any of the mapping code. Major new features in this release: * Support for bulk operations in Oracle and SQL Server. Bulk operations can be used to persist, update, or erase a range of objects using a single database statement execution which often translates to a significantly better performance. * Ability to join and load one or more complete objects instead of, or in addition to, a subset of their data members with a single SELECT statement execution (object loading views). * Support for specifying object and table join types in views (LEFT, RIGHT, FULL, INNER, or CROSS). * Support for calling MySQL and SQL Server stored procedures. * Support for defining persistent objects as instantiations of C++ class templates. A more detailed discussion of these features can be found in the following blog post: http://www.codesynthesis.com/~boris/blog/2015/02/11/odb-2-4-0-released/ For the complete list of new features in this version see the official release announcement: http://codesynthesis.com/pipermail/odb-announcements/2015/41.html ODB is written in portable C++ (both C++98/03 and C++11 are supported) and you should be able to use it with any modern C++ compiler. In particular, we have tested this release on GNU/Linux (x86/x86-64/ARM), Windows (x86/x86-64), Mac OS X (x86/x86_64), and Solaris (x86/x86-64/SPARC) with GNU g++ 4.2.x-5.x, MS Visual C++ 2005, 2008, 2010, 2012, and 2013, Sun Studio 12u2, and Clang 3.x. The currently supported database systems are MySQL, SQLite, PostgreSQL, Oracle, and SQL Server. ODB also provides optional profiles for Boost and Qt, which allow you to seamlessly use value types, containers, and smart pointers from these libraries in your persistent classes. More information, documentation, source code, and pre-compiled binaries are available from: http://www.codesynthesis.com/products/odb/ Enjoy, Boris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite Virtual Table Example with non-trivial xBestIndex (was: Porting SQLite to another operating system (not supported out of the box))
Having personally written about a dozen virtual table implementations I can confirm that those implementations needing a nontrivial xBestIndex function are all based on building an SQLite interface on substantial proprietary storage subsystems like an in-memory ISAM table (with configurable record and key structure), a Faircom CTree driver (adding configurable record and key structure), a partition provider (allowing storage to be split between several tables of identical structure by configurable record fields), etc. One of the more challenging tasks involved adding a fastbit based index to a variable record length event logfile for an OLTP application. "Simple" virtual tables require retrieval by "rowid" (e.g. memory address or file offset) at best. -Ursprüngliche Nachricht- Von: Jay Kreibich [mailto:j...@kreibi.ch] Gesendet: Mittwoch, 11. Februar 2015 04:04 An: Peter Aronson; General Discussion of SQLite Database Betreff: Re: [sqlite] Porting SQLite to another operating system (not supported out of the box) On Feb 10, 2015, at 11:21 AM, Peter Aronson wrote: > You could add VFS creation if you ever do a revised edition (along with a > virtual table example that actually used xBestIndex and xFilter…) Given that the book is over four years old and covers to the end of SQLIte3 3.6.x, there are a lot of things that would need to go into a revised edition… including a lot more examples of everything, according to reviews. We greatly underestimated the number of SQLite developers that were touching SQL for the first time, and I would have never guessed people would have considered yet another SQL lesson to be so important, given that there are a million books and a bazillion websites on learning SQL basics. You can literally find books on “SQL For Dummies” (Allen Taylor) to “SQL For Smarties” (Joe Celko), and everything in-between. That last book (or books, actually) is awesome, BTW, and the “Advanced SQL Programming” one should be on the shelf of every database programmer doing anything more advanced than an address book. Regardless, if we do a second edition (and at this point that’s an extremely, exceptionally big “if”), VFS is unlikely to make the cut. Consider that out of the thousands of SQLite applications and billions of installed databases, there are likely less than 100 production VFS modules in the whole world. Spending a lot of time and pages, driving up the cost of the book, covering an extremely advanced and obscure topic is a poor trade-off (every page averages about a day to write/edit/prep, and adds about $0.10 to the price of the book). If you need that level of integration and detail, working in the guts of the I/O and locking system, you should likely hand the task to a systems engineer that is familiar with the problem domain and isn’t afraid of looking through a few headers and examples to figure it all out. It’s advanced, custom stuff that is, almost by definition, not textbook work. It is the kind of work that requires digging through nitty-gritty code, documentation, and examples from both SQLite and your environment. This is the kind of thing that’s learned from years of experience, not by reading it in a book. That isn’t meant to be a criticism of the original poster— there is a huge difference between asking if anyone knows where to start looking, and asking for detailed step-by-step instructions. In fact, if we did decide to put some information about VFS modules in a book, it would likely be a discussion of how the structures and APIs fit together, what they’re used for, and the types of things that can be done with them— exactly the kind of info you need to get started, but not much beyond that. After all, what goes in those functions is going to be extremely dependent on the environment the VFS is trying to use. I might say similar things about the xBestIndex() and xFilter() functions. While the APIs and how they are used is a tad confusing, their purpose and function should be reasonably straight forward to someone comfortable with relational data management and design. While the book attempts to cover how the APIs are meant to perform their tasks (and has a six page discussion on their purpose and use), actually writing such a function is extremely dependent on understanding the virtual table being design— and the data in it. I feel it is something that just needs to be done by a skilled engineer, with a lot of detailed knowledge about the problem that’s trying to be solved. Again, there aren’t any real textbook examples here; yes, I could write a contrived example, but if they didn’t understand from a general description, a single specific example is unlikely to help anyone in their specific case. At the end of the day, both functions are an optimizations anyways. You can write a functional virtual table without them, it might just run a tad slower. If you really need that last bit