SQLite syntax Error on UPDATE

Question

I have a database Scheme in SQLite database as:

CREATE TABLE AppRecord ( sno INTEGER PRIMARY KEY, version Text,fields Text, appname    Text, appid Text, status Text, displayDetails Text, id Text);

i am trying to update my record with the following query

UPDATE AppRecord SET version ="16",
fields = "[{"lbl":"user","type":"text","required":true},
    {"lbl":"age","max":100,"type":"text", "number":true, "required":true,     "maxPrecision":2},
    {"lbl":"address","type":"text", "multiline":true},
    {"lbl":"date","type":"date","minDate":"3","maxDate":"3","required":true}]",
    appname = "Sales", 
    appid = "sales",
    status = "modified",
    id = "52860e0d012bad414cc98162" 
    WHERE appid = "sales";

Every time i try to do it, it is giving me a syntactical error saying:

ERROR android.database.sqlite.SQLiteException: near "lbl": syntax error (code 1)

I tried my best to figure out the error but I am not able to resolve it. Any help is appreciated.

You have unescaped " quotes. Better use ContentValues with update() or at least ? literal substitution with raw SQL. — laalto
– laalto, Commented Nov 15, 2013 at 13:58
@laalto Sorry i am not getting which place i have skipped quotes — Alok Agarwal
– Alok Agarwal, Commented Nov 15, 2013 at 14:00
The value you're trying to update to fields contains " quotes. — laalto
– laalto, Commented Nov 15, 2013 at 14:01
@laalto thanks i read about Content value and ? literal in the post stackoverflow.com/questions/3760774/… itswoking thanks — Alok Agarwal
– Alok Agarwal, Commented Nov 15, 2013 at 14:11

laalto · Accepted Answer · 2013-11-15 14:47:49Z

2

You're trying to put a string value enclosed in " quotes in fields but the value also contains " quotes.

The SQL way to escape " is to double it as "":

sqlite> create table foo(bar);
sqlite> insert into foo values("string with ""quotes"" embedded");
sqlite> select * from foo;
string with "quotes" embedded

A better way is to use parameter substitution.

In Android Java API this can be using the bindArgs parameter of execSQL(), or more conveniently for update queries, using update() with ContentValues (the whereArg does the same substitution for the WHERE part of the constructed update query).

answered Nov 15, 2013 at 14:47

laalto

153k68 gold badges293 silver badges306 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQLite syntax Error on UPDATE

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related