An easy way to double check your SQLlite syntax

While in the Android Developer Course, I am finding a little problem with using SQLlite. In Android Studio, when writing Java, Android Studio knows when the code you typed has an error, and it underlines it in red, showing you something you should fix.

However, when you are using SQLlite in your java code. Android Studio has no idea what you are talking about. This causes me a bit of heartburn as I run my apps only to find that something is wrong with my SQL code, but no idea of what to fix.

Thus enter https://sqliteonline.com/ ! I can copy and paste syntax into the web gui and give it a quick run to see if it even works. It has been most helpful to say the least. If you are just starting out with SQLlite, I highly recommend it over other web gui interfaces for testing, because it has a simple display on the left side, showing you all of your tables, views, indexes, and triggers. For a simpleton like me, that is really helpful to have a visual representation.

Linux – keep it simple.

Using SQLlite in your Android app

So, as part of my Android Developer Course by Rob Percival (which is excellent, and I highly recommend every computer/Android geek take), he showed us how to make use of SQLlite in our Android app. In the demonstration and challenge, he was using static inputs, like this:
// And add some information to that table.
firstDatabaseEver.execSQL(“INSERT INTO events (name, year) VALUES (‘Birth’, 1985)”);

Which is great. He was just showing us how SQLlite worked. With that said, in a really useful app, I figured I would need a way to take a user’s input and put it into the table, so I fiddled around a bit, and here is what I came up with.

MainActivity.java:
/* Copyright 2017 by AlaskaLinuxUser (https://thealaskalinuxuser.wordpress.com)
*
* Licensed under the Apache License, Version 2.0 (the “License”);
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an “AS IS” BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package com.alaskalinuxuser.sqllitedemo;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.util.Log;

public class MainActivity extends AppCompatActivity {

@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);

// Let’s make some variables to use later.
String nameVar = “Joined the Navy”;
int yearVar = 2004;

try {

// First, make a database, open or create will open if exist, create if not.
SQLiteDatabase firstDatabaseEver = this.openOrCreateDatabase(“events”, MODE_PRIVATE, null);

// Now, let’s ceate a table.
firstDatabaseEver.execSQL(“CREATE TABLE IF NOT EXISTS events (name VARCHAR, year INT(4))”);

// And add some information to that table.
firstDatabaseEver.execSQL(“INSERT INTO events (name, year) VALUES (‘Birth’, 1985)”);

// And a second one for fun, this time, we will use our variable we created, you can see
// how this would be useful for an app, so you could have the user’s input dumped into a
// table.
firstDatabaseEver.execSQL(“INSERT INTO events (name, year) VALUES (‘” + nameVar + “‘, ” + yearVar + “)”);

// Now we need a cursor to retreive the data with a query.
Cursor myCursor = firstDatabaseEver.rawQuery(“SELECT * FROM events”, null);

// Now that we have the data, we need a way to index them, or grab the parts we need.
int nameIndex = myCursor.getColumnIndex(“name”);
int yearIndex = myCursor.getColumnIndex(“year”);

// Let’s start from the top of the table, by moving to the first position.
myCursor.moveToFirst();

// And if it is not null, let’s use it.
while (myCursor != null){

// Logging for posterity….
Log.i(“WJH”, myCursor.getString(nameIndex));
Log.i(“WJH”, Integer.toString(myCursor.getInt(yearIndex)));

// And move on to the next result in the table.
myCursor.moveToNext();
}
} catch (Exception e) {

e.printStackTrace();

}
}
}

Note that I don’t actually have user’s input into the table here, but I did use variables to input the data into the table, like so:

// Let’s make some variables to use later.
String nameVar = “Joined the Navy”;
int yearVar = 2004;

// And a second one for fun, this time, we will use our variable we created, you can see
// how this would be useful for an app, so you could have the user’s input dumped into a
// table.
firstDatabaseEver.execSQL(“INSERT INTO events (name, year) VALUES (‘” + nameVar + “‘, ” + yearVar + “)”);

As we can see, the input into the table of “events” is based on the output of the variables “nameVar and yearVar, which I previously defined. It is really easy to see that we could use something like getText from an editText field and input those variables into the tables. Which I think is the best use of it.

Linux – keep it simple.