How do I retrieve data from database using cursor? (Android, SQLite)

Question

I am stuck. I can't find out what I do wrong, so please help anybody. Though it's quite simple task, I don't understand how actually database looks like. Here's the code I use to insert data into table in database: ``` EditText title = (EditText)findViewById(R.id.Title); String Title = title.getText().toString(); EditText d = (EditText)findViewById(R.id.director); String Director = d.getText().toString(); SQLiteDatabase db = openOrCreateDatabase("Films", MODE_PRIVATE, null); db.execSQL("CREATE TABLE IF NOT EXISTS MyFilms (idFilm INTEGER PRIMARY KEY AUTOINCREMENT, Title VARCHAR, Director VARCHAR, Year INTEGER, Rating INTEGER);"); db.execSQL("INSERT INTO MyFilms (Title) VALUES('" + Title + "');"); db.execSQL("INSERT INTO MyFilms (Director) VALUES('" + Director + "');"); db.close(); ``` Does this code gives me a table with columns `idfilm`, `Title`, `Director`, `Year`, `Rating`? If it's so, there's still a problem when I try to retrieve all the titles from `Title` column using this code:…

Accepted Answer

Your problem happens when you populate the database (your first code segment).

You need to combine the `INSERT` statements:

```
db.execSQL("INSERT INTO MyFilms (Title, Director, Year) VALUES('" 
    + title + "','" + director + "'," + year + ");");
```

Otherwise, you wind up with rows that contain a null value for `Title` or `Director`.

(Note: Don't use the SQL notation above, because it may leave you vulnerable to SQL injection. I only wrote it this way for the sake of simplicity. In production code, you should use parameters or sanitize your values. Don't become a victim of Little Bobby Tables).

EDIT

Here are some threads regarding prepared statements:

- How do I use prepared statements in SQlite in Android?

- Queries with prepared statements in Android?

How do I retrieve data from database using cursor? (Android, SQLite)

Solution

Problem

Related problems