SQLite Syntax Error code(1)

Question

I am facing problem from long time.

I am dynamically generating insert query in android SQLite database it throws following error.

     android.database.sqlite.SQLiteException: near "(": syntax error (code 1): , while compiling: 
            INSERT INTO 'contacts' (id, name, phone_number, email, fax, job, company, website, address, 
            mobile) VALUES(98,'Vijay Bachani','022 - 61608080','','','TRAVEL CONSULTANT','RIYA TRAVEL &    
        TOURS (I) PVT. LTD.','WWW.RIYA.TRAVEL','237 Gulab Building, P.D Mello Road, Mumbai, 400001',''),
    (99,'Taha Udaipurwala','022 - 61608080','taha.udaipurwala@riya.travel','','JR. CONSULTANT','RIYA TRAVEL & TOURS 
    (I) PVT. LTD.','WWW.RIYA.TRAVEL','237 Gulab Building, P.D Mello Road, Mumbai, 400001',''),(100,'Salman Lakdawala','022 - 61608080','salman.lakdawala@riya.travel','','JUNIOR CONSULTANT','RIYA TRAVEL & TOURS (I) PVT. LTD.','WWW.RIYA.TRAVEL','237 Gulab Building, P.D Mello Road, Mumbai, 400001','9768683322'),(101,'Vipul Kharat','022 - 61608080','vipul.kharat@riya.travel','','JR. CONSULTANT','RIYA TRAVEL & TOURS (I) PVT. LTD.','WWW.RIYA.TRAVEL','237 Gulab Building, P.D Mello Road, Mumbai, 400001','9768215903'),(102,'Abdul Rafiq Siddique','022 - 61608080','rafiq.siddique@riya.travel','','JR. CONSULTANT','RIYA TRAVEL & TOURS (I) PVT. LTD.','WWW.RIYA.TRAVEL','237 Gulab Building, P.D Mello Road, Mumbai, 400001','9820327417'),(103,'Suryaji More','022 - 61608080','','','OFFICE ASSISTANT','RIYA TRAVEL & TOURS (I) PVT. LTD.','WWW.RIYA.TRAVEL','237 Gulab Building, P.D Mello Road, Mumbai, 400001',''),(104,'Wayne Joseph','022 - 61608080','','','SR. CONSULTANT','RIYA TRAVEL & TOURS (I) PVT. LTD.','WWW.RIYA.TRAVEL','237 Gulab Building, P.D Mello Road, Mumbai, 400001',''),(105,'GEORGE THOMAS','022 - 67772300','george.thomas@riya.travel','','GENERAL MANAGER','RIYA TRAVEL & TOURS (I) PVT. LTD.','WWW.RIYA.TRAVEL','M/1, Leela Business Park, Andheri Kurla road, Andheri (East), Mumbai - 400059','9820128819'),(106,'K P RAMKUMAR','022 - 67772300','ramkumar.menon@riya.travel','','MANAGER','RIYA TRAVEL & TOURS (I) PVT. LTD.','WWW.RIYA.TRAVEL','M/1, Leela Business Park, Andheri Kurla road, Andheri (East), Mumbai - 400059','9619481065'),(107,'SANDESH CHAVAN','022 - 29258611','sandesh.chavan@riya.travel','','ASST. MANAGER','RIYA TRAVEL & TOURS (I) PVT. LTD.','WWW.RIYA.TRAVEL','Atlanta Arcade, Ground Floor, Marol Church Road, Opp. The Leela, Andheri (E), Mumbai - 400 059','9619246722'),(108,'SUBRAMANIAN M S','022 - 61608080','subramanian.s@riya.travel','','ASST. MANAGER','RIYA TRAVEL & TOURS (I) PVT. LTD.','WWW.RIYA.TRAVEL','237 Gulab Building, P.D Mello Road, Mumbai, 400001',''),(109,'JOVITA R. GONSALVES','022 - 29258611','jovita.gonsalves@riya.travel','','TEAM LEADER','RIYA TRAVEL & TOURS (I) 
PVT. LTD.','WWW.RIYA.TRAVEL','Atlanta Arcade, Ground Floor, Marol Church Road, Opp. The Leela, Andheri (E), Mumbai - 400 059','9881847272'),(110,'SALIM JAMADAR','022 - 
67772300','salim.jamadar@riya.travel','','SR. CONSULTANT','RIYA TRAVEL & TOURS (I) PVT. LTD.','WWW.RIYA.TRAVEL','M/1, Leela Business Park, Andheri Kurla road, Andheri (East), Mumbai - 400059','9820593817'),(111,'ROHAN SURVE','022 - 67772300','rohan.surve@riya.travel','','ASST. MANAGER','RIYA TRAVEL & TOURS (I) PVT. LTD.','WWW.RIYA.TRAVEL','M/1, Leela Business Park, Andheri Kurla road, Andheri (East), Mumbai - 400059','8898211973'),(112,'NITHIN K','022 - 61608080','nithin.k@riya.travel','','ASST. MANAGER','RIYA TRAVEL & TOURS (I) PVT. LTD.','WWW.RIYA.TRAVEL','237 Gulab Building, P.D Mello Road, Mumbai, 400001',''),(113,'MANOJ TAMBE','022 - 61608080','manoj.tambe@riya.travel','','SR. OFFICER','RIYA TRAVEL & TOURS (I) PVT. LTD.','WWW.RIYA.TRAVEL','237 Gulab Building, P.D Mello Road, Mumbai, 400001',''),(114,'SUBASH N.L','022 - 29258611','subash.nl@riya.travel','','MANAGER','RIYA TRAVEL & TOURS (I) PVT. LTD.','WWW.RIYA.TRAVEL','Atlanta Arcade, Ground Floor, Marol Church Road, Opp. The Leela, Andheri (E), Mumbai - 400 059','9819517027'),(115,'DAVID M','022 - 29258611','david.m@riya.travel','','OFFICER','RIYA TRAVEL & TOURS (I) PVT. LTD.','WWW.RIYA.TRAVEL','Atlanta Arcade, Grou

Java Code:

for (int i=0; i<flag; i++) {

                    JSONObject item = contactsdetails.getJSONObject(k);
                    int id = Integer.parseInt(item.getString("ID"));
                    String name = item.getString("Name");
                    String phone = item.getString("Telephone");
                    String email = item.getString("Email");
                    String fax = item.getString("Fax");
                    String company = item.getString("Company");
                    String address = item.getString("Address");
                    String  job= item.getString("Job");
                    String web = item.getString("Web");
                    String mobile = item.getString("Mobile"); 

                    //sqlString += "INSERT INTO '"+TABLE_CONTACTS+"' ("+KEY_ID+", "+KEY_NAME+", "+KEY_PH_NO+", "+KEY_EMAIL+", "+KEY_FAX+", "+KEY_JOB+", "+KEY_COMPANY+", "+KEY_WEBSITE+", "+KEY_ADDRESS+", "+KEY_MOBILE+") VALUES ("+id+",'"+name+"','"+phone+"','"+email+"','"+fax+"','"+job+"','"+company+"','"+web+"','"+address+"','"+mobile+"');";
                     sqlString += "("+id+",'"+name+"','"+phone+"','"+email+"','"+fax+"','"+job+"','"+company+"','"+web+"','"+address+"','"+mobile+"'),";
                     k++;
                }

Really appreciate if help

You probably forgot to add a space somewhere when creating query. Can you post the code where are you doing this query? — hrskrs, Oct 20 '14 at 12:01
can u attach the sqlite file created inside data folder in android? — Angad Tiwari, Oct 20 '14 at 12:05
or you itself first test on SQLiteManger Mozilla's Add-ons.... — Angad Tiwari, Oct 20 '14 at 12:06
Your SQL command is ending with "[...] ,'Atlanta Arcade, Grou". Sure, this is complete? And for being of any help, this is far too long. Make a _short_ example that shows your problem. — Seelenvirtuose, Oct 20 '14 at 12:20

score 1 · Answer 1 · answered Oct 20 '14 at 12:09

1

Get rid of the string concatenation. Use ? as positional placeholders. This not only will handle the apostrophes and quotation marks that you are ignoring, but it will help defend you against SQL injection attacks.

answered Oct 20 '14 at 12:09

CommonsWare

910,778
176
2,215
2,253

score 0 · Answer 2 · answered Oct 20 '14 at 12:03

0

You're trying to insert several rows with one INSERT. That cannot be done. You need one INSERT for each row.

INSERT INTO 'contacts' (id, name, phone_number, email, fax, job, company, website, address,  mobile) VALUES (98,'Vijay Bachani','022 - 61608080','','','TRAVEL CONSULTANT','RIYA TRAVEL & TOURS (I) PVT. LTD.','WWW.RIYA.TRAVEL','237 Gulab Building, P.D Mello Road, Mumbai, 400001','')
INSERT INTO 'contacts' (id, name, phone_number, email, fax, job, company, website, address,  mobile) VALUES (99,'Taha Udaipurwala','022 - 61608080','taha.udaipurwala@riya.travel','','JR. CONSULTANT','RIYA TRAVEL & TOURS (I) PVT. LTD.','WWW.RIYA.TRAVEL','237 Gulab Building, P.D Mello Road, Mumbai, 400001','')

and so on... Each of these INSERT needs to be executed one by one as well.

answered Oct 20 '14 at 12:03

m0skit0

23,052
11
71
113

i got error in second loop iteration. in first iteration I have inserted 500 rows in one call – Amy Oct 20 '14 at 12:05
Sorry I think you're wrong. SQL INSERT syntax doesn't allow that. – m0skit0 Oct 20 '14 at 12:06
2

@m0skit0: Actually, that does appear to be valid syntax: http://sqlite.org/lang_insert.html – CommonsWare Oct 20 '14 at 12:07
See this http://stackoverflow.com/questions/16055566/insert-multiple-rows-in-sqlite – Amy Oct 20 '14 at 12:07
@CommonsWare Right, but only since SQLite 3.7.11. Android has 3.5.X (or even lower) IIRC. – m0skit0 Oct 20 '14 at 12:09
1

@m0skit0: Nice catch (wish the SQLite documentation made that more obvious). However, Android has been shipping with 3.7.11 for a while now: http://stackoverflow.com/questions/2421189/version-of-sqlite-used-in-android If your `minSdkVersion` is high enough, it should be safe to use this. That being said, I'd never do it, as it will make the use of positional parameters that much more difficult. – CommonsWare Oct 20 '14 at 12:10
Then OP should clarify the Android version he's using. In any case, for more compatibility he should not use this syntax. – m0skit0 Oct 20 '14 at 12:11
@AmolTate Note: *supported by SQLite since version 3.7.11*. What Android version are you using? – m0skit0 Oct 20 '14 at 12:12
@AmolTate Then syntax is OK, but just keep in mind it will not work for Android < 4.0. – m0skit0 Oct 21 '14 at 09:16
@m0skit0 I found solution. Thank you for your consideration – Amy Oct 21 '14 at 09:50

SQLite Syntax Error code(1)

2 Answers2