[android] How to insert double and float values to sqlite?

Following is my db creation code.

@Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE " + TABLE_NAME + " (" + 
                _ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + 
                TIME + " INTEGER, " + 
                LONGI + " TEXT, "+
                LATI + " TEXT, "+
                SPEED + " TEXT, "+
                ACCU + " TEXT);");
    }

Then here goes the adding an data point code

private void addGeoDataEntry(double logi, double lati, float speed, float accu) {
        SQLiteDatabase db = gpsDataHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(TIME, System.currentTimeMillis());
        values.put(LONGI, logi+"");
        values.put(LATI, lati+"");
        values.put(SPEED, speed+"");
        values.put(ACCU, accu+"");
        db.insertOrThrow(TABLE_NAME, null, values);
    }

when I call

addGeoDataEntry(10.0,11.0,3.0f,1.1f);

it gives the following error. How to fix this?

03-14 13:57:26.910: I/Database(27910): sqlite returned: error code = 1, msg = near "1.0": syntax error

This question is related to android sqlite

The answer is


I think you should give the data types of the column as NUMERIC or DOUBLE or FLOAT or REAL

Read http://sqlite.org/datatype3.html to more info.


actually I think your code is just fine.. you can save those values as strings (TEXT) just like you did.. (if you want to)

and you probably get the error for the System.currentTimeMillis() that might be too big for INTEGER


    enter code here




package in.my;

import android.content.ContentValues;

import android.content.Context;

import android.database.Cursor;

import android.database.SQLException;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteOpenHelper;

public class DBAdapter {    
    private final Context context; 
    private DatabaseHelper DBHelper;

    private SQLiteDatabase db;

    private static final String DATABASE_NAME = "helper.db";

    private static final int DATABASE_VERSION = 1;

    public static final String KEY_ID = "_id";

    private static final String Table_Record =

        "create table Student (_id integer primary key autoincrement, "
        + "Name text not null,rate integer, Phone text not null,Salary text not null,email text not null,address text not null,des text not null,qual text not null,doj text not null);";


    public DBAdapter(Context ctx) 
    {
        this.context = ctx;
        DBHelper = new DatabaseHelper(context);
    }

    private class DatabaseHelper extends SQLiteOpenHelper
    {

        public DatabaseHelper(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(Table_Record);

        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            // TODO Auto-generated method stub

        }
    }

        public DBAdapter open() throws SQLException
        {
            db = DBHelper.getWritableDatabase();
            return DBAdapter.this;
        }

        //---closes the database---
        public void close() 
        {
            DBHelper.close();
        }

        public long insertTitle(String name,String phone,String web,String des,String address,String doj,String qual,String sal,int rate) 
        {
            ContentValues initialValues = new ContentValues();
            initialValues.put("Name", name);
            initialValues.put("Phone", phone);
            initialValues.put("email", web);


            initialValues.put("des", des);
            initialValues.put("Salary", sal);
            initialValues.put("qual", qual);
            initialValues.put("address", address);
            initialValues.put("doj", doj);
            initialValues.put("rate", rate);

            return db.insert("Student", null, initialValues);
        }

        public boolean deleteTitle(long rowId) 
        {
            return db.delete("Student", KEY_ID + 
                    "=" + rowId, null) > 0;
        }

        public boolean UpdateTitle(long id,String name,String phone,String web,String des,String address,String doj,String qual,String sal,int rate) 
        {
            ContentValues initialValues = new ContentValues();
            initialValues.put("Name", name);
            initialValues.put("Phone", phone);
            initialValues.put("email", web);
            initialValues.put("des", des);
            initialValues.put("qual", qual);
            initialValues.put("Salary", sal);
            initialValues.put("address", address);
            initialValues.put("doj", doj);          
            initialValues.put("rate", rate);
            return db.update("Student",initialValues, KEY_ID + "=" + id, null)>0;

            //return db.insert("Student", null, initialValues);
        }

        public Cursor getAllRecords()
        {
            return db.query("Student", new String[] {
                    KEY_ID,
                    "Name", 
                    "Phone",
                    "email",
                    "address", 
                    "des",
                    "qual",
                    "doj",
                    "Salary",
                    "rate"

            },
                    null, 
                    null, 
                    null, 
                    null, 
                    null);
        }
    }

SQL Supports following types of affinities:

  • TEXT
  • NUMERIC
  • INTEGER
  • REAL
  • BLOB

If the declared type for a column contains any of these "REAL", "FLOAT", or "DOUBLE" then the column has 'REAL' affinity.