Re: [sqlite] Confusion re UPSERT syntax error
On 16 Jan 2019, at 4:59pm, David Raymond wrote: > Also note on quotes, single is for a text literal, double is for identifiers. > So it should be > insert into person ("name") values ('hello')... > or just plain > insert into person (name) values ('hello')... though if you're going to quote your identifiers, it should really be insert into "person" ("name") values ('hello')... It's better just not to use double quotes at all, unless you have no control over identifiers and they may have punctuation in. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Confusion re UPSERT syntax error
In the docs there is this line: https://www.sqlite.org/lang_UPSERT.html "The conflict target is required for DO UPDATE upserts, but is optional for DO NOTHING. A DO NOTHING upsert without a conflict target works the same as an INSERT OR IGNORE." The charts are pretty, and useful, but they can show illegal paths due to various rules like this. Also note on quotes, single is for a text literal, double is for identifiers. So it should be insert into person ("name") values ('hello')... or just plain insert into person (name) values ('hello')... -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Matt Sent: Wednesday, January 16, 2019 11:30 AM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Confusion re UPSERT syntax error Hello, I am experimenting with the SQLite Upsert syntax: https://www.sqlite.org/lang_UPSERT.html Based on the grammar, I would expect a query like this to work: create table person ( name text primary key count int default 0 ); insert into person ('name') values ('hello') on conflict do update set count = excluded.count + 1; However, I get a syntax error: > while attempting to perform prepare "insert into person (name) values ('a') on conflict do update set count = excluded.count + 1;": near "update": syntax error It would appear that the index list is required by the implementation, as this works: insert into person ('name') values ('hello') on conflict (name) do update set count = excluded.count + 1; The index list is not required with "nothing": insert into person ('name') values ('hello') on conflict do nothing; Am I misreading the grammar chart? If this is a difference between the grammar and the implementation, which one should be updated? Thanks, Matt Parsons ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Confusion re UPSERT syntax error
Hello, I am experimenting with the SQLite Upsert syntax: https://www.sqlite.org/lang_UPSERT.html Based on the grammar, I would expect a query like this to work: create table person ( name text primary key count int default 0 ); insert into person ('name') values ('hello') on conflict do update set count = excluded.count + 1; However, I get a syntax error: > while attempting to perform prepare "insert into person (name) values ('a') on conflict do update set count = excluded.count + 1;": near "update": syntax error It would appear that the index list is required by the implementation, as this works: insert into person ('name') values ('hello') on conflict (name) do update set count = excluded.count + 1; The index list is not required with "nothing": insert into person ('name') values ('hello') on conflict do nothing; Am I misreading the grammar chart? If this is a difference between the grammar and the implementation, which one should be updated? Thanks, Matt Parsons ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Link errors with SQLITE_OMIT_VIRTUALTABLE
It's important to mention Orgad Shaneh, a dear colleague who helped me get to this solution. Patch contents: From 4184b9fc3cbaeabe1892e5d68ddb886373c8ea00 Mon Sep 17 00:00:00 2001 From: Yehiel Zohar Date: Tue, 15 Jan 2019 09:29:46 +0200 Subject: [PATCH] Fix link errors with SQLITE_OMIT_VIRTUALTABLE sqlite3.c:150722: undefined reference to `sqlite3VtabFinishParse' sqlite3.c:150725: undefined reference to `sqlite3VtabFinishParse' sqlite3.c:150729: undefined reference to `sqlite3VtabBeginParse' sqlite3.c:150733: undefined reference to `sqlite3VtabArgInit' sqlite3.c:150738: undefined reference to `sqlite3VtabArgExtend' --- src/sqliteInt.h | 6 +- 1 file changed, 5 insertions(+), 1 deletion(-) diff --git a/src/sqliteInt.h b/src/sqliteInt.h index fe0bd6226..3f62080af 100644 --- a/src/sqliteInt.h +++ b/src/sqliteInt.h @@ -4388,6 +4388,10 @@ void sqlite3AutoLoadExtensions(sqlite3*); # define sqlite3VtabUnlockList(X) # define sqlite3VtabSavepoint(X, Y, Z) SQLITE_OK # define sqlite3GetVTable(X,Y) ((VTable*)0) +# define sqlite3VtabBeginParse(A, B, C, D, E) +# define sqlite3VtabFinishParse(X, Y) +# define sqlite3VtabArgInit(X) +# define sqlite3VtabArgExtend(X, Y) #else void sqlite3VtabClear(sqlite3 *db, Table*); void sqlite3VtabDisconnect(sqlite3 *db, Table *p); @@ -4408,7 +4412,6 @@ void sqlite3AutoLoadExtensions(sqlite3*); void(*)(void*) ); # define sqlite3VtabInSync(db) ((db)->nVTrans>0 && (db)->aVTrans==0) -#endif int sqlite3VtabEponymousTableInit(Parse*,Module*); void sqlite3VtabEponymousTableClear(sqlite3*,Module*); void sqlite3VtabMakeWritable(Parse*,Table*); @@ -4421,6 +4424,7 @@ int sqlite3VtabCallConnect(Parse*, Table*); int sqlite3VtabCallDestroy(sqlite3*, int, const char *); int sqlite3VtabBegin(sqlite3 *, VTable *); FuncDef *sqlite3VtabOverloadFunction(sqlite3 *,FuncDef*, int nArg, Expr*); +#endif sqlite3_int64 sqlite3StmtCurrentTime(sqlite3_context*); int sqlite3VdbeParameterIndex(Vdbe*, const char*, int); int sqlite3TransferBindings(sqlite3_stmt *, sqlite3_stmt *); -- 2.20.1.windows.1.3.g039c8793a4 This email and any files transmitted with it are confidential material. They are intended solely for the use of the designated individual or entity to whom they are addressed. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, use, distribution or copying of this communication is strictly prohibited and may be unlawful. If you have received this email in error please immediately notify the sender and delete or destroy any copy of this message ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users