Create sqlite table is not work when not declare in main method.

I have a method that make a connection to SQLITE and work with database by conditions (insert data or create table in my database.)

I have a big problem with my method. I tested it after not working in another project and it was worked. the difference between this program and that, is just main method. This program is in single method out of Main method and the other(worked) is in Main method, But I need it in single out of main method to call it.

When I call it in my code, at first open database correctly but can not create tables. Mean that query does not show error but not execute. Because when I typed select * from Personel .

the SQLite said No such table

this is my method :

void SQL(string cmnd)
{

    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");//not dbConnection
    QString path = "DBAbsent";
    db.setDatabaseName(path);
    QSqlQuery q(db);

    if(!db.open())
    {

        printf("Error to open database");
    }
    else
    {

        if (cmnd=="createTable")
        {
            q.prepare("create table IF NOT EXISTS Personel (P_id int primary key, "
                                 "CardID varchar(50), "
                               "name varchar(50));");

            if(!q.exec())
                qDebug()<<strerror(errno);

            q.prepare("create table IF NOT EXISTS Absent "
                                 "(P_id int , "
                                 "DateTime varchar(50), "
                                 "key1 int,key2 int,key3 int,key4 int);");
            if(!q.exec())
                qDebug()<<strerror(errno);

             db.close();
             return;

        }
        else if (cmnd=="Register")
        {
            string card=ReadFromCard();
            printf("\nCard Accepted : %s\nEnter Your name: ",card.c_str());
            string name;
            int id;
            //string dt=QDateTime::currentDateTime().toString("yyyy-MM-dd HH:mm:ss");
            cin>>name;
            printf("\n Name Accepted.\nEnter Your Personel ID: ");
            cin>>id;
            q.prepare( "SELECT count(P_id) FROM Personel WHERE P_id =?;" );
            q.bindValue(0,id);

            if( !q.exec() )
            {
              printf("\nThe error occured from P_ID database");
              qDebug()<<strerror(errno);
              //ShowMainMenu();
              //return;
            }

            // Note: if you don't return in case of an error, put this into the else{} part
            while( q.next() )
            {
                if (q.value(0).toInt()>0)
                {
                    printf("\nThis personel ID was repeated\n");
                    break;
                }
                else
                {
                    q.prepare("SELECT CardID FROM Personel WHERE CardID =?;");
                    q.bindValue(0,card.c_str());
                    q.exec();

                    while(q.next())
                    {
                        printf("\nThis card is not valid : it was repeated\n");
                        break;
                    }
                }
            }

            q.prepare("insert into Personel value(?,?,?);");
            q.bindValue(0,id);
            q.bindValue(1,card.c_str());
            q.bindValue(2,name.c_str());
            q.exec();
            printf("\nInsert Personel complete\n");
            db.close();
            return;

        }
        else
        {
            string card;
            card=cmnd;
            QString dt=QDateTime::currentDateTime().toString("yyyy-MM-dd HH:mm:ss");

            q.prepare("SELECT P_id FROM Personel WHERE CardID =?;");
            q.bindValue(0,card.c_str());

            if( !q.exec() )
            {
              printf("\nThe error occured from database418\n");
              ShowMainMenu();

            }
            string result=("\n    CARD NOT VALID =>repeated     %s\n");
            // Note: if you don't return in case of an error, put this into the else{} part
            while( q.next() )
            {
                int P_id = q.value(0).toInt();
                q.prepare("insert into Absent(P_id,DateTime,key1,key2,key3,key4) value(?,?,1,0,0,0);");
                q.bindValue(0,P_id);
                q.bindValue(1,dt);
                q.exec();
                result=("\n********WELL COME **********%s\n");
            }
            printf("%s",result);
            db.close();
            return;
        }

    }
}  

And this is my output :

    0 - Press 0 to Back
    1 - Press 1 to Register the card
    1
    //the below lines when I want to select something from both tables , occurred
    No such file or directory
    No such file or directory  
    //the below line is when the if(cmnd="Register") {..} block is running , occurred.
    QSqlDatabasePrivate::addDatabase: duplicate connection name 'qt_sql_default_connection', old connection removed.

Python sqlite Select With and Limit doesn't work for some reason

Keeping it simple. I am trying to execute this:

c.execute("SELECT id FROM users LIMIT 10 WHERE email IS NULL")

it gives me the following error:

c.execute("SELECT id FROM users LIMIT 10 WHERE email IS NULL") sqlite3.OperationalError: near "WHERE": syntax error

That line, however, works perfectly if I remove the limit clause like this:

c.execute("SELECT id FROM users WHERE email IS NULL")

What's wrong with LIMIT 10? I tried enclosing it in [ ] and it still didn't work.

In android Sqlite i want to search data by current week,month year and date range

This is my table.

CREATE TABLE Income (id INTEGER PRIMARY KEY AUTOINCREMENT, type TEXT, amount DOUBLE, sdate DATE, way TEXT,desc TEXT)

And after that i want to search date sort by sdate column

this is my queary but no out put from this quary.

SELECT * from Income where sdate=DATE('now', 'weekday 0', '-7 days')

Combine Query of Android database

I have Join query that find data from three table so how can I know the data is founded from which table

select text from table1 where title ='Vishal'  union all select text from table2 where title ='Vishal'  union all select text from table3 where title ='Vishal'

This query give me my desired output but I want to know
title match from which table and I want to get the Table name

i have to display contents in sqlite database on listview but it it not displaying

** Here i want to display the contents** package com.example.datewithme; import java.util.ArrayList; import android.app.Activity; import android.content.Intent; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.os.Bundle; import android.view.Menu; import android.view.MenuItem; import android.view.View; import android.widget.AdapterView; import android.widget.ArrayAdapter; import android.widget.ListView; import android.widget.TextView; import android.widget.Toast; import android.widget.AdapterView.OnItemClickListener;

public class Welcome extends Activity{
Intent i=getIntent();
TextView tv;
ListView lv;
SQLiteDatabase db=null;
int k=0;
private ArrayList<String> Questions = new ArrayList<String>();
private DBHelper mHelper;
private SQLiteDatabase dataBase;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main1);
        lv = (ListView) findViewById(R.id.listView1);

        /*String[] values = new String[100] ;
        DBHelper db = new DBHelper(getApplicationContext());
        Cursor cs= db.getQuestions();
        k=0;

        while(cs.moveToNext())
        {
            String q = cs.getString(0);
            values[k++]=q;

        }
        try{
        ArrayAdapter<String> adapter = new ArrayAdapter<String>(this,
                  android.R.layout.simple_list_item_1, android.R.id.text1, values);


                // Assign adapter to ListView
                lv.setAdapter(adapter); 

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

                       // ListView Clicked item index
                       int itemPosition     = position;

                       // ListView Clicked item value
                       String  itemValue    = (String) lv.getItemAtPosition(position);

                        // Show Alert 
                        Toast.makeText(getApplicationContext(),
                          "Position :"+itemPosition+"  ListItem : " +itemValue , Toast.LENGTH_LONG)
                          .show();

                      }


                    }); 
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
        }*/

        tv=(TextView)findViewById(R.id.user);
        //db=openOrCreateDatabase("mydb", MODE_PRIVATE, null);
        String newString;
        if (savedInstanceState == null) {
            Bundle extras = getIntent().getExtras();
            if(extras == null) {
                newString= null;
            } else {
                newString= extras.getString("name");
            }
        } else {
            newString= (String) savedInstanceState.getSerializable("name");
        }
        tv.setText("Hello"+newString);

    }
         @Override
            public boolean onCreateOptionsMenu(Menu menu) {
                getMenuInflater().inflate(R.menu.activity_main, menu);
                return true;
            }
             public boolean onOptionsItemSelected(MenuItem item) {
                switch (item.getItemId()) {
                case R.id.item1: 
                     startActivity(new Intent(Welcome.this, TimeLine.class));
                    return true;
                case R.id.item2: 
                   startActivity(new Intent(Welcome.this, About.class));
                    return true;
                case R.id.item3: 
                    Toast.makeText(this, "Option3", Toast.LENGTH_SHORT).show(); 
                    return true;            
                case R.id.item4: 
                    this.finish();
                    Intent intent = new Intent(Intent.ACTION_MAIN);
                    intent.addCategory(Intent.CATEGORY_HOME);
                    intent.setFlags(Intent.FLAG_ACTIVITY_NEW_TASK);
                    startActivity(intent);
                    return true;

                default:
                    return super.onOptionsItemSelected(item);
                }

            }
             private void displayData() {
                    dataBase = mHelper.getWritableDatabase();
                    Cursor mCursor = dataBase.rawQuery("SELECT * FROM "
                            + DBHelper.TABLE_NAME, null);

                    Questions.clear();
                    if (mCursor.moveToFirst()) {
                        do {
                            Questions.add(mCursor.getString(mCursor.getColumnIndex(DBHelper.KEY_NAME)));


                        } while (mCursor.moveToNext());
                    }
                    DisplayAdapter disadpt = new DisplayAdapter(Welcome.this,Questions);
                    lv.setAdapter(disadpt);
                    mCursor.close();
                }
        }

> Blockquote
Here is my display adapter

package com.example.datewithme;

import java.util.ArrayList;

import android.content.Context;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.BaseAdapter;
import android.widget.TextView;
/**
 * adapter to populate listview with data
 * @author ketan(Visit my <a
 *         href="http://ift.tt/LC4ev7">blog</a>)
 */
public class DisplayAdapter extends BaseAdapter {
    private Context mContext;
    private ArrayList<String> questions;


    public DisplayAdapter(Context c, ArrayList<String> questions) { 
        this.mContext = c;

        this.questions = questions;
    }

    public int getCount() {
        // TODO Auto-generated method stub
        return questions.size();
    }

    public Object getItem(int position) {
        // TODO Auto-generated method stub
        return null;
    }

    public long getItemId(int position) {
        // TODO Auto-generated method stub
        return 0;
    }

    public View getView(int pos, View child, ViewGroup parent) {
        Holder mHolder;
        LayoutInflater layoutInflater;
        if (child == null) {
            layoutInflater = (LayoutInflater) mContext.getSystemService(Context.LAYOUT_INFLATER_SERVICE);
            child = layoutInflater.inflate(R.layout.list, null);
            mHolder = new Holder();
            mHolder.txt_id = (TextView) child.findViewById(R.id.tv_que);
            child.setTag(mHolder);
        } else {
            mHolder = (Holder) child.getTag();
        }
        mHolder.txt_id.setText(questions.get(pos));

        return child;
    }

    public class Holder {
        TextView txt_id;
    }

}

sqlite cordova javascript crashes

So I have a sqlite database which opens and executes well, however when I make sqlite functions at startup, the app freezes.

  var init = (function() {
$scope.getAll();
})();

The problem is var init = function()

$scope.Route = [];
$scope.getAll = function() {
db = $cordovaSQLite.openDB("transportation.db");
    var query = "SELECT agency_id, agency_phone FROM agency";
    $cordovaSQLite.execute(db, query, []).then(function(res) {
        if(res.rows.length > 0) {
            for(var i = 0; i < res.rows.length; i++) {
                $scope.logerror = "Worked" + res.rows.item(i).agency_phone;
                $scope.Route.push({id: res.rows.item(i).agency_id, agency_phone: res.rows.item(i).agency_phone})
                $scope.stops = res.rows.item(i).agency_phone;
                console.log("SELECTED -> " + res.rows.item(i).agency_phone);
            }
        } else {
            $scope.logerror = "No results found";
            console.log("No results found");
        }
    }, function (err) {
        $scope.logerror = "ERROR";
        console.error(err);
    });

};

How can I solve this problem?

Read DataSet from MySQL and update it at a SqLite database

I found this related Question, but it doesn't work for me that way. My implementation looks like follows:

 public void SyncWithMySQL(List<Tuple<String, String>> queriesTableName, DataSet mysqlDataSet, params KeyValuePair<string, object>[] cmdParams)
    {
        foreach (Tuple<String, String> queryTableName in queriesTableName)
        {
            using (var cmd = this._connection.CreateCommand()) { 
                cmd.CommandText = queryTableName.Item2;

                foreach (var p in cmdParams)
                {
                    cmd.Parameters.AddWithValue(p.Key, p.Value);
                }

                SQLiteDataAdapter adap = new SQLiteDataAdapter(cmd);
                using (adap.InsertCommand = new SQLiteCommandBuilder(adap).GetInsertCommand())
                {
                    System.Diagnostics.Trace.TraceInformation(adap.InsertCommand.CommandText);
                    int rows = adap.Update(mysqlDataSet.Tables[queryTableName.Item1]);
                    System.Diagnostics.Trace.TraceInformation("Table " + queryTableName.Item1 + " update: " + rows);
                }
            }
        }
    }

where mysqlDataSet is the dataset i want to store in the sqlite database, queriesTableName is list of tuples where the tuple contains of th name of the table and the query for each table.

The strange thing is, i don't get an error and the update method of the DataAdapter returns values greater 0. So actually they should be data in the database, but unfortunately its not the case

The DataSet from the MySQL database is filled with this command:

MySqlDataAdapter adap = new MySqlDataAdapter(cmd);
adap.AcceptChangesDuringFill = false;
adap.Fill(result, queryTableName.Item1);

Is there something wrong with my code? Do i miss something? Thanks for your help!

Format for including http link in SQL table

I want to include hyperlinks to external sites in an SQL table (so a click takes the user to the external site). Whatever I try the result is my link gets prefixed with my own site name.

What is the format I should be using? Apologies if this is so obvious!

XCode Project Using FMDB, SQLite Table Not Found

I'm new to XCode and have been trying to create what I'd hoped would be a simple app to scan a barcode and check the value it grabs against a database to pull up the info associated with that code.

I'm trying to use FMDB with Swift to do this, but I can't seem to be able to get the database to copy over to the app correctly and am now getting the following error:

DB Error: 1 "no such table: building"

My code attempting to open and query the database for the barcode description is:

import UIKit

let documents = try! NSFileManager.defaultManager().URLForDirectory(.DocumentDirectory, inDomain: .UserDomainMask, appropriateForURL: nil, create: true)

let fileURL = documents.URLByAppendingPathComponent("barcodeDatabase.db")

let database = FMDatabase(path: fileURL.path)

class ViewController: UIViewController {

    @IBOutlet weak var scannedCode: UILabel!

    override func viewDidLoad() {
        super.viewDidLoad()
        // Do any additional setup after loading the view, typically from a nib.

    }

    override func didReceiveMemoryWarning() {
        super.didReceiveMemoryWarning()
        // Dispose of any resources that can be recreated.
    }

    @IBAction func codeSet(sender: UIButton) {
        if !database.open() {
            print("Unable to open database")
            return
        }
        do {
            let temp = try database.executeQuery("select * from building where location_id = ?", values: [myVars.codeData])
            while temp.next() {
                scannedCode.text = temp.stringForColumn("location_desc") + "\r\n" + myVars.codeData
            }
        } catch let error as NSError {
                print("failed: \(error.localizedDescription)")
        }

        database.close()
    }

}

I have the database file "barcodeDatabase.db" added to the project, and it's included in the project target membership and the copy bundle resources area.

I was able to make it work in the simulator by commenting out

let fileURL = documents.URLByAppendingPathComponent("barcodeDatabase.db")

and changing fileURL.path in the next line to the path in my mac's documents, but on the actual iPad it said it was unable to open the database.

How to resolve an AccessViolationException?

Overview: I'm calling an Init method from my DatabaseHelper class to init the SQLLite database attached to the solution. But when I call Init() I get an AccessViolationException.

Question: How can I debug the AccessViolationException further? There isn't an inner exception in the error detail.

Error Log:(no inner exception on the error)

 System.AccessViolationException was unhandled by user code
      HResult=-2147467261
      Message=Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
      Source=Parking Tag Picker WRT
      StackTrace:
           at Parking_Tag_Picker_WRT.ViewModel.TagRequestViewModel.<GetAllZoneInfoAsync>d__0.MoveNext()
           at System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)
           at Parking_Tag_Picker_WRT.ViewModel.TagRequestViewModel.GetAllZoneInfoAsync()
           at Parking_Tag_Picker_WRT.TagRequestPage.OnNavigatedTo(NavigationEventArgs e)
           at Parking_Tag_Picker_WRT.MainPage.Parking_Tag_Picker_WRT.Interfaces.INavigationCallback.NavigateTo(String ItemID)
           at Parking_Tag_Picker_WRT.ViewModel.MainViewModel.set_SelectedCouncilName(CouncilName value)

Code: (During debugging it seems the Init method never gets called as debugger doesn't step into the helper class.

Calling method in VieModel -

    public async Task GetAllZoneInfoAsync()
    {
        string dbPathValue;
        int CouncilId = Int32.Parse(SelectedCouncilId);
        dbPathValue = DBPathDictonary[CouncilId];
        //Exception thrown here on the Init() call 
        IsConnected = await _dbHelper.Init(dbPathValue);

    }

DBHelper Class -

namespace Parking_Tag_Picker_WRT.Helpers
{
    public class DatabaseHelper
    {

        private SQLiteConnection dbConn;


        /// <summary>
        /// Load SQL_LiteTable from Solution   
        /// </summary>
        /// <param name="DB_PATH"></param>
        /// <returns></returns>
        public async Task<bool> Init(string dbPath)
        {


            bool isDatabaseExisting = false;

            try
            {
                StorageFile storageFile = await ApplicationData.Current.LocalFolder.GetFileAsync(dbPath);
                isDatabaseExisting = true;
            }
            catch
            {
                isDatabaseExisting = false;
            }

            if (!isDatabaseExisting)
            {
                StorageFile databaseFile = await Package.Current.InstalledLocation.GetFileAsync(dbPath);
                await databaseFile.CopyAsync(ApplicationData.Current.LocalFolder);
            }

            return true;          
        }   

    }
}

SQLite not printing

I'm trying to learn SQL through "Learn SQL the Hard Way" and I am having difficulty with the the command prompt. In particular, I am having trouble with the 3rd exercise.

I am able to create a database from ex2.sql by calling

sqlite3 ex3.db < ex2.sql

This should build a database with the schema:

CREATE TABLE person (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    age INTEGER
);

CREATE TABLE pet (
    id INTEGER PRIMARY KEY,
    name TEXT,
    breed TEXT,
    age INTEGER,
    dead INTEGER
);

CREATE TABLE person_pet (
    person_id INTEGER,
    pet_id INTEGER
);

Calling .schema on sqlite3 ex3.db prints out exactly this.

Then I use ex3.sql which has the contents:

INSERT INTO person (id, first_name, last_name, age)
    VALUES (0, "Zed", "Shaw", 37);

INSERT INTO pet (id, name, breed, age, dead)
    VALUES (0, "Fluffy", "Unicorn", 1000, 0);

INSERT INTO pet VALUES (1, "Gigantor", "Robot", 1, 1);

I insert to ex3.db by saying:

sqlite3 -echo ex3.db < ex3.sql

According to the book, this should insert the values from ex3.sql into ex3.db while printing out what it is doing. However, when I type the above into cmd it prints nothing. And when I call

sqlite3 ex3.db
select * from person;

it still shows nothing. My guess is that either the database file is not updating, something with my install went wrong, or I'm messing something else up here. Appreciate any help that can be given.

Need help to build SQL

I am not able to figure out the SQL. Below is my table structure. Now what I want is to take all the thread list with latest message for a particular user. Let say here for userId = 6.

Totally offline app-SQlite-show POIS

Can i use osmdroid to save my offline maps in a sqlite db and after show offline some POIS and get distances beetween my location and the location of the POIS? From a research i figure out that osmdroid not support offline searching for POIS. Is that true? Its better to search for the lip Mapforge?

SQLite cannot execute from F# FSI

None of the SQLite .NET packages work in FSI, even when referencing a module in a fs project. I've tried System.Data.SQLite, SQLitePCL.raw, and Microsoft.Data.Sqlite.

From System.Data.SQLite docs:

If the executable that starts the process consists entirely of managed code, it will run with the native processor architecture of the machine, which will be x64 on an x64 machine. Later on, this will cause assemblies containing any native code compiled for x86 (e.g. the "System.Data.SQLite.dll" mixed-mode assembly, the "SQLite.Interop.dll" native interop assembly, or the "sqlite3.dll" native library) to fail to load, typically resulting in a BadImageFormatException being thrown.

The error thrown typically looks like

System.DllNotFoundException: Unable to load DLL 'sqlite3': The specified module could not be found. (Exception from HRESULT: 0x8007007E)
   at Microsoft.Data.Sqlite.Interop.NativeMethods.sqlite3_open_v2(IntPtr filename, Sqlite3Handle& ppDb, Int32 flags, IntPtr vfs)
   at Microsoft.Data.Sqlite.Interop.NativeMethods.sqlite3_open_v2(String filename, Sqlite3Handle& ppDb, Int32 flags, String vfs)
   at Microsoft.Data.Sqlite.SqliteConnection.Open()
   at <StartupCode$FSI_0009>.$FSI_0009.main@() in E:\BitSync\NonGitProjects\SQLiteSandbox\Microsoft.DataSandbox\Scripts\Script1.fsx:line 7

Demo gist

Has anyone found a solution to this? SQLite works just fine in compiled F# programs and in debug mode.

SQLiteConnection not accepting sting as argument

I am trying to connect to a SQLite .db file by using some examples that I found on the internet:

var m_connection = new SQLiteConnection("Data Source=Tabs.db;version=3;");

However, Visual Studio 2015 won't accept that because it says it doensn't accept just one argument.

SQLiteConnection.SQLiteConnection(SQLite.Net.Interop.ISQLitePlatform, string, bool, SQLite.Net.IBlobSerializer, System.Collections.Generic.IDictionary, System.Collections.Generic.IDictionary, SQLite.Net.IContractResolver)

is what shows up in the object browser. All of the examples seem straightforward, what am I encountering? I downloaded SQLite.Net-PCL 3.1.1

How to make list view items clickable? Android SQL Lite DB

i will attempt to keep this short and concise. I am currently developing an android application, and am stuck on a particular element.

The below is an example code i have been reading through, from my understanding it is taking a list of values inserting them in to the db / table, then cycling through them with the cursor and displaying with the list view. On running the application all items are displayed in a list as expected.

I would like it so that when an item in the list is clicked a new page is opened via intent, or a different set of database values is displayed on the current page.

How could this be achieved

Thank you!

public class SQLiteCrudExample  extends ListActivity {
//DB name
private final String dbName = "Android";
//Table name
private final String tableName = "Versions";
//String array has list Android versions which will be populated in the list
private final String[] versionNames= new String[]{"Cupcake", "Donut", "Eclair", "Froyo", "Gingerbread", "Honeycomb", "Ice Cream Sandwich", "Jelly Bean", "Kitkat"};
@Override
public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    ArrayList<String> results = new ArrayList<String>();
    //Declare SQLiteDatabase object
    SQLiteDatabase sampleDB = null;

    try {
        //Instantiate sampleDB object
        sampleDB =  this.openOrCreateDatabase(dbName, MODE_PRIVATE, null);
        //Create table using execSQL
        sampleDB.execSQL("CREATE TABLE IF NOT EXISTS " + tableName +    " (versionname VARCHAR);");
        //Insert Android versions into table created
        for(String ver: versionNames){
            sampleDB.execSQL("INSERT INTO " + tableName + " Values ('"+ver+"');");
        }

        //Create Cursor object to read versions from the table
        Cursor c = sampleDB.rawQuery("SELECT versionname FROM " + tableName, null);
        //If Cursor is valid
        if (c != null ) {
            //Move cursor to first row
            if  (c.moveToFirst()) {
                do {
                    //Get version from Cursor
                    String firstName = c.getString(c.getColumnIndex("versionname"));
                    //Add the version to Arraylist 'results'
                    results.add(firstName);
                }while (c.moveToNext()); //Move to next row
            } 
        }

        //Set the ararylist to Android UI List
        this.setListAdapter(new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1,results));

    } catch (SQLiteException se ) {
        Toast.makeText(getApplicationContext(), "Couldn't create or open the database", Toast.LENGTH_LONG).show();
    } finally {
        if (sampleDB != null) {
            sampleDB.execSQL("DELETE FROM " + tableName);
            sampleDB.close();
        }
    }
}

}

Android Rating Bar shows only full stars, not half stars too

I'm using rating bars in my Android application. But it only shows full stars, when I also want half stars.

I inserted a SQLite row with a name and the ratingValue, in there the ratingValue stands as "3.5" but my application turns it into "4". Even if my StepSize is "0.5", even the default value is changed to "3"

Here's some code

ListItem_layout

<TableRow>    
    <RatingBar
        android:layout_height="wrap_content"
        android:layout_width="wrap_content"
        android:id="@+id/sportsRatingBar"
        android:gravity="center_horizontal"
        android:focusableInTouchMode="false"
        android:isIndicator="true"
        android:clickable="false"
        android:focusable="false"
        android:layout_column="1"
        android:rating="2.5"
        android:stepSize="0.5" />

ListItem

if(ratingBar!=null) {
                ratingBar.setRating(p.getRating());
                ratingBar.setTag(p.getId());
            }

Reusable class

public float getRating() { return Rating; }

SQLite Table Column for rating

 COLUMN_RATING + " real not null);"

And last, getting from database

c.getFloat(COLUMN_RATING_COL));

If anyone could help me with this?

cant delete from getContentResolver

i cant delete a conversation from getContentResolver , i dont know in which part am getting mistakes , as i also searched about these but cant help my self and thanks alot in advance here is the code

    public static boolean deleteSmsofContact(Context context, String number,
        boolean deleteLocked)
{
    int result;

    if (deleteLocked) {

       //changes values

        String[] selectionArgs=new String[]{number};
        String selection= ""+"address=?";

        //
        result = context.getContentResolver().delete(Uri.parse("content://sms/"),selection,selectionArgs);
     // Log.d("UF","WOW "+result+" " +number);
    } else {
        result = context.getContentResolver().delete(Constants.URI_SMS,
                "address=? AND locked=?", new String[] { number, "1" });
    }

    if (result > 0) {
        return true;
    }

    return false;
}

here is the method from which i am calling

  boolean result = Utils.deleteSmsofContact(InboxActivity.this, sms.getNumber(), true);
                                            if (result) {
                                                dataList.remove(threadPosition);
                                                iAdapter.notifyDataSetChanged();
                                                Toast.makeText(InboxActivity.this,"Removed",Toast.LENGTH_LONG).show();
                                            }else
                                            {
                                                Toast.makeText(InboxActivity.this,"cant removed",Toast.LENGTH_LONG).show();
                                            }

specify a data provider with xamarin ios c# for microlite orm

I am trying to use Microlite orm to connect to a sqlite db in my xamarin ios project. Microlite needs a sessionfactory to be defined which I am trying to do like this

_sessionFactory =
                    Configure.Fluently()
                        .ForSQLiteConnection("localSqlLite", connString, "System.Data.SQLite")
                        .CreateSessionFactory();

However I get the error that "Unable to find the requested .Net Framework Data Provider. It may not be installed." MicroLite needs the sqlite data provider be registered which normally you would do in web / app.config like this

<system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SQLite"/>
      <add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".Net Framework Data   Provider for SQLite"
      type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
    </DbProviderFactories>
  </system.data>

However because it is an ios project with xamarin I do not have any type of config. Has anyone found a way to work around this? I can't find a way to define a data provider at runtime if that would even work.

How can I insert a code as a text for a column into SQLite using SQLite3 command line

How can I insert a code as a text for a column into SQLite using SQLite3 command line.

Here is the table structure:

CREATE TABLE `content` (
`id` int(11) DEFAULT NULL,
`id2` int(11) DEFAULT NULL,
`id3` int(11) DEFAULT NULL,
`value` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I want to insert:

INSERT INTO table(`value`) VALUES(”function() {
 var obj='xxxx'
 retun results;”);

The Output I want when I perform a SELECT statement is:

value

------------------  
function(){
var obj='xxxx'
retun results;

But I am not able to succesfully insert the code inside the insert statement as a value to a column.It gives me error 1064.

Can some one help me how to insert a code as a value to a column preserving the whitespaces and end of line in the code.

Some values of array are lost after For cycle using Android API 18, but works in API 22

I'm trying to retrieve the cloud contacts of the user during the Log-in and save them locally in the SQLite, everything is fine until i must write the info into the db, but using a phone with API 18 (4.3) seems that some of the values of the dedicated array are lost, this doesn't happen with another phone with API 22 (5.1.1). I'm trying to figure out the reason of this behavior but after some test i don't know what else do. However, i notice a strange thing, when i start the app using API 18 i got some strange warning in the Android Studio Log, i've made some research but i don't know if it's relevant.

Code in the activity:

            DatabaseNumbersHandler dbn = new DatabaseNumbersHandler(getApplicationContext());

            Integer jsonLen = json.length();

            String[][] id = new String[jsonLen][4];
            String[][] number = new String[jsonLen][4];
            String[][] title = new String[jsonLen][4];
            String[][] description = new String[jsonLen][4];
            String[][] time_creation = new String[jsonLen][4];

            Integer stop = 0;

            Integer iter_position = 0;

            for (int i = 0; i < jsonLen; i++) {
                Log.v("ITER POS", String.valueOf(iter_position));
                JSONObject object = json.optJSONObject(i);
                Iterator<String> iterator = object.keys();

                while (iterator.hasNext()) {
                    try {
                        String currentKey = iterator.next();
                        String currentValue = object.getString(currentKey);

                        // If the key name is not numerical go on
                        if (!currentKey.matches("[-+]?\\d*\\.?\\d+")) {
                            Log.v("KEY_CLEAN", currentKey);

                            // If an "error" key exists show the alert and stop the db writing
                            if (currentKey.matches(KEY_ERROR)) {
                                if (Integer.parseInt(currentValue) == 1){
                                    // If no number exists
                                    final String no_contacts = getResources().getString(R.string.no_contacts_imported);

                                    Toast.makeText(getApplicationContext(),
                                            no_contacts, Toast.LENGTH_LONG).show();
                                    stop = 1;

                                    break;
                                } else if (Integer.parseInt(currentValue) == 2) {
                                    // If the numbers are not imported due to an error
                                    final String contacts_error = getResources().getString(R.string.error_contacts_not_imported);

                                    Toast.makeText(getApplicationContext(),
                                            contacts_error, Toast.LENGTH_LONG).show();
                                    stop = 1;

                                    break;
                                } else {
                                    // Undefined error
                                    final String undefined_error = getResources().getString(R.string.undefined_error);

                                    Toast.makeText(getApplicationContext(),
                                            undefined_error, Toast.LENGTH_LONG).show();
                                    stop = 1;

                                    break;
                                }
                            } else {
                                switch(currentKey){
                                    case "id":
                                        id[i][iter_position] = currentValue;
                                        Log.v("ID", id[i][iter_position]);
                                        break;
                                    case "smart_number":
                                        smart_number[i][iter_position] = currentValue;
                                        Log.v("NUMBER", number[i][iter_position]);
                                        break;
                                    case "title":
                                        title[i][iter_position] = currentValue;
                                        Log.v("TITLE", title[i][iter_position]);
                                        break;
                                    case "description":
                                        description[i][iter_position] = currentValue;
                                        Log.v("DESC", description[i][iter_position]);
                                        break;
                                    case "time_creation":
                                        time_creation[i][iter_position] = currentValue;
                                        Log.v("TIME", time_creation[i][iter_position]);
                                        iter_position++;
                                        break;
                                }
                            }
                        }
                    } catch (JSONException e) {
                        e.printStackTrace();
                    }
                }
            }

            if(stop.equals(0)) {
                for (int i = 0; i < jsonLen; i++) {
                    Log.v("WRITE ID", String.valueOf(id[i][i]));
                    Log.v("WRITE NUMBER", String.valueOf(number[i][i]));
                    Log.v("WRITE TITLE", String.valueOf(title[i][i]));
                    Log.v("WRITE DESC", String.valueOf(description[i][i]));
                    Log.v("WRITE TIME", String.valueOf(time_creation[i][i]));
                    dbn.addNumber(id[i][i], user_id_get, number[i][i], title[i][i], description[i][i], time_creation[i][i]);
                }
            }

Generated log with API 18 (Android 4.3)

Note: The first row is the json array returned from the cloud

02-29 14:55:57.049 2847-7742/com.myapp E/JSON: [{"id":2,"0":2,"number":3384729034,"1":3384729034,"title":"Mark","2":"Mark","description":"friend","3":"friend","time_creation":1449830672,"4":1449830672},{"id":3,"0":3,"number":3497812429,"1":3497812429,"title":"Erik","2":"Erik","description":"brother","3":"brother","time_creation":1455030339,"4":1455030339}]
02-29 14:55:57.064 2847-2847/com.myapp V/ITER POS: 0
02-29 14:55:57.069 2847-2847/com.myapp V/KEY_CLEAN: id
02-29 14:55:57.069 2847-2847/com.myapp V/ID: 2
02-29 14:55:57.074 2847-2847/com.myapp V/KEY_CLEAN: title
02-29 14:55:57.074 2847-2847/com.myapp V/TITLE: Mark
02-29 14:55:57.074 2847-2847/com.myapp V/KEY_CLEAN: time_creation
02-29 14:55:57.074 2847-2847/com.myapp V/TIME: 1449830672
02-29 14:55:57.079 2847-2847/com.myapp V/KEY_CLEAN: number
02-29 14:55:57.079 2847-2847/com.myapp V/NUMBER: 3384729034
02-29 14:55:57.079 2847-2847/com.myapp V/KEY_CLEAN: description
02-29 14:55:57.079 2847-2847/com.myapp V/DESC: friend
02-29 14:55:57.079 2847-2847/com.myapp V/ITER POS: 1
02-29 14:55:57.084 2847-2847/com.myapp V/KEY_CLEAN: id
02-29 14:55:57.084 2847-2847/com.myapp V/ID: 3
02-29 14:55:57.084 2847-2847/com.myapp V/KEY_CLEAN: title
02-29 14:55:57.084 2847-2847/com.myapp V/TITLE: Erik
02-29 14:55:57.089 2847-2847/com.myapp V/KEY_CLEAN: time_creation
02-29 14:55:57.089 2847-2847/com.myapp V/TIME: 1455030339
02-29 14:55:57.089 2847-2847/com.myapp V/KEY_CLEAN: number
02-29 14:55:57.089 2847-2847/com.myapp V/NUMBER: 3497812429
02-29 14:55:57.089 2847-2847/com.myapp V/KEY_CLEAN: description
02-29 14:55:57.094 2847-2847/com.myapp V/DESC: brother
02-29 14:55:57.094 2847-2847/com.myapp V/WRITE ID: 2
02-29 14:55:57.094 2847-2847/com.myapp V/WRITE NUMBER: null
02-29 14:55:57.094 2847-2847/com.myapp V/WRITE TITLE: Mark
02-29 14:55:57.094 2847-2847/com.myapp V/WRITE DESC: null
02-29 14:55:57.094 2847-2847/com.myapp V/WRITE TIME: 1449830672
02-29 14:55:57.104 2847-2847/com.myapp V/WRITE ID: 3
02-29 14:55:57.104 2847-2847/com.myapp V/WRITE NUMBER: null
02-29 14:55:57.104 2847-2847/com.myapp V/WRITE TITLE: Erik
02-29 14:55:57.104 2847-2847/com.myapp V/WRITE DESC: null
02-29 14:55:57.104 2847-2847/com.myapp V/WRITE TIME: 1455030339

Generated log with API 22 (Android 5.1)

Note: The first row is the json array returned from the cloud

02-29 15:05:10.081 23832-24626/com.myapp E/JSON: [{"id":2,"0":2,"number":3384729034,"1":3384729034,"title":"Mark","2":"Mark","description":"friend","3":"friend","time_creation":1449830672,"4":1449830672},{"id":3,"0":3,"number":3497812429,"1":3497812429,"title":"Erik","2":"Erik","description":"brother","3":"brother","time_creation":1455030339,"4":1455030339}]
02-29 15:05:10.081 23832-23832/com.myapp V/ITER POS: 0
02-29 15:05:10.081 23832-23832/com.myapp V/KEY_CLEAN: id
02-29 15:05:10.081 23832-23832/com.myapp V/ID: 2
02-29 15:05:10.081 23832-23832/com.myapp V/KEY_CLEAN: number
02-29 15:05:10.081 23832-23832/com.myapp V/NUMBER: 3384729034
02-29 15:05:10.081 23832-23832/com.myapp V/KEY_CLEAN: title
02-29 15:05:10.081 23832-23832/com.myapp V/TITLE: Mark
02-29 15:05:10.091 23832-23832/com.myapp V/KEY_CLEAN: description
02-29 15:05:10.091 23832-23832/com.myapp V/DESC: friend
02-29 15:05:10.091 23832-23832/com.myapp V/KEY_CLEAN: time_creation
02-29 15:05:10.091 23832-23832/com.myapp V/TIME: 1449830672
02-29 15:05:10.091 23832-23832/com.myapp V/ITER POS: 1
02-29 15:05:10.091 23832-23832/com.myapp V/KEY_CLEAN: id
02-29 15:05:10.091 23832-23832/com.myapp V/ID: 3
02-29 15:05:10.091 23832-23832/com.myapp V/KEY_CLEAN: number
02-29 15:05:10.091 23832-23832/com.myapp V/NUMBER: 3497812429
02-29 15:05:10.091 23832-23832/com.myapp V/KEY_CLEAN: title
02-29 15:05:10.091 23832-23832/com.myapp V/TITLE: Erik
02-29 15:05:10.101 23832-23832/com.myapp V/KEY_CLEAN: description
02-29 15:05:10.101 23832-23832/com.myapp V/DESC: brother
02-29 15:05:10.101 23832-23832/com.myapp V/KEY_CLEAN: time_creation
02-29 15:05:10.101 23832-23832/com.myapp V/TIME: 1455030339
02-29 15:05:10.101 23832-23832/com.myapp V/WRITE ID: 2
02-29 15:05:10.101 23832-23832/com.myapp V/WRITE NUMBER: 3384729034
02-29 15:05:10.101 23832-23832/com.myapp V/WRITE TITLE: Mark
02-29 15:05:10.101 23832-23832/com.myapp V/WRITE DESC: friend
02-29 15:05:10.101 23832-23832/com.myapp V/WRITE TIME: 1449830672
02-29 15:05:10.111 23832-23832/com.myapp V/WRITE ID: 3
02-29 15:05:10.111 23832-23832/com.myapp V/WRITE NUMBER: 3497812429
02-29 15:05:10.111 23832-23832/com.myapp V/WRITE TITLE: Erik
02-29 15:05:10.111 23832-23832/com.myapp V/WRITE DESC: brother
02-29 15:05:10.111 23832-23832/com.myapp V/WRITE TIME: 1455030339

As you can see the array values of "number" and "description" just before the db writing are null with API 18, instead are correct with API 22

The strange warnings that i noticed when i start the app with API 18 are the following:

02-29 14:50:57.264 2847-2847/? D/dalvikvm: Late-enabling CheckJNI
02-29 14:50:57.469 2847-2847/com.myapp W/dalvikvm: VFY: unable to find class referenced in signature (Landroid/view/SearchEvent;)
02-29 14:50:57.469 2847-2847/com.myapp I/dalvikvm: Could not find method android.view.Window$Callback.onSearchRequested, referenced from method android.support.v7.internal.view.WindowCallbackWrapper.onSearchRequested
02-29 14:50:57.469 2847-2847/com.myapp W/dalvikvm: VFY: unable to resolve interface method 18175: Landroid/view/Window$Callback;.onSearchRequested (Landroid/view/SearchEvent;)Z
02-29 14:50:57.469 2847-2847/com.myapp D/dalvikvm: VFY: replacing opcode 0x72 at 0x0002
02-29 14:50:57.469 2847-2847/com.myapp I/dalvikvm: Could not find method android.view.Window$Callback.onWindowStartingActionMode, referenced from method android.support.v7.internal.view.WindowCallbackWrapper.onWindowStartingActionMode
02-29 14:50:57.474 2847-2847/com.myapp W/dalvikvm: VFY: unable to resolve interface method 18179: Landroid/view/Window$Callback;.onWindowStartingActionMode (Landroid/view/ActionMode$Callback;I)Landroid/view/ActionMode;
02-29 14:50:57.474 2847-2847/com.myapp D/dalvikvm: VFY: replacing opcode 0x72 at 0x0002
02-29 14:50:57.519 2847-2847/com.myapp I/dalvikvm: Could not find method android.content.res.TypedArray.getChangingConfigurations, referenced from method android.support.v7.internal.widget.TintTypedArray.getChangingConfigurations
02-29 14:50:57.519 2847-2847/com.myapp W/dalvikvm: VFY: unable to resolve virtual method 490: Landroid/content/res/TypedArray;.getChangingConfigurations ()I
02-29 14:50:57.519 2847-2847/com.myapp D/dalvikvm: VFY: replacing opcode 0x6e at 0x0002
02-29 14:50:57.524 2847-2847/com.myapp I/dalvikvm: Could not find method android.content.res.TypedArray.getType, referenced from method android.support.v7.internal.widget.TintTypedArray.getType
02-29 14:50:57.524 2847-2847/com.myapp W/dalvikvm: VFY: unable to resolve virtual method 512: Landroid/content/res/TypedArray;.getType (I)I
02-29 14:50:57.524 2847-2847/com.myapp D/dalvikvm: VFY: replacing opcode 0x6e at 0x0002
02-29 14:50:57.564 2847-2847/com.myapp I/dalvikvm: Could not find method android.content.res.Resources.getDrawable, referenced from method android.support.v7.internal.widget.ResourcesWrapper.getDrawable
02-29 14:50:57.564 2847-2847/com.myapp W/dalvikvm: VFY: unable to resolve virtual method 453: Landroid/content/res/Resources;.getDrawable (ILandroid/content/res/Resources$Theme;)Landroid/graphics/drawable/Drawable;
02-29 14:50:57.564 2847-2847/com.myapp D/dalvikvm: VFY: replacing opcode 0x6e at 0x0002
02-29 14:50:57.564 2847-2847/com.myapp I/dalvikvm: Could not find method android.content.res.Resources.getDrawableForDensity, referenced from method android.support.v7.internal.widget.ResourcesWrapper.getDrawableForDensity
02-29 14:50:57.564 2847-2847/com.myapp W/dalvikvm: VFY: unable to resolve virtual method 455: Landroid/content/res/Resources;.getDrawableForDensity (IILandroid/content/res/Resources$Theme;)Landroid/graphics/drawable/Drawable;
02-29 14:50:57.564 2847-2847/com.myapp D/dalvikvm: VFY: replacing opcode 0x6e at 0x0002
02-29 14:50:57.659 2847-2847/com.myapp D/libEGL: loaded /system/lib/egl/libEGL_mali.so
02-29 14:50:57.664 2847-2847/com.myapp D/libEGL: loaded /system/lib/egl/libGLESv1_CM_mali.so
02-29 14:50:57.669 2847-2847/com.myapp D/libEGL: loaded /system/lib/egl/libGLESv2_mali.so
02-29 14:50:57.729 2847-2847/com.myapp D/OpenGLRenderer: Enabling debug mode 0

ListView not updating on new inserted items into ObservableCollection on UWP for Windows 10

Im trying to understand how all is connected and I need some help. So far I can do insert, update, delete into sqlite database but I cant make the UI show the changes from the database automatically without me to update the ItemsSource on the ListView whenever changes are made to the database. i.e.:

In my App.xaml.cs

public App()
        {
            this.InitializeComponent();
            this.Suspending += OnSuspending;

            //Connection to the database and create the table if not there
            string path = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "db.sqlite");
            SQLiteConnection conn = new SQLiteConnection(path);
            conn.CreateTable<CheckListItemModel>();
        }

Than in my MainPage.xaml i have a simple page with two buttons and the Listview

<Page
    x:Class="Personal_Checklist_2.MainPage"
    xmlns="http://ift.tt/o66D3f"
    xmlns:x="http://ift.tt/mPTqtT"
    xmlns:local="using:Personal_Checklist_2"
    xmlns:d="http://ift.tt/pHvyf2"
    xmlns:mc="http://ift.tt/pzd6Lm"
    mc:Ignorable="d">

    <Grid Background="{ThemeResource ApplicationPageBackgroundThemeBrush}">
        <Grid.RowDefinitions>
            <RowDefinition Height="100" />
            <RowDefinition Height="*" />
        </Grid.RowDefinitions>
        <StackPanel Grid.Row="0" Orientation="Horizontal" >
            <Button Content="Add Sample To Db" Click="Add_Sample_To_Db_Click" Margin="10,0,10,0" />
            <Button Content="Clear Tasks" Click="Clear_Tasks_Click" />
        </StackPanel>

        <ListView Name="lvListView" Grid.Row="1" >
            <ListView.ItemTemplate>
                <DataTemplate>
                    <StackPanel Orientation="Horizontal">
                        <TextBlock Text="{Binding Title}" />
                        <TextBlock Text="{Binding Id}" />
                    </StackPanel>
                </DataTemplate>
            </ListView.ItemTemplate>
        </ListView>
    </Grid>
</Page>

In MainPage.xaml.cs code-behind I also keep as simple as I know just to add new row into the table and clear the table

using Personal_Checklist_2.DataModels;
using System.IO;
using System.Linq;
using SQLite;
using Windows.UI.Xaml;
using Windows.UI.Xaml.Controls;
using System.Collections.ObjectModel;

namespace Personal_Checklist_2
{
    public sealed partial class MainPage : Page
    {
        static string path = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "db.sqlite");
        ObservableCollection<CheckListItemModel> Tasks = new ObservableCollection<CheckListItemModel>();

        public MainPage()
        {
            this.InitializeComponent();
            using (var conn = new SQLiteConnection(path))
            {
                var query = conn.Table<CheckListItemModel>();
                Tasks = new ObservableCollection<CheckListItemModel>(query.ToList());

                lvTasksList.ItemsSource = Tasks.ToList(); //<<<--- If I dont do this, list is not updated
            }
        }

        private void Add_Sample_To_Db_Click(object sender, RoutedEventArgs e)
        {
            var Task = new CheckListItemModel()
            {
                taskTitle = "Sample Task"
            };

            using (var conn = new SQLiteConnection(path))
            {
                conn.Insert(Task);
                var query = conn.Table<CheckListItemModel>();
                Tasks = new ObservableCollection<CheckListItemModel>(query.ToList());
                lvTasksList.ItemsSource = Tasks.ToList(); //<<<--- If I dont do this, list is not updated
            }
        }

        private void Clear_Tasks_Click(object sender, RoutedEventArgs e)
        {
            using (var conn = new SQLiteConnection(path))
            {
                conn.DeleteAll<CheckListItemModel>();
                var query = conn.Table<CheckListItemModel>();
                Tasks = new ObservableCollection<CheckListItemModel>(query.ToList());
                lvTasksList.ItemsSource = Tasks.ToList(); //<<<--- If I dont do this, list is not updated
            }
        }
    }
}

The database table model looks like this CheckListItemModel.cs

using SQLite;
using System;
using System.ComponentModel;

namespace Personal_Checklist_2.DataModels
{
    class CheckListItemModel : INotifyPropertyChanged
    {
        #region Private fields on DataModel
        private string _taskTitle;
        #endregion

        #region Public properties on DataModel
        [PrimaryKey, AutoIncrement]
        public int taskId { get; set; }

        public string taskTitle
        {
            get { return _taskTitle; }
            set { _taskTitle = value; NotifyPropertyChanged("taskTitle"); }
        }
        #endregion

        #region INotifyPropertyChanged implementation
        public event PropertyChangedEventHandler PropertyChanged;
        private void NotifyPropertyChanged(String propertyName)
        {
            PropertyChangedEventHandler handler = PropertyChanged;
            if (null != handler)
            {
                handler(this, new PropertyChangedEventArgs(propertyName));
            }
        }
        #endregion
    }
}

But it doesn't look right to me this way. Is there a way to achieve this without setting the listview.ItemsSource everytime some changes in db.sqlite and how?

QT Sqlite UPDATE statement prepare error

I am trying to prepare a SQL statement using QT's built-in Sqlite engine.

The following statement:

UPDATE collections SET collection_type=:v1 WHERE 
(collections.collection_name = 'asfg')

fails in QSqlQuery's prepare statement (prepare returns false).

QSqlError err = db.lastError(); gives no error message...

However if I execute the same statement (by replacing :v1 with a number) in the sqlite prompt it succeeds.

What am I doing wrongly?

Modelling multiple tables in Sqlite in Android

I am about to create a database with four tables , A table with Games Played a table with Players, a table with their statistics and a table of statistics codes, which will have a relationship with each other. Do I have to model and the four of them in a separate package in Android Studio, or just players and games?

Need to build complex sqlite query

Have two sqlite tables:

sh_item
id
title
..

and

sh_paramvalues
id
paramid
itemid
value

Have some query that i need to modify

//$get[0] - search query from user
$this->q        = "SELECT * FROM sh_item WHERE title LIKE '%".$get[0]."%' OR descr LIKE '%".$get[0]."%' LIMIT '".$this->page->start."','".$this->page->on1page."'";

I need some query that could get value ( LIKE '%".$get[0]."%') from sh_paramvalues where paramid='some my value' and itemid = id wich use sh_item in base query.

Maybe i need to use join but i am not good at that..

DateAdd function in Sqlite server with column name as input

select DATE(Orders.OrderDate,'10 DAY') from Orders. it will give the following result "1996-07-14"

When using column name directly instead of value in numeric it will give the empty result select DATE(Orders.OrderDate,'Orders.OrderId DAY') from Orders.

What wrong with the above select query?

How to check SQLite table is empty or not in Swift

I am using FMDB. I need a function which tells that table is empty or not I tried this. My table is Empty but it writing SomeData

 var count = shoppingPad.executeStatements("SELECT COUNT(*) FROM myTable")


            print("Count",count)
            if(!count )
            {
               print("EMpty Table")
            }
            else
            {
                print("SomeData")
            }

can anyone provide me code of pouch db and sqllite in ionic framework to store data with syncing facility with simple callback only

Can anyone provide me code of pouch db and sqllite in ionic framework to store data with syncing facility with simple callback only without using promises, service and factory. i am not understanding how to code.

Provide MySQL-to-Sqlite type compatibility with python

I need to read data from MySQL, process it with python script and write the result into Sqlite. Also, I need to convert MySql create definitions to Sqlite create definitions. Are there any existing libraries for python to convert MySql data type (including set, enum, timestamp, etc.) to Sqlite data type, or I should write it myself?

sqlite query not matching string

I have a list of about 350 music files that I store in an sqlite table. If new files get added they are added to the db. So I have a query to check if the path of the file is in the table and if not then insert it. However 3 files never match the query even though they are in the table! Therefore they are added over and over each time I start my app.

I am using SQLite for Universal Windows Platform & SQLite.Net-PCL packages for the sql content. File is my custom class that stores some strings and other file properties from each file.

 //database creation on first app launch
 using (var db = DbConnection)
 {
      var Trackdb = db.CreateTable<File>();
 }

 //where I scan all files and add to db if not already there 
 var dbconn = DbConnection;
 foreach (File file in FileList)
 {
      var existingfile = dbconn.Query<Track>("select * from File where Path = '" + file .Path.Replace("'", "''") + "'").FirstOrDefault();
      if (existingtrack == null)
      {
          file.ID = dbconn.Insert(file);
          Debug.WriteLine(file.Path + " was added to db");
      }
 }

The output everytime, from what I see there might be 2 characters which could cause this, but why? "–" vs "-" and "ë" vs "e". Is sqlite not equipped to handle these characters or is my query not robust enough? The insert statement which works fine, leading me to believe it accepts these characters as it displays my my app fine.

"C:\Data\Users\Public\Music\David Guetta & Avicii – Sunshine.mp3 was added to db"
"C:\Data\Users\Public\Music\Gotye - Somebody That I Used To Know (Tiësto Remix).mp3 was added to db"
"C:\Data\Users\Public\Music\Lana Del Rey – Summertime Sadness (Cedric Gervais Remix).mp3 was added to db"

SQLiteOpenHelper shows error

I want to count number of tables saved in the database by giving some parameters. But it is showing error message

"The method query(boolean, String, String[], String, String[], String,
String, String, String, CancellationSignal) in the type SQLiteDatabase is not applicable for the arguments (boolean, String, String[], String, String, String, String[], null, null, null, null)" what might be the reason?

Here's the method in my helper class:

public int fetchBatches(String jobId, String jobName, String jobType) {

 String columnName = TableClass.COLUMN_JOB_ID, columnValue = jobId,  
 columnJobName = TableClass.COLUMN_JOB_NAME, columnJobValue = jobName, 
            columnJobType =  TableClass.COLUMN_JOB_TYPE, columnJobTypeValue  
  = jobType;

    if (jobId.equalsIgnoreCase("")) {
        columnName = TableClass.COLUMN_JOB_ID;
        columnValue = jobId;
        columnJobName = TableClass.COLUMN_JOB_NAME;
        columnJobValue = jobName;
        columnJobType =  TableClass.COLUMN_JOB_TYPE;
        columnJobTypeValue = jobType;
    }

    Cursor cursor = null;
    SQLiteDatabase database = getReadableDatabase();
    cursor = database.query(true, TableClass.TABLE_PRODUCTS,   //showing 
//error here
            new String[] { TableClass.COLUMN_BATCH }, columnName + " =? and ", columnJobName + " =? and ", columnJobType + "=?",
            new String[] { columnValue,columnJobValue,columnJobTypeValue }, null, null, null, null);


    cursor = database.query(TableClass.TABLE_PRODUCTS,
            null, columnJobType + " =? and " +columnName +" =? ",new String[] 
                    { columnJobTypeValue,columnValue }, null, null, null);

    if (cursor != null) {
        int count = cursor.getCount();
        if (cursor != null) {
            cursor.close();
            database.close();
        }
        return count;
    } else {
        return 0;
    }
}

add image in sqlite database

Hi i have a problem when inserting an android image in my datatabase for a quizz application.The questions must appear with a specific image,i trie to do as someone explained me here but i have an error in my method where i add the questions.I inserted the image like this.

This my database

package com.example.toureamidou.piste;

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

import java.util.ArrayList;
import java.util.List;

/**
 * Created by TOURE Amidou on 24/02/2016.
 */
public class DatabaseHelper extends SQLiteOpenHelper {
    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "Question";
    private static final String TABLE_QUEST = "quest";
    private static final String KEY_ID = "id";
    private static final String KEY_QUES = "question";
    private static final String KEY_ANSWER = "answer";
    private static final String KEY_OPTA= "opta";
    private static final String KEY_OPTB= "optb";
    private static final String KEY_OPTC= "optc";
    private static final String KEY_IMAGE = "image";
    private SQLiteDatabase dbase;
    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        dbase=db;
        String sql = "CREATE TABLE IF NOT EXISTS " + TABLE_QUEST + " ( "
                + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_QUES
                + " TEXT, " + KEY_ANSWER+ " TEXT, "+KEY_OPTA +" TEXT, "
                +KEY_OPTB +" TEXT, "+KEY_OPTC+" TEXT,"+KEY_IMAGE+"TEXT)";
        db.execSQL(sql);
        addQuestions();
    }





    private void addQuestions() {
        Questions q1=new Questions("FFFFF","Tata", "Titi", "Toto", "Tata");
        this.addQuestion(q1);
        Questions q2=new Questions("Quel est le plus grand pays au monde", "Suisse", "Italie", "Chine", "Chine");
        this.addQuestion(q2);
        Questions q3=new Questions("Comment s'appele le président francais","Obama", "Hollande","Gorbatchev", "Hollande" );
        this.addQuestion(q3);
        Questions q4=new Questions("zzzzzzzzzzzzzzzzzzzz", "ali", "dede", "home","dede");
        this.addQuestion(q4);
        Questions q5=new Questions(" est l'homme le plus riche au monde","Gates","Trump","Carlos Slim","Trump");
        this.addQuestion(q5);

    }






    private void addQuestion(Questions quest,byte[] image) {
        Log.d("addQuestions", quest.toString());
        ContentValues values = new ContentValues();
        values.put(KEY_QUES, quest.getQUESTION());
        values.put(KEY_ANSWER, quest.getANSWER());
        values.put(KEY_OPTA, quest.getOPTA());
        values.put(KEY_OPTB, quest.getOPTB());
        values.put(KEY_OPTC, quest.getOPTC());
        values.put(KEY_IMAGE,image);
        dbase.insert(TABLE_QUEST, null, values);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_QUEST);
        onCreate(db);
    }
    public List<Questions> getAllQuestions() {
        List<Questions> quesList = new ArrayList<Questions>();
        String selectQuery = "SELECT  * FROM " + TABLE_QUEST;
        dbase=this.getReadableDatabase();
        Cursor cursor = dbase.rawQuery(selectQuery, null);
        if (cursor.moveToFirst()) {
            do {
                Questions quest = new Questions();
                quest.setID(cursor.getInt(0));
                quest.setQUESTION(cursor.getString(1));
                quest.setANSWER(cursor.getString(2));
                quest.setOPTA(cursor.getString(3));
                quest.setOPTB(cursor.getString(4));
                quest.setOPTC(cursor.getString(5));
                quesList.add(quest);
            } while (cursor.moveToNext());
        }

        return quesList;
    }
    public int updateQuestions (Questions questions){
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put("questions",questions.getQUESTION());
        contentValues.put("answer",questions.getANSWER());
        contentValues.put("opta", questions.getOPTA());
        contentValues.put("optb", questions.getOPTB());
        contentValues.put("optc", questions.getOPTC());
        int i = db.update(TABLE_QUEST, contentValues, KEY_ID + " = ?", new String[]{String.valueOf(questions.getID())});
        db.close();
        return i;

    }

}

and this is my quizz activity class


package com.example.toureamidou.piste;

import android.content.Intent;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;
import android.media.MediaPlayer;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.RadioButton;
import android.widget.RadioGroup;
import android.widget.TextView;

import java.io.ByteArrayOutputStream;
import java.util.List;
public class Quiz extends AppCompatActivity {
    List<Questions> quesList;
    int qid=0;
    Questions currentQ;
    TextView txtQuestion;
    RadioButton rda, rdb, rdc;
    Button butNext;
    DatabaseHelper myDb;
    MediaPlayer wrongsound;
    MediaPlayer winsound;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_quiz);
        myDb = new DatabaseHelper(this);
        quesList = myDb.getAllQuestions();
        currentQ=quesList.get(qid);
        txtQuestion=(TextView)findViewById(R.id.textView1);
        rda=(RadioButton)findViewById(R.id.radio0);
        rdb=(RadioButton)findViewById(R.id.radio1);
        rdc=(RadioButton)findViewById(R.id.radio2);
        butNext=(Button)findViewById(R.id.button1);
        wrongsound = MediaPlayer.create(this,R.raw.mywrong);
        winsound = MediaPlayer.create(this,R.raw.mysound);
        setQuestionsView();

        Bitmap b = BitmapFactory.decodeResource(getResources(),R.drawable.quizz);
        ByteArrayOutputStream boss = new ByteArrayOutputStream();
        b.compress(Bitmap.CompressFormat.PNG,100,boss);
        byte[] img = boss.toByteArray();


        butNext.setOnClickListener(new View.OnClickListener() {

            @Override
            public void onClick(View v) {
                RadioGroup grp = (RadioGroup) Quiz.this.findViewById(R.id.radioGroup1);
                RadioButton answer = (RadioButton) Quiz.this.findViewById(grp.getCheckedRadioButtonId());
                Log.d("your answer", Quiz.this.currentQ.getANSWER() + " " + answer.getText());
                if (Quiz.this.currentQ.getANSWER().equals(answer.getText())) {

                    winsound.start();
                } else {
                    wrongsound.start();
                }

                if (Quiz.this.qid < 5) {
                    Quiz.this.currentQ = (Questions) Quiz.this.quesList.get(Quiz.this.qid);
                    Quiz.this.setQuestionsView();
                } else {
                    Intent intent = new Intent(Quiz.this, Result.class);
                    Bundle b = new Bundle();

                    intent.putExtras(b);
                    Quiz.this.startActivity(intent);
                    Quiz.this.finish();
                }

            }
        });



    }



    private void setQuestionsView() {
        txtQuestion.setText(currentQ.getQUESTION());
        rda.setText(currentQ.getOPTA());
        rdb.setText(currentQ.getOPTB());
        rdc.setText(currentQ.getOPTC());
        qid++;                                


    }







}

cant delete the value from inbox at specific number

i didnt write this code , this code has been given to me for some changes but some of the features are not working like a deleting conversation method. here is the Deleting method

        public static boolean deleteSmsofContact(Context context, String number,
        boolean deleteLocked)
{
    int result;

    if (deleteLocked) {

       //URI_SMS = Uri.parse("content://sms/");


        result = context.getContentResolver().delete(Constants.URI_SMS,
                "address = ?",new String[]{ number });

    } else {
        result = context.getContentResolver().delete(Constants.URI_SMS,
                "address = ? AND locked = ?", new String[] { number, "1" });
    }

    if (result > 0) {
        return true;
    }

    return false;
}

from where i am calling this method

 boolean result = Utils.deleteSmsofContact(InboxActivity.this,    sms.getNumber(), true);
                                            if (result) {
                                                  dataList.remove(threadPosition);
                                                iAdapter.notifyDataSetChanged();
                                                Toast.makeText(InboxActivity.this,"Removed",Toast.LENGTH_LONG).show();
                                            }else
                                            {
                                                Toast.makeText(InboxActivity.this,"cant removed",Toast.LENGTH_LONG).show();
                                            }

Update a column with anothers table's column [duplicate]

This question already has an answer here:

• sql: how to update table values from another table with the same user name? 1 answer

I have table EMPLOYEES (surname, name, id, email) and TEMP(surname, name, email) and I want to update the EMPLOYEES.mail with the TEMP.email. I tried this, but I get an error

UPDATE EMPLOYEES
SET email = ( SELECT TEMP.surname , TEMP.name , TEMP.email , EMPLOYEES.email, EMPLOYEES.surname , EMPLOYEES.name
              FROM EMPLOYEES, TEMP
              WHERE (TEMP.surname = EMPLOYEES.surname AND TEMP.name = EMPLOYEES.name)
            )

but I get this error [ only a single result allowed for a SELECT that is part of an expression ]

In SQLite/WebSQL, will BEGIN go through entire table again to find the updated row even though the TRIGGER knows where it is?

Below I have a users table and I want it so that every time a row's avatar changes to a different value, the same row's downloadavatar is set to 1.

CREATE TABLE users (
   id UNIQUE TEXT,
   avatar TEXT,
   downloadavatar INTEGER
)

CREATE TRIGGER update_users_downloadavatar AFTER UPDATE OF avatar ON users WHEN old.avatar!=new.avatar
BEGIN
    UPDATE users SET downloadavatar=1 WHERE id=old.id;
END

The SQL above does just that. My question is will it result in searching the entire users table for the row that meets the WHERE condition despite the TRIGGER already knowing where it is? If yes, how can I rectify this inefficiency?

How to add multiple rows in a single table using single submit button in ruby on rails

I am new to ruby on rails and dont know much about it. I want to add attribute_id and attribute_value_id from table Attribute and AttirbuteValue to third table CustomerOrder (id, attribute_id, attribute_value_id). A user can have multiple combination of attribute_id and attribute_value_id. I am looping all values of Attribute and AttirbuteValue to a customerOrder from and want to save all checked values to CustomerOrder table. Thanks for your help in advance.

SQLIte ForeignKey to multiple tables

I am having scenario where i have to use two different tables as foreign table where the primary tables remain same but need to map with different tables so required to give two different foreign key with two different table, is it possible?

CREATE TABLE Address1(folderId VARCHAR,uid INTEGER,folderId1 VARCHAR,uid1 INTEGER,type INTEGER,address VARCHAR(256),name VARCHAR(256),"folderId1" VARCHAR, "uid1" INTEGER,PRIMARY KEY (folderId, uid, type, address),
FOREIGN KEY (folderId, uid) REFERENCES MapHeader1(folderId, uid),
FOREIGN KEY (folderId1, uid1) REFERENCES MapHeader2(folderId, did))

I have tried this so far but its not working.Any suggestion or example.

Python - None returned by using cursor.fetchone()

Internet searches have told me to use cursor.fetchone() as a way to get a particular row from an SQL database in python. But when I use it, I get a None. I'm pretty sure there is stuff in there, so I'm confused about why this is happening. Here is some code

db = sqlite3.connect('dataset.db')
curs = db.cursor()
curs.execute("CREATE TABLE data(id INTEGER PRIMARY KEY, dataObj REAL)")

sqlQuery = "SELECT dataObj FROM data WHERE id = ?"
curs.execute('''INSERT INTO data(dataObj) VALUES (?)''', (info, )) #this is how I add stuff into the table
num = 1
result = curs.execute(sql, (num, )).fetchone()  #should return row from data table
print result  #returns None

I feel like I'm doing something dumb, so sorry if it's something easy.

Neccessary procedure for deploying a django app for production

I have developed a django app and i'am confused.

1). What is the procedure to convert the django app from development to production.

2). Once the app is available for users (ie Live on a server),How to add future development to the app without needing to upload the whole thing but just the changes and the new packages.

3)Is sqllite efficient enough to be used for servicing a large group of users and integration with android, ios apps.

I used sqlite + pysqlite2 + sqlalchemy. But there are some confused error. eg.undefined symbol

File "/home/myweb/app/db.py", line 40, in init

engine = create_engine('sqlite:///'+db_path)

File "/usr/local/lib/python2.7/site- packages/sqlalchemy/engine/init.py", line 386, in create_engine

return strategy.create(*args, **kwargs)

File "/usr/local/lib/python2.7/site- packages/sqlalchemy/engine/strategies.py", line 75, in create

dbapi = dialect_cls.dbapi(**dbapi_args)

File "/usr/local/lib/python2.7/site- packages/sqlalchemy/dialects/sqlite/pysqlite.py", line 339, in dbapi

 raise e

ImportError: /usr/local/lib/python2.7/site-packages/pysqlite2/_sqlite.so:

undefined symbol: PyOS_mystrnicmp

SQLite datetime columns show up as text

I'm creating my own SQLite functions by inheriting from System.Data.SQLite.SQLiteFunction. This works well except I'm having issues with datetime columns. I'm not sure if it's due to SQLite or the .NET wrapper (System.Data.SQLite).

If I pass in a DateTime column into my function as an argument, the values get passed into my function as strings (e.g. "2016-02-28 10:52") instead of datetimes. Also, if my function returns a DateTime object the value gets turned into a string.

So I'm unclear if SQLite understands DateTime. I can create a table that has a datetime column and if I use pragma table_info(myTable), it shows that the column is indeed datetime, but if I test it using typeof(myCol) that returns text. What gives, does it support datetime or no? Can I get it to pass DateTime values to my functions if the column is created as a datetime?

Importing large data into sqlite db on android - takes too long. need alternatives

I have an sqlite table with my app...but will be needing to add/remove large sets of data as quickly as possible. I'm open to a lot of options. Right now, I have about 35,000 lines which I can update in about 15 seconds. I would prefer something even quicker. Essentially need just one column to act as my secondary text.

Right now, the user downloads a txt file and then that file gets passed to the below method.

public boolean installLanguageFile(String resourceId) {

        SQLiteDatabase db;
        db = new DataBaseHelper(getContext()).getWritableDatabase();
        try {

            File mFile = new File(getActivity().getFilesDir() + resourceId);

            InputStream insertsStream = new FileInputStream(mFile);
            BufferedReader insertReader = new BufferedReader(new InputStreamReader(insertsStream));

            long lengthOfFile = 34504;
            long total = 0;
            int rowId = 1;

            String sql = "UPDATE Story_Data SET text2 = ('?') WHERE _id = ?";
            db.beginTransaction();
            SQLiteStatement stmt = db.compileStatement(sql);
            while (insertReader.ready()) {
                total += rowId;
                stmt.bindString(1, insertReader.readLine());
                stmt.execute();
                stmt.clearBindings();
                publishProgress("" + (rowId * 100 / lengthOfFile));
                rowId++;
            }

            db.setTransactionSuccessful();
            db.endTransaction();

            insertReader.close();

            //mCallback.reloadView();
            return true;

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

    }

Row in SQLite database not getting deleted after a specified amount of time

I have a table with one row. I would like to delete this row after one hour. Within this row, I store the timestamp with the specified format as 'COULMN_DATE'. I got the query that should work from here:

Delete a row in a table after an hour it is inserter using sql query

And I call it like this in my onCreate() function within my databaseHandler class, and I made sure I also updated my database version beforehand. I replaced hour to minutes for checking purposes:

 db.execSQL("DELETE FROM " + TABLE_PRODUCTS +" 
              WHERE "+ COLUMN_DATE +" < DATETIME('NOW', '-1 minutes')");

But after inserting a timestamp, waiting a couple of minutes, and then restarting the application to check whether the data was deleted, it was still there. Is it because I am calling it in the wrong place? If so, where should this query be done?

Nightmare Fuzzing Suite

I am trying to set the configuration of the Fuzzing Suite called 'Nightmare'. But I have problems. First, I cloned the repository using the command:

$ git clone http://ift.tt/1oHMZeY

Later I installed all dependencies described in the file install.txt. But at step 6) I ve got problems. The config.py file looks like the following:

##############################################################################
# Nightmare Fuzzing Project web application configuration file
##############################################################################

# Administrator username
NFP_USER="admin"
# Administrator password (SHA1 hash, default is 'nightmare')
NFP_PASS="0ffa932690225172d291718510fd8a31b44d5698"

# Beanstalk queue host
QUEUE_HOST="localhost"
# Beanstalk queue listening port
QUEUE_PORT=11300

# Is debug enabled?
DEBUG=True

# Database hostname or IP address
DB_HOST= "localhost"
# Database name
DB_NAME= "nightmare"
# Database username
DB_USER= "fuzzing"
# Database password
DB_PASS= "fuzzing"

and the config.cfg file has the following content:

#[database]
#dbn=mysql
# Database hostname or IP address
#host=localhost
# Database name
#db=nightmare
# Database username
#user=fuzzing
# Database password
#pw=fuzzing

#~ ########################################################################
#~ # Example configuration for SQLite3
#~ ########################################################################
[database]
dbn=sqlite
#Database name
db=/home/abdullah/nightmare/db.sqlite

now, when I type the commands:

$ cd nightmare/runtime
$ python nightmare_frontend.py

Then I get this display:

Can somebody tell me what is wrong here ? It says that there is no config table but in a file nightmare_sqlite.sql I have such a table and I also the paths correctly (see the following lines):

CREATE TABLE `config` (
  `config_id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `value` varchar(300) DEFAULT NULL,
  `description` varchar(300) DEFAULT NULL,
  `date` date DEFAULT NULL
);
INSERT INTO "config" VALUES(7,'SAMPLES_PATH','/home/abdullah/nightmare/results',NULL,NULL);
INSERT INTO "config" VALUES(8,'TEMPLATES_PATH','/home/abdullah/nightmare/samples',NULL,NULL);
INSERT INTO "config" VALUES(9,'NIGHTMARE_PATH','/home/abdullah/nightmare',NULL,NULL);
INSERT INTO "config" VALUES(10,'QUEUE_HOST','localhost',NULL,NULL);
INSERT INTO "config" VALUES(11,'QUEUE_PORT','11300',NULL,NULL);
INSERT INTO "config" VALUES(12,'TEMPORARY_PATH','/tmp',NULL,NULL);
DELETE FROM sqlite_sequence;
INSERT INTO "sqlite_sequence" VALUES('mutation_engines',11);
INSERT INTO "sqlite_sequence" VALUES('config',11);
CREATE UNIQUE INDEX `project_id` on project_engines (`project_id`,`mutation_engine_id`);
CREATE UNIQUE INDEX idx_uq_config_name on config (`name`);
COMMIT;

I hope somebody can help.

Best regards,

SQLite print message command

I need to print a message between query results, but i can't find an appropriate SQL command that works with SQLite. For example if I have 2 tables, BRANCH and STAFF, and I need to return it's contents, i want the output to be like

BRANCH data ------------ *table content here* STAFF data ------------- * table content here* How do I print BRANCH data ----- message?

Similar SQLite toString() functions yielding different results?

I have a SQLite table initialised as such. It is a single row implementation.

public void onCreate(SQLiteDatabase db) {
    String query = "CREATE TABLE " + TABLE_PRODUCTS + "(" +
            COLUMN_ID + " INTEGER PRIMARY KEY DEFAULT 1, " +
            COLUMN_PRODUCTNAME + " TEXT ," +
            COLUMN_DATE + " TEXT " +
            ");";
    db.execSQL(query);
}

I update the row this way.

public void updateRow(int id, Products product){
    ContentValues values = new ContentValues();

    values.put(COLUMN_ID, id);
    values.put(COLUMN_PRODUCTNAME, product.get_productname());
    values.put(COLUMN_DATE, getDateTime());

    SQLiteDatabase db = getWritableDatabase();
    db.replace(TABLE_PRODUCTS, null, values);
    db.close();
}

Initially, I did not have COLUMN_DATE. I tacked it on after the original was working correctly. When I want to check that things work, I toast a String to the screen. And this works great with COLUMN_PRODUCTNAME, here's the function to do this.

public String prodToString(){
    String dbString = "";
    SQLiteDatabase db = getWritableDatabase();
    String query = "SELECT * FROM " + TABLE_PRODUCTS + " WHERE 1";
    Cursor c = db.rawQuery(query, null);
    c.moveToFirst();
    dbString = c.getString(c.getColumnIndex(COLUMN_PRODUCTNAME));
    db.close();
    return dbString;
}

But when I copypasted the exact same function above under name dateToString(), only changing COLUMN_PRODUCTNAME to COLUMN_DATE and call said function, I get the error:

Failed to read row 0, column -1 from a CursorWindow which has 1 rows, 2 columns.

java.lang.IllegalStateException: Couldn't read row 0, col -1 from CursorWindow. 

Make sure the Cursor is initialized correctly before accessing data from it.

I doubt it has anything to do with the getDateTime() function, because I can output that fine. Whether I put it into the table correctly is still unclear to me, but it looks to be ok in that sense. I'm not sure what's going wrong. Can anyone see it?

how to make sure that insert operation complete and the table has update?

Assume that there are 2 threads, one thread insert data to database via content provider. After the insert statement, callback a method to query the database in the anohter thread. However, some times it is possible that the query finished before the table has been update. So the new data can not been fetch.

Is there a method to make the insert operation really complete in android? Then calling the query method would return the new inserted data.

sqlib iOS preformance issue

I got a strange performance issue on iOS using C library version.

I found all the "common" tips regarding performance and using transaction, prepared statements, memory cache, late writing and so on.

The code is a loop that spins through a text file, inserting 370000 rows. The time for inserts increase drastically as I ran trough it. Here is the timeing for the first 130000 rows:

Wrote 10000 rows: 5 seconds.
Wrote 10000 rows: 8 seconds.
Wrote 10000 rows: 11 seconds.
Wrote 10000 rows: 18 seconds.
Wrote 10000 rows: 25 seconds.
Wrote 10000 rows: 35 seconds.
Wrote 10000 rows: 61 seconds.
Wrote 10000 rows: 114 seconds.
Wrote 10000 rows: 136 seconds.
Wrote 10000 rows: 163 seconds.
Wrote 10000 rows: 180 seconds.
Wrote 10000 rows: 198 seconds.
Wrote 10000 rows: 215 seconds.

So, the first 10000 took 5 sec, the 10000 from 120000 to 130000 tok 215 seconds. So, is this a limitation of sqlite on iOS? Or is there something I have missed?

Before I start, I use:

PRAGMA synchronous=OFF
PRAGMA journal_mode=MEMORY
PRAGMA temp_store=MEMORY

In addition I have tried inserts without keys or indexes in the table, just to see if that got something to do with it.

I do commit each 10000 row, then starts a new transaction. I ran one prepare, then binds and insert data 10000 times before starting over.

I also read threads like Improve INSERT-per-second performance of SQLite? without much help in my problem.

Udate:

The timing is done like this:

db->Prepare(insertSQL);
timer=time(NULL);
while((pos=content.find('\n',lineStart))!=std::string::npos)
{
    if (!(lines%10000) && lines>0)
    {
        printf("Wrote 10000 rows: %d seconds.\n",(int) (time(NULL)-timer));
        timer=time(NULL);
        db->Prepare("COMMIT");
        db->Execute();
        db->Prepare("BEGIN");
        db->Execute();
        db->Prepare(insertSQL);
     }
     values.clear();

     <Some voodoo that fills the values array>
     for (j = 1; j < numFields; j++)
        db->Bind("@COL" + std::to_string(j), values[j]);
    db->Execute();
    db->Reset();

    lineStart=pos+1;
    lines++;
}
db->Prepare("COMMIT");
db->Execute();

Rails 4 - Group by date rage on month intervals

I'm trying to group my Events table my months and in range so i can present it in a chart (i'm using the chartKick gem). The Events have a start_date and a end_date so if a event starts in February and ends in February it will count as a February event, but if the Event starts in February and end in March it will count as a March Event. The format i desire is:

{"JAN"=>2, "FEB"=>3, "MAR"=>5, "APR"=>10, "MAY"=>5, "JUNE"=>0, "JULY"=>12, "AUG"=>4, "SEPT"=>17, "OCT"=>8, "NOV"=>0, "DEC"=>3}

Right now i'm using something very simple that doesn't do what i want:

<%= pie_chart Event.group(["DATE(created_at)"]).count %>

Can anyone help me getting the right query?

thanks

Iterate HashMap

I have three values I get from a json querry, and I thought that I would need to store then in a HashMap> to later, iterate over it and store in a sqlite database, but I don't know how to iterate it.

I have 3 values: a name, product and quantity, and I need to store it with:

db.execSQL("Insert into table(name, product , quantity) values ('" + name + "', '" + product + "' + " + quantity + ")");

Any idea? Thanks (Sorry for my English)

How to add date in sqlite database android studio

I have to two date variables in my bean class

public class bean {

private Date dos;
private Date doe;

}

In my DAO class // columns in my sqlite database table

public class TaskDAO extends SQLiteOpenHelper {

public static final String  C_DOS = "d1";
public static final String  C_DOE = "d2";

@Override public void onCreate(SQLiteDatabase db) {

    StringBuilder sb = new StringBuilder();
    sb.append("CREATE TABLE "+TBNAME);
    sb.append(" ("+ID+" INTEGER PRIMARY KEY AUTOINCREMENT, ");
    sb.append(C_DOS+" INTEGER,");
    sb.append(C_DOE+" INTEGER)");

    String sql = sb.toString();
    db.execSQL(sql);
}

void addbean(bean b)

{
    SQLiteDatabase database = this.getWritableDatabase();
    ContentValues values = new ContentValues();

    values.put(C_DOS,b.getdos);
    values.put(C_DOE,b.getdoe);

    database.insert(TBNAME,null,values);
}

Fetch data from sqlite android

I have following table

private static final String DB_TABLE = "create table user (id integer primary key autoincrement, " 
                                            + "username text not null, password text not null, tel text not null, info text not null, job text);";

How can I fetch data using cursor and convert it to string? because I am getting null Should I have set gets? I have to fetch job and info by username and password?

Cursor theUser = dbHelper.fetchUser(thisUsername, thisPassword);
   if (theUser != null) {

How here also fetch a info and job in String?

            startManagingCursor(theUser);
            if (theUser.getCount() > 0) {
                //saveLoggedInUId(theUser.getLong(theUser.getColumnIndex(DatabaseAdapter.COL_ID)), thisUsername, thePassword.getText().toString());
                stopManagingCursor(theUser);
                theUser.close();
               // String text = dbHelper.getYourData();
                Intent i = new Intent(v.getContext(), InfoActivity.class);
                startActivity(i);
            }

            //Returns appropriate message if no match is made
            else {
                Toast.makeText(getApplicationContext(),
                        "You have entered an incorrect username or password.",
                        Toast.LENGTH_SHORT).show();
               // saveLoggedInUId(0, "", "");
            }
            stopManagingCursor(theUser);
            theUser.close();
        }

        else {
            Toast.makeText(getApplicationContext(),
                    "Database query error",
                    Toast.LENGTH_SHORT).show();
        }
    }

---

private static final String LOGIN_TABLE = "user";
    //Table unique id
    public static final String COL_ID = "id";
    //Table username and password columns 
    public static final String COL_USERNAME = "username";
    public static final String COL_PASSWORD = "password";
    private static final String KEY_PHONE = "tel";
    private static final String INFO = "info";
    private static final String JOB = "info";

public Cursor fetchUser(String username, String password) {
    Cursor myCursor = database.query(LOGIN_TABLE, 
            new String[] { COL_ID, COL_USERNAME, COL_PASSWORD }, 
            COL_USERNAME + "='" + username + "' AND " + 
            COL_PASSWORD + "='" + password + "'", null, null, null, null);

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

thank you in advance!!!

Android Login App using existing sqlite database file

I want to make an android login project using an existing SQLite database file which already have some passwords for certain users

Save my offline osm maps(MOBAC, .mbtiles) to the sqlite database

Firstly sorry about my poor English. Also i am new to android developing and also to osmdroid.
I managed to show my offline maps in my device. I am trying for days with no success to find out how can i save my offline osm maps with some POIS to the the sqlite database.
The reason is because to make an app that when someone else get it to have the maps of a specific areas already saved in his/her device.

Is there is any sample or a tutorial? Thank you in advance.

How to retrieve data from sqlite database by category

I have a table contain 5 columns id,product_category,product_price,product_name,product_quantity.

i want to retrieve data grouped according to category. how to query this data??

Convert String to character if character not match will replace until it matches

/* search from database */
            String value = fname+"_"+mname+"_"+lname+"_"+bdate;
            DataBaseHelper dbh = new DataBaseHelper(SecondActivity.this);
            String holder = dbh.search(value);


            /*convertion of string search to array */
            char[] chars=holder.toCharArray();
            Character[] characters=new Character[chars.length];

                try{
                for(int x=0;x<chars.length;x ++){
                    characters[x]=chars[x];

                    if(characters[x].equals('A')){
                        // Do nothing
                    }else{
                        //replace function i dont know.
                    }

im stuck and i dont know what should i do next. I want to replace the character if it does not match the Name from the database. For example: from database: fname Robin User input: rodin since it did not match it will then replace the un-matched character until it matches the name Robin.

Embedded Sqlite database with Ormlite and JavaFX: error creating table

I am trying to persist some data in my JavaFX application. My code fails when attempting to create a table in my PersistenceManager class:

private final static String DATABASE_URL = "jdbc:sqlite:my.db";
private final static String DATABASE_DRIVER = "org.sqlite.JDBC";
// ...
    Class.forName(DATABASE_DRIVER);
    DriverManager.getConnection(DATABASE_URL);
    connectionSource = new JdbcConnectionSource(DATABASE_URL);
    // here it fails:
    TableUtils.createTable(connectionSource,MyClass.class);

A file my.db is actually created, but it is empty. When executing TableUtils.createTable, it triggers the following:

Caused by: java.lang.reflect.InvocationTargetException
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at sun.reflect.misc.Trampoline.invoke(MethodUtil.java:71)
    at sun.reflect.GeneratedMethodAccessor1.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at sun.reflect.misc.MethodUtil.invoke(MethodUtil.java:275)
    at javafx.fxml.FXMLLoader$MethodHandler.invoke(FXMLLoader.java:1771)
    ... 43 more
Caused by: java.lang.AbstractMethodError: com.j256.ormlite.jdbc.JdbcDatabaseConnection.compileStatement(Ljava/lang/String;Lcom/j256/ormlite/stmt/StatementBuilder$StatementType;[Lcom/j256/ormlite/field/FieldType;I)Lcom/j256/ormlite/support/CompiledStatement;
    at com.j256.ormlite.table.TableUtils.doStatements(TableUtils.java:458)
    at com.j256.ormlite.table.TableUtils.doCreateTable(TableUtils.java:441)
    at com.j256.ormlite.table.TableUtils.createTable(TableUtils.java:221)
    at com.j256.ormlite.table.TableUtils.createTable(TableUtils.java:54)
    at org.lh.inquadramentopupilla.persistence.PersistenceManager.<init>(PersistenceManager.java:32)

My Class is this:

@DatabaseTable(tableName = "myclass")
public class MyClass {
    @DatabaseField(id = true)
    private String nome;

    @ForeignCollectionField(eager = false)
    private Collection<MyOtherClass> risposte;

    protected MyClass() {
        super();
    }
}

and MyOtherClass is:

@DatabaseTable(tableName = "myotherclass")
public class MyOtherClass {
    @DatabaseField(id = true)
    private String nome;

    @DatabaseField(foreign = true)
    private MyClass scheda;

    protected MyOtherClass() {
        super();
    }
}

What should I look for correcting this?

Use the latest android Data Binding library with data in a SQLite DB

In my application I'm currently using android content providers to get data from a sqlite database. That data is not shared with other apps.

Now I'd like to reengineer my app in such a way that it adopts the MVVM guidelines, but I couldn't find any best practice to use data from DBs.

Should I use an ORM like greenDAO or is there any alternative that uses data from cursors?

SQL Help Using NESTED SELECTION

Here's my table

CREATE TABLE emp (num INTEGER NOT NULL, 
              name VARCHAR(20) NOT NULL, 
              dept VARCHAR(20) NOT NULL, 
              salary INTEGER NOT NULL,
              boss INTEGER NOT NULL,
              PRIMARY KEY (num),
              FOREIGN KEY (boss) REFERENCES emp (num)
              );

INSERT INTO emp
VALUES ('1', 'PRESIDENT', 'gh', '10000', '1');
INSERT INTO emp
VALUES ('2', 'Bob', 'Slave', '6456', '3');
INSERT INTO emp
VALUES ('3', 'Matthew', 'M', '1', '1');
INSERT INTO emp
VALUES ('4', 'Marl', 'P', '534465', '2');
INSERT INTO emp
VALUES ('5', 'Apple', 'P', '554545646512', '2');
INSERT INTO emp
VALUES ('6', 'Roy', 'Slave', '125', '1');
INSERT INTO emp
VALUES ('7', 'Marth', 'Slave', '56456', '1');
INSERT INTO emp
VALUES ('8', 'Mart', 'Slave', '98', '3');

Here are my Queries:

SELECT * FROM emp;

SELECT * FROM emp 
  WHERE boss = (SELECT num FROM emp
              WHERE num = boss) AND num != boss;

SELECT e1.num,e1.name FROM emp e1
  WHERE
(SELECT e2.salary FROM emp e2 
  WHERE e2.boss = (SELECT e3.num FROM emp e3
                  WHERE e3.num = e3.boss) AND e2.num != e2.boss) < 98;  

So the first output prints out everything as expected. The second output prints out: Matthew, Roy and Marth as expected.

But the final output prints out one.

This is a practice test question I was given My goal is print the num and name of everyone working under the president who makes less than MIN of people who aren't working for the president.

I can calculate the MIN with the following code:

SELECT min(salary) FROM emp 
            WHERE boss != (SELECT num FROM emp
                        WHERE num = boss);  

Ideally I want to replace 98 in the previous query with this statement, but I decided it would be best if I broke it down and tried one thing at a time.