StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSqlLiteDatabase
text
Body
copied!<p>Hi everyone I am trying to add the information to the database but I get an runtime error. I am pretty new to android and database. If someone can help it would be great as this is a homework and I have been stuck.</p> <pre><code>public class Home extends MenuActivity { static int counter = 0; String url; String date; String content; int countMe = 0; int i = 0; StringBuilder sb; List<String> str; List<WebPage> pages = new ArrayList<WebPage>(); long myDate; DatabaseHandler dh; @Override public void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.home); regularExpresionStrings(); dh = new DatabaseHandler(this); // dh.addWarrior(new Warrior(1, "Kenobi", "high", 50 , 30, "kenobi")); dh.addWebPage(pages); Cursor cursor = dh.getAllAsCursor(); String[] cols = new String[] {DatabaseHandler.url}; int[] to = new int[] {R.id.imageView2}; // sca = new SimpleCursorAdapter(this, R.layout.home, cursor, cols, to, 2); //new SimpleCursorAdapter(context, layout, c, from, to, flags) // mainListView.setAdapter(sca); } /** * When this method is called we try to find a file in the sandbox area that * matches the DDMMYY extracted from the picker. * * @param filename the name of the file (built using the DatePicker info) * @return the content of the file, or an error message if not found */ private String load() { try { Log.d("Home", " in the load method"); // final FileInputStream fis = openFileInput(filename); final InputStream fis = getResources().openRawResource(R.raw.pages); final BufferedReader reader = new BufferedReader(new InputStreamReader(fis)); StringBuilder sb = new StringBuilder(); String line = null; while ((line = reader.readLine()) != null) { sb.append(line); } reader.close(); fis.close(); return sb.toString(); } catch (Exception ex) { return "No entry exists for this file"; } } private String regularExpresionStrings() { Log.d("Home222", " in the regularExpresionStrings method"); String content = load(); String[] parts = content.split("</html>"); String pa = ""; SharedPreferences.Editor editor = getPreferences(MODE_PRIVATE).edit(); for(int i=0; i<parts.length; i++) { pa = new String(parts[i] + "</html>"); Log.d("Page" + (i + 1), pa); if(pa.contains("Address:")) { url = pa.substring(pa.indexOf("http"), pa.indexOf("html") + 4); Log.d("Address" + (i + 1), url); SharedPreferences prefs = PreferenceManager.getDefaultSharedPreferences(this); Editor ed = prefs.edit(); ed.putString("URL", url); ed.commit(); // getPreferences(MODE_PRIVATE).edit().putString("URL ", url).commit(); } if(pa.contains("Date:")) { date = pa.substring(pa.indexOf("Date:") + 5, pa.indexOf("Date:") + 18); myDate = Long.parseLong(date); Log.d("Date" + (i + 1), date); SharedPreferences prefs = PreferenceManager.getDefaultSharedPreferences(this); Editor ed = prefs.edit(); ed.putString("Date", myDate + ""); ed.commit(); // getPreferences(MODE_PRIVATE).edit().putString("Date ", myDate + "").commit(); } content = pa.substring(pa.indexOf("<!DOCTYPE html>"), pa.indexOf("</html>")) + "</html>"; Log.d("Content" + (i + 1), content); SharedPreferences prefs = PreferenceManager.getDefaultSharedPreferences(this); Editor ed = prefs.edit(); ed.putString("Content", content); ed.commit(); // getPreferences(MODE_PRIVATE).edit().putString("Content ", content).commit(); WebPage webPages = new WebPage(i++, url, content, myDate); pages.add(webPages); } Log.d("WebOff","File successfully imported"); return content; } } public class WebPage { /** * id represent the id of the table. */ private int id; /** * url represent the url of the web page. */ private String url; /** * source represent the source of the web page. */ private String source; /** * date represent a date */ private long date; public WebPage() { } /** * WebPage() creates a web page object * @param url set the current url with the passed one. * @param source set the current source with the passed one. * @param date set the current date with the passed one. */ public WebPage(int id, String url, String source, long date) { this.url = url; this.source = source; this.date = date; Log.d("WebOff", "WebPage object created"); } /** * @return the id */ public int getId() { return id; } // /** // * @param id the id to set // */ public void setId(int id) { this.id = id; } /** * getUrl() gets the url of the web page. * @return the url */ public String getUrl() { return url; } /** * setUrl() sets the url of the web page. * @param url to set */ public void setUrl(String url) { this.url = url; } /** * getSource() gets the source fo the web page. * @return the source */ public String getSource() { return source; } /** * setSource() sets the source of the web page. * @param source to set */ public void setSource(String source) { this.source = source; } /** * getDate() gets the date. * @return the date */ public long getDate() { return date; } /** * setDate() sets the date. * @param date to set */ public void setDate(long date) { this.date = date; } public void webPage() { Log.d("Debug","" + this.url); Log.d("Debug", this.source); Log.d("Debug", this.date + ""); } } public class DatabaseHandler extends SQLiteOpenHelper { /** * The name of the database */ private static final String DATABASE_NAME = "WebOff.db"; /** * The name of the (only) table. */ private static final String TABLE_NAME = "pages"; /** * The name of the first column (ID) */ static final int _id = 0; /** * The name of the second column (url) */ static final String url = "url"; /** * The name of the third column (source) */ static final String source = "source"; static final int date = 0; // attack /** * A constructor which builds a DatabaseHandler object. Note that calling * the constructor does not create a database. This does not happen until * the first call to getReadableDatabase() or getWriteableDatabase() * * @param context * In this case, a reference to WarriorActivity */ public DatabaseHandler(Context context) { super(context, DATABASE_NAME, null, 1); } /** * This method is called when the database is created for the first time. * This is where the creation of tables and the initial population of the * tables should happen. */ @Override public void onCreate(SQLiteDatabase db) { String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_NAME + "(" + _id + " INTEGER PRIMARY KEY," + url + " URL," + source + " Source, " + date + " Date" + ")"; db.execSQL(CREATE_CONTACTS_TABLE); Log.d("WebOff", "Database created successful"); } /** * Called when the database needs to be upgraded. Only relevant when you * have multiple versions of the database scheme in play. * */ @Override public void onUpgrade(SQLiteDatabase db, int oldNum, int newNum) { // Drop older table if exist and create fresh db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME); onCreate(db); } /** * Use this method to add a Warrior to the database. * * @param Warrior * the Warrior you want to add */ public void addWebPage(List<WebPage> webPage) { SQLiteDatabase db = this.getWritableDatabase(); ContentValues values = new ContentValues(); ByteArrayOutputStream out = new ByteArrayOutputStream(); // for(int i=0; i<webPage.size(); i++) // { // // Iterate through the list, adding the information stored within the WebPage object to the databese. // values.put("text", webPage.indexOf(i)); // } // values.put(_id + "", webPage.getId()); // values.put(url, webPage.getUrl()); // values.put(source, webPage.getSource()); // values.put(date +"", webPage.getDate() +""); db.insert(TABLE_NAME, null, values); db.close(); Log.d("WebOff", "Database populated"); } /** * Use this method to get all of the Warriors in the database. * * @return a list of Warrior objects, one per row */ public List<WebPage> getAll() { List<WebPage> list = new ArrayList<WebPage>(); String selectQuery = "SELECT * FROM " + TABLE_NAME; SQLiteDatabase db = this.getReadableDatabase(); Cursor cursor = db.rawQuery(selectQuery, null); if (cursor.moveToFirst()) { do { WebPage webPage = new WebPage(cursor.getInt(0), cursor.getString(1), cursor.getString(2), cursor.getInt(3)); list.add(webPage); } while (cursor.moveToNext()); } cursor.close(); db.close(); return list; } /** * Use this method to get a Cursor object that points at all the Warriors in * the database * * @return a Cursor object pointing at all Warriors in db */ public Cursor getAllAsCursor() { String selectQuery = "SELECT * FROM " + TABLE_NAME; SQLiteDatabase db = this.getReadableDatabase(); Cursor cursor = db.rawQuery(selectQuery, null); //db.close(); return cursor; } /** * Use this method to remove all of the Warriors from the database. This is * useful when experimenting. After dropping all tables, the initial state * of the database is re-created. */ public void removeAll() { SQLiteDatabase db = this.getWritableDatabase(); db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME); onCreate(db); } /** * This method removes one WebPage from the database. * * @param WebPage * the WebPage to remove */ public void deleteWebPage(WebPage webPage) { SQLiteDatabase db = this.getWritableDatabase(); db.delete(TABLE_NAME, _id + " = ?", new String[] { String.valueOf(webPage.getId()) }); db.close(); } /** * This method updates the data stored in the database for one Warrior. * * @param Warrior * the Warrior to update * @return the number of rows affected */ public int updateWebPage(WebPage webPage) { SQLiteDatabase db = this.getWritableDatabase(); ContentValues values = new ContentValues(); values.put(_id + "", webPage.getId()); values.put(url, webPage.getUrl()); values.put(source, webPage.getSource()); values.put(date + "", webPage.getDate()); return db.update(TABLE_NAME, values, _id + " = ?", new String[] { String.valueOf(webPage.getId()) }); } /** * This method gets a single WebPage from the database, using the ID field * as a key * * @param id * the ID of the WebPage we want * @return a Warrior object */ public WebPage getWebPage(int id) { SQLiteDatabase db = this.getReadableDatabase(); Cursor cursor = db.query(TABLE_NAME, new String[] { _id + "", url, source, date + "" }, _id + "=?", new String[] { String.valueOf(id) }, null, null, null, null); if (cursor != null) { cursor.moveToFirst(); } WebPage webPage = new WebPage(Integer.parseInt(null, cursor.getInt(0)), cursor.getString(1), cursor.getString(2), cursor.getInt(3)); db.close(); cursor.close(); return webPage; } public WebPage getWebPageByName(String name){ String selectQuery = "SELECT * FROM " + TABLE_NAME + " WHERE " + _id + " = '" + name + "'"; Log.d("Debug WebPage name: ", selectQuery); SQLiteDatabase db = this.getReadableDatabase(); Cursor cursor = db.rawQuery(selectQuery, null); WebPage webPage = null; if(cursor.moveToFirst()){ webPage = new WebPage(cursor.getInt(0), cursor.getString(1), cursor.getString(2), cursor.getInt(3)); } db.close(); cursor.close(); return webPage; } } </code></pre> <p>when I run it i get this: </p> <pre><code>01-05 17:24:19.594: E/Database(1524): Failure 1 (near "0": syntax error) on 0x2465e0 when preparing 'CREATE TABLE pages(0 INTEGER PRIMARY KEY,url URL,source Source, 0 Date)'. 01-05 17:24:19.699: E/AndroidRuntime(1524): java.lang.RuntimeException: Unable to start activity ComponentInfo{uk.ac.tees.L1087591/uk.ac.tees.L1087591.Home}: android.database.sqlite.SQLiteException: near "0": syntax error: CREATE TABLE pages(0 INTEGER PRIMARY KEY,url URL,source Source, 0 Date) 01-05 17:24:19.699: E/AndroidRuntime(1524): Caused by: android.database.sqlite.SQLiteException: near "0": syntax error: CREATE TABLE pages(0 INTEGER PRIMARY KEY,url URL,source Source, 0 Date) 01-05 17:24:19.699: E/AndroidRuntime(1524): at android.database.sqlite.SQLiteDatabase.native_execSQL(Native Method) 01-05 17:24:19.699: E/AndroidRuntime(1524): at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1727) 01-05 17:24:19.699: E/AndroidRuntime(1524): at uk.ac.tees.L1087591.DatabaseHandler.onCreate(DatabaseHandler.java:76) 01-05 17:24:19.699: E/AndroidRuntime(1524): at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:106) 01-05 17:24:19.699: E/AndroidRuntime(1524): at uk.ac.tees.L1087591.DatabaseHandler.addWebPage(DatabaseHandler.java:101) 01-05 17:24:19.699: E/AndroidRuntime(1524): at uk.ac.tees.L1087591.Home.onCreate(Home.java:52) 01-05 17:24:19.699: E/AndroidRuntime(1524): at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1047) 01-05 17:24:19.699: E/AndroidRuntime(1524): at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2627) 01-05 17:24:19.699: E/AndroidRuntime(1524): ... 11 more </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload