CODEWITHSUNDEEP
androidsqliteandroid-sqlitesql-insert
G_V
G_V

Reputation: 2434

Android - SQLite no such column, passed columns are ignored

ANSWER: String fields require ' ' single quotes around them

I keep getting the message "no such column: value (code 1)" without understanding why.

I am trying to store persons with indexes so I can uniquely identify them and retrieve more complex data that doesn't need to be queried as json, for which I use Gson to convert to and from Json from an object.

Edited the create query to show a functioning UNIQUE function. Here you can find more information about ON CONFLICT keywords

//Creation query
private static final String CREATE_PERSON_TABLE = "CREATE TABLE IF NOT EXISTS
    "+PERSON_TABLE_NAME+" (id INTEGER PRIMARY KEY, firstname VARCHAR, lastname VARCHAR,
    zipcode VARCHAR, birthdate VARCHAR, json VARCHAR,
    UNIQUE(lastname, zipcode, birthdate) ON CONFLICT REPLACE);";

//Insert or replace query execution
getWritableDatabase().execSQL("INSERT OR REPLACE INTO "+PERSON_TABLE_NAME+"
(firstname, lastname, zipcode, birthdate, json) VALUES 
("+person.getFirstname()+","+person.getLastName()+","+person.getZipcode()+",
'"+person.getBirthdate+"','"+person.getLifestoryJson()+"')");

Stacktrace

android.database.sqlite.SQLiteException: no such column: Hank (code 1): , while compiling: INSERT OR REPLACE INTO person (firstname, 
lastname, zipcode, birthdate, json) VALUES (Hank,Hairy,8888HH,'06-01-1966','{"events":"[]","comments":[],"storyList":[],"favoriteFoods":"cheddar","peperoni":"lasagna"}')
            at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
            at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:882)
            at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:493)
            at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
            at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
            at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
            at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1663)
            at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1594)

I've been staring and comparing with tutorials for hours now, I'm just not seeing what is going wrong. It appears that for some reason the columns I pass are ignored and the values are used as columns instead. Any help or explanation as to what can cause this error is very appreciated.

Upvotes: 0

Views: 814

Answers (2)

clockwerk
clockwerk

Reputation: 263

I don't know if this has anything to do with your problem, but for SQLite I believe the data types allowed are:

INTEGER, TEXT, BLOB, REAL, and NUMERIC

so your create table statement might be causing some issues.

here is a different create table statement:

CREATE TABLE 'person_table_name' (
    'id'            INTEGER,
    'first_name'    TEXT,
    'last_name'     TEXT,
    'zipcode'       TEXT,
    'birthdate'     TEXT,
    'json'          TEXT,
    PRIMARY KEY(id)
);

your create unique index maybe can change to:

CREATE UNIQUE INDEX 
person_unique 
ON 
person_table_name (first_name, last_name, zipcode, birthdate);

your insert or replace into statement can maybe change to:

INSERT OR REPLACE INTO 
person_table_name (first_name, last_name, zipcode, birthdate, json) 
VALUES (
    'Hank', 
    'Hairy',
    '8888HH',
    '06-01-1966',
    '{"events":"[]","comments":[],"storyList":[],"favoriteFoods":"cheddar","peperoni":"lasagna"}'
);

I used "DB Browser for SQLite" (small program to create db and test sqlite statements etc.)

all the above statements executed successfully.

I think for your exact situation you need to change the following for your specific statement:

getWritableDatabase().execSQL("INSERT OR REPLACE INTO " +
PERSON_TABLE_NAME + "(firstname, lastname, zipcode, birthdate, json) +
VALUES ('" + person.getFirstname() + "','" + person.getLastName() + "','" + person.getZipcode() + "','" + person.getBirthdate + "','" + person.getLifestoryJson() + "')");

it appears you don't have ' (single quote) around the person.getFirstName() and person.getLAstName() method calls

Upvotes: 1

Simas
Simas

Reputation: 44118

Strings need to be wrapped with quotes:

getWritableDatabase().execSQL("INSERT OR REPLACE INTO "+PERSON_TABLE_NAME+"
(firstname, lastname, zipcode, birthdate, json) VALUES 
('"+person.getFirstname()+"','"+person.getLastName()+"','"+person.getZipcode()+",
'"+person.getBirthdate+"','"+person.getLifestoryJson()+"')");

Somehow you did wrap the date, the lifestory, but not the firstname, lastname and the zipcode.

Upvotes: 2

Related Questions