Android: how do I increment SQLite INTEGER PRIMARY KEY AUTOINCREMENT Column?

In my activity I have a Save button that upon clicking will save a bunch of user input data to a SQLite table. On the same click, I want to also increment an integer counter in another table. The counter is set up as "cardnum".

What am I missing here?

Activity file:

...
public void onClickSave(View v) {
    int stringToDo = cListenerEditText.getText().toString().replace(" ", "").length();
    int stringDueDate = fListenerEditText.getText().toString().replace(" ", "").length();
    int stringDueTime = gListenerEditText.getText().toString().replace(" ", "").length();

String todo = cListenerEditText.getText().toString();
String note1 = dListenerEditText.getText().toString();
String note2 = eListenerEditText.getText().toString();
String duedate =  fListenerEditText.getText().toString();
String duetime =  gListenerEditText.getText().toString();
long timestamp = System.currentTimeMillis();

// Increment the counter column in the Count Table.
int cardnum = ++ cardnum;

helper = new DatabaseHelper(CardViewActivity.this);

helper.insertIntoTableUserInputs(todo,note1,note2,duedate,duetime,timestamp);
helper.insertIntoTableCount(cardnum);

startActivity(new Intent(CardViewActivity.this,ListActivity.class));
}     

DBHelper file:

...
private static final String SQL_CREATE_TABLE =
        "CREATE TABLE IF NOT EXISTS " + DBContract.DBEntry.TABLE_NAME_COUNT +
            "(" + DBContract.DBEntry.COLUMN_NAME_CARDNUM +
                  " INTEGER PRIMARY KEY AUTOINCREMENT" + ")";

public void insertIntoTableCount(int cardnum) {

    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values2 = new ContentValues();
    values2.put(DBContract.DBEntry.COLUMN_NAME_CARDNUM, cardnum);

    db.insert(DBContract.DBEntry.TABLE_NAME_COUNT, null, values2);

    db.close();
}

MPAndroid does not show the pie chart

I want to display a pie chart using the data from the sqlite. I out the data into the list to have the entries for the pie chart. But it seems something is wrong with the code and I don;t know what is wrong with it.Below is the code.

public class chart extends Activity {

SQLiteDatabase SQ;

@Override
protected void onCreate (Bundle savedInstanceState){
…
    //add data
    addData();

    //customize legends
    Legend l=mChart.getLegend();
    l.setPosition(Legend.LegendPosition.RIGHT_OF_CHART);
    l.setXEntrySpace(7);
    l.setYEntrySpace(5);

}

public ArrayList<String> queryXData(){
    ArrayList<String> xNewData = new ArrayList<String>();
    String query="SELECT "+ TableInfo.APPLIANCE +" FROM "+TableInfo.TABLE_NAME;
    Cursor cursor = SQ.rawQuery(query,null);
    for(cursor.moveToFirst();!cursor.isAfterLast();cursor.moveToNext()){
        xNewData.add(cursor.getString(0));
    }
    cursor.close();
    return xNewData;
}

public ArrayList<Integer> queryYData(){
    ArrayList<Integer> yNewData= new ArrayList<Integer>();
    String query="SELECT "+ TableInfo.POWER_USED +" FROM "+TableInfo.TABLE_NAME;
    Cursor cursor=SQ.rawQuery(query,null);
    for(cursor.moveToFirst();!cursor.isAfterLast();cursor.moveToNext()){
        yNewData.add(cursor.getInt(5));
    }
    cursor.close();
    return yNewData;
}

private void addData() {
    ArrayList<Entry> yVals = new ArrayList<Entry>();

    for (int i = 0; i < queryYData().size(); i++)
        yVals.add(new Entry(queryYData().get(i), i));

    ArrayList<String> xVals = new ArrayList<String>();

    for (int i = 0; i < queryXData().size(); i++)
        xVals.add(queryXData().get(i));

    //create pie data set
    PieDataSet dataSet=new PieDataSet(yVals,"Appliances");
    dataSet.setSliceSpace(3); // space between each arc slice
    dataSet.setSelectionShift(5);

    //add many colors
    ArrayList<Integer> colors=new ArrayList<Integer>();

    for(int c: ColorTemplate.VORDIPLOM_COLORS)
        colors.add(c);
    for(int c: ColorTemplate.JOYFUL_COLORS)
        colors.add(c);
    for(int c: ColorTemplate.COLORFUL_COLORS)
        colors.add(c);
    for(int c: ColorTemplate.LIBERTY_COLORS)
        colors.add(c);
    for(int c: ColorTemplate.PASTEL_COLORS)
        colors.add(c);
    colors.add(ColorTemplate.getHoloBlue());
    dataSet.setColors(colors);

    //instantiate pie data object now
    PieData data=new PieData(xVals, dataSet);
    data.setValueFormatter(new PercentFormatter());
    data.setValueTextSize(11f);
    data.setValueTextColor(Color.GRAY);

    mChart.setData(data);

    //undo all highlights
    mChart.highlightValues(null);

    //update pie chart
    mChart.invalidate();

    //demo start
}

}

Aboce is the code I tried , but there are errors, mainly

chart.queryYData(chart.java:100) Cursor cursor=SQ.rawQuery(query,null); chart.addData(chart.java:111) for (int i = 0; i < queryYData().size(); i++) chart.onCreate(chart.java:76) addData();

These error made the app “Unfortunaely, app has stopped.”. Can someone help me with this? Thank you.

How to select data from a database, sort it and insert it in another database in SQLite android?

I have a table which contains values from 2 tree map. I couldn't merge them into one and pass as a single map because the input contains duplicates . In short, the data in my table looks like

APPLE  1
BANANA 4
ORANGE 5
GRAPES 1
POTATO 9

I want to sort the integers in ascending order. I tried using order by clause in select statement and used that select statement for creating another table in sorted manner but I couldn't do as there is some missing parenthesis in exec statement.

db.execSQL(INSERT INTO my_ordered_table (name, num) SELECT name,num FROM my_table ORDER BY name ;

I tried writing this but I don't know where to insert parenthesis, " " etc . The column values are dynamic ones and not hard coded values .

I even want INSERT IF EXISTS ELSE UPDATE condition in the insert statement.Any help would be great !! Thanks

Line break when importing csv to mysql and sqlite

I am importing CSV data to MySQL (using 000webhost) and SQLite. I have data with line breaks in my database as such:

My first line.\nMy second line.

it should print out like this:

My first line. My second line.

I am developing for an Android app to display the results. I get from the database into a TextView and the data that is displayed is shown with \n as such:

My first line.\nMy second line.

Shouldn't it escape \n and print it as a line break instead??

Cordova sqlite pre populated in iOS

i have a pre populated database which i put in www , it worked fine when building for android , now i have spent a whole day trying to compile it on iOS from mac and it loads the sqlite , i can create new tables and queries but i can not load my pre populated database like android , this is my code : can someone tell what i am doing wrong

var db;

function onLoad(){
    document.addEventListener("deviceready", onDeviceReady, false);
}

// Cordova is ready
    var onDeviceReady = function(){
        db = window.sqlitePlugin.openDatabase({name: "my.db",iosDatabaseLocation: 1});
        db.transaction(getFirst200,querySuccess,errorCB);
    };

    var querySuccess = function(){
        alert("success");
    };

    var errorCB = function(){
        alert("error");
    };

    var getFirst200 = function(tx){
        db.transaction(function(tx) {
                tx.executeSql('select * from nutrition_values Limit 200', [], querySuccess, errorCB);
            });
    };

Add Columns to a SQL Row from Count of Unique Values in One Column

I have two columns I am interested in:

bought_time           |    product
-----------------------------------
1990-10-26T09:00:00Z  | bicycle
1990-10-26T09:00:01Z  | car
1990-10-26T09:13:22Z  | bicycle
1990-10-26T09:26:11Z  | cookies
1990-10-26T09:26:13Z  | milk
1990-10-26T09:27:16Z  | milk

I want to create a SQLite query in Ruby on Rails that will return this after being converted by as_json:

[{date by minute: 1990-10-26T09:00:00Z, bicycle: 1, car: 1, cookies:0, milk:0}, {date by minute: 1990-10-26T09:13:00Z, bicycle:1, car:0, cookies:0, milk:0}, {date by minute: 1990-10-26T09:26:00Z, bicycle:0, car:0, cookies:1, milk:1}, {date by minute: 1990-10-26T09:26:00Z, bicycle:0, car:0, cookies:0, milk:1} ]

So far I have something like:

Bought.select("datetime((strftime('%s', date)/60)*60, 'unixepoch') interval, count(type)").where("date(date) >= date(:from) AND date(date) <= date(:to)", {from: event_params["from"], to: event_params["to"]}).order("interval").group("type").group("interval")

Which in SQL is

SELECT datetime((strftime('%s', date)/60)*60, 'unixepoch') interval, count(type), type FROM "events" WHERE (date(date) >= date('1985-10-26T09:00:00Z') AND date(date) <= date('1985-10-27T09:00:00Z')) GROUP BY interval, "events"."type"  ORDER BY interval

but I am at a loss at how to proceed from here.

SQLITE3 query condition

I'm new to sqlite3 and have hit a bit of a wall with my database. I don't really have anybody else to ask so a friend recommended I post here to try get a bit of help.

Say I have two tables

restaurants:

Restaurant  Location  Chef
'All Beef'  'London'  'Bob'
'All Lamb'  'Paris'   'Mike'
'All Pork'  'Berlin'  'Jill'
'All Veg'   'London'  'Heather'

chefs:

Name      Gender     Place_Of_Birth
'Bob'     'Male'     'London'
'Mike'    'Male'     'Paris'
'Jill'    'Female'   'London'
'Heather' 'Female'   'Berlin'

'How many restaurants are there in London with chefs that were not born there?'

Just looking for a point in the right direction, thanks a lot!

SQL JOIN on one field or the other

Trying to order a family by father's name or, if there is no father, then the mother's name where the names are in a separate "person" table, something like:

SELECT DISTINCT family.myid FROM family 
   JOIN person 
   ON family.father_id = person.myid OR
   family.mother_id = person.myid
   ORDER BY person.surname, 
            person.given_name;

In this version, the families without fathers end up unsorted at the bottom. Would like families without fathers to appear in the order by the mother's name. Sqlite SQL will suffice.

first time using python to connect with sqlite3 getting error

I have installed Pyhton 3.5.1 version,

tried to connect sqlite to it with following code:

import sqlite3
conn = sqlite3.connect('test.db')

It worked well for first time so I left it as it was. Came back to computer and I tried to use the same code again

However I am receiving this message now all the sudden using the same code:

Traceback (most recent call last): File "", line 1, in sqlite3.OperationalError: unable to open database file

I find it very strange, because I haven't done anything special afterwards when using code for first time. I am new to Python and I have absolutley no idea why I get this. Any help would be more then welcome.

converting sqlite database to excel in android

I am trying to take the data from my database and create an excel csv file from this data. I have looked at some tutorials but am not sure how to go about this. any advice?

I'm not sure where to start but here's my dbadapter class:

public class DBAdapter {

public static final String KEY_ROWID = "_id";
public static final String KEY_TITLE = "title";
public static final String KEY_WORKOUTDATE = "workoutDate";
public static final String KEY_EXERCISE_NOTES = "notes";

private static final String TAG = "WorkoutDBAdapter";
private DatabaseHelper mDBHelper;
private SQLiteDatabase mdb;

private static final String DATABASE_NAME = "WorkoutDB";
private static final String DATABASE_TABLE = "workouts";
private static final int DATABASE_VERSION = 2;

private final Context mCtx;

private static final String DATABASE_CREATE =
        "create table if not exists workouts " +
                "(_id integer primary key autoincrement, " +
                "title VARCHAR not null, " +
                "workoutDate date, " +
                "notes VARCHAR );";

private static class DatabaseHelper extends SQLiteOpenHelper {

    DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        Log.w(TAG, DATABASE_CREATE);
        db.execSQL(DATABASE_CREATE);
    }

    @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 workouts");
        onCreate(db);
    }
}


public DBAdapter(Context ctx) {
    this.mCtx = ctx;
}


public DBAdapter open() throws SQLException {
    mDBHelper = new DatabaseHelper(mCtx);
    mdb = mDBHelper.getWritableDatabase();
    return this;
}

public void close() {
    if (mDBHelper != null) {
        mDBHelper.close();
    }
}

//---insert a record into the database---
public long insertRecord(String title, String workoutdate, String notes) {
    ContentValues initialValues = new ContentValues();
    initialValues.put(KEY_TITLE, title);
    initialValues.put(KEY_WORKOUTDATE, workoutdate);
    initialValues.put(KEY_EXERCISE_NOTES, notes);

    return mdb.insert(DATABASE_TABLE, null, initialValues);
}


//---retrieves all the records---
public Cursor getAllRecords() {
    Cursor mCursor = mdb.query(DATABASE_TABLE, new String[]{KEY_ROWID, KEY_TITLE,
            KEY_WORKOUTDATE, KEY_EXERCISE_NOTES}, null, null, null, null, null);

    if (mCursor != null) {
        mCursor.moveToFirst();
    }
    return mCursor;
}
} 

How to update data in sqlite Database

Basically I have made an app which is a quiz type app. At every Activity 1 single question appears. Since I'm using ViewPager adapter and answers are to be chosen in the form of radio buttons, I am having trouble how to store the answers in the database and if the user goes back on previous question again and changes that answer I need to update the database accordingly.

How to manage these all things in sqlite database?

Getting null as a result

I have created two tables, First is Posts table and seconds is Comments table. I'm using sqlite database.

I'm getting null as result for comment function using hasMany method in Post Model.

Posts table migration file:

    class CreatePostsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('Posts', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('Posts');
    }
}

Comments table migration file:

 class CreateCommentsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
{
    Schema::create('Comments', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('post_id')->unsigned()->index();
        $table->text('body');
        $table->timestamps();
    });
        Schema::table('Comments', function (Blueprint $table) {
            $table->foreign('post_id')->refrences('id')->on('Posts');
        });
}


/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    Schema::drop('Comments');
}
}

Post Model:

class Post extends Model
{

    //
    public function comment(){
    return $this->hasMany(Comment::class);    
   //I have also tried: return $this->hasMany(Comment::class,'post_id','id');
    }
}

The data has been entered in both the tables and I'm getting results when I do

$post=new App\Post;
$post->get();

But when I try to get all the comments for a post using

$post->comment;

I'm getting

=>Illuminate\Database\Eloquent\Collection {#633
all:[],
}

Why am I getting null as a result?

Android MVC approach with SQLiteDatabase: no such column exception [duplicate]

This question already has an answer here:

• When is SQLiteOpenHelper onCreate() / onUpgrade() run? 4 answers

Was experimenting with a simple way to implement MVC in Android (no controller yet) with SQLiteDatabase to store the model but when i run my application it throws "android.database.sqlite.SQLiteException: no such column" exception and shuts down.

The main activity:

package nemphys.listitup.activity;

import android.content.DialogInterface;
import android.os.Bundle;
import android.support.v7.app.AlertDialog;
import android.support.v7.app.AppCompatActivity;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.ArrayAdapter;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.TextView;

import nemphys.listitup.R;
import nemphys.listitup.model.OnChangeListener;
import nemphys.listitup.model.ToDoModel;

public class MainActivity extends AppCompatActivity implements
        OnChangeListener<ToDoModel> {

    private static final String TAG = MainActivity.class.getSimpleName();

    private ToDoModel model;
    private ListView listToDo;
    private ArrayAdapter<String> adapter;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        model = new ToDoModel(this);
        model.addListener(this);
        listToDo = (ListView) findViewById(R.id.listToDo);
        adapter = new ArrayAdapter<>(this, R.layout.item_todo,
                R.id.txtItemName, model.getToDoList());
        listToDo.setAdapter(adapter);
    }

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        getMenuInflater().inflate(R.menu.menu_main, menu);
        return super.onCreateOptionsMenu(menu);
    }

    @Override
    public boolean onOptionsItemSelected(MenuItem item) {
        switch (item.getItemId()) {
            case R.id.addItem:
                final EditText taskEditText = new EditText(this);
                AlertDialog dialog = new AlertDialog.Builder(this)
                        .setTitle("Neue Aufgabe hinzufügen")
                        .setMessage("Was wollen Sie als nächstes tun?")
                        .setView(taskEditText)
                        .setPositiveButton("Hinzufügen",
                                new DialogInterface.OnClickListener() {
                                    @Override
                                    public void onClick(DialogInterface dialog, int which) {
                                        String task = String.valueOf(taskEditText.getText());
                                        model.addItem(task);
                                    }
                                })
                        .setNegativeButton("Abbrechen", null)
                        .create();
                dialog.show();
                return true;
            default:
                return false;
        }
    }

    public void deleteTask(View view) {
        View parent = (View) view.getParent();
        TextView taskTextView = (TextView) parent.findViewById(R.id.txtItemName);
        String task = String.valueOf(taskTextView.getText());
        model.removeItem(task);
    }

    @Override
    public void onChange(ToDoModel model) {
        adapter.notifyDataSetChanged();
    }
}

The model:

package nemphys.listitup.model;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import java.util.ArrayList;

import nemphys.listitup.activity.MainActivity;

public class ToDoModel extends SimpleObservable<ToDoModel> {

    private static final String TAG =
            MainActivity.class.getSimpleName();

    private ToDoDbHelper dbHelper;

    public ToDoModel(Context context) {
        dbHelper = new ToDoDbHelper(context);
    }

    public ArrayList<String> getToDoList() {
        ArrayList<String> toDoList = new ArrayList<>();
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        Cursor cursor = db.query(ToDoContract.ToDoEntry.TABLE_NAME,
                new String[]{ToDoContract.ToDoEntry._ID,
                        ToDoContract.ToDoEntry.COLUMN_TASK_TITLE},
                null, null, null, null, null);
        while (cursor.moveToNext()) {
            int i = cursor.getColumnIndex(ToDoContract.ToDoEntry.COLUMN_TASK_TITLE);
            toDoList.add(cursor.getString(i));
        }
        cursor.close();
        db.close();
        return toDoList;
    }

    public void addItem(String toDoItem) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(ToDoContract.ToDoEntry.COLUMN_TASK_TITLE, toDoItem);
        db.insertWithOnConflict(ToDoContract.ToDoEntry.TABLE_NAME,
                null,
                values,
                SQLiteDatabase.CONFLICT_REPLACE);
        db.close();
        notifyObservers(this);
    }

    public void removeItem(String toDoItem) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        db.delete(ToDoContract.ToDoEntry.TABLE_NAME,
                ToDoContract.ToDoEntry.COLUMN_TASK_TITLE + " = ?",
                new String[]{toDoItem});
        db.close();
        notifyObservers(this);
    }
}

The Observable:

package nemphys.listitup.model;

import java.util.ArrayList;

public class SimpleObservable<T> {

    private final ArrayList<OnChangeListener<T>> listeners = new ArrayList<>();

    public void addListener(OnChangeListener<T> listener) {
        synchronized (listeners) {
            listeners.add(listener);
        }
    }

    public void removeListener(OnChangeListener<T> listener) {
        synchronized (listeners) {
            listeners.remove(listener);
        }
    }

    protected void notifyObservers(final T model) {
        synchronized (listeners) {
            for (OnChangeListener<T> listener : listeners) {
                listener.onChange(model);
            }
        }
    }

}

The SQLiteOpenHelper:

package nemphys.listitup.model;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class ToDoDbHelper extends SQLiteOpenHelper {
    public static final int DATABASE_VERSION = 1;
    public static final String DATABASE_NAME = "ToDo.db";

    private static final String SQL_CREATE_ENTRIES =
            "CREATE TABLE " + ToDoContract.ToDoEntry.TABLE_NAME + " (" +
                    ToDoContract.ToDoEntry._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
                    ToDoContract.ToDoEntry.COLUMN_TASK_TITLE + "TEXT NOT NULL);";

    private static final String SQL_DELETE_ENTRIES =
            "DROP TABLE IF EXISTS " + ToDoContract.ToDoEntry.TABLE_NAME;

    public ToDoDbHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    public void onCreate(SQLiteDatabase db) {
        db.execSQL(SQL_CREATE_ENTRIES);
    }

    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL(SQL_DELETE_ENTRIES);
        onCreate(db);
    }

    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        onUpgrade(db, oldVersion, newVersion);
    }
}

The Contract:

package nemphys.listitup.model;

import android.provider.BaseColumns;

public final class ToDoContract {
    private ToDoContract() {
    }

    public static class ToDoEntry implements BaseColumns {
        public static final String TABLE_NAME = "tasks";
        public static final String COLUMN_TASK_TITLE = "title";
    }
}

The logcat:

04-30 13:28:37.718 22986-22986/nemphys.listitup E/SQLiteLog: (1) no such column: title
04-30 13:28:37.722 22986-22986/nemphys.listitup D/AndroidRuntime: Shutting down VM
04-30 13:28:37.723 22986-22986/nemphys.listitup E/AndroidRuntime: FATAL EXCEPTION: main
                                                                  Process: nemphys.listitup, PID: 22986
                                                                  java.lang.RuntimeException: Unable to start activity ComponentInfo{nemphys.listitup/nemphys.listitup.activity.MainActivity}: android.database.sqlite.SQLiteException: no such column: title (code 1): , while compiling: SELECT _id, title FROM tasks
                                                                      at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2416)
                                                                      at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2476)
                                                                      at android.app.ActivityThread.-wrap11(ActivityThread.java)
                                                                      at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1344)
                                                                      at android.os.Handler.dispatchMessage(Handler.java:102)
                                                                      at android.os.Looper.loop(Looper.java:148)
                                                                      at android.app.ActivityThread.main(ActivityThread.java:5417)
                                                                      at java.lang.reflect.Method.invoke(Native Method)
                                                                      at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:726)
                                                                      at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:616)
                                                                   Caused by: android.database.sqlite.SQLiteException: no such column: title (code 1): , while compiling: SELECT _id, title FROM tasks
                                                                      at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
                                                                      at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:887)
                                                                      at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:498)
                                                                      at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
                                                                      at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
                                                                      at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
                                                                      at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
                                                                      at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1316)
                                                                      at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1163)
                                                                      at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1034)
                                                                      at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1202)
                                                                      at nemphys.listitup.model.ToDoModel.getToDoList(ToDoModel.java:29)
                                                                      at nemphys.listitup.activity.MainActivity.onCreate(MainActivity.java:36)
                                                                      at android.app.Activity.performCreate(Activity.java:6237)
                                                                      at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1107)
                                                                      at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2369)
                                                                      at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2476) 
                                                                      at android.app.ActivityThread.-wrap11(ActivityThread.java) 
                                                                      at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1344) 
                                                                      at android.os.Handler.dispatchMessage(Handler.java:102) 
                                                                      at android.os.Looper.loop(Looper.java:148) 
                                                                      at android.app.ActivityThread.main(ActivityThread.java:5417) 
                                                                      at java.lang.reflect.Method.invoke(Native Method) 
                                                                      at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:726) 
                                                                      at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:616) 

In a previous version of my application I stored the reference to the SQLiteOpenHelper in the main activity and everything went fine. Now that i introduced the model in my application I just can't make it work.

Thanks in advance!

Delete item from SQLite and listView

i want to delete item from a listView and SQLite database with contextMenu and my code doesnt remove anything. Maybe someone can help me?

DBAdapter.java

public void delete(String name)throws SQLException {
        SQLiteDatabase db = helper.getWritableDatabase();
        if (db == null) {
            return;
        }
        String[] whereArgs = new String[] { name };
        db.delete("m_TB", "NAME"+ "=?", whereArgs);
        db.close();
    }

MainActivity.java

public void onCreateContextMenu(ContextMenu menu, View v, ContextMenu.ContextMenuInfo menuInfo) {
        super.onCreateContextMenu(menu, v, menuInfo);
        menu.add("Delete");
    }

    public boolean onContextItemSelected(MenuItem item) {
        super.onContextItemSelected(item);
        AdapterView.AdapterContextMenuInfo info = (AdapterView.AdapterContextMenuInfo) item.getMenuInfo();
        String name = info.toString();
        if (item.getTitle().equals("Delete")) {
            db.delete(name);
            books.remove(item);
            adapter.notifyDataSetChanged();

        }
        return true;
    }

String starting with + operator doesn't match with database String value

I want to compare incoming sms numbers with database saved number. if they are same then show a Toast. It works fine for simple numbers like 9 or any others but not with + operator.

When we recieved a message from a number it consists of country code like for pakistan +92XXXXXXXXXX. I've saved it to database but when message recieved with the same number then it doesn't match.

I checked on emulator it works fine for simple number like message from 9 etc. but not with + operator. kidnly help.

My database tabe:

// Create statement for block_list table
    public static final String CREATE_TABLE_BLOCK_LIST = "CREATE TABLE " + TABLE_BLOCK_LIST
            + "(" + ID_BLOCK_LIST + " INTEGER PRIMARY KEY AUTOINCREMENT DEFAULT 0,"
            + ORIGINATING_ADDRESS_BLOCKlIST + " TEXT,"
            + TIME_BLOCK_LIST + " TEXT)";  

comparing statement:

// Select all Blocked Numbers
    public Cursor compareToBlockList(String originatingAddress){
        Cursor cursor = null;
        try {
            SQLiteDatabase db = this.getWritableDatabase();
            cursor = db.rawQuery("SELECT " + ORIGINATING_ADDRESS_BLOCKlIST + " FROM " + TABLE_BLOCK_LIST
                    + " WHERE " + ORIGINATING_ADDRESS_BLOCKlIST + "=" + originatingAddress, null);
        } catch (Exception e){
            Log.d("compare to Block list", " is" + e.getMessage());
        }
        return  cursor;
    }

calling function:

Cursor cursor = dbManagement.compareToBlockList(originatingAddress);
                        cursor.moveToFirst();
                        String number = cursor.getString(cursor.getColumnIndex(dbManagement.ORIGINATING_ADDRESS_BLOCKlIST));

                        Toast.makeText(context, "found", Toast.LENGTH_LONG).show();

database value:

Wrong number of rows updated in SQLite DB

I am trying to change a set amount of rows randomly in a SQLite DB. The problem is that the number of rows changed is alsways greater than the one intended. Could somebody point out my mistake?

"probM" and "probF" should be the probability for the rows to be changed. For example, when probM is set to 5, countM goes up to about 120. But instead of 120 rows being updated 970 are.

"DBUpdater.count" returns the number of rows for a given collumn and WHERE statement.

public static void verwitwetStmt(int probM, int probF) {
    String select;
    String preparedStmt;
    Random r = new Random();
    int rnd;
    int countW = 0;
    int countM = 0;
    int anzahl = DBUpdater.count("Jahre", "Jahre BETWEEN 66 AND 70 AND Geschlecht IS 0");

    for (int i = 0; i < anzahl; i++) {
        rnd = r.nextInt(100);
        if (rnd <= probM)
            countM++;
        }
    System.out.println(anzahl + ", " +countM);
    select = "SELECT ID FROM individuen WHERE Jahre BETWEEN 66 AND 70 AND Familienstand IS NULL "
            + "AND Geschlecht IS 0 ORDER BY RANDOM() LIMIT " + Integer.toString(countM);
    preparedStmt = "UPDATE individuen SET Familienstand = ? WHERE ID = ?";

    DBUpdater.blankUpdate(select, preparedStmt, 3);

    anzahl = DBUpdater.count("Jahre", "Jahre BETWEEN 66 AND 70 AND Geschlecht IS 1");

    for (int i = 0; i < anzahl; i++) {
        rnd = r.nextInt(100);
        if (rnd <= probF)
            countW++;
        }

    select = "SELECT ID FROM individuen WHERE Jahre BETWEEN 66 AND 70 AND Familienstand IS NULL "
            + "AND Geschlecht IS 1 ORDER BY RANDOM() LIMIT " + Integer.toString(countW);
    preparedStmt = "UPDATE individuen SET Familienstand = ? WHERE ID = ?";

    DBUpdater.blankUpdate(select, preparedStmt, 3);
}

Here is the blankUpdate method:

public static void blankUpdate(String selectQuery, String preparedStatement, int wert) {
    DBController dbc = DBController.getInstance();
    dbc.initDBConnection();
    try {
        Statement stmt = DBController.connection.createStatement();
        ResultSet rs = stmt.executeQuery(selectQuery);
        PreparedStatement ps = DBController.connection.prepareStatement(preparedStatement);

        while (rs.next()) {
            ps.setInt(1, wert);
            ps.setInt(2, rs.getInt(1));
            ps.addBatch();
        }
        DBController.connection.setAutoCommit(false);
        ps.executeBatch();
        DBController.connection.setAutoCommit(true);
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

And here the count method:

public static int count(String zeile, String whereStmt) {
    DBController dbc = DBController.getInstance();
    dbc.initDBConnection();
    int anzahl = 0;
    try {
        Statement stmt = DBController.connection.createStatement();
        String select = "SELECT COUNT(" + zeile + ") FROM individuen WHERE " + whereStmt + ";";
        ResultSet rs = stmt.executeQuery(select);

        select = rs.getString(1);
        anzahl = Integer.parseInt(select);
        rs.close();
        stmt.close();

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

Estimate size of SQLite Database for offline dictionary

I'm trying to build offline Vocabulary builder app in android which has nearly 5000 words with each word having multiple fields(like synonyms,antonyms,related words etc..,)all these are placed into one table in a sqlite db and there is another table in which user can insert/delete words in it,at the maximum each word can be inserted into 15 rows in this second table.So max rows in this table can be 15x5000 =75,000.can anyone with experience in using sqlite db, estimate size of this db?.any comment on performance is also helpful.

Core Data sqlite store single table due to inheritance drawbacks?

I have a Core Data model with something like 20 entities. I want all entities to have common attributes. For example, all of them have a creation date attribute. I therefore introduced an common entity containing all the common attributes, and all the other entities inherit from this common entity.

This is fine and works well, but then, all entities end up in one single SQLite table (which is rather logical).

I was wondering if there was any clear drawback to this ? For example, when going in real life with 1000+ objects of each entity, would the (single) table become so huge that terrible performance problems could happen ?

SQLite querying for a specific colum

I have a database called students.db. It contains STUDENTTABLE with three columns which are NAME, LASTNAME and GRADE. I know how the get all the table data but now I want to get the data in column NAME. How can that be done?

I use this query get all the data:

Cursor res = db.rawQuery("select * from " + DataTableName,null);

How to disable stack trace in node-sqlite3?

I had installed sqlite3 from npm and I see this stack trace (but more long) when using the package. How to disable it?

Node version is 6.0.

==== JS stack trace =========================================

Security context: 0x1e993ebc9fa9 #0#
    1: .node [module.js:568] [pc=0x1e3962aa3c84] (this=0x14ceca9bad31 #1#,module=0x2f85be11f541 #2#,filename=0x2f85be11f509 )
    2: load [module.js:456] [pc=0x1e3962a38e72] (this=0x2f85be11f541 #2#,filename=0x2f85be11f509 )
    3: tryModuleLoad(aka tryModuleLoad) [module.js:415] [pc=0x1e3962a3899d] (this=0x1e993eb04189 ,module=0x2f85be11f541 #2#,filename=0x2f85be11f509 )
    4: _load [module.js:407] [pc=0x1e3962a345e2] (this=0x14ceca9badb1 #3#,request=0x2f85be1186c1 ,parent=0x14ceca9bd321 #4#,isMain=0x1e993eb04299 )
    5: require [module.js:466] [pc=0x1e3962a42573] (this=0x14ceca9bd321 #4#,path=0x2f85be1186c1 )
    6: require(aka require) [internal/module.js:20] [pc=0x1e3962a422a6] (this=0x1e993eb04189 ,path=0x2f85be1186c1 )
    7: /* anonymous */ [/home/oleh/node/node_modules/sqlite3/lib/sqlite3.js:4] [pc=0x1e3962a4795e] (this=0x14ceca9bd5b9 #5#,exports=0x14ceca9bd5b9 #5#,require=0x14ceca9bd3b1 #6#,module=0x14ceca9bd321 #4#,__filename=0x14ceca9bd569 ,__dirname=0x14ceca9bd541 )
    8: _compile [module.js:541] [pc=0x1e3962a41944] (this=0x14ceca9bd321 #4#,content=0x14ceca9bec19 #7#,filename=0x14ceca9bd569 )
    9: .js [module.js:550] [pc=0x1e3962a3a46b] (this=0x14ceca9bad31 #1#,module=0x14ceca9bd321 #4#,filename=0x14ceca9bd569 )
   10: load [module.js:456] [pc=0x1e3962a38e72] (this=0x14ceca9bd321 #4#,filename=0x14ceca9bd569 )
   11: tryModuleLoad(aka tryModuleLoad) [module.js:415] [pc=0x1e3962a3899d] (this=0x1e993eb04189 ,module=0x14ceca9bd321 #4#,filename=0x14ceca9bd569 )
   12: _load [module.js:407] [pc=0x1e3962a345e2] (this=0x14ceca9badb1 #3#,request=0x14ceca956b29 ,parent=0x14ceca9c0201 #8#,isMain=0x1e993eb04299 )
   13: require [module.js:466] [pc=0x1e3962a42573] (this=0x14ceca9c0201 #8#,path=0x14ceca956b29 )
   14: require(aka require) [internal/module.js:20] [pc=0x1e3962a422a6] (this=0x1e993eb04189 ,path=0x14ceca956b29 )
   15: /* anonymous */ [/home/oleh/node/sqlite3.js:1] [pc=0x1e3962a41c2b] (this=0x14ceca9c03b9 #9#,exports=0x14ceca9c03b9 #9#,require=0x14ceca9c0291 #10#,module=0x14ceca9c0201 #8#,__filename=0x14ceca9c0381 ,__dirname=0x14ceca9c0359 )
   16: _compile [module.js:541] [pc=0x1e3962a41944] (this=0x14ceca9c0201 #8#,content=0x14ceca9c0621 ,filename=0x14ceca9c0381 )
   17: .js [module.js:550] [pc=0x1e3962a3a46b] (this=0x14ceca9bad31 #1#,module=0x14ceca9c0201 #8#,filename=0x14ceca9c0381 )
   18: load [module.js:456] [pc=0x1e3962a38e72] (this=0x14ceca9c0201 #8#,filename=0x14ceca9c0381 )
   19: tryModuleLoad(aka tryModuleLoad) [module.js:415] [pc=0x1e3962a3899d] (this=0x1e993eb04189 ,module=0x14ceca9c0201 #8#,filename=0x14ceca9c0381 )
   20: _load [module.js:407] [pc=0x1e3962a345e2] (this=0x14ceca9badb1 #3#,request=0x14ceca9c0811 ,parent=0x1e993eb04101 ,isMain=0x1e993eb04231 )
   21: runMain [module.js:575] [pc=0x1e3962a340aa] (this=0x14ceca9badb1 #3#)
   22: startup(aka startup) [node.js:159] [pc=0x1e396294085e] (this=0x1e993eb04189 )
   23: /* anonymous */(aka /* anonymous */) [node.js:444] [pc=0x1e396293e4f2] (this=0x1e993eb04101 ,process=0x1e993ebe4d89 #11#)
=====================


==== C stack trace ===============================

 1: v8::Template::Set(v8::Local, v8::Local, v8::PropertyAttribute)
 2: node_sqlite3::Statement::Init(v8::Local)
 3: 0x7f81237df4b6
 4: node::DLOpen(v8::FunctionCallbackInfo const&)
 5: v8::internal::FunctionCallbackArguments::Call(void (*)(v8::FunctionCallbackInfo const&))
 6: 0x98b90b
 7: 0x98beb1
 8: 0x1e396290961b

Windows phone application exiting on launch

I have build an application for windows phone 8.0. The app is using SQLite database and is building and running well on the emulator. Besides, have also added my database (Data.sqlite) to the root of my project and copied to the directory(Copy if newer). However, it did not pass certification after submitting it to the store and being reviewed by the store app certification team, and i was issued with this error. The application exits on launch.

How can i solve the above.

Are SQLite result sets in-memory data structures?

I currently find myself needing to do fairly simple computations on several million datapoints. (Constructing a large list of strings from a well defined multi-gigabite file, sorting that list, and then comparing it to another list, a superset.) This is the sort of simple work most of us normally do with the data entirely in-memory, but the size and quantity of the units of data I need to work with could make RAM an issue if I try to keep everything in memory. I quickly realized I probably need to write the data to a file, at a few points, to avoid exhausting my system's resources. I decided to use SQLite3 for this. (This is probably a bit much for a CSV.) It is fairly lightweight, while its storage limits seem to safely exceed my requirements.

The problem I am having is the understanding exactly how the result set works. The documentation I have come across seems a little vague on this. Obviously, SQLite is not writing a whole new table to the database every time a SELECT statement is executed. Does this mean it is duplicating all the selected fields in a complete in-memory table, or does it only keep some sort of pointers in memory (rather than the actual data)? Something else altogether?

I need to be able to sort the data in question. If the result set is really just an in-memory data structure, than simply creating creating a new table and populating it with the help of ORDER BY could be a bad idea.

Android Sqlite - using integer in selection arguement

I have searched all SO questions but i don't think, this kind of question is asked. I have a database in which one of the column is of integer type(mobilenumber integer). I want to perform SELECT operation and using query(...) to perform the operation. In query(...), there is "selection args", I want to know how can I put Integer value in selection arguement where it takes string array for the arguements.

My query statement:

db.query(DataHandler.TABLE_CONTACTS, null, DataHandler.KEY_MOBILE + "= ?", new String[]{mobile}, null, null, null);

I am retrieving value of mobile number from edittext and the query is inside method.

ViewData(String mobile){
    db.query(DataHandler.TABLE_CONTACTS, null, DataHandler.KEY_MOBILE + "= ?", new String[]{mobile}, null, null, null);
    ....
}

Set two spinner from database, second spinner according to first one

There are two spinner named sp_name and sp_vadya. I am not able to set the second spinner value according to the first spinner which comes from database. Here is my code, more detail is in java code what i want to do. Help to set spinner.

Thanks in advance.

Ghosh.xml

<?xml version="1.0" encoding="utf-8"?>
    <LinearLayout xmlns:android="http://ift.tt/nIICcg"
        android:layout_width="match_parent"
        android:layout_height="fill_parent"
        android:background="#ddeeff"
        android:orientation="vertical" >

        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_marginTop="10dp" >

            <TextView
                android:id="@+id/tv_name"
                android:layout_width="100dp"
                android:layout_height="wrap_content"
                android:layout_marginLeft="5dp"
                android:text="Name"
             android:textAppearance="?android:attr/textAppearanceLarge" />

            <Spinner
                android:id="@+id/sp_name"
                android:layout_width="match_parent"
                android:layout_height="wrap_content" />
        </LinearLayout>

        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content" >

            <TextView
                android:id="@+id/tv_vadya"
                android:layout_width="100dp"
                android:layout_height="wrap_content"
                android:layout_marginLeft="5dp"
                android:text="Vadya"
                android:textAppearance="?android:attr/textAppearanceLarge" />

            <Spinner
                android:id="@+id/sp_vadya"
                android:layout_width="match_parent"
                android:layout_height="wrap_content" />
        </LinearLayout>

        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content" >

            <TextView
                android:id="@+id/tv_rachna"
                android:layout_width="100dp"
                android:layout_height="wrap_content"
                android:layout_marginLeft="5dp"
                android:text="Rachna"
                android:textAppearance="?android:attr/textAppearanceLarge" />
        </LinearLayout>
    </LinearLayout>

Ghosh.java

public class Ghosh extends Activity {

                Button btn_deleteghosh, btn_updateghosh;
                Spinner sp_name, sp_vadya;
                EditText edt_rachna;
                ArrayList<String> arlst_name, arlst_vadya, arlst_rachna, arlst_sid;
                ArrayAdapter<String> arad_vadya, arad_rachna, arad_name;
                Database database;

                protected void onCreate(Bundle savedInstanceState) {
                    super.onCreate(savedInstanceState);
                    setContentView(R.layout.ghosh);

                    btn_deleteghosh = (Button) findViewById(R.id.btn_deleteghosh);
                    btn_updateghosh = (Button) findViewById(R.id.btn_updateghosh);

                    edt_rachna = (EditText) findViewById(R.id.edt_rachna);

                    sp_name = (Spinner) findViewById(R.id.sp_name);
                    sp_vadya = (Spinner) findViewById(R.id.sp_vadya);

                    // ===========For VADYA============
                    arlst_vadya = new ArrayList<String>();

                    arlst_vadya.add("VANSHI");
                    arlst_vadya.add("AANAK");
                    arlst_vadya.add("SANKH");
                    arlst_vadya.add("PARNA");
                    arlst_vadya.add("TRIBHUJ");
                    arlst_vadya.add("SEXOPHONE");
                    arlst_vadya.add("None");

                    ArrayAdapter<String> arad_vadya = new ArrayAdapter<String>(Ghosh.this,
                            android.R.layout.simple_spinner_item, arlst_vadya);

                    sp_vadya.setAdapter(arad_vadya);

                    loaddata();



     *//here load data function will be for the fetching the data from the database

            // if in database there r 3 records
            //1) name: abc     vadya:TRIBHUJ
            //2) name:pqr      vadya:None
            //3) name:xyz      Vadya:SANKH

            // now if i will select name pqr then sp_vadya will have to set automatically by none. That is my exact problem.*


                    sp_name.setOnItemSelectedListener(new OnItemSelectedListener() {

                        @Override
                        public void onItemSelected(AdapterView<?> parent, View view,
                                int pos, long id) {
                            // TODO Auto-generated method stub

                             sp_vadya.setSelection(arlst_vadya.indexOf(pos));

                            edt_rachna.setText(arlst_rachna.get(pos).toString());

                        }

                        @Override
                        public void onNothingSelected(AdapterView<?> arg0) {
                            // TODO Auto-generated method stub

                        }
                    });

                    btn_updateghosh.setOnClickListener(new OnClickListener() {

                        @Override
                        public void onClick(View v) {
                            // TODO Auto-generated method stub

                            Intent i = new Intent(Ghosh.this, UpdateGhoshActivity.class);
                            startActivity(i);

                        }
                    });

                    btn_deleteghosh.setOnClickListener(new OnClickListener() {

                        @Override
                        public void onClick(View v) {
                            // TODO Auto-generated method stub

                        }
                    });

                }

                public void loaddata() {
                    arlst_name = new ArrayList<String>();
                    arlst_sid = new ArrayList<String>();
                    arlst_vadya = new ArrayList<String>();
                    arlst_rachna = new ArrayList<String>();

                    try {

                        database = new Database(Ghosh.this);
                        database.open();

                        Cursor c = database.getGhoshdetail();

                        if (c.moveToFirst()) {
                            do {
                                arlst_sid.add(c.getString(0));
                                arlst_vadya.add(c.getString(1));
                                arlst_rachna.add(c.getString(2));
                                arlst_name.add(c.getString(3));

                                Log.d("sid in ghosh", c.getString(0));
                                Log.d("vadya in ghosh", c.getString(1));
                                Log.d("rachna in ghosh", c.getString(2));
                                Log.d("name in ghosh", c.getString(3));

                            } while (c.moveToNext());
                            if (c != null && c.isClosed()) {
                                c.close();
                            }

                            ArrayAdapter<String> arad_name = new ArrayAdapter<String>(
                                    Ghosh.this, android.R.layout.simple_spinner_item,
                                    arlst_name);
                            sp_name.setAdapter(arad_name);

                            c.close();
                            database.close();
                        }

                    } catch (Exception e) {
                        // TODO: handle exception
                    }
                }

            }

when i convert CSV file and saves it to database it saves data in database between " ",so how can i remove " "

'this is my comma separated value file conversion code , string deliminator = ",";

 string tablename = "Converted";
        //string filelink = FileName.Text;
        string fileintro = FileName.Text;
        DataSet DS = new DataSet();
        StreamReader SR = new StreamReader(fileintro.Trim());
        DS.Tables.Add(tablename);

    DS.Tables[tablename].Columns.Add("A");
    DS.Tables[tablename].Columns.Add("B");
    DS.Tables[tablename].Columns.Add("C");
    DS.Tables[tablename].Columns.Add("D");
    DS.Tables[tablename].Columns.Add("E");
    DS.Tables[tablename].Columns.Add("F");
    DS.Tables[tablename].Columns.Add("G");
    DS.Tables[tablename].Columns.Add("H");
    DS.Tables[tablename].Columns.Add("I");

SQLiteConnectionconn=newSQLiteConnection(ConfigurationManager.ConnectionStrings["dbcon"].ConnectionString);

SQLiteCommand cmd = new SQLiteCommand ("INSERT INTO ConvertData (a,b,c,d,e,f) values ('"+a+"'...), con);

                cmd.ExecuteNonQuery();
                co.Close();
                FileName.Text = "";
   }

it saves data in database table like "1,2,3,4,5,6,7" so how can i save it normally like 1,2,3,4,5,6,into database columns.'

Why is my SQLite database keep adding those data that have been added?

I just want my SQLite databse add the data one time, but My SQLite database keeps adding those data that have been added when I launch my app? How could I solve this problem? Can someone help me? I appreciate it.

Here is my java main class:

public class MainActivity extends AppCompatActivity implements AdapterView.OnItemClickListener {
public WebView web;
public DBHelper AsongHelper;
@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);
    initial();
    Insertdata();

}
public void initial() {
    AsongHelper = new DBHelper(this);
    AsongHelper.getWritableDatabase();
    AsongHelper.getReadableDatabase();
    ListView ml = (ListView) findViewById(R.id.listView1);
    List<listview_item>Songlist = AsongHelper.getSongsName();
    listview_adapter ada = new listview_adapter(this, Songlist);
    ml.setAdapter(ada);
    ml.setOnItemClickListener(this);
    web = (WebView) findViewById(R.id.webView1);
    WebSettings webSettings = web.getSettings();
    webSettings.setJavaScriptEnabled(true);
    web.getSettings().setCacheMode(webSettings.LOAD_CACHE_ELSE_NETWORK);
    web.setWebViewClient(
            new WebViewClient() {
                @Override
                public boolean shouldOverrideUrlLoading(WebView view, String url) {
                    return false;
                }
            }
    );
    web.loadUrl("http://youtu.be/bMqKM9lqX7M");
}
@Override
public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
    List<String> UrlList =AsongHelper.geturl();
    web.loadUrl(UrlList.get(position));}
public void Insertdata(){
    AsongHelper.insertdata("小幸运", "https://youtu.be/bMqKM9lqX7M", "Jason Chen");
    AsongHelper.insertdata("Love Yourself","https://youtu.be/oyEuk8j8imI","Justin Bieber");
    AsongHelper.insertdata("Gentleman","https://youtu.be/lGVKrj6gpnI","Will Jay");
}
}

Here is my database class:

public class DBHelper extends SQLiteOpenHelper{

public static final String DATABASE_NAME = "ASong.op";
public static final String ASONG_TABLE_NAME = "Asongs";
public static final String ASONG_COLUMN_ID = "id";
public static final String ASONG_COLUMN_SONGSNAME = "SongsName";
public static final String ASONG_COLUMN_URL = "url";
public static final String ASONG_COLUMN_ARTIST = "artist";

public DBHelper(Context context)
{
    super(context, DATABASE_NAME , null, 1);
}
@Override
public void onCreate(SQLiteDatabase db) {
    // TODO Auto-generated method stub
    db.execSQL( "create table Asongs " + "(id integer primary key, SongsName text,url text,artist text)"
    );
}
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // TODO Auto-generated method stub
    db.execSQL("DROP TABLE IF EXISTS Asongs");
    onCreate(db);
}
public boolean insertdata  (String songsname, String url, String artist)
{
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put(ASONG_COLUMN_SONGSNAME,songsname);
    contentValues.put(ASONG_COLUMN_URL,url);
    contentValues.put(ASONG_COLUMN_ARTIST, artist);
    db.insert(ASONG_TABLE_NAME, null, contentValues);
    return true;
}
public List<listview_item> getSongsName(){
    SQLiteDatabase db = this.getReadableDatabase();
    List<listview_item> SongsNameList = new ArrayList<>();
    String[] SongsName={ASONG_COLUMN_SONGSNAME};

    Cursor cusor =  db.query(ASONG_TABLE_NAME,SongsName,null,null,null,null,null);
    //StringBuffer buffer = new StringBuffer();
    while(cusor.moveToNext()){
        int index1 = cusor.getColumnIndex(ASONG_COLUMN_SONGSNAME);
        String cid = cusor.getString(index1);
        //buffer.append(cid);
        SongsNameList.add(new listview_item(cid));
        cusor.close();
      ;}
    return SongsNameList;
}
public List<String> geturl(){
    List<String> SongsUrlList = new ArrayList<>();
    SQLiteDatabase db = this.getReadableDatabase();
    String[] ColumnUrl={ASONG_COLUMN_URL};
    Cursor cusor =  db.query(ASONG_TABLE_NAME,ColumnUrl,null,null,null,null,null);
    StringBuffer buffer = new StringBuffer();
    while(cusor.moveToNext()){
        int index2 = cusor.getColumnIndex(ASONG_COLUMN_URL);
        String cid2 = cusor.getString(index2);
        buffer.append(cid2);
        SongsUrlList.add(buffer.toString());
    }
    cusor.close();
    return SongsUrlList;

}
}

How do I insert string to sqlite in Objective-C?

I could not insert NSMutableString to sqlite. I get the information successfully from web server and create the sqlite file successfully but I could not insert the data from web to sqlite.

I think the problem is here.

NSString *insertSQL = @"INSERT INTO Questions VALUES (result)";

but I am not sure and I could not solve this problem. Could anyone help me please?

- (void)getInforamtionFromWeb {
    NSURL *url = [NSURL URLWithString:kGetUrl];
    data = [NSData dataWithContentsOfURL:url];
    NSError *error;
    json = [NSJSONSerialization JSONObjectWithData:data options:kNilOptions error:&error];
    result = [[NSMutableString alloc] init];
    for (NSObject * obj in json)
    {
        [result appendString:[obj description]];
    }
}

-(void)initiatSqlite{
    // Get the documents directory
    dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    docsDir = dirPaths[0];
    // Build the path to the database file
    _databasePath = [[NSString alloc]
                     initWithString: [docsDir stringByAppendingPathComponent:
                                      @"Questions.db"]];
    NSFileManager *filemgr = [NSFileManager defaultManager];
    if ([filemgr fileExistsAtPath: _databasePath ] == NO)
    {
        const char *dbpath = [_databasePath UTF8String];
        if (sqlite3_open(dbpath, &_contactDB) == SQLITE_OK)
        {
            char *errMsg;
            const char *sql_stmt =
            "CREATE TABLE IF NOT EXISTS Questions (ID INTEGER PRIMARY KEY AUTOINCREMENT, Question Text, AnswerA Text, AnswerB Text, AnswerC Text, AnswerD Text, CorrectAnswer Text, Explanation Text)";
            if (sqlite3_exec(_contactDB, sql_stmt, NULL, NULL, &errMsg) != SQLITE_OK)
            {
                [self Worningtitle:@"Error" Message:@"Failed to create table"];
            }
            sqlite3_close(_contactDB);
        } else {
            [self Worningtitle:@"Error" Message:@"Failed to open/create database"];

        }
    }

}
- (void) insertData
{
    sqlite3_stmt    *statement;
    const char *dbpath = [_databasePath UTF8String];


    if (sqlite3_open(dbpath, &_contactDB) == SQLITE_OK)
    {
        NSString *insertSQL = @"INSERT INTO Questions VALUES (result)";

        const char *insert_stmt = [insertSQL UTF8String];
        NSLog(@"%s",insert_stmt);
        sqlite3_prepare_v2(_contactDB, insert_stmt, -1, &statement, NULL);
        sqlite3_bind_text(statement, 1, [result UTF8String], -1, SQLITE_TRANSIENT);

        if (sqlite3_step(statement) == SQLITE_DONE)
        {
            NSLog(@"Product added");
        } else {
            NSLog(@"Failed to add Product");
        }

        sqlite3_finalize(statement);
        sqlite3_close(_contactDB);
    }
}

SQLite Database Nullpointer Exception?

I need some helps with extracting my data out of my SQLite Database. I Can't find where are errors at. Can someone help me? I appreciate it.

Here is the Main Activity:

enter cpublic class MainActivity extends AppCompatActivity implements AdapterView.OnItemClickListener {
public WebView web;
public DBHelper AsongHelper;


@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);
    initial();
    Insertdata();
}
public void initial() {
    ListView ml = (ListView) findViewById(R.id.listView1);
    List<listview_item>Songlist = AsongHelper.getSongsName();
    listview_adapter ada = new listview_adapter(this, Songlist);
    ml.setAdapter(ada);
    ml.setOnItemClickListener(this);
    web = (WebView) findViewById(R.id.webView1);
    WebSettings webSettings = web.getSettings();
    webSettings.setJavaScriptEnabled(true);
    web.getSettings().setCacheMode(webSettings.LOAD_CACHE_ELSE_NETWORK);
    web.setWebViewClient(
            new WebViewClient() {
                @Override
                public boolean shouldOverrideUrlLoading(WebView view, String url) {
                    return false;
                }
            }
    );
    web.loadUrl("http://youtu.be/bMqKM9lqX7M");
}
@Override
public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
    List<String> UrlList =AsongHelper.geturl();
    web.loadUrl(UrlList.get(position));}
public void Insertdata(){
    AsongHelper.insertdata("小幸运", "https://youtu.be/bMqKM9lqX7M", "Jason Chen");
    AsongHelper.insertdata("Love Yourself","https://youtu.be/oyEuk8j8imI","Justin Bieber");
    AsongHelper.insertdata("Gentleman","https://youtu.be/lGVKrj6gpnI","Will Jay");
}
}

Here is my database helper class:

public class DBHelper extends SQLiteOpenHelper{

public static final String DATABASE_NAME = "ASong.op";
public static final String ASONG_TABLE_NAME = "Asongs";
public static final String ASONG_COLUMN_ID = "id";
public static final String ASONG_COLUMN_SONGSNAME = "SongsName";
public static final String ASONG_COLUMN_URL = "url";
public static final String ASONG_COLUMN_ARTIST = "artist";
private HashMap hp;
public DBHelper(Context context)
{
    super(context, DATABASE_NAME , null, 1);
}
@Override
public void onCreate(SQLiteDatabase db) {
    // TODO Auto-generated method stub
    db.execSQL(
            "create table Asongs " + "(id integer primary key, SongsName text,url text,artist text)"
    );
}
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // TODO Auto-generated method stub
    db.execSQL("DROP TABLE IF EXISTS Asongs");
    onCreate(db);
}
public boolean insertdata  (String songsname, String url, String artist)
{
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put(ASONG_COLUMN_SONGSNAME,songsname);
    contentValues.put(ASONG_COLUMN_URL,url);
    contentValues.put(ASONG_COLUMN_ARTIST, artist);
    db.insert("Asongs", null, contentValues);
    return true;
}
public List<listview_item> getSongsName(){
    SQLiteDatabase db = this.getReadableDatabase();
    List<listview_item> SongsNameList = new ArrayList<>();
    String[] SongsName={ASONG_COLUMN_SONGSNAME};
    Cursor cusor =  db.query(ASONG_TABLE_NAME,SongsName,null,null,null,null,null);
    StringBuffer buffer = new StringBuffer();
    while(cusor.moveToNext()){
        int index1 = cusor.getColumnIndex(ASONG_COLUMN_SONGSNAME);
        String cid = cusor.getString(index1);
        buffer.append(cid);
        SongsNameList.add(new listview_item(buffer.toString()));
    }
    cusor.close();
    return SongsNameList;
}
public List<String> geturl(){
    List<String> SongsUrlList = new ArrayList<>();
    SQLiteDatabase db = this.getReadableDatabase();
    String[] ColumnUrl={ASONG_COLUMN_URL};
    Cursor cusor =  db.query(ASONG_TABLE_NAME,ColumnUrl,null,null,null,null,null);
    StringBuffer buffer = new StringBuffer();
    while(cusor.moveToNext()){
        int index2 = cusor.getColumnIndex(ASONG_COLUMN_URL);
        String cid2 = cusor.getString(index2);
        buffer.append(cid2);
        SongsUrlList.add(buffer.toString());
    }
    cusor.close();
    return SongsUrlList;
}
}

CatLog is right here:

04-29 19:56:48.673 27116-27116/com.example.jinyu.myapplication E/AndroidRuntime: FATAL EXCEPTION: main
                                                                             Process: com.example.jinyu.myapplication, PID: 27116
                                                                             java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.jinyu.myapplication/com.example.jinyu.myapplication.MainActivity}: java.lang.NullPointerException: Attempt to invoke virtual method 'android.database.sqlite.SQLiteDatabase com.example.jinyu.myapplication.DBHelper.getReadableDatabase()' on a null object reference
                                                                                 at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2416)
                                                                                 at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2476)
                                                                                 at android.app.ActivityThread.-wrap11(ActivityThread.java)
                                                                                 at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1344)
                                                                                 at android.os.Handler.dispatchMessage(Handler.java:102)
                                                                                 at android.os.Looper.loop(Looper.java:148)
                                                                                 at android.app.ActivityThread.main(ActivityThread.java:5417)
                                                                                 at java.lang.reflect.Method.invoke(Native Method)
                                                                                 at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:726)
                                                                                 at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:616)
                                                                              Caused by: java.lang.NullPointerException: Attempt to invoke virtual method 'android.database.sqlite.SQLiteDatabase com.example.jinyu.myapplication.DBHelper.getReadableDatabase()' on a null object reference
                                                                                 at com.example.jinyu.myapplication.MainActivity.initial(MainActivity.java:29)
                                                                                 at com.example.jinyu.myapplication.MainActivity.onCreate(MainActivity.java:25)
                                                                                 at android.app.Activity.performCreate(Activity.java:6251)
                                                                                 at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1107)
                                                                                 at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2369)
                                                                                 at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2476) 
                                                                                 at android.app.ActivityThread.-wrap11(ActivityThread.java) 
                                                                                 at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1344) 
                                                                                 at android.os.Handler.dispatchMessage(Handler.java:102) 
                                                                                 at android.os.Looper.loop(Looper.java:148) 
                                                                                 at android.app.ActivityThread.main(ActivityThread.java:5417) 
                                                                                 at java.lang.reflect.Method.invoke(Native Method) 
                                                                                 at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:726) 
                                                                                 at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:616) 
04-29 19:56:50.553 27116-27116/com.example.jinyu.myapplication I/Process:     Sending signal. PID: 27116 SIG: 9

Are SQLiteDatabase writes done asynchronously

I'm using an SQLiteDatabase. Records are displayed on the screen and I have a method loadRecords() which loads and displays records.

I create a dialog for adding a record. The callback calls loadRecords() but the new records don't appear. I must invoke it myself by pressing a button which calls the same method, loadRecords()... then the new ones appear.

I am not using another thread or an async task so I can't understand why this is. So my question is: Are DB writes done asynchronously? (hence why my records don't load). How can I handle this? Is there a way to wait until the db has been fully written to?

Updating sqlite database, when the column and row is not known before

Hi so Im trying to update my table, in which the user can select the column and row he wants to update though variables. Here what i have done so far. I am getting the error
sqlite3.OperationalError: near "Tree": syntax error

 def MDConfirm_btn_clicked(self,MGStudent_drop):

   Max_marks=int(self.MDMax_inp.text()) #take input from maximim  marks entry  

   Marks_scored= int(self.MDAch_inp.text()) #take input from maximim  marks entry  

   Percentage_scored = (Max_marks/Marks_scored)*100  #formula to find percentage

   Current_student = (self.MGStudent_drop.currentText()) #take data held in student name drop down

   Current_topic= (self.MGMarks_drop.currentText()) #take data held in topic drop down


   con = lite.connect('Student_names')#Links code to the data base file
   cur = con.cursor() 
   myquery=("UPDATE gradesdb SET {cn}=(?) WHERE Names=(?)".\
    format( cn=Current_topic))

   cur.execute(myquery,(Percentage_scored,Current_student))     
   con.commit()

How to prevent calling function twice in a single query with SQLite.swift?

I've created a custom function with SQLite.swift. However after checking, i see it calls this function twice in a single query. Here is mine

SELECT word1,customfunc('daydreamer', kd) as score FROM ngram1 WHERE kd LIKE 'd%r' ORDER BY score ASC,freq DESC LIMIT 0,4

In this query, i call customfunc and name it as score and use it in the sorting.

Normally, i thought SQLite will call to this customfunc only once time to get the score value and use it in the ordering?

Updating sqlite database, when the column and row is not known before

Hi so Im trying to update my table, in which the user can select the column and row he wants to update though variables. Here what i have done so far but im not really sure how to go about this any help would be great thanks.

    Max_marks=int(self.MDMax_inp.text()) #take input from maximim  marks entry  

   Marks_scored= int(self.MDAch_inp.text()) #take input from maximim  marks entry  

   Percentage_scored = (Max_marks/Marks_scored)*100  #formula to find percentage

   Current_student = (self.MGStudent_drop.currentText()) #take data held in student name drop down

   Current_topic= (self.MGMarks_drop.currentText()) #take data held in topic drop down

   con = lite.connect('Student_names')#Links code to the data base file
   cur = con.cursor() 
   cur.execute("UPDATE gradesdb SET {cn}=(?) WHERE {idf}=(?)"(Percentage_scored,Current_student).\
    format( cn=Current_topic, idf=Names))
   con.commit()

Why using SQLITE where clause does not work?

I am trying to create a SQLITE query like this (first approach):

        int count;
        using ( var db = new SQLiteConnection( new SQLitePlatformWinRT(), DbPath ) )
            {
            count = ( from p in db.Table<TickRecord>()
                      where (p.TickStartDate.LocalDateTime >= start && p.TickEndtDate.LocalDateTime <= end )
                      select (int)p.DurationInSeconds ).Sum();
            }
        return count;

When running the query the application crash on the where clause.

I was able to achieve it like this (second approach):

        ObservableCollection<TickRecord> records;

        // Create a new connection
        using ( var db = new SQLiteConnection( new SQLitePlatformWinRT(), DbPath ) )
           {
           records = new ObservableCollection<TickRecord>( db.Table<TickRecord>().Select( i => i ) );
           }

        int count = records.Where( record => record.TickStartDate.LocalDateTime >= start && record.TickEndDate.LocalDateTime <= end ).Sum( record => record.DurationInSeconds );

Is there a way to achieve the same using my first approach?

Thx

C# System Argument Out of Range Exception [on hold]

I am sorry if this question is formatted incorrectly, I am getting a System.Argument Out Of Range Exception. Error Message - Additional information: InvalidArgument=Value of '-1' is not valid for 'index'. I am connected to SQLite Database using ServiceStack. The database is published Microsoft Azure. This is my second table, POST worked fine with the first table. GET, PUT and DELETE work fine.
My Code

Escaping special characters in sqlite when using = in query in android

I have database with a column itemitem that looks like this

aaabbb

aaa%bbb

aaa_bbb

aaa'bbb

My query looks like this where itemitems is one of the above fields;

    itemitems = itemitems.replaceAll("'","''");
    itemitems = itemitems.replaceAll("%","\\\\%");  // use \\\\ to generate \%
    itemitems = itemitems.replaceAll("_","\\\\_");

    itemitems = "'" + itemitems + "'";
    String query = "select * from " + table_ITEMS + " where itemitem = " + itemitems + " ESCAPE '\\'";

I get the following exception:

SQLiteException: near "ESCAPE": syntax error (code 1): , while compiling: select * from items where itemitem = 'aaa\_bbb' ESCAPE '\'

Note that I had to add an additional \ in the last line above for it to show \_. Even this entry field has problems with \.

However if I use itemitem like instead of itemitem =, I don't get the exception. But, I don't get the exact match either. This works fine for finding aaa'bbb with the itemitem = query.

It seems that the = query doesn't accept the ESCAPE clause. How can I escape the % and _ special characters?

How do I convert dates in SqlLite from m/d/y to Y-%m-%d

I have a set of data that I am importing into SqlLite, but don't really have the opportunity to manipulate it before insertion. I am trying to calculate "age" of the date, but this is proving very difficult in it's current format.

I am looking for a select that I can use to update the data and then begin writing queries the way I want.

Data Samples
9/20/1983
2/18/1986
8/1/1994
5/29/1999

Desired
1983-09-20
1986-02-18
1994-08-01
1999-05-29

Once I have data in that format, I will calculate the date using the following

(strftime('%Y', 'now') - strftime('%Y', Born)) - (strftime('%m-%d', 'now') < strftime('%m-%d', BOrn))

I guess if there's a way to cast the date into the right format AND calculate the age in one query, that would save a step, but I havent been able to find a way so far.

Achieving a robust connection to SQLite from

I am creating a connection to an SQLite database (using package RSQLite) in the following way:

fPath <- file.path(getwd(), "db_name.sqlite")
db <<- dbConnect(SQLite(), dbname = fPath)

This allows me to perform queries in multiple functions, in multiple ways, without having to reconnect, pass in the connection object 'db'

A typical query looks like

dbGetQuery(db, "SELECT * FROM table;")
dbWriteTable(df, "table", data, append = TRUE)

I am generating and storing a lot of data, and also modifying the R code I am using, table structures etc. as I go.

I decided to close my R session, and reload. When I reconnected to the database, using the method above, I found that the state of the database was old; old tables, missing data.

It seems that somehow the changes I made and data I stored went to a different database than the one I specified in fPath. I cannot locate a separate database on my machine.

Why/how would this happen? Does SQLite create a temporary database if the original connection is lost? How can I make a connection robust? Losing data / changes is obviously unpleasant.

Is there a way of confirming the file path to an open connection?

e.g.

dbGetInfo(db)$path
summary(db)

Azure/Xamarin Mobile App Hangs at SyncContext.InitializeAsync

So, I had a working xamarin forms app that used azure mobile services. I upgraded the server side to use mobile app service and upgraded the nugets in my client to use the latest and greatest client codes.

I manually updated my test/ios simulator sqlite db to use the proper column names for the system properties since they dropped the double underscore prefix.

When I finally got it all built and tried to run in the ios 6/8.3 simulator it ran fine until it hit the InitializeAsync method. It doesn't throw (its in a try catch) and I let it run for a long time and it just sits there.

I then tried to change the db name so it would make it from scratch, which it did but still just hung. So I then tried to simplify it to a single table and took out my delegating handler so it was as basic as I could get it and it still just hangs.

Has anyone else had this problem? I am at a complete loss because I don't get an error. Not sure where to start.

Thanks.

SQLite delete item

i would like to know, how to delete item from a listview and SQLite database. I have this code. What should i change because items are not deleting from the listview.

public void onCreateContextMenu(ContextMenu menu, View v, 
ContextMenu.ContextMenuInfo menuInfo) 
{
        super.onCreateContextMenu(menu,v,menuInfo);
        menu.add("Delete");
    }

    public boolean onContextItemSelected(MenuItem item)
    {
        super.onContextItemSelected(item);

        if(item.getTitle()== "Delete")
        {
            books.remove(item);
            adapter.notifyDataSetChanged();
        }
        return true;
    }

Multiple condition for SQLITE Triggers

I need to update a field on a different table if the current table that is declared on the trigger gets updated. But I need to update only after 2 conditions

    public class Match{
        public int participant1;
        public int participant2;
        public int winner;
        public boolean finalMatch;
        public int standingId;
    }

        public class Standings{
            public int id;
            public int firstPlace;
            public int SecondPlace;
        }

private static final String CREATE_TRIGGER_MATCH_UPDATE_BRACKET_WINNERS_F1 = "CREATE TRIGGER if not exists updatefields
        AFTER UPDATE OF  winner
            ON Match
            WHEN new.finalMatch = 1 AND new.winner = new.participant1
            BEGIN
            UPDATE Standings
                SET firstPlace = new.winner
                    SecondPlace = new.participant2
            WHERE id = new.standingId
        END;"

But this throws an error. Apparently there can only be on "WHEN condition" for triggers on android. Now I'm not really sure how to go about checking it. As I would like to do this update of the two fields on one trigger. If I do two triggers, 1 for the setting up of "firstPlace" and another for the "secondPlace", the second one will be on the Standings table and I'm not really sure how to get access to that "secondPlace" value.

Movie Reviews iOs Application Backend (Sqlite, API, Firebase)

I just started recently to learn iOs development.

I am trying to make a movie database app, the app will be about movies and tv shows information, reviews, ratings, etc Almost similar to IMDB

I will be targeting local arabic movies and tv shows thus I wouldn't be able to use the available API's like the open movies database api.

Can you please guide me how can I do it in this case.

Is it possible to use a database and refresh the data without the need to upload the app to the market and let the users download the new version, because I will let the users contribute to the data.

Do I need to develop a website with a database and create my own API

Would Firebase be an ideal solution to my case !

I appreciate any help you can provide to keep me move forward

Thanks.

SQLITE TWO PRIMARY KEY

I use a sqllite and I try to create this.

idText idNum  
abc      1    
abd      1     
abc      2 

Whith MySQL I did this but in sqllite is it possible?

SQLite filter data

i want to filter my SQLite database and i want to ask in which way it is done? When i used searchView, after one search, i can not add items to database. Should i use editText to filter?

My code: DBAdapter.java

static final String ROW_ID ="id";
static final String NAME ="name";
static final String TAG = "DBAdapter";

static final String DBNAME="m_DB";
static final String TBNAME="m_TB";
static final int DBVERSION='1';

static final String CREATE_TB="CREATE TABLE m_TB(id INTEGER PRIMARY KEY AUTOINCREMENT,"
        + "name TEXT NOT NULL);";

final Context c;
SQLiteDatabase db;
DBHelper helper;
public DBAdapter(Context ctx) {
    this.c = ctx;
    helper = new DBHelper(c);
}


private static class DBHelper extends SQLiteOpenHelper {
    public DBHelper(Context context) {
        super(context, DBNAME, null, DBVERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        try {
            db.execSQL(CREATE_TB);
        } catch (SQLException e)
        {
            e.printStackTrace();
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.w("DBAdapter","Upgrading DB");
        db.execSQL("DROP TABLE IF EXISTS m_TB");
        onCreate(db);
    }
}
public DBAdapter openDB()
{
    try {
        db=helper.getWritableDatabase();
    } catch (SQLException e)
    {
        Toast.makeText(c, e.getMessage(), Toast.LENGTH_LONG).show();
    }
    return this;
}

public void close()
{
    helper.close();
}

public long add(String name)
{
    try {
        ContentValues cv = new ContentValues();
        cv.put(NAME,name);
        return db.insert(TBNAME,ROW_ID,cv);
    } catch (SQLException e)
    {
        e.printStackTrace();
    }
    return 0;
}

public Cursor getAllNames()
{
    String[] columns={ROW_ID,NAME};
    return db.query(TBNAME,columns,null,null,null,null,null);
}

MainActivity.java

public class MainActivity extends AppCompatActivity {


    ListView lv;
    EditText nameTxt;
    Button savebtn,retrievebtn;
    ArrayList<String> books = new ArrayList<String>();
    ArrayAdapter<String> adapter;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        nameTxt=(EditText)findViewById(R.id.editText);

        savebtn=(Button)findViewById(R.id.saveBtn);
        retrievebtn=(Button)findViewById(R.id.retrieveBtn);

        lv = (ListView)findViewById(R.id.listView1);

        adapter = new ArrayAdapter<String>(this,android.R.layout.simple_selectable_list_item,books);
        final DBAdapter db = new DBAdapter(this);

        savebtn.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                db.openDB();
                long result = db.add(nameTxt.getText().toString());

                if(result > 0)
                {
                    nameTxt.setText("");
                }else
                {
                    Toast.makeText(getApplicationContext(),"Failure", Toast.LENGTH_SHORT).show();
                }
                db.close();
            }
        });

        retrievebtn.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                books.clear();
                db.openDB();
                Cursor c = db.getAllNames();

                while (c.moveToNext())
                {
                    String name = getString(1);
                    books.add(name);
                }
                lv.setAdapter(adapter);
                db.close();
            }
        });
        lv.setOnItemClickListener(new AdapterView.OnItemClickListener() {
            @Override
            public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
                Toast.makeText(getApplicationContext(),books.get(position), Toast.LENGTH_SHORT).show();
            }
        });
    }

How to install SQLite Entity Framework 1.0.99.0 by nuget command

Currently SQLite has version 1.0.101.0. But its visual studio design time installer has trojen (as windows defender showing on windows 10 OS).

So, I want to install SQLite Entity Framework 1.0.99.0 in my visual studio 2013, winform project. This version has correct design time installer also.

I tried this command in package manager console.

install-Package System.Data.SQLite -version 1.0.99.0

and the result is the following.

Attempting to resolve dependency 'System.Data.SQLite.Core (≥ 1.0.99.0)'.
Attempting to resolve dependency 'System.Data.SQLite.Linq (≥ 1.0.99.0)'.
Attempting to resolve dependency 'System.Data.SQLite.EF6 (≥ 1.0.99.0)'.
Attempting to resolve dependency 'EntityFramework (≥ 6.0.0)'.
Installing 'System.Data.SQLite.Core 1.0.101.0'.
Successfully installed 'System.Data.SQLite.Core 1.0.101.0'.
Installing 'System.Data.SQLite.Linq 1.0.101.0'.
Successfully installed 'System.Data.SQLite.Linq 1.0.101.0'.
Installing 'EntityFramework 6.0.0'.
Successfully installed 'EntityFramework 6.0.0'.
Installing 'System.Data.SQLite.EF6 1.0.101.0'.
Successfully installed 'System.Data.SQLite.EF6 1.0.101.0'.
Installing 'System.Data.SQLite 1.0.99.0'.
Successfully installed 'System.Data.SQLite 1.0.99.0'.
Adding 'System.Data.SQLite.Core 1.0.101.0' to QuotMaster.
Successfully added 'System.Data.SQLite.Core 1.0.101.0' to QuotMaster.
Adding 'System.Data.SQLite.Linq 1.0.101.0' to QuotMaster.
Successfully added 'System.Data.SQLite.Linq 1.0.101.0' to QuotMaster.
Adding 'EntityFramework 6.0.0' to QuotMaster.
Successfully added 'EntityFramework 6.0.0' to QuotMaster.

Type 'get-help EntityFramework' to see all available Entity Framework commands.
Adding 'System.Data.SQLite.EF6 1.0.101.0' to QuotMaster.
Successfully added 'System.Data.SQLite.EF6 1.0.101.0' to QuotMaster.
Adding 'System.Data.SQLite 1.0.99.0' to QuotMaster.
Successfully added 'System.Data.SQLite 1.0.99.0' to QuotMaster.

It is installing the latest version. I am unable to install 1.0.99.0 version. I want to know how can I install this specific version.

Is there a way to use a column header in a database as a variable in Python?

I'll preface this saying I'm pretty new to python.

I have a database and need to access it using Python and SQLite. That's the easy part. I have created the table and column names etc, such as:

c.execute("CREATE TABLE Tanks \
(ID INT PRIMARY KEY NOT NULL,\
...
DIR_Radius INT NOT NULL,\
DIR_Dish1 INT NOT NULL,\
DIR_Dish2 INT NOT NULL,\
DIR_Length INT NOT NULL,\

And so on. So what I want to do is use the column header as a variable, i.e.

Dish1 = 'DIR_Dish1'

But as you may know in python that just assigns the string value to the variable name.

Saying

Dir_Dish1 > 0.001

Doesn't work either

def Dish_Vol():
if (Dish > 0.0001):
    int1 = math.pi * Dish,
    ...

I am trying to check that the value in the comlumn DIR_Dish1 has a integer value greater than 0.001, so that I can go ahead and perform some calculation. How do I go about using a variable as such?

SQLlite causes crash upon button pressed

I have an application in which I'm trying to add user info inside a table created using SQLlite. The problem is when I press the add button the application crashes. bellow I include all the code and logcat.

MainActivity.java

package com.example.asus.sqlliteproject;

import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

public class MainActivity extends AppCompatActivity {
    DataBaseHelper myDB;
    EditText Name,LastName,Grades;
    Button AddData;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        myDB = new DataBaseHelper(this);
        Name = (EditText)findViewById(R.id.name);
        LastName = (EditText)findViewById(R.id.lastName);
        Grades = (EditText)findViewById(R.id.Grades);
        AddData = (Button)findViewById(R.id.addDataButton);
        addData();
    }
    public void addData () {
        AddData.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                boolean inserted = myDB.insertData(Name.getText().toString(),
                        LastName.getText().toString(),
                        Grades.getText().toString());
                if (inserted){
                    Toast.makeText(MainActivity.this,"Text Inserted!", Toast.LENGTH_SHORT).show();
                } else
                    Toast.makeText(MainActivity.this,"Unsuccessful!", Toast.LENGTH_SHORT).show();
            }
        });
    }

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        // Inflate the menu; this adds items to the action bar if it is present.
        getMenuInflater().inflate(R.menu.menu_main, menu);
        return true;
    }

    @Override
    public boolean onOptionsItemSelected(MenuItem item) {
        // Handle action bar item clicks here. The action bar will
        // automatically handle clicks on the Home/Up button, so long
        // as you specify a parent activity in AndroidManifest.xml.
        int id = item.getItemId();

        //noinspection SimplifiableIfStatement
        if (id == R.id.action_settings) {
            return true;
        }

        return super.onOptionsItemSelected(item);
    }
}

DataBaseHelper.java

package com.example.asus.sqlliteproject;

import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * Created by Asus on 29.4.2016.
 */
public class DataBaseHelper extends SQLiteOpenHelper {

    public static final String DataBaseName = "student.db";
    public static final String DataTableName = "studentTable.db";
    public static final String ColID = "ID";
    public static final String ColName = "Name";
    public static final String ColLastName = "LastName";
    public static final String ColGrades = "Grade";

    public DataBaseHelper(Context context) {
        super(context, DataBaseName, null, 1);
        //SQLiteDatabase db = this.getWritableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("create table" + DataTableName + "(ID INTEGER PRIMARY KEY AUTOINCREMENT)," +
                "Name TEXT, LastName TEXT, Grade INTEGER");

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("Drop table if exists" + DataTableName);
        onCreate(db);
    }
    public boolean insertData (String name, String LastName, String Grades) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(ColName,name);
        contentValues.put(ColLastName,LastName);
        contentValues.put(ColGrades,Grades);
        long result = db.insert(DataTableName, null, contentValues);
        if (result == -1) {
            return false;
        }else
            return true;
    }
}

logcat error (separated into two imgs) and activity_main view

How to install extension-functions.c for sqlite3 on ubuntu

I use sqlite3 on Ubuntu and would like to add the acos and asin functions that are provided by the extension-functions.c file.

http://ift.tt/1WuSquJ

When I follow the instructions given in the source code, I get the following error and I didn't find any answer on how I could get this working. I tried on Ubuntu 15.04, 15.10 and now 16.04.

extension-functions.c: In function ‘print_elem’:
extension-functions.c:1942:10: warning: format ‘%lld’ expects argument of type ‘long long int’, but argument 3 has type ‘int64_t {aka long int}’ [-Wformat=]
   printf("%d => %lld\n", ee,c);
          ^

Here's what I did:

• I installed sqlite3, libsqlite3-0, libsqlite3-3:i386, and libsqlite3-dev.
• I downloaded the extension-functions.c into a new folder
• inside the new folder I executed $ gcc -fPIC -lm -shared extension-functions.c -o libsqlitefunctions.so as suggested in the source code.

=> and then I get the error mentioned above.

What am I doing wong?

Thanx a lot in advance for your help.

Cheers, Dom

How to connect SQLite with Responsive Frameworks using Visual Studio 2010?

I want help and explanation to connect the SQLite database with an application in C# ASP.NET MVC what should i use? bootstrap or such frameworks? I'd prefer to use Visual Studio 2010 if it's easy/ preferable or else what would you suggest for such use? How to connect with Visual Studio?

Thank you in advance

Wrong data type accept in sqlite while creating table

I have planed to create a editor for sqlite, I have created many part successfully but when I enter the create statement it is created successfully, but when I created with wrong data type it is also created successfully.
1) How is it possible?
2) If it is created, is it will affect in feature.
3) How can I control this.

I told about the below statement

CREATE TABLE tb_merbinee(name TEXeT,AGE INTEGER,Mark INTEGER);

Import an External SQLite database to my project

I'm interested in importing an external SQLite database to my project. When using the QT Quick Local Storage:

LocalStorage.openDatabaseSync("QQmlExampleDB", "1.0", "The Example QML SQL!", 1000000);

The problem is that, the program generates a NEW database, and if it's possible I'm interested in open an existing database.

Any idea?

Thank you very much!

How to Upgrade IOS app database in swift

I have an app with version 1.0 on app store which uses sqlite database. i already have some users of my app . Now I want to update my version to 1.1 with update in database file. when any user update the app, the database also get updated according the current version without deleting the version 1.0 sqlite data. I am using FMDB framework for database.

Load large number of data from sqlite db

I have more than 10000+ record in database. I want to load it on a table view. It will take 3 to 4 seconds to fetch all data from database and than load it on table view. Is there is more efficient way to increase the response and load the data in a uitableview? Here is my code for get All Data fro database

- (void)getAllData {
    NSString * convertInttoStr = [NSString stringWithFormat:@"%d", rowNumber];
    // Getting the database path.
    NSArray  *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *docsPath = [paths objectAtIndex:0];
    NSString *dbPath = [docsPath stringByAppendingPathComponent:@"iandroidquran_database 3.sqlite"];

    FMDatabase *database = [FMDatabase databaseWithPath:dbPath];
    [database open];
    NSString *sqlSelectQuery = [NSString stringWithFormat:
                                @"SELECT * FROM qSurahContent WHERE surahID=%@" ,
                                convertInttoStr];


    // Query result
    FMResultSet *resultsWithNameLocation = [database executeQuery:sqlSelectQuery];
    while([resultsWithNameLocation next]) {
        NSString *strID = [NSString stringWithFormat:@"%d",[resultsWithNameLocation intForColumn:@"surahID"]];
        NSString *strName = [NSString stringWithFormat:@"%@",[resultsWithNameLocation stringForColumn:@"surahContentArabic"]];

        NSLog(@"surahID = %@, surahName = %@",strID, strName);
        [surahId addObject:strID];
        [surahContentArabic addObject:strName];


    }
    [self.tblView reloadData];

    [database close];
}

Any solution?? Thanks

How to store Image location (fetching from filepicker/Intent) in Sqlite

I am new to Android Development. I am trying to store Image file location in Sqlite database. Image is received from Intent with any file manager or gallery. I want to store its real path into the database so i can retrieve it next time for any action. I don't want to use blob/dont want to store image in the database. I am not finding any better solutions. Please help.