Getting GetId() to work on a LinkedList()

Further to my question "Extracting multiple data items from LinkedList() element" which now works fine on the demo "Book" app thanks to quocnhat7, I've come across the next issue setting up the onItemClick. In this case, the getId() method, which throws "error: cannot find symbol method getId()" on building the app.

    // get all books
    List<Book> list = db.getAllBooks();
    List<String> listTitle = new ArrayList<String>();

    for (int i = 0; i < list.size(); i++) {
        listTitle.add(i, list.get(i).getTitle());
                }

    myAdapter = new ArrayAdapter(this, R.layout.row_layout, R.id.listText, listTitle);
    getListView().setOnItemClickListener(this);
    setListAdapter(myAdapter);
}

@Override
public void onItemClick(AdapterView arg0, View arg1, int arg2, long arg3) {
    // start BookActivity with extras the book id
    Intent intent = new Intent(this, BookActivity.class);
    // Don't seem to be able to access Book.java for getId function
    intent.putExtra("book", list.get(arg2).getId());
    startActivityForResult(intent, 1);
}

How to write Android Async task for database inserting

I'm new to android. I need to insert huge data stack to database at first run of my app (min number of lines copied to here). This database insertion will take few minuets to complete. I want to add progress bar showing in this time. I looked in the internet and copied some codes, but I never completed this task correctly. Can you help me to do this database insertion through Async task. I used database open helper to insert data. Tank you.

buttonInitiate.setOnClickListener(new View.OnClickListener() {

            @Override
            public void onClick(View v) {               

                dbConn = new DatabaseConnect(getApplicationContext());              

                dbConn.insertContact("1","g1", "m1", "apostolic  nunciature  in  sri  lanka ", "112582554","112597685", "", "112580906");
                dbConn.insertContact("1","g1", "m1", "Cardinal - Archbishop of Colombo His Eminence  Malcolm Cardinal Ranjith", "112695471","112695472", "112695473", "112692009");
                dbConn.insertContact("1","g1", "m1", "Conference  of  major  religious  superiors  sri lanka, Executive Secretary Sister Laetitia Coorey RGS", "766594466","713452929", "", "");
                dbConn.insertContact("1","g1", "bh", "Anuradhapura", "252222503","252234332", "", "252234901");
                dbConn.insertContact("1","g1", "bh", "Badulla", "552229241","552222867", "552222079", "552229241");
                dbConn.insertContact("1","g1", "bh", "Batticaloa", "652227642","652222723", "", "652224667");
                dbConn.insertContact("1","g1", "bh", "Chilaw", "322220625","322223377", "322222332", "322223233");
                dbConn.insertContact("1","g1", "bh", "Galle", "912234256","912223318", "", "914385602");
                dbConn.insertContact("1","g1", "bh", "Jaffna", "212222649","212222161", "", "212229953");
                dbConn.insertContact("1","g1", "bh", "Kandy", "814471601","814471602", "812222300", "812222300");
                dbConn.insertContact("1","g1", "bh", "Kurunegala", "372222854","372224935", "", "");
                dbConn.insertContact("1","g1", "bh", "Mannar", "232222503","232222710", "", "232222709");
                dbConn.insertContact("1","g1", "bh", "Ratnapura", "362232368","362232369", "362232370", "");
                dbConn.insertContact("1","g1", "bh", "Trincomalee", "262222677","262050546", "", "262222238");

                dbConn.insertKey("g1", "General Information");
                dbConn.insertKey("m1", "Main Information");
                dbConn.insertKey("bh", "Bishop`s Houses");
                dbConn.insertKey("nda", "National Directors / Animators");
                dbConn.insertKey("nc", "National Chaplains");
                dbConn.insertKey("cmr", "Congregations of Men Religious");
                dbConn.insertKey("cwr", "Congregations of Women Religious");
                dbConn.insertKey("ccwr", "Contemplative Congregations of Women Religious");


                buttonGo.setEnabled(true);

            }

        });

How do you catch exceptions during class loading

I have a SQLite database and use SQLAlchemy to map the tables to classes. The generate layout is that I have a parent table and a child table. A Parent class may have multiple children, and a Child only one parent. The children are loaded into a custom collection class for various pre and post processing.

There are times that an exception may be raised during the loading of the Child class. This is somewhat abnormal operation, usually due to database corruption or an exceedingly old database, but it does require intervention. However I don't seem to have any way of catching this exception.

As a work around, I assume that instead of raising an Exception, I could flag the class with a self.delete = True and check for this when loading into the collection class. But it still begs the question about what to do about exceptions that may raise during loading of a class from the database mapper?

Not able to get data from Sqlite in Xamarin.Android

I am using this tutorial to show sqlite data in my app. Use a local Database in Xamarin but it did not work for me. help me with my errors in code. Error shown on Visual Studio is "an unhandled exception occur", and app fails to open on my phone. i am not using any emulator. thanks

using System;
using Android.App;
using Android.Content;
using Android.Runtime;
using Android.Views;
using Android.Widget;
using Android.OS;

using System.Data;
using System.IO;
using SQLite;

namespace DicApp
{
public class Album
{
    [PrimaryKey, AutoIncrement]
    public int AlbumId { get; set; }
    public string Title { get; set; }
    public int ArtistId { get; set; }
}

[Activity(Label = "DicApp", MainLauncher = true, Icon = "@drawable/icon")]
public class MainActivity : Activity
{

    protected override void OnCreate(Bundle bundle)
    {
        base.OnCreate(bundle);

        // Set our view from the "main" layout resource
        SetContentView(Resource.Layout.Main);

        string dbName = "db.sqlite";
        string dbPath = Path.Combine(Android.OS.Environment.ExternalStorageDirectory.ToString(), dbName);
        // Check if your DB has already been extracted.
        if (!File.Exists(dbPath))
        {
            using (BinaryReader br = new BinaryReader(Assets.Open(dbName)))
            {
                using (BinaryWriter bw = new BinaryWriter(new FileStream(dbPath, FileMode.Create)))
                {
                    byte[] buffer = new byte[2048];
                    int len = 0;
                    while ((len = br.Read(buffer, 0, buffer.Length)) > 0)
                    {
                        bw.Write(buffer, 0, len);
                    }
                }
            }
        }

        using (var conn = new SQLite.SQLiteConnection(dbPath))
        {
            var cmd = new SQLite.SQLiteCommand (conn);
cmd.CommandText = "select * from Album";
var r = cmd.ExecuteQuery<Album> ();

Console.Write (r);
        }
    }

}
}

How to select the remaining rows after selected some in Android SQLite?

I have a task in which i have to display the rows selected using a query in one tab and the remaining rows in another tab. I have displayed the selected rows using SimpleCursorAdapter. When i tried to display the remaining rows in next tab it throws error. I have tried NOT IN but it also doesn't work. I have tried NOT EXISTS also it shows all rows. Anyone who can answer please help. I have posted my code here. Thanks in advance. This is the activity of first tab which displays selected rows

public class OnlineDevices extends Activity {
ListView listOnline;
DatabaseHelper databaseHelper;
String count;
int conut;
TextView tvOnlineCount;

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

    listOnline=(ListView)findViewById(R.id.listView3);
    tvOnlineCount = (TextView) findViewById(R.id.textView59);

    databaseHelper=new DatabaseHelper(this);
    SQLiteDatabase db=databaseHelper.getReadableDatabase();

    String date= DateFormat.getDateInstance().format(new Date());
    String statusQuery="select rowid _id, deviceContact from statusTable where sentTime='"+date+"'";
    Cursor cursor1=db.rawQuery(statusQuery,null);

    if (cursor1.getCount()>0){
        while (cursor1.moveToNext()){
            String deviceNo=cursor1.getString(cursor1.getColumnIndex("deviceContact"));

            String device=deviceNo.substring(2, 12);

            String query="select rowid _id, userName, uSerialNo from bikeTable where uContactNo='"+device+"' AND userName IS NOT NULL";
            Cursor cursor2=db.rawQuery(query, null);
            SimpleCursorAdapter adapter=new SimpleCursorAdapter(this,R.layout.status_item,cursor2,new String[]{"userName","uSerialNo"},new int[]{R.id.textView51,R.id.textView52});
            listOnline.setAdapter(adapter);
        }
    }
    try {
        conut = listOnline.getAdapter().getCount();
        count = String.valueOf(conut);
        tvOnlineCount.setText(count);
    }catch (Exception e){
        e.printStackTrace();
        int i=0;
        Toast.makeText(OnlineDevices.this,"No device is active",Toast.LENGTH_SHORT).show();
        tvOnlineCount.setText(String.valueOf(i));
    }
}

}

Activity for second tab which display the remaining rows are

public class OfflineDevices extends Activity {

ListView listOffline;
DatabaseHelper databaseHelper;
String deviceContact;

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

    listOffline=(ListView)findViewById(R.id.listView4);

    databaseHelper=new DatabaseHelper(this);
    SQLiteDatabase db=databaseHelper.getReadableDatabase();

    String date= DateFormat.getDateInstance().format(new Date());
    String query="select rowid _id, deviceContact from statusTable where sentTime='"+date+"'";
    Cursor cursor1=db.rawQuery(query,null);

    if (cursor1.getCount()>0){
        while (cursor1.moveToNext()) {
            deviceContact = cursor1.getString(cursor1.getColumnIndex("deviceContact"));
        }
            String device=deviceContact.substring(2, 12);
            String query2="select rowid _id, userName, uSerialNo from bikeTable where userName IS NOT NULL AND uContactNo='"+device+"'";
            Cursor cursor3=db.rawQuery(query2,null);
            String query1="select rowid _id,*from bikeTable where userName IS NOT NULL NOT IN('"+query2+"')";
            Cursor cursor2=db.rawQuery(query1,null);
            SimpleCursorAdapter adapter=new SimpleCursorAdapter(this,R.layout.status_item,cursor2,new String[]{"userName","uSerialNo"},new int[]{R.id.textView51,R.id.textView52});
            listOffline.setAdapter(adapter);


    }
}

}

Extracting multiple data items from LinkedList() element

I have a problem with an Android SQLite Database Example with the following code in MainActivity that fails to build;

"error: cannot find symbol method getTitle()"

    // get all books
    list = db.getAllBooks();
    List listTitle = new ArrayList();

    for (int i = 0; i < list.size(); i++) {
        listTitle.add(i, list.get(i).getTitle());
                }

The getAllBooks() query works fine, however the code is meant to extract the title of a book from the LinkedList() named list that was populated by the query. If I drop off the getTitle() like this...

    // get all books
    list = db.getAllBooks();
    List listTitle = new ArrayList();

    for (int i = 0; i < list.size(); i++) {
        listTitle.add(i, list.get(i));
                }

I get data returned as expected in the new ArrayList() named listTitle

Book [id=1, title=The Great Gatsby, author=F. Scott Fitzgerald]
Book [id=2, title=Load of rubbish, author=A. Nonymouse]
Book [id=3, title=A lot more rubbish, author=A.Nonymouse]
Book [id=4, title=Anna Karenina, author=Leo Tolstoy]
Book [id=5, title=The Grapes of Wrath, author=John Steinbeck]
Book [id=6, title=Invisible Man, author=Ralph Ellison]
Book [id=7, title=Gone with the Wind, author=Margaret Mitchell]
Book [id=8, title=Pride and Prejudice, author=Jane Austen]
Book [id=9, title=Sense and Sensibility, author=Jane Austen]
Book [id=10, title=Mansfield Park, author=Jane Austen]
Book [id=11, title=The Color Purple, author=Alice Walker]
Book [id=12, title=The Temple of My Familiar, author=Alice Walker]
Book [id=13, title=The waves, author=Virginia Woolf]
Book [id=14, title=Mrs Dalloway, author=Virginia Woolf]
Book [id=15, title=War and Peace, author=Leo Tolstoy]

Of course, I was just wanting the title in the new list to display on the device, so the issue is how do I access the individual id or title or author from the LinkList() returned by the query..?

Using sql lite database in my android application

I created a database using DB browser. I should copy the file in assets folder. What should be the extension of the file?

How does SqliteBrowser determine that the data in a cell is a blob?

How does sqlitebrowser determine that the data within a cell is a blob? For some data it will determine it to be a blob, but other data it will say it is not a blob. My example of this is a set of data where there are 10 doubles written out as a blob, yet it does not call it a blob. So I would really really like to know how it determines a cell to be a blob or not be a blob?

Export data in SQLIte database from Android App to remote SQL Server

I'm trying to post data stored in SQLite database on Android app to a remotely hosted SQL Server database.

Also what would be the best way to export data from SQL Server to SQLite database in Android App.

The application under development will post data by connecting to SQL database server through WIFI or over the internet.(This is another area where I would need advise on).

I have come across multiple tutorials like creating a wcf service and consuming it the application, creating php application to send data in JSON format etc.

I'm new to android app development so need advise from professional android developers to suggest the best way for data exchange between android app and SQL server.

ContentProvider call on a table without _id column

Is it necessary to add _id column on the table I am calling operations insert or query ? Or it will itself work out or create such _id column if not present?

Mono on Mac: DllNotFoundException despite SQLite.Interop.dll being in diretory and in $PATH

I have a C# application that uses SQLite and works fine on Windows.

The same Visual Studio project compiles fine in Xamarin Studio, but when running I get:

DllNotFoundException: SQLite.Interop.dll

Despite:

• SQLite.Interop.dll being in the same folder as the executable and other DLLs
• . being part of the $PATH
• My code does not contain any DllImport

What is the problem?

How do you save shared preferences in a sql database?

I am creating an app that needs to save account information for multiple accounts. This data we want to store in an SQL database. We also want to use the familiar, built-in shared preference code format to create and edit this data for each account.

We have created code for a shared preference with the various items for each account.

We also created the code for an SQL database that has all the same fields for each item in the preference as well as a unique, auto incremented, id field (called acct_id). I have the various setters and getters for each field in the record... as well as the typical code for adding a record, deleting a record, and updating a record.

Both the preference screen and the sqllite helper code work... in testing... separately.

What I am not sure of is how to "load" the shared preference screen from the appropriate database record and how to "save" the changes to the appropriate database record. From what I have read on here and the internet, I "think" I need to use a preference editor... but, from what I have read, all the examples I can find are using a flat-file or XML file. Not sure how to use those examples for the database.

Here is my preference fragment code...

public class PrefAcctFrag extends PreferenceFragment implements SharedPreferences.OnSharedPreferenceChangeListener {

@Override
public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    addPreferencesFromResource(R.xml.prefacct);
    for (int i = 0; i < getPreferenceScreen().getPreferenceCount(); i++) {
        initSummary(getPreferenceScreen().getPreference(i));
    }
}

@Override
public void onResume() {
    super.onResume();
    // Set up a listener whenever a key changes
    getPreferenceScreen().getSharedPreferences().registerOnSharedPreferenceChangeListener(this);
}

@Override
public void onPause() {
    super.onPause();
    // Unregister the listener whenever a key changes
    getPreferenceScreen().getSharedPreferences().unregisterOnSharedPreferenceChangeListener(this);
}

public void onSharedPreferenceChanged(SharedPreferences sharedPreferences, String key) {
    updatePrefSummary(sharedPreferences, findPreference(key));
}

private void initSummary(Preference p) {
    if (p instanceof PreferenceCategory) {
        PreferenceCategory pCat = (PreferenceCategory) p;
        for (int i = 0; i < pCat.getPreferenceCount(); i++) {
            initSummary(pCat.getPreference(i));
        }
    } else {
        updatePrefSummary(getPreferenceManager().getSharedPreferences(),p);
    }

}

private void updatePrefSummary(SharedPreferences sharedPreferences, Preference p) {
    if (p==null)
        return;
    if (p instanceof ListPreference) {
        ListPreference listPref = (ListPreference) p;
        p.setSummary(listPref.getEntry());
    } else if (p instanceof EditTextPreference) {
        EditTextPreference editTextPref = (EditTextPreference) p;
        if (p.getKey().equalsIgnoreCase("editKey")) {
            p.setSummary("I am not going to display a password!");
        } else {
            p.setSummary(editTextPref.getText());
        }
    } else if (p instanceof MultiSelectListPreference) {
        // MultiSelectList Preference
        MultiSelectListPreference mlistPref = (MultiSelectListPreference) p;
        String summaryMListPref = "";
        String and = "";

        // Retrieve values
        Set<String> values = mlistPref.getValues();
        for (String value : values) {
            // For each value retrieve index
            int index = mlistPref.findIndexOfValue(value);
            // Retrieve entry from index
            CharSequence mEntry = index >= 0
                    && mlistPref.getEntries() != null ? mlistPref
                    .getEntries()[index] : null;
            if (mEntry != null) {
                // add summary
                summaryMListPref = summaryMListPref + and + mEntry;
                and = ";";
            }
        }
        // set summary
        mlistPref.setSummary(summaryMListPref);

    } else if (p instanceof RingtonePreference) {
        // RingtonePreference
        RingtonePreference rtPref = (RingtonePreference) p;
        String uri;
        if (rtPref != null) {
            uri = sharedPreferences.getString(rtPref.getKey(), null);
            if (uri != null) {
                Ringtone ringtone = RingtoneManager.getRingtone(
                        getActivity(), Uri.parse(uri));
                p.setSummary(ringtone.getTitle(getActivity()));
            }
        }
    }
}
}

As stated, that works fine as is and stores a single account in the typical preferences. I now need to add the code to store it in the sql database.

My SQLLiteHelper looks like this...

public class MySQLiteHelper extends SQLiteOpenHelper {
private static final int DATABASE_VERSION=1;
private static final String DATABASE_NAME="MyDB";

public static final String ACCT_TABLE="acct";
public static final String ACCT_FLD_ID="id";
public static final String ACCT_FLD_TYPE="type";

private static final String CREATE_TABLE_ACCT="CREATE TABLE " + ACCT_TABLE + " ( "+
        "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
        "name TEXT, " +
        "type TEXT)";


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

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL(CREATE_TABLE_ACCT);
}

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

    this.onCreate(db);
}

public void addAccount(TblAccount acct){
    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(ACCT_FLD_NAME,acct.get_name());
    values.put(ACCT_FLD_TYPE,acct.get_type());

    db.insert(ACCT_TABLE, null, values);
    db.close();
}

public TblAccount getAccount(int id){
    SQLiteDatabase db = this.getReadableDatabase();

    String SQL_STRING="SELECT * FROM "+ACCT_TABLE+" WHERE "+ACCT_FLD_ID+" = "+String.valueOf(id);
    Cursor cursor =
            db.rawQuery(SQL_STRING, null);

    TblAccount acct = new TblAccount();

    if (cursor!=null) {
        cursor.moveToFirst();

        acct.set_id(cursor.getInt((cursor.getColumnIndex(ACCT_FLD_ID))));
                    acct.set_name(cursor.getString(cursor.getColumnIndex(ACCT_FLD_NAME)));
        acct.set_type(cursor.getString(cursor.getColumnIndex(ACCT_FLD_TYPE)));
                    cursor.close();
    } else {
        acct = null;
    }
    db.close();
    return acct;
}

public int updateAccount(TblAccount acct){
    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(ACCT_FLD_NAME,acct.get_name());
    values.put(ACCT_FLD_TYPE,acct.get_type());

    int i = db.update(ACCT_TABLE, values, ACCT_FLD_ID+" = ?",new String[] { String.valueOf(acct.get_id())});
    db.close();
    return i;
}

public void deleteAccount(TblAccount acct) {
    SQLiteDatabase db = this.getWritableDatabase();

    db.delete(ACCT_TABLE,ACCT_FLD_ID+" = ?",new String[] { String.valueOf(acct.get_id())});
    db.close();
}
}

So that is where I am stuck. Any help, suggestions, or links to some sample code would be great to get me in the right direction.

Thanks

Storing NSDate in SQLite retrieving and displaying shows in GMT, need to display time in original timezone it was created in

When user creates a new record, I store the NSDate in SQLite with local timezone (say PST). When the same NSDate is retrieved from DB, the time is treated as GMT (which is understandable, since NSDate has no concept of timezone and is absolute).

I want to display the NSDate with the time as per the timezone it was created in - For example, if NSDate is stored in a PST timezone and later, user moves to Northeast US, I still want to be able to retrieve the NSDate from DB and display the time in PST (and not EDT). Should I store the local timezone when the record is created, so that that timezone is used in displaying the time?

This is my code, any help is appreciated.

NSDate *date = xxxxxxxxxxx; // This is retrieved from DB
NSDateFormatter *format = [[NSDateFormatter alloc] init];
format.dateFormat = @"MM/dd/yyyy hh:mm:ss";
NSString *timeStr = [format stringFromDate:date];
NSLog(@"Time :%@",timeStr);

Is it possible to use a generic identifier in a SELECT query in SQLite?

I'm creating a function for querying a SQLite database that should be generic in the sense of reading from multiple pre-defined tables. As part of the function's paremeters, it is necessary to tel which column of the table should be read, an info that is supposed to be passed by an enumerator value. So the call would be something like this:

callMyFunction(enumTableA,enumColumnB);

Since enumColumnB is an enumerator value, the argument is an integer and I would like to identify the column by that integer value without having to use a switch-case for that. Something like this:

SELECT column(enumColumnB) from ...

So instead of passing the name of the column or reading all columns with * and then selecting the desired one when reading, it would use the number of the column as passed by the enumerator value to identify what should be read.

Is it possible to do this for SQLite? I couldn't find any tutorial mentioning such possibility, so I'm almost concluding there is no such possibility (and I'll have to pass the name of the column as argument or use a switch-case at the end), but I'ld like to be sure I don't have this option available.

How to get values from a table without duplicate values

I have a table like this :

id -  phones - group_id

0  ---   11------- 1

1  ---   22------- 2

2  ---   12------- 1

3  ---   11------- 1

4  ---   45------- 1

5  ---   44--------1

Now, How can I get these values ?

11,12,45,44

for example : ".... where group_id=1"

I Google it can't find exact solution.

Android Contacts: Is it possible to reference a given contact entry using a URN?

Let me try and clarify my intentions.

I'm developing an app that accesses to the Android contacts provider. I have already implemented a mechanism for pulling contacts from the contacts provider and storing the results in an SQLite table. Currently, when I query for the results of a contact's _ID, I can retrieve all the data for that contact, phone numbers, email addresses, etc.

However, in order to specify which of those my app should use on future occasions, I have to store the resulting contact data (e.g. CommonDataKinds.Phone.NUMBER, CommonDataKinds.Phone.TYPE etc) in the SQL table.

This presents a problem if the data in the Android contacts provider has changed. One solution I have considered is to re-query the _ID and store the data that has changed. However, implementing such a solution requires gathering all data for that contact, making it difficult to determine the correct contact data to use.

My question is thus:

Is there a unique record key used in the Android Provider's contact data, in the same way as there is in the Provider's contact entry itself? A phone number or email address equivalent of Contact._ID?

Failing that, does the Android contacts provider store the last modified date and time? I'm hoping that if I can't reference the contact data in the provider, I can at least run a check to see if anything has changed since the contact was selected for use in the app, allowing my app to alert the user that the data has changed.

What is the best way to convert .mdb to sqlite to work on android?

I have an access database (.mdb) i would like to convert this database to sqlite so i can put it in my android applications

I would like to ask about the best way to do that?

Good way to store many nested JSON objects and arrays as data structure in Android SQLite?

Considering the below JSON, what would be the best way to store this into SQLite?

I am already parsing this with Gson, but wondering what would be a pain-free way to store this into SQLite and be able to retrieve it with no parsing issues.

I am already storing the desc, deposit objects as a HashMaps. My issue is the lease object. What would be an elegant way to store the leasees array?

Should I just create another Leasee object? And then serialize the ArrayList into a Blob for storage into the database?

{
  "name": "1",
  "desc": {
    "country": "1",
    "city": "1",
    "postal": "1",
    "street": "1",
    "substreet": "1",
    "year": 1,
    "sqm": 1
  },
  "owner": [
    "1"
  ],
  "manager": [
    "1"
  ],
  "lease": {
    "leasee": [
      {
        "userId": "1",
        "start": {
          "$date": 1420070400000
        },
        "end": {
          "$date": 1420070400000
        }
      }
    ],
    "expire": {
      "$date": 1420070400000
    },
    "percentIncrease": 1,
    "dueDate": 1
  },
  "deposit": {
    "bank": "China Construction Bank",
    "description": "Personal Bank Account USA"
  }
}

Wich of these is the right way to do foreign keys?

So, i'm trying sqlite3 and i have a question about Foreign Keys and Indices.

Wich of these methods is the right one ?

CREATE TABLE artist(
  artistid    INTEGER PRIMARY KEY, 
  artistname  TEXT
);
CREATE TABLE track(
  trackid     INTEGER,
  trackname   TEXT, 
  trackartist INTEGER REFERENCES artist
);
CREATE INDEX trackindex ON track(trackartist);

OR this one:

CREATE TABLE student(
  SudentID INTEGER PRIMARY KEY,
  First TEXT,
  Last,
  Contact Text,
  Year INTEGER)

CREATE TABLE loan(
  StudentID INTEGER,
  ISBN INTEGER,
  out INTEGER,
  FOREIGN KEY(SudentID)
      REFERENCES student(SudentID)
      ON DELETE CASCADE
  PRIMARY KEY(StudentID, ISBN)
)
CREATE INDEX StudentID_Index ON student(StudentID)

Could not build lazy iterator for class of Foreing collection member

I have a simple master-detail, when I quey for de master and inspect for the foreign member collection, throw an exception java.lang.IllegalStateException: Could not build lazy iterator for class com.example.entity.detail

List<Master> masters = DBHelper.getMasterDao().queryForAll();

At this point, masters are retrieved fine but at try to access to the foreign member throws the exception.

Class definition

public class Master {

    public Master(){

    }

    @DatabaseField(id = true)
    public int Id;

    @DatabaseField(format = "yyyy-MM-dd'T'HH:mm:ss", dataType = DataType.DATE_STRING)
    public Date CreationDate;

    // Reverse navigation
    @ForeignCollectionField(eager = true)
    public Collection<detail> details;
}

public class Detail{

    @DatabaseField(id = true)
    public long Id;
    @DatabaseField
    public int Month;
    @DatabaseField
    public double Price;
    @DatabaseField
    public double Diff;

    @DatabaseField(canBeNull = true, foreign = true)
    public Master master;


}

Qt Combobox Model Using SQLite

With Sq-lite, how can I set a model for the combo-box that has two table columns added together (ie surName +' '+ firstName)...with MS SQL Server I simply use a view.

When I tried with Sq Lite...nope! all other widgets (ie.QLineEdit and QDateEdits) loose their content and only combo-boxes display please Help!

Periodically outputting SQL table to a file

I'm trying to write a python script that continuously updates a file with the contents of a table in my database.

The table in the database is changing continuously, so I need to periodically update this file as well. I could do a select * query and get all the entries, but what would be great is if I could get the output table when applying the formatting of .mode column and .headers on.

What I've tried to do is create a SQL cursor and execute ".output file.txt", but that gives me an sqlite syntax error. I tried to call from the script os.sys("sqlite3 dbname.db 'select * from table;' > file.txt") but that doesn't seem to work either ("'module' object is not callable").

Is there a way for me to get the nicely formatted sqlite table?

android sqlite database error _id does not exist using existing database

I am using an existing database to build a database in an Android app. I continue to get an error in the debug saying column stop_name does not exist:

    java.lang.RuntimeException: Unable to start activity ComponentInfo{com.bkane56.practice.practiceapp/com.bkane56.practice.practiceapp.MainActivity}: java.lang.IllegalArgumentException: column '_id' does not exist

the schema for the data base is as follows:

CREATE TABLE trips (
    block_id char(40),
    route_id char(40),
    direction_id char(2),
    trip_headsign char(20),
    shape_id char(20),
    service_id char(20),
    _id char(20) PRIMARY KEY,
    FOREIGN KEY(route_id) references routes(_id),
    FOREIGN KEY(service_id) references calendar(_id)
);
CREATE TABLE calendar(
    _id char(40),
    start_date char(10),
    end_date char(10),
    monday char(1),
    tuesday char(1),
    wednesday char(1),
    thursday char(1),
    friday char(1),
    saturday char(1),
    sunday char(1)
);
CREATE TABLE routes (
    route_long_name char(200),
    route_type char(40),
    route_text_color char(40),
    agency_id char(40),
    _id char(40) PRIMARY KEY,
    route_color char(40),
    route_short_name char(40)
);
CREATE TABLE stops (
    stop_lat Decimal(9,6),
    zone_id char(30),
    stop_lon Decimal(9,6),
    _id char(30) PRIMARY KEY,
    stop_desc char(30),
    stop_name char(30),
    location_type char(30),
    stop_code char(30)
);
CREATE TABLE stop_times ( 
    trip_id char(40),
    arrival_time char(40), 
    departure_time char(40), 
    stop_id char(40), 
    stop_sequence char(40), 
    stop_headsign char(40), 
    pickup_type char(40), 
    drop_off_type char(40), 
    shape_dist_traveled char(40),
    timepoint char(20),
    FOREIGN KEY(stop_id) references stops(_id),
    FOREIGN KEY(trip_id) references trips(_id)
);
CREATE TABLE android_metadata (
    locale char(40)
);

The method to construct the database (common on StackOverflow) seems to be working, not sure if correctly. There is no error or notification that the database does not exist. Here is the method:

public void copydatabase() throws IOException {

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

        // Path to the created empty db
        String outfilename = DB_PATH + DB_NAME;

        // Open the empty db as the output stream
        OutputStream myoutput = new FileOutputStream(outfilename);

        // transfer byte from inputfile to 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();

    }

This is the method (in my Database Helper file which extends SQLiteOpenHelper) I am using to get a list of the stop names. This is later used to populate a ViewList.

public Cursor getAllStops() {

        final String TABLE = "stops";
        final String[] COLUMN = new String[] {"stop_name as _id"};
        final String ORDER_BY = "stop_lon";
        String mypath = DB_PATH + DB_NAME;

        try {
            myDataBase = SQLiteDatabase.openDatabase(mypath, null,
                    SQLiteDatabase.OPEN_READONLY);
            Cursor c = myDataBase.query(TABLE, COLUMN, null, null, null, null, ORDER_BY, null);
//            Cursor c = myDataBase.rawQuery("select stop_name as _id from stops", null);
            if (c != null) {
                c.moveToFirst();
                return c;
            } else {
                System.out.println("unable to execute getAllStops, cursor = null!");
            }
        }
        finally {
            myDataBase.close();
        }
        return null;
    }

Is there a way to check the database that the used by the emulator. I cannot find the:

data/data/com.bkane56.practice.practiceapp/databases/metrolink.sqlite

Any help would be greatly appreciated.

SQLite Sum & Group BY Statement

productName | purchasePrice | tax | price 
------------+---------------+-----+--------

Product 1   | 5099          | 16  | 10099
Product 1   | 5099          | 16  | 10099
Product 1   | 5099          | 16  | 10099
Product 1   | 5099          | 16  | 10099
Product 2   | 5099          | 19  | 10099
Product 2   | 5099          | 19  | 10099
Product 2   | 5099          | 19  | 10099

My Calculation for the Total Profit is this

SUM( price- ( price * tax/100.0  + purchasePrice)) as Profit

Result as Profit = 22780.210000000006

My Calculation for the Profit of every Product is this

SUM(price- (price*TAX/100.0 + purchasePrice)) as Profit GROUP BY productName

Result as Profit for Product 1 = 13536,6

Result as Profit for Product 2 = 9243,57

Total 22780,17 Whats wrong here and how can i get the same value?

Android SQLite database...nullpointerException error while accesing table

public Cursor getPro()  
{  
sql=dbhelper.getReadableDatabase();  
return db.query("pro", new String[] {jid,username,password},null,null,null,null,null);  

}

its my getpro method...and here i'm accessing it.

                 c=db.getPro();
                 if(c != null $$ c.moveToFirst())
                 {
                     Toast.makeText(getApplicationContext(), "True", Toast.LENGTH_LONG).show();
                 }
                 db.close();

i want to check whether table has any data or not but app crashes with nullpointerException

sqlite3_exec() Callback function Clarification

I am having trouble understanding the use of the callback function in a SQLite3 database.

I understand it is used to traverse SELECT statements with multiple records. But I do not understand how it does that or how to make my own useful callback. I have read through TutorialsPoint several times to try to understand, but that is just not doing it for me.

When I use their example and debug in Visual Studio to see how the argument arrays are populated and traversed i get lost. Also VS only shows the current slot in the array, not the entire array itself.

If you need any clarification please let me know as I am here to learn!

Cannot insert multiple items into SQLite database

The DB has a primary table for batch data, each Batch can have zero or more Samples. They are linked on Batch.BatchID == Samples.FK_BatchID. The Classes for these tables are shown below.

I can add a value to Batches - the autoincrement BatchID updates as expected. I can add a single value to the Samples. I cannot add multiple values to the Samples table and get an exception with

Additional information: Cannot add an entity with a key that is already in use.

If I set STOP to '1' then the db gets a new Batch with a properly referenced new Sample. What must I do to allow multiple Samples to be added for a single Batch. In addition, I'd ideally like to use the same context and a single 'SubmitChanges()' operation - but let me walk before I run.

Here is the code I've attempted:

 Int64 newbatchID = 0;
 using (var context = new Data.BatchContext(connection))
 {    // This 'chunk' work fine and the newbatchID gets the new value
      context.Log = Console.Out;
      Data.Batches newBatch = new Data.Batches {
           Created = System.DateTime.Now.ToString("u"),
           Title = "New Title",
           Varietal = "Waltz"
      };

      // Return the Batch Id if necessary...
      var qs = from c in context.sqlite_sequence
               where c.name == "Batches"
               select c.seq;
      context.Batches.InsertOnSubmit(newBatch);
      context.SubmitChanges();
      newbatchID = qs.ToList().First();
 }

 // Use the new batch ID to submit a load of new samples
 int STOP = 2;     // PROBLEM. If Stop is not 1 the following fails
 using (var context = new Data.BatchContext(connection))
 {
      context.Log = Console.Out;
      List<Data.Samples> samplelist = new List<Data.Samples>();
      for (var i = 0; i < STOP; ++i)
      {    // Just to get different time values
           System.Threading.Thread.Sleep(500);
           samplelist.Add(
                new Data.Samples {
                     // Commenting out the FK_BatchID doesn't help
                     FK_BatchID = newbatchID,
                     Created = System.DateTime.Now.ToString("u"),
                     ImageURI = String.Format("./Path/Img_{0}.jpg", i)
                });
           }
           context.Samples.InsertAllOnSubmit(samplelist);
           context.SubmitChanges();
      }

Data base classes

[Table(Name = "Batches")]
public class Batches
{
    public virtual ICollection<Batches> batches { get; set; }
    public Batches()
    {
        batches = new HashSet<Batches>();
    }

    // Primary key - nullable to allow Autoincrement
    [Column(Name = "BatchID", IsPrimaryKey = true)]
    public Int64? BatchID { get; set; }

    // Batch creation date
    [Column(Name = "Created")]
    public String Created { get; set; }

    // Other String columns the same as Created
    ...
}

[Table(Name = "Samples")]
public class Samples
{
    public virtual ICollection<Samples> samples { get; set; }
    public Samples()
    {
        samples = new HashSet<Samples>();
    }

    // Primary key - nullable to allow Autoincrement
    [Column(Name = "SampleID", IsPrimaryKey = true)]
    public Int64? SampleID { get; set; }

    // Foreign key to the Batches Table
    private EntityRef<Batches> _batch = new EntityRef<Batches>();
    [Association(Name = "FK_BatchID", IsForeignKey = true, 
                 Storage = "_batch", ThisKey = "FK_BatchID", 
                  OtherKey = "BatchID")]
    public Batches Batches
    {
        get { return _batch.Entity; }
        set { _batch.Entity = value; }
    }
    // Foreign key table entry
    [Column(Name = "FK_BatchID")]
    public Int64? FK_BatchID { get; set; }

    // Date the image was processed by the batch
    [Column(Name = "Created")]
    public String Created { get; set; }

    // Other String columns etc
    ...
 }

Select Row with MAX value while using another GROUP BY

I'm brand new to learning SQL, so my apologies in advance is my question is elementary.

What I'm looking to do is display a column that would tell me the country with the highest and lowest values, respectively, of percent_aged_over_60 within already distinguished groups of "old_country" and "young_country":

SELECT
    CASE
        WHEN percent_aged_over_60 >= 10 THEN "old_country"
        WHEN percent_aged_over_60 < 10 THEN "young_country"
        ELSE "error"
    END AS "oldness",
COUNT(*)
FROM countries_by_population
    GROUP BY oldness;

In other words, I'm looking to add a column that will tell me the name of the country with MAX(percent_aged_over_60) for "old_country" and the MIN(percent_aged_over_60) for "young_country".

While I'm sure this is easiest to do as two separate queries, I would like to do this within the same query.

As such, I'm looking for a result that produces three columns - oldness, count(*) and country.

Please let me know if I can clarify anything!

Thank you for your help!

sqlite3 with FTS4 table: Query returns wrong row

I have a weird issue with my FTS4 index in SQLite3, namely that a MATCH query for one term returns not the exact match but another, similar one, and vice versa.

Here is an example to illustrate it better:

SELECT name FROM test_idx WHERE name MATCH 'lehmbruck-museum';
-- "Lehmbruck-Archiv"
SELECT name FROM test_idx WHERE name MATCH 'lehmbruck-archiv';
-- "Lehmbruck-Museum"

It seems to have something to do with the dash, here is a similar case that exhibits the same behavior:

SELECT name FROM test_idx WHERE name MATCH 'some-thing';
-- "some-thang"
SELECT name FROM test_idx WHERE name MATCH 'some-thang';
-- "some-thing"

Here is how this test database is built, in case somebody wants to have a go at reproducing it:

CREATE VIRTUAL TABLE test_idx USING fts4(name);
INSERT INTO test_idx (name) VALUES
    ('some-thing'), ('some-thang'),
    ('Lehmbruck-Museum'), ('Lehmbruck-Archiv');

How to get List of SQLite columns records in android?

I create a sqlite database name Bands and a table name PinkFloyd.

pId   pName    pFamily

1     David    Gilmour
2     Roger    Waters
3     Richard  Wright

now I want to get all columns of PinkFloyd records.

I create a method in my BandsDataSource.java to get all columns records:

private static final String[] pinkfloydColumns = {
            IronClubDBOpenHelper.COLUMN_P_ID,
            IronClubDBOpenHelper.COLUMN_P_NAME,
            IronClubDBOpenHelper.COLUMN_P_FAMILY
    };

public List<BandsTablesModel> findAll() {

        List<BandsTablesModel> bandsTablesModelList = new ArrayList<BandsTablesModel>();
        Cursor cursor = database.query(BandsDBOpenHelper.TABLE_PINKFLOYD, pinkfloydColumns, null, null, null, null, null);
        Log.i(LOGTAG, "Returned " + cursor.getCount() + " rows.");
        if (cursor.getCount() > 0) {
            while (cursor.moveToNext()) {

                BandsTablesModel bandsTablesModel = new BandsTablesModel();
                BandsTablesModel.setPId(cursor.getInt(cursor.getColumnIndex(BandsDBOpenHelper.COLUMN_P_ID)));
                BandsTablesModel.setPName(cursor.getString(cursor.getColumnIndex(BandsDBOpenHelper.COLUMN_P_NAME)));
                BandsTablesModel.setPFamily(cursor.getString(cursor.getColumnIndex(BandsDBOpenHelper.COLUMN_P_FAMILY)));
                bandsTablesModelList.add(BandsTablesModel); 

                Log.i(LOGTAG, "Returned " + cursor.getInt(cursor.getColumnIndex(BandsDBOpenHelper.COLUMN_P_ID)) + " rows");  
                Log.i(LOGTAG, "Returned " + cursor.getString(cursor.getColumnIndex(BandsDBOpenHelper.COLUMN_P_NAME)) + " rows");  
                Log.i(LOGTAG, "Returned " + cursor.getString(cursor.getColumnIndex(BandsDBOpenHelper.COLUMN_P_FAMILY)) + " rows"); 
            }
        }
        return BandsTablesModelList;
    }

when I call the findall() method, it gives me nothing in Logcat!

Returned 3 rows.
Returned 0 rows
Returned bandName rows
Returned bandFamily rows
Returned 0 rows
Returned bandName rows
Returned bandFamily rows
Returned 0 rows
Returned bandName rows
Returned bandFamily rows

my Bands database and pinkfloyd Table has records, so how it's possible?

Logcat should be like this:

Returned 3 rows.
Returned 1 rows
Returned David rows
Returned Gilmour rows
Returned 2 rows
Returned Roger rows
Returned Waters rows
Returned 3 rows
Returned Richard rows
Returned Wright rows

Thank you for helping.

SQLite - How to fix syntax error (code 1)

Inside of my Input.java file I have

public class Input {

    private int _id;
    private String _username;

    public Input(){

    }

    public Input(String username){
        this._username = username;
    }

    public void set_id(int _id) {
        this._id = _id;
    }

    public void set_username(String _username) {
        this._username = _username;
    }

    public int get_id() {
        return _id;
    }

    public String get_username() {
        return _username;
    }
}

and inside of my DBHandler.java

public class DBHandler extends SQLiteOpenHelper {

    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "input.db";
    public static final String TABLE_INPUT = "input";
    public static final String COLUMN_ID = "_id";
    public static final String COLUMN_USERNAME = "username";

public DBHandler(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_INPUT + "(" +
            COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT " +
            COLUMN_USERNAME +  " TEXT " +
            ");";
    db.execSQL(query);
}

Whenever I try to execute this, I get the error:

Caused by: android.database.sqlite.SQLiteException: near "username": syntax error (code 1): , while compiling: CREATE TABLE input(_id INTEGER PRIMARY KEY AUTOINCREMENT username TEXT );

What exactly am I doing wrong?

Android app crash sqlite

I built database handler and avery time im calling function that return a number of rows the app crashes This is the code:

    private static final String CREATE_TABLE_USERS = "CREATE TABLE " + TABLE_NAME + "("
        + COLUMN_NAME_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
        + COLUMN_NAME_USERNAME + " TEXT, "
        + COLUMN_NAME_PASSWORD + " TEXT, "
        + COLUMN_NAME_EMAIL + " TEXT " + ")";

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

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL(CREATE_TABLE_USERS);
}

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

public int getCountRows(){
    SQLiteDatabase db = this.getWritableDatabase();
    int i = db.rawQuery("SELECT * FROM " + TABLE_NAME, null).getCount();
    return i;
}

The usage:

    public void loginButtonClicked(View view){
    dbHandler.createUser(user);
    Toast.makeText(this, dbHandler.getCountRows(), Toast.LENGTH_SHORT).show();
}

Thanks for help

this is the logcat a lot of not understandable errors bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

logcat
  **java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.company.loginregistersystem/com.example.company.loginregistersystem.MainActivity}: java.lang.IllegalStateException: attempt to re-open an already-closed object: SQLiteQuery: SELECT  * FROM users
            at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2059)
            at **android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2084)
            at android.app.ActivityThread.access$600(ActivityThread.java:130)
            at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1195)
            at** android.os.Handler.dispatchMessage(Handler.java:99)
            at android.os.Looper.loop(Looper.java:137)
            at android.**app.ActivityThread.main(ActivityThread.java:4745)
            at java.lang.reflect.Method.invokeNative(Native Method)
            at java.lang.reflect.Method.invoke(Method.java:511)
            at com.android.internal.os.ZygoteInit$MethodAndArgsCall**er.run(ZygoteInit.java:786)
            at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553)
            at dalvik.system.NativeStart.main(Native Method)
     Caused by: java.lang.IllegalStateException: attempt to re-open an already-closed object: SQLiteQuery: SELECT  * FROM users
            at android.database.sqlite.SQLiteClosable.acquireReference(SQLiteClosable.java:55)
            at android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:58)
            at android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:143)
            at android.database.sqlite.SQLiteCursor.getCount(SQLiteCursor.java:133)
            at com.example.company.loginregistersystem.DBHandler.getCountRows(DBHandler.java:62)
            at com.example.company.loginregistersystem.MainActivity.onCreate(MainActivity.java:28)
            at android.app.Activity.performCreate(Activity.java:5008)
            at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1079)
            at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2023)
            at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2084)
            at android.app.ActivityThread.access$600(ActivityThread.java:130)
            at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1195)
            at android.os.Handler.dispatchMessage(Handler.java:99)
            at android.os.Looper.loop(Looper.java:137)
            at android.app.ActivityThread.main(ActivityThread.java:4745)
            at java.lang.reflect.Method.invokeNative(Native Method)
            at java.lang.reflect.Method.invoke(Method.java:511)
            at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:786)
            at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553)
            at dalvik.system.NativeStart.main(Native Method)**

Getting TypeLoadException when using Sqlite-WinRT

i am developing a Windows Phone 8.1 app (WinRT) and i want to add support for SQLite. I have added the SQLite for Windows Phone extension to the project and also added the SQLite-WinRT wrapper. I also changed the build configuration to x86 so it can run in the emulator. My problem is: When i try to open a connection to a database file (either create or just open) i get a TypeLoadException. My code to open the connection is like this:

using (var db = new SQLiteWinRT.Database(ApplicationData.Current.TemporaryFolder, "Database.db"))
        {
            await db.OpenAsync(SqliteOpenMode.OpenOrCreateReadWrite);
            await db.ExecuteStatementAsync(@"create table if not exists MyTable ( ID integer not null primary key autoincrement, Name varchar(150) not null);");

            await db.ExecuteStatementAsync(@"insert into MyTable (Name) values ('Rafael');");
        }

When the first line is executed i get this error:

Here is a list of references in my project:

Any help is appreciated. Thank you all.

SQLite random() rows but 1 of them must have a specific value?

So, I am developing a kind of Quiz game, the game will have a question on the screen and below 4 buttons, which will be filled with 1 right answer and 3 wrong answers. Simple right? I'm trying to use the following rawQuery() to load 4 answers, all of them must be random, except the right answer that has to appear.

SQLite:

myCursor = db.rawQuery("SELECT * FROM Answers WHERE QuestionID = 3 ORDER BY RANDOM() LIMIT 4", null);

So, QuestionID is the field that is specifying which question is being displayed to load the proper answers. I'll need to have a lot of answers so that the player can't memorise them. Is something like this pseudo-code possible?

myCursor = db.rawQuery("SELECT * FROM Answers WHERE QuestionID = 3 ORDER BY RANDOM('WHERE RightWrong = 1 LIMIT 1') LIMIT 4", null);

RightWrong is the field that tells if it's the right answer or not (inside the DB), it's an INT, if it is set to 1 then it is the correct answer.

I'm using SQLiteAssetHelper with SQLite DB Browser.

SQLite extensions in BIRT

I have an Sqlite database local copy file (file.db). I am trying to make reports using BIRT. I have connected the data to BIRT using JDBC driver for SQLite. The problem is that in the SQLite database, we rely on .dll extensions to load views for the tables. How do I load that extension into BIRT to be able to see and work with the views?

Thanks

Copy SQLite database to android device

I'm already created my application used Android Studio. The database also completely done, when I run my application using emulator, that application can access the data in database. But the problem is when I try to run my application to the phone/android device, the database cannot be access. I run it by "choose a running device", where my phone already connected to the PC and already has been "root". thank you so much for help.

How do you synchronize data between SQLite and Azure in Android?

Ladies and Gentlemen,

I am currently working on an Android app, which is connected to a Microsoft Azure Backend. I have some trouble, synchronizing the app's database with the backend, as my current approach seems overly complicated. I searched for best practices / tutorials on synchronization without using SyncAdapter, but could not find anything that covers both storage and synchronization.

Functionality: A user can subscribe to a topic. This subscription shall be pushed to the Backend, so that the backend can send push notifications. So, the backend has to know, which users subscribed to which topics.

Task: Subscriptions are created and deleted on the Android app via a button click. They have to be stored offline in a database and also synced with Azure.

Current Approach: After reading multiple tutorials on databases and synchronization with android, I chose the following approach:

• Subscriptions are directly saved in a SQLite database on the phone. Each subscription is saved with lastUpdated = now() and deleted = false. If a subscription is deleted, lastUpdated is updated and deleted is set to true.
• A DataSynchronizer listens to updates and deletes on the subscription table. He stores the time of the last sync, lastSynced. For every update / insert / delete, he collects all subscriptions with lastUpdated > lastSynced and applies the changes via the MobileServiceClient provided by Microsoft Azure.

My question: Is there something you would do differently? As I said, this solution seems complicated and not like the best practice way.

Android SqliteAssetHelper copy user saved data

I am using SQLiteAssetHelper utility to manage the copying of the database from my asset folder. In the app, the user enters some data into the database and as soon as I upgrade the db, all the data gets deleted cause I have set setForcedUpgrade();. I heard you can use upgrade scripts, but I think they do not help me in this case as the data is unique for each user. How can I solve this?

sqlite database stack smashing while putting epoch time into integer field

I use sqlite database and it has one column for epoch time, I use its type as INTEGER because I don't find any other suitable type for that. When I try to add data in that field through my C program it shows me * stack smashing detected *. probably it is because i try to put epoch time to INTEGER field. But it allows only following types. The types supported by database: text,integer,real,blob,null. what would be suitable type and if it is INTEGER how to remove this error.

How to retrieve existing number from column and update it

I am trying to make this simple app which calculates likes. Every-time you click on like button, it updates the counter in DB. The problem is, when I click it 5 times in one session, close and open again, it starts from 1 instead of 6 and shows 1,2,3,4,5,1. The value is lost once the session ends. I want it to be 6 and continue. How can I retrieve values from Database. Also, the rows are increasing. I want it to update the count in a single row and not add a new row every time I click the button.

This is my Main Activity:

public class MainActivity extends AppCompatActivity {
//declare
TextView showCounts;
MyDBHandler dbHandler;
Calculate obj;
@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);
    //initialize objects
    showCounts = (TextView) findViewById(R.id.showCounts);
    dbHandler = new MyDBHandler(this, null, null, 1);
    obj = new Calculate();

}

public void likeButtonClicked(View view) //when like button is clicked
{
    obj.set_likes(1); //clicked 1 time
    boolean isLiked = dbHandler.incrementLikes(); //increment like in database
    printDatabase(); //show db
    if(isLiked) { //if like goes into db
        Toast.makeText(MainActivity.this, "Liked successfully", Toast.LENGTH_LONG).show();
    }
    else
        Toast.makeText(MainActivity.this, "Error", Toast.LENGTH_LONG).show();
}

public void dislikeButtonClicked(View view)//dislike button is clicked
{
    obj.set_dislikes(1);
    boolean isInserted = dbHandler.incrementDislikes();
    printDatabase();
    if (isInserted) {
        Toast.makeText(MainActivity.this, "Added successfully", Toast.LENGTH_LONG).show();
    } else
        Toast.makeText(MainActivity.this, "Error", Toast.LENGTH_LONG).show();
}

// print database
public void printDatabase()
{
    Cursor res = dbHandler.dbToString();
    if(res.getCount() == 0) //if table is empty
    {
        showCounts.setText("Error Nothing Found!");
        return;
    }

    StringBuffer str = new StringBuffer(); //if not empty
    while(res.moveToNext())
    {
        str.append("Likes: "+res.getString(0)+"\n");
    }
    showCounts.setText(str.toString()); //display database in textview
}

This is my Calculate class which has getters and setters:

public class Calculate {
private static int _likes;
private static int _dislikes;

public Calculate() 
{    }

public int get_likes() {
    return this._likes;
}

public void set_likes(int likes) {
    _likes += likes;

}

public int get_dislikes() {
    return this._dislikes;
}

public void set_dislikes(int dislikes) {
    _dislikes += dislikes;
} }//class ends

This is my DBHandler

public class MyDBHandler extends SQLiteOpenHelper{
//Declarations
private static final int DATABASE_VERSION = 4; 
private static final String DATABASE_NAME = "likeCalci.db";
public static final String TABLE_LIKECALCULATOR = "likeCalculator";
public static final String COLUMN_LIKE = "_likes";
public static final String COLUMN_DISLIKE = "_dislikes";
int like_count = 0;
int dislike_count = 0;

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

@Override
public void onCreate(SQLiteDatabase db)
{
    //create table
    String query = "CREATE TABLE " + TABLE_LIKECALCULATOR + "("+
            COLUMN_LIKE + " INTEGER " + COLUMN_DISLIKE + " INTEGER " + ");";
    db.execSQL(query);
}

@Override
public void onUpgrade(SQLiteDatabase db, int i, int i1) //upgrade db
{
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_LIKECALCULATOR);
    onCreate(db);
}

//call when like button is clicked
public boolean incrementLikes()
{
    Calculate obj = new Calculate(); //obj
    like_count = obj.get_likes(); //get number of likes and store it
    ContentValues values = new ContentValues(); 
    values.put(COLUMN_LIKE, like_count); //put values in column
    SQLiteDatabase db = getWritableDatabase();
    long result = db.insert(TABLE_LIKECALCULATOR, null, values); //insert likes in db
    db.close();
    if(result == -1) //if insert is unsuccessful
        return false;
    else //if insert is successful
        return true;
}

public boolean incrementDislikes()
{
    Calculate obj = new Calculate();
    dislike_count = obj.get_dislikes();
    ContentValues values = new ContentValues();
    values.put(COLUMN_DISLIKE, dislike_count);
    SQLiteDatabase db = getWritableDatabase();
    long result = db.insert(TABLE_LIKECALCULATOR, null, values);
    db.close();
    if(result == -1)
        return false;
    else
        return true;
}

//print database
public Cursor dbToString() 
{
  SQLiteDatabase db  = this.getWritableDatabase();
    Cursor res = db.rawQuery("select * from "+TABLE_LIKECALCULATOR,null);
    return res;
}} //Class dbhandler ends

This is how the program runs: http://ift.tt/1R1vfrr

The dislike column is also not created. I started learning android few days back and this is taking a lot of time. Any times while dealing with Databases?

Populate ListView in android from SQLite database

I am just learning android programming and now i am making an app that uses database. For now i am using SQLite to store the data.

I have successfully created app which can stored/insert data in a database. Its working and i have checked my database using root Explorer.

Now on 2nd activity lets say DisplayRecords, i want to display my records into list view which will populate dynamically. How can i achieve that through programming.

here is my code of 2nd activity.

public class SearchResult extends ActionBarActivity {

ListView ls;
SQLiteDatabase db;

@Override
protected void onCreate(@Nullable Bundle savedInstanceState) {
    // TODO Auto-generated method stub
    super.onCreate(savedInstanceState);
    setContentView(R.layout.search_result);

     Toolbar toolbar = (Toolbar)findViewById(R.id.toolbar);
     setSupportActionBar(toolbar);


     ls = (ListView)findViewById(R.id.displayListView);

}

Can anyone please help me

Thanks

Need filtered result Optimised query

I have one table Called Device

        Device Table
id    deviceName     genId       description               overview 
1      PV motor       5       motor capacity  5        motor load is good
2      puf pannel     6       puf capacity  2          puf is for small load
3      gd motor       5       motor capacity 7         motor load is very good
4      rg motor       5       capacity is 3            low capacity

Now suppose this table has thousands of records , i need to add searching of rows like , genId=5 and description Like='%motor%' OR Overview Like='%motor%'

Search result will be 
1      PV motor       5       motor capacity  5        motor load is good

3      gd motor       5       motor capacity 7         motor load is very good

I need to construct query like first it search for genId 5 from table , then after it search for description and overview having text like motor because if my table had 100 records and then only 5 records of genSet=5 then my text search query will be executed on those 5 rows instead of 100 rows .

My Search query :
Select * From Device where (genId=5) And (description Like '%motor%' Or overview Like '%motor%') 

Can any one help me to create optimized query ?

ActiveAndroid abstract class table

Does anyone know if it is possible to create a table for an abstract class in ActiveAndroid. For example i could have an abstract class Animal with two concrete classes Dog and Cat. I want to be able to do something like:

List<Animal> animals = new Select().from(Animals.class).execute();

which would result in 'animals' containing all of the saved Cats and Dogs. Or:

Animal animal = new Select().from(Animals.class).where("name = ?", name).executeSingle();

Where 'animal' could be either a Cat or a Dog. Unfortunately when I do this I get an exception because no table is created for the abstract class Animal. Does anyone know how I might go about this using ActiveAndroid?

SQlite Calculation with same Values different Results

My Database contains this Values

1. ROW: productName = Product 1, purchasePrice = 5099 , tax = 16, price = 10099
2. ROW: productName = Product 1, purchasePrice = 5099 , tax = 16, price = 10099
3. ROW: productName = Product 1, purchasePrice = 5099 , tax = 16, price = 10099

4. ROW: productName = Product 1, purchasePrice = 5099 , tax = 16, price = 10099

5. ROW: productName = Product 2, purchasePrice = 5099 , tax = 19, price = 10099

6. ROW: productName = Product 2, purchasePrice = 5099 , tax = 19, price = 10099
7. ROW: productName = Product 2, purchasePrice = 5099 , tax = 19, price = 10099

My Calculation for the Total Profit is this

SUM( price- ( price * tax/100.0  + purchasePrice)) as Profit

Result as Profit = 22780.210000000006

My Calculation for the Profit of every Product is this

SUM(price- (price*TAX/100.0 + purchasePrice)) as Profit GROUP BY productName

Result as Profit for Product 1 = 13536,6

Result as Profit for Product 2 = 9243,57

Total 22780,17

I have to round this Values and divide them by /100.0 because I've read that it is better to not store the values with floating points in Sqlite.

I do the rounding and /100.0 like this

Round for Total with the Result of = 227.8

ROUND((SUM( price- ( price * tax/100.0  + purchasePrice)))/100 ,2) as Profit

Round for every Product

ROUND((SUM( price- ( price * tax/100.0  + purchasePrice)))/100 ,2) as Profit

Product 1 result = 135,37

Product 2 result = 92,44

Total 227,81 but the Round for Total gives me the Result of = 227.8

Any ideas?

Moving C# SQLite Program to new Machine

I have a SQLite Database already filled with tables and data, my program reads and writes from this table already.

However I need to deploy this Program on other machines and when I do I get the error

"SQL Logic Error or missing database no such table: Customer"

The SQLite database is saved in the root of the C Drive, and it is there on the machines I have tested on.

This is my Connection String

 Data Source= "C:\EpasSqLite.db"; Version = 3;

How do I resolve this?

How to full text search in SQLite without tokenizing, but rather looking for substrings

I would like to utilize the full-text search features (FTS3 or FTS4 extensions) of SQLite to search through biological (DNA, protein) sequences. Unlike what seems to be the designer use-case for FTS, I'd like my search to match any substring of a specific column, with no regard to delimiters or tokens.

I saw I can limit the columns being indexed by using the notindexed= option, and that I can use prefix matching by using the prefix= option. Since my sequences don't include any delimiters (they only include latin alphabet characters), I can regard each of the sequences as a single token, and set up a prefix search. This has two problems, though. The first is that I'd like to search through substrings, not only prefixes. The second is that each sequence will be stored twice, if I'm not mistaken - once as the contents, and the second time as a token. I could create a contentless table to overcome the second caveat, but I don't know how to address the first.

To conclude, my question is: is there a way to full-text search (using an index) on substrings which aren't prefixes in SQLite?

Thanks!

Is it possible to use SQLite database inside a web application

Since I am new to web application developments, please bear for my silly question.

I am having a scenario wherein the web application want to have a SQLite database inside it.

Also need to access the database with the help of an servlet class/ajax request

I am not sure, its possible or not. please advise me

1. Is it possible to have a sqlite database inside a application
2. Id Yes,is it possible to access the same via servlet/ajax request
3. If yes, is the database will be locally available at client side or not.

Please help me ....!! thanks in advance

useing php while loop json_encode

I am trying to use json_encode() in a while loop while getting database results. Here is my code:

 <?
    $sql_page = "select * from type";
    $rs_type=mysql_query($sql_page,$link);
    while ($row=mysql_fetch_array($rs_type)){
$jsonString =array( "n" => $row['tTitle'],
  "s" => array( array("n" => $row['tTitle1'],
  "s" => array( array("n" => $row['tTitle2'],
 "s" => array( array("n" => $row['tTitle3'],
 "s" => array( array("n" => $row['tTitle4'],
  ),
  ),
  ),
  ),
  ),
  ),
  ),
  ),
);


    $cart = json_encode( $jsonString );
    $file = '../application/js/tecair.json';
    file_put_contents($file, '['.$cart.']');
    ?>

The output following below is

[{"n":"1","s":[{"n":"1_1","s":[{"n":"1_2","s":[{"n":"1_3","s":[{"n":"1_4"}]}]}]}]}]

but i want it to be:

[ { "n":"1", "s":[ { "n":"1_1", "s":[{"n":"1_1_1"},{"n":"1_1_2"},{"n":"1_1_3"},{"n":"1_1_4"}] },{ "n":"1_2", "s":[{"n":"1_2_1"},{"n":"1_2_2"},{"n":"1_2_3"},{"n":"1_2_4"},{"n":"1_2_5"},{"n":"1_2_6"}] },{ "n":"1_3", "s":[ { "n":"1_3_1", "s":[ { "n":"1_3_1_1", "s":[{"n":"1_3_1_1_1"},{"n":"1_3_1_1_2"},{"n":"1_3_1_1_3"},{"n":"1_3_1_1_4"}] } ] } ] },{ "n":"1_4", "s":[{"n":"1_4_1"},{"n":"1_4_2"},{"n":"1_4_3"},{"n":"1_4_4"}] } ] },{ "n":"2" },{ "n":"2_1", "s":[{"n":"2_1_1"},{"n":"2_1_2"},{"n":"2_1_3"},{"n":"2_1_4"},{"n":"2_1_5"},{"n":"2_1_6"}] } ]

How do I use while loop to do it well? thanks!

How to convert MSSQL .sql database file into .sqlite file using PHP

I am working for application which will work offline also, so i have question

How to convert MSSQL .sql database file into .sqlite file using PHP ?

I am not going to use any external converter.

Please suggest

Thanks

Python code running too slow (SQLITE)

I have located a piece of code that runs quite slow (in my opinion) and would liek to know what you guys think. The code in question is as follows and is supposed to:

• Query a database and get 2 fields, a field and its value
• Populate the object dictionary with their values

The code is:

query = "SELECT Field, Value FROM metrics " \
        "WHERE Status NOT LIKE '%ERROR%' AND Symbol LIKE '{0}'".format(self.symbol)
query = self.db.run(query, True)
if query is not None:
    for each in query:
        self.metrics[each[0].lower()] = each[1]

The query is run using a db class I created that is very simple:

def run(self, query, onerrorkeeprunning=False):
    # Run query provided and return result
    try:
        con = lite.connect(self.db)

    cur = con.cursor()
    cur.execute(query)
    con.commit()

    runsql = cur.fetchall()
    data = []
    for rows in runsql:
        line = []
        for element in rows:
            line.append(element)
        data.append(line)
    return data

except lite.Error, e:
    if onerrorkeeprunning is True:
            if con:
                con.close()
            return
    else:
        print 'Error %s:' % e.args[0]
        sys.exit(1)

finally:

    if con:
        con.close()

I know there are tons of ways of writting this code and I was trying to keep things simple but for 24 fields this takes 0.03s so if I have 1,000 elements that is 30s and I find it a little too long!

Any help will be much appreciated.

How to Perform Full Text search functionality using MS Entity Framework 6 & sqlite?

I have to implement Full text search functionality using Entity Framework 6.1 with SQLite DB. How to Implement the functionality using C# Linq.

how to get the selected item inside spinner populated from sqlite database

I have written a program to get some values from sqlite database and populate inside spinner ,which is working perfectly , but i want to show the selected item from the spinner into a different activity , all i am getting is either a blank value or android.database.sqlite.SQLiteCursor@3456046c , so how to convert it to string ??

Need some help

int[] to = new int[] { android.R.id.text1 };
    String[] from = new String[] { DbListHelper.ACCOUNT_TYPE };
    final SimpleCursorAdapter adapter4 = new SimpleCursorAdapter(
            getBaseContext(), android.R.layout.simple_list_item_1, cursor,
            from, to) {
        public View getView(int position, View convertView, ViewGroup  
            parent) {
            View v = super.getView(position, convertView, parent);
            ((TextView) v).setTextSize(18);
            ((TextView) v).setGravity(Gravity.CENTER);
            ((TextView) v).setTextColor(Color.parseColor("#1C689C"));
            return v;
        }

    };

    adapter4.setDropDownViewResource
    (android.R.layout.simple_spinner_dropdown_item);
    spinnerType.setAdapter(adapter4);

On button click to save the records i am doing this , acName is the spinner variable which i am inserting , i also tried to put a dummy value inside acName as acName = "1" , which is showing properly .

    btnPrint.setOnClickListener(new OnClickListener() {

                    @Override
                    public void onClick(View v) {

                        if(cursor.moveToFirst()){
                            do {
                                acName = 
    cursor.getString(cursor.getColumnIndex(DbListHelper.ACCOUNT_NAME));
                            } while (cursor.moveToNext());

                        }
                        vehicleNo1 = editVehicleNo1.getText().toString();

                        driverName = editDriverName.getText().toString();
                        driverCode = editDriverCode.getText().toString();
                        dieselRate = editDieselRate.getText().toString();
                        dieselQty = editDieselQty.getText().toString();
                        amount = editAmount.getText().toString();
                        referenceNo = editReferenceNo.getText().toString();
                        noOfCopies = editNoOfCopies.getText().toString();
                        remark = editRemark.getText().toString();
                        transactionID = imeiCode + "" + dateTime;

                        if (spinnerData.equals("") || vehicleNo1.equals("")
                                || spinnerAccountName.equals("")
                                || spinnerFuelRate.equals("")
                                || driverName.equals("")
                                || dieselQty.equals("")
                                || dieselRate.equals("")
                                || amount.equals("")
                                || referenceNo.equals("")) {
                            Toast.makeText(getBaseContext(),
                                    "Please fill all the fields",
                                    Toast.LENGTH_LONG).show();
                        }
                        else if (noOfCopies.equals("")) {
                            Toast.makeText(getBaseContext(),
                                    "Please enter any value",
                                    Toast.LENGTH_LONG).show();
                        } else {
                            long id = listHelper.insertPumpData(
                                    acName, vehicleNo1, 
                                    driverName,driverCode,
                                    dieselRate, dieselQty, amount + " Rs",
                                    referenceNo, noOfCopies, date, time,
                                    transactionID);


                        }

                    }
                });

Thank You

SQLite: Can I have a where statement inside another where statement?

I am writing SQL queries in Ruby on Rails application and I will execute these statements in SQLite data base.

Because my queries are composed by other complicated statements, I thought that I can include a where statement inside another where statement.

for example:

SELECT * FROM "patients" INNER JOIN "encounters" INNER JOIN "conditions" WHERE "patients"."patient_characteristic_birthdate" <= '1997-06-30 07:25:50 UTC' AND "encounters"."length_of_stay" <= 120 AND "encounters"."encounter" = 'Inpatient Encounter' AND (SELECT * FROM "patients" INNER JOIN "conditions" WHERE ("conditions"."diagnosis" = 'cancer' OR "conditions"."diagnosis" = 'obesity')))

For this statement, I have the following error: ActiveRecord::StatementInvalid (SQLite3::SQLException: only a single result allowed for a SELECT that is part of an expression:

SQLiteDatabase error in android on inserting

I have a problem trying to insert some data into database (SQLiteDatabase) in android. I'm building simple notepad app and I just can't figure out what is wrong. Below is code of my project.

Notes.java

package com.cidecode.xnotes;
public class Notes {
    private long id;
    private String title;
    private String note;
    private String date;

    public Notes(){

    }

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getNote() {
        return note;
    }

    public void setNote(String note) {
        this.note = note;
    }

    public String getDate() {
        return date;
    }

    public void setDate(String date) {
        this.date = date;
    }

    @Override
    public String toString(){
        return title + ", " + note + ", " + date;
    } 
}

DatabaseHelper.java

package com.cidecode.xnotes;

import android.content.Context;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHelper extends SQLiteOpenHelper {

    public static final String TABLE_NOTES = "notes";
    public static final String COLUMN_ID = "id";
    public static final String COLUMN_TITLE = "title";
    public static final String COLUMN_NOTE = "note";
    public static final String COLUMN_DATE = "date";

    private static final String DATABASE_NAME = "xnotes.db";
    private static final int DATABASE_VERSION = 1;

    // Create the database
    private static final String DATABASE_CREATE = "create table " + TABLE_NOTES + "(" +
            COLUMN_ID + " integer primary key autoincrement, " + COLUMN_TITLE + " text not null, " +
            COLUMN_NOTE + " text not null, " + COLUMN_DATE + " text not null);";

    // Drop table notes
    private static final String DATABASE_DROP_TABLE_NOTES = "drop table if exists " + TABLE_NOTES;

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

    @Override
    public void onCreate(android.database.sqlite.SQLiteDatabase db) {
        db.execSQL(DATABASE_CREATE);
    }

    @Override
    public void onUpgrade(android.database.sqlite.SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.w(DatabaseHelper.class.getName(), "Upgrading database from v" + oldVersion + " to v" +
                newVersion + " which will delete all old data.");

        db.execSQL(DATABASE_DROP_TABLE_NOTES);
        onCreate(db);
    }
}

NotesDataSource.java

package com.cidecode.xnotes;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.widget.EditText;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class NotesDataSource {
    private SQLiteDatabase database;
    private DatabaseHelper dbHelper;
    private String[] allColumns = {DatabaseHelper.COLUMN_ID, DatabaseHelper.COLUMN_TITLE, DatabaseHelper.COLUMN_NOTE, DatabaseHelper.COLUMN_DATE};

    public NotesDataSource(Context context){
        dbHelper = new DatabaseHelper(context);
    }

    public void open() throws SQLException{
        database = dbHelper.getWritableDatabase();
    }

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

    public Notes createNote(String title, String note, String date){
        ContentValues values = new ContentValues();
        values.put(DatabaseHelper.COLUMN_TITLE, title);
        values.put(DatabaseHelper.COLUMN_NOTE, note);
        values.put(DatabaseHelper.COLUMN_DATE, date);
        long insertId = database.insert(DatabaseHelper.TABLE_NOTES, null, values);

        Cursor cursor = database.query(DatabaseHelper.TABLE_NOTES, allColumns,
                DatabaseHelper.COLUMN_ID + " = " + insertId, null, null, null, null);
        cursor.moveToFirst();
        Notes newNotes = cursorToNote(cursor);
        cursor.close();
        return newNotes;
    }

    public List<Notes> getAllNotes(){
        List<Notes> notesList = new ArrayList<Notes>();

        Cursor cursor = database.query(DatabaseHelper.TABLE_NOTES, allColumns,
                null, null, null, null, null);

        cursor.moveToFirst();
        while (!cursor.isAfterLast()){
            Notes note = cursorToNote(cursor);
            notesList.add(note);
            cursor.moveToNext();
        }
        cursor.close();
        return notesList;
    }

    private Notes cursorToNote(Cursor cursor){
        Notes note = new Notes();
        note.setId(cursor.getLong(0));
        note.setTitle(cursor.getString(1));
        note.setNote(cursor.getString(2));
        note.setDate(cursor.getString(3));
        return note;
    }
}

AddNote.java

package com.cidecode.xnotes;

import android.database.sqlite.SQLiteDatabase;
import android.support.v7.app.ActionBarActivity;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.view.Window;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

import java.sql.SQLException;


public class AddNote extends ActionBarActivity {

    private EditText title,note;
    private String s_title, s_note, s_date;
    Button b_save;
    private SQLiteDatabase database;
    private DatabaseHelper dbHelper;
    private NotesDataSource datasource;

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

        dbHelper = new DatabaseHelper(this);
        datasource = new NotesDataSource(this);
        try {
            datasource.open();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        title = (EditText)findViewById(R.id.id_title);
        note = (EditText)findViewById(R.id.id_write_note_here);

        s_title = title.getText().toString();
        s_note = note.getText().toString();
        s_date = "11.11.1111";

        b_save = (Button)findViewById(R.id.id_save);
        b_save.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                datasource.createNote(s_title, s_note, s_date);
                Toast.makeText(getApplicationContext(), "Note is saved.", Toast.LENGTH_LONG).show();

            }
        });

    }

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

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

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

        return super.onOptionsItemSelected(item);
    }
}

activity_add_note.xml

<LinearLayout xmlns:android="http://ift.tt/nIICcg"
    xmlns:tools="http://ift.tt/LrGmb4"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    tools:context=".MainActivity"
    android:background="#EBD28F">

    <TextView
        android:id="@+id/id_add_new_note"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="@string/s_add_new_note"
        android:gravity="center"
        android:textSize="30sp"
        android:layout_marginBottom="10dp"/>

    <EditText
        android:id="@+id/id_title"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="@string/s_title"
        android:layout_marginLeft="5dp"
        android:layout_marginRight="5dp"
        android:layout_marginBottom="5dp"/>

    <EditText
        android:id="@+id/id_write_note_here"
        android:layout_width="match_parent"
        android:layout_height="fill_parent"
        android:layout_weight="1"
        android:layout_marginLeft="5dp"
        android:layout_marginRight="5dp"
        android:hint="@string/s_write_note_here"
        android:gravity="top" />

    <Button
        android:id="@+id/id_save"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_gravity="center_horizontal"
        android:layout_marginTop="5dp"
        android:layout_marginBottom="5dp"
        android:text="@string/s_save"
        android:textColor="#FFFFFF"
        android:background="#5E553A"/>

</LinearLayout>

Logcat

06-30 08:49:12.435  20736-20736/? E/SQLiteLog﹕ (1) table notes has no column named date
06-30 08:49:12.455  20736-20736/? E/SQLiteDatabase﹕ Error inserting title= date=11.11.1111 note=
    android.database.sqlite.SQLiteException: table notes has no column named date (code 1): , while compiling: INSERT INTO notes(title,date,note) VALUES (?,?,?)
            at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
            at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1013)
            at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:624)
            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.insertWithOnConflict(SQLiteDatabase.java:1467)
            at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1339)
            at com.cidecode.xnotes.NotesDataSource.createNote(NotesDataSource.java:38)
            at com.cidecode.xnotes.AddNote$1.onClick(AddNote.java:50)
            at android.view.View.performClick(View.java:4232)
            at android.view.View$PerformClick.run(View.java:17298)
            at android.os.Handler.handleCallback(Handler.java:615)
            at android.os.Handler.dispatchMessage(Handler.java:92)
            at android.os.Looper.loop(Looper.java:137)
            at android.app.ActivityThread.main(ActivityThread.java:4921)
            at java.lang.reflect.Method.invokeNative(Native Method)
            at java.lang.reflect.Method.invoke(Method.java:511)
            at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1027)
            at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:794)
            at dalvik.system.NativeStart.main(Native Method)
06-30 08:49:12.455  20736-20736/? E/SQLiteLog﹕ (1) no such column: date
06-30 08:49:12.485  20736-20736/? E/AndroidRuntime﹕ FATAL EXCEPTION: main
    android.database.sqlite.SQLiteException: no such column: date (code 1): , while compiling: SELECT id, title, note, date FROM notes WHERE id = -1
            at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
            at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1013)
            at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:624)
            at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
            at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
            at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
            at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
            at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314)
            at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1161)
            at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1032)
            at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1200)
            at com.cidecode.xnotes.NotesDataSource.createNote(NotesDataSource.java:40)
            at com.cidecode.xnotes.AddNote$1.onClick(AddNote.java:50)
            at android.view.View.performClick(View.java:4232)
            at android.view.View$PerformClick.run(View.java:17298)
            at android.os.Handler.handleCallback(Handler.java:615)
            at android.os.Handler.dispatchMessage(Handler.java:92)
            at android.os.Looper.loop(Looper.java:137)
            at android.app.ActivityThread.main(ActivityThread.java:4921)
            at java.lang.reflect.Method.invokeNative(Native Method)
            at java.lang.reflect.Method.invoke(Method.java:511)
            at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1027)
            at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:794)
            at dalvik.system.NativeStart.main(Native Method)

It tells me that something is wrong with creating database or table. Can someone help me, please?

SQL Query in Android application adds unwanted extra Fileds

I have developed an application using android studio. In my code I have written an SQL Query in the developed android application. The SQL query seems to be correct but for Some reason, It adds an extra Filed to the Column name like below. As you can see asset_description table has the word "Text". As this should not be the case.

I do not know why this is happening

The Column names are as shown below

 //Columns in the Asset Table
    public static final String ASSET_ID = "Assetid";
    public static final String ASSET_Name="asset_name";
    public static final String ASSET_Category ="category";
    public static final String ASSET_PurchaseDate = "Purchase_date";
    public static final String Asset_Cost ="Asset_cost";
    public static final String ASSET_STATUS ="asset_status";
    public static final String ASSET_DESCRIPTION ="assets_description";
    public static final String ASSET_Warrenty ="warrenty";

This is the table creation section

private static final String CREATE_TABLE_ASSET ="CREATE TABLE" + TABLE_ASSET + "("
            + ASSET_ID + " INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
            + ASSET_Name + " TEXT NOT NULL,"
            + ASSET_Category +" TEXT NOT NULL,"
            + CUSTODIAN_ID + " TEXT NOT NULL,"
            + Asset_Cost + " DOUBLE NOT NULL,"
            + ASSET_PurchaseDate + " DATE NULL,"
            + ASSET_STATUS +" TEXT NOT NULL,"
            +  ASSET_DESCRIPTION + "Text NOT NULL,"
            + ASSET_Warrenty +" DATE NOT NULL"+ ");";

Select with pattern from table in sqlite

Hi i have table with column contain this values

patern_number
0936
09154
123456

and i have number which can be anything
what i want is a select which return 1 when input number start with one of pattern else return 0
example
input number -------result
093628987 ----- ----- 1
0915 --------------------0
0222---------------------0
091546666-------------1
so can anyone help me?

Hi guys!. I have that error cursor index out of bounds exception [duplicate]

This question already has an answer here:

• Cursor Index Out of Bounds Exception 2 answers

My cursor uses here! I have tried many times :D. I am beginner

 public NotePad getNote(int id){
       SQLiteDatabase db = this.getReadableDatabase();
       Cursor cursor = db.query(TABLE_NAME,new String[]{TITLE,TEXT,DATE,STATE},
               NOTE_ID +"=?", new String[]{String.valueOf(id)},null,null,null,null);
       if(cursor!=null)
           cursor.moveToFirst();
       NotePad notePad = new NotePad(cursor.getString(1), cursor.getString(2),
               cursor.getString(3), Integer.parseInt(cursor.getString(4)));
       cursor.close();
       db.close();
       return notePad;
    }

error in line

NotePad notePad = new NotePad(cursor.getString(1), cursor.getString(2),
               cursor.getString(3), Integer.parseInt(cursor.getString(4))); 

Set default search engine Chrome using meta table Not Working

There may be many variations to this question..but I am still unable to figure out how Chrome backend files work.

I am trying to set default search engine for Chrome, Firefox and IE using VC++ executable. I have been able to do so for Firefox and IE, but stuck in Chrome. I integrated SQLite with vc++ project and accessed Chrome's "Web Data" file to add my search engine to defaults list.

Now based on the link : Setting the Default Search Provider on Chrome via a script, I was able to add a search engine to defaults list of Chrome by adding entry to Web Data - keywords table.

Now to set it default, I added entry for key (if not present) : "Default Search Provider ID" to Web Data database and meta table.

        string query = "INSERT INTO meta (key, value) values ('Default Search Provider ID', " + std::to_string(engineId)+ ");";
        const char *sqlInsert = query.c_str();

        rc = sqlite3_exec(db, sqlInsert, NULL, NULL, &error);
        if (rc)
        {
            cerr << "Error executing SQLite3 statement: " << sqlite3_errmsg(db) << endl << endl;
            sqlite3_free(error);
            sqlite3_close(db);
        }

But this is not reflecting on Chrome. Also change in preferences file sets back the default search engine on reload of Chrome.

SQLite Android No such Column Error

I'm trying to use SQLite in android but for some reason I'm getting the no such column error. I think the error may be syntax but I can't seem to locate it.

This is my code:

         @Override
    public void onCreate(SQLiteDatabase database)
    {
        database.execSQL("create table " +
                TABLE_NAME +
                " (" +
                TABLE_ROW_ID + " integer primary key autoincrement not null," +
                TABLE_ROW_ONE + " text," +
                TABLE_ROW_TWO + " text," +
                TABLE_ROW_THREE +  " text," +
                TABLE_ROW_FOUR + " text" +
                ");");
    }

I had tried some formatting tricks like adding a space to the fourth text, or subtracting one but none of them seemed to have worked yet.

Beneath is the logcat reading.

 android.database.sqlite.SQLiteException: table database_table has no column named table_row_three (code 1): , while compiling: INSERT INTO database_table(table_row_three,table_row_two,table_row_four,table_row_one) VALUES (?,?,?,?)
        at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
        at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:892)
        at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:503)
        at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:726)
        at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
        at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
        at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1568)
        at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1440)

Thank you!

Fixing async error in Node + Sqllite3

I have the following code :

// reference the http module so we can create a webserver
var http = require("http");
var sqlite3 = require('sqlite3').verbose();
var db = new sqlite3.Database('movies.db');

// create a server
http.createServer(function(req, res) 
{
    var catloc = req.url.indexOf("category");
    if (catloc > 0)
    {
        // part 1
        var page = csm1;
        var url_parts = url.parse(req.url, true);
        var query = url_parts.query;
        var category = query.type;

        // part 2
        db.each("SELECT * FROM MOVIES WHERE LOWER(CATEGORY1)='"+category+"'", function(err, row)
        {
            if (err) throw err;
            page += row.html;
        });

        // part 3
        page += csm3;
        res.writeHead(200, {'Content-Type': 'text/html'});
        res.end(page);
    }
}).listen(process.env.PORT, process.env.IP);

part1 should be executed, followed by part2, then part3. But part1+part3 is executed before part2 due to async.
I would like help on how I could set up a callback to fix this.

Any help is appreciated!