Datatype mismatch (code 20) while inserting

问题:

In my project im using sqlite database to save pictures and their locations. i get this error while trying to insert the data into my database:

E/SQLiteLog: (20) statement aborts at 5: [INSERT INTO PHOTO VALUES(NULL,?,?,?,?)] datatype mismatch
D/AndroidRuntime: Shutting down VM
E/AndroidRuntime: FATAL EXCEPTION: main
                  Process: com.example.tomer.finalproject, PID: 18550
                  android.database.sqlite.SQLiteDatatypeMismatchException: datatype mismatch (code 20)
                      at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
                      at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:786)
                      at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:926)

Here is my code:

the insert method which is inside the class called SQLiteOpenHelper:

public void insertData(String name, String lat, String lon, byte[] image) {

        SQLiteDatabase database = getWritableDatabase();
        String sql = "INSERT INTO PHOTO VALUES(NULL,?,?,?,?)";
        SQLiteStatement statement = database.compileStatement(sql);
        statement.clearBindings();
        statement.bindString(1, name);
        statement.bindString(2, lat);
        statement.bindString(3, lon);
        statement.bindBlob(4, image);

        statement.executeInsert();

}

I call this in a button 'add' from another activity :

Those 2 lines are under onCreate

sqLiteHelper=new SQLiteHelper(this,"GalleryDB.sqlite",null,1);
        sqLiteHelper.queryData("CREATE TABLE IF NOT EXISTS PHOTO (name VARCHAR,lat VARCHAR,lon VARCHAR,image BLOG,Id INTEGER PRIMARY KEY  AUTOINCREMENT)");

and this is my add Button:

        btnadd.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
               GPStracker g = new GPStracker(getApplicationContext());
               Location l = g.getLocation();
                if (l != null) {
                    double lat = l.getLatitude();
                    double lon = l.getLongitude();
                     String stLat= String.valueOf(lat);
                     String stLon= String.valueOf(lon);
                    try{
                Toast.makeText(getApplicationContext(), "Saved To Gallery!", Toast.LENGTH_SHORT).show();
                 sqLiteHelper.insertData(etname.getText().toString().trim(),stLat.trim(),stLon.trim(),imageViewToByte(imageView));

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


                }


                }
        });

Just want to point out that everything was working until my Laptop crushed and i had to change to another one.

Thank you for your help.

回答1:

I think your issue is with the column order and most importantly the retsriction that an alias for the rowid column, in your case the Id column (Id INTEGER PRIMARY KEY AUTOINCREMENT defines Id as an alias of the rowid column).

• Note other columns can store any value as per :-

  • Any column in an SQLite version 3 database, except an INTEGER PRIMARY KEY column, may be used to store a value of any storage class.

  • Datatypes In SQLite Version 3

Such a column can only store an integer value. String sql = "INSERT INTO PHOTO VALUES(NULL,?,?,?,?)"; equates to insert values as per :-

• NULL into the name column
• name, as passed, into the lat column
• lat, as passed, into the lon column
• lon, as passed, into the image column
• image, as passed, into the Id column (bye[] cannot be stored in Id column)

You can fix this be either providing the column names or by re-ordering the columns to match the order in which they were defined (the former arguably the better practice).

As such either of the following should fix the issue :-

String sql = "INSERT INTO PHOTO (Id, name, lat, lon, image) VALUES(NULL,?,?,?,?)";

or :-

String sql = "INSERT INTO PHOTO VALUES(?,?,?,?,NULL)";