sqlite

Flutter, sqflite, and escaping quotes with SQL INSERT and UPDATE statements

When you want to do a SQL INSERT or UPDATE using the Flutter sqflite package, I’ve found that it’s best to use their insert and update methods (as shown below) so you don’t have to worry about escaping single- and double-quotes. This is similar to the reason why Java developers switched from Statement to PreparedStatement way back in the late 1900s.

As an example, given this SQLite database table:

Using a SQLite date/time field with Flutter and Dart

As a brief note, SQLite doesn’t have date/time (datetime) fields, so when you want to use a datetime field with Flutter (and Dart), you have to do something else.

My choice was to store my datetime data as a SQLite INTEGER field. I did this so I can query and sort that field easily myself. (The benefit of storing a datetime field as a TEXT field is that you can read it easier, but I think an INTEGER field is a little easier to work with, though YMMV.)

A simple way to populate an Android ListView with a SQLite database Cursor

Here’s an example of how to populate an Android ListView, where I get the data for the ListView from a database Cursor:

DatabaseHelper.TeamsCursor tc = DatabaseManager.get(getActivity()).getAllTeams();
ArrayList<String> listOfTeamNames = new ArrayList<>();
for(tc.moveToFirst(); !tc.isAfterLast(); tc.moveToNext()) {
    listOfTeamNames.add(tc.getTeam().teamName);
}

// list the team names with an adapter that talks to our listview
TeamNamesAdapter adapter = new TeamNamesAdapter(listOfTeamNames);
setListAdapter(adapter);

There are more formal ways to create an adapter class to work with a Cursor, but for my needs I just needed to get a list of names from a SQLite database table and show them in a simple ListView — part of a ListFragment — and this was the simplest code to write.

FWIW, the example also shows one way to iterate over the elements in a Cursor using a Java for loop.

Android SQLiteOpenHelper example source code

Here’s an Android SQLite class I use in an Android app I wrote in 2014-2015. I’m sharing it here so I can easily find an Android SQLiteOpenHelper example:

SQLite: Default a datetime field to the current time (now)

SQLite FAQ: How do I default a SQLite datetime field to the current date and time? (i.e., how do I default it to now?)

Just use the SQLite current_timestamp function, like this:

last_updated datetime default current_timestamp

In a more complete create table example I just used on an Android project, this looks like this:

SQLite alter table syntax examples

SQLite FAQ: Can you show me how the SQLite ALTER TABLE syntax works?

At the time of this writing you can use the SQLite ALTER TABLE syntax for two purposes:

  1. Add a column to the end of an existing SQLite database table
  2. Change the name of a database table.

For other changes you'll have to follow some workaround procedures (discussed below).