Xamarin PCL library and Sqlite data storage

I'm developing a cross platform application with Xamarin for Android, iOS and Windows Phone. I'm creating a PCL library that contains all my business logic including calls to my REST API. For now this seem to work fine, however, I need to store bits of information in a persistent space, such as API authentication keys etc, and I figured I might as well use Sqlite for this, it seems to be supported on all three platforms.

The problem I'm running into is that System.Data and Mono.Data.Sqlite don't seem to be available within the context of a PCL project.

Even if my PCL library can't, or doesn't, link with the required assemblies, I should at least be able to use the references (through using) in my PCL library, right? I mean, I should be able to have the application be responsible for creating a database connection and then use dependency injection to inject that connection into my library. But seems I can't even make references in my code to any the System.Data and Mono.Data.Sqlite at all.

How do I abstract my data layer in a PCL library with Xamarin for Android, iOS and Windows Phone?

[1] http://ift.tt/1csOmHV

Sync two SQLite databases via a single sqlstatement?

I have two identical databases with parameters. From then on only the column "Value" can change. So there will be no delete or insert statements, just update commands on the column Value and column "lastChanged"

till now I have attached both databases via

attach database 'parameters.db' as parameters;
attach database 'parameters1.db' as parameters1;

no I have this command to determine the last changed value of both tables with

select 
CASE par.lastchange WHEN par.lastchange > par1.lastchange THEN
    par.wert
ELSE
    par1.wert
END as newvalue
from parameters.parameter par
left join parameters1.parameter par1
    on par.id=par1.id
where par.wert<>par1.wert

is there a way to wrap this command into a update command? or do I have to iterate through the result set and execute the update commands by my self inidivually?

Find the Average salary deviation for an employee within department

I have a list of employees who work within a set of teams. We are trying to figure out for each year the difference for each employee’s salary to their department’s average. I have been looking at standard deviations but I’m a little stuck if I need to have a sub select to achieve this.

The table looks like:

I do not have any code to show because I always get stuck on aliases.

How parse datetime format from datebase?

The database (SQLite) has a field of type REAL with the values of the form (42153.659595). How to translate this value in the form "dd.MM.yy HH:mm:ss" if 42153.659595 = 29.05.2015 15:49:49 ?

Save sqlite db in external memory

I want to save sqlite db file in external memory.

I written code to save in internal memory like below.But I want to extract that "xxx.db" in run time and check whether my DB fields are saved or not.

manifest.xml

<uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE" />
<uses-permission android:name="android.permission.READ_EXTERNAL_STORAGE" />
<uses-permission android:name="android.permission.INTERNET" />
<uses-permission android:name="android.permission.ACCESS_NETWORK_STATE" />

and DBHelper.java

private static String DB_PATH           =   "data/data/com.example.myApp/databases/";
private static final String DATABASE_TABLE  =   "myDB";
private static final int DATABASE_VERSION   =    1;
private static final String DATABASE_NAME   =   "mydb.db";

public void createDatabase() throws IOException{

        boolean doesDbExist = checkDatabase();

        if(doesDbExist){
            Toast.makeText(ourContext, "DB Already Created", Toast.LENGTH_SHORT).show();
        }else{
            this.getReadableDatabase();
            try{
                copyDatabase();
            }catch(Exception e){
                Log.d("DBError", "Copy DB error");
            }
            Toast.makeText(ourContext, "New DB created.", Toast.LENGTH_SHORT).show();
        }
    }

    private void copyDatabase() throws IOException{
            //Open your local db as the input stream
            InputStream myInput     =   ourContext.getAssets().open(DATABASE_NAME);

            // Path to the just created empty db
            String outFileName      =   DB_PATH + DATABASE_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 len;

            while ((len = myInput.read(buffer))>0) {

                myOutput.write(buffer, 0, len);

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

        }

Please help me and suggest me for required changes.

In SQLite in Cordova, why do I get "Error preparing SQLite statement" error?

I'm working on a Windows 8 Cordova app in Visual Studio 2015. Right now I'm just trying to test creating my tables but I get an error.

Below is what I get in my console log:

adding proxy for SQLitePlugin
OPEN database: nanoDB.db
db name: nanoDB.db at full path: .[...]
new transaction is waiting for open operation
DB opened: nanoDB.db
sql exception error: Error preparing an SQLite statement.
sql exception error: Error preparing an SQLite statement.
sql exception error: Error preparing an SQLite statement.
sql exception error: Error preparing an SQLite statement.
sql exception error: Error preparing an SQLite statement.
sql exception error: Error preparing an SQLite statement.
sql exception error: Error preparing an SQLite statement.

This is the script I'm running inside after the "deviceready" listener:

var nanoDB = window.sqlitePlugin.openDatabase({ name: "nanoDB.db" })

nanoDB.transaction(function (tx) {

    tx.executeSql(nanoDB, "CREATE TABLE IF NOT EXISTS nanoInst (id integer primary key, api_id integer, name text)");
    tx.executeSql(nanoDB, "CREATE TABLE IF NOT EXISTS nonoProd (id integer primary key, api_id integer, name text)");
    tx.executeSql(nanoDB, "CREATE TABLE IF NOT EXISTS nanoInd (id integer primary key, api_id integer, name text)");
    tx.executeSql(nanoDB, "CREATE TABLE IF NOT EXISTS nanoFiles (id integer primary key, api_id integer, name text, fileType text, fileLoc text)");
    tx.executeSql(nanoDB, "CREATE TABLE IF NOT EXISTS nanoRelProd (id integer primary key, inst_id integer, prod_id integer)");
    tx.executeSql(nanoDB, "CREATE TABLE IF NOT EXISTS nanoRelInd (id integer primary key, inst_id integer, ind_id integer)");
    tx.executeSql(nanoDB, "CREATE TABLE IF NOT EXISTS nanoRelFiles (id integer primary key, inst_id integer, file_id integer)");
});

You can see the DB is created but I error when I try to add tables.

Exception came in old version of application and not rectifying in new version?

I have created one application in that I made some mistake so when I am storing into database it is getting some error.

After I changed my program code and uploaded into playstore and users is updating but now also it is showing same error. where the problem is coming I didn't get, I thought that the table is not updating so my database onUpgrade method is like this

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

how can I update the table means drops the old table and creates new table, then my application data that is stored in database will be lost I don't want loss of data.I am little bit confused, is there any change(option) like uninstall the application and install again from store side.

Integrity constraint violation: 1048 Column 'user_id' cannot be null in IOS

I'm getting this error -
"Integrity constraint violation: 1048 Column 'user_id' cannot be null, query was: INSERT INTO user_covers (user_id, user_cover_url) VALUES (?, ?)"

I'm using POST method to call.

SQL: Must appear in the GROUP BY clause or be used in an aggregate function

I have a Table that displays a teams salaries and I want to display their minimum, maximum and average salary for each team for each year.

My table looks like:

I run the following SQL : SELECT MIN(salary), MAX(salary), AVG(salary), teamID, yearID FROM salaries;

But get the following error: ERROR: column "salaries.teamid" must appear in the GROUP BY clause or be used in an aggregate function

Full Text Search SQL has different effect between ios8 and ios7

I have use the "*" symbols to do selecting on ios7 sqlite which the effect what i want, but ios8 sqlite does not supporting "*" and show "no result". Is the FMDB problem or the ios 8 does not support the "*" symbols?

SQL(with FMDB):

db.executeQuery("SELECT * FROM TABLE_1 WHERE col_1 MATCH ?",withArgumentsInArray:["*A*"])

Table:

    +==============================+
    |     Name: TABLE_1            |
    +------------------------------+
    |   col_1     |     Result     |
    +==============================+
    |     A       |      result 1  |
    +------------------------------+
    |     B       |      result 2  |
    +------------------------------+
    |     AAB     |      result 3  |
    +------------------------------+

Browsing csv file and then import it on android sqlite

Im new to android. Im still need to learn a lot of things. I know some might report this as duplicate but i guess its different. I want to select files first then import it on android sqlite. I have this code where i can select files and it works properly, after i selected a file it goes like Selected file : "/path/file.csv" something like that. I've also seen some code to import csv file but those files are just stored it assets folder of the project. What i want is to select the file and execute the import so it will be saved on the database. please help.

Trouble joining and filtering data in sqlite

I know this is a simple question, and I tried to find a solution but with no luck. I'm a sqlite newb and am having trouble performing this query. First, the .schema:

CREATE TABLE cities (name text, state text);
CREATE TABLE weather (city text, year int, warm_month text, cold_month text, average_high int);

I have a table of city name / state values, and then a weather table with the columns listed above. I need my query to find the mean of the average high temperatures for all of the cities within a state.

My query is as follows:

select city, avg(average_high) from weather
  ...> left outer join cities
  ...> on name = city
  ...> group by state;

This actually displays the correct average for each state, but it displays a city name rather than the state name. I know that I ask for city in my select statement, and I never select state anywhere... I guess I need an additional select statement but I don't know the syntax.

Thanks for helping me resolve this trivial question!

Android SQLite Display all records in Listview

I have been on this all weekend and Im at my wits end as to how to return all records in a query into a listview

the following code works perfectly

public void DisplayContact(String sitename)
{
final DBAdapter db = new DBAdapter(this);
db.open();  

Cursor c = db.getAsset6(sitename);
int mx=0;
String[] lvdata = new String[c.getCount()-1];
while (c.moveToNext()) {
lvdata[mx]=c.getString(2) + "-" + c.getString(1) + "-" + c.getString(6);
mx++;

}
getActionBar().setTitle("Number of Records = " + mx); 
    ArrayAdapter<String> adapter = new ArrayAdapter<String>(this,
        R.layout.rowlayout, R.id.label, lvdata);
setListAdapter(adapter);

}

public Cursor getAsset6(String strname) throws SQLException 
{ 
    Cursor mCursor =
            db.query(true, DATABASE_TABLE,
                     new String[] {KEY_ROWID,KEY_FAULTREF,KEY_WORKNUM,KEY_ORIGNUM,KEY_REPBY,KEY_REPDATTIM,KEY_DESCRIPTION},
                     KEY_REPBY + " LIKE ?",
                     new String[] { "%" + strname + "%" },
                     null, null, null, null); if (mCursor != null) {
        mCursor.moveToFirst();
    }
    return mCursor;                           
}

The problem is it doesnt add the first item in the query (ie there are 151 records in the query but it only puts 150 in the listview missing the first entry)

I am sure its something to do with c.moveToFirst(); and a Do Statement but i have read every article I can find on here and whatever combination I try it crashes when I run the query in the app. I am running the app using an external database so I cant see whats causing the crash in eclipse

Can one of you SQL gurus help me out its driving me crazy

Your help appreciated

Mark

SqliteEntity provider in SharpDevelop

In SharpDevelop, the Entity Framework Wizard only lists the database driver "MS SQL Server". Is it possible to use Sqlite in the entity model designer?

SQLite update command fails when triggered from Toolbar

I have noticed strange behaviour while executing SQLite update command from Toolbar menu item.

Update returns 0 which stands for 0 rows affected. The same method called from floating action button in the same activity works like charm (1 row is updated then).

I am using the following SQLite database helper (synchronized singleton):

DataBaseHelper

Has anyone of you had the same issue with SQLite?

Adding Å,Ä and Ö to sqlite table through python 2.7, cgi and html post

I'm using cgi, python 2.7 and sqlite.

I'm trying to insert an string? that contains the characters å,ä and ö into an sqlite table.

the string are taken from an html post using cgi

this code in takes the value:

print '<form action="./printusername.cgi" method="post">'
print 'First Name: <input type="text" name="first_name">'
print '</form>'

im receiving the value in printusername.cgi like this:

import cgi
cgitb.enable()
form = cgi.FieldStorage() 
first_name = form.getvalue('first_name')

then i'm trying to pass it to an sqlite table, along whit some other values, like this:

import sqlite3
con = sqlite3.connect('Addressbook.db')
cursor = con.cursor()
contact = (first_name,other_valu_1,other_valu_1)
cursor.execute("INSERT INTO entrys VALUES(?, ?, ?)",contact)

when i'm doing this I'll receive the following error:

<class 'sqlite3.ProgrammingError'>: You must not use 8-bit bytestrings
unless you use a text_factory that can interpret 8-bit bytestrings
(like text_factory = str). It is highly recommended that you instead
just switch your application to Unicode strings. 

if I don't use å,ä or ö in the html post everything is working fine.

why is this error occurring? how can i avoid it and still keep the data in the sqlite table readable?.

if the data need to be formated, how can formate it back when 'im accessing it the next time?

all help is appreciated! sry 4 my bad eng.

Android ORMLite create or Update statment block to update

i want to simulate Sqlite command as INSERT OR UPDATE with ORMlite library,i'm trying this link to get help to updte msg_id column if exist in database,but i can not.

List<MessagesList> id = G.messagesListsDao.queryForEq("msg_id", msg_id);
if (id == null) {
    G.messagesListsDao.create(
            new MessagesList(
                    0,
                    msg_id,
                    user_eitaa_id,
                    group_id,
                    msg_title,
                    msg_content,
                    msg_type,
                    stream_link,
                    preview_image_url,
                    msg_admin,
                    received_date,
                    avatar_url
            )
    );
} else {
    G.messagesListsDao.update(
            new MessagesList(
                    id.get(0).getId(),
                    msg_id,
                    user_eitaa_id,
                    group_id,
                    msg_title,
                    msg_content,
                    msg_type,
                    stream_link,
                    preview_image_url,
                    msg_admin,
                    received_date,
                    avatar_url
            )
    );
}
} catch (JSONException e) {
}

with G.messagesListsDao.queryForEq i'm trying to search msg_id, if exist in database must be update otherwise create new. i can not do it. please help me

SQLite compare query from 2 tables

I would like to create a new query for know how many product are in the store.

table: tb_store

+--------+------------+------------------+-----------+
| item_id| nome       |  date_out        |   date_in |
+--------+------------+------------------+-----------+
| 1      | Produ1     | null             | 2015-01-06|
| 2      | Produ1     | null             | 2015-01-06|
| 3      | Produ3     | null             | 2015-01-06|
| 3      | Produ3     | null             | 2015-01-06|
| 3      | Produ5     | null             | 2015-01-06|
+--------+------------+------------------+-----------+

table: tb_product

+--------+------------+
| item_id| nome       |
+--------+------------+
| 1      | Produ1     |
| 2      | Produ2     |
| 3      | Produ3     |
| 3      | Produ4     |
| 3      | Produ5     |
+--------+------------+

i have write this query:

select nome, count(nome) as pezzi from  tb_store where data_out is null or data_out="" group by nome order by pezzi desc

the result are:

+--------+------------+
| nome   | pezzi      |
+--------+------------+
| Produ1 | 2          |
| Produ3 | 2          |
| Produ5 | 1          |
+--------+------------+

i would like to obtain this result:

+--------+------------+
| nome   | pezzi      |
+--------+------------+
| Produ1 | 2          |
| Produ3 | 2          |
| Produ5 | 1          |
| Produ2 | 0          |
| Produ4 | 0          |
+--------+------------+

is possible? how can rewrite the query?

How can i print all values of a row in SQLite?

I want to print all values of a row in SQLite. I use Corona SDK (Lua)

Eg

column1     column2       column3

row1         3462           482 

row2        Value1        Value2
 

How can i choose, for example, all values of only row 1, and than print them?

Output:

3462

482

Android ORMLite create or Update statment block to update

i want to simulate Sqlite command as INSERT OR UPDATE with ORMlite library,i'm trying this link to get help to updte msg_id column if exist in database,but i can not.

List<MessagesList> id = G.messagesListsDao.queryForEq("msg_id", msg_id);
if (id == null) {
    G.messagesListsDao.create(
            new MessagesList(
                    0,
                    msg_id,
                    user_eitaa_id,
                    group_id,
                    msg_title,
                    msg_content,
                    msg_type,
                    stream_link,
                    preview_image_url,
                    msg_admin,
                    received_date,
                    avatar_url
            )
    );
} else {
    G.messagesListsDao.update(
            new MessagesList(
                    id.get(0).getId(),
                    msg_id,
                    user_eitaa_id,
                    group_id,
                    msg_title,
                    msg_content,
                    msg_type,
                    stream_link,
                    preview_image_url,
                    msg_admin,
                    received_date,
                    avatar_url
            )
    );
}
} catch (JSONException e) {
}

with G.messagesListsDao.queryForEq i'm trying to search msg_id, if exist in database must be update otherwise create new. i can not do it. please help me

How to copy Database from assets folder to /data/data/ folder android device

i have a class, it import database.sql from assets folder into /data/data/ of my phone , but i can't find that database in data/data folder. maybe i have a mistake. this is my code : SQLiteDBHelper.java

public class SQLiteDBHelper extends SQLiteOpenHelper {

private static final int DATABASE_VERSION = 1;

private static final String DB_NAME = "DATABASE_VIETNAM.sqlite";
private static final String DB_PATH = "/databases/";
static Context ctx;
public SQLiteDBHelper(Context context) {
    super(context, DB_NAME, null, DATABASE_VERSION);
    ctx = context;
}
public void CopyDataBaseFromAsset() throws IOException{

    InputStream myInput = ctx.getAssets().open(DB_NAME);
// Path to the just created empty db
String outFileName = getDatabasePath(); 
// if the path doesn't exist first, create it 
File f = new File(ctx.getApplicationInfo().dataDir + DB_PATH);
    if (!f.exists())
        f.mkdir(); 
// 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 static String getDatabasePath() {
    return ctx.getApplicationInfo().dataDir + DB_PATH
            + DB_NAME;
}
public SQLiteDatabase openDataBase() throws SQLException{
    File dbFile = ctx.getDatabasePath(DB_NAME);
    if (!dbFile.exists()) {
        try {
            CopyDataBaseFromAsset();
            System.out.println("Copying sucess from Assets folder");
        } catch (IOException e) {
            throw new RuntimeException("Error creating source database", e);
        }
    }

    return SQLiteDatabase.openDatabase(dbFile.getPath(), null, SQLiteDatabase.NO_LOCALIZED_COLLATORS |
            SQLiteDatabase.CREATE_IF_NECESSARY);
}
@Override
public void onCreate(SQLiteDatabase db) {

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

how can i fix it?

Android data from database to listview

I want to get data from another activity through OnActivityResult,put the data to sqllite and print to listview.What must I put to OnActivityResult method? I really seen many examples but I dont understand them and now.Thanks for answers. Sorry for my English:)

Android SQLite database not saving data after app is closed

I was looking for some examples to learn how to use SQLite in android and found this: http://ift.tt/1D8xXlV

I downloaded the project file and noticed that when I exit the app, if I have edited an entry or deleted one, it doesn't keep the changes afterwards. Shouldn't the database file just keep everything stored and up to date? What needs to be done to this code for it do so? Thanks!

Prevent Inconsistent Database After Delete

I have a table Users, Licenses and Programs. The Licenses table consists of the primary key of Users and Programs.

When I have a user entry in Users and Licenses as well deleting the entry in Users will not delete the Licenses entry hence leaving the database in an inconsistent state: A user identifier still is referenced but the user does not exist.

How would I go about implementing the automatic delete on cascade? I tried adding ON DELETE CASCADE to the definition of the Licenses table and to the delete statement itself but nothing happened.

Shouldn't this be the default behavior?

Note: I'm using SQLite JDBC.

Dumping SQLite database in time interval

I have XUL desktop app which stores data in SQLite database. To prevent too much data store in DB i want to create some kind of auto dumping system for my DB. My idea is to store timestamp of last DB dump so on every app boot to check for that timestamp and if it is older than week to dump DB. Are there any other methods of cleaning db in some time intervals?

Is there a way to remove the parenthesis that are automatically generated in CursorLoader()'s selection parameter?

First of all, I was attempting to use a fts3 sqlite table for searching on Android but it doesn't work since it doesn't by default support parenthesis.

I noticed that the CursorLoader() automatically puts the selection parameter (where clause) within parenthesis when executing.

So my question is, is there a way to remove the parenthesis?

Any work-around, comments, or answers would be appreciated.

Best way to deploy classic asp webapp with sqlite database?

I have a classic ASP front end which serves to collaboratively enter data into a sqlite database, meant to be used later in an android app. I need to deploy this ASP webapp onto an online server with sqlite odbc installed, because the only other option I know is to use my own computer as a server, and I don't think it would be a good idea to keep my fairly modest PC always on for 3 months or so.

The problem is, I can't find any server (preferably free) fitting these requirements.

Any ideas?

Alternative to the use of mathematical equations in Android

Because of the great difficulties with mathematical equations I had a trick I use on Android (Note that all these equations are only text, not the algebraic properties)

Writing in Word -> Convert to PDF -> then converted into picture

I want to put the images into Mysql server and download them into Android applications. I do not want users to have access image   I put them into the database. The cost of these actions are high, but to my mind, not the other way

If there's one that is easier to please help

How to adding marker from latitude and longitude i have in my sqlite database to google maps android

I have some data like name, address, type, image, lat and lng, in my sqlite db. I display the name and address in listview, if the list is clicked it will launching my db_parse.java or my new activity with display the name, address, type, image, lat and lng. Now i want to adding button "view in map" to the dp_parse.java for showing the location or add marker in google maps. How do I get the lat and lng value for the button and launching the map in maps activity?

This my DB class :

public class DB_Restoran extends SQLiteOpenHelper {
final static String DB_NAME = "db_restoran";

public DB_Restoran(Context context) {
    super(context, DB_NAME, null, 1);
    //Todo auto
}

@Override
public void onCreate(SQLiteDatabase db) {
    String sql = "CREATE TABLE IF NOT EXISTS restoran(_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, address TEXT, img BLOB, lat TEXT, lng TEXT)";
    db.execSQL(sql);

    ContentValues values = new ContentValues();
    values.put("_id", "1");
    values.put("name", "RM. Raya");
    values.put("address", "Jl.Endro Suratmin Sukarame");
    values.put("img", R.drawable.ic_listrestro);
    values.put("lat", "-5.384402");
    values.put("lng", "105.295443");
    db.insert("restoran", "_id", values);

    values.put("_id", "2");
    values.put("name", "RM. Saudara");
    values.put("address", "Jl.P.Tirtayasa Sukabumi");
    values.put("img", R.drawable.dua_saudara);
    values.put("lat", "-5.384402");
    values.put("lng", "105.295443");
    db.insert("restoran", "_id", values);

    values.put("_id", "3");
    values.put("name", "RM. Wito");
    values.put("address", "Jl.Arief Rahman Hakim Blok A No.16");
    values.put("img", R.drawable.mbok_wito);
    values.put("lat", "-5.384402");
    values.put("lng", "105.295443");
    db.insert("restoran", "_id", values);

    values.put("_id", "4");
    values.put("name", "Cafe");
    values.put("address", "Jl.Arief Rahman Hakim Blok A No.29");
    values.put("img", R.drawable.cafe_babe);
    values.put("lat", "-5.384402");
    values.put("lng", "105.295443");
    db.insert("restoran", "_id", values);
}

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

This my Menu Class

public class Menu_Restoran extends ActionBarActivity {
    protected ListView lv;
    protected ListAdapter adapter;
    SQLiteDatabase db;
    Cursor cursor;

    @SuppressWarnings("deprecation")
    @Override
    protected void onCreate(Bundle SavedInstanceState) {
        super.onCreate(SavedInstanceState);
        setContentView(R.layout.menu_restoran);

        // enable up/back button
        getSupportActionBar().setDisplayHomeAsUpEnabled(true);

        db = (new DB_Restoran(this)).getWritableDatabase();
        lv = (ListView) findViewById(R.id.list);

        try {
            cursor = db.rawQuery("SELECT * FROM restoran ORDER BY name ASC", null);
            adapter = new SimpleCursorAdapter(this, R.layout.list_view, cursor,
                    new String[]{"name", "address", "img"},
                    new int[]{R.id.item, R.id.textView1, R.id.icon});
            lv.setAdapter(adapter);
            lv.setTextFilterEnabled(true);
            lv.setOnItemClickListener(new AdapterView.OnItemClickListener() {
                @Override
                public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
                    detail(position);

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

public void detail(int position) {
int img = 0;
String _id = "";
    String name = "";
    String address = "";
    String lat = "";
    String lng = "";
    if (cursor.moveToFirst()) {
        cursor.moveToPosition(position);
        img = cursor.getInt(cursor.getColumnIndex("img"));
        name = cursor.getString(cursor.getColumnIndex("name"));
        address = cursor.getString(cursor.getColumnIndex("address"));
        lat = cursor.getString(cursor.getColumnIndex("lat"));
        lng = cursor.getString(cursor.getColumnIndex("lng"));
    }

    Intent iIntent = new Intent(this, DBResto_Parse.class);
    iIntent.putExtra("dataIMG", img);
    iIntent.putExtra("dataName", name);
    iIntent.putExtra("dataAddress", address);
    iIntent.putExtra("dataLat", lat);
    iIntent.putExtra("dataLng", lng);
    setResult(RESULT_OK, iIntent);
    startActivityForResult(iIntent, 99);

}
}

This is my DB_Parse Class when i want to add button

public class DBResto_Parse extends ActionBarActivity {
    ImageView Img;
    TextView tv_name, tv_address, id;
    Gallery gallery;
    ImageSwitcher imageSwitcher;
    Integer[] imageIDs = new Integer[2];
    int msg_img;

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

        // enable up/back button
        getSupportActionBar().setDisplayHomeAsUpEnabled(true);

        Button btnlihat = (Button) findViewById(R.id.lihatmap);
        btnlihat.setOnClickListener(new View.OnClickListener() {
//This is when a want to add button "view in map"

         
        Intent iIdentifikasi = getIntent();
        msg_img = iIdentifikasi.getIntExtra("dataIMG", 0);
        String msg_name = iIdentifikasi.getStringExtra("dataName");
        String msg_address = iIdentifikasi.getStringExtra("dataAddress");
        Img = (ImageView) findViewById(R.id.iv_detail);
        tv_name = (TextView) findViewById(R.id.tvName);
        tv_address = (TextView) findViewById(R.id.tvAddress);
        Img.setImageResource(msg_img);
        tv_name.setText(msg_name);
        tv_address.setText(msg_address);
    }
}

Thanks for your help

Can't import DATABASE into AndroidProject

I created an assets forder in my project, after that i copied my database.sql file into assets. I import database into my project i have a problem, when i run my app , it stopped, i can't see the logcat. i think someone can help me.

this is my SQLiteDBHelper.java

package com.ngo.evan.testmap;

private final Context myContext;

public SQLiteDBHelper(Context context) {
    super(context, DB_NAME, null, 1);
    this.myContext = context;
}

public void createDataBase() throws IOException{

    boolean dbExist = checkDataBase();
    if(dbExist){
        //do nothing - database already exist
    }else{
        //By calling this method and empty database will be created into the default system path
        //of your application so we are gonna be able to overwrite that database with our database.
        this.getReadableDatabase();
        try {
            copyDataBase();

        } catch (IOException e) {

            throw new Error("Error copying database");
        }
    }
}

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 does't exist yet.
    }

    if(checkDB != null){
        checkDB.close();
    }

    return checkDB != null ? true : false;
}

private void copyDataBase() throws IOException{

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

    // Path to the just created empty db
    String outFileName = 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();
}

public void openDataBase() throws SQLException {
    //Open the database
    String myPath = DB_PATH + DB_NAME;
    myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
}

public synchronized void close() {
    if(myDataBase != null)
        myDataBase.close();
    super.close();
}


@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
}

@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
}

}

How can i fix it?

Check if int from database string is equal to certain value

I want to check if a Database variable has the value 5 and then change an image. The Log.d I do before I check with my if statment gives me the value 5 and the headline I want to check for, so the Data is in the Database. But my if-statement is not executed, "Done" is not logged and the image is not changed. Maybe I am using a wrong way to parse the cursor.getString(0) to Integer? There is no error logged.

DbHelper dbh  = new DbHelper(context);
    Cursor cursor = dbh.getScore(dbh);
    cursor.moveToFirst();
    if (cursor.moveToFirst()) {
        do {
            Log.d("Database", cursor.getString(0) + cursor.getString(1));
            if (Integer.parseInt(cursor.getString(0))== 5 && Headline.toString().equals(cursor.getString(1))){
                Log.d("Database","Done");
                ImageDone.setImageResource(R.drawable.markerdone);
            }
        }
        while(cursor.moveToNext());
    }
    cursor.close();

Let user add/remove favorites from drawer/listview from position and save it to SQLite db

I have a sqlite database prepopulated with some data and I would like to have method/function that enables the user to "save" the info in his favorites and display it. The only problem is i have problems with the queries to work properly. I don't understand how to get the position from the current displayed data (from the db) in the activity and save it to the database. I have a column called "Favorites" in db with the default value set to "no" and when the user click a button the value should change to "yes".

The user click a row in listview populated from the DB and it starts a new activity with intent.putExtra(data from db). The new activity displays the data in a single textview. In this new activity i've made a navigation drawer/listview(shows by sliding or "hamburger meny"). In this nav drawer I have a "button" that i would like to get the id from the db according to the current displayed data and change the value of the column "Favorite". In a another class i used

Cursor c = dbHelper.showBook(Integer.toString(position + 1));

Got this help from another recent thread of mine and it worked fine but my new problem is that i have if statements as below. The start of intent works fine. But I want to call a method from my dbHelper to update rows in db. And the code above dont work(but no errors) when i try to use it. The toast shows but nothing is happening in the DB.

myDrawerListView.setOnItemClickListener(new AdapterView.OnItemClickListener(){

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

if (position == 0){
                Intent iHome = new Intent(DisplayBook.this, MyAccont.class);
                startActivity(iHome);
 else if (position == 3){
                dbHelper.addFavorites(Integer.toString(position+ 1));
                Toast.makeText(getApplicationContext(), "Added to favorites",    Toast.LENGTH_SHORT).show();

the dbHelper.addFavorites: (the addFavs param is supposed to be the id/pos of the db row as in the working code above) public void addFavorites(String addFavs){

SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(COL_FAV, "yes");
String whereClause = COL_ID + " = ?" ;
String[] whereArgs = new  String[] { addFavs};

          db.update(
          TABLE_NAME,
          values,
          whereClause,
          whereArgs);

}

I've tried a fair amount of versions och different codes and stuff but nothing works and now I'm asking for som advice on this. How can i do it in a good an efficient way? Thank you so much for any help at all!

Running django from a symlink directory so that __file__ reports symlink path rather than real path

I have a fairly basic django 1.8 site, the environment structure is as such:

/home/x/releases/v1
/home/x/releases/v2
/home/x/live-site -> /x/releases/v2
/home/x/database

My goal is that my sqlite DB config tells django that I want my database to be created in /home/x/database/blah.sqlite3.

BASE_DIR = os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': os.path.join(BASE_DIR, '../database/blah.sqlite3'),
    }
}

However, when running in the django shell (executed from /home/x/live-site) OR via apache with mod_wsgi (where there are NO references to /home/x/releases) __file__ is reported as /x/releases/v2/project/settings/PROD.py (I've rejigged my settings file structure from the default). Obviously that means when Django tries to construct the path to the DB it is ending up with /x/releases/v2/

Before I resort to something stupid like hardcoding the DB directory path as a config item, or accounting for my environment's deployment directory structure within my code...

Is there a way to prevent symlink paths being converted to their real path targets? Basically... I'm guessing some alternative to __file__? I've messed around with os.path.abspath and realpath, but can't seem to make any headway.

Sqlite query Select only when equals two values

"SELECT ? FROM TableSales....

I have two Columns, one is for Manufacturer and the other one is for Model. I want to count how many times they exists in the table. The problem is to use a filter which only counts those, where the Manufacturer and the Model column are equal. Cause it could happen that the Model Column or the Manufacturer Column are equal but not both together.

Reading from assets directs to wrong path?

I'm trying to use sqlite helper class, I have my db_map at /assets folder. sqlite helper should copy it from there to data/data/http://ift.tt/1FKBCa3

what happens is that I get a FileNotFound exception.

While debugging and using assetManager.list(""). I found that I have "images", "sounds", "webkit" files in the list. what is weird is I don't have those and only my db_map file.

code :

AssetManager assetManager = context.getResources().getAssets();
myInput = assetManager.open("db_map");

Check if data was written in SQLite Database

I want to save the Score from a Quiz in a SQLite Database and change an image in another activity if the Score is 5. There is no error shown, but even if I score 5 the image won't change... How can I log the content of my database to check if the score was added or how can I find the mistake?

DB Helper:

public class DbHelper extends SQLiteOpenHelper {
private static final int DATABASE_VERSION = 7;
private static final String DATABASE_NAME = "CE";

public static final String SCORE_TABLE = "score";
public static final String COLUMN_ID = "ID";
public static final String COLUMN_SCORE = "SCORE";
public static final String COLUMN_MARKERID = "MARKERID";

 private SQLiteDatabase dbase;

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

 }
@Override
public void onCreate(SQLiteDatabase db) {

dbase= db;

String create_query = "CREATE TABLE IF NOT EXITS " + SCORE_TABLE + " ( "
        + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
        + COLUMN_SCORE + " INTEGER, "
        + COLUMN_MARKERID + " TEXT) ";
db.execSQL(create_query);
}

public void addScore (DbHelper dbh, Integer score, String markerID) {
    dbase = dbh.getWritableDatabase();
    ContentValues cv = new ContentValues();
    cv.put(COLUMN_SCORE, score);
    cv.put(COLUMN_MARKERID, markerID);
    dbase.insert(SCORE_TABLE, null, cv);
}

public Cursor getScore(DbHelper dbh) {
    dbase = dbh.getReadableDatabase();
    String columns[] = {COLUMN_SCORE, COLUMN_MARKERID};
    Cursor cursor = dbase.query(SCORE_TABLE, columns, null, null, null, null, null);
    return cursor;
}

Write the Score into the Database after completing the Quiz:

public class ResultActivity extends Activity {

String markerID;
int score;
TextView t=(TextView)findViewById(R.id.textResult);
Button saveButton = (Button) findViewById(R.id.saveButton);
Context context = this;

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

Bundle b = getIntent().getExtras();
score = b.getInt("score");
markerID = b.getString("markerID");
}

saveButton.setOnClickListener(new View.OnClickListener() {
    @Override
    public void onClick(View v) {
        DbHelper dbh = new DbHelper(context);
        dbh.addScore(dbh,score,markerID);
        Intent intent = new Intent(ResultActivity.this, Discover.class);
            intent.putExtra("MarkerID", markerID);
            startActivity(intent);
    }
});
}

Discover class -> Check if score is 5 and change image if:

 DbHelper dbh  = new DbHelper(context);
    Cursor cursor = dbh.getScore(dbh);
    cursor.moveToFirst();
    if (cursor.moveToFirst()) {
        do {
            if (Integer.parseInt(cursor.getString(0))== 5 && InfoUeberschrift.toString().equals(cursor.getString(1))){
                ImageDone.setImageResource(R.drawable.markerdone);
            }
        }
        while(cursor.moveToNext());
    }
    cursor.close();
}

Runtime Exeption: NullPointerExeption with SQLite Database

I have an Andorid App with Google Maps and Markers. If you click at one marker you get to an "Home Screen" of the Location where you can start a quiz with a Button. After going through 5 Multiple Choice Questions the Score is shown (1 - 5 Points). To this Point everthing was working. Now I want to save the Score in a SQLite Database and change an image at the "Home Screen" if the Score is 5. Now everytime I test and click on a Marker to open the "Home Screen"(where the image should be changed if Score is = 5) of a specific location the App crashes. Until now there is no data in the table because I never ran through a quiz to save a score. I would be very thankful if someone finds a mistake!

Here is my Code:

Database Helper:

public class DbHelper extends SQLiteOpenHelper {
private static final int DATABASE_VERSION = 7;
private static final String DATABASE_NAME = "CE";

public static final String SCORE_TABLE = "score";
public static final String COLUMN_ID = "ID";
public static final String COLUMN_SCORE = "SCORE";
public static final String COLUMN_MARKERID = "MARKERID";

 private SQLiteDatabase dbase;

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

}
@Override
public void onCreate(SQLiteDatabase db) {
    //Hier alle Tables erstellen

    String create_query = "CREATE TABLE IF NOT EXITS " + SCORE_TABLE + " ( "
            + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
            + COLUMN_SCORE + " INTEGER, "
            + COLUMN_MARKERID + " TEXT) ";
    db.execSQL(create_query);
}

public void addScore (DbHelper dbh, Integer score, String markerID) {
    dbase = dbh.getWritableDatabase();
    ContentValues cv = new ContentValues();
    cv.put(COLUMN_SCORE, score);
    cv.put(COLUMN_MARKERID, markerID);
    dbase.insert(SCORE_TABLE, null, cv);
}

public Cursor getScore(DbHelper dbh) {
    dbase = dbh.getReadableDatabase();
    String columns[] = {COLUMN_SCORE, COLUMN_MARKERID};
    Cursor cursor = dbase.query(SCORE_TABLE, columns, null, null, null, null, null);
    return cursor;
}

Write the Score into the Database after completing the Quiz in ResultActivity:

public class ResultActivity extends Activity {

String markerID;
int score;
TextView t=(TextView)findViewById(R.id.textResult);
Button saveButton = (Button) findViewById(R.id.saveButton);
Context context = this;

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

    Bundle b = getIntent().getExtras();
    score = b.getInt("score");
    markerID = b.getString("markerID");
}

saveButton.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View v) {
            DbHelper dbh = new DbHelper(context);
            dbh.addScore(dbh,score,markerID);
        }
    });
}

Look at the "Home Screen" what Score is saved on this marker:

public class Discover extends ActionBarActivity {

TextView InfoHeadline;
ImageView ImageDone;
Button QuizButton;

String markerID;

Context context;

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

    InfoHeadline = (TextView)findViewById(R.id.InfoUeberschrift);
    ImageDone =(ImageView)findViewById(R.id.imageDone);
    QuizButton = (Button)findViewById(R.id.QuizButton);

//get markerID from previous google maps activity
    if (savedInstanceState == null) {
        Bundle extras = getIntent().getExtras();
        if(extras == null) {
            markerID= null;
        } else {
            markerID= extras.getString("MarkerID");
        }
    } else {
        markerID = (String) savedInstanceState.getSerializable("MarkerID");
    }

    InfoHeadline.setText(markerID);

    DbHelper dbh  = new DbHelper(context);
    Cursor cursor = dbh.getScore(dbh);
    cursor.moveToFirst();
    do {
        if (Integer.parseInt(cursor.getString(0))== 5 && InfoHeadline.toString().equals(cursor.getString(1))){
            ImageDone.setImageResource(R.drawable.markerdone);
        }
    }while(cursor.moveToNext());
}

public void StartQuiz (View v) {
    Intent intent = new Intent(Discover.this,QuizActivity.class);
    intent.putExtra("MarkerID", markerID);
    startActivity(intent);
}
}

Here is the crash report:

java.lang.RuntimeException: Unable to start activity ComponentInfo{de.marmor.discover/de.marmor.discover.Discover}: java.lang.NullPointerException: Attempt to invoke virtual method 'android.database.sqlite.SQLiteDatabase android.content.Context.openOrCreateDatabase(java.lang.String, int, android.database.sqlite.SQLiteDatabase$CursorFactory, android.database.DatabaseErrorHandler)' on a null object reference
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2345)
        at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2407)
        at android.app.ActivityThread.access$800(ActivityThread.java:149)
        at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1324)
        at android.os.Handler.dispatchMessage(Handler.java:102)
        at android.os.Looper.loop(Looper.java:211)
        at android.app.ActivityThread.main(ActivityThread.java:5321)
        at java.lang.reflect.Method.invoke(Native Method)
        at java.lang.reflect.Method.invoke(Method.java:372)
        at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1016)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:811)
 Caused by: java.lang.NullPointerException: Attempt to invoke virtual method 'android.database.sqlite.SQLiteDatabase android.content.Context.openOrCreateDatabase(java.lang.String, int, android.database.sqlite.SQLiteDatabase$CursorFactory, android.database.DatabaseErrorHandler)' on a null object reference
        at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:223)
        at android.database.sqlite.SQLiteOpenHelper.getReadableDatabase(SQLiteOpenHelper.java:187)
        at de.marmor.discover.DbHelper.getScore(DbHelper.java:71)
        at de.marmor.discover.Discover.onCreate(Discover.java:46)
        at android.app.Activity.performCreate(Activity.java:5933)
        at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1105)
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2298)`

...

sqlite : time difference between two dates in decimals

I have two two timestamp fields (START,END) and a TIME_DIFF field which is of Integer type. I am trying to calculate the time between START and END field.. I created a trigger to do that :

CREATE TRIGGER [TIME_DIFF]
AFTER UPDATE OF [END]
ON [KLOG]
BEGIN
update klog set TIME_DIFF =
cast(
      (      
        strftime('%s',KLOG.END) -        
 strftime('%s',KLOG.START) 
) as INT
) / 60/60;
END

This gives me result in whole hours.Anything between 0 and 59 minutes is neglected.

I am wondering how can I modify this trigger so it displays in decimals?

Meaning, if the time difference is 1 hour 59 minutes the result would display 1.59.If the time difference is 35 minutes it would display 0.35.

no columns in mysqlite database

i made an android eclipse project that uses msqlite as database, there are some error that occur when i try to run the project in the phone. when i click view button(to view my database)it says that there are no columns in my database, can someone help me why this is happen.

this is my coding database file:

public class Translator {

public static final String KEY_ROWID ="_id";
public static final String KEY_DESC ="page_desc";

private static final String DATABASE_NAME  ="Translatordb";
private static final String DATABASE_TABLE ="pageTable";
private static final int DATABASE_VERSION  = 1;

private DbHelper ourHelper;
private final Context ourContext;
private SQLiteDatabase ourDatabase;

public static class DbHelper extends SQLiteOpenHelper{

    public DbHelper(Context context) {  
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        // TODO Auto-generated constructor stub
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        db.execSQL("CREATE TABLE " + DATABASE_TABLE + " (" +
        KEY_ROWID + " STRING PRIMARY KEY, " +
        KEY_DESC + " TEXT NOT NULL);"
                );
    }


    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
        db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
        onCreate(db);
    }

}

 public Translator(Context c){
     ourContext = c;
}

public Translator open() throws SQLException{
    ourHelper = new DbHelper(ourContext);
    ourDatabase = ourHelper.getWritableDatabase();
    return this;
}

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

public long createEntry(String id, String description){
    ContentValues cv = new ContentValues();
    cv.put(KEY_ROWID,id);
    cv.put(KEY_DESC,description);
    return ourDatabase.insert(DATABASE_TABLE,null, cv);
}

public long createEntry1(String id, String description){
    ContentValues cv = new ContentValues();
    cv.put(KEY_ROWID,"P01");
    cv.put(KEY_DESC,"Snow White");
    return ourDatabase.insert(DATABASE_TABLE,null, cv);
}


public String getData() {
    // TODO Auto-generated method stub
    String[] columns = new String[]{KEY_ROWID,KEY_DESC};
    Cursor c = ourDatabase.query(DATABASE_TABLE, columns,null,null,null,null,null);
    String result =" ";

    int iRow = c.getColumnIndex(KEY_ROWID);
    int iDescription = c.getColumnIndex(KEY_DESC);

 for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()){
    result =result + c.getString(iRow)+ "  " +c.getString(iDescription) +"  \n";
    }

    return result;

}   

main Activity.java

public class MainActivity extends Activity implements OnClickListener {


static final String ACTION_SCAN = "com.google.zxing.client.android.SCAN";
Button view;
@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);

    view =(Button) findViewById(R.id.viewSQL);
    view.setOnClickListener(this);

}



  //product qr code mode
public void scanQR(View v) {
    try {
        //start the scanning activity from the com.google.zxing.client.android.SCAN intent
        Intent intent = new Intent(ACTION_SCAN);
        intent.putExtra("SCAN_MODE", "QR_CODE_MODE");
        startActivityForResult(intent, 0);


    } catch (ActivityNotFoundException anfe) {
        //on catch, show the download dialog
        showDialog(MainActivity.this, "No Scanner Found", "Download a scanner code activity?", "Yes", "No").show();
    }
}

//alert dialog for downloadDialog
private static AlertDialog showDialog(final Activity act, CharSequence title, CharSequence message, CharSequence buttonYes, CharSequence buttonNo) {
    AlertDialog.Builder downloadDialog = new AlertDialog.Builder(act);
    downloadDialog.setTitle(title);
    downloadDialog.setMessage(message);
    downloadDialog.setPositiveButton(buttonYes, new DialogInterface.OnClickListener() {
        @Override
        public void onClick(DialogInterface dialogInterface, int i) {
            Uri uri = Uri.parse("market://search?q=pname:" + "com.google.zxing.client.android");
            Intent intent = new Intent(Intent.ACTION_VIEW, uri);
            try {
                act.startActivity(intent);
            } catch (ActivityNotFoundException anfe) {

            }
        }
    });
    downloadDialog.setNegativeButton(buttonNo, new DialogInterface.OnClickListener() {
        @Override
        public void onClick(DialogInterface dialogInterface, int i) {
        }
    });
    return downloadDialog.show();
}

//on ActivityResult method
@Override
public void onActivityResult(int requestCode, int resultCode, Intent intent) {
    if (requestCode == 0) {
        if (resultCode == RESULT_OK) {
            //get the extras that are returned from the intent
            String contents = intent.getStringExtra("SCAN_RESULT");
            String format = intent.getStringExtra("SCAN_RESULT_FORMAT");
            Toast toast = Toast.makeText(this, "Content:" + contents + " Format:" + format, Toast.LENGTH_LONG);
            toast.show();
        }
    }
}

public void onClick(View arg0) {
    switch(arg0.getId()){

        case R.id.viewSQL:   

            Translator entry = new Translator(MainActivity.this);
            entry.open();
            entry.close();
            Intent i = new Intent("com.example.scanner.SQLVIEW");
            startActivity(i);
            break;
        }
    }

SQLView.java

public class SQLView extends Activity {

@Override
protected void onCreate(Bundle savedInstanceState) {
    // TODO Auto-generated method stub
    super.onCreate(savedInstanceState);
    setContentView(R.layout.sqlview);
    TextView tv =(TextView) findViewById(R.id.tvSQLint);
    Translator info = new Translator(this);
    info.open();
    String data = info.getData();
    info.close();
    tv.setText(data);


}

this is my error:

java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.scanner/com.example.scanner.SQLView}: android.database.sqlite.SQLiteException: no such column: page_desc (code 1): , while compiling: SELECT _id, page_desc FROM pageTable

can someone tell me where i do wrong???

Fetching values from Cursor but Application Crashes

I am trying to fetch values from the database when I click on the view button which is another activity for navigation when I click on the view all button the CustodianViewActivity is triggered to display all the values from the database on a list view but in my case the application crashes every time I try to view the data from the database. Am not sure where I am going wrong.

Database Class

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

        public class DBHandler extends SQLiteOpenHelper   {
         private static DBHandler instance;
        // Database Name
        public static final String DATABASE_NAME ="AssetDB.db";

        //Database version
        public static final int  Databasevr = 1;

        //Custodian Table Name
        public static final String TABLE_CUSTODIAN = " Custodian";
        // Columbs in the Custodian Table

        public static final String CUSTODIAN_ID = "_CustID";
        public static final String CUSTODIAN_NAME = "CustName";
        public static final String CUSTODIAN_DESIGNATION = "CustDesign";
        public static final String CUSTODIAN_DEPARTMENT = "CustDepart";private static final String CREATE_TABLE_CUSTODIAN = "CREATE TABLE" + TABLE_CUSTODIAN  + "("
            + CUSTODIAN_ID  + " INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
            + CUSTODIAN_NAME +  " TEXT NOT NULL,"
            + CUSTODIAN_DESIGNATION +   " TEXT NOT NULL,"
            + CUSTODIAN_DEPARTMENT + " TEXT NOT NULL" + ");";
// constructor passing parameter passing Database name and Database version
     public DBHandler(Context ct)
     {
         super(ct, DATABASE_NAME, null, Databasevr);
     }



        @Override
        public void onCreate(SQLiteDatabase db) {
            // TODO Auto-generated method stub
            // Creating the tables
            db.execSQL(CREATE_TABLE_CUSTODIAN);
            db.execSQL(CREATE_TABLE_ASSET);
            Log.d("Tables","Tables have been created");
        }

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

            // dropping the tables
            db.execSQL("DROP TABLE IF EXISTS " + CREATE_TABLE_CUSTODIAN );
            db.execSQL("DROP TABLE IF EXISTS " + CREATE_TABLE_ASSET);

            // recreate the tables
            onCreate(db);

        }



    public Cursor getAllCustodians(){
    try {
        SQLiteDatabase db_database = getWritableDatabase();

        Cursor cursor = db_database.rawQuery("SELECT * FROM" + TABLE_CUSTODIAN, null);

        if (cursor != null) {
            return cursor;
        } else {
            return null;
        }
    }

    catch(Exception e)
    {
        return null;
    }

    }}

CustodianViewActivity

package com.example.nfcams;

import android.app.Activity;
import android.content.Context;
import android.database.Cursor;
import android.os.Bundle;
import android.os.Handler;
import android.view.LayoutInflater;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.view.ViewGroup;
import android.widget.CursorAdapter;
import android.widget.ListView;
import android.widget.TextView;

public class CustodianViewActivity extends Activity {



    ListView CustodianListview;
    DBHandler db_database;


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



        db_database = new DBHandler(getApplicationContext());
        CustodianListview = (ListView) findViewById(R.id.custodianlistView);




        new Handler().post(new Runnable() {
            @Override
            public void run() {

                populateCustoListView();

            }
        });


    }



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



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

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

        return super.onOptionsItemSelected(item);
    }




    private void populateCustoListView()
   {

       Cursor c = db_database.getAllCustodians();

       CustodianListview.setAdapter(new CustodiansListAdapter(this,c));


   }



    private class CustodiansListAdapter extends CursorAdapter
    {
        private Cursor cursor;
        public CustodiansListAdapter(Context context, Cursor cur) {
            super(context, cur);
            cursor = cur;

        }

        @Override
        public void bindView(View view, Context context, Cursor cursor) {


           TextView custID = (TextView) view.findViewById(R.id.Custodianid_view);
           int CustodID = cursor.getInt(cursor.getColumnIndex("_CustID"));
           custID.setText(String.valueOf(CustodID));

            TextView name = (TextView) view.findViewById(R.id.Custodianname_view);
            String Custname = cursor.getString(cursor.getColumnIndex("CustName"));
            name.setText(Custname);

            TextView Designation = (TextView) view.findViewById(R.id.CustodianDesignation_view);
            String CustDesignation = cursor.getString(cursor.getColumnIndex("CustDesign"));
            Designation.setText(CustDesignation);

            TextView Department = (TextView) view.findViewById(R.id.CustodianDepartment_view);
            String CustDepartment = cursor.getString(cursor.getColumnIndex("CustDepart"));
            Department.setText(CustDepartment);

           }

        @Override
        public View newView(Context context, Cursor cursor, ViewGroup parent) {

            LayoutInflater inflater = LayoutInflater.from(context);
            View retView = inflater.inflate(R.layout.custodianrow_views, parent, false);
            bindView(retView,context,cursor);
            return retView;
        }




    }



}

Custodianview Activity layout

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



    <ListView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:id="@+id/custodianlistView"
        android:layout_centerHorizontal="true"
        android:layout_alignParentTop="true" />

</RelativeLayout>

List view layout

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://ift.tt/nIICcg"
    android:layout_width="fill_parent"
    android:layout_height="wrap_content"
    android:background="@color/list_item_bg"
    android:descendantFocusability="blocksDescendants" >

    <RelativeLayout
        android:id="@+id/layout_item"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content" >

        <TextView
            android:id="@+id/Custodianname_view"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:padding="6dp" />

        <TextView
            android:id="@+id/Custodianid_view"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_toRightOf="@+id/Custodianname_view"
            android:padding="6dp" />

        <TextView
            android:id="@+id/CustodianDesignation_view"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_below="@+id/Custodianid_view"
            android:padding="6dp" />

        <TextView
            android:id="@+id/CustodianDepartment_view"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_below="@+id/CustodianDesignation_view"
            android:padding="6dp" />
    </RelativeLayout>

    <View
        android:layout_width="match_parent"
        android:layout_height="1dp"
        android:layout_below="@+id/layout_item"
        android:background="@color/view_divider_color" />

</RelativeLayout>

Error Log

05-30 09:42:16.471    1334-1334/com.example.nfcams E/AndroidRuntime﹕ FATAL EXCEPTION: main
    Process: com.example.nfcams, PID: 1334
    java.lang.IllegalArgumentException: column '_id' does not exist
            at android.database.AbstractCursor.getColumnIndexOrThrow(AbstractCursor.java:303)
            at android.widget.CursorAdapter.init(CursorAdapter.java:172)
            at android.widget.CursorAdapter.<init>(CursorAdapter.java:120)
            at com.example.nfcams.CustodianViewActivity$CustodiansListAdapter.<init>(CustodianViewActivity.java:95)
            at com.example.nfcams.CustodianViewActivity.populateCustoListView(CustodianViewActivity.java:84)
            at com.example.nfcams.CustodianViewActivity.access$000(CustodianViewActivity.java:17)
            at com.example.nfcams.CustodianViewActivity$1.run(CustodianViewActivity.java:42)
            at android.os.Handler.handleCallback(Handler.java:733)
            at android.os.Handler.dispatchMessage(Handler.java:95)
            at android.os.Looper.loop(Looper.java:157)
            at android.app.ActivityThread.main(ActivityThread.java:5356)
            at java.lang.reflect.Method.invokeNative(Native Method)
            at java.lang.reflect.Method.invoke(Method.java:515)
            at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1265)
            at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1081)
            at dalvik.system.NativeStart.main(Native Method)

SQLite best way to save and traverse arrays of strings

I have a table that looks like this.

   | id | coords |
   |  0 | [1,0],[4,3],[4,9],[9,3],[1,8]     
   |  1 | [3,6],[3,8],[7,4],[5,2],[2,1]
   .. and more

There will be around 70k-100k rows at most, and the CPU is not very powerful.

What is the fastest and least cpu intensive SQLite statement i can use to determine which id has any given coordinate? No two id's share a coordinate.

Example.

SELECT * FROM mytable WHERE coords LIKE '%[[]3,8]%'

I imagine the LIKE statement above will get pretty intensive right?

Why is listview that uses loaders empty with fts3 table on Android?

Basically my listview that uses a CursorLoader to load data was displaying fine with my normal table, but once I converted my table to fts3 (to use MATCH for searching) it stopped displaying data.

Table declaration:

 public static final String NOTES_SCHEMA="CREATE VIRTUAL TABLE "+TABLE_NAME+" USING fts3("+
            "_id INTEGER PRIMARY KEY"+COMMA+NOTE_TITLE+" TEXT"+COMMA+NOTE_TEXT+" TEXT"+COMMA+TRASH_STATUS+" INTEGER DEFAULT 0"+COMMA+TIME+" INTEGER"+");";

onCreateCursorLoader:

case LOADER_NOTELIST_ID:
           String[] projection = new String[]{NotesModel.NotesTable._ID, NotesModel.NotesTable.NOTE_TITLE, NotesModel.NotesTable.NOTE_TEXT,NotesModel.NotesTable.TIME,NotesModel.NotesTable.TRASH_STATUS};
            String sortOrder = NotesModel.NotesTable.TIME+ " DESC";
            String where = NotesModel.NotesTable.TRASH_STATUS+"=0";
            CursorLoader cursorLoader = new CursorLoader(getApplicationContext().getApplicationContext(), uri, projection, where, null , sortOrder
            );

            return cursorLoader;

I also noticed when I removed the where argument, it worked (but i need that argument).

Content Provider:

 public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder) {

    SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
    Cursor cursor;

    switch (uriMatcher.match(uri)) {
        case NOTES_LIST://display the whole list, for main activity
            qb.setTables(NotesModel.NotesTable.TABLE_NAME);//set table to be queried
           break;

        case NOTES_ITEM:
            qb.setTables(NotesModel.NotesTable.TABLE_NAME);
            qb.appendWhere(NotesModel.NotesTable._ID + " = "+ uri.getLastPathSegment());
            break;

        default:
            throw new IllegalArgumentException("Invalid URI: " + uri);
    }

    cursor = qb.query(db,projection,selection,selectionArgs,null,null,sortOrder);
    cursor.setNotificationUri(getContext().getContentResolver(), uri);//observer for loader
    return cursor;
}

Any comments or solutions would highly be appreciated.

Store Core Data Sqlite in NSLibraryDirectory or NSApplicationSupportDirectory?

What is the difference between NSLibraryDirectory and NSApplicationSupportDirectory? Which one is the best place to store the Core Data .sqlite file if I am planning on using a syncing service?

I currently store my .sqlite file in NSLibraryDirectory. If NSApplicationSupportDirectory turns out to be the better choice, how will changing this affect my current users? Can it easily be changed, and what are the steps I should take to ensure a seamless transition for my existing users?

Copy Website Data from Cache Memory To Sqlite in IOS

I am making a Web Browser in IOS in which I want to implement feature of offline view of website like "Reading List" in Safari.

The logic I want to use is to Load the website data from Cache Memory to Sqlite Database.

Is it possible? If yes can anyone give the sample code or any other method to implement this. Thank you in advance.

ADO.Net reporting empty data type in SQLite

I am trying to dynamically get the schema of one of my views in SQLite using C#. I am using this code:

using (var connection = new SQLiteConnection(ConnectionString))
{
    connection.Open();
    using (DataTable columns = connection.GetSchema("Columns"))
    {
        foreach (DataRow row in columns.Rows)
        {
            string dataType = ((string) row["DATA_TYPE"]).Trim();
            // doing irrelevant other stuff here
        }
    }
}

Its working perfectly for all my tables and views except for one view. For some reason, the data type for the field called SaleAmount is coming up blank. There is nothing in the row["DATA_TYPE"] element.

Here is my view:

SELECT [Order Subtotals].Subtotal AS SaleAmount, 
              Orders.OrderID, 
           Customers.CompanyName, 
              Orders.ShippedDate
FROM Customers 
JOIN Orders ON Customers.CustomerID = Orders.CustomerID
JOIN [Order Subtotals] ON Orders.OrderID = [Order Subtotals].OrderID 
WHERE ([Order Subtotals].Subtotal >2500) 
AND (Orders.ShippedDate BETWEEN DATETIME('1997-01-01') And DATETIME('1997-12-31'))

I am using the standard System.Data.SQLite libraries. Anyone have any idea why this would come up blank? Like I said, it is happening only on this one field in this one view.

LINQ to SQLite Cannot Update: "Store update, insert, or delete statement affected an unexpected number of rows"

With System.Data.SQLite and LINQ to Entities, I'm unable to do a simple update.

This code

using (Entities context = new Entities()) {
    var Obj = context.MyTable.Where(m => m.Title.StartsWith("Alaska")).FirstOrDefault();
    Obj.Artist = "A";
    context.SaveChanges();
}

Throws this exception

A first chance exception of type 'System.Data.Entity.Infrastructure.DbUpdateConcurrencyException' occurred in EntityFramework.dll

Additional information: Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries.

How can I fix this?

I'm in an isolated test project so there's nothing else going on around it.

Android: Reminder concept with Alarm Manager

I tried to make reminder application for Android, followed tutorial from this website Set Notification for Specific Date. Basically it used Alarm Manager to create a reminder.

This code used to call alarm manager and show notification on specific date.

reminderClient.setAlarmForNotification(calendar, uniqueid, interval);

I save all of the reminder in SQLite Database. So when this code (above) called, new record will be inserted to database and when notification show up, that record will be deleted.

The problem is whenever device restart, alarm manager stopped.

So I create a new BroadcastReceiver that receive event when device turned on.

<application ...  >

    <receiver android:name=".ReminderReceiver" >
        <intent-filter>
            <action android:name="android.intent.action.BOOT_COMPLETED" />
            <uses-permission android:name="android.permission.WAKE_LOCK" />
        </intent-filter>
    </receiver>
</application>

public class ReminderReceiver extends BroadcastReceiver {

    @Override
    public void onReceive(Context context, Intent intent) {
        //do stuff
    }

}

Is it OK to get all record from database, and call setAlarmForNotification again inside OnReceive like this?

public class ReminderReceiver extends BroadcastReceiver {

    @Override
    public void onReceive(Context context, Intent intent) {
        List<MyReminder> reminders = database.getAllReminder();
        Calendar cal = Calendar.getInstance();

        for (int i=0; i<reminders.size(); i++) {
            cal.setTime(parseStringDateToDate(reminders.get(i).getDateTime());

            reminderClient.setAlarmForNotification(
                cal, 
                reminders.get(i).getUniqueID(), 
                reminders.get(i).getInterval()
            );
        }
    }

}

Or is there a better way?

CursorLoader and setUpdateThrottle

I'm building an application that leverages a CursorLoader, CursorTreeAdapter and LoaderManager to display information backed by a ContentProvider / SQLite database.

I'm inserting new rows in to the database in rapid succession (around every ~500 milliseconds) and would like to throttle the refresh of the list so it does't jump all over the place when I insert data.

Looking through the documentation there's a function on AsyncTaskLoader (which CursorLoader inherits from) - setUpdateThrottle

public void setUpdateThrottle (long delayMS)

Added in API level 11 Set amount to throttle updates by. This is the minimum time from when the last loadInBackground() call has completed until a new load is scheduled.

Parameters delayMS Amount of delay, in milliseconds.

Unfortunately, setting this to something like 5000 milliseconds doesn't seem to slow the reloads of the CursorTreeAdapter.

@Override
public Loader<Cursor> onCreateLoader(int id, Bundle args) {
    ...
    CursorLoader cl = new CursorLoader(getActivity(),
            NotesContract.Notes.CONTENT_URI,
            projection,
            selection,
            selectionArgs,
            null);

    // setUpdateThrottle Doesn't seem to change anything
    cl.setUpdateThrottle(5000);
    return cl;
}

Any ideas on how to use this throttle function?

If this isn't the right way to use it, do you have any ideas on the best way to batch redraws using CursorLoader, CursorTreeAdapter and LoaderManager?

LINQ to SQLite Gives Parser Stack Overflow Error

I finally managed to get SQLite running with C# LINQ to Entities, and also to use User Defined Functions. It works for some queries but throws errors for other queries.

Here the log of the queries that fail.

This first query returns "stack overflow error".

SELECT 
[Project27].[MediaTypeId] AS [MediaTypeId], 
[Project27].[MediaId] AS [MediaId], 
[Project27].[Artist] AS [Artist], 
[Project27].[Title] AS [Title], 
[Project27].[Album] AS [Album], 
[Project27].[MediaCategoryId] AS [MediaCategoryId], 
[Project27].[FileName] AS [FileName], 
[Project27].[Preference] AS [Preference], 
[Project27].[C1] AS [C1], 
[Project27].[C2] AS [C2], 
[Project27].[BuyUrl] AS [BuyUrl], 
[Project27].[C5] AS [C3], 
[Project27].[C6] AS [C4], 
[Project27].[C7] AS [C5], 
[Project27].[C8] AS [C6], 
[Project27].[C9] AS [C7], 
[Project27].[C10] AS [C8], 
[Project27].[C11] AS [C9], 
[Project27].[C12] AS [C10], 
[Project27].[C3] AS [C11], 
[Project27].[C4] AS [C12]
FROM ( SELECT 
    [Project26].[MediaId] AS [MediaId], 
    [Project26].[MediaTypeId] AS [MediaTypeId], 
    [Project26].[Artist] AS [Artist], 
    [Project26].[Title] AS [Title], 
    [Project26].[Album] AS [Album], 
    [Project26].[MediaCategoryId] AS [MediaCategoryId], 
    [Project26].[FileName] AS [FileName], 
    [Project26].[Preference] AS [Preference], 
    [Project26].[BuyUrl] AS [BuyUrl], 
    [Project26].[Length] AS [C1], 
    CASE WHEN ('' <> [Project26].[DownloadUrl]) THEN 1 WHEN ('' = [Project26].[DownloadUrl]) THEN 0 END AS [C2], 
    CASE WHEN ((@p__linq__9 IS NULL) OR ((LENGTH(@p__linq__9)) = 0)) THEN NULL ELSE [Project26].[C9] END AS [C3], 
    1 AS [C4], 
    [Project26].[C1] AS [C5], 
    [Project26].[C2] AS [C6], 
    [Project26].[C3] AS [C7], 
    [Project26].[C4] AS [C8], 
    [Project26].[C5] AS [C9], 
    [Project26].[C6] AS [C10], 
    [Project26].[C7] AS [C11], 
    [Project26].[C8] AS [C12]
    FROM ( SELECT 
        [Project24].[MediaId] AS [MediaId], 
        [Project24].[MediaTypeId] AS [MediaTypeId], 
        [Project24].[Artist] AS [Artist], 
        [Project24].[Title] AS [Title], 
        [Project24].[Album] AS [Album], 
        [Project24].[MediaCategoryId] AS [MediaCategoryId], 
        [Project24].[FileName] AS [FileName], 
        [Project24].[Preference] AS [Preference], 
        [Project24].[Length] AS [Length], 
        [Project24].[DownloadUrl] AS [DownloadUrl], 
        [Project24].[BuyUrl] AS [BuyUrl], 
        [Project24].[C1] AS [C1], 
        [Project24].[C2] AS [C2], 
        [Project24].[C3] AS [C3], 
        [Project24].[C4] AS [C4], 
        [Project24].[C5] AS [C5], 
        [Project24].[C6] AS [C6], 
        [Project24].[C7] AS [C7], 
        [Project24].[C8] AS [C8], 
        (SELECT 
            [DbGetRatingValue]([Extent18].[Height], [Extent18].[Depth], @p__linq__11) AS [C1]
            FROM  [MediaRatings] AS [Extent18]
            INNER JOIN [RatingCategories] AS [Extent19] ON [Extent18].[RatingId] = [Extent19].[RatingId]
            WHERE (([Project24].[MediaId] = [Extent18].[MediaId]) AND (([Extent19].[Name] = @p__linq__10) OR (1 = 0))) AND ([Extent19].[Custom] = 1) LIMIT 1) AS [C9]
        FROM ( SELECT 
            [Project23].[MediaId] AS [MediaId], 
            [Project23].[MediaTypeId] AS [MediaTypeId], 
            [Project23].[Artist] AS [Artist], 
            [Project23].[Title] AS [Title], 
            [Project23].[Album] AS [Album], 
            [Project23].[MediaCategoryId] AS [MediaCategoryId], 
            [Project23].[FileName] AS [FileName], 
            [Project23].[Preference] AS [Preference], 
            [Project23].[Length] AS [Length], 
            [Project23].[DownloadUrl] AS [DownloadUrl], 
            [Project23].[BuyUrl] AS [BuyUrl], 
            [Project23].[C1] AS [C1], 
            [Project23].[C2] AS [C2], 
            [Project23].[C3] AS [C3], 
            [Project23].[C4] AS [C4], 
            [Project23].[C5] AS [C5], 
            [Project23].[C6] AS [C6], 
            [Project23].[C7] AS [C7], 
            [Project23].[C8] AS [C8]
            FROM ( SELECT 
                [Project21].[MediaId] AS [MediaId], 
                [Project21].[MediaTypeId] AS [MediaTypeId], 
                [Project21].[Artist] AS [Artist], 
                [Project21].[Title] AS [Title], 
                [Project21].[Album] AS [Album], 
                [Project21].[MediaCategoryId] AS [MediaCategoryId], 
                [Project21].[FileName] AS [FileName], 
                [Project21].[Preference] AS [Preference], 
                [Project21].[Length] AS [Length], 
                [Project21].[DownloadUrl] AS [DownloadUrl], 
                [Project21].[BuyUrl] AS [BuyUrl], 
                [Project21].[C1] AS [C1], 
                [Project21].[C2] AS [C2], 
                [Project21].[C3] AS [C3], 
                [Project21].[C4] AS [C4], 
                [Project21].[C5] AS [C5], 
                [Project21].[C6] AS [C6], 
                [Project21].[C7] AS [C7], 
                (SELECT 
                    [DbGetRatingValue]([Extent16].[Height], [Extent16].[Depth], @p__linq__8) AS [C1]
                    FROM  [MediaRatings] AS [Extent16]
                    INNER JOIN [RatingCategories] AS [Extent17] ON [Extent16].[RatingId] = [Extent17].[RatingId]
                    WHERE ([Project21].[MediaId] = [Extent16].[MediaId]) AND ('Egoless' = [Extent17].[Name]) LIMIT 1) AS [C8]
                FROM ( SELECT 
                    [Project20].[MediaId] AS [MediaId], 
                    [Project20].[MediaTypeId] AS [MediaTypeId], 
                    [Project20].[Artist] AS [Artist], 
                    [Project20].[Title] AS [Title], 
                    [Project20].[Album] AS [Album], 
                    [Project20].[MediaCategoryId] AS [MediaCategoryId], 
                    [Project20].[FileName] AS [FileName], 
                    [Project20].[Preference] AS [Preference], 
                    [Project20].[Length] AS [Length], 
                    [Project20].[DownloadUrl] AS [DownloadUrl], 
                    [Project20].[BuyUrl] AS [BuyUrl], 
                    [Project20].[C1] AS [C1], 
                    [Project20].[C2] AS [C2], 
                    [Project20].[C3] AS [C3], 
                    [Project20].[C4] AS [C4], 
                    [Project20].[C5] AS [C5], 
                    [Project20].[C6] AS [C6], 
                    [Project20].[C7] AS [C7]
                    FROM ( SELECT 
                        [Project18].[MediaId] AS [MediaId], 
                        [Project18].[MediaTypeId] AS [MediaTypeId], 
                        [Project18].[Artist] AS [Artist], 
                        [Project18].[Title] AS [Title], 
                        [Project18].[Album] AS [Album], 
                        [Project18].[MediaCategoryId] AS [MediaCategoryId], 
                        [Project18].[FileName] AS [FileName], 
                        [Project18].[Preference] AS [Preference], 
                        [Project18].[Length] AS [Length], 
                        [Project18].[DownloadUrl] AS [DownloadUrl], 
                        [Project18].[BuyUrl] AS [BuyUrl], 
                        [Project18].[C1] AS [C1], 
                        [Project18].[C2] AS [C2], 
                        [Project18].[C3] AS [C3], 
                        [Project18].[C4] AS [C4], 
                        [Project18].[C5] AS [C5], 
                        [Project18].[C6] AS [C6], 
                        (SELECT 
                            [DbGetRatingValue]([Extent14].[Height], [Extent14].[Depth], @p__linq__7) AS [C1]
                            FROM  [MediaRatings] AS [Extent14]
                            INNER JOIN [RatingCategories] AS [Extent15] ON [Extent14].[RatingId] = [Extent15].[RatingId]
                            WHERE ([Project18].[MediaId] = [Extent14].[MediaId]) AND ('Love' = [Extent15].[Name]) LIMIT 1) AS [C7]
                        FROM ( SELECT 
                            [Project17].[MediaId] AS [MediaId], 
                            [Project17].[MediaTypeId] AS [MediaTypeId], 
                            [Project17].[Artist] AS [Artist], 
                            [Project17].[Title] AS [Title], 
                            [Project17].[Album] AS [Album], 
                            [Project17].[MediaCategoryId] AS [MediaCategoryId], 
                            [Project17].[FileName] AS [FileName], 
                            [Project17].[Preference] AS [Preference], 
                            [Project17].[Length] AS [Length], 
                            [Project17].[DownloadUrl] AS [DownloadUrl], 
                            [Project17].[BuyUrl] AS [BuyUrl], 
                            [Project17].[C1] AS [C1], 
                            [Project17].[C2] AS [C2], 
                            [Project17].[C3] AS [C3], 
                            [Project17].[C4] AS [C4], 
                            [Project17].[C5] AS [C5], 
                            [Project17].[C6] AS [C6]
                            FROM ( SELECT 
                                [Project15].[MediaId] AS [MediaId], 
                                [Project15].[MediaTypeId] AS [MediaTypeId], 
                                [Project15].[Artist] AS [Artist], 
                                [Project15].[Title] AS [Title], 
                                [Project15].[Album] AS [Album], 
                                [Project15].[MediaCategoryId] AS [MediaCategoryId], 
                                [Project15].[FileName] AS [FileName], 
                                [Project15].[Preference] AS [Preference], 
                                [Project15].[Length] AS [Length], 
                                [Project15].[DownloadUrl] AS [DownloadUrl], 
                                [Project15].[BuyUrl] AS [BuyUrl], 
                                [Project15].[C1] AS [C1], 
                                [Project15].[C2] AS [C2], 
                                [Project15].[C3] AS [C3], 
                                [Project15].[C4] AS [C4], 
                                [Project15].[C5] AS [C5], 
                                (SELECT 
                                    [DbGetRatingValue]([Extent12].[Height], [Extent12].[Depth], @p__linq__6) AS [C1]
                                    FROM  [MediaRatings] AS [Extent12]
                                    INNER JOIN [RatingCategories] AS [Extent13] ON [Extent12].[RatingId] = [Extent13].[RatingId]
                                    WHERE ([Project15].[MediaId] = [Extent12].[MediaId]) AND ('Spiritual Feminine' = [Extent13].[Name]) LIMIT 1) AS [C6]
                                FROM ( SELECT 
                                    [Project14].[MediaId] AS [MediaId], 
                                    [Project14].[MediaTypeId] AS [MediaTypeId], 
                                    [Project14].[Artist] AS [Artist], 
                                    [Project14].[Title] AS [Title], 
                                    [Project14].[Album] AS [Album], 
                                    [Project14].[MediaCategoryId] AS [MediaCategoryId], 
                                    [Project14].[FileName] AS [FileName], 
                                    [Project14].[Preference] AS [Preference], 
                                    [Project14].[Length] AS [Length], 
                                    [Project14].[DownloadUrl] AS [DownloadUrl], 
                                    [Project14].[BuyUrl] AS [BuyUrl], 
                                    [Project14].[C1] AS [C1], 
                                    [Project14].[C2] AS [C2], 
                                    [Project14].[C3] AS [C3], 
                                    [Project14].[C4] AS [C4], 
                                    [Project14].[C5] AS [C5]
                                    FROM ( SELECT 
                                        [Project12].[MediaId] AS [MediaId], 
                                        [Project12].[MediaTypeId] AS [MediaTypeId], 
                                        [Project12].[Artist] AS [Artist], 
                                        [Project12].[Title] AS [Title], 
                                        [Project12].[Album] AS [Album], 
                                        [Project12].[MediaCategoryId] AS [MediaCategoryId], 
                                        [Project12].[FileName] AS [FileName], 
                                        [Project12].[Preference] AS [Preference], 
                                        [Project12].[Length] AS [Length], 
                                        [Project12].[DownloadUrl] AS [DownloadUrl], 
                                        [Project12].[BuyUrl] AS [BuyUrl], 
                                        [Project12].[C1] AS [C1], 
                                        [Project12].[C2] AS [C2], 
                                        [Project12].[C3] AS [C3], 
                                        [Project12].[C4] AS [C4], 
                                        (SELECT 
                                            [DbGetRatingValue]([Extent10].[Height], [Extent10].[Depth], @p__linq__5) AS [C1]
                                            FROM  [MediaRatings] AS [Extent10]
                                            INNER JOIN [RatingCategories] AS [Extent11] ON [Extent10].[RatingId] = [Extent11].[RatingId]
                                            WHERE ([Project12].[MediaId] = [Extent10].[MediaId]) AND ('Spiritual Masculine' = [Extent11].[Name]) LIMIT 1) AS [C5]
                                        FROM ( SELECT 
                                            [Project11].[MediaId] AS [MediaId], 
                                            [Project11].[MediaTypeId] AS [MediaTypeId], 
                                            [Project11].[Artist] AS [Artist], 
                                            [Project11].[Title] AS [Title], 
                                            [Project11].[Album] AS [Album], 
                                            [Project11].[MediaCategoryId] AS [MediaCategoryId], 
                                            [Project11].[FileName] AS [FileName], 
                                            [Project11].[Preference] AS [Preference], 
                                            [Project11].[Length] AS [Length], 
                                            [Project11].[DownloadUrl] AS [DownloadUrl], 
                                            [Project11].[BuyUrl] AS [BuyUrl], 
                                            [Project11].[C1] AS [C1], 
                                            [Project11].[C2] AS [C2], 
                                            [Project11].[C3] AS [C3], 
                                            [Project11].[C4] AS [C4]
                                            FROM ( SELECT 
                                                [Project9].[MediaId] AS [MediaId], 
                                                [Project9].[MediaTypeId] AS [MediaTypeId], 
                                                [Project9].[Artist] AS [Artist], 
                                                [Project9].[Title] AS [Title], 
                                                [Project9].[Album] AS [Album], 
                                                [Project9].[MediaCategoryId] AS [MediaCategoryId], 
                                                [Project9].[FileName] AS [FileName], 
                                                [Project9].[Preference] AS [Preference], 
                                                [Project9].[Length] AS [Length], 
                                                [Project9].[DownloadUrl] AS [DownloadUrl], 
                                                [Project9].[BuyUrl] AS [BuyUrl], 
                                                [Project9].[C1] AS [C1], 
                                                [Project9].[C2] AS [C2], 
                                                [Project9].[C3] AS [C3], 
                                                (SELECT 
                                                    [DbGetRatingValue]([Extent8].[Height], [Extent8].[Depth], @p__linq__4) AS [C1]
                                                    FROM  [MediaRatings] AS [Extent8]
                                                    INNER JOIN [RatingCategories] AS [Extent9] ON [Extent8].[RatingId] = [Extent9].[RatingId]
                                                    WHERE ([Project9].[MediaId] = [Extent8].[MediaId]) AND ('Emotional Feminine' = [Extent9].[Name]) LIMIT 1) AS [C4]
                                                FROM ( SELECT 
                                                    [Project8].[MediaId] AS [MediaId], 
                                                    [Project8].[MediaTypeId] AS [MediaTypeId], 
                                                    [Project8].[Artist] AS [Artist], 
                                                    [Project8].[Title] AS [Title], 
                                                    [Project8].[Album] AS [Album], 
                                                    [Project8].[MediaCategoryId] AS [MediaCategoryId], 
                                                    [Project8].[FileName] AS [FileName], 
                                                    [Project8].[Preference] AS [Preference], 
                                                    [Project8].[Length] AS [Length], 
                                                    [Project8].[DownloadUrl] AS [DownloadUrl], 
                                                    [Project8].[BuyUrl] AS [BuyUrl], 
                                                    [Project8].[C1] AS [C1], 
                                                    [Project8].[C2] AS [C2], 
                                                    [Project8].[C3] AS [C3]
                                                    FROM ( SELECT 
                                                        [Project6].[MediaId] AS [MediaId], 
                                                        [Project6].[MediaTypeId] AS [MediaTypeId], 
                                                        [Project6].[Artist] AS [Artist], 
                                                        [Project6].[Title] AS [Title], 
                                                        [Project6].[Album] AS [Album], 
                                                        [Project6].[MediaCategoryId] AS [MediaCategoryId], 
                                                        [Project6].[FileName] AS [FileName], 
                                                        [Project6].[Preference] AS [Preference], 
                                                        [Project6].[Length] AS [Length], 
                                                        [Project6].[DownloadUrl] AS [DownloadUrl], 
                                                        [Project6].[BuyUrl] AS [BuyUrl], 
                                                        [Project6].[C1] AS [C1], 
                                                        [Project6].[C2] AS [C2], 
                                                        (SELECT 
                                                            [DbGetRatingValue]([Extent6].[Height], [Extent6].[Depth], @p__linq__3) AS [C1]
                                                            FROM  [MediaRatings] AS [Extent6]
                                                            INNER JOIN [RatingCategories] AS [Extent7] ON [Extent6].[RatingId] = [Extent7].[RatingId]
                                                            WHERE ([Project6].[MediaId] = [Extent6].[MediaId]) AND ('Emotional Masculine' = [Extent7].[Name]) LIMIT 1) AS [C3]
                                                        FROM ( SELECT 
                                                            [Project5].[MediaId] AS [MediaId], 
                                                            [Project5].[MediaTypeId] AS [MediaTypeId], 
                                                            [Project5].[Artist] AS [Artist], 
                                                            [Project5].[Title] AS [Title], 
                                                            [Project5].[Album] AS [Album], 
                                                            [Project5].[MediaCategoryId] AS [MediaCategoryId], 
                                                            [Project5].[FileName] AS [FileName], 
                                                            [Project5].[Preference] AS [Preference], 
                                                            [Project5].[Length] AS [Length], 
                                                            [Project5].[DownloadUrl] AS [DownloadUrl], 
                                                            [Project5].[BuyUrl] AS [BuyUrl], 
                                                            [Project5].[C1] AS [C1], 
                                                            [Project5].[C2] AS [C2]
                                                            FROM ( SELECT 
                                                                [Project3].[MediaId] AS [MediaId], 
                                                                [Project3].[MediaTypeId] AS [MediaTypeId], 
                                                                [Project3].[Artist] AS [Artist], 
                                                                [Project3].[Title] AS [Title], 
                                                                [Project3].[Album] AS [Album], 
                                                                [Project3].[MediaCategoryId] AS [MediaCategoryId], 
                                                                [Project3].[FileName] AS [FileName], 
                                                                [Project3].[Preference] AS [Preference], 
                                                                [Project3].[Length] AS [Length], 
                                                                [Project3].[DownloadUrl] AS [DownloadUrl], 
                                                                [Project3].[BuyUrl] AS [BuyUrl], 
                                                                [Project3].[C1] AS [C1], 
                                                                (SELECT 
                                                                    [DbGetRatingValue]([Extent4].[Height], [Extent4].[Depth], @p__linq__2) AS [C1]
                                                                    FROM  [MediaRatings] AS [Extent4]
                                                                    INNER JOIN [RatingCategories] AS [Extent5] ON [Extent4].[RatingId] = [Extent5].[RatingId]
                                                                    WHERE ([Project3].[MediaId] = [Extent4].[MediaId]) AND ('Physical Feminine' = [Extent5].[Name]) LIMIT 1) AS [C2]
                                                                FROM ( SELECT 
                                                                    [Project2].[MediaId] AS [MediaId], 
                                                                    [Project2].[MediaTypeId] AS [MediaTypeId], 
                                                                    [Project2].[Artist] AS [Artist], 
                                                                    [Project2].[Title] AS [Title], 
                                                                    [Project2].[Album] AS [Album], 
                                                                    [Project2].[MediaCategoryId] AS [MediaCategoryId], 
                                                                    [Project2].[FileName] AS [FileName], 
                                                                    [Project2].[Preference] AS [Preference], 
                                                                    [Project2].[Length] AS [Length], 
                                                                    [Project2].[DownloadUrl] AS [DownloadUrl], 
                                                                    [Project2].[BuyUrl] AS [BuyUrl], 
                                                                    [Project2].[C1] AS [C1]
                                                                    FROM ( SELECT 
                                                                        [Extent1].[MediaId] AS [MediaId], 
                                                                        [Extent1].[MediaTypeId] AS [MediaTypeId], 
                                                                        [Extent1].[Artist] AS [Artist], 
                                                                        [Extent1].[Title] AS [Title], 
                                                                        [Extent1].[Album] AS [Album], 
                                                                        [Extent1].[MediaCategoryId] AS [MediaCategoryId], 
                                                                        [Extent1].[FileName] AS [FileName], 
                                                                        [Extent1].[Preference] AS [Preference], 
                                                                        [Extent1].[Length] AS [Length], 
                                                                        [Extent1].[DownloadUrl] AS [DownloadUrl], 
                                                                        [Extent1].[BuyUrl] AS [BuyUrl], 
                                                                        (SELECT 
                                                                            [DbGetRatingValue]([Extent2].[Height], [Extent2].[Depth], @p__linq__1) AS [C1]
                                                                            FROM  [MediaRatings] AS [Extent2]
                                                                            INNER JOIN [RatingCategories] AS [Extent3] ON [Extent2].[RatingId] = [Extent3].[RatingId]
                                                                            WHERE ([Extent1].[MediaId] = [Extent2].[MediaId]) AND ('Physical Masculine' = [Extent3].[Name]) LIMIT 1) AS [C1]
                                                                        FROM [Media] AS [Extent1]
                                                                        WHERE [Extent1].[MediaTypeId] = @p__linq__0
                                                                    )  AS [Project2]
                                                                )  AS [Project3]
                                                            )  AS [Project5]
                                                        )  AS [Project6]
                                                    )  AS [Project8]
                                                )  AS [Project9]
                                            )  AS [Project11]
                                        )  AS [Project12]
                                    )  AS [Project14]
                                )  AS [Project15]
                            )  AS [Project17]
                        )  AS [Project18]
                    )  AS [Project20]
                )  AS [Project21]
            )  AS [Project23]
        )  AS [Project24]
    )  AS [Project26]
)  AS [Project27]
ORDER BY [Project27].[C4] DESC, [Project27].[Artist] ASC, [Project27].[Title] ASC


-- p__linq__0: '0' (Type = Int32, IsNullable = false)

-- p__linq__1: '0' (Type = Double, IsNullable = false)

-- p__linq__2: '0' (Type = Double, IsNullable = false)

-- p__linq__3: '0' (Type = Double, IsNullable = false)

-- p__linq__4: '0' (Type = Double, IsNullable = false)

-- p__linq__5: '0' (Type = Double, IsNullable = false)

-- p__linq__6: '0' (Type = Double, IsNullable = false)

-- p__linq__7: '0' (Type = Double, IsNullable = false)

-- p__linq__8: '0' (Type = Double, IsNullable = false)

-- p__linq__10: '' (Type = String)

-- p__linq__11: '0' (Type = Double, IsNullable = false)

-- p__linq__9: '' (Type = String)

-- Executing at 29/05/2015 5:41:19 PM -05:00

SQLite error (1): parser stack overflow
-- Failed in 39 ms with error: SQL logic error or missing database
parser stack overflow

This second query returns "no such function: Substring" error

SELECT 
[Extent1].[MediaTypeId] AS [MediaTypeId], 
[Extent1].[MediaId] AS [MediaId], 
[Extent1].[Preference] AS [Preference]
FROM [Media] AS [Extent1]
WHERE ((((((((@p__linq__0 = 1) OR ([Extent1].[FileName] IS NOT NULL)) AND (([Extent1].[FileName] IS NOT NULL) OR ('' <> [Extent1].[DownloadUrl]))) AND (([Extent1].[Length] IS NULL) OR (((CASE WHEN ([Extent1].[EndPos] IS NOT NULL) THEN [Extent1].[EndPos] ELSE [Extent1].[Length] END) - (CASE WHEN ([Extent1].[StartPos] IS NOT NULL) THEN [Extent1].[StartPos] ELSE 0 END)) <= 720))) AND ((@p__linq__1 = 1) OR (0 = (CASE WHEN ( EXISTS (SELECT 
    1 AS [C1]
    FROM  [MediaRatings] AS [Extent2]
    INNER JOIN [RatingCategories] AS [Extent3] ON [Extent2].[RatingId] = [Extent3].[RatingId]
    WHERE ([Extent1].[MediaId] = [Extent2].[MediaId]) AND ((('Fire' = [Extent3].[Name]) AND (([DbGetRatingValue]([Extent2].[Height], [Extent2].[Depth], 0)) >= 8.5)) OR (('Water' = [Extent3].[Name]) AND (([DbGetRatingValue]([Extent2].[Height], [Extent2].[Depth], 0)) >= 6)))
)) THEN 1 ELSE 0 END)))) AND ([Extent1].[MediaTypeId] = @p__linq__2)) AND (((CASE WHEN ( EXISTS (SELECT 
    1 AS [C1]
    FROM ( SELECT [Project2].[C1] AS [C1], [Project2].[Height] AS [Height], [Project2].[Depth] AS [Depth]
        FROM ( SELECT 
            [DbGetRatingValue]([Extent4].[Height], [Extent4].[Depth], @p__linq__4) AS [C1], 
            [Extent4].[Height] AS [Height], 
            [Extent4].[Depth] AS [Depth]
            FROM  [MediaRatings] AS [Extent4]
            INNER JOIN [RatingCategories] AS [Extent5] ON [Extent4].[RatingId] = [Extent5].[RatingId]
            WHERE ([Extent1].[MediaId] = [Extent4].[MediaId]) AND ((CHARINDEX(@p__linq__3, [Extent5].[Name])) = 1)
        )  AS [Project2]
        ORDER BY [Project2].[C1] DESC LIMIT 1
    )  AS [Limit1]
    WHERE (((1 = @p__linq__6) AND ((([DbGetRatingValue]([Limit1].[Height], [Limit1].[Depth], @p__linq__5)) = @p__linq__7) OR (([DbGetRatingValue]([Limit1].[Height], [Limit1].[Depth], @p__linq__5) IS NULL) AND (@p__linq__7 IS NULL)))) OR ((0 = @p__linq__8) AND (([DbGetRatingValue]([Limit1].[Height], [Limit1].[Depth], @p__linq__5)) >= @p__linq__9))) OR (((2 = @p__linq__10) AND (([DbGetRatingValue]([Limit1].[Height], [Limit1].[Depth], @p__linq__5)) >= @p__linq__11)) AND (([DbGetRatingValue]([Limit1].[Height], [Limit1].[Depth], 0)) >= @p__linq__12))
)) THEN 1 ELSE 0 END) = (CASE WHEN (2 <> @p__linq__13) THEN 1 WHEN (2 = @p__linq__13) THEN 0 END)) OR ((CASE WHEN ( EXISTS (SELECT 
    1 AS [C1]
    FROM ( SELECT [Project4].[Height] AS [Height], [Project4].[Depth] AS [Depth], [Project4].[C1] AS [C1]
        FROM ( SELECT 
            [Extent6].[Height] AS [Height], 
            [Extent6].[Depth] AS [Depth], 
            [DbGetRatingValue]([Extent6].[Height], [Extent6].[Depth], @p__linq__4) AS [C1]
            FROM  [MediaRatings] AS [Extent6]
            INNER JOIN [RatingCategories] AS [Extent7] ON [Extent6].[RatingId] = [Extent7].[RatingId]
            WHERE ([Extent1].[MediaId] = [Extent6].[MediaId]) AND ((CHARINDEX(@p__linq__3, [Extent7].[Name])) = 1)
        )  AS [Project4]
        ORDER BY [Project4].[C1] DESC LIMIT 1
    )  AS [Limit2]
    WHERE (((1 = @p__linq__6) AND ((([DbGetRatingValue]([Limit2].[Height], [Limit2].[Depth], @p__linq__5)) = @p__linq__7) OR (([DbGetRatingValue]([Limit2].[Height], [Limit2].[Depth], @p__linq__5) IS NULL) AND (@p__linq__7 IS NULL)))) OR ((0 = @p__linq__8) AND (([DbGetRatingValue]([Limit2].[Height], [Limit2].[Depth], @p__linq__5)) >= @p__linq__9))) OR (((2 = @p__linq__10) AND (([DbGetRatingValue]([Limit2].[Height], [Limit2].[Depth], @p__linq__5)) >= @p__linq__11)) AND (([DbGetRatingValue]([Limit2].[Height], [Limit2].[Depth], 0)) >= @p__linq__12))
)) THEN 1 ELSE 0 END IS NULL) AND (CASE WHEN (2 <> @p__linq__13) THEN 1 WHEN (2 = @p__linq__13) THEN 0 END IS NULL)))) AND (((CASE WHEN ( EXISTS (SELECT 
    1 AS [C1]
    FROM ( SELECT [Project6].[C1] AS [C1], [Project6].[Height] AS [Height], [Project6].[Depth] AS [Depth]
        FROM ( SELECT 
            [DbGetRatingValue]([Extent8].[Height], [Extent8].[Depth], @p__linq__15) AS [C1], 
            [Extent8].[Height] AS [Height], 
            [Extent8].[Depth] AS [Depth]
            FROM  [MediaRatings] AS [Extent8]
            INNER JOIN [RatingCategories] AS [Extent9] ON [Extent8].[RatingId] = [Extent9].[RatingId]
            WHERE ([Extent1].[MediaId] = [Extent8].[MediaId]) AND ((CHARINDEX(@p__linq__14, [Extent9].[Name])) = 1)
        )  AS [Project6]
        ORDER BY [Project6].[C1] DESC LIMIT 1
    )  AS [Limit3]
    WHERE (((1 = @p__linq__17) AND ((([DbGetRatingValue]([Limit3].[Height], [Limit3].[Depth], @p__linq__16)) = @p__linq__18) OR (([DbGetRatingValue]([Limit3].[Height], [Limit3].[Depth], @p__linq__16) IS NULL) AND (@p__linq__18 IS NULL)))) OR ((0 = @p__linq__19) AND (([DbGetRatingValue]([Limit3].[Height], [Limit3].[Depth], @p__linq__16)) >= @p__linq__20))) OR (((2 = @p__linq__21) AND (([DbGetRatingValue]([Limit3].[Height], [Limit3].[Depth], @p__linq__16)) >= @p__linq__22)) AND (([DbGetRatingValue]([Limit3].[Height], [Limit3].[Depth], 0)) >= @p__linq__23))
)) THEN 1 ELSE 0 END) = (CASE WHEN (2 <> @p__linq__24) THEN 1 WHEN (2 = @p__linq__24) THEN 0 END)) OR ((CASE WHEN ( EXISTS (SELECT 
    1 AS [C1]
    FROM ( SELECT [Project8].[Height] AS [Height], [Project8].[Depth] AS [Depth], [Project8].[C1] AS [C1]
        FROM ( SELECT 
            [Extent10].[Height] AS [Height], 
            [Extent10].[Depth] AS [Depth], 
            [DbGetRatingValue]([Extent10].[Height], [Extent10].[Depth], @p__linq__15) AS [C1]
            FROM  [MediaRatings] AS [Extent10]
            INNER JOIN [RatingCategories] AS [Extent11] ON [Extent10].[RatingId] = [Extent11].[RatingId]
            WHERE ([Extent1].[MediaId] = [Extent10].[MediaId]) AND ((CHARINDEX(@p__linq__14, [Extent11].[Name])) = 1)
        )  AS [Project8]
        ORDER BY [Project8].[C1] DESC LIMIT 1
    )  AS [Limit4]
    WHERE (((1 = @p__linq__17) AND ((([DbGetRatingValue]([Limit4].[Height], [Limit4].[Depth], @p__linq__16)) = @p__linq__18) OR (([DbGetRatingValue]([Limit4].[Height], [Limit4].[Depth], @p__linq__16) IS NULL) AND (@p__linq__18 IS NULL)))) OR ((0 = @p__linq__19) AND (([DbGetRatingValue]([Limit4].[Height], [Limit4].[Depth], @p__linq__16)) >= @p__linq__20))) OR (((2 = @p__linq__21) AND (([DbGetRatingValue]([Limit4].[Height], [Limit4].[Depth], @p__linq__16)) >= @p__linq__22)) AND (([DbGetRatingValue]([Limit4].[Height], [Limit4].[Depth], 0)) >= @p__linq__23))
)) THEN 1 ELSE 0 END IS NULL) AND (CASE WHEN (2 <> @p__linq__24) THEN 1 WHEN (2 = @p__linq__24) THEN 0 END IS NULL)))) AND ( NOT EXISTS (SELECT 
    1 AS [C1]
    FROM  [MediaRatings] AS [Extent12]
    INNER JOIN [RatingCategories] AS [Extent13] ON [Extent12].[RatingId] = [Extent13].[RatingId]
    WHERE ([Extent1].[MediaId] = [Extent12].[MediaId]) AND ((((((CHARINDEX(Substring(@p__linq__26, 1 + 1, (LENGTH(@p__linq__26)) - 1), [Extent13].[Name])) <> 1) AND (0 = [Extent13].[Custom])) AND ('Egoless' <> [Extent13].[Name])) AND ('Love' <> [Extent13].[Name])) AND ((2 = @p__linq__27) AND (([DbGetRatingValue]([Extent12].[Height], [Extent12].[Depth], @p__linq__25)) >= @p__linq__28)))
))


-- p__linq__0: 'True' (Type = Boolean, IsNullable = false)

-- p__linq__1: 'False' (Type = Boolean, IsNullable = false)

-- p__linq__2: '0' (Type = Int32, IsNullable = false)

-- p__linq__3: 'Emotional' (Type = String)

-- p__linq__4: '0' (Type = Double, IsNullable = false)

-- p__linq__6: '0' (Type = Int32, IsNullable = false)

-- p__linq__5: '0' (Type = Double, IsNullable = false)

-- p__linq__7: '6.7' (Type = Double)

-- p__linq__8: '0' (Type = Int32, IsNullable = false)

-- p__linq__9: '6.7' (Type = Double)

-- p__linq__10: '0' (Type = Int32, IsNullable = false)

-- p__linq__11: '6.7' (Type = Double)

-- p__linq__12: '6.7' (Type = Double)

-- p__linq__13: '0' (Type = Int32, IsNullable = false)

-- p__linq__14: 'Emotional' (Type = String)

-- p__linq__15: '0' (Type = Double, IsNullable = false)

-- p__linq__17: '2' (Type = Int32, IsNullable = false)

-- p__linq__16: '0' (Type = Double, IsNullable = false)

-- p__linq__18: '7.5' (Type = Double)

-- p__linq__19: '2' (Type = Int32, IsNullable = false)

-- p__linq__20: '7.5' (Type = Double)

-- p__linq__21: '2' (Type = Int32, IsNullable = false)

-- p__linq__22: '7.5' (Type = Double)

-- p__linq__23: '7.5' (Type = Double)

-- p__linq__24: '2' (Type = Int32, IsNullable = false)

-- p__linq__26: '!Emotional' (Type = String)

-- p__linq__27: '2' (Type = Int32, IsNullable = false)

-- p__linq__25: '0' (Type = Double, IsNullable = false)

-- p__linq__28: '8' (Type = Double)

-- Executing at 29/05/2015 5:36:36 PM -05:00

SQLite error (1): no such function: Substring
-- Failed in 103 ms with error: SQL logic error or missing database
no such function: Substring

This seems to be a bug in the SQL parser as the correct function is substr, not substring

Is there any way to fix these errors?