SQLiteException when executing rawQuery - syntax error

Question

I am getting a SQLiteException / a SQL syntax error when executing the following rawQuery:

Code:

public static final String NAME = "name";   
public static final String LONG = "longitude";   
public static final String LAT = "latitude";
public static final String IDENTNR = "identNr";
public static final String IDENTSTR = "ident";
public static final String TABLE_NAME = "communities";

public Cursor findClosest(double lng, double lat) {

     Cursor c = db.rawQuery("SELECT " + NAME + ", " + LAT + ", " + LONG + ", " + IDENTNR + ", " + IDENTSTR + " SQRT(" +
                "POW(69.1 * (latitude - " + lat + "), 2) + " +
                "POW(69.1 * (" + lng + " - longitude) * COS(latitude / 57.3), 2)) AS distance " +
                "FROM " + TABLE_NAME + " HAVING distance < 25 ORDER BY distance DESC", null);

     return c;
}

Logcat error message:

03-20 15:46:07.248: E/AndroidRuntime(20605): FATAL EXCEPTION: main
03-20 15:46:07.248: E/AndroidRuntime(20605): android.database.sqlite.SQLiteException: near "(": syntax error (code 17): , while compiling: SELECT name, latitude, longitude, identNr, ident SQRT(POW(69.1 * (latitude - 48.2347197), 2) + POW(69.1 * (16.3550203 - longitude) * COS(latitude / 57.3), 2)) AS distance FROM communities HAVING distance < 25 ORDER BY distance DESC
03-20 15:46:07.248: E/AndroidRuntime(20605):    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)

Unfortunately I am not very experienced with SQL statements and cannot figure out where my syntax error might be?

Do the functions SQRT(), POW() and COS() exist in SQL and am I using them correctly?

My table name is definitely correct and my database / table contains values.

score 2 · Accepted Answer · answered Mar 20 '14 at 14:53

2

For a start, correct this :

IDENTSTR + ", " + " SQRT(.....

answered Mar 20 '14 at 14:53

Stathis Andronikos

1,259
2
25
44

That did it, thanks :) now I'm getting a NoSuchFunctionError which means I cannot use the functions I named in SQL. Thank you. – Philipp Jahoda Mar 20 '14 at 14:55

score 1 · Answer 2 · edited May 23 '17 at 12:20

1

As far as I know these functions do not exist in SQLite. You can, however, add functionality. See this StackOverflow post for more info.

edited May 23 '17 at 12:20

Community

1
1

answered Mar 20 '14 at 14:52

Grant Amos

2,256
17
11

SQLiteException when executing rawQuery - syntax error

2 Answers2