Android SQLite: near "...": syntax error (code 1): , while compiling: SELECT ".." FROM ".."WHERE _id=?

Question

I'm getting this error when running my code in Android Studio.

08-06 04:10:00.209  24514-24514/com.example.mk.anotherapp E/SQLiteLog﹕ (1) near "16": syntax error
08-06 04:10:00.269  24514-24514/com.example.mk.anotherapp E/AndroidRuntime﹕ FATAL EXCEPTION: main
Process: com.example.mk.anotherapp, PID: 24514
android.database.sqlite.SQLiteException: near "16": syntax error (code 1): , while compiling: SELECT 10 FROM 16 WHERE _id=?
        at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
        at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
        at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
        at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
        at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
        at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
        at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
        at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314)
        at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1161)
        at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1032)
        at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1238)
        at com.example.mk.anotherapp.Irradfrag$1.onClick(Irradfrag.java:108)
        at android.view.View.performClick(View.java:4445)
        at android.view.View$PerformClick.run(View.java:18446)
        at android.os.Handler.handleCallback(Handler.java:733)
        at android.os.Handler.dispatchMessage(Handler.java:95)
        at android.os.Looper.loop(Looper.java:136)
        at android.app.ActivityThread.main(ActivityThread.java:5146)
        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:732)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:566)
        at de.robv.android.xposed.XposedBridge.main(XposedBridge.java:132)
        at dalvik.system.NativeStart.main(Native Method)

Here is the relevant code:

            //assign actual values
            Integer pitch = pitchsel;
            Integer orientation = orientaationsel * 5;
            Double shadefactor =   shadesel / 100.0;
            String postregion = "1";

            RegionDatabase mDb  = new RegionDatabase(getActivity());
            SQLiteDatabase db = mDb.getReadableDatabase();
            String[] columns = {"Region"};
            String Selection = "_id=?";
            String[] SelectionArgs ={""+postcodesel};

            Cursor cursor = db.query("Region_Key",columns,Selection,SelectionArgs,null,null,null,null);
            while (cursor.moveToNext()) {
                    postregion = cursor.getString(cursor.getColumnIndexOrThrow("Region"));
                    Toast.makeText(getActivity(),"Region:"+ postregion, Toast.LENGTH_SHORT).show();
                }


             String[] columnsx = {""+orientation};
             String[] SelectionArgsx ={""+pitch};
             String ratio ="1";
// This is the query that the error refers to
            cursor = db.query(postregion,columnsx,Selection,SelectionArgsx,null,null,null,null);
            while (cursor.moveToNext()) {
                ratio = cursor.getString(cursor.getColumnIndexOrThrow(""+orientation));
                Toast.makeText(getActivity(),"Ratio:"+ ratio, Toast.LENGTH_SHORT).show();
            }

What really has me stumped is that the first query works perfectly and the second doesn't. This is a syntax error and the syntax seems to be exactly the same. The queries are done in two different tables in the same database. I've double checked the column names and data values to make sure everything exists as typed.

The other times this error has been posted, it is usually a case of wrong quotes in a rawquery, but that's not what I'm doing.

EDIT

My database is premade and consists of 25 tables with 37 columns each. I generated it from a spreadsheet whose data I have to be able to access. If the name of each column has to be non-numeric, any idea how to easily change the column names?

EDIT 2

Thanks everyone. It was due to numeric table names. I submitted an answer with 2 solutions below.

`SELECT 10 FROM 16`??? Yeah, that's a syntax error. What is that even supposed to do? — Kevin, Aug 06 '15 at 03:35
Those were the values of the arguments. The database isn't named great, but it's very large and not mine to begin with. 16 is the name of the table and 10 is the name of the column. Both are strings, so it should be ok. — Matt, Aug 06 '15 at 03:41
Nope, you can't do that. At the very least, table names which are SQL keywords need double quoting. I'm not even sure if there *is* a method of escaping integer literals. Just call them `table16` or something. — Kevin, Aug 06 '15 at 03:43
Ok, I'll try changing the table names, that won't be too bad. It's the column names that would take ages. — Matt, Aug 06 '15 at 03:44
You may need to change the column name as well. Otherwise, it'll just SELECT a literal 10. I suppose you could try `table.column` syntax, but no guarantee that works. — Kevin, Aug 06 '15 at 03:44
see http://stackoverflow.com/questions/3694276/what-are-valid-table-names-in-sqlite — njzk2, Aug 06 '15 at 03:49
That did it, I had to add a letter in front of each column and the table name. I did it for one table, now I just need to do it with the rest. Thanks! — Matt, Aug 06 '15 at 03:56

score 0 · Answer 1 · answered Aug 06 '15 at 03:42

cursor = db.query(postregion,columnsx,Selection,SelectionArgsx,null,null,null,null);
        while (cursor.moveToNext()) {
            ratio = cursor.getString(cursor.getColumnIndexOrThrow(""+orientation));
            Toast.makeText(getActivity(),"Ratio:"+ ratio, Toast.LENGTH_SHORT).show();
        }

columnsx should be n string, you are sending Integer orientation = orientaationsel * 5; and you should specify the name of you column, not the value.

So your sintax should be

    cursor = db.query(postregion,"orientacionColumnName",Selection,SelectionArgsx,null,null,null,null);
        while (cursor.moveToNext()) {
            ratio = cursor.getString(cursor.getColumnIndexOrThrow(""+orientation));
            Toast.makeText(getActivity(),"Ratio:"+ ratio, Toast.LENGTH_SHORT).show();
        }

Same thing for your Selection should be your where columns specs, not Integer.

Regards.

The names of my columns are numbers, unfortunately. I thought it would be ok, as long as I reference them as strings, using ""+. — Matt, Aug 06 '15 at 03:47
just check this answer: http://stackoverflow.com/questions/3694276/what-are-valid-table-names-in-sqlite — Max Pinto, Aug 06 '15 at 03:50
And then, i recommend you to analize your columns name again, or your data structure. — Max Pinto, Aug 06 '15 at 03:51

score 0 · Answer 2 · answered Aug 06 '15 at 03:57

Replace postregion(first arg of query) to tablename, as per error suggest.

android.database.sqlite.SQLiteException: near "16": syntax error (code 1): , while compiling: SELECT 10 FROM 16 WHERE _id=?

 cursor = db.query(postregion,columnsx,Selection,SelectionArgsx,null,null,null,null);

replce like this

 cursor = db.query("tableName",columnsx,Selection,SelectionArgsx,null,null,null,null);

score 0 · Accepted Answer · edited May 23 '17 at 10:26

Turns out that table and column names have to begin with a letter (or at least not a number)

What are valid table names in SQLite?

Changed things and now they work.

But, adding quotes like this doesn't require me to change the entire database. It was suggested by someone and then they deleted their answer

     String[] columnsx = {String.format("\"%s\"", orientation)};
             Selection = "Slope=?";
             String[] SelectionArgsx ={""+pitch};
             String ratio ="1";

                //This is where the error is called.
            cursor = db.query(String.format("\"%s\"", postregion),columnsx,Selection,SelectionArgsx,null,null,null,null);

Android SQLite: near "...": syntax error (code 1): , while compiling: SELECT ".." FROM ".."WHERE _id=?

3 Answers3