jeudi 31 décembre 2015

SQlite update incremental counter

I have the following table:
Group | SubGroup | id
'A' | 'AA' | 2
'A' | 'AB' | 4
'A' | 'AC' | 6
'B' | 'BA' | 2
'B' | 'BB' | 9

And I would like to update the id column to have sequentially value for each group:
Group | SubGroup | id
'A' | 'AA' | 1
'A' | 'AB' | 2
'A' | 'AC' | 3
'B' | 'BA' | 1
'B' | 'BB' | 2

I used a query to ask the value and got it but don't know how to make the update.

CREATE TABLE temp_tbl (groupa TEXT, subgroup TEXT, num INTEGER);  
INSERT INTO temp_tbl(groupa,subgroup,num) VALUES ('A','AA',2);   
INSERT INTO temp_tbl(groupa,subgroup,num) VALUES ('A','AB',4);  
INSERT INTO temp_tbl(groupa,subgroup,num) VALUES ('A','AC',6);  
INSERT INTO temp_tbl(groupa,subgroup,num) VALUES ('B','BA',2);  
INSERT INTO temp_tbl(groupa,subgroup,num) VALUES ('B','BB',9); 

'A' | 'AA' | 1
'A' | 'AB' | 2
'A' | 'AC' | 3
'B' | 'BA' | 1
'B' | 'BB' | 2
Now I would like to update the field accordingly. How could I do it?

Thanks

how to put sqlite android local database to the server

im new in android programming and i need your help. im creating an application and i need to import sqlite database to web server and connect it to my app so i can upgrade and other stuff plz someone tell me how can i do that step by step ot what book should i read

think of this as you are creating an android application and its getting big so you need to save your data on some server and then your app should read that data and show them to the user plz help me how can i do that . thank you

TDD with slick and sqlite

So I'm trying to test my database directly instead of mocking stuff here. While mocking is fine and dandy in some cases, I want to explicitly test the database here.

See the following test-class:

package models

import org.scalatest.{BeforeAndAfter, Matchers, FlatSpec}
import org.scalatest.concurrent.ScalaFutures
import slick.driver.SQLiteDriver.api._
import scala.concurrent.ExecutionContext.Implicits.global
import org.scalatest.time.{Millis, Seconds, Span}
import org.scalactic.StringNormalizations._
import scala.concurrent.Future

/**
  * Created by Teolha on 30.12.15.
  */
class BookTest extends FlatSpec with Matchers with ScalaFutures with BeforeAndAfter {
  implicit val defaultPatience =
    PatienceConfig(timeout = Span(120, Seconds), interval = Span(10, Millis))

  val db = Database.forURL("jdbc:sqlite:/home/teolha/test.sqlite", driver = "org.sqlite.JDBC")

  private val books = Books.all

  before {
    db.run(setup)
  }

  after {
    db.run(tearDown)
  }

  val setup = DBIO.seq(
    (books.schema).create
  )

  val tearDown = DBIO.seq(
    (books.schema).drop
  )

  "Books" should "be empty at the beginning" in
  {
    whenReady(db.run(books.result))
    {
      result =>
      {
        result shouldBe empty
      }
    }
  }

  "Books" should "be a Sequence of Books with the appropriate length" in
  {
    // Preparation
    db.run(
      DBIO.seq
      (
        Books.add(0, "Awesome Title #1"),
        Books.add(1, "Gorgeous Sequel #2"),
        Books.add(2, "Bombastic prequel")
      )
    )

    whenReady(db.run(books.result))
    {
      result =>
      {
        result should have length 3
        result shouldBe a [Seq[_]] // Remember type erasure
        result(0) shouldBe a [Book]
      }
    }
  }

  "Books" should "be insertable as well as removable" in
  {
    whenReady(db.run(books.result))
    {
      result => result shouldBe empty
    }

    db.run(
      DBIO.seq
      (
        Books.add(0, "Awesome Title #1")
      )
    )

    whenReady(db.run(books.result))
    {
      result =>
      {
        result should have length 1
        result(0).title should equal ("nathanael mondae and the spear of destiny") (after being lowerCased)
      }
    }

    db.run(
      DBIO.seq
      (
        Books.delete(1)
      )
    )

    whenReady(db.run(books.result))
    {
      result => result shouldBe empty
    }
  }

  "Books" should "have a unique order" in
  {
    // Preparation
    db.run(
      DBIO.seq
      (
        Books.add(0, "Awesome Title #1"),
        Books.add(1, "Gorgeous Sequel #2"),
        Books.add(1, "Bombastic prequel")
      )
    )

    whenReady(db.run(books.result))
    {
      result => result should have length 2
    }
  }
}

Now the problem I have is, that these tests are completely non-deterministic. Sometimes they all run, and then I get an exception:

The future returned an exception of type: java.sql.SQLException, with message: [SQLITE_BUSY] The database file is locked (database is locked).

I'm really at my wits end here. How can I go about testing that kind of stuff? It's a local database and it's definitely not complicated right now.

App crashes when clicking on a button in fragment (when selecting data from SQLite) [duplicate]

This question already has an answer here:

I am building an application that save data in SQLite and has one main fragment and 2 others fragments with corresponding activities. I created a table and save the data the user enters in one fragment, it works. In the second fragment, I created a button, when the user clicks on it will make a select query on the db and show the data in an alert dialog. But when running the application, it crashes and gives the following error 12-31 14:53:48.569: E/AndroidRuntime(1288): FATAL EXCEPTION: main 12-31 14:53:48.569: E/AndroidRuntime(1288): Process: com.example.appointapp, PID: 1288 12-31 14:53:48.569: E/AndroidRuntime(1288): java.lang.NullPointerException 12-31 14:53:48.569: E/AndroidRuntime(1288): at com.example.appointapp.AppointFragment$3.onClick(AppointFragment.java:110) 12-31 14:53:48.569: E/AndroidRuntime(1288): at android.view.View.performClick(View.java:4438) 12-31 14:53:48.569: E/AndroidRuntime(1288): at android.view.View$PerformClick.run(View.java:18422) 12-31 14:53:48.569: E/AndroidRuntime(1288): at android.os.Handler.handleCallback(Handler.java:733) 12-31 14:53:48.569: E/AndroidRuntime(1288): at android.os.Handler.dispatchMessage(Handler.java:95) 12-31 14:53:48.569: E/AndroidRuntime(1288): at android.os.Looper.loop(Looper.java:136) 12-31 14:53:48.569: E/AndroidRuntime(1288): at android.app.ActivityThread.main(ActivityThread.java:5017) 12-31 14:53:48.569: E/AndroidRuntime(1288): at java.lang.reflect.Method.invokeNative(Native Method) 12-31 14:53:48.569: E/AndroidRuntime(1288): at java.lang.reflect.Method.invoke(Method.java:515) 12-31 14:53:48.569: E/AndroidRuntime(1288): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:779) 12-31 14:53:48.569: E/AndroidRuntime(1288): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:595) 12-31 14:53:48.569: E/AndroidRuntime(1288): at dalvik.system.NativeStart.main(Native Method)

Here is the code: AppointFragment.java

package com.example.appointapp;

import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Locale;

import android.app.AlertDialog;
import android.app.TimePickerDialog;
import android.database.Cursor;
import android.os.Bundle;
import android.support.v4.app.DialogFragment;
import android.support.v4.app.Fragment;
import android.util.Log;
import android.view.LayoutInflater;
import android.view.View;
import android.view.View.OnClickListener;
import android.view.ViewGroup;
import android.widget.Button;
import android.widget.DatePicker;
import android.widget.EditText;
import android.widget.TimePicker;

public class AppointFragment extends Fragment{
    DatabaseHelper myDB;
    private static final String TAG = "AppoinFragment";
    EditText idap, chosdate , chostime;
    Button showp, saveap;
    private Appointment mappointment;


    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        mappointment= new Appointment();


}



    @Override
    public View onCreateView(LayoutInflater inflater, ViewGroup parent, Bundle savedInstanceState){
    View v = inflater.inflate(R.layout.fragment_appoint, parent, false);

    chosdate = (EditText)v.findViewById(R.id.datea_text);
    idap = (EditText)v.findViewById(R.id.idd_text);
    showp = (Button)v.findViewById(R.id.showp_button);
    saveap = (Button)v.findViewById(R.id.savea_button);
    chostime = (EditText)v.findViewById(R.id.timea_text);

    chostime.setOnClickListener(new OnClickListener() {

        @Override
        public void onClick(View v) {
            // TODO Auto-generated method stub
            Calendar mcurrentTime = Calendar.getInstance();
            int hour = mcurrentTime.get(Calendar.HOUR_OF_DAY);
            int minute = mcurrentTime.get(Calendar.MINUTE);
            TimePickerDialog mTimePicker;
            mTimePicker = new TimePickerDialog(getActivity(), new TimePickerDialog.OnTimeSetListener() {
                @Override
                public void onTimeSet(TimePicker timePicker, int selectedHour, int selectedMinute) {
                    chostime.setText( selectedHour + ":" + selectedMinute);
                }
            }, hour, minute, true);//Yes 24 hour time
            mTimePicker.setTitle("Select Time");
            mTimePicker.show();

        }
    });


    chosdate.setOnFocusChangeListener(new View.OnFocusChangeListener() {
        @Override
        public void onFocusChange(View v, boolean hasFocus) {
            if (hasFocus) {
                DialogFragment datePickerFragment = new DatePickerFragment() {
                    @Override
                    public void onDateSet(DatePicker view, int year, int month, int day) {
                        Log.d(TAG, "onDateSet");
                        Calendar c = Calendar.getInstance();
                        c.set(year, month, day);

                        String myFormat = "dd/MM/yy"; 
                        SimpleDateFormat sdf = new SimpleDateFormat(myFormat, Locale.US);

                        chosdate.setText(sdf.format(c.getTime()));

                        chosdate.requestFocus(); 
                    }
                };
                datePickerFragment.show(getActivity().getSupportFragmentManager(), "datePicker");
            }
        }
    });

    viewAllP();
    return v;


}

    public void viewAllP()   {

        showp.setOnClickListener(

                new View.OnClickListener() {

                    @Override
                    public void onClick(View v) {
                        Cursor res = myDB.getAllPdata();
                        if (res.getCount() == 0){
                    //show message
                            showMessage("Error", "No data found");
                            return;

                    }


                        StringBuffer buffer = new StringBuffer();
                        while (res.moveToNext()){
                            buffer.append("Id:"+res.getString(0)+"\n");
                            buffer.append("Name:"+res.getString(1)+"\n");
                            buffer.append("Familyname:"+res.getString(2)+"\n");
                            buffer.append("DOB:"+res.getString(3)+"\n");
                            buffer.append("Address:"+res.getString(4)+"\n");
                            buffer.append("PhoneNumber:"+res.getString(5)+"\n");
                            buffer.append("Email:"+res.getString(6)+"\n");
                            buffer.append("Gender:"+res.getString(7)+"\n\n");


                        }
                        //show all data
                        showMessage("Patients", buffer.toString());
                    }


                }
                );
                }





        public void showMessage(String title, String Message){

            AlertDialog.Builder builder = new AlertDialog.Builder(getActivity());
            builder.setCancelable(true);
            builder.setTitle(title);
            builder.setMessage(Message);
            builder.show();

        }
        }

Databasehelper.java

package com.example.appointapp;

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

public class DatabaseHelper extends SQLiteOpenHelper {

    public static final String DATABASE_NAME="appo.db";
    public static final String TABLE_NAME = "patients_table";
    public static final String COL_1 = "ID";
    public static final String COL_2 = "NAME";
    public static final String COL_3 = "FAMILYNAME";
    public static final String COL_4 = "DOB";
    public static final String COL_5 = "ADDRESS";
    public static final String COL_6 = "PHONENUMBER";
    public static final String COL_7 = "EMAIL";
    public static final String COL_8 = "GENDER";

    public DatabaseHelper(Context context){
        super(context, DATABASE_NAME, null, 2);

    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL( "create table "+ TABLE_NAME + "( ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, FAMILYNAME TEXT, DOB TEXT, ADDRESS TEXT, PHONENUMBER TEXT, EMAIL TEXT, GENDER TEXT )" );

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db);

    }

    public boolean insertData(String name, String familyname, String dob, String address, String phonenumber, String email, String gender) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(COL_2,name);
        contentValues.put(COL_3,familyname);
        contentValues.put(COL_4,dob);
        contentValues.put(COL_5,address);
        contentValues.put(COL_6,phonenumber);
        contentValues.put(COL_7,email);
        contentValues.put(COL_8,gender);
        long result = db.insert(TABLE_NAME, null , contentValues);
        if(result == -1)
            return false;
        else 
            return true;
}

    public Cursor getAllPdata ( ) {
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor res = db.rawQuery(" select * from " +TABLE_NAME, null);
        return res;
    }

}   

Please help

.sqlite Files in Project not Archiving (XCode)

I am trying to get the .ipa of my project with the imported .sqlite files. They are about 60Mb, but when I archive the app via XCode the .ipa comes out as 26MB, and it will not install over AirWatch.

I know this is the problem, so is there a way where I can force XCode to archive those files?

Performance difference incrementing counters in memory vs multuple upsert calls to sqllite database

I'm modifying a prototype of a system generating heuristics from various data points. Right now I'm playing with a part of the code which reads in CSV files which contain a list of entities and counters for those entities Each file will have an entity only once, however, we will be reading in hundreds of files and duplicates can exist across files. The program should sum all counters for an entity and save to sql the entity and it's total count. Currently they do this by using an in-memory hash to increment the counter for an entity each time it's seen until they have a final sum once all files are read.

Once a count is generated it writes out files which contains SQL upsert statements to update the databases. It then calls sqllite program with the output file to generate/update the database; and we create a new database for every single day. Shockingly they have scaling problems trying to collect massive data using this single single, poorly optimized, Perl application, and sqllite! However, I'm not authorized to spend time tweaking this part of the code yet, as much as it bugs me.

I have to make significant changes to the reading in of CSV and format of the SQL files generated. As part of this change I'm considering doing away with the current in memory approach, replacing it with the 'dumb' solution of creating an upsert statement in the sql-file every time an entity is seen, even if this results in multiple upserts being run on a single entity. I want to do this partly to remove some really ugly Perl code, because I feel dirty every time I see their code and if they won't let me refactor it properly I'd rather find an excuse to toss it entirely, but also because this may open up the possibility of easier parallelizing of the code later when/if I'm authorized to do so. Basically, I think the code will be more maintainable with the dumb solution and thus would like to be dumb!

For now I'm trying to get a feel for what, if any, performance differences I may introduce by going dumb. I know the standard answer here is to try it out and see, but the problem is that I don't have access to much of the data yet so I can't do a realistic test. Worse, I'm having a hard time getting characteristics of the data we will be parsing, particularly how common duplicate entities across files are, as we are going to be running on different/more data points soon. Since each sqllight db is for one day ironically the DB likely contain somewhat fewer entities then the in-memory hash would. Our total heuristics (this being just a small part) is taking long enough that they are a little worried about scaling, not enough to give me free reign to properly parallelize the prototype, but enough that I can't de-optimize it unless I prove it won't do much harm.

In lack of an ability to do a real performance test, I'm wondering if someone can help give me a general ball-park idea for performance differences between hash and multiple upsert options to help me decide rather it's worth pushing to. My off-the-cuff feeling is that the CPU expense will be about the same, maybe slightly worse for multiple upserts simply because of limitations of sqllite compared to a real database. I'm assuming the real expense is the time lost to IO from reading and writing an extra sql line per duplicate to the SQL files? If IO is the main expense then I'm inclined to go with my dumb solution unless duplicates prove to be absurdly common, I don't think the IO expense will be high enough and it can be easily fixed with a simple parallelizing via running multiple instances of application to process different inputs. However, I don't know if there are reasons this would be noticeable slower for sqllite then I would expect. Are there any performance tradeoffs I'm unaware of large enough to compete with the IO tradeoffs I should be aware of?

Remove unused data inside sqlite

I use this topic to remove some columns.

BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t1_backup(a,b);
INSERT INTO t1_backup SELECT a,b FROM t1;
DROP TABLE t1;
CREATE TABLE t1(a,b);
INSERT INTO t1 SELECT a,b FROM t1_backup;
DROP TABLE t1_backup;
COMMIT;

But the size of the database is the same as before. If use a deep copy for each table of the old database, the size of new one dramatically decrease with respect to the number of new columns in new tables:

BEGIN TRANSACTION;
CREATE TABLE NEWDB.tb (c1 INTEGER PRIMARY KEY ,c2);
INSERT INTO NEWDB.tb (c1,c2) SELECT c1,c2 FROM tb;
COMMIT;

In my case, the size of old table is 220MB, the new one - 5 MB. What is 215MB? This is because of there are a lot of used data in the old table. Am I right? What kind of data consumes so much disk space? Is there the other methods to decrease the size of the table without the deep copy?

Django - no such table exception

In Django, I've added some models into models.py. After manage.py makemigrations, manage.py migrate raised this exception:

django.db.utils.OperationalError: no such table: auth_test_usertranslatorprofile

So I've removed all old migrations and run makemigrations and migrate again which seemed to work.

Unfortunately, I've noticed that it didn't helped because when I try to click on User customer profiles of User translator profiles it raises exception:

Environment:

Request Method: GET
Request URL: http://ift.tt/1MILGAw

Django Version: 1.8.7
Python Version: 2.7.10
Installed Applications:
('django.contrib.admin',
 'django.contrib.auth',
 'django.contrib.contenttypes',
 'django.contrib.sessions',
 'django.contrib.messages',
 'django.contrib.staticfiles',
 'auth_test')
Installed Middleware:
('django.contrib.sessions.middleware.SessionMiddleware',
 'django.middleware.common.CommonMiddleware',
 'django.middleware.csrf.CsrfViewMiddleware',
 'django.contrib.auth.middleware.AuthenticationMiddleware',
 'django.contrib.auth.middleware.SessionAuthenticationMiddleware',
 'django.contrib.messages.middleware.MessageMiddleware',
 'django.middleware.clickjacking.XFrameOptionsMiddleware',
 'django.middleware.security.SecurityMiddleware')


Traceback:
File "C:\Python27\lib\site-packages\django\core\handlers\base.py" in get_response
  132.                     response = wrapped_callback(request, *callback_args, **callback_kwargs)
File "C:\Python27\lib\site-packages\django\contrib\admin\options.py" in wrapper
  618.                 return self.admin_site.admin_view(view)(*args, **kwargs)
File "C:\Python27\lib\site-packages\django\utils\decorators.py" in _wrapped_view
  110.                     response = view_func(request, *args, **kwargs)
File "C:\Python27\lib\site-packages\django\views\decorators\cache.py" in _wrapped_view_func
  57.         response = view_func(request, *args, **kwargs)
File "C:\Python27\lib\site-packages\django\contrib\admin\sites.py" in inner
  233.             return view(request, *args, **kwargs)
File "C:\Python27\lib\site-packages\django\utils\decorators.py" in _wrapper
  34.             return bound_func(*args, **kwargs)
File "C:\Python27\lib\site-packages\django\utils\decorators.py" in _wrapped_view
  110.                     response = view_func(request, *args, **kwargs)
File "C:\Python27\lib\site-packages\django\utils\decorators.py" in bound_func
  30.                 return func.__get__(self, type(self))(*args2, **kwargs2)
File "C:\Python27\lib\site-packages\django\contrib\admin\options.py" in changelist_view
  1550.                 self.list_max_show_all, self.list_editable, self)
File "C:\Python27\lib\site-packages\django\contrib\admin\views\main.py" in __init__
  82.         self.get_results(request)
File "C:\Python27\lib\site-packages\django\contrib\admin\views\main.py" in get_results
  177.         result_count = paginator.count
File "C:\Python27\lib\site-packages\django\core\paginator.py" in _get_count
  72.                 self._count = self.object_list.count()
File "C:\Python27\lib\site-packages\django\db\models\query.py" in count
  318.         return self.query.get_count(using=self.db)
File "C:\Python27\lib\site-packages\django\db\models\sql\query.py" in get_count
  466.         number = obj.get_aggregation(using, ['__count'])['__count']
File "C:\Python27\lib\site-packages\django\db\models\sql\query.py" in get_aggregation
  447.         result = compiler.execute_sql(SINGLE)
File "C:\Python27\lib\site-packages\django\db\models\sql\compiler.py" in execute_sql
  840.             cursor.execute(sql, params)
File "C:\Python27\lib\site-packages\django\db\backends\utils.py" in execute
  79.             return super(CursorDebugWrapper, self).execute(sql, params)
File "C:\Python27\lib\site-packages\django\db\backends\utils.py" in execute
  64.                 return self.cursor.execute(sql, params)
File "C:\Python27\lib\site-packages\django\db\utils.py" in __exit__
  98.                 six.reraise(dj_exc_type, dj_exc_value, traceback)
File "C:\Python27\lib\site-packages\django\db\backends\utils.py" in execute
  64.                 return self.cursor.execute(sql, params)
File "C:\Python27\lib\site-packages\django\db\backends\sqlite3\base.py" in execute
  318.         return Database.Cursor.execute(self, query, params)

Exception Type: OperationalError at /admin/auth_test/usertranslatorprofile/
Exception Value: no such table: auth_test_usertranslatorprofile

I'm attaching my files:

MODELS.PY:

from django.db import models
from django.contrib.auth.models import User

class Language(models.Model):
    shortcut = models.CharField(max_length=6)
    name = models.CharField(max_length=50)
    price_per_sign = models.FloatField()

class UserTranslatorProfile(models.Model):
    user = models.OneToOneField(User)
    languages = models.ManyToManyField(Language)
    price_per_word = models.FloatField()

class UserCustomerProfile(models.Model):
    user = models.OneToOneField(User)

ADMIN.PY:

from django import forms
from .models import Language
from django.contrib.auth.models import User
from django.contrib.auth.forms import UserCreationForm
class FreelancerRegistrationForm(forms.Form):
    language = forms.ModelChoiceField(queryset=Language.objects.all().order_by('shortcut'))

Do you know where is the problem? Thanks

What's wrong with my sqlite?

When I run the code, the program runs with no errors but leaves a blank space after the asking the 'class number'. I can't tell what's wrong. I've not yet finished with the code so just want to see if it works as it's supposed to but haven't been able to get that far yet.

user_name = input('Enter a name: ')

total_score = 0
x=0
while x<10:
    import random

    signs = ['+', '-', '*']
    sign = random.choice(signs)
    num1 = random.randint(1, 10)
    num2 = random.randint(1, 10)

    print(num1, sign, num2)

    answer = int(eval(str(num1) + sign + str(num2)))

    userAnswer= int(input("= "))

    if userAnswer != answer:
        print ("Incorrect. The right answer is {}.".format(answer))
    else:
        print('Correct')
        total_score = total_score +1 
    x=x+1

if total_score == 10:
    print('Wow',user_name,'!','All 10 of your answers were correct!')
else:
    print (total_score, 'out of 10 were correct!')

from datetime import datetime

now = datetime.now().strftime('%Y-%m-%d %H:%M')
class_number = int(input('Please enter your class number: '))

import sqlite3
if class_number in ['1','2','3']:

    conn = sqlite3.connect('class{}.db')
    c = conn.cursor()

    c.execute('''CREATE TABLE CLS1
             (Username, Score, Date)''')

    c.execute("INSERT INTO CLS1 VALUES (user_name, total_score, now)")

    conn.commit()

import sqlite3
if class_number in ['1','2','3']:

    conn = sqlite3.connect('class{}.db')
    print ("Opened database successfully");

    cursor = conn.execute("SELECT user_name,total_score, now from CLS1 ")
    for row in cursor:
       print ("user_name = ", row[0])
       print ("total_score = ", row[1])
       print ("date text = ", row[2], "\n")

    print ("Operation done successfully");
    conn.close()

android.database.sqlite.SQLiteException: near "update": syntax error (code 1):

My databaseHelper class, which uses SQLite:

public class DatabaseHandler extends SQLiteOpenHelper {

private static final int DATABASE_VERSION = 1;

private static final String DATABASE_NAME = "AnimeManager";

private static final String TABLE_FAVS = "Animes";


private static final String KEY_TITLE = "title";
private static final String KEY_UPDATE = "update";
private static final String KEY_CAP = "cap";
private static final String KEY_CAP_TOT = "cap_tot";
private static final String KEY_FROM = "from";
private static final String KEY_ALARM = "alarm";


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

@Override
public void onCreate(SQLiteDatabase db) {
  /*  String CREATE_ANIME_TABLE=" CREATE TABLE "+TABLE_FAVS+" ("
            +KEY_TITLE+" TEXT PRIMARY KEY,"+KEY_UPDATE+" TEXT,"
            +KEY_FROM+" TEXT,"+KEY_CAP+ " INTEGER,"+ KEY_CAP_TOT+" INTEGER,"+KEY_ALARM+" INTEGER)";
   */
    String CREATE_ANIME_TABLE = String.format(
            "CREATE TABLE %s (%s TEXT PRIMARY KEY," +
                    " %s TEXT," +
                    " %s TEXT," +
                    " %s INTEGER," +
                    " %s INTEGER," +
                    " %s INTEGER);", TABLE_FAVS, KEY_TITLE,
            KEY_UPDATE, KEY_FROM, KEY_CAP, KEY_CAP_TOT, KEY_ALARM);
    db.execSQL(CREATE_ANIME_TABLE);

}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_FAVS);
    onCreate(db);

}

Logcat:

FATAL EXCEPTION: main
Process: com.vvss.gestoranime, PID: 32580
java.lang.RuntimeException: Unable to start activity ComponentInfo{com.vvss.gestoranime/com.vvss.gestoranime.MainActivity}: android.database.sqlite.SQLiteException: near "update": syntax error (code 1): , while compiling: CREATE TABLE Animes (title TEXT PRIMARY KEY, update TEXT, from TEXT, cap INTEGER, cap_tot INTEGER, alarm INTEGER);
    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2379)
    at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2442)
    at android.app.ActivityThread.access$800(ActivityThread.java:156)
    at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1351)
    at android.os.Handler.dispatchMessage(Handler.java:102)
    at android.os.Looper.loop(Looper.java:211)
    at android.app.ActivityThread.main(ActivityThread.java:5373)
    at java.lang.reflect.Method.invoke(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:372)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1020)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:815)
Caused by: android.database.sqlite.SQLiteException: near "update": syntax error (code 1): , while compiling: CREATE TABLE Animes (title TEXT PRIMARY KEY, update TEXT, from TEXT, cap INTEGER, cap_tot INTEGER, alarm INTEGER);
    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
    at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:895)
    at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:506)
    at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
    at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
    at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1674)
    at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1605)
    at com.vvss.gestoranime.database.DatabaseHandler.onCreate(DatabaseHandler.java:60)
    at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:251)
    at android.database.sqlite.SQLiteOpenHelper.getReadableDatabase(SQLiteOpenHelper.java:187)
    at com.vvss.gestoranime.database.DatabaseHandler.getFavCount(DatabaseHandler.java:120)
    at com.vvss.gestoranime.MainActivity.onCreate(MainActivity.java:80)
    at android.app.Activity.performCreate(Activity.java:5990)
    at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1106)
    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2332)
    at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2442) 
    at android.app.ActivityThread.access$800(ActivityThread.java:156) 
    at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1351) 
    at android.os.Handler.dispatchMessage(Handler.java:102) 
    at android.os.Looper.loop(Looper.java:211) 
    at android.app.ActivityThread.main(ActivityThread.java:5373) 
    at java.lang.reflect.Method.invoke(Native Method) 
    at java.lang.reflect.Method.invoke(Method.java:372) 
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1020) 
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:815) 

I tried to create it with commented line but all time got the same error.

I think I forgot an ',' but the statement seems to be ok, I don't know. Thanks for any help.

UPDATE: The error was in KEY_UPDATE and KEY_FROM, just renamed and it works, thanks to laalto.

Removal of SQLite database file using Java doesn't work

These code blocks work but... If I use this blocks one by one my application doesn't remove database file. (result is equeal to false)

sqlite manage part:

    // sqlite manage

        Class.forName("org.sqlite.JDBC").newInstance();            
            conn = DriverManager.getConnection("jdbc:sqlite:/"+ myDBpath);
            stmt = conn.createStatement();
    //some calculations
    stmt.close();

removal part:

    //remove this file
    boolean result = new File(myDBpath).delete();

But if I use only remove code without database operations it works! Why? How can I avoid it?

How to resolve "Can't downgrade database from version 2 to 1" in ActiveAndroid?

I used ActiveAndroid ORM in my application and in AndroidManifest.xml we just need do define meta-data tag for database version like

<meta-data android:name="AA_DB_VERSION" android:value="2" />

but when i uninstall build and change Db version to 1 instead of 2 then After reinstalling it throws an error like Can't downgrade database from version 2 to 1

I don't know why it would happen because i completely uninstall application from my phone then also AA won't allow me to downgrade database version.

May be it's internally handle by AA but still is there any solution ?Please help.

mercredi 30 décembre 2015

Unable to create/modify sqlite tables from Java application

I have a basic java application and a sqlite DB. I can create table manually through sqLite browser. And I am able to access these tables from my JAVA application.

E.g. Reading from existing table: (I have omitted the try catch blocks in the sample below just to reduce the length of question)

PreparedStatement preparedStatement = null;
ResultSet resultSet = null;

String query = "select * from test1 where uname = ? and name = user1";
preparedStatement = connection.prepareStatement(query);
preparedStatement.setString(1, "test"); 
resultSet = preparedStatement.executeQuery();           

if(resultSet.next())
    return true;
else
    return false;

I am able to perform this query successfully.

However, if I create / modify a table, changes does not show up in SQlite DB (I am viewing db in sqLite browser). If I copy paste the same query in SQlite browser, the query runs successfully and a row is added.

PreparedStatement preparedStatement = null;
ResultSet resultSet = null;

String query = "INSERT INTO COMPANY (ID,NAME,AGE) VALUES (3, 'tOM', 32);";

preparedStatement = connection.prepareStatement(query); 
preparedStatement.executeUpdate();          

Am I missing something?

EDIT: Both the above tables exist in my sqlite db. Both were created through sqlite browser

How to set parameters in SQLite?

I've been unable to figure out how to set parameters from within SQLite. I'm mainly using it for testing, and am designing my code so that it can be ported as easily as possible to another, yet to be determined, version of SQL. Normally, I use named parameters, and when testing queries internally, I will use the syantax @varname="value". I'll provide two examples below, showing how I would like to use parameters when testing.

This first example just sets and displays a variable.

@string="Example String";
SELECT @string;

This should return the contents of the variable, in this case: example string.

In the second example, the parameter is being used as part of a condition:

@param=Dark;
SELECT Title from table;
WHERE Title= '%' || @param || '%';

This should return results like the following:

Afterdark    
Alone in the Dark
The Darkness

etc.

QSqlError("", "", "")

I want to write SQLite database for my client server program. In the database, server should save every message comes from client. The table have 3 column: client IP, date and message.I wrote the following code but during debbugging in insertmessage() i get this error: "QSqlError("", "", "") " . What is wrong with it?should i have some code for connecting message that server received or client IP with DB?Thanks a lot.

bool MainWindow::createConnection()
{

QSqlDatabase database = QSqlDatabase::addDatabase("QSQLITE");

database.setDatabaseName("Message.dat");

   if(!database.open())
   {
       qDebug()<<"can not open database";

       database.lastError().text();

       return false;
    }
   return true;
}

MainWindow::messagewindow()
{
     enum {
          messages_id = 0 ,
          messages_date =1 ,
          messages_HostAddreess = 2,
          messages_message = 3,
          };



      model = new QSqlTableModel(this);
     {
        model->setTable("messages");
        model->setHeaderData(messages_date, Qt::Horizontal, tr("Date"));
        model->setHeaderData(messages_HostAddreess, Qt::Horizontal, tr("From"));
        model->setHeaderData(messages_message, Qt::Horizontal, tr("Message"));
        model->select();

     }



     view = new QTableView;
     {
        view->setSelectionMode(QAbstractItemView::SingleSelection);
        view->setSelectionBehavior(QAbstractItemView::SelectRows);
        view->setColumnHidden(messages_id, true);
        view->setModel(model);
        view->resizeColumnsToContents();
        view->setEditTriggers(QAbstractItemView::NoEditTriggers);

        QHeaderView *header = view->horizontalHeader();
        header->setStretchLastSection(true);
     }

 }

 MainWindow::~MainWindow()
 {
  delete ui;
  }


  QString HostAddrress;
  QDate currentDate;
  QString message;

bool create =QFile::exists("Message.dat");
if (!myserver.createConnection())
   return 1;

if (create)

   myserver.createdata();

   myserver.insertMessage( HostAddrress, currentDate, message);


myserver.show();

return a.exec();

}


void MainWindow::createdata()
{
    QSqlQuery query;
    query.exec("DROP TABLE messages");
    query.exec("CREATE TABLE messages("
               "id INTEGER PRIMARY KEY AUTOINCREMENT,"
               "HostAddress varchar(20),"
               "date varchar(10),"
               "message varchar(30))");
}


void MainWindow::insertMessage(QString HostAddrress, QDate currentDate, QString message)
{
    QSqlQuery query;

    query.prepare("INSERT INTO messages(HostAddress, date, message) values(?,?,?)");

    query.addBindValue(HostAddrress);
    query.addBindValue(currentDate);
    query.addBindValue(message);
    query.exec();

    if (query.exec()) {
      // got no error, proceed
      qDebug() << "yes!";
    } else {
      // got an error, deal with it
      qDebug() << query.executedQuery();
      qDebug() << query.lastError();
    }
 }

Create Multiple Tables in SQLite Windows Phone 8.1

I am referring This Tutorial to store data in SQLite database. I am Trying to add another table to the database called "Schools". But it throws an Exception as follows saying No such table: Schools.

enter image description here

Classes

public class Contacts
{
    //The Id property is marked as the Primary Key
    [SQLite.PrimaryKey, SQLite.AutoIncrement]
    public int Id { get; set; }
    public string Name { get; set; }
    public string Age { get; set; }
    public string Address { get; set; }
    public string School { get; set; }
    public string Gardient { get; set; }
    public string PhoneNumber { get; set; }
    public string Latitude { get; set; }
    public string Longitude { get; set; }
    public string CreationDate { get; set; }
    public Contacts()
    {
        //empty constructor
    }
    public Contacts( string name, string age, string address, string school, string gardient, string phone_no, string latitude, string longitude)
    {

        Name = name;
        Age = age;
        Address = address;
        School = school;
        Gardient = gardient;
        PhoneNumber = phone_no;
        Latitude = latitude;
        Longitude = longitude;
        CreationDate = DateTime.Now.ToString();
    }
}

  public class Schools
    {
        //The Id property is marked as the Primary Key
        [SQLite.PrimaryKey, SQLite.AutoIncrement]
        public int Id { get; set; }
        public string School { get; set; }
        public string Latitude { get; set; }
        public string Longitude { get; set; }
        public string CreationDate { get; set; }
        public Schools()
        {
            //empty constructor
        }
        public Schools( string school,string latitude, string longitude)
        {

            School = school;
            Latitude = latitude;
            Longitude = longitude;
            CreationDate = DateTime.Now.ToString();
        }
    }

DatabaseHelperClass

   public class DatabaseHelperClass
    {
        SQLiteConnection dbConn;

        //Create Tabble 
        public async Task<bool> onCreate(string DB_PATH)
        {
            try
            {
                if (!CheckFileExists(DB_PATH).Result)
                {
                    using (dbConn = new SQLiteConnection(DB_PATH))
                    {
                        dbConn.CreateTable<Schools>();
                        dbConn.CreateTable<Contacts>();

                    }
                }
                return true;
            }
            catch
            {
                return false;
            }
        }

        public void createtable()
        {
            SQLite.SQLiteConnection db = new SQLite.SQLiteConnection(DB_PATH);
            db.CreateTable<Schools>();
            db.CreateTable<Contacts>();
        }

        private async Task<bool> CheckFileExists(string fileName)
        {
            try
            {
                var store = await Windows.Storage.ApplicationData.Current.LocalFolder.GetFileAsync(fileName);
                return true;
            }
            catch
            {
                return false;
            }
        }

I have found similar questions but not answered my issue :(

Data is not working

i make two table in sqlite browser, but it's not working. please help me! CREATE TABLE DanhMuc ( MaDM INTEGER NOT NULL, Ten TEXT NOT NULL, HinhAnh INTEGER, PRIMARY KEY(MaDM) );

CREATE TABLE SanPham ( MaSP TEXT NOT NULL, Ten TEXT NOT NULL, DonGia NUMERIC NOT NULL, DonViTinh TEXT NOT NULL, MaDM_PK INTEGER NOT NULL, HinhAnh INTEGER, PRIMARY KEY(MaSP), FOREIGN KEY(MaDM_PK) REFERENCES DanhMuc(MaDM) );

Data Insertion in table giving error in android

I'm retrieving some data from different Views and then inserting the data into a table. But whenever I press the insert button the application crashes and says "Application stopped responding". I have spent like 2 hours on this and I'm completely stuck here. This is my first application using Sqlite in android.

This is my Java code

 btnInsert.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View v) {
            getValues();
            if(b.getString("insTableVal").equals("Donor")){
                MainActivity.db.execSQL("INSERT INTO Donor VALUES("+IdVal+","+NameVal+","+AgeVal+","+GenderVal+","+BGVal+");");
            }
            else if(b.getString("insTableVal").equals("Recipient")){
                MainActivity.db.execSQL("INSERT INTO Recipient VALUES("+IdVal+","+NameVal+","+AgeVal+","+GenderVal+","+BGVal+");");
            }
        }
    });

This is the logcat

12-31 06:50:00.038 13562-13562/com.example.bilalrafique.bloodbankmanagementsystem E/AndroidRuntime: FATAL EXCEPTION: main
                                                                                                Process: com.example.bilalrafique.bloodbankmanagementsystem, PID: 13562
                                                                                                java.lang.NullPointerException
                                                                                                    at com.example.bilalrafique.bloodbankmanagementsystem.DnRInsertion$1.onClick(DnRInsertion.java:34)
                                                                                                    at android.view.View.performClick(View.java:4466)
                                                                                                    at android.view.View$PerformClick.run(View.java:18537)
                                                                                                    at android.os.Handler.handleCallback(Handler.java:733)
                                                                                                    at android.os.Handler.dispatchMessage(Handler.java:95)
                                                                                                    at android.os.Looper.loop(Looper.java:136)
                                                                                                    at android.app.ActivityThread.main(ActivityThread.java:5102)
                                                                                                    at java.lang.reflect.Method.invokeNative(Native Method)
                                                                                                    at java.lang.reflect.Method.invoke(Method.java:515)
                                                                                                    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:785)
                                                                                                    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:601)
                                                                                                    at dalvik.system.NativeStart.main(Native Method)

Where is the Null pointer Exception occuring. I can't figure out. Thanks in Advance.

Scrapy exports all results in one row in SQLite

I'm making a basic spider using Scrapy and want to store the data with SQLite. The spider is working fine and saves the data I want, but it writes all data on the same row in the database.

Here's my spider:

    def parse(self, response):

    for sel in response.xpath('//*[@class="class"]'):
        item = ScrapedItem()
        item['Name'] = sel.xpath('*/header/div//h2/a/text()').extract()
        item['Site'] = sel.xpath('*/header/div/a[1]/text()').extract()
        item['Category'] = sel.xpath('*/header/div/h6[3]/text()').extract()

        yield item

And here is my pipeline:

import sqlite3 as lite
from xyz import settings
from xyz import items
con = None

class Pipeline(object):

    def __init__(self):
        self.setupDBCon()
        self.createTables()

    def process_item(self, item, spider):
        self.storeInfoInDb(item)
        return item

def storeInfoInDb(self, item):
        self.cur.execute("INSERT INTO Table(\
            Name, \
            Site, \
            Category\
            ) \
        VALUES( ?, ?, ?, ? )", \
        ( \
            str(item.get('Name', '')),
            str(item.get('Site', '')),
            str(item.get('Category', ''))
        ))
        print item.get('Name', '')
        self.con.commit()

def setupDBCon(self):
    self.con = lite.connect('test.db')
    self.cur = self.con.cursor()

def __del__(self):
    self.closeDB()

def createTables(self):
    self.dropAgencyTable()
    self.createAgencyTable()

def createTable(self):
    self.cur.execute("CREATE TABLE IF NOT EXISTS Table(id INTEGER PRIMARY KEY NOT NULL, \
        Name TEXT, \
        Site TEXT, \
        Category TEXT )")

def dropTable(self):
    self.cur.execute("DROP TABLE IF EXISTS Agency")

def closeDB(self):
    self.con.close()

How do I save my scraped data in one separate row per scraped item?

My emulator log doesn't recognize that I added a third column to my SQL database

I started working on app recently, to give myself a crash course in coding so I can give app and game development a whirl. The code below is what I wrote to create a database with three columns - an ID column, an email column, and a password column. (Full disclosure, i was using tutorials online to write this and am adding my own to it as practice).
I ran the full program on my phone, with the ability to add an delete from the database with only one column and it worked, but when I added code for the the third column, everything stopped working. Did I make a hideous mistake, or am I just missing a line somewhere? Thank you for reading this far.

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


public class EmailDBHandler extends SQLiteOpenHelper{

private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = "loginEntries.db";
public static final String TABLE_LOGINENTRIES = "loginEntries";
public static final String COLUMN_ID = "_id";
public static final String COLUMN_EMAILADDRESS = "emailAddress";

/*this is where I initialized the column "password" */

public static final String COLUMN_PASSWORD = "password";


public EmailDBHandler(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
    super(context, DATABASE_NAME, factory, DATABASE_VERSION);
}


@Override
public void onCreate(SQLiteDatabase db) {
    String query = "CREATE TABLE " + TABLE_LOGINENTRIES + "(" +
            COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            COLUMN_EMAILADDRESS + " TEXT " +

/this part of the code shows that I added the column to my table/

            COLUMN_PASSWORD + " TEXT " + ");";
    db.execSQL(query);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_LOGINENTRIES);
    onCreate(db);
}

//add new row to Database

public void addEntry(LoginEntries entry){
    ContentValues values = new ContentValues();
    values.put(COLUMN_EMAILADDRESS,entry.get_emailAddress());
    values.put(COLUMN_PASSWORD,entry.get_password());
    SQLiteDatabase db = getWritableDatabase();
    db.insert(TABLE_LOGINENTRIES, null, values);
    db.close();
}


//delete items from database

public void deleteEmailEntry(String emailEntry){
    SQLiteDatabase db = getWritableDatabase();
    db.execSQL("DELETE FROM " + TABLE_LOGINENTRIES + " WHERE " + COLUMN_EMAILADDRESS + "=\"" +
            emailEntry + "\";");
}


public void deletePasswordEntry(String passwordEntry){
    SQLiteDatabase db = getWritableDatabase();
    db.execSQL("DELETE FROM " + TABLE_LOGINENTRIES + " WHERE " + COLUMN_PASSWORD + "=\"" +
            passwordEntry + "\";");
}

//Print database as a string
public String databaseToString(){
    String dbString = "";
    SQLiteDatabase db = getWritableDatabase();
    String query = "SELECT * FROM " + TABLE_LOGINENTRIES + " WHERE 1";

    //cursor point to a location in your results
    Cursor c = db.rawQuery(query, null);
    c.moveToFirst();
    while(!c.isAfterLast()){
        if(c.getString(c.getColumnIndex(COLUMN_EMAILADDRESS)) !=null){
            dbString += c.getString(c.getColumnIndex(COLUMN_EMAILADDRESS));
            dbString += "\n";
        }c.moveToNext();
    }
    db.close();
    return dbString;
}
public String databaseTwoString(){
    String dbStringTwo = "";
    SQLiteDatabase db = getWritableDatabase();
    String query = "SELECT * FROM " + TABLE_LOGINENTRIES + " WHERE 1";

    //cursor point to a location in your results
    Cursor c = db.rawQuery(query, null);
    c.moveToFirst();
    while(!c.isAfterLast()){
        if(c.getString(c.getColumnIndex(COLUMN_PASSWORD)) != null){
            dbStringTwo += c.getString(c.getColumnIndex(COLUMN_PASSWORD));
            dbStringTwo += "\n";
        }
        c.moveToNext();
    }
    db.close();
    return dbStringTwo;
}
}

/The code below is the declaration of the items in my database/

public class LoginEntries {
private int _id;
private String _emailAddress;
private String _password;

public LoginEntries(){}

public LoginEntries(String emailAddress, String password){
    this._emailAddress = emailAddress;
    this._password = password;
}
public void set_id(int _id) {
    this._id = _id;
}

public void set_emailAddress(String _emailAddress) {this._emailAddress = _emailAddress;}

public void set_password(String _password) {this._password = _password;}

public int get_id() {
    return _id;
}

public String get_emailAddress() {return _emailAddress;}

public String get_password() {  return _password;}
}

Alternative to Stored procedures in SQLite3

I am working on a .net win forms project with SQLite as backend.I want to create some Stored procedures to be used form my project.These SP's would generally take in some parameter and after querying the database will return some values etc. But SQLite doesn't support Stored procedures. My question is are there alternatives to sp's or how can i run multiple queries taking some inputs and giving some outputs using c# ...Please advise in this regard.Thanks in advance.

SQLite:Query for a specific time tracking scenario

I have a specific scenario to track the in and out times for an inventory item.

I have a Inventory_track table of the following format

      Inv_id     in_time              out_time
1)   I_1111   2015-11-29 21:05:00.  2015-12-01 03:00:00
2)   I_1111  2015-12-01 05:00:00.  2015-12-01 09:00:00
3)   I_1111  2015-12-01 15:00:00.  2015-12-01 17:00:00
4)   I_1111  2015-12-01 21:00:00.  NULL

  1. Anytime an inventory is placed into a Freezer, I insert a new record with an in_time
  2. Anytime an inventory is taken out, I update the out_time ( not an insert, only an update)

At any given point, I want to find the time that the inventory spent outside the freeze location. Basically what I want to do is :

(record_2's in_time- record_1's out_time) + ( record_3's in_time- record_2's out_time)

and so on for a given inventory. Can someone help me with a query in sqlite for this. How do I go about using self joins for this scenario described above.

IntentService is starting but the work is not getting done

I'm calling this method where ever the service needs to be started

public void syncData(){
    Intent mServiceIntent = new Intent(this, DataSyncToServer.class);
    Log.d("SyncDAta", "Starting Intent Service");
    this.startService(mServiceIntent);
}

This is my service class

public class DataSyncToServer extends IntentService {
final String NAMESPACE = "http://tempuri.org/";
final String URL = "http://***/**";
final String SOAP_ACTION_SaveTrip = "http://ift.tt/1RS43La";
long id;
protected void onHandleIntent(Intent workIntent) {
    Log.d("DataSyncService", "Inside on handleIntent");
    if(new Tracker().isNetworkAvailable()){
        Log.d("DataSync Network Check", "network is available");
        updateTripPrice();
        Log.d("DataSync TripPrice After", "starting cabtrans update");
        new NriTrackerDb(getApplicationContext()).deleteTrips();
    }
}
public DataSyncToServer(String name) {
    super(name);
}
public void updateTripPrice(){
    Log.d("DataSync updTripPrice", "just entered it");
    ArrayList<TripPrice> al = new NriTrackerDb(getApplicationContext()).getTrips();
    if(al!=null){
        Log.d("DataSync updTripPrice", "retrieved data from database.");
        for(int i = 0;i < al.size(); i++){
            TripPrice tr = al.get(i);
            id = tr.trip_id;
            SoapObject saveTrip = new SoapObject(NAMESPACE,"SaveTrip");
            saveTrip.addProperty("cab_no",tr.cab_no);
            saveTrip.addProperty("mobile_no",tr.mobile_no);
            saveTrip.addProperty("ticket_nbr",String.valueOf(tr.trip_id));
            saveTrip.addProperty("distance",String.valueOf(tr.distance));
            saveTrip.addProperty("amount",String.valueOf(tr.amount));
            saveTrip.addProperty("trip_start",String.valueOf(tr.trip_start));
            saveTrip.addProperty("trip_end",String.valueOf(tr.trip_end));
            Log.d("DataSync updTripPrice", "calling async task");
            new SaveTrip().execute(saveTrip);
            Log.d("DataSync updTripPrice", "after async task");
        }
    }
}
public class SaveTrip extends AsyncTask<SoapObject,Void,Void>{
    @Override
    protected Void doInBackground(SoapObject... request) {
        Log.d("DataSync AsyncTask", "AsyncTask do in background entered");
        try{    
            SoapSerializationEnvelope envelope = new SoapSerializationEnvelope(SoapEnvelope.VER11);
            envelope.dotNet=true;
            envelope.setOutputSoapObject(request[0]);
            HttpTransportSE androidHttpTransport = new HttpTransportSE(URL);
            try {
                Log.d("DataSync AsyncTask", "before calling the web api");
                androidHttpTransport.call(SOAP_ACTION_SaveTrip, envelope);
                Log.d("DataSync AsyncTask", "called api successfully");
                androidHttpTransport.debug = true;
                Log.d("DataSync AsyncTask", "before taking response");
                SoapPrimitive response = (SoapPrimitive)envelope.getResponse();
                Log.d("DataSync AsyncTask", "response is taken successfully");
                System.out.println(response);

                Log.d("DataSync AsyncTask", "at the end of doInBackground");
                new NriTrackerDb(getApplicationContext()).updateTripPrice(id);
            } catch (XmlPullParserException | IOException e) {
                Log.i("Exception in doInBackground of SaveTrip","Trying to handle exception here");
            }
        }catch(Exception e){
            Log.d("in the service method","failed sending trips to database");
        }
        return null;
    }
}
}

And this is the method of my db

public ArrayList<TripPrice> getTrips(){
    Log.d("Database GetTrips", "just entered");
    SQLiteDatabase db = this.getReadableDatabase();
    String selectQuery = "SELECT * FROM trip_price WHERE push_data = 'NO'";
    ArrayList<TripPrice> tp = new ArrayList<TripPrice>();
    Log.d("Database GetTrips", "before query");
    Cursor cursor = db.rawQuery(selectQuery, null);
    Log.d("Database GetTrips", "after query");
    if(cursor != null && cursor.moveToFirst()){
        Log.d("Database GetTrips", "query successful");
        do{
            TripPrice tr = new TripPrice();
            tr.cab_no = cursor.getString(cursor.getColumnIndex(COLUMN_NAME_CAB));
            tr.mobile_no = cursor.getString(cursor.getColumnIndex(COLUMN_NAME_MOBILE_NO));
            tr.trip_id = cursor.getInt(cursor.getColumnIndex(COLUMN_NAME_TRIP_ID));
            tr.distance = cursor.getDouble(cursor.getColumnIndex(COLUMN_NAME_DISTANCE));
            tr.amount = cursor.getDouble(cursor.getColumnIndex(COLUMN_NAME_AMOUNT));
            tr.trip_start = cursor.getString(cursor.getColumnIndex(COLUMN_NAME_TRIP_START));
            tr.trip_end = cursor.getString(cursor.getColumnIndex(COLUMN_NAME_TRIP_END));
            tp.add(tr);
            Log.d("Database GetTrips", "in the loop");
        }while(cursor.moveToNext());
        db.close();
        Log.d("Database GetTrips", "sending data");
        return tp;
    }
    else
        db.close();
        return null;
}

I'm not able to check weather this service is working or not. SO I'm checking for new data in back end, but there is no data that is sent by this service. Need suggestions badly.

How do I combine these two SQLite queries?

How do I combine these two queries into a single query to get the last 10 messages, using the LIMIT keyword?

Cursor cursor0 = db.query(Chat_MESSAGE, messagecolumns, senderid + "=? AND " + receiverid + "=?", new String[] {
            sender_id, receiver_id }, null, null, null, null);

Cursor cursor1 = db.query(Chat_MESSAGE, messagecolumns, senderid + "=? AND " + receiverid + "=?", new String[] {
            receiver_id, sender_id }, null, null, null, null);

Pre-Populated DB with support of creating entries by App Users as well

We are working on an app in which we are using a Data Base. The DataBase will be containing pre-filled information which will be presented to user as and when it is required.

The application also has a provision to create multiple users account, login with them, switch accounts and create their profile and save their settings related data. Now as this information source is specific to user and is provided by the user, so it will only be available once the user provides it and since these are user preferences and account details so we require a way to save these details on device, and to do so again we choose Data Base. Also we are using the same DataBase with different tables to store both types of information, namely-

1) Pre-filled data contained in the DB and 2) User created data stored in the DB,

The above two approaches work fine when these are used independently, in other words, at a time only one will work, reason being the DB File in which approach of point 1 works is saved in Applications Bundle and the 2nd type of DB File is saved in documents directory. The DataBase pointer can point to only one directory at a a time, thus only one approach amongst the above two will work.

What we are looking for is a way to use both the approaches, using a single DB.

Any solution related to supporting both type of data in one DB, in a single directory will be beneficial.

Thanks for reading the problem, and thanks in advance for your valuable time and suggestions.

how can i call the updateContact method from DataBaseHandler.java in MainActivity

public class DataBaseHandler extends SQLiteOpenHelper {

// Version de la BD
private static final int DATA_BASE_VERSION = 1;
// Nom de la BD
private static final String DATA_BASE_NAME = "contactsManager";
// Nom de la table Contacts
private static final String TABLE_CONTACTS = "contacts";
// Colonnes de la table Contacts
private static final String KEY_ID = "id";
static final String KEY_NAME = "name";
static final String KEY_PH_NB = "phone_number";
private SQLiteDatabase maBaseDeDonnees;
private DataBaseHandler baseHandler;
// méthode permettant d'ouvrir la base en écriture
public void open() {
    maBaseDeDonnees = baseHandler.getWritableDatabase();
}
public DataBaseHandler(Context context, String name, SQLiteDatabase.CursorFactory factory,
                       int version) {
    super(context, DATA_BASE_NAME, null, DATA_BASE_VERSION);
}
@Override

public void onCreate(SQLiteDatabase db) {
    String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_CONTACTS + "("
            + KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT,"
            + KEY_PH_NB + " TEXT" + ")";
    db.execSQL(CREATE_CONTACTS_TABLE);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS);

    onCreate(db);
}


void addContact(Contact contact) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(KEY_NAME, contact.getName()); // Nom du contact
    values.put(KEY_PH_NB, contact.getPhone_number()); // Tel du contact

    db.insert(TABLE_CONTACTS, null, values);
    db.close(); // Fermer la connection à la base de données
}

Contact getContact(int id) {
    SQLiteDatabase db = this.getReadableDatabase();

    Cursor cursor = db.query(TABLE_CONTACTS, new String[] { KEY_ID,
                    KEY_NAME, KEY_PH_NB }, KEY_ID + "=?",
            new String[] { String.valueOf(id) }, null, null, null, null);
    if (cursor != null) // il existe un contact avec l'id spécifié
        cursor.moveToFirst();

    Contact contact = new Contact(Integer.parseInt(cursor.getString(0)),
            cursor.getString(1), cursor.getString(2));

    db.close();
    return contact;
}

public List<Contact> getAllContacts() {
    List<Contact> contactList = new ArrayList<Contact>();

    String selectQuery = "SELECT * FROM " + TABLE_CONTACTS;
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(selectQuery, null);

    if (cursor.moveToFirst()) {
        do {
            Contact contact = new Contact();
            contact.setId(Integer.parseInt(cursor.getString(0)));
            contact.setName(cursor.getString(1));
            contact.setPhone_number(cursor.getString(2));

            contactList.add(contact);
        } while (cursor.moveToNext());
    }

    return contactList;
}


public int removeContact(String name) {

    return maBaseDeDonnees.delete(DATA_BASE_NAME, KEY_NAME + "LIKE" + name,
            null);
}

public int updateContact(Contact c) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues val = new ContentValues();
    val.put(KEY_NAME, c.getName());
    val.put(KEY_PH_NB, c.getPhone_number());
    return db.update(TABLE_CONTACTS, val, KEY_ID + "=?",
            new String[] { String.valueOf(c.getId()) });
}
public void deleteContact(Contact c) {
    SQLiteDatabase db = this.getWritableDatabase();
    db.delete(TABLE_CONTACTS, KEY_ID + "=?",
            new String[] { String.valueOf(c.getId()) });
    db.close();
}
public int getContactsNumber() {
    String req = "SELECT * FROM " + TABLE_CONTACTS;
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor c = db.rawQuery(req, null);
    c.close();
    return c.getCount();
}

} public class MainActivity extends Activity {

String nom, tel;
// Version de la BD
private static final int DATA_BASE_VERSION = 1;
// Nom de la BD
private static final String DATA_BASE_NAME = "contactsManager";

DataBaseHandler db = new DataBaseHandler(this, DATA_BASE_NAME, null, DATA_BASE_VERSION);


@Override
public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_add_contact);

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

    String[] values = new String[] { "Afficher tous les contacts",
            "Ajouter un contact",
            "Modifier un contact",};

    ArrayAdapter adapter = new ArrayAdapter<String>(this,R.layout.simplerow,values);

// Associer la listView XML à java //Affichage de tous les contacts

    lv.setAdapter(adapter);
    lv.setOnItemClickListener(new AdapterView.OnItemClickListener() {
        @Override
        public void onItemClick(AdapterView<?> parent, View view, int position, long id) {

//hjhgjgjghgv lljklnkjnj if (position==0) {

               EditText edt = (EditText) findViewById(R.id.editText);
               String resultat = String.valueOf(db.getAllContacts());
               edt.setText(String.valueOf(resultat));

           }else if (position==1){

               EditText ed1 = (EditText) findViewById(R.id.editnom);
               EditText ed2 = (EditText) findViewById(R.id.editphone);
               nom = ed1.getText().toString();
               tel = ed2.getText().toString();
               db.getWritableDatabase();
               db.getReadableDatabase();
               Contact nouveauContact = new Contact();
               nouveauContact.setName(nom);
               nouveauContact.setPhone_number(tel);
               Log.d("Reading: ", "Reading all contacts..");
               List<Contact> contacts = db.getAllContacts();
               for (Contact cn : contacts) {
                   String log = "Id: " + cn.getId() + " ,Name: " + cn.getName() + " ,Phone: " + cn.getPhone_number();
                   // Writing Contacts to log
                   Log.d("Name: ", log);
               }
           }
        }
    }); 

}

Populating ListView from external SQlite database queried with a SearchView widget - Android

I am trying to display data from my database in a ListView widget. The database is queried using a SearchView widget.

Here is the code for my SQlite open helper class:

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;


import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

public class DataBaseHelper extends SQLiteOpenHelper {

//The Android's default system path of your application database.

private static String DB_NAME = "DB.sqlite";

private SQLiteDatabase myDB;

private final Context myContext;


/**
 * Constructor
 * Takes and keeps a reference of the passed context in order to access to the application assets and resources.
 * @param context
 */
public DataBaseHelper(Context context) {

    super(context, DB_NAME, null, 1);
    this.myContext = context;
}

/**
 * Creates a empty database on the system and rewrites it with your own database.
 * */
public void createDataBase() throws IOException {

    boolean dbExist = checkDataBase();

    if(dbExist){
        //do nothing - database already exist
    }else{

        //By calling this method and empty database will be created into the default system path
        //of your application so we are gonna be able to overwrite that database with our database.
        this.getReadableDatabase();

        try {

            copyDataBase();

        } catch (IOException e) {

            throw new Error("Error copying database");

        }
    }

}

/**
 * Check if the database already exist to avoid re-copying the file each time you open the application.
 * @return true if it exists, false if it doesn't
 */
private boolean checkDataBase(){

    SQLiteDatabase checkDB = null;

    try{
        String myPath = myContext.getDatabasePath(DB_NAME).getPath();
        checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);

    }catch(SQLiteException e){

        //database does't exist yet.

    }

    if(checkDB != null){

        checkDB.close();

    }

    return checkDB != null ? true : false;
}


/**
 * Copies your database from your local assets-folder to the just created empty database in the
 * system folder, from where it can be accessed and handled.
 * This is done by transfering bytestream.
 * */
private void copyDataBase() throws IOException{

    //Open your local db as the input stream
    InputStream myInput = myContext.getAssets().open(DB_NAME);

    // Path to the just created empty db
    String outFileName = myContext.getDatabasePath(DB_NAME).getPath();

    //Open the empty db as the output stream
    OutputStream myOutput = new FileOutputStream(outFileName);

    //transfer bytes from the inputfile to the outputfile
    byte[] buffer = new byte[1024];
    int length;
    while ((length = myInput.read(buffer))>0){
        myOutput.write(buffer, 0, length);
    }

    //Close the streams
    myOutput.flush();
    myOutput.close();
    myInput.close();

}

public void openDataBase() throws SQLException {

    //Open the database
    String myPath = myContext.getDatabasePath(DB_NAME).getPath();
    myDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);

}

@Override
public synchronized void close() {

    if(myDB != null)
        myDB.close();

    super.close();

}

@Override
public void onCreate(SQLiteDatabase myDataBase) {

}


@Override
public void onUpgrade(SQLiteDatabase myDataBase, int oldVersion, int newVersion) {

}



public Cursor getC(String animal) {
    Cursor c;
    c=myDB.rawQuery("SELECT * FROM Table where animal MATCH ?", new String [] {animal});
    c.moveToFirst();

    return c;
}




}


// Add your public helper methods to access and get content from the database.
// You could return cursors by doing "return myDataBase.query(....)" so it'd be easy
// to you to create adapters for your views.

Here is my code for the cursor adapter class:

import android.content.Context;
import android.database.Cursor;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.CursorAdapter;
import android.widget.TextView;


public class TodoCursorAdapter extends CursorAdapter {

private LayoutInflater mInflater;

public TodoCursorAdapter(Context context, Cursor c, int flags) {
    super(context, c, flags);
    mInflater = (LayoutInflater) context.getSystemService(Context.LAYOUT_INFLATER_SERVICE);
}

/* ... */

@Override
public View newView(Context context, Cursor cursor, ViewGroup parent) {
    return mInflater.inflate(R.layout.search_item, parent, false);
}



// The bindView method is used to bind all data to a given view
// such as setting the text on a TextView.

@Override
public void bindView(View view, Context context, Cursor cursor) {
    TextView content = (TextView) view.findViewById(R.id.résultatAnimal);
    content.setText(cursor.getString(2));

    }
}

Here is my code for the searchable activity:

import android.app.ListActivity;
import android.app.SearchManager;
import android.content.Intent;
import android.database.SQLException;
import android.os.Bundle;
import android.widget.ListView;


import java.io.IOException;


public class searchableActivity extends ListActivity {



@Override
public void onCreate(Bundle savedInstanceState) {

    DataBaseHelper myDbHelper = new DataBaseHelper(searchableActivity.this);

    try {

        myDbHelper.createDataBase();

    } catch (IOException ioe) {

        throw new Error("Unable to create database");

    }

    try {

        myDbHelper.openDataBase();

    }catch(SQLException sqle){

        throw sqle;

    }


    super.onCreate(savedInstanceState);



    // Get the intent, verify the action and get the query
    Intent intent = getIntent();
    if (Intent.ACTION_SEARCH.equals(intent.getAction())) {
        String query = intent.getStringExtra(SearchManager.QUERY);
        //myDbHelper.getC(query).getString(2);
        // Find ListView to populate
        ListView AnimalListe = (ListView) findViewById(R.id.list);
// Setup cursor adapter using cursor from last step
        TodoCursorAdapter todoAdapter = new TodoCursorAdapter(this, myDbHelper.getC(query), 0);
// Attach cursor adapter to the ListView
        AnimalListe.setAdapter(todoAdapter);
    }

    myDbHelper.close();
}
}

When I launch the searching in my SearchView widget, I have an error "NULL POINTER EXCEPTION" and the application crashes. Why?

Android studio database error

I have error when i insert values into driver_table Code of table

The Error

Fragment: getItem() mistake

this is my situation: I want to show a few fragments. In my case, I have a sqlite database with a some students, the fields are: _id, Name, Lastname, sex, photoSize, field1, field2, field3, field4. For example, if field1 == 1 i have to show the fragment 1, if field2 == 2 i have to show the fragment 2 and so.I'd like to select which fragment I'm gonna show. I don't know what i am doing wrong. Here is the code.

public class SectionsPagerAdapter extends FragmentPagerAdapter {

        Bundle b = getIntent().getExtras();
        int i = 5;


        public SectionsPagerAdapter(FragmentManager fm) {
            super(fm);
        }

        @Override
        public Fragment getItem(int position) {

            String id = b.getString("id");
            c1 = dbm.buscarAlumno(id);
            // getItem is called to instantiate the fragment for the given page.
            // Return a PlaceholderFragment (defined as a static inner class below).
            try {
                if (c1.moveToFirst()){
                    switch (position){
                        case 0:
                            if (c1.getString(5).equals("1")){
                                return PlaceholderFragment.newInstance(position);
                            }
                        case 1:
                            if (c1.getString(6).equals("1")){
                                return PlaceholderFragment.newInstance(position);
                            }
                        case 2:
                            if (c1.getString(7).equals("1")){
                                return PlaceholderFragment.newInstance(position);
                            }
                        case 3:
                            if (c1.getString(8).equals("1")){
                                return PlaceholderFragment.newInstance(position);
                            }
                        case 4:
                                return PlaceholderFragment.newInstance(position);
                    }
                }

            }
            finally {
                c1.close();
            }
        return null;

        }

        @Override
        public int getCount() {
            // Show 1 total pages.
            return i;
        }

        @Override
        public CharSequence getPageTitle(int position) {
            String id = b.getString("id");
            c = dbm.buscarAlumno(id);
            try {
                if (c.moveToFirst()) {
                    switch (position) {
                        case 0:
                            if (c.getString(5).equals("1")){
                                return "PISTA";
                            }
                        case 1:
                            if (c.getString(6).equals("1")){
                                return "ESTABLO";
                            }else{break;}
                        case 2:
                            if (c.getString(7).equals("1")){
                                return "EMOCIONES";
                            }else{break;}
                        case 3:
                            if (c.getString(8).equals("1")){
                                return "NECESIDADES";
                            }else{break;}
                        case 4:
                            return b.getString("alumno");
                    }
                }

            }
            finally {
                c.close();
            }
            return null;
        }
    }

getItem() doesn't work, but getPageTitle works fine. The dbm variable is declare in the container class, means DataBaseManager, and buscarAlumno(id) is the method that returns the student, and swear that this method works perfect. Here is the logcat errors:

12-30 11:48:00.168 1847-1847/? E/AndroidRuntime: FATAL EXCEPTION: main
                                                 Process: laboratorio.hermes2, PID: 1847
                                                 java.lang.RuntimeException: Unable to instantiate application android.app.Application: java.lang.IllegalStateException: Unable to get package info for laboratorio.hermes2; is package not installed?
                                                     at android.app.LoadedApk.makeApplication(LoadedApk.java:507)
                                                     at android.app.ActivityThread.handleBindApplication(ActivityThread.java:4301)
                                                     at android.app.ActivityThread.access$1500(ActivityThread.java:135)
                                                     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1256)
                                                     at android.os.Handler.dispatchMessage(Handler.java:102)
                                                     at android.os.Looper.loop(Looper.java:136)
                                                     at android.app.ActivityThread.main(ActivityThread.java:5001)
                                                     at java.lang.reflect.Method.invokeNative(Native Method)
                                                     at java.lang.reflect.Method.invoke(Method.java:515)
                                                     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:785)
                                                     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:601)
                                                     at dalvik.system.NativeStart.main(Native Method)
                                                  Caused by: java.lang.IllegalStateException: Unable to get package info for laboratorio.hermes2; is package not installed?
                                                     at android.app.LoadedApk.initializeJavaContextClassLoader(LoadedApk.java:368)
                                                     at android.app.LoadedApk.getClassLoader(LoadedApk.java:321)
                                                     at android.app.LoadedApk.makeApplication(LoadedApk.java:500)
                                                     at android.app.ActivityThread.handleBindApplication(ActivityThread.java:4301) 
                                                     at android.app.ActivityThread.access$1500(ActivityThread.java:135) 
                                                     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1256) 
                                                     at android.os.Handler.dispatchMessage(Handler.java:102) 
                                                     at android.os.Looper.loop(Looper.java:136) 
                                                     at android.app.ActivityThread.main(ActivityThread.java:5001) 
                                                     at java.lang.reflect.Method.invokeNative(Native Method) 
                                                     at java.lang.reflect.Method.invoke(Method.java:515) 
                                                     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:785) 
                                                     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:601) 
                                                     at dalvik.system.NativeStart.main(Native Method) 
12-30 11:48:00.178 1389-1389/? W/EGL_emulation: eglSurfaceAttrib not implemented
12-30 11:48:00.178 1253-1264/? W/InputMethodManagerService: Got RemoteException sending setActive(false) notification to pid 1783 uid 10057
12-30 11:48:00.198 1356-1367/? W/Binder: Caught a RuntimeException from the binder stub implementation.
                                         java.lang.NullPointerException
                                             at android.inputmethodservice.IInputMethodWrapper.setSessionEnabled(IInputMethodWrapper.java:280)
                                             at com.android.internal.view.IInputMethod$Stub.onTransact(IInputMethod.java:129)
                                             at android.os.Binder.execTransact(Binder.java:404)
                                             at dalvik.system.NativeStart.run(Native Method)


12-30 11:48:06.238 1389-1389/? E/ActivityThread: Performing stop of activity that is not resumed: {com.android.launcher/com.android.launcher2.Launcher}
                                                 java.lang.RuntimeException: Performing stop of activity that is not resumed: {com.android.launcher/com.android.launcher2.Launcher}
                                                     at android.app.ActivityThread.performStopActivityInner(ActivityThread.java:3133)
                                                     at android.app.ActivityThread.handleStopActivity(ActivityThread.java:3220)
                                                     at android.app.ActivityThread.access$1100(ActivityThread.java:135)
                                                     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1223)
                                                     at android.os.Handler.dispatchMessage(Handler.java:102)
                                                     at android.os.Looper.loop(Looper.java:136)
                                                     at android.app.ActivityThread.main(ActivityThread.java:5001)
                                                     at java.lang.reflect.Method.invokeNative(Native Method)
                                                     at java.lang.reflect.Method.invoke(Method.java:515)
                                                     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:785)
                                                     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:601)
                                                     at dalvik.system.NativeStart.main(Native Method)



12-30 11:55:16.425 1356-1368/? W/Binder: Caught a RuntimeException from the binder stub implementation.
                                         java.lang.NullPointerException
                                             at android.inputmethodservice.IInputMethodWrapper.setSessionEnabled(IInputMethodWrapper.java:280)
                                             at com.android.internal.view.IInputMethod$Stub.onTransact(IInputMethod.java:129)
                                             at android.os.Binder.execTransact(Binder.java:404)
                                             at dalvik.system.NativeStart.run(Native Method)

Please help! Thanks.

How to Add Parameters in SQLite C#?

I'm using WPF and SQLite, I want to add new Parameters. I tried:

string _search;

private void txtSearchBox_TextChanged(object sender, TextChangedEventArgs e)
{
    _search = txtSearchBox.Text;
}

SQLiteConnection sqlite_conn = new SQLiteConnection("Data Source = music.db");
SQLiteCommand sqlite_cmd = new SQLiteCommand();

private void btn_search_Click(object sender, RoutedEventArgs e)
{
    sqlite_conn.Open();
    sqlite_cmd = sqlite_conn.CreateCommand();
    sqlite_cmd.CommandText = "select Title from Song where Title=@title";

    sqlite_cmd.Parameters.Add("@title",SqlDbType.NVarChar, -1);  // ************ //

    sqlite_cmd.Parameters["@title"].Value = _search;


    DataSet dataSet = new DataSet();
    SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(sqlite_cmd.CommandText,sqlite_conn);
    dataAdapter.Fill(dataSet);

    datagrid.ItemsSource = dataSet.Tables[0].DefaultView;
}

I got error on the line I ticked // ******* //. How can I fix it?

EF7 (Code First) + SQLite doesn't create a database and the tables for the models

Im currently trying to recreate the example, done in the documentation http://ift.tt/1OkeJ2m , using EF7 and SQLite to create a Universal Windows Platform app.

I've installed the required EF7 and EF7 Commands package, and created the model and context:

 public class BloggingContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
            string dirPath = ApplicationData.Current.LocalFolder.Path;
            string connectionString = "Filename=" + Path.Combine(dirPath, "blogging.db");
            optionsBuilder.UseSqlite(connectionString);
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
    }
}

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }

    public List<Post> Posts { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public int BlogId { get; set; }
    public Blog Blog { get; set; }
}

My problem now is, that after building the solution, the command that should scaffold a migration to create the initial set of tables for my model fails with the following exception:

PM> Add-Migration MyFirstMigration
System.Reflection.ReflectionTypeLoadException: Unable to load one or more of the requested types. Retrieve the LoaderExceptions property for more information.
 at System.Reflection.RuntimeModule.GetTypes(RuntimeModule module)
 at System.Reflection.RuntimeAssembly.get_DefinedTypes()
 at Microsoft.Data.Entity.Design.Internal.StartupInvoker..ctor(String startupAssemblyName, String environment)
 at Microsoft.Data.Entity.Design.DbContextOperations..ctor(ILoggerProvider loggerProvider, String assemblyName, String startupAssemblyName, String environment)
 at Microsoft.Data.Entity.Design.MigrationsOperations..ctor(ILoggerProvider loggerProvider, String assemblyName, String startupAssemblyName, String environment, String projectDir, String rootNamespace)
 at Microsoft.Data.Entity.Design.OperationExecutor.<>c__DisplayClass3_0.<.ctor>b__3()
 at Microsoft.Data.Entity.Internal.LazyRef`1.get_Value()
 at Microsoft.Data.Entity.Design.OperationExecutor.<AddMigrationImpl>d__7.MoveNext()
 at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
 at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
 at Microsoft.Data.Entity.Design.OperationExecutor.OperationBase.<>c__DisplayClass4_0`1.<Execute>b__0()
 at Microsoft.Data.Entity.Design.OperationExecutor.OperationBase.Execute(Action action)
Unable to load one or more of the requested types. Retrieve the LoaderExceptions property for more information.

Does anyone have a solution for this problem ? Thanks in advance

Database not updating with tokens

I have a login activity that grants an access token. I get an http post response from a url corresponding to the token. The only way I can get a response with an error is by updating the access token. However, the access token is not updating in the database.

public void updateToken(String token) {

            Cursor oldTokenRes = getTokenRes();
        if (oldTokenRes.moveToFirst()) {
            SQLiteDatabase db = this.getWritableDatabase();
            ContentValues contentValues = new ContentValues();
            contentValues.put(TOKEN.get("TOKEN").getColumnName(), token);
            Log.v("cv1", token.toString());
            Log.v("update id", Integer.toString(oldTokenRes.getInt(oldTokenRes.getColumnIndex("_id"))));

            db.update(TOKEN_TABLE_NAME, contentValues, "_id = ? ", new String[]{Integer.toString(oldTokenRes.getInt(oldTokenRes.getColumnIndex("_id")))});
        }
    }

public String POST(String url) {

       String response = "";

       if (Looper.myLooper() == null) {
           Looper.prepare();
       }

       DatabaseHelper db = DatabaseHelper.getInstance(getApplicationContext());
       SharedPreferences prefs = getApplicationContext().getSharedPreferences("preferences", Context.MODE_PRIVATE);

        String token = prefs.getString("token", db.getToken());
        Log.v("token1", token);
        String t = prefs.getString("token", token);
        Log.v("t", t);

       if (token != null) {

           String token1;
           token1 = db.getToken();

           db.updateToken(token1);
           Log.v("token1", token1); //not called, but calls update method's log

           uWebConnection conn = new uWebConnection(URL + "?token=" + token1, getApplicationContext());
           Log.v("URL", URL + "?token=" + token1);

           conn.addValuePair("alert_name", "subject");
           conn.addValuePair("alert_message", "message");
           Log.v("conn", conn.toString());

           InputStream in = null;
           try {
               Utils utils = new Utils();
               in = conn.connect();
               Log.v("in", in.toString());
               response = utils.convertStreamToString(in);

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

             }
            Log.v("response", response.toString());
            return response;
   }

       private class HttpAsyncTask extends AsyncTask<String, Void, String> {
           @Override
           protected String doInBackground(String... urls) {
               return POST(urls[0]);
           }

           //onPostExecute displays the results of the AsyncTask.
           @Override
           protected void onPostExecute(String response) {
               Log.v("onpost result", response);
               Toast.makeText(getBaseContext(), "Alert URL Received.", Toast.LENGTH_LONG).show();
           }
       }

Any suggestions of how to alert the database of an updated token?

Insert or Update in sqlite?

my table is :

CREATE TABLE IF NOT EXISTS BrowseHistory (
id INTEGER NOT NULL, 
tid INTEGER NOT NULL PRIMARY KEY, 
data BLOB NOT NULL)

what I want to do is give tid and data, I want to find if there's not a record's tid == given tid, then insert it( the id is the max(id) + 1). If there is, check if the id is equal to max(id) ,if not set the id to max(id) +1. I used the following sql, it can work ,but it will always set the id equals to max(id) + 1

INSERT OR REPLACE INTO BrowseHistory (id, tid, data) values ((SELECT IFNULL(MAX(id),0) FROM BrowseHistory) + 1, ?, ?)

android sqlite insert query

Here is my code, I don't know what is wrong with getWritableDatabase().insert(TABLE_ROOM, null, contentValues); and Cursor wrapped = getReadableDatabase().rawQuery("select * from room", null); this is RoomDatabaseHelper.java

public class RoomDatabaseHelper extends SQLiteOpenHelper {
private static final String DB_NAME = "rooms.sqlite";
private static final int VERSION = 1;

private static final String TABLE_ROOM = "room";
private static final String ROOM_NUMBER = "room_number";
private static final String ROOM_PRICE = "room_price";
private static final String GUEST_NAME = "guest_name";
private static final String ID_CARD = "id_card";
private static final String CHECK_IN_DATE = "check_in_date";
private static final String CHECK_OUT_DATE = "check_out_date";

public RoomDatabaseHelper(Context context) {
    super(context, DB_NAME, null, VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL("create table room (room_number integer primary key autoincrement, " +
            "room_price integer, guest_name varchar(100), id_card varchar(100), " +
            "check_in_date varchar(100), check_out_date varchar(100))");

    for (int i = 1; i < 50; i++) {
        Room room = new Room(100 + i, 270);
        insertRoom(room);
    }
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

}

public void insertRoom(Room room) {
    ContentValues contentValues = new ContentValues();
    contentValues.put(ROOM_NUMBER, room.getNumber());
    contentValues.put(ROOM_PRICE, room.getPrice());
    contentValues.put(GUEST_NAME, room.getGuestName());
    contentValues.put(ID_CARD, room.getIdCard());
    contentValues.put(CHECK_IN_DATE, room.getCheckInDate());
    contentValues.put(CHECK_OUT_DATE, room.getCheckOutDate());
    getWritableDatabase().insert(TABLE_ROOM, null, contentValues);
}

public RoomCursor queryRoom(int number) {
    Cursor wrapped = getReadableDatabase().query(TABLE_ROOM, null,
            ROOM_NUMBER + "=?",
            new String[]{String.valueOf(number)},
            null, null, null, "1");
    return new RoomCursor(wrapped);
}

public RoomCursor queryRooms() {
    Cursor wrapped = getReadableDatabase().rawQuery("select * from room", null);
    return new RoomCursor(wrapped);
}

public static class RoomCursor extends CursorWrapper {
    public RoomCursor(Cursor cursor) {
        super(cursor);
    }

    public Room getRoom() {
        if (isBeforeFirst() || isAfterLast()) {
            return null;
        }
        Room room = new Room(-1, -1);
        int roomNumber = getInt(getColumnIndex(ROOM_NUMBER));
        room.setNumber(roomNumber);
        int roomPrice = getInt(getColumnIndex(ROOM_PRICE));
        room.setPrice(roomPrice);
        String guestName = getString(getColumnIndex(GUEST_NAME));
        room.setGuestName(guestName);
        String idCard = getString(getColumnIndex(ID_CARD));
        room.setIdCard(idCard);
        String checkInDate = getString(getColumnIndex(CHECK_IN_DATE));
        room.setCheckInDate(checkInDate);
        String checkOutDate = getString(getColumnIndex(CHECK_OUT_DATE));
        room.setCheckOutDate(checkOutDate);
        return room;
    }
}

} this is Room.java

public class Room {
private int mNumber;
private int mPrice;
private String mGuestName;
private String mIdCard;
private String mCheckInDate;
private String mCheckOutDate;

public Room(int number, int price) {
    mNumber = number;
    mPrice = price;
    mGuestName = " ";
    mIdCard = " ";
    mCheckInDate = " ";
    mCheckOutDate = " ";
}

public int getNumber() {
    return mNumber;
}

public void setNumber(int number) {
    mNumber = number;
}

public int getPrice() {
    return mPrice;
}

public void setPrice(int price) {
    mPrice = price;
}

public String getGuestName() {
    return mGuestName;
}

public void setGuestName(String guestName) {
    mGuestName = guestName;
}

public String getIdCard() {
    return mIdCard;
}

public void setIdCard(String idCard) {
    mIdCard = idCard;
}

public String getCheckInDate() {
    return mCheckInDate;
}

public void setCheckInDate(String checkInDate) {
    mCheckInDate = checkInDate;
}

public String getCheckOutDate() {
    return mCheckOutDate;
}

public void setCheckOutDate(String checkOutDate) {
    mCheckOutDate = checkOutDate;
}

}

DBFlow - inner join with another select statement

I'm trying to convert the following SQL statement into DBFlow method calls:

SELECT t.SSID, t.BSSID, t.Latitude, t.Longitude, t.Timestamp 
FROM wlan_events t 
INNER JOIN (SELECT BSSID, MAX(Timestamp) AS MaxTimestamp FROM wlan_events GROUP BY BSSID) groupedt 
ON t.BSSID = groupedt.BSSID AND t.Timestamp = groupedt.MaxTimestamp

What I got so far:

SQLite.select(WifiEvent_Table.SSID, WifiEvent_Table.BSSID, WifiEvent_Table.latitude,
    WifiEvent_Table.longitude)
    .from(WifiEvent.class)
    .as("t")
    .innerJoin(WifiEvent.class) // ????

;

How do i create that inner join statement using dbflow?

Jasmine JS: How to write unit tests for Cordova SQLite plugin queries?

Let's suppose, we have an angular service that has a function as follows:

this.getAllBranches = function () {

    var deferred = $q.defer();

    db.transaction(function (tx) {
        tx.executeSql("select * from branch", [], function (tx, res) {
            var branchList = [];
            for (var i = 0; i < res.rows.length; i++) {
                var branch = {};
                branch.id = res.rows.item(i).id;
                branch.code = res.rows.item(i).code;
                branch.address = res.rows.item(i).address;
                branch.city = res.rows.item(i).city;
                branch.state = res.rows.item(i).state;
                branch.zip = res.rows.item(i).zip;

                branchList.push(branch);
            }
            deferred.resolve(branchList);
        }, function (t, e) {
            alert('An error occurred getting list of branches.');
            deferred.reject(e);
        });
    });

    return deferred.promise;
};

How do we test the above function?