SQLite Forum

I was mindful of a certain Aesop's fable ("The Man, The Boy, and The Donkey") while making this last¹ change. However, in light of your comments, Keith's comments, and what I understand to be an accurate disclosure policy for API behavior, I decided that the additional changes about return type and its effects is worth the extra verbiage². Please let me know if this falls short of your expectations for what the docs should accomplish. (This invitation goes to Keith also.) Please note that when converted to HTML, this renders prettily, so critiques should be about content and clarity rather than aesthetics.

1. At least, I hope it's the last for the present issue.

2. It can be a narrow path between glossing over occasionally relevant detail and verbosity that hinders comprehension of more commonly important points. The fact that the added verbiage was already in a footnote-like paragraph made this change easier to justify.

Ok, I see your point now, although I could not easily grasp that you might expect anything but text from something named "str*". (I know you said it, but I read too fast when busy sometimes.)

I don't agree that the type of the return breaks the claimed equivalency. And I am not convinced that such difference as there is in return type is worth the doc dilution effect of explaining it, in part because words like 'number' and names like 'str%' nearly shout that they are not precisely equivalent.

If the results were in fact equivalent when converted to REAL or TEXT (one way or the other), I would agree that the difference probably does not matter.

However, I ran a little experiment, running the following repeatedly:

 select x, y, x-y from (select julianday('now') as x, strftime('%J', 'now') as y);

 select x, y, x-y from (select strftime('%J', 'now') as x, julianday('now') as y);

This yields a result that makes me think some words on the difference could matter, to those naive enough to be comparing floating point numbers. The difference term always has one of 3 tiny values, set of which is centered on the origin. To get it to be 0.0 consistently, I have to say:

select x, y, x-y from (select strftime('%J', 'now') as x, printf('%17.9f',julianday('now')) as y);

I'm still mulling over whether mentioning these minutia is a net benefit. While doing so might be more technically correct, they will likely be a distraction for the vast majority of users who either just want to know what to use to get what they want and care little about numerical accuracy limitations of binary floating point values rendered into decimal form.

For the System.Data.SQLite wrapper, this forum is the right place to come for help. There is no "support" in the sense of anybody obliged to try to get users through their difficulties. That is why you are encouraged to read the documentation for System.Data.SQLite as your primary source of help.

Last time I looked, that documentation was in the form of a .htm file (which is some sort of Microsoft-devised HTML archive), and is impossible to link from here. You should have gotten that archive along with the library. Somewhere in the connection constructor or methods doc, your options with respect to auto-creation of the DB file can be found.

Having gone over that doc earlier, and seen it as a model of clarity, I was tempted to disagree with you that more clarity was needed. However, having read it again with the idea that it might mislead, or fail to overcome a common (but not required) interpretation of certain words, I have come around to your view. The phrase "day number" is too easily assumed to be a "whole day number" (or "an integer" in computer/mathematician-speak.) So, to ward off that not quite required, but natural interpretation (for some fraction of readers), I have made this change. I think that suffices, but will carefully consider opinions, and especially arguments, to the contrary.

Thanks for the tip.

I've not actually been executing any Open() or Close() statements ...

The library you are using is layered over the SQLite library. If you are causing that intermediate layer to create, read and modify SQLite databases, it is a virtual certainty that one of the sqlite3_open*() family of functions is being called. Equally certain is that your code is instigating such calls.

I gather that you have not focused on the flags passed to the _v2 variant, because if you had, you would have seen that there is some control to be had over whether the DB file is auto-created. I had thought that once you understood that, you would then look at the docs for the library you are using most directly to see how to affect what flags get passed into the sqlite3_open*() API on your behalf.

I could go read the System.Data.SQLite docs for you, but decline. Please consider this tip as to where to begin reading as enough to get you going: The sqlite3_open* calls are made from connection object methods.

(Edited to correct for oversight.)

Yes, it is possible to do something much like that. Please read the latter part of the article you linked where it says how to do that without the PG-special ON syntax, and let us know if you cannot do it the same way with SQLite.

See Opening a New Database Connection, where someone as clearly aware of details as you can see what is going on. Pay particular attention to the issue of flags passed into the open.

Didn't you imply though that basically this one-liner would suffice to get the changeset/incremental view updates?

There was nothing about this incremental or updating behavior in your original post. So I have no idea where such an implication could have arisen.

I still don't know how that would work without rerunning the entire SELECT query on every database update and then somehow manually computing the difference between the stored table and the updated query result.

There are more incremental ways. The INSERT and UPDATE triggers have enough information available to permit a less heavy-handed approach. Whether the ROI on exploiting that information is positive is for you to determine.

Your understanding of table stability is correct. I did not mean to imply that a table created using "CREATE TABLE <name> AS SELECT ..." would somewhat dynamically reflect changes to whatever data the SELECT drew from.

Do these updates have to be triggered manually?

Yes, if you really want them to happen and have not bothered to effect those changes automatically with UPDATE and INSERT trigers.

is there any way you can extract the Select* from the sqlite3_stmt (*Vdbe) ... after it's fully prepared?

No. The '*' is not incorporated into the VDBE code.

is there not a case for a TREEVIEW [NUM

TEXT] sql command akin to EXPLAIN?

I've not seen such a case made. That code is really for internal use by the SQLite developers. It's output is not stable. I would have little hope of prevailing with an argument for publishing that as an interface.

I understood that intention when I posted #3 (which remains pertinent.)

There is no present direct support for such a thing, and I know of no extension that does it. (That latter is what I call "a statement of ignorance.")

It might be useful at times. You can get the same effect by using the CREATE TABLE AS SELECT ... construct and creating a changeset from the resulting tables.

The ease of doing this would be part of the "why not?".

how can I check if it is safe to call step on it?

It seems to be a problem of your own devising. ("I want to check if the given statement ran to completion, but I don't want to have to keep the result code along with it") If you are willing to preserve just 1 bit along with your sqlite3_statement pointer, to record whether it is "ready to run" or "has been run to completion", your problem is solved. But you are asking for some way to get at the execution state that is not presently exposed by the API, except by the dubious means of trying to step it and assessing the result in light of this doc.

I think you need to just bite the bullet and keep track of whether SQLITE_DONE has yet been returned without an intervening sqlite3_reset() call.

Please study this working code: create table if not exists Country(prefixBeg text, prefixLim text, name); insert into Country values ('A2A', 'A2Z', 'Botswana (Republic of)'), ('A3A', 'A3Z', 'Tonga (Kingdom of)'), ('A4A', 'A4Z', 'Oman (Sultanate of)'), ('A5A', 'A5Z', 'Bhutan (Kingdom of)'); .parameter set @prefix "'A4A'" select name from Country where @prefix>=prefixBeg and @prefix<prefixLim; , which runs in the SQLite shell and acts as I expect and you should too.

That is consistent with what the docs say. It appears a reread is due.

Your test selects for values within an empty range. The WHERE clause is false forever, assuming Prefix_Start <= Prefix_End (which seems like the only sensible way to do such a thing.)

... it would [be] the applications responsibility to ensure that the values don't change until...

That is an easy statement to make, but a much less easy rule to adhere/enforce/test for in reality, especially with the gain being insignificant.

My initial thought when I saw this thread appear was much like that, and reminded me of a bit of prescience I had when multi-threading was first becoming a thing and microprocessors were getting more than one "core". I figured it would be a potent source of bugs because it required discipline not evident in a significant fraction of programmers I knew.

Those last two tokens specify different identifiers. The first is 3 characters long and the last is 5 characters long. The tokenizer does not "distinguish" in any way except by faithfully collecting what lies between the functional delimiters.

The doc on CHECK constraints, (last sentence of first paragraph), says "The expression of a CHECK constraint may not contain a subquery." Without a subquery, there is no "previous column value" (which I take to mean the value of the same column in a "previous" row.)

Others may have words on how to think about ordering and its enforcement. It's a dubious concept, but beyond me this close to bedtime.

When one is INSERTing or REPLACEing (or UPDATEing), one often does it in a loop.
For these situations, one currently has to call sqlite3_bind_XXX once for each parameter for each loop.

One also often does binding in code that is not directly part of such a loop. For example, the binding may be deferred a model/view adaptor of some kind which, when running, would have a different set of automatic variables than where the sqlite3_step() is being done. Or, an example from the SQLite shell: Binding is done in a subroutine that consults a DB table. Or the values being bound may come from a dynamic source or some sequence of sources (such as a tree walk or iteraction over a std::vector, where an unnecessary copy would have to be made so that those "by reference" bindings could keep working.)

To answer your numbered questions:
(1) No.
(2) No, but it would have very limited utility.
(3) I don't think so, but you could do it.
(4) Speaking only for myself, no.

To answer a question you might have asked: "Is there a good way to accomplish the same thing in an application using SQLite?"

This would be child's play with some templatized C++ and/or a vector of polymorphic binder objects. The pattern is "Set of do_binding objects which each know how to get the value to be bound." This "know how to get" is (and should be) much more general than "deref a pointer", IMO.

Having not read its code or understood its genesis, I cannot speak to what go-sqlite3 will do. However, if it is a faithful rendition of the SQLite v3 code, (rather than merely misappropriating the name), and if no open transactions or unfinished backup objects exist, then the user-visible^a consequence of not closing "the db" before the application exits should be precisely the same as if it was closed before exiting. This is because sqlite3_close() (or its faithful rendition) is for reclaiming resources held by the "db" object, and under the stated precondition those resources are just memory. In the Go system, heap memory resources are reclaimed using garbage collection, but even if the garbage collector cannot run, the memory it might have collected will all become available to the system when the process exits, at least for any respectable OS.

a. Here, "user-visible" means visible in the ordinary usage patterns, not including examination of process memory when using a debugger or memory usage tracing diagnostics.

(Responding to a point untouched earlier:)

Since CLI parser behavior applied to all commands then such "magic" quotes might do a bad thing in SQL queries

No, that cannot happen. At present, all SQL which the CLI collects and passes to the SQLite library for execution is untouched by any CLI argument parsing. The quoting rules of section 4 apply only to "dot-commands".

(Edited to reflect action rather than intention.)

I have to agree that the doc on argument parsing, when parsed per usual English conventions, does not lead one to expect the result you show (and which I have replicated.)

I have clarified the rules by which dot-command arguments are parsed. These changes have been pushed to the website. While the rules may be somewhat more verbose than is strictly necessary, they should be complete and clear regarding present behavior. Observations to the contrary are welcome.

Running a docker container and the docker container crashes up to 3 times a day due to the following sqlite issue.

This is NOT a SQLite issue. You should report it to whoever is responsible for that go (sp?) translation or rendition of the SQLite C library.

Good luck.

I would much prefer that the CLI's parsing match what POSIX says shells should do. Anticipating what quoting rules do is challenging enough without extraneous complexity.

Unfortunately, there are too many likely uses of the SQLite shell which rely on its present argument recognition to institute such a change without it being optional in some way, either at build-time or as an invocation option.

The website reflects the current release, as a rule, and is published from the branch-#.#... branch. The doc change in question was done on trunk (which becomes a branch-#.#... branch upon the next release.) It probably should have been cherrypicked to the soon-to-be-published branch, which I just did only to find that Richard beat me to it.

Thanks. Fixed.

FWIW, I could not figure out what that option's default was until I read the code.

The only documentation I see, on the 1/3 day old sqlite3_vtab_distinct branch (which is the only place anything about this appears publicly), is embedded in the code. Documentation located in code with the CAPI3REF: tag is usually not expected to do the whole job of documenting the API. Instead, it is for details specific to each API, and needed to either write calls into it or remind code readers (and writers!) what has been promised at the interface. It is not expected to address how and when the API should be used, particularly when a datum or entry point is part of a group which operates together such as the optimization interplay between the VDBE compiler and a vtab implementation.

It is way too early in the life of this particular API to be worrying about clarity of what is likely to be only a portion of its documentation.

I do not understand why you think the (possibly) dynamically varying information to be returned by that API function should instead be ensconced in a (an extension of) the sqlite3_index_info struct. For the library to convey that information through that struct, it would have to compute it ahead of time without regard for whether there is any use for it in the particular virtual table implementation.

There will undoubtedly be a beta drop of the library before that feature becomes part of a release. I do not mean to discourage feedback, and feedback on the API design and docs are welcome, anytime for the docs and before a release for new API details. But today, it's just too soon to worry clarity and how-to issues.

All that to avoid parsing the errmsg()?

No. All that consisted of a few alternatives, differing in various costs.

(1) Note that in this application there is no need for the initialization to be done as a transaction; the only restriction is that only one process should do it.

(1) I do not see how you intend to arrange the "only one process", but a single "CREATE TABLE ..." statement is going to run in a transaction whether you see it as needed or not, at least when using SQLite.

(2) Searching the errmsg() for "already exists" has to be faster than a transaction and table does not exist.

(2) You should include the cost of the sqlite3_prepare call in your accounting. Does the error message search or potential extended error code examination still win when you do that?

All I really want is an Extended Result Code so that I am not forced to know what wording will be used in errmsg().

It's an interesting dilemma you have. Your program can work with either present and older versions of SQLite which have complained "table * already exists" for many years, or it can work only with some future version having this feature you "really want", a version which may never exist. This would present no hard choice for me.

The introspection features are relatively new (compared to SQLite v3), but they will be stable. And they take very little code, either in source or compiled form. It seems to me that unless your table definitions(s) will never change, simply knowing that it exists will not be enough either; some examination of how it exists will be needed. And the work to discover than will yield whether it exists with virtually no extra work. In my view, this should be considered when worrying about "all that" versus the "try and see what went wrong" approach.

Your "Extended Result Code" is no panacea either. Such results tend to not be stable, perhaps even less stable than simple error messages. Even if the same extended error codes can be returned, as the set grows how those returns map to the (unbounded) set of possible input errors changes. Of course, that can be managed with extended^N error codes, where the exponent N increases over time.

I think developers' time is better spent getting things right in a deterministic manner than in deciding what needs doing based on parsing error returns, however they may be structured. (That's why this would no hard choice for me.)

Adding to R.Coder's reply:

Using MSVC's dumpbin tool after doing the same wget and unzipping: [C:\Tmp] > dumpbin /exports sqlite3.dll | grep frombind 249 F8 00001267 sqlite3_value_frombind

I dread running the dependency walker anymore because it takes forever to figure out modern, delay-linked DLLs, but eventually, after sucking CPU for 5 minutes and revving the fan at full tilt^a, it also finds that export.

At this point, I wonder what dependency walker you are using. Also, what makes you use the plural "exports are missing" when your post says "There may well be other missing ...". I get precisely as many exports from dumpbin as are present in the accompanying sqlite3.def (using grep and wc), so even the singular would be an overstatement.

a. The walker has to hit a recursion limit numerous times on kernel32.dll, which it dutifully (and painfully slowly) displays after working so hard to descend into a loop in the dependency graph.

Thanks. These flaws are fixed, most as you suggest.

The SELECT part of the statement should always have a WHERE clause, even if it's just WHERE true.

Why do you assert that? The WHERE clause restricts (or filters) output, and "WHERE true" is a no-op, yielding exactly the same VDBE code as if not present. Do you think it is clearer to say "there is no restriction" than to not say there is restriction?

(Referring to a prepared statment, Baruch wrote:)

If it was not stepped yet at all then this will give true even though it has not finished

That assertion is contrary to what the API doc says. What leads you to say that?

It is conceivable but likelihood depends a lot on the nature of the corruption.

Given that the DB schema is stored in the first 16 Kbytes, it may well have been clobbered, much diminishing chances of a useful recovery. What does pragma_integrity_check have to say?

Have you tried running .recover on a copy of your corrupted DB?

Do you have any way of ascertaining whether the result of a .recover at least contains your data, perhaps only missing indexes (which can be readily rebuilt)?

I urge you to consider whether "completely" is the only degree of recovery useful to you. You should be aware that it may not be possible to tell whether all the actual data was recovered.

What is certain is that there is not enough redundancy in a SQLite DB file to permit arbitrary alteration of the first 16 Kbytes without making it uncoverable for most such alterations. So a lot depends on how corrupted your DB is.

I have a hand-built, custom design, MC6809 puter (with 64K of DRAM and a $1K, 5MB, 5 inch hard drive), and would have a similar collection if not for the need to make sacrifices in the interest of marital harmony. It has been decades since it was powered, and its power supplies may have vanished along with all the other stuff having less sentimental value.

It would be fun to see that collection.

(Normally, thread hijacking is frowned upon here. But this one had no interesting future anyway.)

When I double-click on the released v3.36 sqlite3.exe, I get a vertical scrollbar.

I think we're willing to take your word on the scrollbars without pictures. After all, those are easily faked if some grand hoax is in progress.

I'm pretty sure that the scrollbar presence depends upon whether the screenbuffer line limit is greater than the display line height, which settings can be set from the window that comes up and retained without further ado by a user. So no deep meaning should be attached to these variations. As I said, there is no code from the SQLite project at work behind this scrollbar presence (or absence.)

I take it your menagerie includes an operational Windows 1.0 system? Or are you claiming phenomenal memory? (I'm ready to be amazed either way.)

If that's what this is about, the same happens on Windows 10. But it is not due to any particular code in shell.c.

Am I wrong about that?

No, and your code-reading skills are fine.

Now, to be sure, due to the implementation of command line parsing in the shell and the .parameter command, using that command is not a way to secure against SQL injection and the like. Possibly, the documentation should contain a warning against that misconception.

This recent revision, (soon to be pushed to sqlite.org), indicates as much without any paternalistic, "and this enables SQL injection." That is blindingly obvious, to the point of baby-talk redundancy, from the revised text.

I will consider mentioning there that those who might allow sight-unseen values to reach the ".parameter set" meta-command should probably be aware of the -safe invocation option. I am also considering giving .parameters a "text" subcommand which would provide the quotes to guard against SQL injection. (Normally, mere quoting is not the best or easiest way to do that, but the way it is done in the library's printf implementation is pretty fool-proof. Hence I am disinclined to go to the trouble of created a bindable parameter and binding it for the .parameter command.)

Can you elaborate that a bit? To my knowledge, the sqlite3.exe published at the download page has no GUI, and "scroll bars" are a GUI feature. So your news is quite puzzling.

What am I missing?

select * from pragma_table_list('employees');

Note the 1-character spelling difference.

I believe I already explained and proved that .parameter set doesn't use parameter binding and anyone who know just little about C, can see it in source code, so that's my point, if there no "real parameters binding", then there no reason to reference to such binding in documentation.

I know a little, perhaps more, about C, and hence have not ever argued that ".parameter set" does any binding. However, parameter binding is the reason for the temp.sqlite_parameters table to exist. ".parameter set ..." is useful for loading or modifying that table (as its doc clearly explains.) And, in case anybody wonders (as they should), "Why would I care what is in that table?", or "Of what use is the .parameter meta-command?", the answer is right there too, stating that SQL submitted to the shell is subjected to a parameter binding process where named parameters whose names are in that table are bound to the value associated with the name in that table.

This is not rocket science, and I cannot fathom why it has become a point of noise and confusion. As far as I can tell, the document was clear a week ago, and with the text added to ward off a natural but false assumption, has become clearer since.

You have not made the case that the documentation is wrong, unclear, or lacking any essential element(s) regarding the ".parameter set" feature. You have thrown out a flurry of assertions to that effect, which have tended to show what confusion arose in your own thinking. (And that has done some good, resulting in the above-linked clarification.) But saying you have already explained and proved something which nobody contends and is not claimed in the documentation does nothing to dissuade me that "the documentation on the .parameter meta-command, as recently modified, accurately states what ".parameter set ..." does." To accomplish that would take: (1) A specific citation to some particular text in the documents; (2) A statement as to what is false about that text; and (3) why and upon what evidence you believe it to be false.

At this point, I intend to ignore any further vague assertions on that subject.

there no reason to reference to such binding in documentation.

In a different post in this thread, I have explained in detail why that reference to the binding process is present within the ".parameter" doc. See the last two paragraphs, where the reason is elaborated. If you continue to believe "there is no reason" after that, and wish to convince me of it, you will need to explain how else anybody reading that document would guess why the ".parameter" meta-command exists without reading a lot of (C) code.

I can understand that your reading of the document may have led you to false belief about the effect of ".parameter set", because of that brief discussion of binding. However, at this point, I am not prepared to attribute that confusion to document deficiency without a clear argument making that case.

Look for this text in sqlite3.c: ** The "sqlite3_stmt" structure pointer that is returned by sqlite3_prepare() ** is really a pointer to an instance of this structure. .

The parser hardly sees those square brackets. The tokenizer deals with them, and without much fuss.

The Documentation must match implementation.

You have a different view of this than the project team members do. What we strive to accomplish with the documentation is that: it be useful to SQLite users, allowing them to use the API and tools; it state what can be relied upon; and that it not misstate what can be relied upon. Subsidiary to those objectives are clarity, organization and discoverability. It is most definitely not an objective (among the members) that every possible outcome of using SQLite be predictable by recourse to the documentation. Some behaviors are left unspecified, some are explicitly said to yield undefined results, and some are left open so that future enhancements may be made without contradicting a previous version of the documentation.

It is my position that the documentation on the .parameter meta-command, as recently modified, accurately states what ".parameter set ..." does. Further, I contend that this documentation is enough to allow users to employ that meta-command and does not misstate anything about it. If you contend otherwise, please support your contention with an argument which includes evidence and reasoning rather than bare assertions or platitudes.

The .dot command .parameter using the same syntax that used for parameter binding.

Each of the sentences I get when trying to convert that into a declarative sentence is false. The .parameter doc does not claim that it uses the same syntax as is used for parameter binding (or for bindable parameters either.)

It even using the same template syntax, but under the hood, it doesn't do any real binding.

No, it does not "use the same template syntax". It supports such use, and that is all that is claimed. While it is true that the .parameter command, by itself, does not do any "real binding", its purpose is to support a concurrently added feature, described alongside the .parameter doc, whereby when the shell is asked to run some SQL, it attempts to match parameter names found in the statement to names found in the temp.sqlite_parameters table, and when a match occurs, binds the associated value to the named parameter. That under-the-hood binding is entirely real, at least inasmuch as software phenomena can be real.

This sentence: "..." should be removed from documentation related to CLI implementation of .parameter command because substitution in printf that forms SQL query is not what described in that first sentence of documentation.

That sentence is critical to understanding the present purpose of having a temp.sqlite_parameters table and a .parameter meta-command to populate it. Hence, it is entirely appropriate in that context. Further, there is nothing in that documentation (as modified for clarity) that is misleading. And, in my opinion, none of it is superfluous or could be removed without undermining the purposes of the documentation.

The printf substitution that ".parameter set ..." does is an independent fact from what parameter binding does (when SQL is submitted for execution.) However, given the close relationship between ".parameter set ..." does (populating temp.sqlite_parameters) and what the shell's binding process does (reading temp.sqlite_parameters), putting these facts in close proximity and describing the relationship between those actions is entirely consistent with the above-stated documentation purposes.

Per the parameter syntax (or spelling) rules both ?01 and ?1 refer to the same parameter.

The reference link you provided is related to Binding Values To Prepared Statements, but as I already pointed multiple times, - .parameter .dot command IS NOT related to binding at all and this mismatch between documentation and real implementation leads to confusion and wrong assumptions.

You are replying to a post in which that link and the text resting upon it was entirely responsive to the immediate subject. Neither the link nor my reply was intended to address your "binding" issue raised and reiterated in a separate thread. In a series of posts on subject X, it makes no sense to complain that subject Y is not addressed there.

.parameter .dot command feature can not be referenced to unrelated documentation that's describes "Binding Values To Prepared Statements".

Nonsense. The thrust of my post #9 is to show that the treatment of ?1 and ?01 parameters mentioned in post #8, to which I was responding, is independent of (or "orthogonal to") how ".parameter set ..." behaves. The behavior shown in post #8 is highly related to the document I cited, and the facts related there support my contention in post #9. When I cite a fact X in support of "Y is not an instance of Z", where X helps explain what Y is, to complain that X is unrelated to Z is facetious.

You might argue that you should be blocked from creating key/value pairs that cannot be used directly in a query

That's the reason of my report.

I understand your belief on this "should", but you merely stated it without arguing it in post #1 (which would mean providing reasons that others should adopt the same belief.)

What is the harm? Would you agree that no documented behavior is violated by the toleration ".parameter set ..." has for keys that cannot be parameters names? What positive good is achieved by changing the behavior to better match your belief?