When using the ODBC driver found at http://www.ch-werner.de/sqliteodbc/sqliteodbc.exe, which SQLite is is use when several versions exist on a laptop? Is there a way to coerce the driver to use the sersion found at a particular location?

That driver embeds the SQLite library; it does not use any version other than the one with which it was linked at build time.

Making that driver use a SQLite DLL instead of statically linking the SQLite code would be nice. You could then "coerce" the driver to use whatever DLL you liked.

Programmatically, how can I choose the version of SQLite that I use?

You would need to arrange that your application uses the DLL form of SQLite rather than statically linking to it. Then you would need to use (on Windows) the LoadLibrary() function, passing to it the unique (enough [a]) pathname of the DLL built using the SQLite version you choose. On other general purpose platforms, there is similar functionality which you can use. [b]

[a. The pathname need only be unique enough that, in combination with the platform's DLL location rules or your override, the correct DLL is loaded. ]

[b. You can follow the calls made by sqlite3_load_extension() to see how this is done for various platforms. ]

Thank you.

I am using C#. I can load SQLite3.dll from a desired/specified location successfully.

How do I execute an SQL statement? (I assume I'd use sqlite3_exec)

A worked example or a URL showing how it is done would be very useful.

A worked example or a URL showing how it is done would be very useful.

Putting "using sqlite from c#" into any search engine returns literally hundreds of such URLs.

I intend no offense with this, so please take it in the spirit given.

What you are requesting is help with general OS or other platform [a] API usage. That is off-topic here, and I am unwilling to degrade the forum with threads that wander far from SQLite related issues. I am sure that most other participants appreciate the present focus of this forum, and I consider it a disfavor to them, whether they participate now or later, to help dilute that focus.

[a. The .Net environment, absent native code linkage, acts as an application platform much as an operating system does. ]

Now, to answer your question insofar as it relates to SQLite:

Once you have loaded a DLL explicitly, you will need to use the GetProcAddress() API (or its equivalent on your chosen platform) to get a function pointer through which you can call whatever code the pointer addresses. This will be a PITA to do for every call into the SQLite library (or the SQLite.NET library), so you may want to arrange to use such a call to get at a function which can provide a structure from the DLL which contains the set of actual library function pointers you will or may use. Then your get-work-done calls can be indirect through those function pointers.

Again, no offense intended, but: Your progress learning to program will be better made by studying APIs, programming language syntax and semantics, and general techniques than by getting others to write or discover sample code for you. As Stephan said, you can find your own sample code if it exists and has been exposed.

Why not simply address the issue raised or simply ignore it ... no offence intended so take it as your gut feeling implies. Anything else belittles the one asking for help; all too often, the intention is to save time or to avoid re-inventing the wheel.

Why not simply address the issue raised or simply ignore it

This is a "generic" reply, not specific to the original poster, their question, or Larry's response.

I can think of two reasons for not ignoring certain requests for help, while not directly addressing the question asked:

• Just as programming is a skill/art that needs to be learnt, so too is asking a useful question. In both cases people can acquire skills themselves; in both cases, a "nudge" from someone to point them in the right direction can be helpful. One common problem is not providing enough detail (descriptions of what was attempted, instead of precise steps; "it didn't work" instead of actual/expected output); another is an X-Y Problem, where the "real" problem gets hidden behind a perceived problem.

• Different message boards, forums, websites have different goals and focuses: some questions (and/or the answers they might spawn) are outside those goals/focus ("off topic"). Sometimes, directing a questioner to better places to ask such questions is the right thing to do.

Why not ignore attempts to make the SQLite user's forum into a general programming forum? Some people are simply ignorant of forum conventions, such as respecting the purpose of a forum. They can be educated and, if well intentioned, can learn the long established custom of using fora for their ostensible purposes. I am willing to help maintain such conventions because, if nobody ever does, the ignorant have little chance to learn. They need only a polite tip to improve.

You have not been belittled. However, you are marking yourself as someone heedless of this forum's purpose. Your issue is, to put it bluntly, not a SQLite issue in any way different from thousands of other libraries you might put into a DLL.

I have no doubt that you wish to save yourself some time by tapping into the general programming experience common among many SQLite users and this forum's participants. That is beside the point. More than your convenience is at stake.

No, not trying to turn this forum into a general programming forum.

My original question was Programmatically, how can I choose the version of SQLite that I use?

This was in anticipation of version 3.34's imminent availability, as announced, and given that

1. System.Data.SQLite is using version 3.32.1
2. Microsoft.Data.Sqlite is using version 3.28.0
3. The ODBC driver at http://www.ch-werner.de/sqliteodbc/sqliteodbc.exe is using version 3.32.3

None of these have the feature that I need, namely:

CLI enhancements:

The .read dot-command now accepts a pipeline in addition to a filename.

And none of the three options that I've tried allow me to choose which version of SQLite I use and I do NOT want to use one version in code and another for the CLI.

Therefore, the question remains:

Programmatically, how can I choose the version of SQLite that I use?

None of these have the feature that I need, namely:

That one feature you are so intent on having is part of the sqlite3 shell application, not part of the library/DLL. No amount of hooking up to the library is going to get you that feature.

Please read what I said first:

And none of the three options that I've tried allow me to choose which version of SQLite I use and I do NOT want to use one version in code and another for the CLI.

The patient said "Doctor, Doctor, it hurts when I do this" to which the Doctor replied "well, don't do that then".

Programmatically, how can I choose the version of SQLite that I use?

We might be able to answer that if you could answer this: Programmatically how do you choose the version of Windows/Linux you use?

Perhaps that's a tad unfair an example since SQLite isn't quite an OS unto itself, let's change that to: Programmatically, how do you choose the version of MySQL/MSSQL that you use?

The answer is: You DON'T, because you CAN'T. There is no special way in which to start your car that would change what Engine it is using today.

However, because SQLite is Open-Source, you CAN (unlike with Windows/MySQL/etc.) choose to compile it yourself and so control the version in use.
Going with this choice, you have some further options:

• Compile it within a stand-alone command-line application. The source for this is also provided and named "the Sqlite CLI". This is a stand-alone program using the SQLite Engine of whatever version was represented by the code you compiled it with.
  
• Compile it as a Library (.so/.dll/etc.) which you can mostly swap for the libraries used by some other software (i.e. control what version they use) like a ODBC driver or such.
• Roll your own. Download the free and open-source source of SQLite for the latest stable/release version (or newer trunks if you like) and compile it into your project directly (it's just C code - see link below).
  
• Lastly you have the option in specifically the case of System.Data.Sqlite or the dynamic libraries, to download from the SQLite site, pre-compiled binaries of the latest versions.

Do you see the trend here? In every case you either need to "BUILD" it or "DOWNLOAD THE PRE-BUILT THING".

Most of the things I mentioned above (ODBC drivers, other software, etc.) come with their selected version "compiled-in" at the time they were compiled. That is the version you are stuck with if you choose to programmatically use that thing. If you want better, roll your own - Instructions can be found by clicking here.

Lastly, all of the above are very basic information (which most people reading it here are probably bored with and stopped reading several lines ago) and none of it were mentioned on this thread before, because this is not a "Learn to program" forum (and I will probably get flamed for "feeding the animals"), but you seem to be genuinely confused by these concepts, so I figured in the spirit of "answering the original question" I would elaborate this time.

PS: One could probably keep a bunch of versions of the amalgamation code somewhere, along with a compiler, then programmatically decide which to compile into a library and then link against that library, or just keep a library of every version and programmatically decide which to link to... so you could "technically" maybe "programmatically" select which version to use in that way, and it is probably what some test suites do, or at least the method of finding/dissecting to the version that first caused a bug, may use.
I'm however confident this is not the OP's use-case.

SQLite is SQLite - SQLite.org provides documentation of its features and scope. I have no contentions.

Likewise, for other RDMBS.

The version of the RDBMS I use programmatically is the version that I install. With SQL Server, I can even get the installed version to operate like an earlier version.

When I install MySQL or SQL Server, the installation of the ODBC drivers or OLEDB Providers are implicit.

With SQLite, I do NOT need to download ANY version of SQLite. I have tried two Dot NET providers and one ODBC driver. EACH ONE OF THESE installs its OWN VERSION of SQLite. I have no option regarding the version that I use. See message#12 in this thread.

I started with the precompiled binaries for SQLite 3.33.

My question was simply an endeavour to figure out if I could use a version of SQLite that I CHOOSE. As far as I can see, I've received only ONE valid response. See message#4. I made some progress with this. See message#7.

However, I came to a sudden halt. Message#8 provided a clue - I am still working on this. That there is no documentation that provides the signature(s) of the function(s) available once I've loaded the library does NOT help.

This forum is valuable for raising real problems encountered with SQLite; the expression of the problems raised is sometimes limited by English Language fluency which is OK.

What I find extremely disturbing/frustrating are the responses (typically from the same individuals and in several threads not just this one) that focus on posters mindset, aptitude, programming experience etc. and do so in a fashion that is denigrating WITHOUT adding any clarity to the question raised. Often, it is alleged that the SQLite problem raised is not within the remit of the forum. (How so when the problem is with SQLite and this is a dedicated SQLite forum?)

I am still looking for a way to use the version of SQLite that I choose with C# or VB.NET or VBA or R.

There are serious misconceptions to be shed before your frustration can end. You claim to want to do something which is ambiguously stated and, to the extent it is meaningful to folks here, impossible or impractical, not to mention useless.

With SQLite, I do NOT need to download ANY version of SQLite.

If you can utilize something properly called "SQLite" on your machine without downloading it (or otherwise transferring it there), then it (or them if more than one such exists) will have some specific version(s), which you cannot "programmatically" select. You might be able to somehow select which already present version you utilize. If you find the already present version(s) insufficient for your (as yet mysterious) purposes, then you will need to download (or otherwise transfer) the version(s) you desire.

endeavour to figure out if I could use a version of SQLite that I CHOOSE

The present and past versions of SQLite are readily available. A brief perusal of the sqlite.org site, particularly its extensive documentation, will lead you to where they are, how to get (or download) them, and how to convert what you get to something you might execute.

extremely disturbing/frustrating are the responses ... WITHOUT adding any clarity to the question raised.

The questions you have posted are extremely unclear, hence you have gotten answers that are responsive to various interpretations of your question. How about, instead of repeating things like "use a version of SQLite that I CHOOSE", you put the actions you intend (or wish) to take and outputs you hope will result into concrete terms?

I am still looking for a way to use the version of SQLite that I choose with C# or VB.NET or VBA or R.

As Ryan so patiently explained, (to no avail), to do those things you will need to either: build the versions you will be choosing into something usable in those programming systems; or find them already built by somebody else such that you can download (or otherwise transfer) them to your machine. That seems like a lot of work either way, for very little benefit that I can imagine (without involving masochism), so I wonder if maybe you have a higher level objective, for which this "choose the version I want" capability is only a means. If you could state what you are trying to do with all these versions, somebody here can probably suggest a less labor intensive way to get that done.

By the way, your early request for sample code and "programmatic" version selection has really thrown the discussion in a direction that seems very different from and incompatible with your present "endeavour to figure out if I could use a version of SQLite that [you] CHOOSE." You have gotten answers to the questions asked that were as good as any programmer should expect and which cast your "only ONE valid response" assessment into the vague complaint category. Your time and our time would be better spent if what you are trying to do was made more clear. Without that, I am ready to view this whole thread as a some sort of childish game playing and a waste of serious people's time.

If you do not compile the program yourself, then consider "downloading the program that someone else compiled" being similar to "buying a car that someone else built".

If the Ford salesman only sells blue cars, you cannot get a pink car from him. Similarly if the Dodge salesman only sells green cars, you cannot get a pink car from him.

If you would like to have a pink Ford and a pink Dodge, then you have a couple of options:

(1) You can go to the Ford salesman and get (or wait) for him to offer you a pink car, and go the the Dodge salesman and get (or wait) for him to offer you a pink car.

(2) You can go fetch all the parts for building a Ford car and all the parts for building a Dodge car, and go to the paint seller and get pink paint, and then put the cars together yourself and paint them with the pink paint you obtained from the paint dealer.

Going to the paint dealer and complaining that the Ford salesman doesn't have a pink car, and that the Dodge salesman doesn't have a pink car, even though the paint salesman has pink paint, will not help your cause in any manner.

What you have done is come to the paint salesman to complain about the car salesman. All that we can tell you is to either (a) go back to the car salesmen and make your case for a pink car, or (b) assemble the pink cars you want from the car parts (which you obtain somewhere that is not here -- we only have paint) and the paint which we will be happy to provide you.

This site sells paint. Not cars.

So if you want a current (or any) version of the SQLite3 engine or the CLI, then you have come to the right place. System.Data.SQLite also lives here.

The dealers for the ODBC thing and for Microsoft.Data.SQLite are somewhere else (the name Microsoft in the latter might give a small clue where you can go to find out about that one and ask for it in neon pink, if that is what you want). The ODBC probably comes from where-ever you got it.

But asking at the paint shop how to get a pink car is a fruitless endeavour because although we can provide the paint, instructing you how to paint a car is not anything that anyone here would know anything whatsoever about. You have to paint it yourself.

So if you want a current (or any) version of the SQLite3 engine or the CLI, then you have come to the right place. System.Data.SQLite also lives here.

The current pre-compiled version for Windows is 3.33.

I have the 'current' version of System.Data.SQLite with the following properties:

Latest Stable Version: 1.0.113.6

Description: The official SQLite database engine for both x86 and x64 along with the ADO.NET provider. This package includes support for LINQ and Entity Framework 6.

Authority:SQLite Development Team

Date Published:Wednesday, November 4, 2020 (11/4/2020)

The statement SELECT SQLITE_VERSION(); returns 3.32.1.

Therefore, your statement (opening paragraph) is NOT correct.

The statement SELECT SQLITE_VERSION(); returns 3.32.1.

That is some SQL which shows the return from a function named SQLITE_VERSION() which, for any loaded SQLite library code, tells what version of the source was used to compile the code. This has virtually no relation to the English sentence, "I wish to select the SQLite version."

Therefore, your refutation of Keith's assertion is groundless.

That's absolutely correct.

Naturally, the latest stable version of System.Data.SQLite (or any other based-upon-sqlite system) has to trail the SQLite release by some time and is still at 3.32.1.

Talking from my observations, System.Data.SQLite will typically start incorporating the newest (3.33) SQLite into it's own code and testing, then after some time, once the changes and needed framework adjustments have settled, will be released with the full new 3.33 version. This is usually somewhere between 2 weeks to a month after the SQLite release wave. (Though I'm sure these figures are not limited to those periods).

This is the normal release cycle of everything.

That said, System.Data.SQLite is also Open-source, the source code is also available here along with instructions if you do wish to build your own so long which will use 3.33. It of course comes with the normal "not stable release yet" disclaimer, but certainly CAN be done.

Like I said in a previous message - If you wish to use a connector or library (or any proxy that is NOT directly including the SQLite code in your own project), then you are a slave to their latest version. And now I can add, their latest version is typically somewhere close behind the latest SQLite version, since adapting to the newest SQLite version requires a non-Zero time investment.

... their latest version is typically somewhere close behind the latest SQLite version, since adapting to the newest SQLite version requires a non-Zero time investment.

Fair enough.

Any guesses as regards the delay in System.Data.SQLite embedding version 3.34 post December 4 when the pre-compiled binary for 3.34 is released? (Hopefully this will be measured in weeks rather than months.)

Any guesses

Why guess? The release notes for both projects are readily found and compared:

• SQLite release notes
• System.Data.SQLite release notes

Still a bit of a guess. The page Warren linked is always available to check, but they do (at the time of writing this) simply say "XX December 2020" so the best we could guess at is "somewhere" in December.

Keep in mind that it's still software... and if everything doesn't go exactly right, it may still be rescheduled.

It generally goes rather quick.

Still a bit of a guess.

Guesses infused with data get automatically upgraded to "informed speculation." 😉

Guesses infused with data get automatically upgraded to "informed speculation." 😉

I guess so. :)

I am still looking for a way to use the version of SQLite that I choose with C# or VB.NET or VBA or R.

Short answer: build your own binary of C#/VB.NET/R wrapper DLL and link it with the version of SQLite of your choosing.

If you are lucky enough and the wrapper uses dynamic linking, you may get away with only building your own SQLite DLL (we might help with that!) and replacing it in the appropriate directory (somewhere near the DLL for C#/VB.NET / somewhere in .libPaths() for R). The fact that it's hard and requires some knowledge of the innards of the target language infrastructure to build your own version of C#/VB.NET/R/whatever other language wrapper is unfortunate, but unavoidable.

For example, with R you might have to download the source code of the package providing you SQLite, replace the bundled copy of sqlite3.c, then run R CMD build path/to/source/directory, then R CMD INSTALL the resulting rebuilt copy of the source package (which requires installing Rtools on Windows). You'll probably get better help in the R-help mailing list with that, because it's R-specific, not SQLite-specific.

A glimmer of hope; sounds promising.

I'll investigate/research further ... some more pointers, URLs, samples/worked examples would help speed things up.

some more pointers, URLs, samples/worked examples

For R there's the definitive guide. Download the sources of whatever package is providing you SQLite and take it apart. Ask on R-help or R-pkg-dev if something breaks.

For C# and VB.NET there's P/Invoke, so, in theory, you could declare all SQLite functions you are interested in as [DllImport("sqlite.dll", ...)] and supply your own sqlite.dll, but that's a lot of manual work that System.Data.SQLite is supposed to protect you from. I have never used or built System.Data.SQLite myself and I can't offer any assistance regarding that. Presumably, you could follow the build procedures, substituting your own copy of SQLite and/or ask around on .NET programming forums.

When I install MySQL or SQL Server, the installation of the ODBC drivers or OLEDB Providers are implicit.

and

With SQLite, I do NOT need to download ANY version of SQLite. I have tried two Dot NET providers and one ODBC driver. EACH ONE OF THESE installs its OWN VERSION of SQLite. I have no option regarding the version that I use. See message#12 in this thread.

I think I can start to see where the confusion comes in.

SQLite is an engine, it never gets "installed". When you say "EACH ONE OF THESE installs its OWN VERSION" you are simply wrong, nothing is getting installed. What happens is that those all use the SQLite Engine, afforded them by the C-Code they added to their own code when getting compiled, or perhaps in some select cases, the code that got compiled against the library they use.

Also, please realize that the ODBC/OLE drivers are not "implicit" when installing, say MySQL. At least one of those are a Windows-only thing and MySQL is multi-platform. What you mean to say is: "The OLE/ODBC connector comes included with the Windows installer". In Linux, we have no such expectation (at least, not typically).
Fair enough though, it is contained in your installer. But you realize that the VERSION of MySQL that the ODBC/OLE connector connects to is simply the version of the RDBMS ENGINE you have installed, right? There is no programmatic "choosing" of the version.

Now with SQLite, very importantly, there is no Server, there is nothing to connect to. SQLite is fully contained inside the process code of whatever is using it.

To use an example - imagine there is a big bread-factory set up in a town, and vendors get their carts and fill up their bread at the factory, then go to the streets/houses to sell it. This is how the ODBC for MySQL/MSSQL/Server RDBMSes work. In SQLite's case, we give you a small self-contained factory, or the plans for one that you build yourself and can sell your own bread from completely free of needing a big factory t o connect to. Now any SQLite connector, such as the ODBC SQLite connector is really a stand-alone little ODBC code around it's own little SQLite factory, it never connects to "SQLite" somewhere else, and so the version of the factory it contains when it was built, IS the only version it will ever use. If you want a connector to use a more newer better version of SQLite, you have to get the newest plans, and rebuild the factory and the ODBC connector that contains it together.

Same goes for any other SQLite connector/wrapper/etc, and any other software that "uses" SQLite - they all have their own version baked-in and you, if you are using any of them, are slave to what version they come with. There are some who can use a Dynamic linked library, but you then still are a slave to whichever version of that library THEY use (which may be local to them and not necessarily the "system"-wide available one). Updating the library will update the version they use, but still, you have to find the newest plans and rebuild said library to make them use it.

Over here on the forum and on the SQLite.org site, we can give you the very latest plans for such an SQLite factory, we can tell you how to build it for use in your code, how to build a CLI that uses it, we can tell you how to build a dynamically linked library that can be substituted in place of an existing library. Heck, we can even give you pre-compiled (you only need to download it and use it) versions of such libraries for all major platforms, plus CLI's for the major platforms.

That said - what we can absolutely never do, is guarantee what version some one of these connectors/ODBC/Wrappers/other SQLite software in the wild will be using, and much less show you a method of "changing" what version they use, especially programmatically. They are what they are. To update them is to rebuild them.

So what you were asking originally (and still) is 100% impossible.

Your question is very much like that of a child asking how to make their ATM card work on their piggy-bank. Naturally a lot of answers revolved around "Mate, please learn about banking first before asking questions like that", and rightfully so.

What you have done though is ignored any such advice and in stead insisted that we are evil for attacking your programming immaturity or personal feelings while still refusing to show you an example of how you can use your ATM card on your piggy bank... and being very demanding that we stop our nonsense and start showing you.

Well, what you want to do cannot be done, it's not possible, it's not how the things work, and we cannot do it ourselves, much less teach you how.

Ask a thing that is possible, then we can show you how, and i daresay, would be happy and glad to assist.

So what you were asking originally (and still) is 100% impossible.

I disagree.

1. https://www.nuget.org/packages/System.Data.SQLite/ should embed the latest published pre-compiled binary as available at https://www.sqlite.org/download.html See also message#22

2. https://www.sqlite.org/download.html ought to make it plain that it is necessary to download the published pre-compiled binaries ... so that SQLite.EXE, the CLI, is the same version as in System.Data.SQLite

Ryan stated:

So what you were asking originally (and still) is 100% impossible.

drawing an ostensible contradiction:

I disagree.

https://www.nuget.org/packages/System.Data.SQLite/ should embed the latest published pre-compiled binary as available at https://www.sqlite.org/download.html See also message#22

https://www.sqlite.org/download.html ought to make it plain that it is necessary to download the published pre-compiled binaries ... so that SQLite.EXE, the CLI, is the same version as in System.Data.SQLite

What "you were asking originally" was not restricted to the latest SQLite version. (eg. "Programmatically, how can I choose the version of SQLite that I use?", repeatedly. See posts 3, 12, 14.) This was clearly the "original" request to which Ryan was responding. (See post 17.)

As Ryan, I and others have mentioned, that is not going to be possible without building your own executable images of the SQLite library code.

You assert that the folks managing the NuGet repository should, for the System.Data.SQLite binaries, "embed the latest published pre-compiled binary", as if this refutes Ryan's observation about your original (and repeated) request which is not about the "latest" anything, except perhaps secretly in your own mind because you neglected to mention that using "the latest" is just what you really want. (There is a very simple way to achieve that secret objective.)

Your contention that the SQLite development crew (which is quite small) "ought to make it plain that it is necessary to download" anything so that the SQLite shell and System.Data.SQLite have the same version likewise does not refute Ryan's true response to your original request. Furthermore, the contention is silly. If that crew were to busily state every potentially interesting SQLite-related fact as obvious as that, they would never get real development done. Maybe then, System.Data.SQLite would nearly always be caught up with the SQLite library evolution.

The download pages (for SQLite and System.Data.SQLite ) have made very clear what version of the library is built into the executable code they make available. Anybody who wants to be sure of using the same library code across multiple platforms and execution contexts has an obvious way to accomplish it: Wait until the downstream library users catch up to the version of particular interest. There is no reason for Richard, Dan or Joe to put a banner somewhere in the docs or download page stating this obvious fact. Nor is there any reason for them to notify downloaders that downstream users of SQLite (whose numbers are legion) might be using older versions. They may as well state that 1+1=2, 1+2=3, etc. until nothing else can be seen.

I must say that I admire Ryan's patience in trying to help you past your confusion, frustration, and poorly framed questions while overlooking your whining and silly contentions. To the extent he has been in a patience contest with me, I concede him to be the winner.

Regarding:

"None of these have the feature that I need, namely: CLI enhancement: The .read dot-command now accepts a pipeline in addition to a filename."

I'm writing to say that the sqlite developers take backward compatibility very seriously, and since the feature only accrues to the CLI program, there should be no disadvantages to having "mixed versions" of sqlite on your machine -- in fact, it's typically what one has. (Just hold off on putting any new 3.34 SQL features in your non-CLI code, of course.)

As to obtaining the new CLI -- as others have said, you can compile it yourself immediately, or just wait 8 days and the official release should be available.

Donald