CODEWITHSUNDEEP
androidsql
M.V.
M.V.

Reputation: 1672

ANDROID SQL problem

I can't figure what is wrong with this query in Android:

INSERT INTO current 
(channel_name, channel, id_language, title, start, length, catergory, genre, sortOrder)   VALUES 
('3SAT', '3SAT', '27', 'Frizija', '2011-05-16 13:45:00', '45', '8', '103', '0'), 
('A Kanal', 'AKANAL', '2', 'Varna hiša', '2011-05-16 13:40:00', '110', '1', '71', '1'), 
('ABMoteurs', 'ABMOTEURS', '12', 'Garažna pošast', '2011-05-16 14:00:00', '60', '11', '81', '2'), 
('ARD', 'ARD', '27', 'Dnevnik', '2011-05-16 14:00:00', '10', '7', '6', '3'), 
('ARTE', 'ARTE', '27', 'Sanje o podeželju: Normandija', '2011-05-16 14:00:00', '45', '8', '103', '4');

When I do it this way: 

    newTaskValues.put(KEY_CURRENT_CHANNEL_NAME  , channel_name);
    newTaskValues.put(KEY_CURRENT_CHANNEL       , channel);
    newTaskValues.put(KEY_CURRENT_ID_LANG       , id_language);
    newTaskValues.put(KEY_CURRENT_TITLE         , title);
    newTaskValues.put(KEY_CURRENT_START         , start);
    newTaskValues.put(KEY_CURRENT_LENGTH        , length);
    newTaskValues.put(KEY_CURRENT_CATEGORY      , category);
    newTaskValues.put(KEY_CURRENT_GENRE         , genre);
    newTaskValues.put(KEY_CURRENT_ORDER         , sort);
    db.insert(DATABASE_TABLE_CURRENT            , null, newTaskValues);

it works OK ... But I want to make multiple inserts with one query, because there is about 160 inserts at once...

Any ideas... Thanks for helping me out!

I always get this from LogCat:

05-16 14:07:59.601: ERROR/AndroidRuntime(15153): FATAL EXCEPTION: main
05-16 14:07:59.601: ERROR/AndroidRuntime(15153): android.database.sqlite.SQLiteException: near ",": syntax error: , while compiling: INSERT INTO current (channel_name, channel, id_language, title, start, length, catergory, genre, sortOrder) VALUES ('3SAT', '3SAT', '27', 'Frizija', '2011-05-16 13:45:00', '45', '8', '103', '0'), ('A Kanal', 'AKANAL', '2', 'Varna hiša', '2011-05-16 13:40:00', '110', '1', '71', '1'), ('ABMoteurs', 'ABMOTEURS', '12', 'Garažna pošast', '2011-05-16 14:00:00', '60', '11', '81', '2'), ('ARD', 'ARD', '27', 'Dnevnik', '2011-05-16 14:00:00', '10', '7', '6', '3'), ('ARTE', 'ARTE', '27', 'Sanje o podeželju: Normandija', '2011-05-16 14:00:00', '45', '8', '103', '4');
05-16 14:07:59.601: ERROR/AndroidRuntime(15153):     at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
05-16 14:07:59.601: ERROR/AndroidRuntime(15153):     at android.database.sqlite.SQLiteCompiledSql.compile(SQLiteCompiledSql.java:92)
05-16 14:07:59.601: ERROR/AndroidRuntime(15153):     at android.database.sqlite.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:65)
05-16 14:07:59.601: ERROR/AndroidRuntime(15153):     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:83)
05-16 14:07:59.601: ERROR/AndroidRuntime(15153):     at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:49)
05-16 14:07:59.601: ERROR/AndroidRuntime(15153):     at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:42)
05-16 14:07:59.601: ERROR/AndroidRuntime(15153):     at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1356)
05-16 14:07:59.601: ERROR/AndroidRuntime(15153):     at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1324)
05-16 14:07:59.601: ERROR/AndroidRuntime(15153):     at com.TVSpored.ToDoDBAdapter.insertCurrent(ToDoDBAdapter.java:416)
05-16 14:07:59.601: ERROR/AndroidRuntime(15153):     at com.TVSpored.Currently$LoadCurrent.onPostExecute(Currently.java:269)
05-16 14:07:59.601: ERROR/AndroidRuntime(15153):     at com.TVSpored.Currently$LoadCurrent.onPostExecute(Currently.java:1)
05-16 14:07:59.601: ERROR/AndroidRuntime(15153):     at android.os.AsyncTask.finish(AsyncTask.java:417)
05-16 14:07:59.601: ERROR/AndroidRuntime(15153):     at android.os.AsyncTask.access$300(AsyncTask.java:127)
05-16 14:07:59.601: ERROR/AndroidRuntime(15153):     at android.os.AsyncTask$InternalHandler.handleMessage(AsyncTask.java:429)
05-16 14:07:59.601: ERROR/AndroidRuntime(15153):     at android.os.Handler.dispatchMessage(Handler.java:99)
05-16 14:07:59.601: ERROR/AndroidRuntime(15153):     at android.os.Looper.loop(Looper.java:130)
05-16 14:07:59.601: ERROR/AndroidRuntime(15153):     at android.app.ActivityThread.main(ActivityThread.java:3683)
05-16 14:07:59.601: ERROR/AndroidRuntime(15153):     at java.lang.reflect.Method.invokeNative(Native Method)
05-16 14:07:59.601: ERROR/AndroidRuntime(15153):     at java.lang.reflect.Method.invoke(Method.java:507)
05-16 14:07:59.601: ERROR/AndroidRuntime(15153):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:839)
05-16 14:07:59.601: ERROR/AndroidRuntime(15153):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:597)
05-16 14:07:59.601: ERROR/AndroidRuntime(15153):     at dalvik.system.NativeStart.main(Native Method)
05-16 14:07:59.609: WARN/ActivityManager(109):   Force finishing activity com.TVSpored/.TVSpored

Code (I get JSONArray in function):

    String SQL = "INSERT INTO " + DATABASE_TABLE_CURRENT + " " +  
    "(" + KEY_CURRENT_CHANNEL_NAME + ", " + KEY_CURRENT_CHANNEL + ", " + KEY_CURRENT_ID_LANG + ", " + 
    KEY_CURRENT_TITLE + ", " + KEY_CURRENT_START + ", " + KEY_CURRENT_LENGTH + ", " + 
    KEY_CURRENT_CATEGORY + ", " + KEY_CURRENT_GENRE + ", " + KEY_CURRENT_ORDER + ") VALUES ";

  for (int i = 0; i <= CShows.length() && i < 5; i++) 
    {
        JSONObject jsonObject;
        try {
              jsonObject = CShows.getJSONObject(i);
              String start = jsonObject.getString("start");
              Integer duration = jsonObject.getInt("length");
              String title = jsonObject.getString("shortTitle");
              String epg_channel = jsonObject.getString("channel");
              String channel_name = jsonObject.getString("channel_name");
              Integer emission_id = jsonObject.getInt("id_emission");
              Integer id_language = 0;
              Integer id_category = 0;
              Integer id_genre = 0;
              if(jsonObject.getString("id_language").length() > 0)
              {
                  id_language = jsonObject.getInt("id_language");
              }
              if(jsonObject.getString("id_category").length() > 0)
              {
                  id_category = jsonObject.getInt("id_category");
              }
              if(jsonObject.getString("id_genre").length() > 0)
              {
                  id_genre = jsonObject.getInt("id_genre");
              }

              SQL += "(" + "'" + channel_name   + "', " + "'" + epg_channel + "', " + "'" + id_language + "', " + 
                  "'" + title           + "', " + "'" + start + "', "       + "'" + duration + "', " 
                + "'" + id_category     + "', " + "'" + id_genre + "', "    + "'" + i + "')";
            if(CShows.length() != (1 + i) && i != 4)
            {
                Log.d("TV Spored++", "I je enako: " + i);
                SQL = SQL + ", ";
            }

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


    }
  SQL = SQL + ";";

    Log.d("TV Spored++", SQL.toString());
    db.rawQuery(SQL, null);

Upvotes: 1

Views: 330

Answers (2)

Alex K.
Alex K.

Reputation: 175866

Inserting in the form VALUES (xxx), (yyy) does not appear to be supported by SQLite, instead you can:

INSERT INTO current (channel_name, channel, id_language, title, start, length, catergory, genre, sortOrder) 
  SELECT '3SAT', '3SAT', '27', 'Frizija', '2011-05-16 13:45:00', '45', '8', '103', '0'
  UNION ALL SELECT 'A Kanal', 'AKANAL', '2', 'Varna hiša', '2011-05-16 13:40:00', '110', '1', '71', '1'
  UNION ALL SELECT 'ABMoteurs', 'ABMOTEURS', '12', 'Garažna pošast', '2011-05-16 14:00:00', '60', '11', '81', '2'

Upvotes: 2

ba__friend
ba__friend

Reputation: 5903

Why did you posted the ContentValues part if its not even used? ;) As I said you're generating invalid SQL (maybe read the basics again). A approach would be to preserve the SQL variable and work in the loop with a new one.

In pseudocode:

for i in elements
    String insertSQL = SQL
    insertSQL += newValues
    insert()

Upvotes: 0

Related Questions