Creating tables in sqlite database on android

  @Override
         public void onCreate (SQLiteDatabase db)
         {
             try {
                 db.execSQL ("create table" + NotificationManager.getUserStatic (context) + "log (" +
                 KEY_TIME + "INTEGER primary key," + KEY_TEXT + "TEXT not null);");

             }
             catch (SQLException e) {
                 e.printStackTrace ();
             }
         }

         / **
          * onOpen method called when app is opening.
          * /

         @Override
         public void onOpen (SQLiteDatabase db)
         {
             try {
                 System.out.println ("tophere");
                 db.execSQL ("create table if not exists" + DATABASE_NAME + "." + NotificationManager.getUserStatic (context) + "log (" +
                 KEY_TIME + "INTEGER primary key," + KEY_TEXT + "TEXT not null);");
                 System.out.println ("downhere");
             }
             catch (SQLException e) {
                 e.printStackTrace ();
             }
         }

We have this code to create a database for the application. the problem is that, as far as we can tell, it does not create tables, so when we try to insert them into tables and read them, it leads to crashes. we tried everything System.out.println to see where it does not work. we get the top, but he never gets to the bottom of the magazine, so we guess that something makes him fail. we checked with DDMS that the database is in the correct folder, so the database should be there, but for some reason it cannot find it (lines 2 and 3 in the log).

Any thoughts?

This is the error log:

  04-27 10: 45: 46.768: I / System.out (6441): tophere
 04-27 10: 45: 46.772: I / SqliteDatabaseCpp (6441): sqlite returned: error code = 1, msg = unknown database NOPO, db = / data / data / dmri.nopo / databases / NOPO
 04-27 10: 45: 46.772: W / System.err (6441): android.database.sqlite.SQLiteException: unknown database NOPO:, while compiling: create table if not exists NOPO.log (time INTEGER primary key, text TEXT not null);
 04-27 10: 45: 46.792: W / System.err (6441): at android.database.sqlite.SQLiteCompiledSql.native_compile (Native Method)
 04-27 10: 45: 46.792: W / System.err (6441): at android.database.sqlite.SQLiteCompiledSql. (SQLiteCompiledSql.java:68)
 04-27 10: 45: 46.811: W / System.err (6441): at android.database.sqlite.SQLiteProgram.compileSql (SQLiteProgram.java:134)
 04-27 10: 45: 46.811: W / System.err (6441): at android.database.sqlite.SQLiteProgram.compileAndbindAllArgs (SQLiteProgram.javahaps61)
 04-27 10: 45: 46.811: W / System.err (6441): at android.database.sqlite.SQLiteStatement.acquireAndLock (SQLiteStatement.java:260)
 04-27 10: 45: 46.811: W / System.err (6441): at android.database.sqlite.SQLiteStatement.executeUpdateDelete (SQLiteStatement.java:84)
 04-27 10: 45: 46.811: W / System.err (6441): at android.database.sqlite.SQLiteDatabase.executeSql (SQLiteDatabase.java:1899)
 04-27 10: 45: 46.823: W / System.err (6441): at android.database.sqlite.SQLiteDatabase.execSQL (SQLiteDatabase.java:1839)
 04-27 10: 45: 46.823: W / System.err (6441): at dmri.nopo.DBAdapter $ DatabaseHelper.onOpen (DBAdapter.java:67)
 04-27 10: 45: 46.823: W / System.err (6441): at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase (SQLiteOpenHelper.java:180)
 04-27 10: 45: 46.902: W / System.err (6441): at dmri.nopo.DBAdapter.open (DBAdapter.java:86)
 04-27 10: 45: 46.912: W / System.err (6441): at dmri.nopo.LogManager.readLogFile (LogManager.java:32)
 04-27 10: 45: 46.912: W / System.err (6441): at dmri.nopo.LogActivity.onCreate (LogActivity.java:25)
 04-27 10: 45: 46.921: W / System.err (6441): at android.app.Activity.performCreate (Activity.java:4465)
 04-27 10: 45: 46.921: W / System.err (6441): at android.app.Instrumentation.callActivityOnCreate (Instrumentation.java:1049)
 04-27 10: 45: 46.931: W / System.err (6441): at android.app.ActivityThread.performLaunchActivity (ActivityThread.java:1920)
 04-27 10: 45: 46.931: W / System.err (6441): at android.app.ActivityThread.handleLaunchActivity (ActivityThread.java:1981)
 04-27 10: 45: 46.941: W / System.err (6441): at android.app.ActivityThread.access $ 600 (ActivityThread.java:123)
 04-27 10: 45: 46.941: W / System.err (6441): at android.app.ActivityThread $ H.handleMessage (ActivityThread.java:1147)
 04-27 10: 45: 46.953: W / System.err (6441): at android.os.Handler.dispatchMessage (Handler.java:99)
 04-27 10: 45: 46.953: W / System.err (6441): at android.os.Looper.loop (Looper.java:137)
 04-27 10: 45: 46.972: W / System.err (6441): at android.app.ActivityThread.main (ActivityThread.java:4424)
 04-27 10: 45: 46.972: W / System.err (6441): at java.lang.reflect.Method.invokeNative (Native Method)
 04-27 10: 45: 46.972: W / System.err (6441): at java.lang.reflect.Method.invoke (Method.java∗11)
 04-27 10: 45: 46.982: W / System.err (6441): at com.android.internal.os.ZygoteInit $ MethodAndArgsCaller.run (ZygoteInit.java:784)
 04-27 10: 45: 46.982: W / System.err (6441): at com.android.internal.os.ZygoteInit.main (ZygoteInit.java►51)
 04-27 10: 45: 46.992: W / System.err (6441): at dalvik.system.NativeStart.main (Native Method)
 04-27 10: 45: 47.002: I / SqliteDatabaseCpp (6441): sqlite returned: error code = 1, msg = no such table: log, db = / data / data / dmri.nopo / databases / NOPO
 04-27 10: 45: 47.012: D / AndroidRuntime (6441): Shutting down VM
 04-27 10: 45: 47.012: W / dalvikvm (6441): threadid = 1: thread exiting with uncaught exception (group = 0x409c01f8)
 04-27 10: 45: 47.181: E / AndroidRuntime (6441): FATAL EXCEPTION: main
 04-27 10: 45: 47.181: E / AndroidRuntime (6441): java.lang.RuntimeException: Unable to start activity ComponentInfo {dmri.nopo / dmri.nopo.LogActivity}: android.database.sqlite.SQLiteException: no such table : log:, while compiling: SELECT time, text FROM log
 04-27 10: 45: 47.181: E / AndroidRuntime (6441): at android.app.ActivityThread.performLaunchActivity (ActivityThread.java:1956)
 04-27 10: 45: 47.181: E / AndroidRuntime (6441): at android.app.ActivityThread.handleLaunchActivity (ActivityThread.java:1981)
 04-27 10: 45: 47.181: E / AndroidRuntime (6441): at android.app.ActivityThread.access $ 600 (ActivityThread.java:123)
 04-27 10: 45: 47.181: E / AndroidRuntime (6441): at android.app.ActivityThread $ H.handleMessage (ActivityThread.java:1147)
 04-27 10: 45: 47.181: E / AndroidRuntime (6441): at android.os.Handler.dispatchMessage (Handler.java:99)
 04-27 10: 45: 47.181: E / AndroidRuntime (6441): at android.os.Looper.loop (Looper.java:137)
 04-27 10: 45: 47.181: E / AndroidRuntime (6441): at android.app.ActivityThread.main (ActivityThread.java:4424)
 04-27 10: 45: 47.181: E / AndroidRuntime (6441): at java.lang.reflect.Method.invokeNative (Native Method)
 04-27 10: 45: 47.181: E / AndroidRuntime (6441): at java.lang.reflect.Method.invoke (Method.java∗11)
 04-27 10: 45: 47.181: E / AndroidRuntime (6441): at com.android.internal.os.ZygoteInit $ MethodAndArgsCaller.run (ZygoteInit.java:784)
 04-27 10: 45: 47.181: E / AndroidRuntime (6441): at com.android.internal.os.ZygoteInit.main (ZygoteInit.java►51)
 04-27 10: 45: 47.181: E / AndroidRuntime (6441): at dalvik.system.NativeStart.main (Native Method)
 04-27 10: 45: 47.181: E / AndroidRuntime (6441): Caused by: android.database.sqlite.SQLiteException: no such table: log:, while compiling: SELECT time, text FROM log
 04-27 10: 45: 47.181: E / AndroidRuntime (6441): at android.database.sqlite.SQLiteCompiledSql.native_compile (Native Method)
 04-27 10: 45: 47.181: E / AndroidRuntime (6441): at android.database.sqlite.SQLiteCompiledSql. (SQLiteCompiledSql.java:68)
 04-27 10: 45: 47.181: E / AndroidRuntime (6441): at android.database.sqlite.SQLiteProgram.compileSql (SQLiteProgram.java:143)
 04-27 10: 45: 47.181: E / AndroidRuntime (6441): at android.database.sqlite.SQLiteProgram.compileAndbindAllArgs (SQLiteProgram.javahaps61)
 04-27 10: 45: 47.181: E / AndroidRuntime (6441): at android.database.sqlite.SQLiteProgram. (SQLiteProgram.java:127)
 04-27 10: 45: 47.181: E / AndroidRuntime (6441): at android.database.sqlite.SQLiteProgram. (SQLiteProgram.java:94)
 04-27 10: 45: 47.181: E / AndroidRuntime (6441): at android.database.sqlite.SQLiteQuery. (SQLiteQuery.java:53)
 04-27 10: 45: 47.181: E / AndroidRuntime (6441): at android.database.sqlite.SQLiteDirectCursorDriver.query (SQLiteDirectCursorDriver.java:47)
 04-27 10: 45: 47.181: E / AndroidRuntime (6441): at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory (SQLiteDatabase.java:1564)
 04-27 10: 45: 47.181: E / AndroidRuntime (6441): at android.database.sqlite.SQLiteDatabase.queryWithFactory (SQLiteDatabase.java:1449)
 04-27 10: 45: 47.181: E / AndroidRuntime (6441): at android.database.sqlite.SQLiteDatabase.query (SQLiteDatabase.java:1405)
 04-27 10: 45: 47.181: E / AndroidRuntime (6441): at android.database.sqlite.SQLiteDatabase.query (SQLiteDatabase.java:1485)
 04-27 10: 45: 47.181: E / AndroidRuntime (6441): at dmri.nopo.DBAdapter.getAllSMS (DBAdapter.java:116)
 04-27 10: 45: 47.181: E / AndroidRuntime (6441): at dmri.nopo.LogManager.readLogFile (LogManager.java:34)
 04-27 10: 45: 47.181: E / AndroidRuntime (6441): at dmri.nopo.LogActivity.onCreate (LogActivity.java:25)
 04-27 10: 45: 47.181: E / AndroidRuntime (6441): at android.app.Activity.performCreate (Activity.java:4465)
 04-27 10: 45: 47.181: E / AndroidRuntime (6441): at android.app.Instrumentation.callActivityOnCreate (Instrumentation.java:1049)
 04-27 10: 45: 47.181: E / AndroidRuntime (6441): at android.app.ActivityThread.performLaunchActivity (ActivityThread.java:1920)
 04-27 10: 45: 47.181: E / AndroidRuntime (6441): ... 11 more
+6
source share
5 answers

First of all, I would recommend using android.util.Log to register exceptions in Android.

Secondly - I suspect that you have tables with incorrect names. Your error says that the query cannot find the "log", but I see that you are doing some concatenation in the "CREATE" statement. This may be the reason.

You can check what is actually created for you. Having looked through the created sqlite base.

You may try:

  • adb shell
  • cd /data/data/<your.package.name>/databases
  • sqlite3 <yourdbname>
  • .tables
+4
source

it will help you, I tried it and its work

this is just an example

 public class DatabaseMarks { public static final String KEY_STUID = "stuid"; public static final String KEY_SUB1 = "subject_one"; public static final String KEY_SUB2 = "subject_two"; public static final String KEY_SUB3= "subject_three"; public static final String KEY_MARKS1= "marks_one"; public static final String KEY_MARKS2 = "marks_two"; public static final String KEY_MARKS3 = "marks_three"; private static final String DATABASE_NAME = "Student"; private static final String DATABASE_MARKSTABLE = "StudentMarks"; private static final int DATABASE_VERSION = 1; private DbHelper ourHelper; private final Context ourContext; private SQLiteDatabase ourDatabase; private static class DbHelper extends SQLiteOpenHelper{ public DbHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); // TODO Auto-generated constructor stub } @Override public void onCreate(SQLiteDatabase db) { // TODO Auto-generated method stub db.execSQL(" CREATE TABLE " + DATABASE_MARKSTABLE + " (" + KEY_STUID + " TEXT PRIMARY KEY, " + KEY_SUB1 + " TEXT NOT NULL, " + KEY_SUB2 + " TEXT NOT NULL, " + KEY_SUB3 + " TEXT NOT NULL, " + KEY_MARKS1 + " INTEGER NOT NULL, " + KEY_MARKS2 + " INTEGER NOT NULL, " + KEY_MARKS3 + " INTEGER NOT NULL);" ); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // TODO Auto-generated method stub db.execSQL("DROP TABLE IF EXISTS " + DATABASE_MARKSTABLE); onCreate(db); } } public DatabaseMarks(Context c){ ourContext = c; } public DatabaseMarks open()throws SQLException{ ourHelper = new DbHelper(ourContext); ourDatabase = ourHelper.getWritableDatabase(); return this; } public void close(){ ourHelper.close(); } public long createInsert(String stuid, String subject1, String subject2, String subject3, String marks1, String marks2, String marks3) { // TODO Auto-generated method stub ContentValues cv = new ContentValues(); cv.put(KEY_STUID, stuid); cv.put(KEY_SUB1, subject1); cv.put(KEY_SUB2, subject2); cv.put(KEY_SUB3, subject3); cv.put(KEY_MARKS1, marks1); cv.put(KEY_MARKS2, marks2); cv.put(KEY_MARKS3, marks3); return ourDatabase.insert(DATABASE_MARKSTABLE, null, cv); } 
+4
source

Remove the DATABASE_NAME portion of your SQL.

If you want NOPO to specify your database name and want to create a log table, then:

 private static class DatabaseHelper extends SQLiteOpenHelper { DatabaseHelper(Context context) { super(context, "NOPO", null, 2); } @Override public void onCreate(SQLiteDatabase db) { db.execSQL("CREATE TABLE " + "log" + " (" + KEY_TIME +" INTEGER primary key, " + KEY_TEXT +" TEXT not null);"); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion + ", which will destroy all old data"); db.execSQL("DROP TABLE IF EXISTS log"); onCreate(db); } } 

super(context, "NOPO", null, 2); makes this DatabaseHelper object only in the NOPO database NOPO , so you do not need to specify it in SQL.

ps. I am doing something, and you must AVOID this.

+1
source

public class LoginDataBaseAdapter {static final String DATABASE_NAME = "login.db";

  static final int DATABASE_VERSION = 1; public static final int NAME_COLUMN = 1; // TODO: Create public field for each column in your table. // SQL Statement to create a new database. static final String DATABASE_CREATE = "create table "+"LOGIN"+ "( " +"ID"+" integer primary key autoincrement,"+ "USERNAME text,PASSWORD text,UNAME text); "; // Variable to hold the database instance public SQLiteDatabase db; // Context of the application using the database. private final Context context; // Database open/upgrade helper private DataBaseHelper dbHelper; public LoginDataBaseAdapter(Context _context) { context = _context; dbHelper = new DataBaseHelper(context, DATABASE_NAME, null, DATABASE_VERSION); } public LoginDataBaseAdapter open() throws SQLException { db = dbHelper.getWritableDatabase(); return this; } public void close() { db.close(); } public SQLiteDatabase getDatabaseInstance() { return db; } 
0
source
  public class DBHandler extends SQLiteOpenHelper { private static String DATABASE_NAME = "mydb"; private static int DATABASE_VERSION = 1; String TABLE_NAME = "Student"; String KEY_ID = "id"; String KEY_STUDENT_NAME = "name"; String KEY_STUDENT_MARKS = "marks"; public DBHandler(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } @Override public void onCreate(SQLiteDatabase db) { // creating table String query = "CREATE TABLE " + TABLE_NAME + " ( " + KEY_ID + " INTEGER PRIMARY KEY," + KEY_STUDENT_NAME + " TEXT," + KEY_STUDENT_MARKS + " INTEGER" " ) "; db.execSQL(query); // insertion in the table ContentValues values = new ContentValues(); values.put(KEY_QUESTION_ID, "1"); values.put(KEY_STUDENT_NAME , "abc"); values.put(KEY_STUDENT_MARKS , "95"); db.insert(TABLE_NAME, null, values); values.put(KEY_QUESTION_ID, "2"); values.put(KEY_STUDENT_NAME , "def"); values.put(KEY_STUDENT_MARKS , "93"); db.insert(TABLE_NAME, null, values); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // TODO Auto-generated method stub db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME); // Create tables again onCreate(db); } // retrieve date List<Student> getAllData(int Id) { List<Student> student = new ArrayList<Student>(); // Select All Query String selectQuery = "SELECT * FROM " + TABLE_NAME + " WHERE KEY_ID =" + "'" + Id + "'"; SQLiteDatabase db = this.getWritableDatabase(); Cursor cursor = db.rawQuery(selectQuery, null); // looping through all rows and adding to list if (cursor.moveToFirst()) { do { Student std = new Student(); u.setId(cursor.getInt(0)); u.setStudentName(cursor.getString(1)); u.setMarks(cursor.getString(2)); student.add(std); } while (cursor.moveToNext()); } // return student list return student; } } public class Student { public static int Id; public static String studentName; public static int marks; public int getId() { return Id; } public static void setId(int id) { Id = id; } public String getStudentName() { return studentName; } public static void setStudentName(String studentName) { Student.studentName = studentName; } public int getMarks() { return marks; } public static void setMarks(int marks) { Student.marks = marks; } } public class MainActivity extends AppCompatActivity { @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); DBHandler db = new DBHandler(getApplicationContext()); List<Student> std = new ArrayList<Student>(); rl = db.getAllData(1); for (Student s : std) { Log.v("Id",s.getId()); Log.v("Name",s.getStudentName()); Log.v("Marks",s.getMarks()); } } } 
-1
source

Source: https://habr.com/ru/post/914321/


All Articles