[sqlite] CEROD in System.Data.SQLite

[E. Timothy Uy]

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

[Joe Mistachkin]

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

[E. Timothy Uy]

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

[E. Timothy Uy]

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

[Richard Hipp]

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

[Igor Tandetnik]

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

[Joe Mistachkin]

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

[E. Timothy Uy]

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

[Simon Slavin]

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

[pablo Van]

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

[Jono Poff]

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

[Peter Aronson]

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

[Jono Poff]

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

[farkas andras]

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. Im 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 thats a minor issue. - In my usage, the 
most inconvenient limitation is that the first  search term cant 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 dont 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 thats 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, dont 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

[Boris Kolpackov]

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))

[Hick Gunter]

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