I am writing an app that is pulling info from an SQLite DB and passing it to a list. The code is not finalized, but I am unable to test past a certain point . The error message I am getting is that my table name is a "null" in my SQL Query, but I dont understand why that is happening. I have created a textview within the App that displays the data contained in the String I am passing in. That TextView is showing the correct information. So if the string is working to pass into a TextView, why isnt it working to pass it into a String based SQLQuery?
Specifically, the getBeerDrinkLabels() from the DBHelper is showing "null" for barTableName, which comes from Bar.testingBarSet, which comes from MainActivity.upperCaseName. Bar.testingBarSet shows the correct value in the String that loads into the setBarTester TextView, so why not in the DB Query?
DBHelper Class (Where the SQL Query lives within the public List getBeerDrinkLabels() method)
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
public class DBHelper extends SQLiteOpenHelper {
private static String DB_PATH = "/data/data/com.example.sixth/databases/";
private static String DB_NAME = "BarSample.db";
private final Context myContext;
public static String tableName = "BARS";
public static String barTableName = Bar.testingBarSet;//Pass in the specific bar from the spinner choice
public static String drinkType = Bar.setDrinkType;//Use this to pass in selected drink type to SQL query
//public static final String KEY_ROWID = "_id";
//public static final String BARNAME = "Bar Name";
public static final String BARCITY = "Bar City";
private SQLiteDatabase myDataBase;
public DBHelper(Context context) {
super(context, DB_NAME, null, 1);
this.myContext = context;
}
/**
* Creates a empty database on the system and rewrites it with your own
* database.
*/
public void createDataBase() throws IOException {
boolean dbExist = checkDataBase();
if (dbExist) {
// do nothing - database already exist
} else {
// By calling this method and empty database will be created into
// the default system path
// of your application so we are gonna be able to overwrite that
// database with our database.
this.getReadableDatabase();
try {
this.close();
copyDataBase();
} catch (IOException e) {
throw new Error("Error copying database");
}
}
}
/**
* Check if the database already exist to avoid re-copying the file each
* time you open the application.
*
* @return true if it exists, false if it doesn't
*/
private boolean checkDataBase() {
SQLiteDatabase checkDB = null;
try {
String myPath = DB_PATH + DB_NAME;
checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
} catch (SQLiteException e) {
// database does't exist yet.
}
if (checkDB != null) {
checkDB.close();
}
return checkDB != null ? true : false;
}
/**
* Copies your database from your local assets-folder to the just created
* empty database in the system folder, from where it can be accessed and
* handled. This is done by transfering bytestream.
*/
private void copyDataBase() throws IOException {
// Open your local db as the input stream
InputStream myInput = myContext.getAssets().open(DB_NAME);
// Path to the just created empty db
String outFileName = DB_PATH + DB_NAME;
// Open the empty db as the output stream
OutputStream myOutput = new FileOutputStream(outFileName);
// transfer bytes from the inputfile to the outputfile
byte[] buffer = new byte[1024];
int length;
while ((length = myInput.read(buffer)) > 0) {
myOutput.write(buffer, 0, length);
}
// Close the streams
myOutput.flush();
myOutput.close();
myInput.close();
}
public void openDataBase() throws SQLException {
// Open the database
String myPath = DB_PATH + DB_NAME;
myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
}
@Override
public synchronized void close() {
if (myDataBase != null)
myDataBase.close();
super.close();
}
@Override
public void onCreate(SQLiteDatabase db) {
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
public List<String> getAllLabels(){
List<String> labels = new ArrayList<String>();
// Select All Query
String selectQuery = "SELECT * FROM " + tableName;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
labels.add(cursor.getString(1) + " " + cursor.getString(2)+ ", " + cursor.getString(3));
} while (cursor.moveToNext());
}
// closing connection
cursor.close();
db.close();
// returning labels
return labels;
} // will returns all labels stored in database
public List<String> getBeerDrinkLabels(){
List<String> allBeerDrinkLabels = new ArrayList<String>();
// Select All Query
String sqlquery="SELECT * FROM "+barTableName + " WHERE DRINKTYPE='Beer';";
String selectQuery = sqlquery;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
allBeerDrinkLabels.add(cursor.getString(1) + ", " + cursor.getString(2));
} while (cursor.moveToNext());
}
// closing connection
cursor.close();
db.close();
// returning labels
return allBeerDrinkLabels;
} // will returns all labels stored in database
}
Main Activity.java (where the String that eventually goes into the Query is created)
package com.example.sixth;
import java.io.IOException;
import java.util.List;
import android.app.Activity;
import android.content.Intent;
import android.database.SQLException;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemSelectedListener;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.Spinner;
public class MainActivity extends Activity implements
OnItemSelectedListener {
DBHelper myDB;
Button selectBar;
Spinner spinner;
static String pullBar,setBar,name, cityState, upperCaseName;
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
Button selectBar = (Button)findViewById(R.id.btnSelectBar);
myDB = new DBHelper(this);
// Spinner element
spinner = (Spinner) findViewById(R.id.spinner);
// Spinner click listener
spinner.setOnItemSelectedListener(this);
try {
myDB.createDataBase();
} catch (IOException ioe) {
throw new Error("Unable to create database");
}
try {
myDB.openDataBase();
} catch (SQLException sqle) {
throw sqle;
}
// Loading spinner data from database
loadSpinnerData();
selectBar.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
pullBar = String.valueOf(spinner.getSelectedItem());
String[] parts = pullBar.split(" "); //returns an array with the 2 parts
name = parts[0];
String nlast = parts[parts.length-2];
String last = parts[parts.length-1];
cityState = nlast+ " " + last;
upperCaseName=name.toUpperCase();
setBar = name.toLowerCase();
Intent i = (new Intent(MainActivity.this, Bar.class));
startActivity(i);
}
});
}
private void loadSpinnerData() {
// database handler
DBHelper db = new DBHelper(getApplicationContext());
// Spinner Drop down elements
List<String> lables = db.getAllLabels();
// Creating adapter for spinner
ArrayAdapter<String> dataAdapter = new ArrayAdapter<String>(this,
android.R.layout.simple_spinner_item, lables);
// Drop down layout style - list view with radio button
dataAdapter
.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
// attaching data adapter to spinner
spinner.setAdapter(dataAdapter);
}
@Override
public boolean onCreateOptionsMenu(Menu menu) {
// Inflate the menu; this adds items to the action bar if it is present.
getMenuInflater().inflate(R.menu.main, menu);
return true;
}
@Override
public boolean onOptionsItemSelected(MenuItem item) {
// Handle action bar item clicks here. The action bar will
// automatically handle clicks on the Home/Up button, so long
// as you specify a parent activity in AndroidManifest.xml.
int id = item.getItemId();
if (id == R.id.action_settings) {
return true;
}
return super.onOptionsItemSelected(item);
}
@Override
public void onItemSelected(AdapterView<?> parent, View view, int position, long id) {
// TODO Auto-generated method stub
}
@Override
public void onNothingSelected(AdapterView<?> parent) {
// TODO Auto-generated method stub
}
}
Bar.java (Where the string going in to the Query is finalized)
public class Bar extends Activity{
String setBarTest = MainActivity.setBar;
static String testingBarSet = MainActivity.upperCaseName;
String barNameHolder, picHolder, barContactHolder, barPhoneHolder;
int imageInt, textInt1,textInt2, textInt3, textInt4;
TextView setBarName, setBarContact,setBarPhone, setBarHours;
static TextView setBarTester;
ImageView barPic;
Button viewAll, beer, wine, mixedDrinks, other, specials, getTaxi;
static String setDrinkType;
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_bar);
Button viewAll = (Button)findViewById(R.id.btnviewAll);
Button specials = (Button)findViewById(R.id.btnSpecials);
Button getTaxi = (Button)findViewById(R.id.btnTaxi);
barPic = (ImageView) findViewById(R.id.barPic);
String picHolder = "drawable/"+setBarTest;
imageInt = getResources().getIdentifier(picHolder, null, getPackageName());
barPic.setImageResource(imageInt);
setBarName = (TextView)findViewById(R.id.barName);
String barNameHolder = "@string/"+setBarTest;
textInt1 = getResources().getIdentifier(barNameHolder, null, getPackageName());
setBarName.setText(textInt1);
setBarContact = (TextView)findViewById(R.id.barContact);
String barContactHolder = "@string/"+setBarTest+"Contact";
textInt2 = getResources().getIdentifier(barContactHolder, null, getPackageName());
setBarContact.setText(textInt2);
setBarPhone = (TextView)findViewById(R.id.barPhone);
String barPhoneHolder = "@string/"+setBarTest+"Phone";
textInt3 = getResources().getIdentifier(barPhoneHolder, null, getPackageName());
setBarPhone.setText(textInt3);
setBarHours = (TextView)findViewById(R.id.barHours);
String barHoursHolder = "@string/"+setBarTest+"Hours";
textInt4 = getResources().getIdentifier(barHoursHolder, null, getPackageName());
setBarHours.setText(textInt4);
setBarTester = (TextView)findViewById(R.id.setBarTester);
String barTesterHolder = testingBarSet ;
//int textInt5 = getResources().getIdentifier(barTesterHolder, null, getPackageName());
setBarTester.setText(barTesterHolder);
viewAll.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
Intent i = (new Intent(Bar.this, Drinks.class));
startActivity(i);
}
});
specials.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
Intent i = (new Intent(Bar.this, NoResult.class));
startActivity(i);
}
});
getTaxi.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
Intent i = (new Intent(Bar.this, Taxi.class));
startActivity(i);
}
});
}
}
Drinks.java (Where the DB call is made that is giving the Null value)
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import android.app.Activity;
import android.os.Bundle;
import android.widget.ArrayAdapter;
import android.widget.ExpandableListView;
import android.widget.ExpandableListView.OnChildClickListener;
import android.widget.ExpandableListView.OnGroupClickListener;
import android.widget.ExpandableListView.OnGroupCollapseListener;
import android.widget.ExpandableListView.OnGroupExpandListener;
import android.widget.ListView;
import android.widget.Toast;
public class Drinks extends Activity {
// TextView drinkHolder;
public static String drinkType = DBHelper.drinkType;
String drinkTestHolder = "";
ExpandableListAdapter listAdapter;
ExpandableListView expListView;
List<String> listDataHeader;
HashMap<String, List<String>> listDataChild;
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_drinks);
Bundle bundle = getIntent().getExtras();
if (bundle != null) {
drinkType = bundle.getString("sample_name");
}
// get the listview
expListView = (ExpandableListView) findViewById(R.id.lvExp);
// preparing list data
prepareListData();
listAdapter = new ExpandableListAdapter(this, listDataHeader, listDataChild);
// setting list adapter
expListView.setAdapter(listAdapter);
}
private void prepareListData() {
listDataHeader = new ArrayList<String>();
listDataChild = new HashMap<String, List<String>>();
// Adding child data
listDataHeader.add("Beer");
listDataHeader.add("Mixed Drinks");
listDataHeader.add("Other Drinks");
listDataHeader.add("Shots");
listDataHeader.add("Wine");
//Get Beer entries
DBHelper db = new DBHelper(getApplicationContext());
List<String> beerLables = db.getBeerDrinkLabels();
// Adding child data
List<String> beer = new ArrayList<String>();
//beer.add("The Shawshank Redemption");
for (int i = 0; i < beerLables.size(); i++) {
beer.add(beerLables.get(i));
}
/*
beer.add("The Godfather");
* beer.add("The Godfather: Part II"); beer.add("Pulp Fiction");
* beer.add("The Good, the Bad and the Ugly"); beer.add(
* "The Dark Knight"); beer.add("12 Angry Men");
*/
List<String> wine = new ArrayList<String>();
/*
* wine.add("The Conjuring"); wine.add("Despicable Me 2");
* wine.add("Turbo"); wine.add("Grown Ups 2"); wine.add("Red 2");
* wine.add("The Wolverine");
*/
List<String> shots = new ArrayList<String>();
/*
* shots.add("2 Guns"); shots.add("The Smurfs 2"); shots.add(
* "The Spectacular Now"); shots.add("The Canyons"); shots.add(
* "Europa Report");
*/
List<String> mixedDrinks = new ArrayList<String>();
/*
* mixedDrinks.add("2 Guns"); mixedDrinks.add("The Smurfs 2");
* mixedDrinks.add("The Spectacular Now"); mixedDrinks.add("The Canyons"
* ); mixedDrinks.add("Europa Report");
*/
List<String> otherDrinks = new ArrayList<String>();
/*
* otherDrinks.add("2 Guns"); otherDrinks.add("The Smurfs 2");
* otherDrinks.add("The Spectacular Now"); otherDrinks.add("The Canyons"
* ); otherDrinks.add("Europa Report");
*/
listDataChild.put(listDataHeader.get(0), beer); // Header, Child data
listDataChild.put(listDataHeader.get(1), wine);
listDataChild.put(listDataHeader.get(2), shots);
listDataChild.put(listDataHeader.get(3), mixedDrinks);
listDataChild.put(listDataHeader.get(4), otherDrinks);
}
}
I originally had the DBHelper pulling the String right from MainActivity, but was getting Null value no matter where I put it.
LogCat
FATAL EXCEPTION: main
java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.sixth/com.example.sixth.Drinks}: android.database.sqlite.SQLiteException: near "null": syntax error (code 1): , while compiling: SELECT * FROM null WHERE DRINKTYPE='Beer';
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2211)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2261)
at android.app.ActivityThread.access$600(ActivityThread.java:141)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1256)
at android.os.Handler.dispatchMessage(Handler.java:99)
at android.os.Looper.loop(Looper.java:137)
at android.app.ActivityThread.main(ActivityThread.java:5103)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:525)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:737)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553)
at dalvik.system.NativeStart.main(Native Method)
Caused by: android.database.sqlite.SQLiteException: near "null": syntax error (code 1): , while compiling: SELECT * FROM null WHERE DRINKTYPE='Beer';
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314)
at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1253)
at com.example.sixth.DBHelper.getBeerDrinkLabels(DBHelper.java:180)
at com.example.sixth.Drinks.prepareListData(Drinks.java:62)
at com.example.sixth.Drinks.onCreate(Drinks.java:38)
at android.app.Activity.performCreate(Activity.java:5133)
at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1087)
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2175)
