Would it be possible to update official docs on the usage scope of query/binding parameters (QPs)? I see related questions on StackOverflow sufficiently often. Ideally, the section https://www.sqlite.org/lang_expr.html#varparam should clearly state that QPs can be used in DQL and DML queries to replace literal values. It might also be helpful to provide a rationale for this feature, which is intended to sanitize user input and, in certain cases, improve performance. And the user input should not be used to alter schema or to identify database objects. It should only be used to filter rows based on values (DQL) and update values (DML).

This matter is not directly addressed in the docs, and I was under the impression that QPs cannot be used in DDL (DDL statements should not be part of the user<->database workflows). However, the column-constraint page documents acceptance of expr, which does accept QPs. Could you clarify if column-constraint in DDL does in fact accept QPs?

Also, column-constraint shows signed-number as an alternative to literal-value. But signed-number is a literal-value, so why this special case?

Wouldn't any more detailed explanation of this just be a recapitulation of the relevant standard document?

Also note the existence of syntactic sugar that blurs the distinction between DDL, DML, and DQL.

    create table objects as
        select name from sqlite_schema
            where type=?1;

Would it be possible to update official docs on the usage scope of query/binding parameters (QPs)? I see related questions on StackOverflow sufficiently often.

First, before I throw water on the assertion that a doc improvement is needed in this instance, please understand that well-intentioned doc suggestions are (nearly) always welcome. We strive to make the docs accurate, useful, and easy to use.

I would need to see specific confusions arising from the current docs, or inability to find needed facts from them upon a serious attempt, before acting on an assertion such as "Some people felt they had to ask questions."¹

Ideally, this doc should clearly state that QPs can be used in DQL and DML queries to replace literal values.

Well, that would be an incomplete statement of where QPs can be used, so either more or less than that would be needed. At one easily found doc page on the subject can be found this more comprehensive yet succinct statement:

"A 'variable' or 'parameter' token specifies a placeholder in the expression for a value that is filled in at runtime using the sqlite3_bind() family of C/C++ interfaces."

That covers the DQL and DML you mention, as well as DDL which can also have QPs. In this doc on QP binding can be found a slight restatement with the same meaning:

"In the SQL statement text input to sqlite3_prepare_v2() and its variants, literals may be replaced by a parameter that matches one of following templates: ..."

And the user input should not be used to alter schema or to identify database objects. ...

That is very true, and to think such a thing after reading about "values" and "literals", one would have to have ignored the meaning of those words.

However, the column-constraint page documents acceptance of expr, which does accept QPs. Could you clarify if column-constraint in DDL does in fact accept QPs?

I most respectfully decline to lard up the docs with such surplusage. Can an <expr> be or contain a QP? Can an <expr> appear in DDL? The answers are "yes" and "yes". And, hence, by the usual rules of construction of an instance of the accepted language, DDL does in fact accept QPs, provided that the QPs appear in the place literals could appear.²

Also, column-constraint shows signed-number as an alternative to literal-value. But signed-number is a literal-value, so why this special case?

I submit that you have let a pair of concepts become unduly confounded in your thinking. As can be seen here and here, "numeric literal" does not denote the same set as signed number does. So we cannot say that one is the other without qualification.

The diagram in which you see a special case is doing no more that using the union of the literal-value set and the signed-number set to cover the allowed possibilities that do not require parenthesis to segregate the default value from stuff that may follow.³ Those sets are overlapping but neither is a subset of the other.

1. ^{^} Sadly, a lot of people ask questions because it's easier (for them) than reading the fine manual.
2. ^{^} We are not ready to tackle the task of elaborating allowed instances of the language SQLite accepts other than by clearly documenting how instances may be constructed.
3. ^{^} This is not the place to get into parser design challenges.

Yes, I am aware of CREATE / AS SELECT, so DDL/DML/DQL distinction is not longer clear.

The SQL standard is a huge proprietary document, and while familiarity with standards is a good thing in general, I am not sure it is absolutely essential. Apart from being open source, SQLite docs are generally a lot more accessible than the standard. I see that this topic is a common source of confusion, so I can see the benefit of having a passage that summarizes the usage scope and points out this issue (even though it might seem too obvious to professionals), as well as provides a rational explanation, so that this "limitation" makes sense. SQLite is (one of) the most accessible databases in the world, so it should be attracting a wider variety of users. For example, I am a physical chemist myself and learn/use SQLite during my spare.

Thank you for the clarification. In general, I agree with note #1. The thing is that the official docs is also the source of information to people who use higher level bindings and may not be familiar with C / SQLite C API and may not be willing to invest time in more technical sections, such the diagrams (regardless of whether this is a bad/lazy attitude). While the QP doc section does not indicate any restrictions, explicitly stressing that a QP may appear in ANY statement in place of a literal value might be helpful.

Thanks PChemGuy for relaying my question and Larry for your detailed answer, from which I draw the conclusion: you can use QPs in DDL.

So should:

CREATE TABLE test (name TEXT DEFAULT ?)

work or not? Cause it doesn't in my context: https://stackoverflow.com/questions/74768208/can-i-use-pdo-parameter-markers-for-the-default-value-in-create-table-statement. But that could be down to emulation in PDO...

No, it shouldnot, can not, never would, whether in SQLite3 or any other version of SQL ever concieved.

This is because the parameter in the above Data Declaration statement must exist for the duration of the existance of the database in which the table is declared, which may be up to the end of the universe a bazillion tirillion gazillion years hence.

A "bound variable" oly exists, at a maximum, for the duration of the execution of the statement containing the bound variable. This is considerably less that the time during whch the result of the computation can exist which requires the existance of the bound variable to continue to exist.

THerefore, it is obviously impossible for the statement to work. And it, of course, does not.

Or, to simplify for the impaired, a default value must be a constant. A bound parameter is not a constant. Therefore a statement which uses a "variable" where a "constant" is required will not function as intended, because the required constant was not provided.

Note that if you try, you will get an error message pointing to your error. The error does not say that you failed to provide a constant where one is required, merely that the ? is an error).

It appears that the documentation is a misleading on this question. This will soon be remedied.

In the CLI, this can shown thusly: sqlite> .parameter set $foo "'bar'" sqlite> CREATE TABLE test (name TEXT DEFAULT ($foo)); Error: in prepare, default value of column [name] is not constant (1) .

Apparently, a to-be-bound parameter is not constant enough for prepare to get past it, even though it would have a constant value by the time the prepared statement was executed.¹

Note that the parenthesized default value expression was needed to get past the parser so that the above failure during VDBE code generation could be hit. Without the parentheses, it fails with a parse error.

1. ^{^} That value would be NULL if not bound prior to prepare().

This answer just makes no sense.

When you bind a value for an insert and run the statement, the bound value is now permanently in the database, and you can re-bind it for the next execution all you want and it won't affect what was already inserted. It will still be there "a bazillion tirillion gazillion years hence"

So it's not that wild or stupid to think "hey, it's a default value, so I can bind a value to the statement and have it be saved when I run it." It is definitely not "obviously impossible for the statement to work".

That is untrue. When you bind a value (variable) into an insert statement, the statement is executed and the values inserted. The "variable" does not persist in the database.

That is to say that the following is permitted:

insert into sometable(somecolumn) values (?)

but neither

insert into sometable(?) values (1)

nor

insert into ?(somecolumn) values (1)

are.

For the same reason none of

create table ?(somecolumn default (1))

nor

create table sometable(? default (1))

nor

create table sometable(somecolumn default(?))

are.

The failure to maintain separation of "programs", "constants" and "data" these days is quite common and the execution of "data" is the root cause of all the "computer problems" (ie, viruses, malicious behaviour, etc) these days.

This is also the reason why all the kiddies these days need to have languages with belts belts and suspenders that very expensively prohibit them from doing the stupid things that they want to do so much.

Ignorance can be cured, but there is no cure for stupidity.

So, do I understand it correctly that a bound variable cannot be used in column-constraint clause of the CREATE statement, even though the present docs suggests otherwise?

Respectfully, would you kindly use a bit more neutral language/expressions? I understand that you may have valid reasons for being annoyed by "stupid" questions. However, I asked this question because the official SQLite docs indicate that

create table sometable(somecolumn default(?))

is valid. And if you are saying that this construct is illegal, there is a clear problem with docs, not with me being impaired in some way (well, of course I might still be impaired, but my question probably does not give you enough information to make objective judgment on this matter).

The docs already state that the DEFAULT clause requires a "constant expression", and explains that none of the following are "constant":

• subquery
• column reference
• table reference
• BOUND PARAMETER
• double-quoted string

"An explicit DEFAULT clause may specify that the default value is NULL, a string constant, a blob constant, a signed-number, or any constant expression enclosed in parentheses. A default value may also be one of the special case-independent keywords CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP. For the purposes of the DEFAULT clause, an expression is considered constant if it contains no sub-queries, column or table references, bound parameters, or string literals enclosed in double-quotes instead of single-quotes."

NO they DO NOT state that.

"An explicit DEFAULT clause may specify that the default value is NULL, a string constant, a blob constant, a signed-number, or any constant expression enclosed in parentheses. A default value may also be one of the special case-independent keywords CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP. For the purposes of the DEFAULT clause, an expression is considered constant if it contains no sub-queries, column or table references, bound parameters, or string literals enclosed in double-quotes instead of single-quotes."

Go to https://sqlite.org/lang_createtable.html, observe the diagram. It includes column-def clause. Expand its definition below. The definition includes column-constraint clause. Expand that definition to follow to https://sqlite.org/syntax/column-constraint.htm. The latter defines

DEFAULT (expr)

as one of the possibilities.

Follow the expr reference at the bottom - there you go - a bind parameter options.

Please explain how this DOES NOT indicate the validity of

create table sometable(somecolumn default(?))

It is a good thing that "3.2. The DEFAULT clause" explicitly prohibits the use of bound parameters. But, when I learn about parameters, I naturally find the section https://www.sqlite.org/lang_expr.html#parameters specifically devoted to bound parameters. There is obviously a good deal of confusion about this topic, and even the official SQLite docs, as indicated by your quote and diagrams add to this confusion. So it makes sense to me that the Parameters section should clearly state the scope, where they can or cannot be used, even if it means duplicating some information. Duplication is sometimes justified even in a database, but documentation is not handled by a db engine, but is designed for humans. When I read a docs section devoted to a certain feature, I expect to gain complete picture sufficient to understand how to use that feature, rather than trying to guess whether there is additional scattered information in other sections.

Yes, but please see (18) above https://sqlite.org/forum/forumpost/69cbd6a5ebbb546a

Also, column-constraint diagram contains multiple occurrences of expr, not just in the DEFAULT clause. My guess is that bound parameters are not allowed in any of those, and yet I can only see this stated explicitly for the DEFAULT clause only. What about the CHECK and GENERATED? Can bound parameter appear in the SELECT part of the CREATE AS SELECT?

I do not argue that reading text is essential. Yet, diagrams are not pictures, they are an integral part of technical documentation. And diagrams should not convey contradictory information. And as far as text is concerned, the main Parameters section lacks information.

Pardon me for not answering your question directly. SQLite is the most used database engine in the world. And perhaps the most accessible RDBMS to users of various backgrounds. SQLite docs also advocate, among other things, to use it as an application file format and as a container for small files to increase performance and for a bunch of other applications. And it is important that this background diversity is taken into consideration. Of course, the docs cannot dumb down every single bit. But docs should strive to provide a consistent picture in an efficiently accessible manner for IT-savvy audience who are not DBAs or C-programmers. In fact, SQLite has a variety of higher-level bindings, which often abstract the notion of statement preparation to a certain extent or completely, and yet probably fully support parameterized queries. So you should expect that many users/readers are not familiar with the C API and may not have the necessary background to feel in fairly low level blanks or decide where they should trust the docs and where they should not.

I am aware of the gencol page, which does not include a single occurrence of the word "parameter".

For example, as a Python or ADODB or whatever else coder, it should not be necessary for me to understand how a particular engine handles parameterized queries and query parameters. It is a black box for me, that is the whole point. I plug in a string query and supply query parameters. Perhaps, not being able to understand the difference between identifiers and literal values/expressions is my problem, and the docs should not devote too much efforts to this matter. But if the docs explicitly says that I can plug something into the statement, I should be able to trust that.

Post 25. Read it over and over until you grok it in fullness.

There is a dedicated page on diagrams which says nothing about it. Point me at readily accessible place in docs providing an overview and stating you point of Post 25. I do not have the powers to read developers' thoughts. The SQL syntax page, which references the diagrams page says: "The SQL language syntax is described by syntax diagrams." It says nothing about "simplified, graphical representations intended to provide an overview of the syntax."

Can you explain in technical terms why would the to-be-bound parameter be not constant enough? Why is parameter binding not allowed in this place? Does it have something to do with the duality of the default value?

The same syntax in MariaDB works without any issues, so it's not a generic limitation of SQL syntax. I assume it's something specific to how SQLite is designed. Is it?

It looks like you misunderstood my question or the topic in general. I am not asking how to have a parameter as a default value for the table. I am only asking how to parameterize the CREATE TABLE statement. As I understand from previous reply, this is not possible in SQLite because it doesn't store the table in an internal format.

In MariaDB the value that is provided to the prepared statement when it's executed is actually stored in the internal representation of the table as a string value.

The same syntax in MariaDB works without any issues

That would be a MariaDB specific extension to SQL, if it were in fact the case.

Maybe, but I don't really see anything in the standard SQL syntax that would prevent this.

The "standard SQL syntax" has nothing whatsoever to do with anything. You simply cannot do that in standard SQL. Deal with it. If you wish to diddle with a non-variable part of a SQL statement, then you must diddle with it by some method other than bound parameters. Bound parameters are used for passing "variables" between the host language and the SQL RDBMS subsystem.

If you wish to modify the statement itself, then you need to modify the statement itself. You cannot substitute a "bound variable" for a non-variable part of the statement.

Respectfully

That's really a point.

thought about Jamiroquai here. "When you gonna learn? ~ ♪"

(will this go through moderation?)

(will this go through moderation?)

Your posts aren't currently moderated, but if you'd like them to be then keep it up ;). Please try to keep snark to a minimum (the closer to zero, the better) - it's generally not appropriate in this particular forum.