Shiny + SQLite - why is Shiny extremely slow?

We have developing a Shiny app for a few months now. But our Shiny app is extremely slow when it tries to load a huge amount of data. We even use the reactive function to reuse the data. But it is still slow as before when we request different sets of data.

We have a log file and it shows that Shiny takes at least 30.12672 seconds or 52.24799 seconds each time to load the data from our database.

What are the reasons make Shiny so slow? Is it the server or the database? What can we do to speed it up?

We are using SQLite database. Is it the reason that makes Shiny slow?

If so, what other types of database system should we go for to process huge amount of data sets? Cassandra? HBase? Apache Spark?

blob data not set as bitmap on imageview android

i have insert blob data in to sqlite and retrive from sqlite that time blob data not set as bitmap on imageview. give msg to fail to load. so please help me for my code. following are my code.

            bitmap = android.provider.MediaStore.Images.Media
             .getBitmap(cr, selectedImage);

            imageView.setImageBitmap(bitmap);


            ByteArrayOutputStream stream= new ByteArrayOutputStream();
            bitmap.compress(Bitmap.CompressFormat.JPEG, 100, stream);
            byte imginbyte[]=stream.toByteArray();
            byte temp[]=imginbyte;

            myDb.execSQL("INSERT INTO tableimage VALUES('"+temp+"');");
            Cursor c= myDb.rawQuery("SELECT * FROM tableimage", null);
            c.moveToLast();
            byte asd[]= c.getBlob(c.getColumnIndex("imgpath"));
            byte img[]=null;

          //  BitmapFactory.decodeByteArray(asd, 0, asd.length);
            ImageView iv=(ImageView)findViewById(R.id.img2);
            ImageView iv1=(ImageView)findViewById(R.id.img3);

            iv.setImageBitmap( BitmapFactory.decodeByteArray(asd, 0, asd.length));
            iv1.setImageBitmap( BitmapFactory.decodeByteArray(asd, 0, asd.length));

search for column with specific prefix in sqlite

assume that I have 1234. I want a query to select all columns that match:

1234

01234

001234

0001234

...

How can I do that in SQLite?

Thanks

How do i split date with a wher clause in SQLite?

I have a database that is a list of all transactions done by employees. The table contains the transaction ID, Employee_ID, and terminal. I need to return a single query that will list the number of transactions done in each section of the store. (i.e. terminals 1-4, 5-10). I need to look up how many transactions John Smith did on terminals 1-4 and how many he did on terminals 5-10.

Android SQLiteDatabase SELECT query take long time

I have about 1500 records in TABLE_CONTACT. My code to get contacts:

public ArrayList<SBContact> getAllContacts() {
    SQLiteDatabase database = dbHelper.getWritableDatabase();
    ArrayList<SBContact> mContacts = new ArrayList<SBContact>();
    String selectQuery = "SELECT  * FROM " + SBDatabaseHelper.TABLE_CONTACT;
    Cursor cursor = database.rawQuery(selectQuery, null);

    if (cursor.moveToFirst()) {
        do {
            SBContact result = new SBContact(cursor.getString(cursor.getColumnIndex(SBDatabaseHelper.CONTACT_NUMBER)),
                    cursor.getString(cursor.getColumnIndex(SBDatabaseHelper.CONTACT_NAME)), cursor.getInt(cursor.getColumnIndex(SBDatabaseHelper.EXITS_USER)));
            mContacts.add(result);
        } while (cursor.moveToNext());
    }

    return mContacts;
}

The problem is: The first time a run this method, it take about 15ms. If 5 seconds later, I run this method again, it take about 20 seconds.

In similar way, after the first call, 15s later, run method again, it take about 10 seconds. And after 2 minutes later, run this method again, it take about 15ms as the first time run.

In this time, no other thread make a read/write query to database.

I don't understand what the SQLiteDatabase do. Does it need time to release memory or do something?

Android SQLite query missing date

I have 2 tables one contains users and their data, the other contains payment transactions which are done but not limited to once per month. I'd like to query users who have not paid at least once from the day they were added into the system. My table users table looks like so.

idu     fname   lname   date_added
1       John    Doe     2012-01-01 08:00:00
2       Jane    Doe     2012-01-01 08:00:00
3       Mary    Jane    2012-01-01 08:00:00
4       John    Rancho  2012-01-01 08:00:00

My payments table looks like so

payment_id  uid payment_date
1               2       2012-01-07 08:00:00
2               3       2013-01-12 08:00:00
3               1       2012-02-01 08:00:00
4               2       2012-02-07 08:00:00
5               3       2013-02-12 08:00:00
6               1       2012-03-01 08:00:00
7               2       2015-04-08 08:00:00
8               1       2014-05-31 08:00:00
9               1       2014-07-31 08:00:00

I would like my results to look like the table shown below where unpaid times is the count of months not paid for and is greater than one.

IDU  Name      Unpaid Times
2   Jane Doe    3
3   Mary Jane   4

I did the query in PHP and MySQL in a crappy way though I get desired results. I want to transfer the code to Android and SQLite so an otimised query is highly required. My PHP/MySQL code is shown below.

$query = "SELECT idu,fname,lname,date_added,payment_date FROM water_users LEFT JOIN payments ON idu=sold_to GROUP BY (CASE WHEN MONTH(payment_date) IS NULL THEN idu ELSE MONTH(payment_date) END) ORDER BY DATE(payment_date) ASC";    
$result = $db->RunQueryForResults($query);
$defaulters = array();

while ($row = $result->fetch_assoc()) {                   
    $start_date = $row['date_added'];

    while (strtotime($start_date) < strtotime(getCurrentDate())) {
        $current_month = date("Y-m", strtotime($start_date));
        $payment_date = date("Y-m", strtotime($row['payment_date']));
        if ((strtotime($current_month) !== strtotime($payment_date)) && (strtotime($payment_date) <= strtotime($current_month))) {
            $defaulters[] = array(
                'idu' => $row['idu'],
                'name' => $row['fname'] . " " . $row['lname'],
                'defaulted_month' => date("M Y", strtotime($current_month))
            );
        }
        $start_date = date("Y-m-d H:i:s", strtotime("+30 day", strtotime($start_date)));
    }
}

I ported the php code to the java code below. Please advise thanks.

ArrayList<HashMap<String, String>> defaulters = new ArrayList<>();
        HashMap<Integer, HashMap<String, String>> water_users = new HashMap<>();

    String selectQuery = " SELECT "
            + Constants.ID_USER + ","
            + Constants.FNAME + ","
            + Constants.LNAME + ","
            + Constants.DATE_ADDED + " "              
            + " FROM " +
            Constants.TABLE_PREFIX + Constants.WATER_USERS_TABLE;

    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery(selectQuery, null);

    if (cursor.moveToFirst()) {
        do {
            java.util.Date dt = new java.util.Date();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String currentTime = sdf.format(dt);
            GregorianCalendar gcal = new GregorianCalendar();
            Date start = sdf.parse(cursor.getString(cursor.getColumnIndex(Constants.DATE_ADDED)));
            Date end = sdf.parse(currentTime);
            gcal.setTime(start);

            boolean exits = false;

            while (gcal.getTime().before(end) && !exits) {
                String str[] = sdf.format(gcal.getTime()).split("-");
                int year = Integer.parseInt(str[0]);
                int month = Integer.parseInt(str[1]);
                String sql = "SELECT COUNT(" + Constants.ID_SALE + ") AS total_transactions," + Constants.SOLD_TO + ", CAST(strftime('%m', " + Constants.SALE_DATE + ") AS INTEGER) month, CAST(strftime('%Y', " + Constants.SALE_DATE + ") AS INTEGER) year," + Constants.SALE_DATE
                        + " FROM  " + Constants.SALES_TABLE
                        + " WHERE  month=" + month
                        + " AND year=" + year
                        + " AND " + Constants.SOLD_TO + "=" + cursor.getString(cursor.getColumnIndex(Constants.ID_USER));
                Cursor cursor2 = db.rawQuery(sql, null);
                if (cursor2 != null) {
                    cursor2.moveToFirst();
                }

                if (!water_users.containsKey(cursor2.getInt(cursor2.getColumnIndex(Constants.SOLD_TO)))) {
                    HashMap<String, String> user = new HashMap<>();
                    user.put(Constants.IDU, cursor.getString(cursor.getColumnIndex(Constants.ID_USER)));
                    user.put(Constants.FNAME, cursor.getString(cursor.getColumnIndex(Constants.FNAME)));
                    user.put(Constants.LNAME, cursor.getString(cursor.getColumnIndex(Constants.LNAME)));
                    water_users.put(cursor2.getInt(cursor2.getColumnIndex(Constants.SOLD_TO)), user);
                    defaulters.add(user);
                } else {
                    exits = true;
                }

                gcal.add(Calendar.DAY_OF_MONTH, 1);
            }
            exits = false;

        } while (cursor.moveToNext());
    }

SQLite claims duplicate rows on insert whereas none can be found

I have a table in a SQLite database created with the code below. Note the compound primary key:

db.create_table(:person_hash) do
  Integer :person_id
  Bignum :hash // MD5 hash in hex stored as numeric: hash.to_i(16)
  primary_key [:person_id, :hash]
end

This table has some rows already:

puts db[:person_hash].where(:person_id => 285577).all
# {:person_id=>285577, :hash=>306607097659338192312932577746542919680}

Now, when I try to insert this:

db[:person_hash].insert({:person_id=>285577, :hash=>306607097659338206333361532286405644297})

I get this:

SQLite3::ConstraintException: columns person_id, hash are not unique (Sequel::DatabaseError)

If the row does not already exist in the table, how can it be a duplicate?

I tried inserting another hash for the same person ID instead, and it worked without problems.

can not reading from database in my code

I am new to android application development and I have written the following code to start a google map applicaton which reads some data from an sqlite databse. I considered reading database from a path as it has big data, but I encountered the error "Failed to open database '/data/data/http://ift.tt/1Iz6jlq'" in logcat.

Could you please help me finding the problem?

MainActivity.java

package com.mymap.www;
import java.text.DecimalFormat;
import java.util.List;
import com.google.android.gms.maps.CameraUpdateFactory;
import com.google.android.gms.maps.GoogleMap;
import com.google.android.gms.maps.GoogleMap.InfoWindowAdapter;
import com.google.android.gms.maps.GoogleMap.OnMapClickListener;
import com.google.android.gms.maps.MapFragment;
import com.google.android.gms.maps.MapView;
import com.google.android.gms.maps.model.BitmapDescriptorFactory;
import com.google.android.gms.maps.model.CameraPosition;
import com.google.android.gms.maps.model.LatLng;
import com.google.android.gms.maps.model.Marker;
import com.google.android.gms.maps.model.MarkerOptions;
import android.app.Dialog;
import android.database.DataSetObserver;
import android.database.SQLException;
import android.graphics.Typeface;
import android.os.Bundle;
import android.support.v7.app.ActionBarActivity;
import android.util.Log;
import android.view.Gravity;
import android.view.LayoutInflater;
import android.view.Menu;
import android.view.MenuItem;
import android.view.MotionEvent;
import android.view.View;
import android.view.ViewGroup;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ImageView;
import android.widget.ListAdapter;
import android.widget.ListView;
import android.widget.TextView;
import android.widget.Toast;

public class MainActivity extends ActionBarActivity {

private MapView mapView;
DatabaseHelper dataBase;
Typeface typeface;

private GoogleMap googleMap;

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

    try {
        // Loading map
        initilizeMap();
    } catch (Exception e) {
        e.printStackTrace();
    }

    // Changing map type
    googleMap.setMapType(GoogleMap.MAP_TYPE_NORMAL);

    // Showing / hiding your current location
    googleMap.setMyLocationEnabled(true);

    // Enable / Disable zooming controls
     googleMap.getUiSettings().setZoomControlsEnabled(true);

     // Enable / Disable my location button
     googleMap.getUiSettings().setMyLocationButtonEnabled(true);

     // Enable / Disable Compass icon
     googleMap.getUiSettings().setCompassEnabled(true);

     // Enable / Disable Rotate gesture
     googleMap.getUiSettings().setRotateGesturesEnabled(true);

     // Enable / Disable zooming functionality
     googleMap.getUiSettings().setZoomGesturesEnabled(true);

 // Setting a click event handler for the map
    googleMap.setOnMapClickListener(new OnMapClickListener() {

        @Override
        public void onMapClick(LatLng latLng) {

            // Creating a marker
            MarkerOptions markerOptions = new MarkerOptions();

            // Setting the position for the marker
            markerOptions.position(latLng);

            googleMap.setInfoWindowAdapter(new InfoWindowAdapter() {

                // Use default InfoWindow frame
                @Override
                public View getInfoWindow(Marker arg0) {             
                    return null;
                }

                // Defines the contents of the InfoWindow
                @Override
                public View getInfoContents(Marker arg0) {

                    // Getting view from the layout file info_window_layout
                    View v = getLayoutInflater().inflate(R.layout.info_window_layout, null);

                    // Getting the position from the marker
                    LatLng latLng = arg0.getPosition();

                    // Getting reference to the TextView to set latitude
                    TextView tvLat = (TextView) v.findViewById(R.id.tv_lat);

                    // Getting reference to the TextView to set longitude
                    TextView tvLng = (TextView) v.findViewById(R.id.tv_lng);

                    double lat;
                    double lng;

                    lat =Double.parseDouble(new DecimalFormat("##.########").format(latLng.latitude));
                    lng =Double.parseDouble(new DecimalFormat("##.########").format(latLng.longitude));

                    dataBase = DatabaseHelper.instance(lat,lng);

                    // Setting the latitude
                    tvLat.setText("Latitude:" + lat);

                    // Setting the longitude
                    tvLng.setText("Longitude:"+ lng);


                    // Returning the view containing InfoWindow contents
                    return v;
                }
            });
            // Clears the previously touched position
            googleMap.clear();

            // Animating to the touched position
            googleMap.animateCamera(CameraUpdateFactory.newLatLng(latLng));

            // Placing a marker on the touched position
            googleMap.addMarker(markerOptions);

        }
    });    

     double latitude = 17.385044;
    double longitude = 78.486671;

}

private void initilizeMap() {
    if (googleMap == null) {
        googleMap = ((MapFragment) getFragmentManager().findFragmentById(
                R.id.map)).getMap();

        // check if map is created successfully or not
        if (googleMap == null) {
            Toast.makeText(getApplicationContext(),
                    "Sorry! unable to create maps", Toast.LENGTH_SHORT)
                    .show();
        }
    }
}

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

@Override
public boolean onOptionsItemSelected(MenuItem item) {
    int id = item.getItemId();
    if (id == R.id.action_settings) {
        return true;
    }
    return super.onOptionsItemSelected(item);
}
}

ApplicationContentProvider.java

package com.mymap.www;

import android.app.Application;
import android.content.Context;
public class ApplicationContextProvider extends Application {
    /**
     * Keeps a reference of the application context
     */
    private static Context sContext;
    @Override
    public void onCreate() {
        super.onCreate();
        sContext = getApplicationContext();
    }
    /**
     * Returns the application context
     *
     * @return application context
     */

    public static Context getContext() {
        return sContext;
    }
}

DatabaseHelper.java

package com.mymap.www;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.widget.TextView;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

public class DatabaseHelper extends SQLiteOpenHelper {
    SQLiteDatabase mDataBase;
    static double latitude;
    static double longitude;
    int count;
    // The Android's default system path of your application database.
    //data/data/ and /databases remain the same always. The one that must be changed is com.example which represents
    //the MAIN package of your project
    private static String DB_PATH = "/data/data/http://ift.tt/1Ud2FS8";

    //the name of your database
    private static String DB_NAME = "database.db";
    private static final String SQLITE_TABLE = "data91";
    private static final String TAG = "ConcentrationDbAdapter";
    private static DatabaseHelper sInstance = null;
    // database version
    private static final int DATABASE_VERSION = 1;

    /**
     * Constructor Takes and keeps a reference of the passed context in order to
     * access to the application assets and resources.
     */
    private DatabaseHelper() { 
        super(ApplicationContextProvider.getContext(), DB_PATH + DB_NAME, null, DATABASE_VERSION);
        try{
            createDataBase();
        }
        catch (IOException e){
            throw new Error("Error copying database");
        } 
        try {
            createDataBase();
            openDataBase();
        } catch (IOException e) {
        e.printStackTrace();
    }

}
/**
 * Singleton for DataBase
 *
 * @return singleton instance
 */
public static DatabaseHelper instance(double lat, double lng) {
    latitude = lat;
    longitude = lng;

    if (sInstance == null) {
        sInstance = new DatabaseHelper();
    }

    return sInstance;
}
/**
 * Creates a empty database on the system and rewrites it with your own
 * database.
 *
 * @throws java.io.IOException io exception
 */
private void createDataBase() throws IOException {
    boolean dbExist = checkDataBase();
    if (dbExist) {
        // do nothing - database already exist
    } else {
        // By calling this method an empty database will be created into
        // the default system path
        // of your application so we are gonna be able to overwrite that
        // database with our database.
        this.getReadableDatabase();
        try {
            copyDataBase();
        } catch (IOException e) {
            throw new Error("Error copying database");
        }
    }
}
/**
 * Check if the database already exist to avoid re-copying the file each
 * time you open the application.
 *
 * @return true if it exists, false if it doesn't
 */
private boolean checkDataBase() {
    SQLiteDatabase checkDB = null;
    try {
        String myPath = DB_PATH + DB_NAME;
        checkDB = SQLiteDatabase.openDatabase(myPath, null,
                SQLiteDatabase.OPEN_READONLY);
    } catch (SQLiteException e) {
        // database doesn't exist yet.
        throw new Error("database doesn't exist yet");
    }
    if (checkDB != null) {
        checkDB.close();
    }
    return checkDB != null;
}
/**
 * Copies your database from your local assets-folder to the just created
 * empty database in the system folder, from where it can be accessed and
 * handled. This is done by transfering bytestream.
 *
 * @throws java.io.IOException io exception
 */
public void copyDataBase() throws IOException {
    // Open your local db as the input stream
    InputStream myInput = ApplicationContextProvider.getContext().getAssets().open(DB_NAME);
    // Path to the just created empty db
    String outFileName = DB_PATH + DB_NAME;
    // Open the empty db as the output stream
    OutputStream myOutput = new FileOutputStream(outFileName);
    // transfer bytes from the inputfile to the outputfile
    byte[] buffer = new byte[1024];
    int length;
    while ((length = myInput.read(buffer)) > 0) {
        myOutput.write(buffer, 0, length);
    }
    // Close the streams
    myOutput.flush();
    myOutput.close();
    myInput.close();
}
private void openDataBase() throws SQLException {
    // Open the database
    String myPath = DB_PATH + DB_NAME;
    mDataBase = SQLiteDatabase.openDatabase(myPath, null,
            SQLiteDatabase.OPEN_READWRITE);
}
/**
 * Select method
 *
 * @param query select query
 * @return - Cursor with the results
 * @throws android.database.SQLException sql exception
 */
public Cursor select(String query) throws SQLException {
    return mDataBase.rawQuery(query, null);
}
/**
 * Insert method
 *
 * @param table  - name of the table
 * @param values values to insert
 * @throws android.database.SQLException sql exception
 */
public void insert(String table, ContentValues values) throws SQLException {
    mDataBase.insert(table, null, values);
}
/**
 * Delete method
 *
 * @param table - table name
 * @param where WHERE clause, if pass null, all the rows will be deleted
 * @throws android.database.SQLException sql exception
 */
public void delete(String table, String where) throws SQLException {
    mDataBase.delete(table, where, null);
}
/**
 * Update method
 *
 * @param table  - table name
 * @param values - values to update
 * @param where  - WHERE clause, if pass null, all rows will be updated
 */
public void update(String table, ContentValues values, String where) {
    mDataBase.update(table, values, where, null);
}
/**
 * Let you make a raw query
 *
 * @param command - the sql comand you want to run
 */
public void sqlCommand(String command) {
    mDataBase.execSQL(command);
}
@Override
public synchronized void close() {
    if (mDataBase != null)
        mDataBase.close();
    super.close();
}
@Override
public void onCreate(SQLiteDatabase db) {
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}

}

activity_main.xml

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://ift.tt/nIICcg"
android:layout_width="fill_parent"
android:layout_height="fill_parent" >

    <fragment
        android:id="@+id/map"
        android:name="com.google.android.gms.maps.MapFragment"
        android:layout_width="match_parent"
        android:layout_height="match_parent"/>

</RelativeLayout>

info_window_layout.xml

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

    <TextView
        android:id="@+id/tv_lat"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content" />

    <TextView
        android:id="@+id/tv_lng"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content" />

    <TextView
        android:id="@+id/tv_date"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content" />

    <TextView
        android:id="@+id/tv_co"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content" />

    <TextView
        android:id="@+id/tv_no2"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content" />

    <TextView
        android:id="@+id/tv_no"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content" />

    <TextView
        android:id="@+id/tv_nox"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content" />

    <TextView
        android:id="@+id/tv_o3"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content" />

    <TextView
        android:id="@+id/tv_pm10"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content" />

    <TextView
        android:id="@+id/tv_pm2_5"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content" />

    <TextView
        android:id="@+id/tv_so2"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content" />

    <TextView
        android:id="@+id/tv_aqi"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content" />

    <TextView
        android:id="@+id/tv_status"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content" />

</LinearLayout>

AndroidManifest.xml

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://ift.tt/nIICcg"
package="com.airpollutionmap.www"
android:versionCode="1"
android:versionName="1.0" >

    <uses-sdk
        android:minSdkVersion="11"
        android:targetSdkVersion="21" />

    <uses-permission android:name="android.permission.INTERNET"/>
    <uses-permission android:name="android.permission.ACCESS_NETWORK_STATE"/>
    <uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE"/>
    <!-- The following two permissions are not required to use
     Google Maps Android API v2, but are recommended. -->
    <uses-permission
        android:name="android.permission.ACCESS_COARSE_LOCATION"/>
    <uses-permission
        android:name="android.permission.ACCESS_FINE_LOCATION"/>
    <uses-permission
        android:name="com.mymap.http://ift.tt/1Iz6jls" />

    <uses-feature
        android:glEsVersion="0x00020000"
        android:required="true"/>

    <application
        android:allowBackup="true"
        android:icon="@drawable/ic_launcher"
        android:label="@string/app_name"
        android:theme="@style/AppTheme" >

        <meta-data
            android:name="com.google.android.geo.API_KEY"
            android:value="AIzaSyC1BF_BwuWL_iiXx6VCXF6zDFEJk9_U33g"/>

        <meta-data
            android:name="com.google.android.gms.version"
            android:value="@integer/google_play_services_version" />

        <activity
            android:name=".MainActivity"
            android:label="@string/app_name" >
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />
                <category
                    android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
    </application>

</manifest>

SQLite intermittently querying successfully between two dates

I am doing some select statements to get some data from SQLite databases.

The tables have Start and End dates, the dates are in the format of "31/07/2015 3:00:42 PM". The strange thing is, when I execute the reader, and check it immediately after, the results view will have "{"String was not recognized as a valid DateTime."}"

The thing is that it DID successfully query between the two dates I gave it, even though it apparently doesn't think so.

Now where I became even more confused is when trying to query a second table that also has two Date columns, the query throws the same error, but this time it actually DOESN'T work.

So basically SQLite is querying two tables using a date range, thinks that it failed both times, but actually succeeds on one of the tables.

The queries look like:

SELECT * FROM ShiftDetailLog WHERE StartTime >='31/07/2015 2:00:00 PM' AND StartTime <='31/07/2015 4:00:00 PM' ORDER BY StartTime asc   <<<(This query WORKS)

and

SELECT * FROM DowntimeLog WHERE StartTime >='31/07/2015 1:00:00 PM' AND StartTime <='31/07/2015 4:00:00 PM' ORDER BY StartTime asc    <<<(This query DOES NOT work)

Both StartTime and EndTime are both 'datetime' columns in SQLite in both tables.

Edit: Also, when I try to do something like:

string something = reader[0].ToString();

it actually errors saying "String was not recognized as a valid DateTime"... this is an error thrown by mscorlib...

I'm not referencing a datetime object, or telling it to convert anything to a datetime object...

Android: Access database from activity or fragment?

I have a MainActivity with three separate fragments. I need to access data from my SQLite database in each of these fragments.

The data object MyObject contains primitive types along with a list of OtherObject objects.

So lets say we have three fragments, FragmentA, FragmentB and FragmentC.

FragmentB creates MyObject objects and saves them into the database. Upon saving, FragmentC is opened and populated with the MyObject data.

FragmentA lists all MyObject objects and upon clicking on an item from the list, will open FragmentC populated with the chosen MyObject.

What is the correct implementation?

Should the activity control all the database access and pass MyObject to the corresponding fragment?

Should each fragment access the database each time to access the data for itself?

Should FragmentA and FragmentB read in the data and pass the corresponding MyObject to FragmentC?

For the case of passing data from fragment -> activity -> fragment or activity -> fragment, which method would fit best:

1. Parcelable
2. Intent
3. Interface (via the activity)

The use of parcelable seems difficult as MyObject contains a list of OtherObject, and all the data can't be decomposed and transferred in an Intent very easily.

This link shows the use of an Interface to transfer data from the fragment to the activity, with the Fragment declaring the interface which is implemented in the activity. To transfer the objects from the activity to the fragment, do you declare another interface in the activity and implement it in the fragment?

I haven't been able to find any straight forward advice.

Thanks for any help

How can I implement SQL and SQLite simultaneously wihtout big if statement

HI lets say I have a C# code

private const string INSERT = "INSERT INTO Person VALUES (@FirstName, @LastName)";

public static DbCommand INSERTCOMMAND(IPerson person)
{
    DbCommand command = null;
    var sqlParams = new List<DbParameter>();

    if(SQLManagerFactory.SQLManager is SQLServerManager)
    {
        command = new SqlCommand(INSERT);
        sqlParams.Add(new SqlParameter("@FirstName", person.FirstName);
        sqlParams.Add(new SqlParameter("@LastName", person.LastName);
    }
    else // SQLiteManager
    {
        command = new SQLiteCommand(INSERT);
        sqlParams.Add(new SQLiteParameter("@FirstName", person.FirstName);
        sqlParams.Add(new SQLiteParameter("@LastName", person.LastName);
    }

    command.Parameters.AddRange(sqlParams.ToArray());
    return command;
}

Which is working perfectly fine. Of course in my production code, it is quite bigger and has a lot more location that does the similar things for different commands.

My question is that is there way to make this shorter? I do not wish to copy and paste code which essentially does the same thing except for calling the different constructors.

Thank you very much in advance.

Insufficient parameters supplied to the command

In my query in Sqlite database I got the error of insufficient parameters supplied, like my title topic. I really don't understand what is wrong in my code:

SQLiteCommand command = new SQLiteCommand(@"INSERT INTO Fixtures (data, matchday, homeTeamName, awayTeamName, 
                goalsHomeTeam, goalsAwayTeam, status, result, link_self, link_soccerSeason, link_homeTeam, link_awayTeam, caption) 
                VALUES (@data, @matchday, @homeTeamName, @awayTeamName, @goalsHomeTeam, @goalsAwayTeam, @status, @result, @link_self, 
                @link_soccerSeason, @link_homeTeam, @link_awayTeam, @caption)", Database.m_dbConnection);

this is the line that cause the exception:

 command.ExecuteNonQuery();

UPDATE FULL CODE

 foreach (var fixture in obj.fixtures)
            {
                SQLiteCommand command = new SQLiteCommand(@"INSERT INTO Fixtures (data, matchday, homeTeamName, awayTeamName, 
                goalsHomeTeam, goalsAwayTeam, status, result, link_self, link_soccerSeason, link_homeTeam, link_awayTeam, caption) 
                VALUES (@data, @matchday, @homeTeamName, @awayTeamName, @goalsHomeTeam, @goalsAwayTeam, @status, @result, @link_self, 
                @link_soccerSeason, @link_homeTeam, @link_awayTeam, @caption)", Database.m_dbConnection);
                try
                {
                    command.ExecuteNonQuery();

                    command.Parameters.Add("@data", DbType.Date).Value = fixture.date;
                    command.Parameters.Add("@matchday", DbType.String).Value = fixture.matchday;
                    command.Parameters.Add("@homeTeamName", DbType.String).Value = fixture.homeTeamName;
                    command.Parameters.Add("@awayTeamName", DbType.String).Value = fixture.awayTeamName;
                    command.Parameters.Add("@goalsHomeTeam", DbType.String).Value = fixture.result.goalsHomeTeam;
                    command.Parameters.Add("@goalsAwayTeam", DbType.String).Value = fixture.result.goalsAwayTeam;
                    command.Parameters.Add("@status", DbType.String).Value = fixture.status;
                    command.Parameters.Add("@result", DbType.String).Value = fixture.result.goalsHomeTeam + " - " + fixture.result.goalsAwayTeam;
                    command.Parameters.Add("@link_self", DbType.String).Value = fixture._links.self.href;
                    command.Parameters.Add("@link_soccerSeason", DbType.String).Value = fixture._links.soccerseason.href;
                    command.Parameters.Add("@link_homeTeam", DbType.String).Value = fixture._links.homeTeam.href;
                    command.Parameters.Add("@link_awayTeam", DbType.String).Value = fixture._links.awayTeam.href;
                    command.Parameters.Add("@caption", DbType.String).Value = campionato;

                }
                catch (Exception ex)
                {
                    Console.WriteLine("Attenzione: " + ex.ToString());
                }

python sqlite executemany statement error: ValueError: parameters are of unsupported type

What I want to do seems like it should be pretty straightforward but I just can't get past the errors I'm getting. Basically, I create a list, create a database table and then want to insert the elements of the list into the table. Here's what I've got:

F_wheel_data = [1,3,1,3,1,3,1,3,1,3,2,1,3,1,3,1,3,1,3,1,3,4]
curs.execute('CREATE TABLE F_wheel (url_num INTEGER NOT NULL)')
curs.executemany('INSERT INTO F_wheel VALUES (?)', F_wheel_data)

And the error I get is:

curs.executemany('INSERT INTO F_wheel VALUES (?)', F_wheel_data)
ValueError: parameters are of unsupported type

What gives?

Java swing , Error Retrieving Image From Database using Eclipse

Error: java.lang.NullPointerException

at javax.swing.ImageIcon.(Unknown Source)

at Show$2.actionPerformed(Show.java:79)

I think the problem is occuring in BufferedImage , is there any other option to retrieve image(Blob) form Database . I am using Sqlite database .

Here Goes The Code :

public class Show extends JFrame {

private JPanel contentPane;
private JTextField id;
BufferedImage bufImg = null;
JLabel img=null;
InputStream in=null;
ImageIcon imgs=null;
/**
 * Launch the application.
 */
public static void main(String[] args) {
    EventQueue.invokeLater(new Runnable() {
        public void run() {
            try {
                Show frame = new Show();
                frame.setVisible(true);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    });
}

/**
 * Create the frame.
 */
Connection con=null;
public Show() {
    con=dB.Connect();

    setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
    setBounds(100, 100, 588, 432);
    contentPane = new JPanel();
    contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));
    setContentPane(contentPane);
    contentPane.setLayout(null);

    id = new JTextField();
    id.setBounds(158, 23, 86, 20);
    contentPane.add(id);
    id.setColumns(10);

    JButton show = new JButton("New button");
    show.addActionListener(new ActionListener() {
        public void actionPerformed(ActionEvent e) {
            try{
                //String iid=null;
                //iid="10";
                //iid=id.getText().toString();
                String q="select image from image where id='"+id.getText()+"'";

                PreparedStatement ps=con.prepareStatement(q);
                //ps.setString(1, id.getText().trim());
                //ps.executeQuery();
                ResultSet rs=ps.executeQuery(); 

                while (rs.next()) {
                    in= rs.getBinaryStream("image");
                    bufImg = ImageIO.read(in);
                    img.setIcon(new ImageIcon(bufImg));// Console shows the error is in this line 
                }
                rs.close();
                ps.close();

                }catch(Exception c)
                {
                    c.printStackTrace();
                }
        }
    });
    show.setBounds(302, 22, 89, 23);
    contentPane.add(show);

    img = new JLabel("");
    img.setBounds(151, 99, 325, 284);
    contentPane.add(img);
}

}

Android SQLiteDatabase Insert Data to Column?

I am new to this Android, I want to know how to insert data into column? , like messaging app , when you click contact/people , it will display your data from that people , and that data cant be displayed to other,can u guys give me some link or keywords to search,sorry for my bad English., Thanks in Advance.

Immediate isolation level sqliite Python

What does the immediate isolation level in Python sqlite3 correspond to? As far as i can tell, what it does is not really documented in the documentation - http://ift.tt/1eGP2KX

Would i be right in assuming it places a reserved lock on the database? [http://ift.tt/1gtXDlS]

FTS4 sqlite MATCH not working

i tried several methods from here:

SQLite FTS example doesn't work

and here -> Full text search example in Android (best tutorial so far i think)

but my search return just 0 results!

what i've tried:

   String key = "a";
        Cursor c = db.query(true, "texts_virtual",
                new String[]{"id","title_normalized"},
                "title_normalized MATCH '"+key+"'",
                null, null, null, null, null);

= 0 Results;

 String query = "a";
    String[] params = {"%" +query+ "%"};

    Cursor c = db.rawQuery("SELECT * FROM texts_virtual WHERE title_normalized MATCH ?", params);

= 0 Results too

i know that the virtual table is correctly working because i can do this:

String queryText = "a"; //here i test other texts and they worked too
        String query = "select * from texts_virtual where title_normalized like ? order by number";
        String[] params = {"%" + queryText + "%"};
        Cursor c = db.rawQuery(query, params);

so this prove that the texts_virtual is working, what is not working are the queries, but i dont know why, not error, nothing, just 0 results

and after i make it work, im planning to use multiple terms search in 2 columns

user type "WordA WordB WordC"

it search for each word in the 2columns and return the results, but this if for a future task....

Group select data from table with Same lenght

my price table is like this:

i want to get query from price of two city in the same date from one product for comparing price. but in some time may be a city have more price on specific date, and the query of two city is not same length for comparing.

How to restrict two city price to just have get price if that product in two city is exists in specific time? like This:

Missing ADO.NET data source for sqlite in Visual Studio 2015

I've looked at other older threads on this and none of the answers have helped me. I'm using Visual Studio 2015. I grabbed the latest sqlite 1.0.97.0 and Entity Framework 6.1.3 and the ADO Data source is not listed as a option for the ADO.NET Entity Data Model. I've tried a few different versions of both entity framework and sqlite. I also tried a few versions of the .net framework (4.5 and 4.5.1) - Curious if anyone has any suggestions or has seen this.

Error : java.lang.NullPointerException when retrieve Image(Blob) data from SQLite database [duplicate]

This question already has an answer here:

• What is a Null Pointer Exception, and how do I fix it? 12 answers

I am trying to retrieve Image(Blob) Data from database But it gives my null pointer exception . What is wrong with it ? Here is the Code

    JButton show = new JButton("New button");
    show.addActionListener(new ActionListener() {
        public void actionPerformed(ActionEvent e) {
            try{
                String iid="10";
                //iid=id.getText().toString();
                String q="select * from image where id='"+iid+"'";

                PreparedStatement ps=con.prepareStatement(q);
                //ps.setString(1, id.getText());
                ps.executeQuery();
                ResultSet rs=ps.executeQuery();         
                while (rs.next()) {
                    InputStream in = rs.getBinaryStream("image");
                    bufImg = ImageIO.read(in);
                    img.setIcon(new ImageIcon (bufImg));
                }
                rs.close();
                ps.close();


                }catch(Exception c)
                {
                    c.printStackTrace();
                }
        }
    });

c# application missing system.data.SQLite dll when publish

i've a problem in visual studio 2012 Desktop when i try to publish an application written in c#, in fact when i click on the setup.exe produced after publishing it says that it is missing system.data.SQLite even if in the references i set to copy locally.

Thank you!

Getting java.lang.NullPointerException in getView of Adapter class while running the App

I am trying to display rows from my table in SQLite database in custom ListView for that i created an ProductAdapter class . I am getting an error while running the App Following is the error

07-31 14:31:42.548    1968-1968/? E/AndroidRuntime﹕ FATAL EXCEPTION: main
    java.lang.NullPointerException
            at com.sling.freshecomart.ProductAdapter.getView(ProductAdapter.java:54)
            at android.widget.AbsListView.obtainView(AbsListView.java:2267)
            at android.widget.ListView.measureHeightOfChildren(ListView.java:1244)
            at android.widget.ListView.onMeasure(ListView.java:1156)
            at android.view.View.measure(View.java:15172)
            at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:4814)
            at android.widget.LinearLayout.measureChildBeforeLayout(LinearLayout.java:1390)
            at android.widget.LinearLayout.measureVertical(LinearLayout.java:681)
            at android.widget.LinearLayout.onMeasure(LinearLayout.java:574)
            at android.view.View.measure(View.java:15172)
            at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:4814)
            at android.widget.LinearLayout.measureChildBeforeLayout(LinearLayout.java:1390)
            at android.widget.LinearLayout.measureVertical(LinearLayout.java:681)
            at android.widget.LinearLayout.onMeasure(LinearLayout.java:574)
            at android.view.View.measure(View.java:15172)
            at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:4814)
            at android.widget.LinearLayout.measureChildBeforeLayout(LinearLayout.java:1390)
            at android.widget.LinearLayout.measureVertical(LinearLayout.java:681)
            at android.widget.LinearLayout.onMeasure(LinearLayout.java:574)
            at android.view.View.measure(View.java:15172)
            at android.widget.RelativeLayout.measureChildHorizontal(RelativeLayout.java:617)
            at android.widget.RelativeLayout.onMeasure(RelativeLayout.java:399)
            at android.view.View.measure(View.java:15172)
            at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:4814)
            at android.widget.FrameLayout.onMeasure(FrameLayout.java:310)
            at android.support.v7.internal.widget.ContentFrameLayout.onMeasure(ContentFrameLayout.java:124)
            at android.view.View.measure(View.java:15172)
            at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:4814)
            at android.support.v7.internal.widget.ActionBarOverlayLayout.onMeasure(ActionBarOverlayLayout.java:444)
            at android.view.View.measure(View.java:15172)
            at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:4814)
            at android.widget.FrameLayout.onMeasure(FrameLayout.java:310)
            at android.view.View.measure(View.java:15172)
            at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:4814)
            at android.widget.LinearLayout.measureChildBeforeLayout(LinearLayout.java:1390)
            at android.widget.LinearLayout.measureVertical(LinearLayout.java:681)
            at android.widget.LinearLayout.onMeasure(LinearLayout.java:574)
            at android.view.View.measure(View.java:15172)
            at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:4814)
            at android.widget.FrameLayout.onMeasure(FrameLayout.java:310)
            at com.android.internal.policy.impl.PhoneWindow$DecorView.onMeasure(PhoneWindow.java:2148)
            at android.view.View.measure(View.java:15172)
            at android.view.ViewRootImpl.performMeasure(ViewRootImpl.java:1848)
            at android.view.ViewRootImpl.measureHierarchy(ViewRootImpl.java:1100)
            at android.view.ViewRootImpl.performTraversals(ViewRootImpl.java:1273)
            at android.view.ViewRootImpl.doTraversal(ViewRootImpl.java:998)
            at android.view.ViewRootImpl$TraversalRunnable.run(ViewRootImpl.java:4212)
            at android.view.Choreographer$CallbackRecord.run(Choreographer.java:725)
            at android.view.Choreographer.doCallbacks(Choreographer.java:555)
            at android.view.Choreographer.doFrame(Choreographer.java:525)
            at android.view.Choreographer$FrameDisplayEventReceiver.run(Choreographer.java:711)
            at android.os.Handler.handleCallback(Handler.java:615)
            at android.os.Handler.dispatchMessage(Handler.java:92)
            at android.os.Looper.loop(Looper.java:137)
            at android.app.ActivityThread.main(ActivityThread.java:4745)
            at java.lang.reflect.Method.invokeNative(Native Method)
            at java.lang.reflect.Method.invoke(Method.java:511)
            at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:786)
            at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553)
            at dalvik.system.NativeStart.main(Native Method)

following is the line ProductAdapter.java:54

convertView = mInflater.inflate(R.layout.item,null);

ProductAdapter class is

public class ProductAdapter extends BaseAdapter {
    private List<com.sling.freshecomart.Product> mProductList;
    private LayoutInflater mInflater;
    private Activity activity;
    private LayoutInflater inflater=null;
    public ProductAdapter(List<Product> list, Activity a) {
        this.mProductList = list;
        this.activity=a;


        inflater = (LayoutInflater) this.activity.getSystemService(Context.LAYOUT_INFLATER_SERVICE);
    }


    @Override
    public int getCount() {
        return mProductList.size();
    }

    @Override
    public Object getItem(int position) {
        return mProductList.get(position);
    }

    @Override
    public long getItemId(int position) {
        return position;
    }

    @Override
    public View getView(int position, View convertView, ViewGroup parent) {
        final ViewItem item;

        if (convertView == null) {
            convertView = mInflater.inflate(R.layout.item,null);
            item = new ViewItem();

            item.post_id=(TextView)convertView.findViewById(R.id.post_id);
            item.Name = (TextView)convertView.findViewById(R.id.Name);
            item.Weight = (TextView)convertView.findViewById(R.id.Weight);
            item.Description = (TextView)convertView.findViewById(R.id.Description);
            item.saveValue = (TextView)convertView.findViewById(R.id.saveValue);
            item.mrpValue = (TextView)convertView.findViewById(R.id.mrpValue);
            item.Price = (TextView)convertView.findViewById(R.id.Price);

            convertView.setTag(item);
        } else {
            item = (ViewItem) convertView.getTag();
        }

        com.sling.freshecomart.Product cartProduct = mProductList.get(position);

        item.Name.setText(cartProduct.Name);
        item.Weight.setText(cartProduct.Weight);
        item.Description.setText(cartProduct.Description);
        item.mrpValue.setText(cartProduct.mrpValue);
        item.saveValue.setText(cartProduct.saveValue);
        item.Price.setText(cartProduct.Price);
        return convertView;
    }

    private class ViewItem {
        private TextView post_id,Name,Weight,Description,mrpValue,saveValue,Price;
 }
}

Product class is

public class Product {
    public String post_id,Name,Weight,Description;
    public int mrpValue,saveValue,Price;
    public Drawable productImage;
    public boolean selected;

    public Product(String Name,String Wieght,String Description,int mrpValue,int saveValue,int Price) {
        this.Name = Name;
        this.Weight=Wieght;
        this.Description = Description;
        this.mrpValue=mrpValue;
        this.saveValue=saveValue;
        this.Price = Price;
    }
}

oncreate() of The activity where the list should be displayed

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

        cartDB= new SQLiteHandler(this);
        Cursor res= cartDB.getCartDetails();

        ListView lv_cart = (ListView) findViewById(R.id.ListViewCart);

        List<Product> cartList = new ArrayList<>();
        int NoOfItemsInCart = res.getCount();

        if(res.getCount()==0){
            showMessage("Error","No rows Found");
        }else {
            res.moveToFirst();
            while (NoOfItemsInCart != 0) {

                res.getString(0);
                String CName = res.getString(1);
                String CWeight = res.getString(2);
                String CDescription = res.getString(3);
                int CmrpValue = res.getInt(4);
                int CsaveValue = res.getInt(5);
                int CPrice = res.getInt(6);
                cartList.add(new Product(CName, CWeight, CDescription, CmrpValue, CsaveValue, CPrice));
                res.moveToNext();
                NoOfItemsInCart = NoOfItemsInCart - 1;
            }
            ProductAdapter adapter = new ProductAdapter(cartList, this);
            lv_cart.setAdapter(adapter);
        }

Please help and Thanks in Advance

SQL - update main table using temp table

I have a question about SQL, especially SQLite3. I have two tables, let's name them main_table and temp_table. These tables are based on the same relational schema so they have the same columns but different rows (values).

Now what I want to do:

For each row of the main_table I want to replace it if there is a row in a temp_table with the same ID. Otherwise I want to keep the old row in the table.

I was thinking about using some joins but it does not provides the thing I want.

Would you give me an advice?

EDIT: ADITIONAL INFO:

I would like to avoid writing all columns because those tables conains tens of attributes.

how to pull data file from file explorer in eclipse

I am using Eclipse LUNA" and working on app.I have to pull my database file from data>data folder but when i click on data folder nothing happens nor any project is shown there.Using preview screen Nexus S. Kindly help me as soon as possible. I have seen many answers here but they are for mobile devices while i have to pull database file from eclipse.

while on IDataReader.Read doesn't work with yield return but foreach on reader does

This is a commonly seen ADO.NET pattern to retrieve data from database using a data reader, but strangely doesn't work.

Doesn't work:

public static IEnumerable<IDataRecord> SelectDataRecord<T>(string query, string connString)
                                                          where T : IDbConnection, new()
{
    using (var conn = new T())
    {
        using (var cmd = conn.CreateCommand())
        {
            cmd.CommandText = query;
            cmd.Connection.ConnectionString = connString;

            conn.Open();
            using (var reader = (DbDataReader)cmd.ExecuteReader())
            {
                // the main part
                while (reader.Read())
                {
                    yield return (IDataRecord)reader;
                }
            }
        }
    }

This does work:

public static IEnumerable<IDataRecord> SelectDataRecord<T>(string query, string connString)
                                                          where T : IDbConnection, new()
{
    using (var conn = new T())
    {
        using (var cmd = conn.CreateCommand())
        {
            cmd.CommandText = query;
            cmd.Connection.ConnectionString = connString;

            conn.Open();
            using (var reader = (DbDataReader)cmd.ExecuteReader())
            {
                // the main part
                foreach (var item in reader.Cast<IDataRecord>())
                {
                    yield return item;
                }
            }
        }
    }

The only relevant change I see is that in the first code the iterator is returned from while loop while in the second it is returned from a foreach loop.

I call it like:

var result = SelectDataRecord<SQLiteConnection>(query, connString).ToList();

I tried with SQLite .NET connector as well as MySQL connector. The result is the same, i.e. first approach fails, second one succeeds.

Exception

SQLite

An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.SQLite.dll. Additional information: No current row

MySQL

An unhandled exception of type 'System.Exception' occurred in MySql.Data.dll. Additional information: No current query in data reader

Is it because of the implementation differences between reader.Read and reader.GetEnumerator in the specific ADO.NET connectors? I couldn't see any noticeable difference when I checked the source of System.Data.SQLite project, GetEnumerator calls Read internally. I am assuming ideally in both cases the yield keyword prevents eager execution of the method and the loops have to be executed only once the enumerator is enumerated externally.

Time consumption of SQLite GROYP queries, Ruby on Rails

I am a beginner in Sqlite3 and Ruby on Rails.

I would like to ask about the expected time of a GROUP query.

The truth is that I have a query with 10 conditions, retrieving data by that takes 166 ms, which is not a problem.

However, for the same query when I have the query and group by condition it takes 15358 ms or 4362 ms.

Are these number expected? Is there a way to improve the time consuming?

How do I get a Sqlite Table attribute from a Class?

I would like to get a Sqlite Table attribute from my class.

I have a method to check if a table exists from here:

var info = database.Connection.GetTableInfo(typeof(Customer).Name);
if (info.Any())
{
    //do stuff with table
}

where Customer is:

[Table("Customer")]
public class Customer
{
  //class stuff
}

Now my method will work fine as it is but I would like to link it to the Table attribute rather than the class name incase I change the table name in future.

How do I go about getting my Table attribute from my class?

Ps. I am using a PCL (Portable Class Library)

How to prepare sql statements and bind parameters?

Unfortunatelly, the documentation completely lacks examples (what is really strange), as if it assumes all its readers to be good programmers. Whereas, I'm quite new to C++ and can not really figure out from the documentation how to really prepare and execute statements. I love the way how it is implemented in PDO for PHP. Normally, I just do it like this:

$s = $db->prepare("SELECT id FROM mytable WHERE id = :id");
$s->bindParam(':id', $id);
$s->execute();

or do it using ? tokens:

 $data = array();
 $data[] = 1;
 $data[] = 2;
 $s = $db->prepare("SELECT id FROM mytable WHERE id = ? or id = ?");
 $s->execute($data);

Now, I have C++ and sqlite3.h in my hands. At this moment, I know just how to connect to database - I do it and get no errors:

sqlite3 * conn;
int rc = sqlite3_open(db_name, &conn);

Please, give some instructions (with clear tiny examples) about how to implement similar things that PDO does in PHP - prepare a statement using named arguments and using ? tokens.

How to make setup file of an application with sqlite database in C#

My application uses Sqlite Database. I want to deploy it to another machine. I want to make the setup in a way so that I can include my database with it. What should I put in the connection string and where can I drop the database in my project before making the setup file.

Thanks in advance.

How copy String to String[] in android

I have String having data like

String myString = 10,20,30,40,50,60;

all i want to convert it into String[] to use these value in SQLite for

db.delete(TABLE_NAME, myString ,columnName);

But db.delete requires String[] So i want to convert it but it's value is becoming something rubbish and get error at time of delete those Id's from My_Database. So if you have any Smart Answer for that please share it.

My Required Data is String[] myArray = {10, 20, 30, 40, 50, 60};

Improve sqlite perfomance

I have online application, it's using sqlite database, it's containing few tables, one table containing 760k items, other tables containing 3-4k records. All tables have indexes.

I write something in database each 10 seconds. All is good, before i have few visitors on my online app, it's start work really slow, sometimes it just can't return result of query from 760k items table. It's not effect small tables, but only this large table.

Example of table:

"CREATE TABLE IF NOT EXISTS records (id VARCHAR(20) PRIMARY KEY, number INT NOT NULL, timestamp INT NOT NULL, name VARCHAR(20), views BIGINT NOT NULL,  data BINARY(128) NOT NULL, previousRecord VARCHAR(20) NOT NULL, , FOREIGN KEY ( previousRecord ) REFERENCES records ( id ) ON DELETE SET NULL)",

Indexes:

"CREATE UNIQUE INDEX IF NOT EXISTS records_number ON records(number)",

"CREATE INDEX IF NOT EXISTS records_name ON records(name)"

Query than i ran contains joins to other 2-3 tables. When i insert in table i use transaction.

I'm interesting what i can do to improve perfomance on my select requests.

sqlite DB is shows SQLITE_BUSY after the getting sqlite3_last_insert_rowid() objective c

I am trying to insert a member in sqlite DB member table. After inserting values if I take sqlite3_last_insert_rowid() I can't insert another member. the statement shows SQLITE_BUSY.Here is my code. Please anybody help.

-(NSInteger) saveMember:(TMMember *)member {
const char *dbPath = [databasePath UTF8String];
if (sqlite3_open(dbPath, &database) == SQLITE_OK)
{
    NSString *insertSQL = [NSString stringWithFormat:@"insert into members (memberName, memberAmount,shareFlag) values(\"%@\", \"%f\",%d)",member.memberName,member.amount,[[NSNumber numberWithBool:member.shareFlag]intValue]];
    const char *insert_stmt = [insertSQL UTF8String];
    sqlite3_prepare_v2(database, insert_stmt,-1, &statement, NULL);
    if(sqlite3_step(statement) == SQLITE_DONE)
    {
        NSInteger lastRowId = sqlite3_last_insert_rowid(database);
        member.memberId = lastRowId;
        NSLog(@"inserted member id = %ld",lastRowId);
        NSLog(@"member is added");
    }
    sqlite3_finalize(statement);
    statement = nil;
}
sqlite3_reset(statement);
sqlite3_close(database);
return 0;

}

How to retrieve image from SQLite database in Android Apps Develoment?

Suppose I have Prepopulated Database named by "D" that contains 3 field. - ID(pk) - Name - Image Now I want to retrieve this image from Database and try to set on imageview. For that I used 3 Class - MainActivity.java
- Student.java
- PrepopuDB.java

My code for these 3 classes: MainActivity.java

package com.example.db_image;
import java.util.ArrayList;

import android.app.Activity;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.ImageView;
import android.widget.TextView;
import android.widget.Toast;


public class MainActivity extends Activity {
    TextView tvName;
    Button btn;
    ImageView iv;
    private PrePopuDB pdb;
    ArrayList<Student> all;
    int i = 0;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        initialize();
        all = pdb.getAllInfo();

    }
    void initialize(){
        tvName = (TextView)findViewById(R.id.textView2);
        btn  = (Button)findViewById(R.id.button1);
        iv = (ImageView)findViewById(R.id.imageView1);
        pdb = new PrePopuDB(getApplicationContext());
    }
    public void show(View v){

        Student std = all.get(i);
        tvName.setText(std.getName());
        iv.setImageBitmap(getPhoto(std.getPh())); // Try to set bitmap image to imageview
        i++;        
    }
    private Bitmap getPhoto(byte[] phot){// this function try to convert byte array to Bitmap image. And return bitmap image.
        return BitmapFactory.decodeByteArray(phot, 0, phot.length);
    }

}

PrepopuDB.java

package com.example.db_image;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;
import android.util.Log;

public class PrePopuDB extends SQLiteOpenHelper {
    public static final String DB_NAME = "D";
    public static final String Table_NAME = "T";
    public static final String ID = "id";
    public static final String NAME = "name";
    public static String DB_PATH;
    public static final String PH = "photo";
    Context context;
    private SQLiteDatabase database;

    public PrePopuDB(Context context) {
        super(context, DB_NAME, null, 1);
        this.context = context;
        String packageName = context.getPackageName();
        DB_PATH = "/data/data/" + packageName + "/databases/";
        this.database = openDB();

    }

    public synchronized void close() {
        if (this.database != null) {
            this.database.close();
        }
    }

    public SQLiteDatabase openDB() {
        String path = DB_PATH + DB_NAME;
        if (database == null) {
            createDB();
            database = SQLiteDatabase.openDatabase(path, null,
                    SQLiteDatabase.OPEN_READWRITE);
        }
        return database;
    }

    private void createDB() {
        if (!checkDB()) {
            this.getReadableDatabase();
            copyDB();
        } else {
            Log.e(getClass().getName(), "DB Exist");
        }
    }

    private void copyDB() {
        try {
            InputStream is = context.getAssets().open(DB_NAME);
            String path = DB_PATH + DB_NAME;
            OutputStream op = new FileOutputStream(path);
            byte[] buffer = new byte[4096];
            int readcount = 0;
            while ((readcount = is.read(buffer)) > 0) {
                op.write(buffer, 0, readcount);
            }
            is.close();
            op.close();

        } catch (IOException e) {

            e.printStackTrace();
        }
    }

    public boolean checkDB() {
        String path = DB_PATH + DB_NAME;
        File file = new File(path);
        if (file.exists()) {
            return true;
        } else
            return false;
    }

    public ArrayList<Student> getAllInfo() {
        ArrayList<Student> allinfo = new ArrayList<Student>();
        // SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = this.database.query(Table_NAME, null, null, null, null,
                null, null);
        if (cursor != null && cursor.getCount() > 0) {
            cursor.moveToFirst();
            for (int i = 0; i < cursor.getCount(); i++) {
                int id = cursor.getInt(cursor.getColumnIndex(ID));
                String name = cursor.getString(cursor.getColumnIndex(NAME));
                // Try to get image in binary form
                byte[] pho = cursor.getBlob(cursor.getColumnIndex(PH));

                Student std = new Student(id, name, pho);
                allinfo.add(std); // store student class in array list
                cursor.moveToNext();
            }
        }
        cursor.close();
        // this.database.close();
        return allinfo;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub

    }

}

Student.java

package com.example.db_image;

import java.sql.Blob;    
import android.graphics.Bitmap; 

public class Student {
    int id;
    String name;
    // little bit confused about data type 
    //Bitmap ph;
    //Blob ph
    byte[] ph;
    @Override
    public String toString() {
        return "Student [id=" + id + ", name=" + name + "]";
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }

    public byte[] getPh() {
        return ph;
    }
    public Student(int id, String name,byte[] ph) {
        super();
        this.id = id;
        this.name = name;
        this.ph = ph;
    }

}

And my XML code looks Like:

<RelativeLayout xmlns:android="http://ift.tt/nIICcg"
xmlns:tools="http://ift.tt/LrGmb4"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:paddingBottom="@dimen/activity_vertical_margin"
android:paddingLeft="@dimen/activity_horizontal_margin"
android:paddingRight="@dimen/activity_horizontal_margin"
android:paddingTop="@dimen/activity_vertical_margin"
tools:context="com.example.db_image.MainActivity" >

<TextView
    android:id="@+id/textView1"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:text="Name: " />

<TextView
    android:id="@+id/textView2"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:layout_alignBaseline="@+id/textView1"
    android:layout_alignBottom="@+id/textView1"
    android:layout_marginLeft="46dp"
    android:layout_toRightOf="@+id/textView1"
    android:text="Sr7 " />

<ImageView
    android:id="@+id/imageView1"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:layout_below="@+id/textView2"
    android:layout_marginTop="20dp"
    android:layout_toRightOf="@+id/textView2"
    android:src="@drawable/ic_launcher" />

<Button
    android:id="@+id/button1"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:layout_below="@+id/imageView1"
    android:layout_centerHorizontal="true"
    android:layout_marginTop="55dp"
    android:text="Button" 
    android:onClick="show"/>

After write all this code my apps didn't work properly. On error log it shows

So here I ask your help. Any kind of helps to get rid of this problem is highly appreciated. Thank U :)

Writing exception to parcel the bind value at index 2 is null using contentprovider

07-31 11:41:56.333: E/DatabaseUtils(21572): Writing exception to parcel 07-31 11:41:56.333: E/DatabaseUtils(21572): java.lang.IllegalArgumentException: the bind value at index 2 is null 07-31 11:41:56.333: E/DatabaseUtils(21572): at android.database.sqlite.SQLiteProgram.bindString(SQLiteProgram.java:164) 07-31 11:41:56.333: E/DatabaseUtils(21572): at android.database.sqlite.SQLiteProgram.bindAllArgsAsStrings(SQLiteProgram.java:200) 07-31 11:41:56.333: E/DatabaseUtils(21572): at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:47) 07-31 11:41:56.333: E/DatabaseUtils(21572): at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314) 07-31 11:41:56.333: E/DatabaseUtils(21572): at android.database.sqlite.SQLiteQueryBuilder.query(SQLiteQueryBuilder.java:400) 07-31 11:41:56.333: E/DatabaseUtils(21572): at android.database.sqlite.SQLiteQueryBuilder.query(SQLiteQueryBuilder.java:294) 07-31 11:41:56.333: E/DatabaseUtils(21572): at com.piqsho.p.d.h.CP.query(CP.java:542) 07-31 11:41:56.333: E/DatabaseUtils(21572): at android.content.ContentProvider.query(ContentProvider.java:652) 07-31 11:41:56.333: E/DatabaseUtils(21572): at android.content.ContentProvider$Transport.query(ContentProvider.java:189) 07-31 11:41:56.333: E/DatabaseUtils(21572): at android.content.ContentProviderNative.onTransact(ContentProviderNative.java:113) 07-31 11:41:56.333: E/DatabaseUtils(21572): at android.os.Binder.execTransact(Binder.java:351) 07-31 11:41:56.333: E/DatabaseUtils(21572): at dalvik.system.NativeStart.run(Native Method) 07-31 11:41:56.417: E/AndroidRuntime(21586): FATAL EXCEPTION: ServiceStartArguments 07-31 11:41:56.417: E/AndroidRuntime(21586): java.lang.IllegalArgumentException: the bind value at index 2 is null 07-31 11:41:56.417: E/AndroidRuntime(21586): at android.database.DatabaseUtils.readExceptionFromParcel(DatabaseUtils.java:167) 07-31 11:41:56.417: E/AndroidRuntime(21586): at android.database.DatabaseUtils.readExceptionFromParcel(DatabaseUtils.java:137) 07-31 11:41:56.417: E/AndroidRuntime(21586): at android.content.ContentProviderProxy.query(ContentProviderNative.java:366) 07-31 11:41:56.417: E/AndroidRuntime(21586): at android.content.ContentResolver.query(ContentResolver.java:373) 07-31 11:41:56.417: E/AndroidRuntime(21586): at android.content.ContentResolver.query(ContentResolver.java:316) 07-31 11:41:56.417: E/AndroidRuntime(21586): at com.piqsho.p.s.SIMS.getNewChatMessages(SIMS.java:186) 07-31 11:41:56.417: E/AndroidRuntime(21586): at com.piqsho.p.s.SIMS$ServiceHandler$1.run(SIMS.java:81) 07-31 11:41:56.417: E/AndroidRuntime(21586): at com.piqsho.p.s.SIMS$ServiceHandler.handleMessage(SIMS.java:73) 07-31 11:41:56.417: E/AndroidRuntime(21586): at android.os.Handler.dispatchMessage(Handler.java:107) 07-31 11:41:56.417: E/AndroidRuntime(21586): at android.os.Looper.loop(Looper.java:194) 07-31 11:41:56.417: E/AndroidRuntime(21586): at android.os.HandlerThread.run(HandlerThread.java:60) 07-31 11:41:57.428: E/DatabaseUtils(21572): Writing exception to parcel 07-31 11:41:57.428: E/DatabaseUtils(21572): java.lang.IllegalArgumentException: the bind value at index 1 is null 07-31 11:41:57.428: E/DatabaseUtils(21572): at android.database.sqlite.SQLiteProgram.bindString(SQLiteProgram.java:164) 07-31 11:41:57.428: E/DatabaseUtils(21572): at android.database.sqlite.SQLiteProgram.bindAllArgsAsStrings(SQLiteProgram.java:200) 07-31 11:41:57.428: E/DatabaseUtils(21572): at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:47) 07-31 11:41:57.428: E/DatabaseUtils(21572): at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314) 07-31 11:41:57.428: E/DatabaseUtils(21572): at android.database.sqlite.SQLiteQueryBuilder.query(SQLiteQueryBuilder.java:400) 07-31 11:41:57.428: E/DatabaseUtils(21572): at android.database.sqlite.SQLiteQueryBuilder.query(SQLiteQueryBuilder.java:294) 07-31 11:41:57.428: E/DatabaseUtils(21572): at com.piqsho.p.d.h.CP.query(CP.java:542) 07-31 11:41:57.428: E/DatabaseUtils(21572): at android.content.ContentProvider.query(ContentProvider.java:652) 07-31 11:41:57.428: E/DatabaseUtils(21572): at android.content.ContentProvider$Transport.query(ContentProvider.java:189) 07-31 11:41:57.428: E/DatabaseUtils(21572): at android.content.ContentProviderNative.onTransact(ContentProviderNative.java:113) 07-31 11:41:57.428: E/DatabaseUtils(21572): at android.os.Binder.execTransact(Binder.java:351) 07-31 11:41:57.428: E/DatabaseUtils(21572): at dalvik.system.NativeStart.run(Native Method)

Cursor c = finalQb.query(db, projection, selection, selectionArgs, null, null, sortOrder);

/**
 * register to watch a content URI for changes
 */
try {
    if(c != null && c.getCount() > 0){
    c.setNotificationUri(getContext().getContentResolver(), uri);
    }
} catch (Exception e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
}finally{
    if(c != null){
    c.close();
    }
}

Using a database in a Swift app to manage volunteers

To preface, I am very new to Swift.

I am in the planning process of building an app for my organization that manages our volunteers' information. I have read about using Realm or SQLite but my question is more concerning the upkeep of the database by those who manage the volunteers in my organization. Once the app is built and there is information in the database, how do people update it and keep up with it without having to log into the SQL DB. Is there a way to use any APIs to upload spreadsheets or CSVs to update the data?

Any help is welcome.

Sqlite : java.sql.SQLException: attempt to write a readonly database

Code :

 try {
        Class.forName("org.sqlite.JDBC");

        String fname = jTextField1.getText();
        String lname = jTextField2.getText();
        String address = jTextField3.getText();
        String phone = jTextField4.getText();
        String position = jTextField5.getText();
        String salary = jTextField6.getText();
        Connection con = null;
        Statement st = null;
        int result = 0;


        con = DriverManager.getConnection("jdbc:sqlite:C:\\management.db");
        st = con.createStatement();
        if(fname != null && lname != null && address != null && phone != null && position != null && salary != null) {
            String instert = "insert into emp_details (firstname,lastname,address,phone,position,salary) VALUES('"+ fname +"','"+ lname +"','"+ address +"','"+ phone +"','"+ position +"','"+ salary +"') ";
            result = st.executeUpdate(instert);
            if(result == 1) {

                JOptionPane.showMessageDialog(this, "record successfully saved", "Emp Details", WIDTH);

                con.close();
            }
            else {
                JOptionPane.showMessageDialog(this, "Enter Correct Details", "Emp Details", WIDTH);
            }
        }
    } catch (ClassNotFoundException | SQLException ex) {
        Logger.getLogger(Emp.class.getName()).log(Level.SEVERE, null, ex);
    }

i made a program for Restaurant Management ..Whenever I try to add(insert) something to my database using my program, I get this exception:

"java.sql.SQLException: attempt to write a readonly database" .

Android database usage best practice

I have started to work on an android application that requires multiple sqlite databases with same schema. I did some research on how one can add the database for the application and found that there are two ways in which this can be done:

1. Create database

Code for database creation can be added to the activity and once the activity starts the db is created on the specified location.

2. Copy database

The other way could be to add the desired database to the application's assets folder and copy it on the specified location.

I want to know what would be the better approach in my case where I might have require multiple databases with the same schema. Any links or suggestion on the same are highly appreciated.

DELETE A BUTON FROM A BUTTON BOX AS WELL AS FROM DATABASE

When I want to delete a selected button(created dynamically) from vertical button box(created in glade). My code works only for the end row button which is seen instantly as I press a delete button in my gtk window,means it gets destroyed at the same time, but for every other row it does not work...however it is getting deleted but can only be seen when I run my codes again for the next time.my problem is this...i am using sqlite database in my codes... A Little help is very much appreciated...Thanks in advance!

/*delete button created and called*/  
  DeleteButton = GTK_WIDGET( gtk_builder_get_object( builder, "DeleteButton" ) );
  gtk_signal_connect (GTK_OBJECT (DeleteButton), "clicked", GTK_SIGNAL_FUNC (Delete_Data), NULL);

/*delete function*/
void Delete_Data( GtkWidget *widget, gpointer data)
{
    char sql[100];
    sprintf(sql, "DELETE FROM SURVEILLANCE \nwhere ID= %s;",updateid);
                    "SELECT * from SURVEILLANCE";

    deleteData(sql);
    Delete_Button();

}

/*for deletion*/
void Delete_Button()
{
    gtk_signal_connect (GTK_OBJECT (sW_Button), "clicked", GTK_SIGNAL_FUNC (camera3), sW_VBbox);
    gtk_widget_destroy(sW_Button);
}

/*database file*/
void deleteData(char *sql)
{
   sqlite3 *db;
   char *zErrMsg = 0;
   int rc;

   const char* data = "Callback function called";

/* Open database */
   rc = sqlite3_open("Database_Surveillance.db", &db);
   if( rc )
   {
      fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
      exit(0);
   }
   else
   {
      fprintf(stderr, "deleteData_Opened database successfully\n");
   }

//   /* Create merged SQL statement */
//   sql = "DELETE from SURVEILLANCE where ID=2; " \
//         "SELECT * from SURVEILLANCE";

/* Execute SQL statement */
   rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
   if( rc != SQLITE_OK )
   {
      fprintf(stderr, "SQL error: %s\n", zErrMsg);
      sqlite3_free(zErrMsg);
   }
   else
   {
      fprintf(stdout, "deleteData_Operation done successfully\n");
   }
   sqlite3_close(db);
}

this are my codes for deletion...if required I will show my codes for everything I am working!

Need an Idea for create Trigger

I have one Field Modified On with datetime datatype in sql Table.And the Field is not null.

I need to create trigger for update the Modified on Field while the record will created.

How can I do this??

Changed DB, updated DB version can't add data and can't not add data

I am working on an android application that uses two databases. Recently, I had to add a new column to one of the databases. Upon doing so, it broke my database. Installing and re-installing the application on my device did nothing, and I also updated the DB version.

Trying to insert data will net me this error:

E/SQLiteLog﹕ (1) table message_table has no column named msg_type

So, I tried taking out the "msg_type" column from the insert, and inserting data which gave me this error:

E/SQLiteLog﹕ (1299) abort at 8 in [INSERT INTO message_table(recipient,message) VALUES (?,?)]: NOT NULL constraint failed: message_table.msg_typeTEXT

Here is the oncreate:

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " (" +
            COL_1 + " INTEGER PRIMARY KEY AUTOINCREMENT, " + //msg_id
            COL_2 + " TEXT NOT NULL, " + //recipient
            COL_3 + " TEXT, " + //message
            COL_4 + "TEXT NOT NULL);"); //message type
}

and the insert class:

public boolean addMessage(String recipient, String message, String type){
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();

    //populate message object
    contentValues.put(COL_2, recipient);
    contentValues.put(COL_3, message);
    contentValues.put(COL_4, type);

    //insert new message
    long result = db.insert(TABLE_NAME, null, contentValues);

    //check if operation was successful
    if(result == -1)
        return false;
    else
        return true;
}

How can I be getting an error for either case? I thought that it didn't recognize the new column was added from the first error, but it also doesn't like not receiving the data for that column.

ANDROID: attempt to re-open an already-closed object?

I am trying a delete data from my table(s) however it seems that I am getting an error. So basically in my database contains two tables that are connected through an "exercise ID". So to delete data from my database, I would need to first get an exercise ID from the row I want deleted, then, use that as a reference against my second table and delete everything that is associated with it. So heres what I have so far that does not seem to want to work...

public void deleteRowFromDatabase(String exerciseName, String bodyPart) {

    SQLiteDatabase db = myDBHelper.getWritableDatabase();

    int exerciseID = getExerciseID(exerciseName, bodyPart);
    String query = "SELECT " + myDBHelper.COLUMN_EXERCISENAME_ID + " FROM " + myDBHelper.TABLE_EXERCISES_VALUE +
                    " WHERE " + myDBHelper.COLUMN_EXERCISENAME_ID + "=\"" + exerciseID + "\";";

    Cursor c = db.rawQuery(query, null);

    while (c.getCount()>0){
        c.moveToFirst();
        db.execSQL("DELETE FROM " + myDBHelper.TABLE_EXERCISES_VALUE + " WHERE " + myDBHelper.COLUMN_EXERCISENAME_ID +
                    "=\"" + exerciseID + "\";");
        c.moveToNext();
    }

    db.execSQL("DELETE FROM " + myDBHelper.TABLE_EXERCISES + " WHERE " + myDBHelper.COLUMN_BODYPARTNAME +
                "=\"" + bodyPart + "\"" + " AND " + myDBHelper.COLUMN_EXERCISENAME + "=\"" + exerciseName + "\";");

    c.close();
    db.close();
}

sqlite write error on attempt to read

I received this error when trying to SELECT from a sqlite database that I had opened with a read-only connection:

attempt to write a readonly database

What would cause a write error on a read attempt?

SQLite no such table, logic error

I had a database it worked fine, I changed the name of the table and it stopped working, if I change the name back, it works again. Of course I change query string after changies in the database. A feeling that SQLIte caches my database... What could be the problem? My query string:

"SELECT * FROM 'tb_Table_Name' ORDER BY 'Id';"

If i change tb_Table_Name to Table_Name it tell me no such table, logic error... And when I add new columns SQLIte not find them too, it tell me no such column, logic error It looks like it just working on my previous database structure. I can't understand why... Help me.

Where can I find the the SQLite binary SQLite.Interop.dll?

There are supposed to be an x86 and an x64 version of the library SQLite.Interop.dll so that you can work on either machine architecture. However I cannot find where this binary comes from. I've downloaded the .NET 4.0 precompiled binary packages from system.data.sqlite.org (both statically-linked and non), and they just aren't in the zip packages. I don't get it. They are referenced several places in the documentation yet they are not provided in the downloads.

Android DB Update near "WHERE": syntax error

I know there are already too many questions about this, and believe me I've done my part reading the answers to find a solution. Unfortunately, I can't find my mistake

Here's my code:

SQLiteDatabase db = getWritableDatabase();

ContentValues values = new ContentValues();
values.put("eat_it", 1);
values.put("expired", 1);
String[] args = {food.name, "0"};

db.update("foods", values, "name=? AND expired=?", args);
db.close();

What I want to do:

Find a record in the table foods WHERE the value of column name = food.name and the value of column expired = 0. If found, SET the record's eat_it column to 1 and expired column to 1. If no record found, don't do anything

Here's my create table syntax:

CREATE TABLE IF NOT EXISTS foods (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    ...
    eat_it INTEGER DEFAULT 0,
    expired INTEGER DEFAULT 0
);

Here's the error message:

...
 Caused by: android.database.sqlite.SQLiteException: near "WHERE": syntax error (code 1): , while compiling: UPDATE foods SET expired=?,eat_it=? WHERE name ='Taco Salad'WHERE expired = 0
...

Thanks for your help

Check record in SqLite (ionic)

Does anybody know, that how can I check an inserted record? I would like to create an intro page. When the user visits once, I wanna store it and check it.

$rootScope.insert = function(visited) {
     var query = "INSERT INTO shoplist (visited) VALUES (?)";
     $cordovaSQLite.execute(db, query, [visited]).then(function(res) {
        console.log("INSERT ID -> " + res.insertId);
     }, function (err) {
        console.error(err);
     });
}

$rootScope.insert(1);

Basically I would like to check that visited record is 0 or 1. What should I do?

Parsing a list from android sqlite to remote database using a loop

Sorry for the long post - I wasn't sure how much detail to include. Bare with me please, I am fairly new to both Android and PHP.

Part of the android app I am building requires me to save a list of users in an sqlite database. The user can then enter an activity which shows the list of users currently stored in the sqlite datebase within a listview.

I have then tried to implement a function to sync this list of users to a remote database, using Async Tasks, JSON, and a PHP service, by using a FOR LOOP to cycle through the database tables 'users'.

The problem is that the sync only works for the first item in the list. After this has been passed through to the remote database, the activity crashes. The log reads:

E/JSON Parser﹕ Error parsing data org.json.JSONException: Value data of type java.lang.String cannot be converted to JSONObject

E/AndroidRuntime﹕ FATAL EXCEPTION: AsyncTask #3 Process: example.prguru.com.pi_prototype_30, PID: 5139 java.lang.RuntimeException: An error occured while executing doInBackground() at android.os.AsyncTask$3.done(AsyncTask.java:304) ...

Caused by: java.lang.NullPointerException: Attempt to invoke virtual method 'java.lang.String org.json.JSONObject.toString()' on a null object reference at example.prguru.com.pi_prototype_30.ShowAdmins$syncUser.doInBackground(ShowAdmins.java:137) at example.prguru.com.pi_prototype_30.ShowAdmins$syncUser.doInBackground(ShowAdmins.java:87)

Line 137 in the java code is:

 Log.d("Sync Attempt", jobj.toString());

PHP CODE:

require_once 'config.php';

if(!empty($_POST)){
$row = $_POST['table_id'];
$type = $_POST['type'];
$id_num = $_POST['id_number'];
$name = $_POST['name'];
$email  = $_POST['email'];
$course = $_POST['course'];
$password = $_REQUEST['password'];

$ins = mysql_query("INSERT INTO pi_prototype_inventory_30.users (table_id, type, id_number, name, email, course, password) 
VALUES('$row','$type','$id_num','$name', '$email', '$course', '$password')");

if(!$ins)
{
    echo (mysql_error());
}
else
{
    echo ("data inserted");
}
}
else {
echo (mysql_error());
}

SYNC CLASS:

    @Override
    protected String doInBackground(String... args) {
        // TODO Auto-generated method stub
        // Check for success tag
        int success;

        DBHandler dbAdapter = DBHandler.getDBHandlerInstance(getApplicationContext());
        try {
            dbAdapter.createDataBase();
        } catch (IOException e) {
            Log.i("*** select ", e.getMessage());
        }

        dbAdapter.openDataBase();
        String query = "SELECT * FROM users";
        ArrayList<ArrayList<String>> stringList = dbAdapter.selectRecordsFromDBList(query, null);
        dbAdapter.close();

        try {

            List<NameValuePair> nameValuePairs = new ArrayList<NameValuePair>(7);
            for (int i = 0; i < stringList.size(); i++) {
                ArrayList<String> list = stringList.get(i);
                nameValuePairs.add(new BasicNameValuePair("table_id", list.get(0)));
                nameValuePairs.add(new BasicNameValuePair("type", list.get(1)));
                nameValuePairs.add(new BasicNameValuePair("id_number", list.get(2)));
                nameValuePairs.add(new BasicNameValuePair("name", list.get(3)));
                nameValuePairs.add(new BasicNameValuePair("email", list.get(4)));
                nameValuePairs.add(new BasicNameValuePair("course", list.get(5)));
                nameValuePairs.add(new BasicNameValuePair("password", list.get(6)));

                Log.d("Request!", "Starting!");

                jobj = jsonParser.makeObjHttpRequest(REG_URL, "POST", nameValuePairs);

                Log.d("Sync Attempt", jobj.toString());

            }//forLoop
                success = jobj.getInt(TAG_SUCCESS);
                if (success == 1){
                    Log.d("SYNCED!", jobj.toString());
                    return jobj.getString(TAG_MESSAGE);
                }
                else {
                    Log.d("Sync failure!", jobj.getString(TAG_MESSAGE));
                    return jobj.getString(TAG_MESSAGE);
                }



        } catch (JSONException e){
            e.printStackTrace();
        }
        return null;
    }//doInBackground

Do I need to implement some sort of loop in my PHP code so it accepts more than one entry from the list? I don't know how I would go about coding that because I will never be sure how many entries will need to be synced at any one time. Or is the problem within the java code itself? I'm not quite sure how to make sense of the errors presented in the log.

Mono.Data.Sqlite throws exceptions

I'm using SQLite as database engine in my app, but at the same time the application must be cross-platform, so i have decided to use Mono.Data.Sqlite. And this what i did:

• Installed the latest version of Mono (4.0.2 SR 2), copied Mono.Data.Sqlite.dll from Mono's directory (net4_5) to my project in Visual Studio 2015

• Downloaded and copied precompiled sqlite3.dll library.

And then i wrote a simple app:

        const string databaseFileName = "somedata.db";
        var path = Directory.GetCurrentDirectory() + Path.DirectorySeparatorChar + databaseFileName;

        if (!File.Exists(path)) SqliteConnection.CreateFile(databaseFileName);

        var connString = string.Format("Data Source=file:{0}", databaseFileName);

        using (var conn = new SqliteConnection(connString))
        {
            conn.Open();

            /* Some code */
            conn.ChangePassword("testpassword");
            conn.Close();
        }

But i'm facing with some issues:

• First, when i'm using Data Source=file:{0} in connection string, it throws an exception: "URI formats are not supported". Replacing it to URI=file:{0} helps, but why the first option doesn't work?

• Second, when i call conn.ChangePassword("testpassword") it throws an exception: System.EntryPointNotFoundException: Unable to find an entry point named "sqlite3_rekey" in DLL "sqlite3"

• Third, using URI=file:{0};Password=testpassword with the already encrypted database throws an exception: System.EntryPointNotFoundException: Unable to find an entry point named "sqlite3_key" in DLL "sqlite3"

It doesn't actually happen with official wrapper for SQLite, but happens with Mono's.

P.S. Mono's wrapper works normally when i don't use encryption and Data Source=file:{0} instead of URI=file:{0}

SQLite Windows 10 RTM universal app

With the Windows 10 RTM and Visual Studio 2015 RTM SQLite doesn't work any more.

For instance, the tutorial at http://ift.tt/1GH1zrE will not add the SQLLite.cs and SQLiteAsync.cs files to my project, also the "Microsoft Visual C++ Applocal runtime Package for Windows UAP" reference is not available for me.

The funny thing is it worked with the RC versions ...

SQLite Version: sqlite-uap-3081101.vsix (5.73 MiB)

How to get the specific error code from sqlite3_exec?

So, I need to try to lock a sqlite database and update a table. To do this, I started it off with pragma locking_mode=exclusive; begin exclusive;. As expected though, when another program has a lock on the database this fails. It returns the generic SQLITE_ERROR code. However, I'd prefer to have the exact reason, SQLITE_LOCKED. How can I determine what the more specific error from sqlite3_exec() is?

The constructor CursorLoader is undefined

I am trying to retrieve rows of locations from my SQLiteTable but I ran into a compilation error, the constructor CursorLoader is undefined. What can I do to resolve this error?

@Override
public Loader<Cursor> onCreateLoader(int arg0,
    Bundle arg1) {

    // Uri to the content provider LocationsContentProvider
    Uri uri = LocationsContentProvider.CONTENT_URI;

    // Fetches all the rows from locations table

    //return new CursorLoader(null);
   //(Context context, Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder)

    /ERROR HERE
    return new CursorLoader(this, null, null null, null);
}

In LocationsContentProvider.java

 /** A callback method which is invoked by default content uri */
@Override
public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder) {

    if(uriMatcher.match(uri)==LOCATIONS){
        return mLocationsDB.getAllLocations();
    }
    return null;
}

App crashes when I try to get the _id in my SQLite Table?

Hi guys so here is my problem, I need to be able to access the unique ID of a row that is created because I created a second table that will "Connected" through the IDs. However here is my issue, whenever I try calling this method, my app crashes.. I was hoping the community could help me out here. Many Thanks.

public void addExerciseToDatabase(Exercises exercises){

    SQLiteDatabase db = myDBHelper.getWritableDatabase();

    ContentValues values = new ContentValues();
    ContentValues nuValues = new ContentValues();

    values.put(myDBHelper.COLUMN_BODYPARTNAME, exercises.get_bodyPart());
    values.put(myDBHelper.COLUMN_EXERCISENAME, exercises.get_exerciseName());

    db.insert(myDBHelper.TABLE_EXERCISES, null, values);

    //FOR THE NEW TABLE VALUES

    int ID = getExerciseID(exercises.get_bodyPart(),exercises.get_exerciseName());

    nuValues.put(myDBHelper.COLUMN_EXERCISENAME_ID, ID);
    nuValues.put(myDBHelper.COLUMN_NUMSETS, exercises.get_numSets());
    nuValues.put(myDBHelper.COLUMN_NUMWEIGHT, exercises.get_numWeight());
    nuValues.put(myDBHelper.COLUMN_NUMREPS, exercises.get_numReps());

    db.insert(myDBHelper.TABLE_EXERCISES_VALUE, null, nuValues);
    db.close();
}

/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
THIS method returns the EXERCISE ID, that corresponds to the exercise
passed and Bodypart passed
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
public int getExerciseID(String exercise, String bodyPart) {

    SQLiteDatabase db = myDBHelper.getWritableDatabase();

    String query = "SELECT" + myDBHelper.COLUMN_ID + "FROM " + myDBHelper.TABLE_EXERCISES + " WHERE " +
            myDBHelper.COLUMN_BODYPARTNAME + " = " + bodyPart + " AND " +
            myDBHelper.COLUMN_EXERCISENAME + " = " + exercise ;

    Cursor c = db.rawQuery(query, null);
    c.moveToFirst();
    int exerciseID = Integer.parseInt(c.getString(0));
    /*int exerciseID = c.getInt(c.getColumnIndex(myDBHelper.COLUMN_ID));*/
    c.close();
    db.close();

    return exerciseID;
}