CODEWITHSUNDEEP
javaandroidsqlsqliteandroid-sqlite
user1781123
user1781123

Reputation:

SQLite insert query error

I am using following query to insert data into a SQLite database for Android.

INSERT INTO ServerData 
VALUES('404', '192.168.145.101', '1', '1', '1'),
      ('404', '192.168.145.101', '2', '1', '1')

But I am getting an error, the LogCat details are below:

11-22 15:14:56.588: E/Database(1609): Failure 1 (near ",": syntax error) on 0x2e3e30 when preparing 'INSERT INTO ServerData VALUES('404','192.168.145.101','1','1','1'),('404','192.168.145.101','2','1','1')'.
11-22 15:14:56.618: W/dalvikvm(1609): threadid=10: thread exiting with uncaught exception (group=0x40015560)
11-22 15:14:56.648: E/AndroidRuntime(1609): FATAL EXCEPTION: AsyncTask #1
11-22 15:14:56.648: E/AndroidRuntime(1609): java.lang.RuntimeException: An error occured while executing doInBackground()
11-22 15:14:56.648: E/AndroidRuntime(1609): at android.os.AsyncTask$3.done(AsyncTask.java:200)
11-22 15:14:56.648: E/AndroidRuntime(1609): at java.util.concurrent.FutureTask$Sync.innerSetException(FutureTask.java:274) 11-22 15:14:56.648: E/AndroidRuntime(1609): at java.util.concurrent.FutureTask.setException(FutureTask.java:125) 11-22 15:14:56.648: E/AndroidRuntime(1609): at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:308) 11-22 15:14:56.648: E/AndroidRuntime(1609): at java.util.concurrent.FutureTask.run(FutureTask.java:138) 11-22 15:14:56.648: E/AndroidRuntime(1609): at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1088) 11-22 15:14:56.648: E/AndroidRuntime(1609): at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:581) 11-22 15:14:56.648: E/AndroidRuntime(1609): at java.lang.Thread.run(Thread.java:1019) 11-22 15:14:56.648: E/AndroidRuntime(1609): Caused by: android.database.sqlite.SQLiteException: near ",": syntax error: INSERT INTO ServerData VALUES('404','192.168.145.101','1','1','1'),('404','192.168.145.101','2','1','1') 11-22 15:14:56.648: E/AndroidRuntime(1609): at android.database.sqlite.SQLiteDatabase.native_execSQL(Native Method) 11-22 15:14:56.648: E/AndroidRuntime(1609): at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1763) 11-22 15:14:56.648: E/AndroidRuntime(1609): at com.pcloud.fileshare.database.DBMethods.InsertToServerTable(DBMethods.java:228) 11-22 15:14:56.648: E/AndroidRuntime(1609): at com.pcloud.fileshare.dataAccess.commonMethod.updateServerTable(commonMethod.java:472) 11-22 15:14:56.648: E/AndroidRuntime(1609): at com.pcloud.fileshare.mainPages.userLogin$backgroundTask.doInBackground(userLogin.java:238) 11-22 15:14:56.648: E/AndroidRuntime(1609): at com.pcloud.fileshare.mainPages.userLogin$backgroundTask.doInBackground(userLogin.java:1) 11-22 15:14:56.648: E/AndroidRuntime(1609): at android.os.AsyncTask$2.call(AsyncTask.java:185) 11-22 15:14:56.648: E/AndroidRuntime(1609): at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:306) 11-22 15:14:56.648: E/AndroidRuntime(1609): ... 4 more 11-22 15:15:00.059: E/WindowManager(1609): Activity com.pcloud.fileshare.mainPages.userLogin has leaked window com.android.internal.policy.impl.PhoneWindow$DecorView@40529668 that was originally added here 11-22 15:15:00.059: E/WindowManager(1609): android.view.WindowLeaked: Activity com.pcloud.fileshare.mainPages.userLogin has leaked window com.android.internal.policy.impl.PhoneWindow$DecorView@40529668 that was originally added here 11-22 15:15:00.059: E/WindowManager(1609): at android.view.ViewRoot.(ViewRoot.java:258) 11-22 15:15:00.059: E/WindowManager(1609): at android.view.WindowManagerImpl.addView(WindowManagerImpl.java:148) 11-22 15:15:00.059: E/WindowManager(1609): at android.view.WindowManagerImpl.addView(WindowManagerImpl.java:91) 11-22 15:15:00.059: E/WindowManager(1609): at android.view.Window$LocalWindowManager.addView(Window.java:424) 11-22 15:15:00.059: E/WindowManager(1609): at android.app.Dialog.show(Dialog.java:241) 11-22 15:15:00.059: E/WindowManager(1609): at com.pcloud.fileshare.mainPages.userLogin$backgroundTask.onPreExecute(userLogin.java:251) 11-22 15:15:00.059: E/WindowManager(1609): at android.os.AsyncTask.execute(AsyncTask.java:391) 11-22 15:15:00.059: E/WindowManager(1609): at com.pcloud.fileshare.mainPages.userLogin$1.onItemClick(userLogin.java:221) 11-22 15:15:00.059: E/WindowManager(1609): at android.widget.AdapterView.performItemClick(AdapterView.java:284) 11-22 15:15:00.059: E/WindowManager(1609): at android.widget.ListView.performItemClick(ListView.java:3513) 11-22 15:15:00.059: E/WindowManager(1609): at android.widget.AbsListView$PerformClick.run(AbsListView.java:1812) 11-22 15:15:00.059: E/WindowManager(1609): at android.os.Handler.handleCallback(Handler.java:587) 11-22 15:15:00.059: E/WindowManager(1609): at android.os.Handler.dispatchMessage(Handler.java:92) 11-22 15:15:00.059: E/WindowManager(1609): at android.os.Looper.loop(Looper.java:123) 11-22 15:15:00.059: E/WindowManager(1609): at android.app.ActivityThread.main(ActivityThread.java:3683) 11-22 15:15:00.059: E/WindowManager(1609): at java.lang.reflect.Method.invokeNative(Native Method) 11-22 15:15:00.059: E/WindowManager(1609): at java.lang.reflect.Method.invoke(Method.java:507) 11-22 15:15:00.059: E/WindowManager(1609): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:839) 11-22 15:15:00.059: E/WindowManager(1609): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:597) 11-22 15:15:00.059: E/WindowManager(1609): at dalvik.system.NativeStart.main(Native Method)

The same query works fine in SQLite Expert but not in code

Upvotes: 1

Views: 265

Answers (3)

Jivan
Jivan

Reputation: 1320

In SQLite, you cannot use the comma separator for multiple row insertion in the table. So, for your case you can try this :

INSERT INTO ServerData
      SELECT '404','192.168.145.101','1','1','1'
UNION SELECT '404','192.168.145.101','2','1','1';

Upvotes: 1

SRIRAM
SRIRAM

Reputation: 1888

try this you cant insert multiple values seperated by comma

INSERT INTO ServerData VALUES('404','192.168.145.101','1','1','1')

 INSERT INTO ServerData VALUES ('404','192.168.145.101','2','1','1')

try this

 INSERT INTO 'tablename' ('column1', 'column2') VALUES
    ('data1', 'data2'),
    ('data3', 'data4'),
    ('data5', 'data6'),
   ('data7', 'data8');

can be recast into sqlite as:

 INSERT INTO 'tablename'
  SELECT 'data1' AS 'column1',
         'data2' AS 'column2'
 UNION SELECT 'data3', 'data4'
  UNION SELECT 'data5', 'data6'
  UNION SELECT 'data7', 'data8'

Upvotes: 1

Nermeen
Nermeen

Reputation: 15973

Check the solution in https://stackoverflow.com/a/5009740/1434631

You cannot use comma separation to insert multiple rows in sqlite..

It's better if you use SQLiteDatabase

ContentValues values = new ContentValues();
for(int i = 0; i<=5; i++) {
values.put(COLUMN_NAME, i);
values.put(COLUMN_NAME, 0);
db.insert(TABLE_NAME, null, values);
}

Upvotes: 1

Related Questions