How to insert double and float values to sqlite?

Question

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

score 144 · Accepted Answer · answered Mar 14 '12 at 12:09

144

REAL is what you are looking for. Documentation of SQLite datatypes

answered Mar 14 '12 at 12:09

WarrenFaith

57,492
25
134
150

1

i changed the TEXT into REAL, and removed +"" parts to pass them as numbers. Still i get the same error. Any how i added : public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME); onCreate(db);} } – dinesh707 Mar 14 '12 at 12:14
could you show the complete error message? the part after `syntax error`? I would like to see the complete message and error – WarrenFaith Mar 14 '12 at 12:19
Thank you for the link. The problem i had was i have added some wrong vlaues to LOGIN, LATI etc etc. I forgot they are names. I added values there. – dinesh707 Mar 14 '12 at 12:26
3

Without changing the database version the onUpgrade will never be called. So best way is to remove your app and make a clean installation to test it. – WarrenFaith Mar 14 '12 at 12:26
This didn't work for double precision numbers. While the correct values were written to the table, reading them with IDataReader.GetValues() would return floats instead of doubles. In order for this to work I had to add a special case for doubles, changing the affinity to TEXT and then using double.Parse() – Trap Feb 04 '23 at 23:19

score 18 · Answer 2 · edited Jan 15 '17 at 13:31

18

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.

edited Jan 15 '17 at 13:31

Muneeb Mirza

810
1
17
35

answered Aug 26 '15 at 11:25

Sandhu

818
9
18

score 5 · Answer 3 · answered Mar 14 '12 at 12:13

5

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.

answered Mar 14 '12 at 12:13

Chandra Sekhar

18,914
16
84
125

score 1 · Answer 4 · answered Mar 14 '12 at 12:20

1

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

answered Mar 14 '12 at 12:20

Joe

2,252
1
22
32

+1 `System.currentTimeMillis()` returns a `long`. So yes, exceeds `int` size. – IAmGroot Sep 05 '12 at 15:27
3

@Doomsknight: no it does not. from the doc : `INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.` – njzk2 Sep 19 '14 at 18:54
@njzk2 Which docs are you reading? In android / Java, it returns a long. http://developer.android.com/reference/java/lang/System.html#currentTimeMillis() – IAmGroot Sep 19 '14 at 19:50
3

@Doomsknight: yes, but `long` fits in the sqlite type `INTEGER`, which can be up to 8 bytes. – njzk2 Sep 19 '14 at 20:20
@njzk2 Ah yes. woops. It was a long time since I wrote this, I mis understood – IAmGroot Sep 19 '14 at 21:09

score -5 · Answer 5 · edited Mar 23 '22 at 11:52

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);
        }
    }

Stop commenting your own question! Stop posting code only questions without any explanation and please don't use something like `gr8`. We are professionals, right? — WarrenFaith, Mar 14 '12 at 12:18
Sorry, typo. I meant commenting your own answer should more be an edit. — WarrenFaith, Mar 14 '12 at 12:24

How to insert double and float values to sqlite?

5 Answers5

Linked