Could someone please help me spot the bug here? Mainly, I can never
seem to find a value once it has been stored in the database (I am
100% positive that the value is there). I can store it the first time,
but the cursor in my findVenue() method (see below) never has any
data. :-(
If I do not try to use replaced ? marks, and instead write a raw
string that is then used as my query, it works perfectly. It does not
work with prepared statements. I would really like to use the ? marks
to help avoid SQL security issues (and I am frustrated that they do
not work). Also, I would like to be able to see the text of my SQL
query somewhere before I execute it, if that is possible.
Thanks,
Ham
PS - I am tired, so if I am leaving out some required info, let me
know
package org.blah.database;
import java.util.ArrayList;
import java.util.Iterator;
import org.blah.HTMLoader;
import org.blah.Venue;
import org.blah.database.Columns;
import org.blah.database.OpenHelper;
import org.blah.database.Tables;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteConstraintException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;
public class InsertUniqueVenue {
private static final String tag = HTMLoader.tag;
private static final String pre = "InsertUniqueVenue: ";
private static SQLiteDatabase database_ = null;
public InsertUniqueVenue(Context c) {
if (database_ == null) {
OpenHelper oh = OpenHelper.getOpenHelper(c);
database_ = oh.getWritableDatabase();
}
}
public long insertOrFindVenue(Venue v) {
ContentValues values = new ContentValues();
values.put(Columns.V_NAME, v.getName());
values.put(Columns.V_STREET, v.getStreet());
values.put(Columns.V_CITY, v.getCity());
values.put(Columns.V_STATE, v.getState());
values.put(Columns.V_ZIP, v.getZip());
long rowid;
try {
rowid = database_.insertOrThrow(Tables.VENUES, null,
values);
Log.v(tag, pre + "Venue inserted - id " + rowid);
} catch (SQLiteConstraintException e) {
Log.w(tag, pre + "Venue insert failed, already exists");
Log.w(tag, pre + "Venue:");
Log.w(tag, pre + v.toString());
Log.w(tag, pre + "Attempting to find");
rowid = findVenue(v);
}
if (rowid == -1) {
Log.e(tag, pre + "Venue not inserted or found.");
Log.e(tag, pre + "Something went badly");
}
return rowid;
}
/**
* Checks if a venue exists. Does not use the Location, or the rowId,
as
* this should never be called with a venue that we already know
exists.
* This is mainly used for loading the database with new venues
*
* @param v
* @return the id if the venue exists in the database, or -1
otherwise
*/
private long findVenue(Venue v) {
StringBuffer sql = new StringBuffer("SELECT " +
Columns.COLUMN_ID
+ " FROM " + Tables.VENUES + " WHERE ?=?");
ArrayList<String> values = new ArrayList<String>();
values.add(Columns.V_NAME);
values.add(v.getName());
if (v.getStreet() != "") {
sql.append(" AND ?=?");
values.add(Columns.V_STREET);
values.add(v.getStreet());
}
if (v.getCity() != "") {
sql.append(" AND ?=?");
values.add(Columns.V_CITY);
values.add(v.getCity());
}
if (v.getState() != "") {
sql.append(" AND ?=?");
values.add(Columns.V_STATE);
values.add(v.getState());
}
if (v.getZip() != "") {
sql.append(" AND ?=?");
values.add(Columns.V_ZIP);
values.add(v.getZip());
}
String[] stringValues = new String[values.size()];
Iterator<String> it = values.iterator();
int i = 0;
while (it.hasNext())
stringValues[i++] = it.next();
Cursor cursor = database_.rawQuery(sql.toString(),
stringValues);
if (cursor.getCount() != 0)
return cursor.getLong(cursor
.getColumnIndexOrThrow(Columns.COLUMN_ID));
Log.e(tag, pre + "Unable to find Venue in table.");
Log.e(tag, pre + "Returning -1 for Venue ID");
return -1;
}
}
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Android Developers" group.
To post to this group, send email to android-developers@googlegroups.com
To unsubscribe from this group, send email to
android-developers-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/android-developers?hl=en
-~----------~----~----~----~------~----~------~--~---
